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方式的施行方案:
![](/uploads/allimg/200930/a072f1b507d2a61f4621fe99cafd7d64-116414.png)
使用LEFT JOIN方式的施行方案:
![](/uploads/allimg/200930/37961f5f92f93ac2511c5cc140575d55-216416.png)
从施行方案来看,两个表都使用了索引,不同在于NOT EXISTS使用“DEPENDENT SUBQUERY”方式,而LEFT JOIN使用一般表关联的方式。
引荐看下:为什么索引能提高查询速度?
通过MySQL供给的Profiling方式来查看两种方式的施行历程。
使用NOT EXIST方式的施行历程:
![](/uploads/allimg/200930/37961f5f92f93ac2511c5cc140575d55-316420.png)
使用LEFT JOIN方式的施行历程:
![](/uploads/allimg/200930/aac1bccf1e3019e2c3a6ee9c3a0e2a7c-416429.png)
从施行历程来看,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子查询的施行次数即外层查询结果集的数据量。
当外层查询结果集的数据量N较小时施行机能较好,如有N=10施行时间为50μs*10=500us=0.005s,再加上一些额外耗损,施行结果也能在0.01秒或10毫秒内范畴,这个响应时间应当能被大部分利用程序接受。
当外层程勋结果集的数据量N较大乃至上千万数据量时,NOT EXISTS的查询机能会变得非常糟糕,乃至会大量耗损效劳器IO和CPU资源从而影响其他业务正常运转。
除上述问题外,在优化历程中发明本应当储备雷同数据的resource_id列在两个表中定义不一样,一表为VARCHAR而别的一表为BIGINT,外部结果集的字段类型和NOT EXIST字表中字段类型不一样致使NOT EXISTS子查询中没法使用索引,使得子查询机能较差,终究影响整个查询的施行机能。
京东商城也曾显现过大量相似案例,一些表使用VARCHAR来存置订单号,而另一些表使用BIGINT来存置,在两表停止治理时机能极差,但愿研发同事引认为戒。关注公众号Java技术栈回复m36猎取一份MySQL研发军规。
相关学习引荐:mysql视频教程
以上就是MySQL not exists 与索引的关系的具体内容,更多请关注百分百源码网其它相关文章!