mysql - 从性能点理解两个查询之间的区别

Question

我有同一个查询的这两个版本。两者都产生相同的结果(164 行)。但是第二个需要 0.5 秒，而第一个需要 17 秒。有人可以解释这里发生了什么吗？

TABLE organizations : 11988 ROWS
TABLE transaction_metas : 58232 ROWS
TABLE contracts_history : 219469 ROWS

# TAKES 17 SEC
SELECT contracts_history.buyer_id as id, org.name, SUM(transactions_count) as transactions_count, GROUP_CONCAT(DISTINCT(tm.value)) as balancing_authorities 
From `contracts_history` 
INNER JOIN `organizations` as `org` 
ON `org`.`id` = `contracts_history`.`buyer_id` 
LEFT JOIN `transaction_metas` as `tm`
ON `tm`.`contract_token` = `contracts_history`.`token` and `tm`.`field` = '1' 
WHERE `contracts_history`.`seller_id` = '850' 
GROUP BY `contracts_history`.`buyer_id` ORDER BY `balancing_authorities` DESC


# TAKES .6 SEC
SELECT contracts_history.buyer_id as id, org.name, SUM(transactions_count) as transactions_count, GROUP_CONCAT(DISTINCT(tm.value)) as balancing_authorities 
From `contracts_history` 
INNER JOIN `organizations` as `org` 
ON `org`.`id` = `contracts_history`.`buyer_id` 
left join (select * from `transaction_metas` where contract_token in (select token from `contracts_history` where seller_id = 850)) as `tm`
ON `tm`.`contract_token` = `contracts_history`.`token` and `tm`.`field` = '1' 
WHERE `contracts_history`.`seller_id` = '850' 
GROUP BY `contracts_history`.`buyer_id` ORDER BY `balancing_authorities` DESC

解释结果:第一次查询:https://prnt.sc/hjtiw6

第二次查询:https://prnt.sc/hjtjjg

根据我对第一个查询的调试，很明显 left join 到 transaction_metas 表使它变慢，所以我试图限制它的行而不是加入到完整的表格。它似乎有效，但我不明白为什么。

Answer 1

Join 是表格中行的一组组合。请记住，在第一个查询中，引擎会合并所有结果以在之后进行过滤。在第二种情况下，它在尝试进行组合之前应用过滤器。

最好的情况是在没有子查询的 JOIN 子句中使用过滤器。很像这样:

SELECT contracts_history.buyer_id as id, org.name, SUM(transactions_count) as transactions_count, GROUP_CONCAT(DISTINCT(tm.value)) as balancing_authorities 
From `contracts_history` 
INNER JOIN `organizations` as `org` 
ON `org`.`id` = `contracts_history`.`buyer_id` 
  AND `contracts_history`.`seller_id` = '850'
LEFT JOIN `transaction_metas` as `tm`
ON `tm`.`contract_token` = `contracts_history`.`token`
  AND `tm`.`field` = 1
GROUP BY `contracts_history`.`buyer_id` ORDER BY `balancing_authorities` DESC

注意:当您通过使用子查询过滤来减小连接表的大小时，它可能允许行适合缓冲区。小缓冲区限制的好技巧。

更好的解释: https://dev.mysql.com/doc/refman/5.5/en/explain-output.html