前言：

問題分析:

在進(jìn)行數(shù)據(jù)庫(kù)查詢的時(shí)候，我們都知道索引可以加快數(shù)據(jù)查詢的效率。但是在實(shí)際的業(yè)務(wù)場(chǎng)景下，經(jīng)常會(huì)遇到即使在表中增加了索引，但是同樣還是會(huì)出現(xiàn)數(shù)據(jù)查詢慢的問題。這就需要具體分析數(shù)據(jù)查詢慢的具體原因到底是什么了。

首先需要進(jìn)行確認(rèn)的就是 SQL 語句中對(duì)應(yīng)的條件查詢中字段有沒有建立索引。雖然說表中已經(jīng)有索引，但是不一定 SQL 語句中的查詢字段有建立索引，所以第一步應(yīng)該進(jìn)行 SQL 中的字段索引確認(rèn)。如果沒有建立對(duì)應(yīng)的索引可以先嘗試下建立索引再進(jìn)行查詢。如果已經(jīng)有了索引，查詢的字段也是索引字段，那么就要考慮下是不是出現(xiàn)了索引失效的情況。下面我們?cè)倬唧w分析下，看看在哪些場(chǎng)景下會(huì)出現(xiàn)索引失效的情況。

索引失效場(chǎng)景：

在分析索引失效場(chǎng)景之前，我們必須要清楚索引結(jié)構(gòu)的特點(diǎn)是什么。

我們?cè)賮砜聪?Mysql 數(shù)據(jù)庫(kù)索引的結(jié)構(gòu)特點(diǎn)：

本文以 user_info 這張表來作為分析的基礎(chǔ)，在 user_info 這張表上，我們分別創(chuàng)建了 idx_name 以及 idx_phone 二級(jí)索引以及 idx_age_address 聯(lián)合索引。

1、字段類型不匹配導(dǎo)致的索引失效

進(jìn)行 SQL 數(shù)據(jù)查詢的時(shí)候，where 條件字段類型與實(shí)際表中字段類型不匹配的時(shí)候，Mysql 會(huì)進(jìn)行隱式的數(shù)據(jù)類型轉(zhuǎn)換，而類型轉(zhuǎn)換會(huì)使用到內(nèi)置函數(shù)，導(dǎo)致在進(jìn)行數(shù)據(jù)查詢的時(shí)候并沒有使用索引。我們可以使用 explain 命令查看 sql 語句?？梢钥吹某鰜碓?key 欄中，對(duì)應(yīng)的值為 null，說明并沒有使用索引進(jìn)行查詢。

但是如果在按照 phone_number 字段為字符串類型進(jìn)行查詢的時(shí)候，Mysql 沒有進(jìn)行隱式的類型轉(zhuǎn)換，所以最終還是走了索引。

2、被索引字段使用了表達(dá)式計(jì)算

在 where 中條件使用了條件表達(dá)式的時(shí)候，數(shù)據(jù)表中的索引就失效了，實(shí)際是因?yàn)?Mysql 需要將索引字段取出來之后再進(jìn)行表達(dá)式的條件判斷，因而進(jìn)行了全表掃描，導(dǎo)致索引失效。

3、被索引字段使用了內(nèi)置函數(shù)

索引字段實(shí)際上是依賴于整個(gè) B+索引樹的遍歷，而索引樹的遍歷又依賴于索引樹底層葉子節(jié)點(diǎn)的有序性。索引保存的是索引列的原始值，如果經(jīng)過函數(shù)計(jì)算，Mysql 的解釋器無法判斷計(jì)算后的索引在原來的索引樹上是否可以被索引到，因此它就直接放棄使用索引查詢了。

4、like 使用了 %X 模糊匹配

使用左模糊匹配以及左右模糊匹配都會(huì)導(dǎo)致索引失效，但是使用右模糊匹配，還是可以走索引查詢的。

由于 B+樹按照索引值進(jìn)行排序的，實(shí)際是按照最左前綴進(jìn)行比較，而使用了 %作為最左前綴，Mysql 無法判斷其有序性，因此只能進(jìn)行全表掃描查詢。

5、索引字段不是聯(lián)合索引字段的最左字段

如果數(shù)據(jù)庫(kù)表中有聯(lián)合索引的話，我們?cè)?SQL 查詢語句中使用的索引字段又不是聯(lián)合索引的最左字段，那么就會(huì)導(dǎo)致索引失效。

實(shí)際上在 Mysql 中的索引檢索是遵循最左匹配原則的，同時(shí) B+索引樹的葉子節(jié)點(diǎn)的有序性也是建立在最左匹配原則之上，而上述的 4、5 兩種情況實(shí)際違反了最左匹配原則，因此 Mysql 執(zhí)行器則無法使用對(duì)應(yīng)的索引進(jìn)行檢查查詢。

6、or 分割的條件

如果 or 左邊的條件存在索引，而右邊的條件沒有索引，不走索引
因?yàn)?OR 的含義就是兩個(gè)只要滿足一個(gè)即可，因此只有一個(gè)條件列進(jìn)行了索引是沒有意義的，只要有條件列沒有進(jìn)行索引，就會(huì)進(jìn)行全表掃描，因此索引的條件列也會(huì)失效。

7、in、not in 可能會(huì)導(dǎo)致索引失效

這里需要說明的是使用 in 以及 not in 走不走索引，實(shí)際和 Mysql 的版本以及表中的數(shù)據(jù)量有關(guān)系，在 8.0 之后的版本是走索引的。

注：此處加了地址的索引。

總結(jié)

本文總結(jié)了幾種索引失效的場(chǎng)景，希望在大家平時(shí)項(xiàng)目開發(fā)時(shí)遇到類似的問題可以有對(duì)應(yīng)的問題排查方向。導(dǎo)致索引失效的場(chǎng)景歸結(jié)起來實(shí)際就是在索引使用上面存在瑕疵最終導(dǎo)致了索引失效的情況，這就像我們小時(shí)候打拳皇 97 一樣，遙感和按鈕的組合如果姿勢(shì)不對(duì)，就沒辦法放出我們希望的大招。總之需要一些經(jīng)驗(yàn)的積累，同時(shí)在寫完 SQL 的時(shí)候可以進(jìn)行執(zhí)行檢查，避免在線上出現(xiàn)索引失效的問題。

到此這篇關(guān)于為什么Mysql 數(shù)據(jù)庫(kù)表中有索引還是查詢慢的文章就介紹到這了,更多相關(guān)Mysql 查詢慢原理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論