一、什么是索引？

索引（Index） 是數據庫管理系統中一種特殊的數據結構，存儲在磁盤上。它包含對數據表中一列或多列的值進行排序，并存儲了指向表中實際數據行物理位置或主鍵值的引用指針。可以把它類比為書籍的目錄，能夠幫助數據庫系統快速定位到符合查詢條件的數據，而無需掃描整個數據表。

二、索引的作用

1. 快速定位與檢索數據： 這是索引最核心的作用。通過索引，數據庫可以避免全表掃描，直接跳轉到目標數據所在的位置，極大地縮短數據查詢的時間。
2. 提高數據庫性能： 通過加速查詢操作，索引能夠有效提升數據庫的整體性能，尤其是對于讀密集型的應用。
3. （間接作用）保證數據的唯一性： 唯一索引可以確保索引列中的所有值都是唯一的。
4. （間接作用）加速表連接： 對用于連接的列（如外鍵）創建索引，可以顯著提高表連接的效率。
5. （間接作用）加速排序和分組： 如果 ORDER BY 或 GROUP BY 子句中的列有索引，MySQL 可以利用索引的有序性來避免額外的排序操作。

三、索引的缺點

雖然索引能帶來巨大的性能提升，但它并非沒有代價：

1. 空間消耗： 索引本身也需要占用磁盤空間。數據量越大，索引占用的空間也越大。這是一種典型的“以空間換時間”的策略。對于大多數后端應用而言，磁盤成本相對較低，通常可以接受。
2. 影響寫操作效率（增、刪、改）：
   • 當對表中的數據進行 INSERT、DELETE、UPDATE 操作時，數據庫不僅要修改數據本身，還需要同步維護索引結構（例如，B+樹的節點分裂、合并、鍵值調整等）。
   • 這通常會導致寫操作的效率降低。雖然在極少數特定更新場景下影響可能不明顯，但總體趨勢是寫操作的開銷會增加。

權衡利弊： 盡管存在上述缺點，但在實際開發中，索引的使用仍然是必不可少的。主要原因有：

1. 磁盤空間通常不是主要矛盾，其成本遠低于因查詢緩慢導致的用戶體驗下降或系統資源浪費。
2. 在大多數應用場景中，讀操作（查詢）的頻率遠高于寫操作。因此，通過索引提升查詢性能帶來的整體效益往往遠大于其對寫操作的輕微影響。

四、何時使用索引？（使用場景）

在決定是否對數據表的某一列或多列創建索引時，需要綜合考慮以下幾點：

適合創建索引的場景：

• 數據量較大的表： 對于小表，全表掃描可能比走索引更快。索引的優勢在大表上才能充分體現。
• 經常作為查詢條件的列： 即經常出現在 WHERE 子句中的列。
• 經常用于表連接的列： 通常是外鍵列。
• 經常需要排序的列： 即經常出現在 ORDER BY 子句中的列。如果排序方向與索引一致，效果更佳。
• 經常需要分組統計的列： 即經常出現在 GROUP BY 子句中的列。
• 高基數性（高區分度）的列： 列中不同值的數量越多，索引的選擇性就越好，查詢效率提升越明顯。

不適合或需謹慎創建索引的場景：

• 數據量非常小的表： 全表掃描效率可能更高，索引反而增加開銷。
• 寫操作遠多于讀操作的表： 維護索引的代價可能會超過查詢帶來的收益。
• 低基數性的列： 列中只有很少的唯一值（例如：性別列，只有男、女、未知）。索引選擇性差，優化效果不明顯，還浪費空間。
• 很少被查詢或引用的列： 創建索引沒有意義。
• 經常被修改的列： 如果某列的值頻繁更新，那么維護該列索引的代價會比較大。
• 過長的文本字段： 對非常長的文本字段直接創建完整索引會占用大量空間且效率不高，可以考慮使用前綴索引或全文索引。

五、使用

1. 查看索引

SHOW INDEX FROM 表名;

或者查看建表語句，其中也包含了索引信息：

SHOW CREATE TABLE 表名;

2. 創建索引

MySQL 支持多種類型的索引，創建語法略有不同。

普通索引（INDEX）： 最基本的索引，沒有任何限制。

CREATE INDEX 索引名 ON 表名(字段名1, 字段名2, ...);
-- 或者
ALTER TABLE 表名 ADD INDEX 索引名 (字段名1, 字段名2, ...);

