MySQL not exists 真的不走索引么?

逃离我推掉我的手 2022-11-28 13:27 143阅读 0赞

在一些业务场景中，会使用NOT EXISTS语句确保返回数据不存在于特定集合，部分同事会发现NOT EXISTS有些场景性能较差，甚至有些网上谣言说”[**NOT EXISTS不走索引**][NOT EXISTS]”，哪对于NOT EXISTS语句，我们如何优化呢？

以今天优化的SQL为例，优化前SQL为：

SELECT count(1) 
    FROM t_monitor m 
    WHERE NOT exists (
      SELECT 1 
      FROM t_alarm_realtime AS a 
      WHERE a.resource_id=m.resource_id 
      AND a.resource_type=m.resource_type 
      AND a.monitor_name=m.monitor_name
    )

我们使用LEFT JOIN方式进行优化，优化后SQL为：

SELECT count(1) 
    FROM t_monitor m 
    LEFT JOIN t_alarm_realtime AS a 
       ON a.resource_id=m.resource_id 
      AND a.resource_type=m.resource_type 
      AND a.monitor_name=m.monitor_name 
    WHERE a.resource_id is NULL

**优化效果：**

优化前执行时间29秒以上，优化后1.2秒，优化提升25倍。

**NOT EXISTS真的不走索引么？**

查看两种SQL的执行计划！

**使用NOT EXIST方式的执行计划：**

![format_png][]

**使用LEFT JOIN方式的执行计划：**

![format_png 1][]

**从执行计划来看，两个表都使用了索引**，区别在于NOT EXISTS使用“**DEPENDENT SUBQUERY**”方式，而LEFT JOIN使用普通表关联的方式。

[推荐看下：为什么索引能提高查询速度？][Link 1]

通过MySQL提供的[Profiling][]方式来查看两种方式的执行过程。

使用NOT EXIST方式的执行过程：

![format_png 2][]

使用LEFT JOIN方式的执行过程：

![format_png 3][]

从执行过程来看，LEFT JOIN方式的主要消耗在Sending data一项上(1.2s)，而NOT EXISTS方式主要消耗在executeing和Sending data两项上，受限于Profiling只存放100行记录缘故。

从Profiling中只能看到47个” executeing和Sending data”的组合项(每个组合项约50us)，通过执行计划看出，外表t\_monitor的数据量为578436行，忽略统计信息不准情况下，使用NOT EXISTS方式应该会产生578436个” executeing和Sending data”的组合项，总计消耗时间=50μs\*578436=28921800us=28.92s。

**从上面执行过程可以推断出：**

[**使用NOT EXISTS方式的执行性能严重依赖于NOT EXISTS子查询的执行次数即外层查询结果集的数据量。**][NOT EXISTS_NOT EXISTS]

1.  当外层查询结果集的数据量N较小时执行性能较好，如有N=10执行时间为50μs\*10=500us=0.005s，再加上一些额外消耗，执行结果也能在0.01秒或10毫秒内范围，这个响应时间应该能被大部分应用程序接受。
2.  当外层程勋结果集的数据量N较大甚至上千万数据量时，NOT EXISTS的查询性能会变得非常糟糕，甚至会大量消耗服务器IO和CPU资源从而影响其他业务正常运行。

除上述问题外，在优化过程中发现本应该存储相同数据的resource\_id列在两个表中定义不同，一表为VARCHAR而另外一表为BIGINT，[**外部结果集的字段类型和NOT EXIST字表中字段类型不同导致NOT EXISTS子查询中无法使用索引，使得子查询性能较差，最终影响整个查询的执行性能。**][NOT EXIST_NOT EXISTS]

京东商城也曾出现过大量类似案例，一些表使用VARCHAR来存放订单号，而另一些表使用BIGINT来存放，在两表进行管理时性能极差，希望研发同事引以为戒。

[NOT EXISTS]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247489169&idx=1&sn=5188961c066ccb5f1065e8611a8c2012&chksm=eb5393a7dc241ab1d0268266b9263053a87b4b2d2ea81b86d66d49b393d6d5f1ed09e6857a4e&scene=21#wechat_redirect
[format_png]: /images/20221124/9bc8193a8ad8437096c31d939a2b511f.png
[format_png 1]: /images/20221124/493ffdc510da4de58f02c9e07bc8ab99.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247492579&idx=3&sn=1520c66e3227d5f332cd6e512b356261&chksm=eb5066d5dc27efc3e60ca05ac6a726f5a73383bf13a8e653699ff65dbac443c42ee8886bc8d0&scene=21#wechat_redirect
[Profiling]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247483819&idx=1&sn=64c8d0e4f33fcb776d72a202287944a7&chksm=eb53849ddc240d8b92b7697e0af8360ef8df8d494764f341f016094b09cc96a027696ec39194&scene=21#wechat_redirect
[format_png 2]: /images/20221124/f659ec47131b46429f073d64777b00a3.png
[format_png 3]: /images/20221124/35252788529c413ea81cedc200b7764a.png
[NOT EXISTS_NOT EXISTS]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247491744&idx=1&sn=5916fcf89d2f40065728c7c5dd1b258f&chksm=eb506596dc27ec801f487e7d6917e33cbc02eb59cc155fd1aed0cbde5e82d87ddb9bc1026bdb&scene=21#wechat_redirect
[NOT EXIST_NOT EXISTS]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw==&mid=2247486173&idx=2&sn=0603756b36279b5f5f036151af85f9fd&chksm=eb538febdc2406fd70748604c500eeaea584306c137cb2020ab5a72187c9359926015e00a46d&scene=21#wechat_redirect