mysql exits 不走索引,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子查询的实行次数即外层查询效果集的数据量。

当外层查询效果集的数据量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研发军规。