分析MySQL鐘notexists與索引的關(guān)系

在一些業(yè)務(wù)場(chǎng)景中，會(huì)使用NOT EXISTS語(yǔ)句確保返回?cái)?shù)據(jù)不存在于特定集合，部分同事會(huì)發(fā)現(xiàn)NOT EXISTS有些場(chǎng)景性能較差，甚至有些網(wǎng)上謠言說(shuō)”NOT EXISTS不走索引”，哪對(duì)于NOT EXISTS語(yǔ)句，我們?nèi)绾蝺?yōu)化呢？

以今天優(yōu)化的SQL為例，優(yōu)化前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方式進(jìn)行優(yōu)化，優(yōu)化后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

優(yōu)化效果：

優(yōu)化前執(zhí)行時(shí)間29秒以上，優(yōu)化后1.2秒，優(yōu)化提升25倍。

NOT EXISTS真的不走索引么？

查看兩種SQL的執(zhí)行計(jì)劃！

使用NOT EXIST方式的執(zhí)行計(jì)劃：

使用LEFT JOIN方式的執(zhí)行計(jì)劃：

從執(zhí)行計(jì)劃來(lái)看，兩個(gè)表都使用了索引，區(qū)別在于NOT EXISTS使用“DEPENDENT SUBQUERY”方式，而LEFT JOIN使用普通表關(guān)聯(lián)的方式。

推薦看下：為什么索引能提高查詢(xún)速度？

通過(guò)MySQL提供的Profiling方式來(lái)查看兩種方式的執(zhí)行過(guò)程。

使用NOT EXIST方式的執(zhí)行過(guò)程：

使用LEFT JOIN方式的執(zhí)行過(guò)程：

從執(zhí)行過(guò)程來(lái)看，LEFT JOIN方式的主要消耗在Sending data一項(xiàng)上(1.2s)，而NOT EXISTS方式主要消耗在executeing和Sending data兩項(xiàng)上，受限于Profiling只存放100行記錄緣故。

從Profiling中只能看到47個(gè)” executeing和Sending data”的組合項(xiàng)(每個(gè)組合項(xiàng)約50us)，通過(guò)執(zhí)行計(jì)劃看出，外表t_monitor的數(shù)據(jù)量為578436行，忽略統(tǒng)計(jì)信息不準(zhǔn)情況下，使用NOT EXISTS方式應(yīng)該會(huì)產(chǎn)生578436個(gè)” executeing和Sending data”的組合項(xiàng)，總計(jì)消耗時(shí)間=50μs*578436=28921800us=28.92s。

從上面執(zhí)行過(guò)程可以推斷出：

使用NOT EXISTS方式的執(zhí)行性能?chē)?yán)重依賴(lài)于NOT EXISTS子查詢(xún)的執(zhí)行次數(shù)即外層查詢(xún)結(jié)果集的數(shù)據(jù)量。

1. 當(dāng)外層查詢(xún)結(jié)果集的數(shù)據(jù)量N較小時(shí)執(zhí)行性能較好，如有N=10執(zhí)行時(shí)間為50μs*10=500us=0.005s，再加上一些額外消耗，執(zhí)行結(jié)果也能在0.01秒或10毫秒內(nèi)范圍，這個(gè)響應(yīng)時(shí)間應(yīng)該能被大部分應(yīng)用程序接受。

2. 當(dāng)外層程勛結(jié)果集的數(shù)據(jù)量N較大甚至上千萬(wàn)數(shù)據(jù)量時(shí)，NOT EXISTS的查詢(xún)性能會(huì)變得非常糟糕，甚至?xí)罅肯?a title="服務(wù)器" target="_blank" >服務(wù)器IO和CPU資源從而影響其他業(yè)務(wù)正常運(yùn)行。

除上述問(wèn)題外，在優(yōu)化過(guò)程中發(fā)現(xiàn)本應(yīng)該存儲(chǔ)相同數(shù)據(jù)的resource_id列在兩個(gè)表中定義不同，一表為VARCHAR而另外一表為BIGINT，外部結(jié)果集的字段類(lèi)型和NOT EXIST字表中字段類(lèi)型不同導(dǎo)致NOT EXISTS子查詢(xún)中無(wú)法使用索引，使得子查詢(xún)性能較差，最終影響整個(gè)查詢(xún)的執(zhí)行性能。

京東商城也曾出現(xiàn)過(guò)大量類(lèi)似案例，一些表使用VARCHAR來(lái)存放訂單號(hào)，而另一些表使用BIGINT來(lái)存放，在兩表進(jìn)行管理時(shí)性能極差，希望研發(fā)同事引以為戒。關(guān)注公眾號(hào)Java技術(shù)?；貜?fù)m36獲取一份MySQL研發(fā)軍規(guī)。