引言

數據庫性能直接影響系統響應速度。作為關系型數據庫的典型代表，MySQL的索引設計與優化是每個開發者必須掌握的技能。接下來我們將深入剖析MySQL的索引原理，結合真實場景案例，揭秘B+Tree的獨特優勢，并通過執行計劃分析與優化策略，探討如何構建高性能數據庫系統。

我們經常會碰到如下問題：

• 數據庫索引底層使用的是什么數據結構和算法呢？

• 為什么 MySQL InnoDB 選擇 B+Tree 當默認的索引數據結構？

• 如何通過執行計劃查看索引使用詳情？

• 有哪些情況會導致索引失效？

• 平時有哪些常見的優化索引的方法？

• ……

無非就是對應

• MySQL InnoDB 的索引原理；

• B+Tree 相比于其他索引數據結構（如 B-Tree、二叉樹，以及 Hash 表）的優勢；

• MySQL 執行計劃的方法；

• 導致索引失效的常見情況；

• 常用的建立高效索引的技巧（如前綴索引、建立覆蓋索引等）。

---

一、B+Tree索引核心原理

1.1 索引數據結構演化

深入理解二叉樹、B樹與B+樹：原理、應用與實現

數據結構	查詢復雜度	范圍查詢	磁盤I/O效率	適用場景
二叉樹	O(log n)	差	高	小數據量精確查詢
B-Tree	O(log n)	較好	中	通用場景
B+Tree	O(log n)	優秀	高	大數據量范圍查詢
Hash表	O(1)	不支持	高	等值查詢

1.2 B+Tree的存儲結構

建個表如下：

CREATE TABLE `product`  (`id` int(11) NOT NULL,`product_no` varchar(20)  DEFAULT NULL,`name` varchar(255) DEFAULT NULL,`price` decimal(10, 2) DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
);

新增幾條數據如下：

通過主鍵查詢（主鍵索引）商品數據的過程

此時當我們使用主鍵索引查詢商品 15 的時候，那么按照 B+Tree 索引原理，是如何找到對應數據的呢？

select * from product where id = 15

我們可以通過數據手動構建一個 B+Tree，它的每個節點包含 3 個子節點（B+Tree 每個節點允許有 M 個子節點，且 M>2），根節點中的數據值 1、18、36 分別是子節點（1，6，12），（18，24，30）和（36，41，52）中的最小值。

每一層父節點的數據值都會出現在下層子節點的數據值中，因此在葉子節點中，包括了所有的數據值信息，并且每一個葉子節點都指向下一個葉子節點，形成一個鏈表。如圖所

比如想要查找數據值 15，B+Tree 會自頂向下逐層進行查找：

• 將 15 與根節點的數據 (1，18，36) 比較，15 在 1 和 18 之間，所以根據 B+Tree的搜索邏輯，找到第二層的數據塊 (1，6，12)；

• 在第二層的數據塊 (1，6，12) 中進行查找，因為 15 大于 12，所以找到第三層的數據塊 (12，15，17)；

• 在葉子節點的數據塊 (12，15，17) 中進行查找，然后我們找到了數據值 15；

• 最終根據數據值 15 找到葉子節點中存儲的數據。

整個過程一共進行了 3 次 I/O 操作，所以 B+Tree 相比于 B 樹和二叉樹來說，最大的優勢在于查詢效率。

---

通過非主鍵（輔助索引）查詢商品數據的過程

如果使用商品編碼查詢商品（即使用輔助索引進行查詢），會先檢索輔助索引中的 B+Tree 的 商品編碼，找到對應的葉子節點，獲取主鍵值，然后再通過主鍵索引中的 B+Tree 樹查詢到對應的葉子節點，然后獲取整行數據。這個過程叫回表。

---

B+Tree特點：

• 多叉結構：單個節點存儲多個鍵值，降低樹高度（3-4層可支撐千萬級數據）
• 數據聚集：葉子節點形成有序雙向鏈表，支持高效范圍查詢
• 分層存儲：非葉節點僅存索引鍵，葉節點存儲完整數據（聚簇索引）或主鍵（輔助索引）

---

MySQL InnoDB 的索引原理

