聯(lián)合索引是什么

聯(lián)合索引就是多個列組合成一個索引。

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

這條語句創(chuàng)建了一個聯(lián)合索引，包含三個列：user_id、status、create_time。

和單列索引的區(qū)別在哪？單列索引是對一個列建索引，聯(lián)合索引是對多個列的組合建索引。 你可以理解為，聯(lián)合索引是一本按"user_id + status + create_time"順序排列的字典，先按 user_id 排，user_id 相同的再按 status 排，status 也相同的再按 create_time 排。

最左前綴法則

最左前綴原則可以總結為：查詢條件必須從索引的最左列開始連續(xù)使用，索引才能生效。

聽起來有點抽象，我們拆解一下：

• 索引是 (a, b, c) 三個列
• 查詢條件必須從 a 開始，然后是 b，然后是 c，中間不能跳過

來看具體場景：

最后一個有點特殊：WHERE a = 1 AND c = 3。MySQL 會用 a 來定位索引范圍，但 c 沒法用，因為 b 被跳過了，c 在索引中的位置不確定。

從 B+ 樹結構理解為什么

要真正理解最左前綴，得看 B+ 樹的結構。

假設我們有一個聯(lián)合索引 (a, b, c)，數(shù)據在 B+ 樹中是這樣排列的：

根節(jié)點
    │
    ├── [a=1, b=1, c=1]
    ├── [a=1, b=2, c=3]
    ├── [a=2, b=1, c=5]
    ├── [a=2, b=1, c=7]
    └── [a=3, b=2, c=1]

注意數(shù)據的排序規(guī)則：先按 a 排序，a 相同再按 b 排序，b 也相同再按 c 排序。

這意味著：

1. WHERE a = 1 能走索引：因為 a 相同的數(shù)據在 B+ 樹中是相鄰的，可以快速定位
2. WHERE a = 1 AND b = 2 能走索引：a 確定后，b 相同的數(shù)據也是相鄰的
3. WHERE b = 2 不能走索引：b 的值在不同 a 之間是分散的，沒有順序性，沒法用 B+ 樹的二分查找

索引的排序規(guī)則決定了只有從最左列開始連續(xù)匹配，才能利用 B+ 樹的有序性。

你有一本按"省份-城市-區(qū)縣"排序的通訊錄。找"陜西省西安市未央區(qū)"很容易，找"陜西省未央區(qū)"也行（先定位陜西省，再跳過城市直接找區(qū)縣——但效率會降低）。但如果只給你"未央區(qū)"三個字，你根本沒法翻這本通訊錄，因為未央區(qū)的數(shù)據分散在不同省份下面。

哪些情況會失效

最左前綴只是索引失效的其中一種情況。還有幾種常見坑：

1. 范圍查詢右邊的列失效

-- 索引 (a, b, c)

-- 只命中 a，b 和 c 失效
WHERE a = 1 AND b > 5 AND c = 3

b 用了范圍查詢（>、<、BETWEEN），c 就沒法用索引了。因為 b 的范圍確定后，c 的值在范圍內是無序的。

2. 函數(shù)操作導致失效

-- 索引 (user_id)

-- 不走索引 
SELECT * FROM orders WHERE YEAR(create_time) = 2025;

-- 走索引 
SELECT * FROM orders WHERE create_time > '2025-01-01' AND create_time < '2025-12-31';

對索引列做函數(shù)操作，MySQL 無法使用索引的有序性。改成范圍查詢就能走索引。

3. 隱式類型轉換

-- 索引 (phone)

-- 不走索引 （phone 是 varchar，傳入了 int）
SELECT * FROM user WHERE phone = 13800138000;

-- 走索引 
SELECT * FROM user WHERE phone = '13800138000';

類型不匹配時 MySQL 會做隱式轉換，相當于對索引列用了函數(shù)，索引失效。

4. LIKE 左模糊

-- 索引 (name)

-- 不走索引 
SELECT * FROM user WHERE name LIKE '%張';

-- 走索引 
SELECT * FROM user WHERE name LIKE '張%';

左模糊查詢無法利用 B+ 樹的有序性，只能全表掃描。

5. OR 條件（部分場景）

-- 索引 (a), (b)

-- 不走索引
SELECT * FROM t WHERE a = 1 OR b = 2;

-- 走索引 （MySQL 8.0+ 的 Index Merge）
SELECT * FROM t WHERE a = 1 OR a = 2;

如果 OR 兩側的條件涉及不同索引，早期 MySQL 只能走全表掃描。MySQL 8.0 引入了 Index Merge 優(yōu)化，可以同時使用多個索引再合并結果。

索引設計的實操建議

理解了原理，設計索引時記住這幾條：

1. 等值查詢的列放前面

-- 查詢: WHERE user_id = 1 AND status = 'paid' AND create_time > '2025-01-01'

-- 好的索引
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

-- 糟糕的索引（范圍查詢在前，后面的列失效）
ALTER TABLE orders ADD INDEX idx_time_user_status (create_time, user_id, status);

2. 區(qū)分度高的列放前面

-- status 只有幾種值，區(qū)分度低
-- user_id 每個用戶都不同，區(qū)分度高

-- 好：user_id 放前面
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 糟糕：status 放前面
ALTER TABLE orders ADD INDEX idx_status_user (status, user_id);

區(qū)分度公式：COUNT(DISTINCT column) / COUNT(*)。區(qū)分度越高，索引過濾能力越強。

3. 避免創(chuàng)建冗余索引

-- 已有索引 (a, b, c)
-- 不需要再建 (a, b)，因為 (a, b, c) 的前綴已經覆蓋了 (a, b)

-- 但可以考慮建 (a, b)，然后刪掉 (a, b, c)（如果 c 確實用不到的話）

可以用 sys.schema_redundant_indexes 視圖查找冗余索引。

小結

最左前綴原則是 B+ 樹排序規(guī)則的直接推論。索引按 (a, b, c) 排序，那就只有從 a 開始連續(xù)匹配，才能利用有序性進行二分查找。跳過前面的列，后面的列在數(shù)據分布上就是無序的，索引就會失效。

從設計角度看，最左前綴法則的本質是：索引的列順序決定了哪些查詢能受益。 這不是"怎么用索引"的問題，而是"怎么設計索引"的問題。把最常用的查詢條件列放在最前面，把區(qū)分度高的列優(yōu)先排列，才能讓索引真正發(fā)揮作用。

到此這篇關于深入理解MySQL 最左前綴法則的文章就介紹到這了,更多相關MySQL 最左前綴法則內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論