MySQL 索引设计实战：联合索引与最左前缀

最左前缀：B+ 树的排序决定了一切

InnoDB 的普通索引是 B+ 树，节点里的键值是按「整行索引列的拼接比较结果」排序的。也就是说，索引 `(a, b, c)` 在树里的物理顺序是先比 a，a 相同时再比 b，a 和 b 都相同时再比 c。这个排序是词典序，一旦 a 不同，后面两列就完全没机会参与比较了。

最左前缀规则正是这个排序的直接推论：只有从第一列开始、连续地指定条件，优化器才能构造出一个「有边界的区间」交给 B+ 树做定位。`WHERE a = 1` 可以定位到 a=1 的整段；`WHERE a = 1 AND b = 2` 可以继续收窄到更小的一段；一旦跳过 a 直接问 `WHERE b = 2`，条件就变成了「在整棵树的所有叶子上，b 等于 2 的那些」，此时 B+ 树完全没有可用的有序性，只能全表扫描。

理解成「字典查词」最直观：`(张, 三, 王, 小, 军)` 这五个字按字典序排列，能高效查到「张、王、小、军」，因为你知道前三字一定落在哪个范围。查「小、军」是查不出来的，因为不知道「张」和「王」是什么，就无法确定起点。

范围条件是另一个常被误解的点。`(a, b, c)` 上写 `WHERE a = 1 AND b > 5 AND c = 3`，索引对 a 和 b 有效，但 c 不能用于继续缩小扫描区间——因为 b 的取值在 2 到 100 之间分散在许多段上，c 在每一段内部都仍然有序，但这些段不连续，无法合成一个区间。MySQL 的做法是把 a、b 走索引定位，把剩下的 c 作为索引条件下推的过滤条件（Index Condition Pushdown）在索引层做二次筛选，而不是回表后再过滤。

EXPLAIN FORMAT=JSON
SELECT id, name, created_at
FROM orders
WHERE user_id = 1001
  AND status = 1
  AND created_at > '2026-01-01'
  AND channel = 'app';

-- key        : idx_user_status_time  -> a、b 定位区间，c(实际是第四列) 作为 ICP 过滤
-- key_parts  : user_id, status       -> 注意 created_at 之后的列不出现在 key_parts 里
-- Extra      : "Using index condition"  表示条件下推生效，减少了回表次数

列顺序取舍：等值在前、范围在后、排序列特殊处理

定顺序时第一条原则是：把等值条件（`=`、`IN`）的列放在前面，范围条件的列放在最后。这不是经验之谈，而是由 B+ 树的词典序决定的。等值条件相当于把树切成若干个确定的小区间，范围条件必须出现在所有等值条件之后，才能利用前面已经确定的前缀形成一个连续可定位的区间。反过来写 `WHERE b > 5 AND a = 1`，虽然语义等价，但优化器未必能构造出理想的区间。

第二个考虑是选择性。把选择性高的列（区分度大、重复值少）放在前面，能让索引在第一层就砍掉大部分数据。反例是把 `gender`、`is_deleted` 这类区分度极低的列放在最前面：它们取值只有两三个，等于在 B+ 树的根节点分叉处几乎没有过滤能力，只是白白占了一个索引前缀，还会让后面所有列的对齐都变成负担。

但选择性不是唯一标准，实际取舍要平衡三点：等值与范围的先后、区分度高低、以及排序列的位置。举一个典型的三列场景：查询是「某个用户（user_id 等值）最近 30 天（created_at 范围）的订单，按金额（amount）倒序返回」。这时索引是 `idx(user_id, amount, created_at)` 还是 `idx(user_id, created_at, amount)`？

答案是后者。原因是「范围 + 排序」这个组合的优先级高于「等值 + 排序」：把 created_at 放在 amount 前面，索引内部本身就是按 created_at 有序的，取出用户和时间窗口内的数据后，天然已经按 created_at 排好序，几乎不需要额外排序；而 amount 因为在范围条件之后就无法用于排序了，只能作为回表后的过滤条件。反过来写，amount 虽然用上了排序，但 created_at 无法形成范围区间，得扫描该用户全部历史订单再过滤时间，代价可能高几个数量级。实践中真正的做法是先建 `(user_id, created_at, amount)` 覆盖这个主查询，再按需补一个 `(user_id, amount)` 给「按金额排序不分时间」的次要查询。

-- 主查询：用户 + 时间范围 + 按金额倒序
EXPLAIN SELECT id, amount FROM orders
WHERE user_id = 1001 AND created_at >= NOW() - INTERVAL 30 DAY
ORDER BY amount DESC LIMIT 20;

