什么？一对多场景下的exists子查询比join连表查询快这么多？
什么？一对多场景下的exists子查询比join连表查询快这么多？
两张表连表查询可以使用join、exists和in等方式，其中exists和in都属于依赖子查询。参考博客1给出了三种方式使用场景。本文记录一次将join查询转换成exists查询后，性能得到了20倍以上的提升。
现有送货单（delivery_order）和送货商品明细（delivery_sku）两张表。很明显，一个送货单对应多个商品明细。现在有一个需求是根据商品名称或skuId模糊匹配查询包含该sku的送货单列表。通常基于join方式的查询语句为：
select dOrder.*
from delivery_sku dSku join delivery_order dOrder ON
dSku.delivery_no = dOrder.delivery_no
and dSku.tenant_id = 1 and dSku.store_id = 2
and dSku.create_time >= '2020-02-28 20:59:36' and dSku.create_time <= '2020-08-29 20:59:36'
GROUP BY dOrder.delivery_no ORDER BY dOrder.create_time DESC LIMIT 20;执行计划如下：
从图中可知，该查询使用了临时表，以及filesort，因而性能较差。
首次优化
查询语句中，对tenant_id、store_id和create_time等字段的限定只对sku表进行了限制，而没有对送货单表做限制，导致只有sku表使用了索引，而送货单表没能走索引。优化后的语句为：
explain select dOrder.* from delivery_sku dSku join delivery_order dOrder ON
dSku.delivery_no = dOrder.delivery_no
where dSku.tenant_id = 1 and dSku.entity_id = 2
and dSku.create_time >= '2020-02-28 20:59:36' and dSku.create_time <= '2020-08-29 20:59:36'
and dOrder.tenant_id = 1 and dOrder.store_id = 2
and dOrder.create_time >= '2020-02-28 20:59:36' and dOrder.create_time <= '2020-08-29 20:59:36'
GROUP BY dOrder.delivery_no ORDER BY dOrder.create_time DESC LIMIT 20;首次优化后的执行计划：
二次优化
这次优化的目标就是去掉临时表以及filesort。其实仔细分析我们的sql语句，导致使用临时表和filesort的原因是我们使用了group by，因为我们使用了join查询，为了避免重复，我们必须要使用group by或distinct来去重。再分析我们的业务场景：在我们的业务场景中，一个送货单对应多个商品，属于典型的一对多，使用exists就可以避免使用group by或distinct，其性能肯定能好于join。
select dOrder.*
from delivery_order dOrder where exists (select 1 from delivery_sku dSku
where dSku.tenant_id = 1 and dSku.store_id = 2
and dSku.delivery_no = dOrder.delivery_no
and dSku.create_time >= '2020-02-28 20:59:36' and dSku.create_time <= '2020-08-29 20:59:36')
and dOrder.tenant_id = 1 and dOrder.store_id = 2
and dOrder.create_time >= '2020-02-28 20:59:36' and dOrder.create_time <= '2020-08-29 20:59:36' ORDER BY dOrder.create_time DESC LIMIT 20;第二次优化后的执行计划为：
从图中可以看到，查询方式从之前的两个Simple查询变成了一个primary和dependent subquery。而且，没有了临时表和filesort。
通过多次执行优化前和第二次优化后的平均查询耗时发现，第二次优化后性能提升21倍：
优化前执行耗时：
优化后执行耗时：
由此可见，并不是如很多博客所说的那样，dependent subquery就一定性能差，需要根据实际情况来分析。
最后，我们的order by使用的是create_time字段。实际上，create_time和主键id是等效的，所以可以使用order by id来替换order by create_time，以进一步利用唯一索引的自然顺序来进一步提升查询性能。
参考博客：
1、https://www.jianshu.com/p/cfee30b913dc MySQL中使用JOIN、EXISTS、IN时该注意的问题
2、https://blog.csdn.net/Saintyyu/article/details/100170320 Mysql中的join、cross join、inner join是等效的
3、https://www.cnblogs.com/xqzt/p/4469673.html 表连接的三种方式详解 hash join、merge join、 nested loop
4、https://blog.csdn.net/qq_40965479/article/details/107642966 mysql有关《索引失效》的原因及解决办法 《最全总结》
5、https://blog.csdn.net/tracymm19891990/article/details/104798190 MySQL总结（五）——Explain的坑以及如何分析SQL
6、https://segmentfault.com/a/1190000021815758 彻底搞懂MySQL索引优化Explain
7、https://blog.csdn.net/zhanlijun/article/details/11908459 mysql 原理：explain
腾讯云开发者