從數據結構的角度來看， MySQL 常見索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

在實際應用中，InnoDB 是 MySQL 建表時默認的存儲引擎，B+Tree 索引類型也是 MySQL 存儲引擎采用最多的索引類型。

在創建表時，InnoDB 存儲引擎默認使用表的主鍵作為主鍵索引，該主鍵索引就是聚簇索引（Clustered Index），如果表沒有定義主鍵，InnoDB 就自己產生一個隱藏的 6 個字節的主鍵 ID 值作為主鍵索引，而創建的主鍵索引默認使用的是 B+Tree 索引。

---

二、執行計劃深度解析

1存儲商品信息的演示表 product：

CREATE TABLE `product`  (`id` int(11) NOT NULL,`product_no` varchar(20)  DEFAULT NULL,`name` varchar(255) DEFAULT NULL,`price` decimal(10, 2) DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,KEY 'index_name' ('name').KEY 'index_id_name' ('id', 'name')
) CHARACTER SET = utf8 COLLATE = utf8_general_ci

表中包含了主鍵索引、name 字段上的普通索引，以及 id 和 name 兩個字段的聯合索引。現在我們來看一條簡單查詢語句的執行計劃：

重點關注 type 字段， 表示數據掃描類型，也就是描述了找到所需數據時使用的掃描方式是什么，常見掃描類型的執行效率從低到高的順序為（考慮到查詢效率問題，全表掃描和全索引掃描要盡量避免）：

    ALL（全表掃描）；index（全索引掃描）；range（索引范圍掃描）；ref（非唯一索引掃描）；eq_ref（唯一索引掃描）；const（結果只有一條的主鍵或唯一索引掃描）。

關鍵指標解讀：

• type：掃描類型（性能排序：const > ref > range > index > ALL）
• key_len：索引使用長度（可判斷是否使用完整索引）
• possible_keys 字段表示可能用到的索引
• key 字段表示實際用的索引
• rows：預估掃描行數
• Extra：額外信息（Using index/Using filesort等）

---

三、索引失效的六大陷阱

來看一個索引失效的例子：

這條帶有 like 查詢的 SQL 語句，沒有用到 product 表中的 index_name 索引。

我們結合普通索引的 B+Tree 結構看一下索引失效的原因： 當 MySQL 優化器根據 name like ‘%路由器’ 這個條件，到索引 index_name 的 B+Tree 結構上進行查詢評估時，發現當前節點的左右子節點上的值都有可能符合 ‘%路由器’ 這個條件，于是優化器判定當前索引需要掃描整個索引，并且還要回表查詢，不如直接全表掃描。

當然，還有其他類似的索引失效的情況：

• 索引列上做了計算、函數、類型轉換操作，這些情況下索引失效是因為查詢過程需要掃描整個索引并回表，代價高于直接全表掃描；

• like 匹配使用了前綴匹配符 ‘%abc’；

• 字符串不加引號導致類型轉換；

所以， 如果 MySQL 查詢優化器預估走索引的代價比全表掃描的代價還要大，則不走對應的索引，直接全表掃描，如果走索引比全表掃描代價小，則使用索引。

3.1 隱式類型轉換

-- 字符串字段使用數字查詢
SELECT * FROM user WHERE phone = 13800138000;

3.2 索引列參與運算

-- DATE_FORMAT函數導致索引失效
SELECT * FROM orders 
WHERE DATE_FORMAT(create_time, '%Y-%m') = '2023-07';

3.3 模糊查詢通配符前置

-- 前導通配符無法使用索引
SELECT * FROM article WHERE content LIKE '%數據庫%';

3.4 最左前綴原則違反

-- 聯合索引(a,b,c)無法命中
SELECT * FROM table WHERE b = 2 AND c = 3;

3.5 OR條件使用不當

-- 其中一個條件無索引將導致全表掃描
SELECT * FROM products 
WHERE category_id = 5 OR price > 1000;

3.6 統計信息不準確

當數據分布變化超過10%時，需執行ANALYZE TABLE更新統計信息

---

四、高性能索引設計策略

4.1 覆蓋索引優化

