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

建表驗證

如果有些朋友不信的話，那么我們來驗證一下，首先我們建一張表，然后寫入數據，腳本如下：

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;

圖片

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

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

我們插入兩條數據，接下來我們看看效果。

圖片

然后我們通過 LIKE 來進行查詢。

使用 LIKE '%xxx%'。

圖片

使用 LIKE 'xxx%'。

圖片

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

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

當我們遇到這個問題的時候，這個題目也是面試官比較喜歡問到的問題，百分號在左和在右的區(qū)別是哪里呢？

我們都知道在SQL查詢中，LIKE操作符用于模糊匹配字符串。通配符%在LIKE語句中的位置對查詢的性能和結果有著顯著的影響。當%位于LIKE語句的左邊、右邊或兩邊時，它們的行為和效果是不同的。

% 在左邊：

• 當%位于LIKE語句的左邊時，如LIKE '%keyword'，這種查詢模式通常會導致索引失效。因為數據庫系統(tǒng)需要掃描整個表或索引來查找包含指定關鍵詞的記錄，而不是利用索引的有序性來快速定位。這會導致查詢性能下降，尤其是在大型表中。
• 索引失效的原因是數據庫無法高效匹配這種模式。數據庫難以直接根據索引列的值來定位數據，而需要進行全表掃描或索引的全掃描。

% 在右邊：

• 當%位于LIKE語句的右邊時，如LIKE 'keyword%'，這種查詢模式通?？梢岳盟饕绻饕前凑諒淖蟮接业捻樞驑嫿ǖ模ㄈ绯Ｒ姷腂+樹索引）。數據庫系統(tǒng)可以從索引的起始位置開始，沿著索引的順序查找以指定關鍵詞開頭的記錄。
• 雖然索引可以幫助加速查詢，但是如果查詢中還有其他條件或限制，或者如果索引的選擇性較低（即索引中的值大多相同或重復），那么查詢性能可能仍然不如預期。

% 在兩邊：

• 當%同時位于LIKE語句的左邊和右邊時，如LIKE '%keyword%'，這種查詢模式通常也會導致索引失效。因為數據庫系統(tǒng)需要在整個表或索引中查找包含指定關鍵詞的記錄，而無法利用索引的有序性進行快速定位。
• 與%在左邊的情況類似，這種查詢模式需要進行全表掃描或索引的全掃描，從而導致查詢性能下降。

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

那么我們如何讓在左邊的百分號能命中索引，讓索引不失效呢？

我們可以這樣操作：

圖片

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

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

圖片

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

什么是覆蓋索引

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

在傳統(tǒng)的索引結構中，索引通常只包含被索引列的值和指向表中對應行的指針。當查詢需要從表中獲取數據時，數據庫引擎首先通過索引找到對應的指針，然后再根據這些指針去表中獲取實際的數據。這個過程被稱為“回表操作”，它增加了查詢的IO操作和數據傳輸的開銷，影響了查詢性能。

而覆蓋索引則避免了回表操作。因為覆蓋索引本身已經包含了查詢所需的所有列的值，所以數據庫引擎在查詢時可以直接從索引中獲取數據，無需再去訪問表。這樣就減少了IO操作和數據傳輸，大大提高了查詢效率。

要創(chuàng)建一個覆蓋索引，需要確保索引包含了查詢中WHERE子句和SELECT子句引用的所有列。這樣，當執(zhí)行查詢時，數據庫引擎就可以僅通過掃描索引來滿足查詢的需求，無需訪問表。

值得注意的是，雖然覆蓋索引可以提高查詢性能，但它也會增加數據庫的存儲空間和索引維護的開銷。因此，在設計和使用覆蓋索引時，需要權衡其帶來的性能提升和額外的存儲與維護成本。

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

總的來說，%在LIKE語句中的位置對查詢性能有著重要影響。為了提高查詢性能，盡量避免在LIKE語句的開始處使用%通配符，而是盡量將通配符放在查詢模式的末尾或中間位置。同時，合理設計和使用索引也是提高查詢性能的關鍵。

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