作者：夜風_BLOG
來源：https://blog.csdn.net/guanfengliang1988/article/details/92814376
在一些業(yè)務場景中，會使用NOT EXISTS語句確保返回數(shù)據(jù)不存在于特定集合永丝，部分同事會發(fā)現(xiàn)NOT EXISTS有些場景性能較差锹漱，甚至有些網(wǎng)上謠言說”NOT EXISTS不走索引”，哪對于NOT EXISTS語句慕嚷，我們如何優(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方式進行優(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í)行計劃：
使用NOT EXIST方式的執(zhí)行計劃
使用LEFT JOIN方式的執(zhí)行計劃：
從執(zhí)行計劃來看澡谭，兩個表都使用了索引，區(qū)別在于NOT EXISTS使用DEPENDENT SUBQUERY方式损俭，而LEFT JOIN使用普通表關聯(lián)的方式蛙奖。
通過MySQL提供的Profiling方式來查看兩種方式的執(zhí)行過程
使用NOT EXIST方式的執(zhí)行過程
使用LEFT JOIN方式的執(zhí)行過程：
從執(zhí)行過程來看，LEFT JOIN方式的主要消耗在Sending data一項上(1.2s)杆兵，而NOT EXISTS方式主要消耗在executeing和Sending data兩項上雁仲，受限于Profiling只存放100行記錄緣故，從Profiling中只能看到47個” executeing和Sending data”的組合項(每個組合項約50us)琐脏，通過執(zhí)行計劃看出攒砖，外表t_monitor的數(shù)據(jù)量為578436行，忽略統(tǒng)計信息不準情況下日裙，使用NOT EXISTS方式應該會產(chǎn)生578436個” executeing和Sending data”的組合項吹艇，總計消耗時間=50μs*578436=28921800us=28.92s。
從上面執(zhí)行過程可以推斷出阅签，使用NOT EXISTS方式的執(zhí)行性能嚴重依賴于NOT EXISTS子查詢的執(zhí)行次數(shù)即外層查詢結果集的數(shù)據(jù)量掐暮。
當外層查詢結果集的數(shù)據(jù)量N較小時執(zhí)行性能較好，如有N=10執(zhí)行時間為50μs*10=500us=0.005s政钟，再加上一些額外消耗路克，執(zhí)行結果也能在0.01秒或10毫秒內范圍，這個響應時間應該能被大部分應用程序接受养交。
當外層程勛結果集的數(shù)據(jù)量N較大甚至上千萬數(shù)據(jù)量時精算，NOT EXISTS的查詢性能會變得非常糟糕，甚至會大量消耗服務器IO和CPU資源從而影響其他業(yè)務正常運行碎连。
除上述問題外灰羽，在優(yōu)化過程中發(fā)現(xiàn)本應該存儲相同數(shù)據(jù)的resource_id列在兩個表中定義不同，一表為VARCHAR而另外一表為BIGINT，外部結果集的字段類型和NOT EXIST字表中字段類型不同導致NOT EXISTS子查詢中無法使用索引廉嚼，使得子查詢性能較差玫镐，最終影響整個查詢的執(zhí)行性能。京東商城也曾出現(xiàn)過大量類似案例怠噪，一些表使用VARCHAR來存放訂單號恐似，而另一些表使用BIGINT來存放，在兩表進行管理時性能極差傍念，希望研發(fā)同事引以為戒矫夷。
