SQL JOIN 查找不具有特定值的匹配记录的记录

2022-01-23 00:00:00 join sql subquery mysql

我正在尝试加快我多年前为我雇主的购买授权应用程序编写的一些代码.基本上我有一个 SLOW 子查询，我想用 JOIN 替换它(如果它更快的话).

I'm trying to speed up some code that I wrote years ago for my employer's purchase authorization app. Basically I have a SLOW subquery that I'd like to replace with a JOIN (if it's faster).

当主管登录应用程序时，他会看到一份他尚未授权或拒绝的购买请求列表.该列表是使用以下查询生成的:

When the director logs into the application he sees a list of purchase requests he has yet to authorize or deny. That list is generated with the following query:

SELECT * FROM SA_ORDER WHERE ORDER_ID NOT IN
    (SELECT ORDER_ID FROM SA_SIGNATURES WHERE TYPE = 'administrative director');

在 sa_order 中只有大约 900 条记录，在 sa_signature 中只有 1800 条记录，这个查询仍然需要大约 5 秒才能执行.我尝试使用 LEFT JOIN 来检索我需要的记录，但我只能在 sa_signature 中获取没有匹配记录的 sa_order 记录，并且我需要 sa_order 记录没有与‘行政主管’类型匹配的记录".非常感谢您的帮助！

There are only about 900 records in sa_order and 1800 records in sa_signature and this query still takes about 5 seconds to execute. I've tried using a LEFT JOIN to retrieve records I need, but I've only been able to get sa_order records with NO matching records in sa_signature, and I need sa_order records with "no matching records with a type of 'administrative director'". Your help is greatly appreciated!

两个表的架构如下:

涉及的表格有以下布局:

The tables involved have the following layout:

CREATE TABLE sa_order
(
    `order_id`        BIGINT       PRIMARY KEY AUTO_INCREMENT,
    `order_number`    BIGINT       NOT NULL,
    `submit_date`     DATE         NOT NULL,
    `vendor_id`       BIGINT       NOT NULL,
    `DENIED`          BOOLEAN      NOT NULL DEFAULT FALSE,
    `MEMO`            MEDIUMTEXT,
    `year_id`         BIGINT       NOT NULL,
    `advisor`         VARCHAR(255) NOT NULL,
    `deleted`         BOOLEAN      NOT NULL DEFAULT FALSE
);

CREATE TABLE sa_signature
(
    `signature_id`        BIGINT          PRIMARY KEY AUTO_INCREMENT,
    `order_id`            BIGINT          NOT NULL,
    `signature`           VARCHAR(255)    NOT NULL,
    `proxy`               BOOLEAN         NOT NULL DEFAULT FALSE,
    `timestamp`           TIMESTAMP       NOT NULL DEFAULT NOW(),
    `username`            VARCHAR(255)    NOT NULL,
    `type`                VARCHAR(255)    NOT NULL
);

推荐答案

在 sa_signatures (type, order_id) 上创建索引.

除非sa_signatures 允许order_id 中的空值，否则不必将查询转换为LEFT JOIN.有了索引，NOT IN 也会执行.但是，以防万一您好奇:

This is not necessary to convert the query into a LEFT JOIN unless sa_signatures allows nulls in order_id. With the index, the NOT IN will perform as well. However, just in case you're curious:

SELECT  o.*
FROM    sa_order o
LEFT JOIN
        sa_signatures s
ON      s.order_id = o.order_id
        AND s.type = 'administrative director'
WHERE   s.type IS NULL

您应该从 sa_signatures 中选择一个 NOT NULL 列，以便 WHERE 子句执行良好.

You should pick a NOT NULL column from sa_signatures for the WHERE clause to perform well.

相关文章