MySQL索引失效原因以及SQL查詢語句不走索引原因詳解

2023-03-07 06:03:36

前言
1. 隱式的型別轉換，索引失效
2. 查詢條件包含 or，可能導致索引失效
3. like 萬用字元可能導致索引失效
4. 查詢條件不滿足聯合索引的最左匹配原則
5. 在索引列login_time上使用 mysql 的內建函數
6. 對索引列age進行列運算（如，+、-、*、/）, 索引不生效
7. 索引欄位age上使用（！= 或者 < >, not in），索引可能失效
8. 索引欄位上使用 is null， is not null，索引可能失效（查詢結果行數）
9. 左右join連線，關聯的欄位編碼格式不一樣
10. 索引自身失效
總結

前言

日常工作中索引失效原因很多，這個需要平時的日積月累，不斷學習，才能更正確的發揮索引的作用，下面簡單總結一些索引失效原因。

1. 隱式的型別轉換，索引失效

select * from test where num=13911111111; # 失效，num欄位是varchar型別，沒有加引號

假設某手機號列建立時是num varchar(15)

如果上面的手機號沒有加引號，查詢的時候是字串跟數位的比較，它們型別不匹配，MySQL 會做隱式的型別轉換，把它們轉換為浮點數再做比較。隱式的型別轉換，索引會失效。

2. 查詢條件包含 or，可能導致索引失效

select * from test where mul=1 or noidx=2; # 可能失效，當mul設為索引列而noidx不是索引列時

索引+or+無索引的列：會先走索引列，但無索引的列會進行全表掃描，所以還不如不走索引，直接都全表掃描完事。如果or前後都有索引，那麼可能走索引，也可能不走索引。

如果它一開始就走全表掃描，直接一遍掃描就完事。Mysql 優化器出於效率與成本考慮，遇到 or 條件，讓索引失效，看起來也合情合理。

用or連線的兩個含null索引欄位，不走索引。但是，單個索引含null欄位，是走索引的。

注意：如果 or 條件的列都加了索引，索引可能會走也可能不走，平時大家使用的時候，還是要注意一下這個 or，學會用 explain 分析。遇到不走索引的時候，考慮拆開兩條 SQL。

3. like 萬用字元可能導致索引失效

並不是用了 like 萬用字元，索引一定會失效，而是 like 查詢是以 % 開頭，才會導致索引失效。

4. 查詢條件不滿足聯合索引的最左匹配原則

MySQl 建立聯合索引時，會遵循最左字首匹配的原則，即最左優先。如果你建立一個（a,b,c）的聯合索引，相當於建立了 (a)、(a,b)、(a,b,c) 三個索引。

5. 在索引列login_time上使用 mysql 的內建函數

select * from user where DATE_ADD(login_time,INTERVAL 1 DAY) = '2022-11-08 00:00:00'; # 失效
select * from user where login_time = DATE_ADD('2022-11-08 00:00:00',INTERVAL 1 DAY); # 有效

6. 對索引列age進行列運算（如，+、-、*、/）, 索引不生效

select * from user where age-1 = 39; # 失效

7. 索引欄位age上使用（！= 或者 < >, not in），索引可能失效

select * from user where age != 18; # 有可能失效

其實這個也是跟 mySQL優化器有關，如果優化器覺得即使走了索引，還是需要掃描很多很多行的哈，它覺得不划算，不如直接不走索引。平時我們用！= 或者 < >，not in 的時候，要留點心眼。

8. 索引欄位上使用 is null， is not null，索引可能失效（查詢結果行數）

很多時候，是因為資料量問題，導致了 MySQL 優化器放棄走索引。同時，平時我們用 explain 分析 SQL 的時候，如果 type=range, 要注意一下哈，因為這個可能因為資料量問題，導致索引無效。

9. 左右join連線，關聯的欄位編碼格式不一樣

如user 表的 name 欄位編碼是 utf8mb4，而 user_job 表的 name 欄位編碼為 utf8。

10. 索引自身失效

雖然索引有自我維護的能力，但資料表內容修改和更新頻繁的情況下，也有可能索引失效，此時需要刪除索引，重新建立索引。

總結

關於索引失效原因有很多，以上也只是簡單介紹了一下，具體失效原因，還得去自己分析，具體方法就是SQL的執行計劃 EXPLAIN 關鍵字了。
Mysql提供了這個關鍵字讓我們優化索引，使查詢更快，分析優化器的表連線，使它採用最優的順序。使用這個 explain 關鍵字可以檢視查詢語句是否走索引了以及走了哪個索引。

# 命令列執行以下語句即可檢視查詢語句是否走了索引，在查詢語句最前面加上 explain 即可
mysql> explain select * from sampleInfo where agents = "XXX中心有限公司";

如下圖的 key 即表示該語句使用了索引 agents 。如果下圖 key 那裡的為NULL或者 type 那裡為ALL，則表示該語句沒有走索引，需要進行優化了。

到此這篇關於MySQL索引失效原因以及SQL查詢語句不走索引原因的文章就介紹到這了,更多相關MySQL索引失效原因內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL索引失效原因以及SQL查詢語句不走索引原因詳解

目錄

前言

1. 隱式的型別轉換，索引失效

2. 查詢條件包含 or，可能導致索引失效

3. like 萬用字元可能導致索引失效

4. 查詢條件不滿足聯合索引的最左匹配原則

5. 在索引列login_time上使用 mysql 的內建函數

6. 對索引列age進行列運算（如，+、-、*、/）, 索引不生效

7. 索引欄位age上使用（！= 或者 < >, not in），索引可能失效

8. 索引欄位上使用 is null， is not null，索引可能失效（查詢結果行數）

9. 左右join連線，關聯的欄位編碼格式不一樣

10. 索引自身失效

總結

熱門文章

MySQL索引失效原因以及SQL查詢語句不走索引原因詳解

目錄

前言

1. 隱式的型別轉換，索引失效

2. 查詢條件包含 or，可能導致索引失效

3. like 萬用字元可能導致索引失效

4. 查詢條件不滿足聯合索引的最左匹配原則

5. 在索引列login_time上使用 mysql 的內建函數

6. 對索引列age進行列運算（如，+、-、*、/）, 索引不生效

7. 索引欄位age上使用（！= 或者 < >, not in），索引可能失效

8. 索引欄位上使用 is null， is not null，索引可能失效 （查詢結果行數）

9. 左右join連線，關聯的欄位編碼格式不一樣

10. 索引自身失效

總結

熱門文章

8. 索引欄位上使用 is null， is not null，索引可能失效（查詢結果行數）