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

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

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

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

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

最左前綴法則

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

聽(tīng)起來(lái)有點(diǎn)抽象，我們拆解一下：

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

來(lái)看具體場(chǎng)景：

最后一個(gè)有點(diǎn)特殊：WHERE a = 1 AND c = 3。MySQL 會(huì)用 a 來(lái)定位索引范圍，但 c 沒(méi)法用，因?yàn)?b 被跳過(guò)了，c 在索引中的位置不確定。

從 B+ 樹(shù)結(jié)構(gòu)理解為什么

要真正理解最左前綴，得看 B+ 樹(shù)的結(jié)構(gòu)。

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

根節(jié)點(diǎn)
    │
    ├── [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ù)據(jù)的排序規(guī)則：先按 a 排序，a 相同再按 b 排序，b 也相同再按 c 排序。

這意味著：

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

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

你有一本按"省份-城市-區(qū)縣"排序的通訊錄。找"陜西省西安市未央?yún)^(qū)"很容易，找"陜西省未央?yún)^(qū)"也行（先定位陜西省，再跳過(guò)城市直接找區(qū)縣——但效率會(huì)降低）。但如果只給你"未央?yún)^(qū)"三個(gè)字，你根本沒(méi)法翻這本通訊錄，因?yàn)槲囱雲(yún)^(qū)的數(shù)據(jù)分散在不同省份下面。

哪些情況會(huì)失效

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

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

-- 索引 (a, b, c)

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

b 用了范圍查詢（>、<、BETWEEN），c 就沒(méi)法用索引了。因?yàn)?b 的范圍確定后，c 的值在范圍內(nèi)是無(wú)序的。

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

-- 索引 (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';

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

3. 隱式類型轉(zhuǎn)換

-- 索引 (phone)

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

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

類型不匹配時(shí) MySQL 會(huì)做隱式轉(zhuǎn)換，相當(dāng)于對(duì)索引列用了函數(shù)，索引失效。

4. LIKE 左模糊

-- 索引 (name)

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

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

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

5. OR 條件（部分場(chǎng)景）

-- 索引 (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 兩側(cè)的條件涉及不同索引，早期 MySQL 只能走全表掃描。MySQL 8.0 引入了 Index Merge 優(yōu)化，可以同時(shí)使用多個(gè)索引再合并結(jié)果。

索引設(shè)計(jì)的實(shí)操建議

理解了原理，設(shè)計(jì)索引時(shí)記住這幾條：

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);

-- 糟糕的索引（范圍查詢?cè)谇埃竺娴牧惺В?
ALTER TABLE orders ADD INDEX idx_time_user_status (create_time, user_id, status);

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

-- status 只有幾種值，區(qū)分度低
-- user_id 每個(gè)用戶都不同，區(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ū)分度越高，索引過(guò)濾能力越強(qiáng)。

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

-- 已有索引 (a, b, c)
-- 不需要再建 (a, b)，因?yàn)?(a, b, c) 的前綴已經(jīng)覆蓋了 (a, b)

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

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

小結(jié)

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

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

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

最新評(píng)論