MySQL 查询性能 - 子查询与 Join

Question

我不是 SQL 专家，所以我试图理解为什么两个查询的执行时间有很大不同。

common_stats 是一个大型表(300 万行)。我只是想理解为什么性能存在巨大差异。

以下查询大约需要 15 秒:

select distinct cs.basesalary_id
from common_stats AS cs
LEFT JOIN basesalary AS b ON b.id = cs.basesalary_id
WHERE (b.employee_id= 100 AND cs.amount > 0 AND cs.pay_id is null );

此查询大约需要 1 秒:

select distinct basesalary_id from (
    select cs.basesalary_id, cs.pay_id
    from common_stats AS cs
    LEFT JOIN basesalary AS b ON b.id = cs.basesalary_id
    WHERE (b.employee_id= 100 AND cs.amount > 0)
) as temp
where pay_id is null;

Answer 1

一般规则:

• 如果子查询“缩减”了行数(例如，通过 GROUP BY 或 LIMIT)，则子查询方法会更好。
• JOIN 通常会创建比原始表更多的行。 DISTINCT 有时用于减少行数。
• 如果外部查询有一个GROUP BY，JOIN可能会创建比您想象的更多的行，并“膨胀”任何聚合(SUM、COUNT 等)，从而投票支持子查询。
• 多个子查询会导致优化效果不佳。 (从而投票支持JOIN。)

所有这些都假设最佳索引。

您的第一个查询可能会受益于

INDEX(pay_id, amount, basesalary_id) -- both "covering" and optimal for `WHERE`

查看 EXPLAIN SELECT ... 这两个查询。可能更快的是从 basesalary 开始，然后是 INDEX(employee_id)，而且这是非常有选择性的。

看到SHOW CREATE TABLE后我可能会有更多评论。