字段名1, 字段名2, … 表示可以創建復合索引（聯合索引）。

唯一索引（UNIQUE INDEX）： 索引列的值必須唯一，但允許有空值（NULL）。如果是復合索引，則列值的組合必須唯一。

CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
-- 或者
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (字段名);
-- 或者在定義表時指定
-- CREATE TABLE 表名 (
--     id INT PRIMARY KEY,
--     email VARCHAR(100) UNIQUE,
--     ...
-- );

主鍵索引（PRIMARY KEY）： 一種特殊的唯一索引，不允許有空值。一張表只能有一個主鍵索引。

-- 通常在創建表時定義
CREATE TABLE 表名 (id INT PRIMARY KEY,...
);
-- 或者通過 ALTER TABLE 添加
ALTER TABLE 表名 ADD PRIMARY KEY (字段名);

創建主鍵約束時，會自動創建對應列的主鍵索引。

外鍵約束（FOREIGN KEY）：
當創建外鍵約束時，MySQL 也會自動在引用列上創建索引（如果尚不存在），以提高連接和約束檢查的效率。

全文索引（FULLTEXT INDEX）： 用于對文本內容進行全文搜索，通常用于 CHAR, VARCHAR, TEXT 類型的列。

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名);
-- 或者
ALTER TABLE 表名 ADD FULLTEXT (字段名);

刪除索引

DROP INDEX 索引名 ON 表名;

或者：

ALTER TABLE 表名 DROP INDEX 索引名;

對于主鍵索引，刪除方式特殊：

ALTER TABLE 表名 DROP PRIMARY KEY;

六、索引背后的數據結構

為什么數據庫索引普遍選擇 B+ 樹而不是其他數據結構（如哈希表、二叉搜索樹）呢？

1. 為什么不選擇哈希表？
哈希表通過哈希函數將鍵映射到存儲位置，理論上等值查詢（= 或 IN）的時間復雜度可以達到 O(1)，非常快。但是：

• 不支持范圍查詢或順序訪問： 哈希表存儲的數據是無序的，無法高效處理 >、<、BETWEEN 等范圍查詢，也無法支持 ORDER BY 操作。
• 不支持模糊查詢： 無法直接支持 LIKE ‘keyword%’ 這樣的模糊查詢。
• 哈希沖突問題： 當發生哈希沖突時，需要額外的機制（如鏈地址法）來解決，這會降低查詢效率。
• 內存數據庫更適用： MySQL 的 Memory 存儲引擎支持哈希索引，因為它主要在內存中操作，但對于磁盤存儲引擎（如 InnoDB, MyISAM），磁盤 I/O 是主要瓶頸，哈希表的優勢不明顯。

2. 為什么不選擇二叉搜索樹（BST）或平衡二叉搜索樹（AVL樹、紅黑樹）？
二叉搜索樹（及其平衡變種）能夠保持數據有序，支持范圍查詢。但是：

• 樹高問題導致磁盤 I/O 過多： 當數據量非常大時，即使是平衡二叉樹，其高度也會相對較高。數據庫索引
• 通常存儲在磁盤上，每次訪問樹的一個節點都可能對應一次磁盤 I/O 操作。樹越高，I/O 次數就越多，查詢效率越低。
• 節點存儲信息量少： 每個節點只存儲一個鍵值和兩個子節點指針，對于磁盤存儲來說，這沒有充分利用磁盤預讀（通常一次 I/O 會讀取一頁或一塊數據）的特性。

3. B+ 樹：為數據庫索引量身定做
MySQL（尤其是 InnoDB 存儲引擎）普遍采用 B+ 樹 作為索引的數據結構。

B 樹（B-Tree）簡介： B 樹是一種自平衡的 N 叉搜索樹（N 通常遠大于 2）。它的特點是每個節點可以存儲多個鍵值和多個指向子節點的指針，從而降低樹的高度。

B+ 樹的改進： B+ 樹是在 B 樹的基礎上進行優化的，更適合數據庫索引。

B+樹示例

圖示：一個簡化的 B+ 樹示例。非葉子節點存儲索引 key，作為導航；葉子節點存儲所有 key 并形成有序雙向鏈表，可能直接存儲數據（聚簇索引）或指向數據的指針（非聚簇索引）。

B+ 樹的特點：

1. N 叉搜索樹： 每個節點可以包含多個關鍵字（通常遠大于2），這使得樹的階數（fan-out）很高，從而樹的高度非常低。

