MySQL not exists不走索引的實例分析

這篇文章給大家介紹MySQL not exists不走索引的實例分析，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

在一些業(yè)務(wù)場景中，會使用NOT EXISTS語句確保返回數(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í)行計劃！

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

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

從執(zhí)行計劃來看，兩個表都使用了索引，區(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一項上(1.2s)，而NOT EXISTS方式主要消耗在executeing和Sending data兩項上，受限于Profiling只存放100行記錄緣故。

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

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

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

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

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

除上述問題外，在優(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ī)。

關(guān)于MySQL not exists不走索引的實例分析就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。