覆蓋索引是指 SQL 中 query 的所有字段，在索引 B+tree 的葉子節點上都能找得到的那些索引，從輔助索引中查詢得到記錄，而不需要通過聚簇索引查詢獲得。假設我們只需要查詢商品的名稱、價格，有什么方式可以避免回表呢？

我們可以建立一個組合索引，即商品ID、名稱、價格作為一個組合索引。如果索引中存在這些數據，查詢將不會再次檢索主鍵索引，從而避免回表。所以，使用覆蓋索引的好處很明顯，即不需要查詢出包含整行記錄的所有信息，也就減少了大量的 I/O 操作

-- 建立包含所有查詢字段的聯合索引
CREATE INDEX idx_order_status_time 
ON orders(status, create_time, total_amount);SELECT status, create_time, total_amount 
FROM orders 
WHERE status = 1 
ORDER BY create_time DESC;

4.2 前綴索引技巧

前綴索引就是用某個字段中，字符串的前幾個字符建立索引，比如我們可以在訂單表上對商品名稱字段的前 5 個字符建立索引。使用前綴索引是為了減小索引字段大小，可以增加一個索引頁中存儲的索引值，有效提高索引的查詢速度。在一些大字符串的字段作為索引時，使用前綴索引可以幫助我們減小索引項的大小。

但是，前綴索引有一定的局限性，例如 order by 就無法使用前綴索引，無法把前綴索引用作覆蓋索引。

-- 對長文本字段前20字符建立索引
CREATE INDEX idx_product_desc 
ON products(product_desc(20));-- 計算最佳前綴長度
SELECT COUNT(DISTINCT LEFT(product_desc, 20)) / COUNT(*) 
FROM products;

4.3 聯合索引排序策略

聯合索引時，存在最左匹配原則，也就是按照最左優先的方式進行索引的匹配。比如聯合索引 (userpin, username)，如果查詢條件是 WHERE userpin=1 AND username=2，就可以匹配上聯合索引；或者查詢條件是 WHERE userpin=1，也能匹配上聯合索引，但是如果查詢條件是 WHERE username=2，就無法匹配上聯合索引。

另外，建立聯合索引時的字段順序，對索引效率也有很大影響。越靠前的字段被用于索引過濾的概率越高，實際開發工作中建立聯合索引時，要把區分度大的字段排在前面，這樣區分度大的字段越有可能被更多的 SQL 使用到。

-- 區分度高的字段在前
CREATE INDEX idx_user_region_gender 
ON users(region_code, gender);

區分度就是某個字段 column 不同值的個數除以表的總行數，比如性別的區分度就很小，不適合建立索引或不適合排在聯合索引列的靠前的位置，而 uuid 這類字段就比較適合做索引或排在聯合索引列的靠前的位置。

---

4.4 索引下推優化（ICP）

-- MySQL 5.6+ 自動啟用，減少回表次數
SELECT * FROM employees 
WHERE last_name LIKE '張%' 
AND age > 30;

五、真實場景案例解析

5.1 電商訂單查詢優化

原始SQL：

SELECT * FROM orders 
WHERE status = 2 
AND payment_time BETWEEN '2023-07-01' AND '2023-07-31'
ORDER BY create_time DESC;

優化方案：

1. 創建聯合索引(status, payment_time, create_time)
2. 使用覆蓋索引減少回表
3. 分頁查詢使用WHERE id > ?代替LIMIT深度翻頁

5.2 社交平臺好友推薦

-- 優化前（全表掃描）：
SELECT user_id FROM relationships 
WHERE friend_id = 10086 
AND relation_type = 3;-- 優化后（反向索引）：
CREATE INDEX idx_reverse_relation 
ON relationships(friend_id, relation_type);

結語

優秀的索引設計需要平衡查詢效率與寫入性能。建議遵循以下原則：

1. 優先考慮最常用查詢模式
2. 單表索引不超過5個
3. 聯合索引字段數不超過3個
4. 定期審查索引使用情況

通過理解B+Tree的底層原理，結合執行計劃分析與實際業務場景，開發者可以構建出高效的數據訪問方案。記住：沒有最好的索引，只有最適合業務場景的索引設計。