-- 推荐索引：范围列在前，排序靠索引有序性天然满足
ALTER TABLE orders ADD INDEX idx_user_time_amount (user_id, created_at, amount);

-- 辅助查询：用户 + 按金额排序，不限时间
ALTER TABLE orders ADD INDEX idx_user_amount (user_id, amount);

-- 对比：把排序列放在范围列之前的写法，Extra 里会多出 Using filesort
ALTER TABLE orders ADD INDEX idx_user_amount_time (user_id, amount, created_at);

覆盖索引：让查询根本不回表

InnoDB 的二级索引叶子节点存的是「索引列的值 + 主键值」，而不是整行数据。所以查 `SELECT *` 时，凡是索引里没有的列，都必须拿着主键再回聚簇索引取一次，这就是回表。回表是随机的（聚簇索引按主键物理组织，而主键来自二级索引，顺序不一致），大量的随机 IO 会让本来很快的查询慢上几倍。

覆盖索引的做法是：把所有需要读出来的列都塞进索引里，让查询在最左前缀匹配之后，需要的列全部命中索引，于是 `Extra` 字段出现 `Using index`，完全不需要回表。注意条件同样重要：覆盖的不仅是 SELECT 列表，WHERE、JOIN、ORDER BY 里用到的列也要算进去，漏掉一个就前功尽弃。

代价是索引体积。索引越大，缓存中能容纳的索引页越少，插入时的维护成本也越高，还会拖慢写入。所以覆盖索引应当针对「高频 + 列数不多」的查询来做。一个经验判断是：如果一个查询的 SELECT 列表超过了七八列，或者包含了 TEXT、BLOB 这类大字段，就已经不太适合做覆盖索引了，不如老实接受一次回表。

覆盖索引还有一个非常实用的副产品：它能让原本的「索引失效」重新变得可用。典型场景是范围条件之后那些无法用于定位的列——在 `WHERE a = 1 AND b > 5 AND c = 3` 里，c 本来只能做过滤。但如果索引是 `(a, b, c, d)` 而查询只 `SELECT c, d`，c 和 d 都在索引里，MySQL 就不需要为了拿 c 而回表，于是 c 变成了「顺带读出来」的列，过滤成本和回表成本一起消失了。这也是为什么先看查询列、再定索引列，而不是反过来。

-- 覆盖索引：SELECT 列表 + WHERE 条件全部落在索引内
ALTER TABLE orders ADD INDEX idx_cover (user_id, status, created_at, amount);

EXPLAIN SELECT amount, created_at FROM orders
WHERE user_id = 1001 AND status = 1 AND created_at > '2026-06-01';
-- type   : ref
-- Extra : "Using index"   -> 未回表

-- 反例：多查一列，覆盖失效，立刻出现 Using index condition
EXPLAIN SELECT amount, created_at, remark FROM orders
WHERE user_id = 1001 AND status = 1 AND created_at > '2026-06-01';
-- Extra : "Using where; Using index condition"   -> 需要回表读 remark

-- 验证索引列顺序是否符合预期（key_parts 即按序匹配到的索引列）
EXPLAIN FORMAT=JSON SELECT amount FROM orders
WHERE user_id = 1001 AND status = 1 AND created_at > '2026-06-01';

索引失效：真实场景与错误直觉

最经典的一类是「在索引列上套函数或参与表达式」。写成 `WHERE YEAR(created_at) = 2026` 时，索引列上包了一层函数，B+ 树的有序性就断了，优化器只能全表扫描。正确做法是把运算移到常量侧：改成 `created_at >= '2026-01-01' AND created_at < '2027-01-01'`，范围仍然是单调的，索引照常生效。同样的道理适用于对索引列做算术、类型转换和字符串拼接。

第二类是隐式类型转换。这一条几乎所有人都以为不会发生，但它是 MySQL 中文社区里最高频的索引失效原因。当比较两侧类型不一致时，MySQL 会把其中一侧转换掉，而转换的方向往往对索引不利。典型例子：手机号列 `phone` 是 `varchar`，而查询条件里传了数字 `WHERE phone = 13800138000`，字符串列被转成数值，索引随之失效。解决办法是让类型对齐，参数以字符串形式传入；反过来如果列是数值类型而参数是带引号的字符串，转换可能发生在常量侧，此时索引仍然可用。

第三类是前导通配符。`LIKE '%abc'` 无法定位起点，必须全表扫描；但 `LIKE 'abc%'` 可以，因为前缀确定，等价于范围条件。`LIKE '%abc%'` 同理不可用。如果确实需要任意位置匹配，全文索引或者专门的检索方案才是正解，不要指望 B+ 树索引。

