问题:联合索引为什么有时用不上
在 MySQL 5.7 中,联合索引是一棵按索引列顺序排列的 B+Tree。以 (user_id, status, created_at) 为例,索引先按 user_id 排序,user_id 相同再按 status,最后按 created_at。查询条件如果不能从最左列开始形成连续的前缀,优化器通常无法利用这棵索引快速定位数据。
还要区分“是否使用索引”和“是否高效使用索引”。EXPLAIN 的 key 有值,只表示优化器选择了某个索引;type、rows、filtered、Extra 和 key_len 才能帮助判断使用程度。小表、返回大量行或条件选择性很低时,全表扫描可能是合理选择。
最左前缀的判定规则
对索引 (a, b, c),可以按下面的思路判断:
| 查询条件 | 可利用的索引前缀 | 说明 |
|---|---|---|
a = ? | a | 从最左列开始 |
a = ? AND b = ? | a, b | 连续匹配 |
a = ? AND b = ? AND c = ? | a, b, c | 完整匹配 |
a = ? AND c = ? | a | 缺少 b,c 不能继续用于范围定位 |
b = ? | 通常不能用于定位 | 不是从最左列开始 |
a = ? AND b > ? AND c = ? | a, b | b 是范围条件,c 一般不能继续缩小范围 |
需要说明两点。第一,SQL 中条件书写的先后顺序不必和索引列顺序完全一致,优化器会识别等值条件。第二,范围条件之后的列通常不能继续用于索引范围定位,但可能通过索引条件下推(ICP)在存储引擎层过滤,EXPLAIN 的 Extra 可能显示 Using index condition。这不等于该列参与了范围定位。
建索引与验证步骤
可以按“收集查询、设计索引、执行计划验证、调整”的顺序操作。
- 收集高频查询的 WHERE、ORDER BY、GROUP BY 和返回列。
- 把经常一起出现的等值条件列放在联合索引前面,把范围条件列放在后面;如果同一类查询还要排序,尽量让等值列之后的索引顺序与排序顺序一致。
- 建立索引后执行
ANALYZE TABLE更新统计信息,再用EXPLAIN观察执行计划。 - 重点看
key是否为目标索引,type是否为ref、range等较合适的形式,rows是否明显减少,Extra是否出现Using where、Using index、Using index condition或Using filesort。 - 如果优化器没有选择索引,先检查数据分布、返回行数、条件写法和统计信息,再决定是否调整索引,而不是只看 SQL 形式。
示例表结构:
CREATE TABLE t_order (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
status TINYINT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL,
amount DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id),
KEY idx_user_status_time (user_id, status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;完整前缀查询:
EXPLAIN SELECT id, user_id, status, created_at
FROM t_order
WHERE user_id = 100
AND status = 1
AND created_at >= '2024-01-01 00:00:00'
AND created_at < '2024-02-01 00:00:00';缺少中间列时:
EXPLAIN SELECT id, user_id, status, created_at
FROM t_order
WHERE user_id = 100
AND created_at >= '2024-01-01 00:00:00';第二条查询通常只能利用 user_id 做定位,created_at 可能作为索引条件下推的过滤条件,但不能像连续前缀那样缩小索引范围。实际选择仍要以 EXPLAIN 为准。
如果查询只返回索引中已有的列,例如 user_id、status、created_at,可能形成覆盖索引,减少回表;如果使用 SELECT *,通常还需要回表读取其他列。覆盖索引是优化手段,不是使用联合索引的前提。
常见错误与修正
跳过最左列。 对索引 (user_id, status, created_at),只写 WHERE status = 1 通常不能利用该索引定位。若业务确实需要按 status 查询,应考虑建立以 status 开头的索引,或调整查询条件,而不是强行套用原索引。
在范围列之后继续期待等值列缩小范围。 例如 WHERE user_id = 100 AND status > 1 AND created_at = '2024-01-01',索引通常只能利用到 user_id 和 status 的范围,created_at 不能继续用于范围定位。若 created_at 选择性更高,可能需要评估 (user_id, created_at, status) 等顺序,但必须结合查询组合和执行计划权衡。
对索引列做函数或运算。 WHERE DATE(created_at) = '2024-01-01' 不利于使用 created_at 的索引范围,可改写为:
WHERE created_at >= '2024-01-01 00:00:00'
AND created_at < '2024-01-02 00:00:00'同理,WHERE user_id + 1 = 100 应尽量改写为对列本身的比较。
LIKE 使用前置通配符。 LIKE '%abc' 无法利用普通 B+Tree 索引的最左前缀定位;LIKE 'abc%' 才可能利用前缀。若联合索引中前面的列已经等值匹配,后面的列使用前缀匹配仍可能受益。
忽略排序造成的断点。 对索引 (user_id, status, created_at),WHERE user_id = 100 ORDER BY created_at 中间缺少 status,通常无法直接利用索引顺序完成排序。WHERE user_id = 100 ORDER BY status, created_at 更符合该索引顺序。是否需要为排序单独建索引,要看排序频率和返回行数。
OR 条件混用不同列。 WHERE user_id = 100 OR status = 1 往往难以让一个联合索引同时高效服务两个分支,可能出现全表扫描或索引合并。可以根据业务改用 UNION ALL 拆分,但要注意去重语义和额外排序成本。
类型和字符集不一致。 列与参数类型不一致、关联列字符集或排序规则不一致时,可能影响索引使用。应让参数类型与列定义匹配,并用 EXPLAIN 验证,而不是仅凭肉眼判断。
适用边界与排查清单
最左前缀原则是理解联合索引的基础,但不是唯一规则。出现以下情况时,需要更谨慎地下结论:
- 表很小或查询返回大部分数据时,全表扫描可能比索引更便宜。
- 条件选择性很低时,即使走了索引,也可能读很多行。
- 联合索引列越多,索引占用空间和写入维护成本越高,不应为了覆盖所有查询而无限加列。
- MySQL 5.7 没有索引跳跃扫描这类能力,缺少最左列时通常不能直接利用该联合索引定位;但覆盖索引扫描、索引条件下推等情况仍需结合
EXPLAIN判断。 - 统计信息可能过期,数据分布也可能倾斜;可在测试环境用接近真实的数据分布执行
ANALYZE TABLE后观察执行计划。 - 慢查询日志、
EXPLAIN和实际返回行数应结合使用。不要只因为key为空就认定索引无效,也不要只因为key有值就认定查询已经最优。
实际设计中,先明确高频查询模式,再决定联合索引的列顺序,最后用执行计划验证。最左前缀原则回答的是“索引能否被连续利用”,而不是“索引列越多越好”或“所有查询都必须走索引”。