在日常開(kāi)發(fā)中，我們常常為查詢加上聯(lián)合索引，例如：

CREATE INDEX idx_unit_user ON t_user (unit_id, user_id);

但在很多項(xiàng)目里，還能看到這種寫法：

CREATE INDEX idx_del_unit_user ON t_user (del_flag, unit_id, user_id);

del_flag 表示是否刪除，只有 0 和 1 兩種取值。

很多人認(rèn)為“查詢總有 del_flag=0 條件，索引當(dāng)然要從它開(kāi)始”，

但事實(shí)上，這樣反而可能拖慢查詢速度。

本文將系統(tǒng)講清楚三個(gè)關(guān)鍵問(wèn)題：

1. 聯(lián)合索引字段順序的重要性

2. 區(qū)分度（Cardinality）對(duì)索引效率的影響

3. MySQL 優(yōu)化器是否會(huì)自動(dòng)調(diào)整 WHERE 條件順序

一、聯(lián)合索引的匹配原理回顧

聯(lián)合索引 (a, b, c) 的底層是一個(gè) B+Tree。

MySQL 檢索時(shí)會(huì)按照索引定義的列順序有序排列：

a → b → c

因此，它遵循 最左前綴原則（Leftmost Prefix Rule）：

• 可以命中 (a)、(a,b)、(a,b,c)；

• 但無(wú)法單獨(dú)命中 (b)、(c) 或 (b,c)。

這意味著：索引列的順序決定了 MySQL 能否利用該索引。

二、區(qū)分度（Cardinality）是什么？

區(qū)分度是衡量字段“區(qū)分能力”的指標(biāo)：

區(qū)分度 = 不同值數(shù)量 / 總記錄數(shù)

可通過(guò)命令查看：

SHOW INDEX FROM your_table;

其中 Cardinality 表示索引中不同值的大致數(shù)量。

三、為什么低區(qū)分度字段放前面會(huì)拖慢查詢？

假設(shè)你定義了：

CREATE INDEX idx_del_unit_user ON t_user (del_flag, unit_id, user_id);

del_flag 只有兩種值（0、1）。

查詢?nèi)缦拢?/p>

SELECT * FROM t_user WHERE del_flag = 0 AND unit_id = 1001;

索引的邏輯結(jié)構(gòu)類似：

(del_flag=0) → [unit_id 排序 ...]

(del_flag=1) → [unit_id 排序 ...]

MySQL 實(shí)際上會(huì)掃描整個(gè) (del_flag=0) 這半邊索引樹(shù)，

再在其中過(guò)濾出 unit_id=1001 的數(shù)據(jù)。

因?yàn)?del_flag 不能有效縮小數(shù)據(jù)范圍，性能幾乎無(wú)提升。

低區(qū)分度列放在前面時(shí)，索引分區(qū)極不均衡，效果有限。

四、優(yōu)化設(shè)計(jì)：高區(qū)分度字段放前

如果查詢模式是：

WHERE del_flag=0 AND unit_id=? AND user_id=?

更合理的索引應(yīng)為：

CREATE INDEX idx_unit_user_del ON t_user (unit_id, user_id, del_flag);

執(zhí)行順序如下：

1. MySQL 先根據(jù) unit_id 定位；

2. 再通過(guò) user_id 精確匹配；

3. 最后判斷 del_flag=0。

結(jié)果是：掃描范圍更小，性能顯著提升。

五、WHERE 條件順序會(huì)影響嗎？

很多人問(wèn)：

“如果我寫的 SQL 是 WHERE del_flag=0 AND unit_id=? AND user_id=?，
那是不是應(yīng)該把 unit_id 放前面？”

答案是：不用。

MySQL 優(yōu)化器會(huì)自動(dòng)調(diào)整邏輯順序

MySQL 的優(yōu)化器會(huì)：

• 自動(dòng)重排 WHERE 條件；

• 根據(jù)各條件的“選擇性”（區(qū)分度）判斷最優(yōu)的索引路徑；

• 但它不會(huì)改變索引的定義順序。

換句話說(shuō)：

• 寫 SQL 的順序不重要；

• 索引定義的順序才重要。

實(shí)測(cè)驗(yàn)證

索引：

CREATE INDEX idx_unit_user_del ON t_user (unit_id, user_id, del_flag);

兩條 SQL：

EXPLAIN SELECT * FROM t_user 
WHERE del_flag=0 AND unit_id=1001 AND user_id=8888;

EXPLAIN SELECT * FROM t_user 
WHERE unit_id=1001 AND user_id=8888 AND del_flag=0;

結(jié)果完全一致：

key: idx_unit_user_del
key_len: ...
rows: 1
Extra: Using index condition

? 說(shuō)明優(yōu)化器自動(dòng)識(shí)別了最優(yōu)執(zhí)行路徑，
WHERE 條件順序無(wú)關(guān)緊要。

但優(yōu)化器不會(huì)“反轉(zhuǎn)索引”

如果索引定義是：

CREATE INDEX idx_del_unit_user ON t_user (del_flag, unit_id, user_id);

那無(wú)論你寫：

WHERE unit_id=1001 AND user_id=8888 AND del_flag=0;

還是反過(guò)來(lái)寫，

優(yōu)化器都無(wú)法跳過(guò) del_flag 直接用 (unit_id, user_id)。

只能從 del_flag=0 那個(gè)分支掃描，性能依然很差。

六、實(shí)戰(zhàn)對(duì)比

七、區(qū)分度與索引順序的設(shè)計(jì)原則

八、推薦實(shí)踐模板

?? 不推薦 (del_flag, unit_id, user_id)
? 推薦 (unit_id, user_id, del_flag)

九、總結(jié)

?? 一句話總結(jié)：

MySQL 會(huì)自動(dòng)優(yōu)化 WHERE 條件順序，但不會(huì)改變索引定義順序。
因此，請(qǐng)始終把高區(qū)分度字段放在聯(lián)合索引前列，
把低區(qū)分度的 del_flag、status 等放在最后。

到此這篇關(guān)于MySQL聯(lián)合索引設(shè)計(jì)中字段順序、區(qū)分度與優(yōu)化器行為的文章就介紹到這了,更多相關(guān)MySQL聯(lián)合索引字段順序、區(qū)分度與優(yōu)化器內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論