為什么SQL查詢以%開頭索引就失效了呢?

作者：了不起 2024-03-29 08:10:43

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

今天我們來聊一下索引失效的問題，因為在開發的過程中，不光需要我們去寫業務代碼，有時候還可能會接手一些別人的代碼，而別人寫的一些 SQL ，可能有幾百行，甚至有時候遇到一些時間較久的系統，一個完整的 SQL 甚至可能比一個方法都長，這時候，就會出現一種情況，需要你去優化，而優化的時候，我們就會首先從索引層面下手，這時候，我們就遇到了問題了，比如我們做模糊查詢的時候，很多人的習慣是 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 建立了聯合索引，我們插入數據。

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

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

圖片

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

使用 LIKE '%xxx%'。

圖片

使用 LIKE 'xxx%'。

圖片

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

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

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

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

% 在左邊：

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

% 在右邊：

當%位于LIKE語句的右邊時，如LIKE 'keyword%'，這種查詢模式通常可以利用索引，如果索引是按照從左到右的順序構建的（如常見的B+樹索引）。數據庫系統可以從索引的起始位置開始，沿著索引的順序查找以指定關鍵詞開頭的記錄。
雖然索引可以幫助加速查詢，但是如果查詢中還有其他條件或限制，或者如果索引的選擇性較低（即索引中的值大多相同或重復），那么查詢性能可能仍然不如預期。

% 在兩邊：

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

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

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

我們可以這樣操作：

圖片

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

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

圖片

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

什么是覆蓋索引

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

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

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

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

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

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

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

責任編輯：武曉燕來源： Java極客技術

索引失效 SQL