提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  其他

当前栏目

什么？一对多场景下的exists子查询比join连表查询快这么多？

2023-03-15 22:01:08 时间

两张表连表查询可以使用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

相关文章