MySQL 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方式的执行计划:

使用LEFT JOIN方式的执行计划:

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

推荐看下:为什么索引能提高查询速度?

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

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

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

从执行过程来看,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子查询的执行次数即外层查询结果集的数据量。

  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子查询中无法使用索引,使得子查询性能较差,最终影响整个查询的执行性能。

京东商城也曾出现过大量类似案例,一些表使用VARCHAR来存放订单号,而另一些表使用BIGINT来存放,在两表进行管理时性能极差,希望研发同事引以为戒。关注公众号Java技术栈回复m36获取一份MySQL研发军规。

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。本文链接:https://blog.csdn.net/guanfengliang1988/article/details/92814376

推荐去我的博客阅读更多:

1.Java JVM、集合、多线程、新特性系列教程

2.Spring MVC、Spring Boot、Spring Cloud 系列教程

3.Maven、Git、Eclipse、Intellij IDEA 系列工具教程

4.Java、后端、架构、阿里巴巴等大厂最新面试题

觉得不错,别忘了点赞+转发哦!

最后,关注下面的栈长的微信公众号:Java技术栈,回复:福利,可以免费获取一份我整理的 2020 最新 Java 面试题,真的非常全(含答案),无任何套路。

©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值