今天我們來(lái)聊一下索引失效的問(wèn)題，因?yàn)樵陂_(kāi)發(fā)的過(guò)程中，不光需要我們?nèi)?xiě)業(yè)務(wù)代碼，有時(shí)候還可能會(huì)接手一些別人的代碼，而別人寫(xiě)的一些 SQL ，可能有幾百行，甚至有時(shí)候遇到一些時(shí)間較久的系統(tǒng)，一個(gè)完整的 SQL 甚至可能比一個(gè)方法都長(zhǎng)，這時(shí)候，就會(huì)出現(xiàn)一種情況，需要你去優(yōu)化，而優(yōu)化的時(shí)候，我們就會(huì)首先從索引層面下手，這時(shí)候，我們就遇到了問(wèn)題了，比如我們做模糊查詢(xún)的時(shí)候，很多人的習(xí)慣是 LIKE '%xxxx%'，殊不知，這種雖然能滿(mǎn)足條件，但是缺忽略了索引，導(dǎo)致索引不生效，今天我們就來(lái)說(shuō)說(shuō)這個(gè)。

建表驗(yàn)證

如果有些朋友不信的話(huà)，那么我們來(lái)驗(yàn)證一下，首先我們建一張表，然后寫(xiě)入數(shù)據(jù)，腳本如下：

CREATE TABLE `user`  (
  `ID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `USER_NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `AGE` int(11) NULL DEFAULT NULL,
  `ADDRESS` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `IP` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `PHONE` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `EMAIL` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  INDEX `NAME_PHONE`(`USER_NAME`, `PHONE`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

圖片

同時(shí)我們給 USER_NAME 和 PHONE 建立了聯(lián)合索引，我們插入數(shù)據(jù)。

INSERT INTO `baseboot`.`user`(`ID`, `USER_NAME`, `AGE`, `ADDRESS`, `IP`, `PHONE`, `EMAIL`) VALUES ('28715757596573696', '測(cè)試人員', 12, '北京市海淀區(qū)', '127.0.0.1', '17788779981', '789911@163.com');
INSERT INTO `baseboot`.`user`(`ID`, `USER_NAME`, `AGE`, `ADDRESS`, `IP`, `PHONE`, `EMAIL`) VALUES ('28715757596573697', '開(kāi)發(fā)人員', 22, '北京市海淀區(qū)', '127.0.0.1', '17788779981', '789911@163.com');

我們插入兩條數(shù)據(jù)，接下來(lái)我們看看效果。

圖片

然后我們通過(guò) LIKE 來(lái)進(jìn)行查詢(xún)。

使用 LIKE '%xxx%'。

圖片

使用 LIKE 'xxx%'。

圖片

我們使用的是 EXPLAN 來(lái)分析我們的 SQL ，對(duì)比明顯，一個(gè)使用了我們創(chuàng)建的索引，另外一個(gè)沒(méi)有使用我們創(chuàng)建的索引，所以，為什么失效了呢？

為什么 LIKE '%xxxx%' 索引失效了？

當(dāng)我們遇到這個(gè)問(wèn)題的時(shí)候，這個(gè)題目也是面試官比較喜歡問(wèn)到的問(wèn)題，百分號(hào)在左和在右的區(qū)別是哪里呢？

我們都知道在SQL查詢(xún)中，LIKE操作符用于模糊匹配字符串。通配符%在LIKE語(yǔ)句中的位置對(duì)查詢(xún)的性能和結(jié)果有著顯著的影響。當(dāng)%位于LIKE語(yǔ)句的左邊、右邊或兩邊時(shí)，它們的行為和效果是不同的。

% 在左邊：

• 當(dāng)%位于LIKE語(yǔ)句的左邊時(shí)，如LIKE '%keyword'，這種查詢(xún)模式通常會(huì)導(dǎo)致索引失效。因?yàn)閿?shù)據(jù)庫(kù)系統(tǒng)需要掃描整個(gè)表或索引來(lái)查找包含指定關(guān)鍵詞的記錄，而不是利用索引的有序性來(lái)快速定位。這會(huì)導(dǎo)致查詢(xún)性能下降，尤其是在大型表中。
• 索引失效的原因是數(shù)據(jù)庫(kù)無(wú)法高效匹配這種模式。數(shù)據(jù)庫(kù)難以直接根據(jù)索引列的值來(lái)定位數(shù)據(jù)，而需要進(jìn)行全表掃描或索引的全掃描。

% 在右邊：

• 當(dāng)%位于LIKE語(yǔ)句的右邊時(shí)，如LIKE 'keyword%'，這種查詢(xún)模式通?？梢岳盟饕?，如果索引是按照從左到右的順序構(gòu)建的（如常見(jiàn)的B+樹(shù)索引）。數(shù)據(jù)庫(kù)系統(tǒng)可以從索引的起始位置開(kāi)始，沿著索引的順序查找以指定關(guān)鍵詞開(kāi)頭的記錄。
• 雖然索引可以幫助加速查詢(xún)，但是如果查詢(xún)中還有其他條件或限制，或者如果索引的選擇性較低（即索引中的值大多相同或重復(fù)），那么查詢(xún)性能可能仍然不如預(yù)期。

