1-引言：什么是MySQL的索引失效？(What、Why)

1-1 索引失效定義

• 在MySQL中，索引是用來(lái)加快檢索數(shù)據(jù)庫(kù)記錄的一種數(shù)據(jù)結(jié)構(gòu)。
• 索引失效指的是在進(jìn)行查詢操作時(shí)，本應(yīng)該使用索引來(lái)提升查詢效率的場(chǎng)景下，數(shù)據(jù)庫(kù)沒有利用索引，而是采用了全表掃描的方式，這會(huì)大大增加查詢時(shí)間和系統(tǒng)負(fù)擔(dān)。

1-2 為什么排查索引失效

排查索引失效的原因是至關(guān)重要的，主要有以下方面：

• 1. 提高查詢效率：索引的主要目的是加快數(shù)據(jù)檢索速度。當(dāng)索引失效時(shí)，數(shù)據(jù)庫(kù)系統(tǒng)可能退回到更慢的查詢方法，如全表掃描，這會(huì)顯著增加查詢時(shí)間和降低整體性能。
• 2. 降低服務(wù)器負(fù)載：使用索引可以顯著減少數(shù)據(jù)庫(kù)處理查詢所需處理的數(shù)據(jù)量，從而減少CPU使用率和IO讀寫。如果索引失效，數(shù)據(jù)庫(kù)必須加載更多數(shù)據(jù)，這會(huì)增加服務(wù)器的負(fù)載和資源消耗。

2- 索引失效的原因及排查（How）

2-1 索引失效的情況

• 以以下的學(xué)生信息表舉例

CREATE TABLE `student_info` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(50) NOT NULL,
  `student_age` int(11) DEFAULT NULL,
  `enrollment_date` datetime DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  UNIQUE KEY `student_name` (`student_name`),
  KEY `student_age` (`student_age`),
  KEY `enrollment_date` (`enrollment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• 表的索引情況：
• 總結(jié)來(lái)說(shuō)，表 student_info 有四個(gè)字段上定義了索引：
  • 一個(gè)主鍵索引 student_id
  • 一個(gè)唯一索引 student_name
  • 以及兩個(gè)普通索引 student_age 和 enrollment_date。

① 索引列參與計(jì)算

• 正常的通過 age 去做查詢
  • 走的是 student_age 的索引

explain select * from student_info where student_age=21;

• 如果索引列參與了計(jì)算進(jìn)行查詢
  • 索引失效

explain select * from student_info where student_age+1 =21;

• 如果不是對(duì)列進(jìn)行計(jì)算，而是對(duì)列等號(hào)右側(cè)的值進(jìn)行計(jì)算，結(jié)果還是走索引的。

② 對(duì)索引列進(jìn)行函數(shù)操作

• 正常的查詢——>走索引

explain select * from student_info where enrollment_date = '2022-09-04 08:00:00';

• 如果對(duì)查詢的字段加上函數(shù)操作時(shí)，索引失效

explain select * from student_info where YEAR(enrollment_date) = 2022;

③ 查詢中使用了 OR 兩邊有范圍查詢 > 或 <

• 正常情況查詢，查詢使用 student_name 索引

explain select * from student_info where student_name='Helen' and student_age>15;

• 如果使用了 OR 進(jìn)行查詢，兩邊包含范圍查詢 > 或 <
  • 此時(shí)索引失效

explain select * from student_info where student_name='Helen' or student_age>15;

• 如果沒有范圍查詢下使用 OR 還是正常走索引

explain select * from student_info where student_name='Helen' or student_age=18;

④ like 操作：以 % 開頭的 like 查詢

• 以 % 開頭的 LIKE 查詢比如 LIKE ‘%abc’;;

⑤ 不等于比較 !=

• 在MySQL中 != 比較有可能會(huì)導(dǎo)致不走索引，但如果對(duì) id 進(jìn)行 != 比較，是有可能走索引的。
• != 比較是否走索引，與索引的選擇、數(shù)據(jù)分布情況有關(guān)，不單是由于查詢包含 != 而引起的。

⑥ order by

• 如果使用 order by 時(shí)，表中的數(shù)據(jù)量很小，數(shù)據(jù)庫(kù)會(huì)直接在內(nèi)存中進(jìn)行排序，而不使用索引

⑦ 使用 IN

• 使用 IN 的時(shí)候，有可能走索引，也有可能不走索引。當(dāng)在 IN 的取值范圍比較大的時(shí)候有可能會(huì)導(dǎo)致索引失效，走全表掃描（NOT IN 和 IN的失效場(chǎng)景相同）。

2-2 索引失效的排查

使用 explain 排查

• 和 MySQL 慢查詢的排查類似，使用 Explain 語(yǔ)句來(lái)進(jìn)行排查。

需要關(guān)注的字段：type、key、extra

• 我們可以根據(jù) key、type、extra 來(lái)判斷一條語(yǔ)句是否走了索引。
• 一般走索引的情況 ：
  • key 值不為 null
  • type 值應(yīng)該為 ref、eq_ref、range、const 這幾個(gè)
  • extra 的話如果是 NULL，或者 using indedx，using index condition 都是可以的

索引失效情況

• 如果一條語(yǔ)句出現(xiàn)了 type 值為 all、key 為 null，extra = Using where 此時(shí)是索引失效了

此時(shí)就需要排查索引失效的原因

• 索引是否符合最左前綴匹配

• 查詢語(yǔ)句出現(xiàn)以上 7 種情況

3- 總結(jié)：索引失效知識(shí)點(diǎn)小結(jié)

MySQL中什么情況下會(huì)出現(xiàn)索引失效？如何排查索引失效？
回答
:::info
MySQL中索引失效的情況有

• 1. 比如聯(lián)合索引在查詢的過程中不符合最左前綴原則，此時(shí)聯(lián)合索引會(huì)失效
• 2. 查詢的語(yǔ)句 索引列 進(jìn)行計(jì)算，此時(shí)會(huì)使得索引失效
• 3. 查詢的語(yǔ)句 對(duì)索引列進(jìn)行了函數(shù)操作，比如利用了 **YEAR()** 函數(shù)
• 4. 查詢語(yǔ)句中 包含 **OR** ，且 OR 兩側(cè)有范圍查詢 也就是 **>** 或 **<** 此時(shí)索引會(huì)失效
• 5. 查詢語(yǔ)句中 使用了 **like** 且 **like** 中存在 以**%** 開頭的匹配，此時(shí)索引會(huì)失效
• 6. 查詢語(yǔ)句中 使用了 **!=** 進(jìn)行比較，但這種情況也和數(shù)據(jù)的分布情況有關(guān)系，
• 7. 當(dāng)數(shù)據(jù)表中的數(shù)據(jù)較少，使用 **order by** 的時(shí)候，可能會(huì)不走索引直接在內(nèi)存中進(jìn)行排序
• 8. 當(dāng)使用 **IN**** **時(shí)候取值范圍比較大的時(shí)候有可能會(huì)導(dǎo)致索引失效

索引失效的排查

• ① 使用 Explain 對(duì) SQL 語(yǔ)句進(jìn)行排查
• 需要 關(guān)注的字段有 **type**、**key**、**extra**
• 如果一條語(yǔ)句出現(xiàn)了 type 值為 all、key 為 null，extra = Using where 此時(shí)是索引失效了

此時(shí)就需要排查索引失效的原因，是否存在以上情況

以上就是MySQL索引失效的原因及問題排查的詳細(xì)內(nèi)容，更多關(guān)于MySQL索引失效的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論