在 MySQL 中，InnoDB 是最常用的存儲引擎，它支持事務、行級鎖和外鍵約束等功能，而索引則是提升數據庫查詢性能的關鍵。在 InnoDB 存儲引擎中，索引不僅僅是提高查詢速度的工具，還是數據庫的核心組成部分之一。本文將詳細介紹 InnoDB 存儲引擎的索引結構、索引種類、索引優化技巧以及索引失效等方面的知識。
1. InnoDB 索引的結構
在 InnoDB 存儲引擎中，索引主要分為兩種類型：聚集索引（Clustered Index）和非聚集索引（Non-clustered Index）。
1.1 聚集索引（Clustered Index）
- 聚集索引的葉子節點存儲的是數據行本身。換句話說，數據行的實際數據存儲在索引結構中，索引的順序就是數據的物理存儲順序。
- 每個表只能有一個聚集索引，通常是主鍵索引。
- 聚集索引通過將表的數據按索引順序存儲，從而提高了對主鍵字段查詢的效率。
1.2 非聚集索引（Non-clustered Index）
- 非聚集索引的葉子節點并不存儲數據行本身，而是存儲數據行的指針（通常是聚集索引的主鍵值）。因此，查詢時需要通過指針再次訪問數據行。
- 一個表可以有多個非聚集索引。
1.3 InnoDB 索引的 B+ 樹結構
InnoDB 存儲引擎中的聚集索引和非聚集索引通常都是基于 B+ 樹（自平衡的樹狀數據結構）來實現的。B+ 樹有以下特點：
- 平衡性：所有葉子節點都位于同一層，因此查詢的時間復雜度是 O(log N)。
- 有序性：葉子節點之間按照大小順序排列，可以進行范圍查詢。
- 高效的插入和刪除操作：通過自平衡機制，插入和刪除操作能保持樹的平衡。
2. InnoDB 索引的類別
InnoDB 支持以下幾種類型的索引：
2.1 主鍵索引（Primary Key Index）
- 主鍵索引是一種特殊的聚集索引，用來確保表中每一行的唯一性。InnoDB 表默認使用主鍵作為聚集索引。
- 如果沒有顯式定義主鍵，InnoDB 會自動選定一個唯一索引作為主鍵。
2.2 唯一索引（Unique Index）
- 唯一索引保證索引列的值是唯一的，但允許 NULL 值存在（多個 NULL 值也被視為不同的值）。
- 唯一索引可以用于加速查詢。
2.3 普通索引（Index）
- 普通索引是最常見的索引類型，它沒有唯一性約束，僅用于提高查詢效率。
- 可以用于加速查詢，但不保證數據唯一性。
2.4 全文索引（Fulltext Index）
- 全文索引是專門用于對文本字段進行全文檢索的索引類型。通常用于查找包含某個關鍵詞的文本數據。
- 只適用于 CHAR、VARCHAR 和 TEXT 類型的列。
2.5 空間索引（Spatial Index）
- 空間索引主要用于對空間數據類型（如
GEOMETRY）進行查詢優化。它使用的是 R 樹（Region Tree）而不是 B+ 樹。
3. 索引的最左前綴原則
InnoDB 索引遵循 最左前綴原則。也就是說，當你使用復合索引（由多個列組成的索引）時，索引的查詢可以使用到最左側的一部分索引。
舉個例子：
假設有一個復合索引 (a, b, c)，那么查詢時，可以利用以下的前綴索引：
(a)：只使用列a，是有效的。(a, b)：使用列a和列b，是有效的。(a, b, c)：使用列a、b和c，是有效的。
但如果你只使用 (b) 或 (c) 作為查詢條件，MySQL 將無法使用這個復合索引，因為它沒有按照最左前綴的順序來查詢。
4. 索引覆蓋（Covering Index）
索引覆蓋是指查詢中涉及到的所有列都包含在索引中，從而避免了對表數據的訪問。換句話說，查詢的數據完全通過索引獲取，無需回表操作。
舉個例子：
假設有一個復合索引 (a, b, c)，并且你執行了如下查詢：
SELECT a, b FROM table WHERE a = 1;
如果索引 (a, b, c) 已經包含了查詢所需的列 a 和 b，那么 MySQL 可以直接從索引中獲取數據，而不需要回到表中去查詢。
索引覆蓋的好處是能顯著提高查詢性能，尤其是在大數據量的表中。
5. 索引下推（Index Condition Pushdown，ICP）
索引下推是一種優化技術，它能夠將查詢條件推送到存儲引擎的索引掃描階段，而不是等到讀取數據行時再進行過濾。這樣可以減少需要讀取的數據行數量，提升查詢效率。
- 在執行查詢時，MySQL 會盡可能地將條件應用到索引掃描的階段，而不是僅僅依賴于后續的行級過濾。
例如，
SELECT * FROM Employees WHERE age > 30 AND salary like '%5000'其中聯合索引（age、salary）;
沒有索引下推時，執行這條語句的流程：
-
1、存儲引擎使用聯合索引查出age>30的二級索引數據（葉子節點中有age、salary、主鍵）；
-
2、拿到主鍵回表，到聚簇索引中拿到完整記錄；
-
3、將所有的完整記錄返回到server層（服務器層），再進行salary的模糊查詢。
開啟索引下推后，執行流程：
-
1、存儲引擎使用聯合索引查出age>30的二級索引數據（葉子節點中有age、salary、主鍵）；
-
2、直接在二級索引數據中對salary進行模糊查詢。
可以看出索引下推之后減少了回表的次數，從而降低了查詢的時間。
6. 索引合并（Index Merge）
索引合并是 MySQL 在某些情況下使用的一個優化技術。當查詢條件涉及多個索引時，MySQL 會嘗試將多個索引的結果合并起來，從而加快查詢速度。
舉個例子：
假設有兩個索引：idx_a 和 idx_b，查詢條件是：
SELECT * FROM table WHERE a = 1 OR b = 2;
MySQL 可以使用索引合并策略，首先分別從 idx_a 和 idx_b 中找到符合條件的記錄，然后將它們合并，最后返回結果。
7. 索引失效的情況
索引并不是總能在所有情況下發揮作用。在以下情況下，索引可能會失效：
7.1 使用了不等于操作符（!=、<>）
SELECT * FROM table WHERE a != 1;
索引無法有效利用，因為不等于操作會導致掃描整個數據集。
7.2 使用了 OR 連接多個條件
SELECT * FROM table WHERE a = 1 OR b = 2;
當查詢條件中包含多個列的 OR 時，索引可能無法有效地優化查詢，尤其是當 OR 連接的列沒有單獨建立索引時。
7.3 使用函數
SELECT * FROM table WHERE YEAR(date_column) = 2023;
在where語句中使用函數（如 YEAR()）會導致索引失效，因為索引是基于列值進行查找的，函數的使用會改變查詢模式。
7.4 LIKE 前綴不匹配
SELECT * FROM table WHERE name LIKE '%abc';
如果 LIKE 查詢以通配符 % 開頭，索引通常無法被使用，因為 MySQL 無法利用前綴進行快速查找。
8. 如何查看索引
可以通過以下方式查看 MySQL 表的索引信息：
SHOW INDEX FROM table_name;
該語句將列出表 table_name 中所有的索引，包括索引的名稱、類型、涉及的列等信息。
也可以使用explain關鍵字查看一條SQL的執行計劃：
EXPLAIN SELECT * FROM users WHERE age > 30;
結果如下：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|
| 1 | SIMPLE | users | range | idx_age | idx_age | 4 | NULL | 2 | Using where |
這個執行計劃的解釋為：
- id: 查詢的唯一標識。這里的
1表示這是一個簡單查詢。 - select_type: 查詢的類型。
SIMPLE表示這是一個簡單的查詢（沒有子查詢）。 - table: 表名。這里查詢的是
users表。 - type: 這里的
range表示 MySQL 使用了范圍掃描（age > 30是一個范圍條件），這是比ALL更高效的掃描類型。 - possible_keys: MySQL 能夠使用的索引。這里沒有索引可用（
NULL）。 - key: 使用了
idx_age索引，意味著查詢能夠利用索引進行更高效的檢索。 - key_len: 使用的索引的長度。
- ref: 如果使用了索引，它將顯示與哪個列比較。
- rows: 預估掃描了 2 行，MySQL 使用了索引來減少掃描的行數。
- Extra: 附加信息。
Using where表示在掃描每一行時，MySQL 使用了WHERE子句的條件進行過濾。
9. 索引優化建議
- 選擇合適的索引類型：根據查詢的特點選擇合適的索引類型。如果查詢需要精確匹配，可以選擇唯一索引；如果查詢涉及文本搜索，可以選擇全文索引。
- 避免過度索引：索引雖然提高查詢效率，但會增加寫操作的開銷。需要在查詢性能和寫操作性能之間做出平衡。
- 創建復合索引：對于多個列的查詢，可以考慮創建復合索引，而不是對每個單獨列都創建索引。
- 避免索引失效的情況：盡量避免在查詢中使用
LIKE、OR、!=等會導致索引失效的操作。 - 定期分析和優化索引：通過
EXPLAIN語句來分析查詢的執行計劃，檢查索引的使用情況，并根據結果調整索引設計。