2. 非葉子節點冗余關鍵字： 非葉子節點只存儲關鍵字（索引）和指向下一級子節點的指針，不存儲實際數據。這些關鍵字會作為其對應子樹中葉子節點所管理區間的最大（或最小）值。

3. 所有數據（或指向數據的指針）都在葉子節點： 所有的查詢最終都會落到葉子節點。葉子節點包含了所有索引鍵值。

   • 對于聚簇索引（Clustered Index，如 InnoDB 的主鍵索引），葉子節點直接存儲完整的數據行。
   • 對于非聚簇索引（Secondary/Non-Clustered Index，如 InnoDB 的輔助索引或 MyISAM 的索引），葉子節點存儲的是索引鍵值和指向實際數據行的指針（InnoDB 輔助索引存的是主鍵值，MyISAM 存的是數據文件的物理地址）。

4. 葉子節點通過雙向鏈表連接： 所有葉子節點按照鍵值順序被串聯起來，形成一個有序的雙向鏈表。
   B+ 樹的優勢：

5. 極低的樹高，減少磁盤 I/O： 由于 N 叉的特性，即使存儲大量數據，B+ 樹的高度也非常低（通常 3-4 層就能支持千萬級別的數據）。這意味著從根節點到葉子節點的路徑很短，大大減少了磁盤 I/O 次數。

6. 高效的范圍查詢和排序： 葉子節點通過雙向鏈表連接，使得范圍查詢（如 BETWEEN, >, <）和排序（ORDER BY）操作非常高效，只需在葉子節點層進行順序掃描。

7. 穩定的查詢性能： 任何一次查詢（無論是單點查詢還是范圍查詢的起點定位）都需要從根節點走到某個葉子節點，查詢路徑長度基本一致，IO 次數穩定。

8. 非葉子節點更小，利于緩存： 非葉子節點不存儲實際數據行，只存儲索引鍵和指針，因此它們占用的空間較小。這使得更多的非葉子節點可以被加載到內存中進行緩存，進一步減少了磁盤 I/O。

七、MySQL 常見索引類型詳解

1. 聚簇索引 (Clustered Index)

• 定義： 數據行的物理存儲順序與索引順序一致的索引。一張表只能有一個聚簇索引。
• InnoDB：
  • InnoDB 表是索引組織表，數據本身就是按照聚簇索引的順序存儲的。
  • 如果你定義了 PRIMARY KEY，那么主鍵就是聚簇索引。
  • 如果你沒有定義 PRIMARY KEY，InnoDB 會選擇第一個 UNIQUE NOT NULL 的索引作為聚簇索引。
  • 如果兩者都沒有，InnoDB 會自動生成一個隱藏的 6 字節的 ROWID 作為聚簇索引。
• 優點： 基于聚簇索引的查詢速度非常快，因為可以直接定位到數據行。
• 缺點：
  • 維護成本較高，插入新行或主鍵更新（不推薦）可能導致數據頁分裂。
  • 輔助索引查找需要兩次索引查找（先查到主鍵值，再通過主鍵值查數據行），也稱為“回表”。

2. 非聚簇索引 (Secondary / Non-Clustered Index)

• 定義： 數據行的物理存儲順序與索引順序不一致。一張表可以有多個非聚簇索引。
• InnoDB： 輔助索引（除主鍵索引外的其他索引）都是非聚簇索引。其葉子節點存儲的是索引列的值和對應行的數據的主鍵值。
• MyISAM： MyISAM 表的所有索引（包括主鍵索引）都是非聚簇索引。其葉子節點存儲的是索引列的值和對應行的數據在磁盤上的物理地址（指針）。
• 優點： 維護成本相對較低。
• 缺點： 查詢時可能需要“回表”操作（除非是覆蓋索引）。

3. 覆蓋索引 (Covering Index)

• 定義： 如果一個索引包含了查詢語句中 SELECT、WHERE、ORDER BY、GROUP BY 所需的所有列，那么數據庫引擎就可以直接從該索引中獲取所有數據，而無需再訪問數據表本身（即無需“回表”）。這種索引就稱為覆蓋索引。
• 優點： 極大地提高了查詢性能，避免了回表操作的額外 I/O。
• 示例： 表 users 有 (name, age) 的聯合索引。查詢 SELECT name, age FROM users WHERE name = 'Alice'; 就可以使用覆蓋索引。

