一、什麼是 MySQL 中的索引？

定義：
索引是數據庫中一種用於 加快數據查詢速度 的數據結構。
它就像一本書的“目錄”一樣，可以讓數據庫更快地找到你想要的數據，而不需要從頭到尾掃描整張表。

簡單比喻：
假設有一本 1000 頁的書：

• 沒有索引時：你找“第 732 頁的某個內容”，只能一頁頁翻；
• 有索引時：你看目錄就能直接跳轉到對應頁碼。

在 MySQL 中，索引是存儲引擎層（如 InnoDB）維護的一種 數據結構，常用的數據結構是 B+ 樹。

二、為什麼需要索引？

索引的主要作用：

1. 加快數據查詢速度
2. 加快排序、分組等操作
3. 加快表的連接（JOIN）
4. 通過唯一索引保證數據唯一性

但要注意：

• 索引會佔用額外的磁盤空間；
• 寫入/更新/刪除數據時也會維護索引，稍微降低寫入性能。

所以：索引是“以空間換時間”的機制。

三、MySQL索引的分類

索引可以按多種維度分類：
我們主要從以下四種維度講解：

1️⃣ 按 數據結構 分類
2️⃣ 按 物理存儲 分類
3️⃣ 按 字段特性 分類
4️⃣ 按 字段個數 分類

① 按數據結構分類

舉例：

-- 創建B+樹索引（默認）
CREATE INDEX idx_name ON user(name);

-- 創建全文索引
CREATE FULLTEXT INDEX idx_content ON article(content);

② 按物理存儲分類

舉例：

-- InnoDB 聚簇索引
CREATE TABLE student (
  id INT PRIMARY KEY,       -- 主鍵聚簇索引
  name VARCHAR(50),
  age INT,
  INDEX idx_name(name)      -- 非聚簇索引
);

解釋：

• id 是主鍵 → 聚簇索引；
• idx_name 是普通索引 → 非聚簇索引。

③ 按字段特性分類

舉例：

CREATE TABLE user (
  id INT PRIMARY KEY,                -- 主鍵索引
  email VARCHAR(100) UNIQUE,         -- 唯一索引
  name VARCHAR(50), 
  INDEX idx_name(name),              -- 普通索引
  FULLTEXT INDEX idx_bio(bio)        -- 全文索引
);

④ 按字段個數分類

舉例：

-- 單列索引
CREATE INDEX idx_name ON user(name);

-- 聯合索引
CREATE INDEX idx_name_age ON user(name, age);

注意：
聯合索引遵循 最左前綴原則，即索引 (name, age) 實際上相當於對：

• (name)
• (name, age) 有索引效果，但對 (age) 單獨無效。

四、總結表格一覽

補充講解：

補充一 、最左匹配原則（Leftmost Prefix Rule）

✅ 1. 定義

“最左匹配原則”是指：

聯合索引會從最左邊的字段開始匹配，只有按照最左的字段順序使用索引，索引才會生效。

換句話説：
MySQL 從聯合索引的 第一個字段 開始匹配條件，
當中途遇到 範圍查詢（如 >, <, BETWEEN, LIKE 'xxx%'） 時，就會停止繼續匹配後面的字段。

✅ 2. 舉例講解

假設我們有如下表：

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  sex CHAR(1),
  INDEX idx_name_age_sex(name, age, sex)
);

索引的順序是：(name, age, sex)。

📘 例1：完整匹配（使用索引）

SELECT * FROM user WHERE name = 'Tom' AND age = 20 AND sex = 'M';

✅ 按 (name, age, sex) 順序依次匹配，用到了全部三列索引。
👉 索引使用情況：name → age → sex

📘 例2：部分匹配（仍然使用索引）

SELECT * FROM user WHERE name = 'Tom' AND age = 20;

✅ 用到了前兩列 (name, age)。
👉 索引使用情況：name → age

📘 例3：只用最左字段（仍使用索引）

SELECT * FROM user WHERE name = 'Tom';

✅ 用到了索引的第一列 name。
👉 索引使用情況：name

📘 例4：跳過最左字段（索引失效）

SELECT * FROM user WHERE age = 20;

❌ 不符合最左匹配原則，因為沒有使用 name。
👉 索引 完全失效，需要全表掃描。

📘 例5：範圍查詢中斷匹配

SELECT * FROM user WHERE name = 'Tom' AND age > 18 AND sex = 'M';

✅ 只會使用 (name, age) 索引部分；
❌ 不會使用 sex，因為 age > 18 是範圍查詢。
👉 索引使用情況：name → age（停止）

📘 例6：順序不一致但可優化

SELECT * FROM user WHERE age = 20 AND name = 'Tom';

✅ 雖然條件順序不一致，但 MySQL 優化器會自動調整 順序，依然可以使用 (name, age) 索引。

📘 例7：LIKE 前綴匹配可用索引

SELECT * FROM user WHERE name LIKE 'T%';

✅ LIKE 'T%' 等價於範圍查詢 'T' <= name < 'U'，仍然可用索引。

但：

SELECT * FROM user WHERE name LIKE '%Tom%';

❌ 前面有通配符 %，索引失效。

三、索引下推（Index Condition Pushdown, ICP）

✅ 1. 定義

索引下推（ICP） 是 MySQL 5.6 引入的一種 索引優化機制。

在沒有 ICP 之前，MySQL 在通過索引定位到數據行後，會把這些行提取出來交給 Server 層 再做 WHERE 條件判斷。
而有了 ICP 後，一部分 WHERE 條件判斷可以 在存儲引擎層完成，減少回表次數，提高性能。

✅ 2. 舉例説明

仍然以這個表為例：

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  sex CHAR(1),
  INDEX idx_name_age(name, age)
);

📘 示例1：沒有索引下推的情況

SELECT * FROM user WHERE name LIKE 'Tom%' AND age = 20;

假設聯合索引 (name, age)：

• name LIKE 'Tom%' 是範圍查詢；
• 所以索引只能匹配 name，不能直接匹配 age。

🔹 沒有 ICP 時的執行邏輯：

1. 通過索引掃描所有 name 以 'Tom%' 開頭的記錄；
2. 每匹配一條記錄，都回表取完整行；
3. 再在 Server 層 判斷 age = 20；
4. 不符合的行被丟棄。

👉 缺點：回表次數太多（磁盤 IO 多）。

📘 示例2：啓用索引下推的情況（ICP）

有 ICP 時：

1. 通過索引掃描 name LIKE 'Tom%'；
2. 在索引層直接判斷 age = 20；
3. 只有滿足 age = 20 的行才回表取數據。

👉 優勢：

• 減少回表次數；
• 大幅提升查詢效率。

✅ 3. ICP 的觸發條件

• 必須是 InnoDB 或 MyISAM 引擎；
• 必須是 聯合索引；
• 查詢語句中有 範圍查詢 + 其他條件；
• 其他條件可以在索引中判斷的情況下才生效。

最左匹配 + 索引下推 對比總結