MySQL联合索引设计中字段顺序、区分度与优化器行为示例详解_Mysql

在日常开发中，我们常常为查询加上联合索引，例如：

create index idx_unit_user on t_user (unit_id, user_id);

但在很多项目里，还能看到这种写法：

create index idx_del_unit_user on t_user (del_flag, unit_id, user_id);

del_flag 表示是否删除，只有 0 和 1 两种取值。

很多人认为“查询总有 del_flag=0 条件，索引当然要从它开始”，

但事实上，这样反而可能拖慢查询速度。

本文将系统讲清楚三个关键问题：

联合索引字段顺序的重要性
区分度（cardinality）对索引效率的影响
mysql 优化器是否会自动调整 where 条件顺序

一、联合索引的匹配原理回顾

联合索引 (a, b, c) 的底层是一个 b+tree。

mysql 检索时会按照索引定义的列顺序有序排列：

a → b → c

因此，它遵循 最左前缀原则（leftmost prefix rule）：

可以命中 (a)、(a,b)、(a,b,c)；
但无法单独命中 (b)、(c) 或 (b,c)。

这意味着：索引列的顺序决定了 mysql 能否利用该索引。

二、区分度（cardinality）是什么？

区分度是衡量字段“区分能力”的指标：

区分度 = 不同值数量 / 总记录数

可通过命令查看：

show index from your_table;

其中 cardinality 表示索引中不同值的大致数量。

字段	取值示例	区分度	是否适合放在索引前面
del_flag	0/1	极低	❌
gender	m/f	极低	❌
unit_id	上千单位	中高	✅
user_id	唯一	极高	✅

三、为什么低区分度字段放前面会拖慢查询？

假设你定义了：

create index idx_del_unit_user on t_user (del_flag, unit_id, user_id);

del_flag 只有两种值（0、1）。

查询如下：

select * from t_user where del_flag = 0 and unit_id = 1001;

索引的逻辑结构类似：

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

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

mysql 实际上会扫描整个 (del_flag=0) 这半边索引树，

再在其中过滤出 unit_id=1001 的数据。

因为 del_flag 不能有效缩小数据范围，性能几乎无提升。

低区分度列放在前面时，索引分区极不均衡，效果有限。

四、优化设计：高区分度字段放前

如果查询模式是：

where del_flag=0 and unit_id=? and user_id=?

更合理的索引应为：

create index idx_unit_user_del on t_user (unit_id, user_id, del_flag);

执行顺序如下：

mysql 先根据 unit_id 定位；
再通过 user_id 精确匹配；
最后判断 del_flag=0。

结果是：扫描范围更小，性能显著提升。

五、where 条件顺序会影响吗？

很多人问：

“如果我写的 sql 是 where del_flag=0 and unit_id=? and user_id=?，
那是不是应该把 unit_id 放前面？”

答案是：不用。

mysql 优化器会自动调整逻辑顺序

mysql 的优化器会：

自动重排 where 条件；
根据各条件的“选择性”（区分度）判断最优的索引路径；
但它不会改变索引的定义顺序。

换句话说：

写 sql 的顺序不重要；
索引定义的顺序才重要。

实测验证

索引：

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;

结果完全一致：

key: idx_unit_user_del
key_len: ...
rows: 1
extra: using index condition

✅ 说明优化器自动识别了最优执行路径，
where 条件顺序无关紧要。

但优化器不会“反转索引”

如果索引定义是：

create index idx_del_unit_user on t_user (del_flag, unit_id, user_id);

那无论你写：

where unit_id=1001 and user_id=8888 and del_flag=0;

还是反过来写，

优化器都无法跳过 del_flag 直接用 (unit_id, user_id)。

只能从 del_flag=0 那个分支扫描，性能依然很差。

六、实战对比

查询	索引	是否命中	说明
`where unit_id=? and user_id=? and del_flag=0`	`(unit_id, user_id, del_flag)`	✅ 完整命中	🚀 性能最优
`where del_flag=0 and unit_id=? and user_id=?`	`(unit_id, user_id, del_flag)`	✅ 完整命中	🚀 一样快
`where del_flag=0 and unit_id=?`	`(unit_id, user_id, del_flag)`	✅ 部分命中	👍 仍快
`where del_flag=0`	`(unit_id, user_id, del_flag)`	❌ 不命中最左前缀	🐢 慢
`where unit_id=? and user_id=?`	`(del_flag, unit_id, user_id)`	❌ 无法跳过 del_flag	🐢 慢

七、区分度与索引顺序的设计原则

原则	说明
区分度优先	高区分度列放在前（如 unit_id、user_id）
过滤性优先	查询中最能减少扫描范围的条件放前
稳定性优先	每次查询必带的条件（如 del_flag）放最后
低区分度列不单独建索引	例如 0/1、状态、布尔值
用 explain 验证执行计划	理论与实际可能受统计信息影响

八、推荐实践模板

查询场景	推荐索引
`where del_flag=0 and unit_id=?`	`(unit_id, del_flag)`
`where del_flag=0 and user_id=?`	`(user_id, del_flag)`
`where del_flag=0 and unit_id=? and user_id=?`	`(unit_id, user_id, del_flag)`

🚫 不推荐 (del_flag, unit_id, user_id)
✅ 推荐 (unit_id, user_id, del_flag)

九、总结

重点	说明
✅ 索引顺序决定可用性	最左前缀原则
✅ 区分度决定效率	区分度高 → 放前面
✅ where 条件顺序无关紧要	优化器会自动重排
❌ 低区分度列放前浪费索引	如 del_flag、status
✅ 正确索引能提升数十倍性能	用 explain 验证