第四类是字符集或排序规则的错配。连接两张表时，如果两张表上同一列的字符集不同（比如一张是 `utf8mb4`、另一张是 `latin1`），MySQL 无法直接比较两个索引值做索引合并，只能把其中一侧转换，结果就是索引失效。规范的做法是全库统一字符集和 collation，并且避免在 JOIN 键上使用不同 collation。

还有几个需要澄清的「伪失效」：隐式转换不一定每次都发生（方向决定了转换对象）；`!=` 和 `NOT IN` 通常确实走不了索引但如果选择性极高全表扫描反而更快；类型不匹配的 JOIN 条件也有可能通过索引合并优化走两个索引，但 `EXPLAIN` 里需要明确看到 `Using index merge` 才算真的走了。判断的唯一标准永远是看执行计划，不要靠推理。

-- ① 函数包裹索引列 -> 全表扫描
EXPLAIN SELECT id FROM orders WHERE YEAR(created_at) = 2026;
-- type: ALL, key: NULL

-- 改写：运算移到常量侧，索引恢复
EXPLAIN SELECT id FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- type: range, key: idx_user_time_amount

-- ② 隐式类型转换：varchar 列与数字比较
EXPLAIN SELECT id FROM users WHERE phone = 13800138000;
-- type: ALL（字符串列被转为数值）
EXPLAIN SELECT id FROM users WHERE phone = '13800138000';
-- type: ref, key: idx_phone

-- ③ 前导通配符
EXPLAIN SELECT id FROM orders WHERE remark LIKE '%refund%';   -- type: ALL
EXPLAIN SELECT id FROM orders WHERE remark LIKE 'refund%';   -- type: range

-- ④ 字符集不一致的 JOIN
SELECT o.id, u.name FROM orders o
JOIN users u ON o.user_name = u.login_name
WHERE u.charset_col <> 'utf8mb4';   -- 排查是否存在跨 collation 连接

-- 通用体检：找出全表扫描占比高的语句
SELECT db, digest_text, count_star, sum_rows_examined, sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_rows_examined / NULLIF(sum_rows_sent, 0) > 100
ORDER BY sum_rows_examined DESC LIMIT 10;

设计与验证的工作流

第一步永远是收集真实查询，而不是凭想象设计索引。从 `performance_schema.events_statements_summary_by_digest` 里按执行次数和总耗时排序，找出 top N 的查询，把它们的 WHERE 条件、JOIN 条件、ORDER BY 和 SELECT 列整理成表。这些高频查询决定了索引的优先级——为了一条一天执行两次的查询加索引，不如先把那条每秒执行两千次的慢查询治好。

第二步是对每条高频查询先写 `EXPLAIN`，记录基线：type、possible_keys、key、key_len、rows、Extra 六个字段都要看。`key_len` 特别有信息量，它反映实际用到了索引的前几列——对比预期的前缀长度，就知道有多少列没能参与。`rows` 是估算的扫描行数，如果它和实际结果集差一个数量级，说明统计信息过期，需要 `ANALYZE TABLE`。

第三步是加索引，然后重新 `EXPLAIN` 对比。这一步不能省，因为优化器的选择可能与你的预期不同：比如你以为它会用 `(a, b)`，它实际选了一个选择性更差的单列索引，或者在两个候选索引之间摇摆。差异出现时先更新统计信息，再考虑用索引提示（`FORCE INDEX` / `USE INDEX` / `IGNORE INDEX`）临时纠正，并在注释里写清原因。

第四步是长期观察。索引不是一次建成就永远对的：数据分布会漂移，查询模式会变化，写入模式改变也会让某个索引的维护成本超过收益。建议每季度拉一次全表扫描和回表量最高的查询列表，对照索引清单做一次「删除与新增」评审——长期只增不删的索引集合，最终会让写入性能莫名其妙地退化，而根因往往无从追溯。

-- 基线 EXPLAIN 关键列
EXPLAIN FORMAT=JSON
SELECT id, amount FROM orders
WHERE user_id = 1001 AND created_at >= '2026-01-01';

-- 统计信息过期时刷新
ANALYZE TABLE orders;

-- 临时纠正优化器选择（务必在代码注释里写明原因与复核日期）
SELECT id, amount FROM orders FORCE INDEX (idx_user_time_amount)
WHERE user_id = 1001 AND created_at >= '2026-01-01';

-- 查索引实际使用情况（需开启 performance_schema 的 instruments）
SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0
ORDER BY object_schema, object_name;
-- count_star = 0 的索引即为长期未被使用，是删除候选