在 MySQL InnoDB 存儲引擎 中，索引主要分為 聚簇索引（Clustered Index） 和 非聚簇索引（Secondary Index）。它們的主要區別如下：

---

1. 聚簇索引（Clustered Index）

定義

• 聚簇索引是表數據存儲的方式，即 索引和數據在一起，葉子節點存儲的是完整的行記錄。
• InnoDB 中的主鍵索引就是聚簇索引（如果沒有主鍵，InnoDB 會選擇一個 UNIQUE 索引作為聚簇索引；如果沒有 UNIQUE，InnoDB 會自動生成一個隱藏的 rowid）。

存儲結構

• 使用 B+ 樹 結構存儲，葉子節點存放 完整的行數據。
• 數據物理存儲順序和主鍵索引順序一致。

特點

• 訪問主鍵索引時，直接獲取整行數據，查詢速度快。
• 數據存儲在主鍵索引的葉子節點上，表本身就是一棵 B+ 樹。
• 適用于 基于主鍵的查詢、范圍查詢。
• 更新、刪除、插入操作可能導致數據的頁分裂，影響性能。

示例

CREATE TABLE users (id INT PRIMARY KEY,  -- 主鍵，自動成為聚簇索引name VARCHAR(50),age INT
);

在 users 表中，id 作為 聚簇索引，數據在磁盤上的存儲順序與 id 值的順序相同。

---

2. 非聚簇索引（Secondary Index / 輔助索引）

定義

• 非聚簇索引是獨立于數據存儲的索引，葉子節點存儲的是主鍵值，而不是完整的行數據。
• 訪問非聚簇索引時，需要先查找索引，再通過主鍵回表查詢數據，這種過程稱為 回表查詢（回溯查詢，Bookmark Lookup）。

存儲結構

• 也是 B+ 樹 結構，但葉子節點存儲的是主鍵值，而非完整的行記錄。

特點

• 加速非主鍵列的查詢（適用于 WHERE 條件非主鍵字段）。
• 需要二次查詢（先通過非聚簇索引找到主鍵值，再回表查詢完整數據）。
• 索引占用空間較大，需要額外存儲主鍵值。

示例

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

如果執行：

SELECT * FROM users WHERE name = 'Alice';

查詢過程如下：

1. 先在 非聚簇索引 idx_name 中查找 Alice，獲取其 主鍵 id。
2. 使用 id 在 聚簇索引 中回表查找完整的行數據。

---

3. 聚簇索引 vs. 非聚簇索引

對比項	聚簇索引（Clustered Index）	非聚簇索引（Secondary Index）
存儲結構	葉子節點存儲 完整行數據	葉子節點存儲 主鍵值
查詢速度	主鍵查詢快（無需回表）	非主鍵查詢慢（需要回表）
插入速度	慢（影響數據存儲順序）	較快（不影響主鍵順序）
更新操作	影響數據物理存儲順序，可能導致頁分裂	更新可能導致回表查詢增加
占用空間	只存儲數據本身	需要存儲 索引列 + 主鍵值，占用空間大
適用場景	主鍵查詢，范圍查詢	查詢非主鍵列，提高查詢性能

---

4. 什么時候使用聚簇索引 vs. 非聚簇索引？

? 適合使用聚簇索引的場景：

• 主鍵查詢多，如 WHERE id = 100;
• 范圍查詢多，如 BETWEEN 100 AND 200;
• 數據表的主鍵是有序遞增的（如 AUTO_INCREMENT），避免頁分裂。

? 適合使用非聚簇索引的場景：

• 查詢非主鍵字段時，如 WHERE name = 'Alice';
• 涉及聯合查詢，如 INDEX (name, age);
• 表很大，不希望頻繁回表查詢（可以使用 覆蓋索引，避免回表）。

---

5. 可能的優化方案

1. 覆蓋索引（Covering Index）：

   • 避免回表，提高查詢性能。

   • 優化方法：如果查詢的數據只包含索引字段，則直接從索引中獲取數據，不需要回表。

   • 示例：

     CREATE INDEX idx_name_age ON users(name, age);
     SELECT name, age FROM users WHERE name = 'Alice';  -- 只查 name 和 age，不回表
     

2. 使用合適的主鍵：

   • 采用 自增主鍵，避免頁分裂，提高插入性能。
   • 避免使用 UUID、隨機值作為主鍵，會導致索引失效、性能下降。

3. 減少回表查詢：

   • 索引覆蓋查詢，如 SELECT name FROM users WHERE name = 'Alice';
   • 適當調整索引列，如 INDEX (name, age) 避免多次回表。

---

總結

• InnoDB 中的主鍵索引是聚簇索引，數據存儲在 B+ 樹的葉子節點上，查詢主鍵時速度最快。
• 非聚簇索引存儲的是主鍵值，查詢時需要回表查詢完整數據，適用于非主鍵查詢場景。
• 優化索引可以減少回表查詢，提高 MySQL 查詢性能。