MySQL 教程：联合索引与最左前缀匹配详解

前言#

联合索引是最容易被误用的东西：明明建了 KEY (a, b, c)，某些查询却偏偏不走。原因几乎都出在"最左前缀"这条规则上，这篇用一张表把它讲透。

CREATE TABLE article (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  status    TINYINT NOT NULL DEFAULT 0,
  category  INT     NOT NULL DEFAULT 0,
  views     INT     NOT NULL DEFAULT 0,
  title     VARCHAR(200) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY idx_s_c_v (status, category, views)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

什么是最左前缀#

idx_s_c_v 里的数据先按 status 有序，status 相同的再按 category 有序，category 再相同的按 views 有序。后面的列只在前面列相等的前提下才有序，所以能用上的条件必须从最左列开始、连续不断：

MySQL 5.7 没有跳跃扫描（skip scan 是 8.0.13 才加的），缺最左列时这个索引基本帮不上忙。

范围条件之后就不再匹配#

EXPLAIN SELECT id FROM article WHERE status = 1 AND category > 3 AND views = 100;

status 是等值，到 category 变成范围，索引里 category 之后的 views 就不再全局有序，所以 views = 100 不能用来定位。从 5.6 起有索引条件下推（ICP），存储引擎可以在索引层先把 views = 100 过滤掉再回表，Extra 里显示 Using index condition，省下无效回表，但扫描区间并不会变小。

ORDER BY 同样吃最左前缀#

SELECT id FROM article WHERE status = 1 ORDER BY category, views LIMIT 10;

WHERE 用掉 status，ORDER BY 正好接上后面两列，索引顺序就是结果顺序，执行计划里不会有 Using filesort。排序写成 ORDER BY views, category 就只能额外排序；写成 ORDER BY category ASC, views DESC 也一样不行，5.7 的索引只能单向遍历，降序索引要到 8.0 才支持。

列顺序怎么排#

• 等值条件放前面，范围条件放最后，避免范围列把后面的列废掉
• 全是等值条件时，把区分度高的列往前放，过滤得更早
• 最常被单独拿来做条件的列放最左，同一个索引能被更多查询复用
• 尾部列只有在凑成覆盖索引（查询要的列都在索引里，不用回表）时才有意义

几种把索引写废的写法#

-- 在列上套函数，索引顺序被打乱
WHERE YEAR(create_time) = 2017
-- 隐式类型转换：code 是 varchar 却传了数字
WHERE code = 12345
-- 前置通配符无法确定起点
WHERE title LIKE '%mysql%'

第一条改成范围条件 create_time >= '2017-01-01' AND create_time < '2018-01-01'；第二条把参数补上引号写成 '12345'；LIKE 'mysql%' 这种右模糊仍然可以走索引。

用 key_len 验证用了几列#

改完 SQL 不要凭感觉，EXPLAIN 的 key_len 直接给出命中的字节数，对照列类型就知道前缀吃到第几列：status 是 TINYINT 非空算 1，加上 category（INT 非空）是 5，再加 views 是 9；列允许 NULL 时每列还要多算一个字节的空值标记。这条判断在联合索引有四五个列时特别有用，光看 WHERE 里写了几个条件很容易误判。

小结#

• 联合索引的本质是多层排序，只有从最左列开始连续使用才有顺序可利用
• 遇到范围条件这条链路就断了，后面的列只能靠 ICP 做过滤
• 排序和查询共用同一套最左前缀规则，要把 WHERE 和 ORDER BY 连起来看
• 建索引前先想清楚最常出现的条件组合，而不是把列随便堆在一起