Mysql數據庫表中為什麼有索引卻沒有提高查詢速度

Posted on by

背景

時間過得太快瞭，春節假期感覺光速般就結束瞭，轉眼間就要繼續搬磚上班瞭。緊接著很快就要進入金三銀四的求職面試高峰期，程序猿小楓還沒有找到令自己感到滿意的工作。就算是在過年放假期間也在拼命的準備技術面試，這不他又梳理瞭下之前面試過程中面試官經常問到的關於數據庫方面的一道面試題，我們來一起幫小楓看看有沒有遺漏的地方吧。

面試題目——問題

面試官:看你的簡歷中有提到過曾經進行過索引優化的工作，那我就問問你，假設數據庫表中有索引，但是進行SQL數據查詢還是很慢，這種情況下應該怎麼分析查詢慢的原因？

分析

在進行數據庫查詢的時候，我們都知道索引可以加快數據查詢的效率。但是在實際的業務場景下，經常會遇到即使在表中增加瞭索引，但是同樣還是會出現數據查詢慢的問題。這就需要具體分析數據查詢慢的具體原因到底是什麼瞭。

首先需要進行確認的就是SQL語句中對應的條件查詢中字段有沒有建立索引。雖然面試官說瞭有索引，但是不一定SQL語句中的查詢字段有建立索引，所以第一步應該進行SQL中的字段索引確認。如果沒有建立對應的索引可以先嘗試下建立索引再進行查詢。如果已經有瞭索引，查詢的字段也是索引字段，那麼就要考慮下是不是出現瞭索引失效的情況。下面我們再具體分析下，看看在哪些場景下會出現索引失效的情況。

索引失效場景

在分析索引失效場景之前，我們必須要清楚索引結構的特點是什麼。關於Mysql的數據庫索引結構在之前的文章中已經進行瞭詳細的分析，可以參見之前的文章。

Mysql數據庫索引面試題(程序員基礎技能)

我們來看下Mysql數據庫索引的結構特點：

 這裡以user_info這張表來作為分析的基礎，在user_info這張表上，我們分別創建瞭idx_name以及idx_phone二級索引以及idx_age_address聯合索引。

CREATE TABLE IF NOT EXISTS `user_info` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) NOT NULL,
 `gender` int(2) NOT NULL,
 `age` int(10) NOT NULL,
 `phone_number` VARCHAR(20) NOT NULL,
 `address` VARCHAR(40) NOT NULL,
  PRIMARY KEY ( `id` ),
  KEY `idx_name`(`name`),
  KEY `idx_phone`(`phone_number`),
  KEY `idx_age_address`(`age`,`address`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 1、字段類型不匹配導致的索引失效

進行SQL數據查詢的時候，where條件字段類型與實際表中字段類型不匹配的時候，Mysql會進行隱式的數據類型轉換，而類型轉換會使用到內置函數，導致在進行數據查詢的時候並沒有使用索引。我們可以使用explain命令查看sql語句。可以看的出來在key欄中，對應的值為null，說明並沒有使用索引進行查詢。 

 但是如果在按照phone_number字段為字符串類型進行查詢的時候，Mysql沒有進行隱式的類型轉換，所以最終還是走瞭索引。

  • 2、被索引字段使用瞭表達式計算

在where中條件使用瞭條件表達式的時候，數據表中的索引就失效瞭，實際是因為Mysql需要將索引字段取出來之後再進行表達式的條件判斷，因而進行瞭全表掃描，導致索引失效。

  • 3、被索引字段使用瞭內置函數

索引字段實際上是依賴於整個B+索引樹的遍歷，而索引樹的遍歷又依賴於索引樹底層葉子節點的有序性。索引保存的是索引列的原始值，如果經過函數計算，Msql的解釋器無法判斷計算後的索引在原來的索引樹上是否可以被索引到，因此它就直接放棄使用索引查詢瞭。

  • 4、like使用瞭%X模糊匹配

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

由於B+樹按照索引值進行排序的，實際是按照最左前綴進行比較，而使用瞭%作為最左前綴，Mysql無法判斷其有序性，因此隻能進行全表掃描查詢。

  • 5、索引字段不是聯合索引字段的最左字段

如果數據庫表中有聯合索引的話，我們在SQL查詢語句中使用的索引字段又不是聯合索引的最左字段，那麼就會導致索引失效。

實際上在Mysql中的索引檢索是遵循最左匹配原則的，同時B+索引樹的葉子節點的有序性也是建立都在最左匹配原則之上，而上述的4、5兩種情況實際違反瞭最左匹配原則，因此Mysql執行器則無法使用對應的索引進行檢查查詢。

  • 6、or分割的條件，如果or左邊的條件存在索引，而右邊的條件沒有索引，不走索引

因為 OR 的含義就是兩個隻要滿足一個即可，因此隻有一個條件列進行瞭索引是沒有意義的，隻要有條件列沒有進行索引，就會進行全表掃描，因此索引的條件列也會失效。

  • 7、in、not in可能會導致索引失效

這裡需要說明的是使用in以及not in走不走索引，實際和Mysql的版本以及表中的數據量有關系，在8.0之後的版本是走索引的。

總結

本文根據面試官的存在索引但是出現數據查詢慢的問題為出發點，總結瞭幾種索引失效的場景，希望在大傢平時項目開發時遇到類似的問題可以有對應的問題排查方向。導致索引失效的場景歸結起來實際就是在索引使用上面存在瑕疵最終導致瞭索引失效的情況，這就像我們小時候打拳皇97一樣，遙感和按鈕的組合如果姿勢不對，就沒辦法放出我們希望的大招。小楓的求職面試之路還在繼續，我們一起期待下次小楓還會遇到怎樣的面試問題吧。

到此這篇關於Mysql數據庫表中為什麼有索引卻沒有提高查詢速度的文章就介紹到這瞭,更多相關Mysql 數據庫表索引內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：