在一些業(yè)務(wù)場景中，會使用NOT EXISTS語句確保返回?cái)?shù)據(jù)不存在于特定集合，部分同事會發(fā)現(xiàn)NOT EXISTS有些場景性能較差，甚至有些網(wǎng)上謠言說”NOT EXISTS不走索引”，哪對于NOT EXISTS語句，我們?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í)行時間29秒以上，優(yōu)化后1.2秒，優(yōu)化提升25倍。

NOT EXISTS真的不走索引么？

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

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

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

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

推薦看下：為什么索引能提高查詢速度？

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

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

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

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

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

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

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

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

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