mysql - 两个相似的 SQL 查询之间的巨大性能差异

Question

我有两个提供相同输出的 SQL 查询。我的第一直觉是使用这个:

SELECT * FROM performance_dev.report_golden_results 
where id IN (SELECT max(id) as 'id' from  performance_dev.report_golden_results 
group by platform_id, release_id, configuration_id)

现在，这需要大约 70 秒才能完成!

在寻找另一个解决方案时，我尝试了类似的方法:

SELECT * FROM performance_dev.report_golden_results e 
join (SELECT max(id) as 'id' 
from performance_dev.report_golden_results 
group by platform_id, release_id, configuration_id) s 
ON s.id = e.id;

令人惊讶的是，这需要 0.05 秒才能完成!!!

为什么这两个如此不同？

谢谢!

Answer 1

可能导致时间延迟的第一件事是 MySQL 对子查询使用“半连接”策略。半连接包括以下步骤:

If a subquery meets the preceding criteria, MySQL converts it to a semi-join and makes a cost-based choice from these strategies:

Convert the subquery to a join, or use table pullout and run the query as an inner join between subquery tables and outer tables. Table pullout pulls a table out from the subquery to the outer query.

Duplicate Weedout: Run the semi-join as if it was a join and remove duplicate records using a temporary table.

FirstMatch: When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.

LooseScan: Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.

Materialize the subquery into a temporary table with an index and use the temporary table to perform a join. The index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned.

但是给出显式连接会减少这些工作，这可能就是原因。

希望对您有所帮助!