% 在兩邊：

• 當(dāng)%同時(shí)位于LIKE語(yǔ)句的左邊和右邊時(shí)，如LIKE '%keyword%'，這種查詢(xún)模式通常也會(huì)導(dǎo)致索引失效。因?yàn)閿?shù)據(jù)庫(kù)系統(tǒng)需要在整個(gè)表或索引中查找包含指定關(guān)鍵詞的記錄，而無(wú)法利用索引的有序性進(jìn)行快速定位。
• 與%在左邊的情況類(lèi)似，這種查詢(xún)模式需要進(jìn)行全表掃描或索引的全掃描，從而導(dǎo)致查詢(xún)性能下降。

使 LIKE '%xxx%' 索引生效的操作是什么樣子呢？

那么我們?nèi)绾巫屧谧筮叺陌俜痔?hào)能命中索引，讓索引不失效呢？

我們可以這樣操作：

圖片

也就是說(shuō)，我們需要把有索引的字段，放在最開(kāi)始的位置，并且盡量的精確索引的字段，而沒(méi)有其他的字段，這種情況也是可以命中索引的。

而這種情況下，其實(shí)就可以理解為是覆蓋索引，就是下圖的樣子。

圖片

那么，什么是覆蓋索引呢？

什么是覆蓋索引

覆蓋索引（Covering Index）是一種特殊的索引結(jié)構(gòu)，其設(shè)計(jì)初衷是為了提高查詢(xún)性能。與傳統(tǒng)的索引不同，覆蓋索引包含了查詢(xún)所需的所有列，因此，當(dāng)執(zhí)行查詢(xún)時(shí)，數(shù)據(jù)庫(kù)引擎可以直接從索引中獲取所需的數(shù)據(jù)，而無(wú)需回表去訪問(wèn)實(shí)際的表數(shù)據(jù)。

在傳統(tǒng)的索引結(jié)構(gòu)中，索引通常只包含被索引列的值和指向表中對(duì)應(yīng)行的指針。當(dāng)查詢(xún)需要從表中獲取數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)引擎首先通過(guò)索引找到對(duì)應(yīng)的指針，然后再根據(jù)這些指針去表中獲取實(shí)際的數(shù)據(jù)。這個(gè)過(guò)程被稱(chēng)為“回表操作”，它增加了查詢(xún)的IO操作和數(shù)據(jù)傳輸?shù)拈_(kāi)銷(xiāo)，影響了查詢(xún)性能。

而覆蓋索引則避免了回表操作。因?yàn)楦采w索引本身已經(jīng)包含了查詢(xún)所需的所有列的值，所以數(shù)據(jù)庫(kù)引擎在查詢(xún)時(shí)可以直接從索引中獲取數(shù)據(jù)，無(wú)需再去訪問(wèn)表。這樣就減少了IO操作和數(shù)據(jù)傳輸，大大提高了查詢(xún)效率。

要?jiǎng)?chuàng)建一個(gè)覆蓋索引，需要確保索引包含了查詢(xún)中WHERE子句和SELECT子句引用的所有列。這樣，當(dāng)執(zhí)行查詢(xún)時(shí)，數(shù)據(jù)庫(kù)引擎就可以?xún)H通過(guò)掃描索引來(lái)滿(mǎn)足查詢(xún)的需求，無(wú)需訪問(wèn)表。

值得注意的是，雖然覆蓋索引可以提高查詢(xún)性能，但它也會(huì)增加數(shù)據(jù)庫(kù)的存儲(chǔ)空間和索引維護(hù)的開(kāi)銷(xiāo)。因此，在設(shè)計(jì)和使用覆蓋索引時(shí)，需要權(quán)衡其帶來(lái)的性能提升和額外的存儲(chǔ)與維護(hù)成本。

總的來(lái)說(shuō)，覆蓋索引是一種有效的優(yōu)化手段，可以在某些情況下顯著提高SQL查詢(xún)的性能。然而，它的使用需要根據(jù)具體的查詢(xún)和數(shù)據(jù)庫(kù)環(huán)境進(jìn)行仔細(xì)的考慮和測(cè)試。

總的來(lái)說(shuō)，%在LIKE語(yǔ)句中的位置對(duì)查詢(xún)性能有著重要影響。為了提高查詢(xún)性能，盡量避免在LIKE語(yǔ)句的開(kāi)始處使用%通配符，而是盡量將通配符放在查詢(xún)模式的末尾或中間位置。同時(shí)，合理設(shè)計(jì)和使用索引也是提高查詢(xún)性能的關(guān)鍵。

所以，你知道為什么失效了么？