4. 聯合索引 (Composite / Compound Index) 與最左前綴匹配原則

• 定義： 對表上的多個列組合起來創建的索引。
• 最左前綴匹配原則： 這是使用聯合索引時非常重要的一個原則。當查詢條件使用了聯合索引中的最左邊連續的若干個列時，索引才會被有效利用。
  • 例如，有一個聯合索引 idx_abc (a, b, c)：
    • WHERE a = 1：可以用到索引。
    • WHERE a = 1 AND b = 2：可以用到索引。
    • WHERE a = 1 AND b = 2 AND c = 3：可以用到索引。
    • WHERE a = 1 AND c = 3：只能用到索引 a 部分，c 部分用不到。
    • WHERE b = 2 AND c = 3：無法用到該索引（除非有其他以b開頭的索引）。
• 順序很重要： 創建聯合索引時，列的順序非常關鍵。通常將選擇性高（區分度大）且最常用的列放在最左邊。

八、索引失效的常見場景

即使創建了索引，某些查詢也可能無法有效利用索引，導致性能下降。以下是一些常見的索引失效場景：

1. 在索引列上使用函數或進行計算：
   WHERE YEAR(create_time) = 2024; (應改為 WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';)
   WHERE score / 10 = 9; (應改為 WHERE score >= 90 AND score < 100;)
2. WHERE 子句中使用 OR 連接條件：
   如果 OR 前后的條件列沒有都建立索引，或者優化器認為全表掃描更快，索引可能失效。某些情況下優化器可能會嘗試索引合并（Index Merge）。
3. LIKE 查詢以通配符 % 開頭：
   WHERE name LIKE '%keyword'; (索引失效)
   WHERE name LIKE 'keyword%'; (索引有效，會走范圍掃描)
4. 字符串類型字段查詢時不加引號（隱式類型轉換）：
   如果 phone 列是 VARCHAR 類型：WHERE phone = 12345678901; (可能發生隱式類型轉換，導致索引失效，應為 WHERE phone = '12345678901';)
5. 索引列數據類型不匹配或隱式轉換。
6. IS NULL 和 IS NOT NULL：
   • IS NULL 通常可以用到索引。
   • IS NOT NULL 在某些情況下可能用不到索引，取決于數據的NULL值分布和優化器判斷。
7. 范圍查詢右邊的列索引失效：
   對于聯合索引 (a, b, c)，如果 WHERE a = 1 AND b > 10 AND c = 3;，那么 c 列的索引將無法使用，因為 b 列是范圍查詢。
8. 優化器選擇：
   有時即使查詢符合索引使用規則，MySQL 優化器也可能基于成本估算（如判斷回表代價過高或掃描數據量小）而選擇全表掃描。可以使用 EXPLAIN 查看執行計劃。
9. 索引列區分度過低： 例如在性別列上建索引，優化器可能認為走索引意義不大。

九、索引設計原則

良好的索引設計是數據庫性能優化的關鍵。

1. 選擇合適的列創建索引：
   • 經常用于 WHERE 子句的列。
   • 經常用于 ORDER BY、GROUP BY 子句的列。
   • 經常用于表連接的列（外鍵）。
2. 選擇高基數性（高區分度）的列： 列中不同值的數量越多，索引的選擇性越好。
3. 使用短索引（前綴索引）： 對于 VARCHAR、TEXT 等長字符串列，如果只需要索引前面一部分字符就能保證足夠的區分度，可以創建前綴索引，以節省空間和提高效率。CREATE INDEX idx_name_prefix ON users(name(10));
4. 利用最左前綴匹配原則： 合理設計聯合索引的列順序。
5. 盡量使用覆蓋索引： 減少回表操作。
6. 避免冗余和重復索引： 例如，有了主鍵索引 (id)，再創建普通索引 (id) 就是冗余的。有了聯合索引 (a, b)，再創建索引 (a) 也是冗余的（除非有特殊場景需要單獨的 (a) 索引）。
7. 索引并非越多越好： 每個額外的索引都會增加寫操作的開銷和存儲空間。需要權衡利弊。
8. 定期維護和審查索引： 隨著業務發展和數據變化，一些索引可能不再適用或效率低下，需要定期檢查并優化。可以使用 EXPLAIN 分析查詢計劃。
9. 刪除不再使用的索引： 避免不必要的開銷。