mysql中联合索引与查询效率的平衡策略

联合索引严格遵循最左前缀匹配原则：只有从最左列开始连续等值匹配时索引才生效；例如索引(user_id, status, created_at)，WHERE user_id=123可命中，而仅WHERE status=1则无法使用。

联合索引的最左前缀匹配到底怎么生效

mysql 的联合索引不是“只要字段在索引里就能用”，而是严格按定义顺序逐列匹配。比如建了 INDEX idx_user_status_time (user_id, status, created_at)，只有当 WHERE 条件中出现 user_id（或含 user_id 的等值条件）时，索引才可能被使用；如果只查 status = 1 或 created_at > '2024-01-01'，这个索引基本无效。

常见错误是以为“覆盖了查询字段就行”，结果 EXPLaiN 显示 type: ALL 或 key: NULL。真正起效的组合包括：

• WHERE user_id = 123
• WHERE user_id = 123 AND status = 1
• WHERE user_id = 123 AND status IN (1,2) AND created_at > '2024-01-01'
• WHERE user_id = 123 AND status = 1 ORDER BY created_at LIMIT 10（可用于排序优化）

但 WHERE status = 1 AND created_at > '2024-01-01' 就无法走该索引——因为跳过了最左列 user_id。

等值查询 + 范围查询的顺序不能乱

在联合索引中，等值条件（=、IN）应尽量放在左侧，范围条件（>、、BETWEEN、LIKE 'abc%'）靠右。一旦出现范围查询，其右侧所有字段就无法用于索引查找（但仍可能参与索引覆盖）。

例如索引 (a, b, c)：

• WHERE a = 1 AND b > 10 AND c = 5 → 只能用上 a 和 b，c 不参与查找（但若 select c，仍可从索引中直接读取，即“覆盖索引”）
• WHERE a = 1 AND b = 5 AND c > 100 → 三列都可用于查找，效率最高
• WHERE a > 1 AND b = 5 → 实际只用到 a，b 完全失效

所以建索引前要预判高频查询模式：如果常查 user_id = ? AND created_at > ?，那 (user_id, created_at) 比 (created_at, user_id) 合理得多。

什么时候该拆联合索引，而不是堆字段

联合索引不是字段越多越好。每多一列，索引体积增大、写入开销上升、缓存命中率下降。尤其当某列选择性极低（如 status 只有 0/1）、或很少单独出现在 WHERE 中时，加进去反而拖累整体性能。

判断是否保留某列进联合索引，看三点：

• 该列是否在 WHERE 中与左侧列构成高频等值组合？
• 该列是否显著提升 rows 预估精度（用 EXPLAIN forMAT=jsON 查 used_columns 和 range_analysis）？
• 该列是否让常见查询实现“索引覆盖”（即 Extra 出现 using index）？

比如表有 user_id、tenant_id、status、created_at，若 90% 查询都是 tenant_id = ? AND user_id = ?，那优先建 (tenant_id, user_id)；若还有大量 SELECT status, created_at 也走这个条件，再考虑扩展为 (tenant_id, user_id, status, created_at) ——但得先确认 status 和 created_at 的区分度够不够支撑这额外开销。

ORDER BY 和 GROUP BY 如何借力联合索引

MySQL 能利用联合索引避免文件排序（Using filesort），前提是 ORDER BY 字段顺序与索引最左前缀一致，且不混用 ASC/DESC（8.0+ 支持混合，但需显式声明）。

例如索引 (user_id, created_at)：

• ORDER BY user_id, created_at → ✅ 无 filesort
• ORDER BY user_id DESC, created_at DESC → ✅（需索引定义为 KEY idx (user_id DESC, created_at DESC)）
• ORDER BY user_id ASC, created_at DESC → ❌ filesort（除非 MySQL 8.0+ 且索引明确定义了混合方向）
• ORDER BY created_at → ❌ 无法利用（跳过最左列）

GROUP BY 同理。如果常做 GROUP BY user_id, status 并聚合统计，建 (user_id, status) 索引后，EXPLAIN 中的 Extra 会显示 Using index for group-by，比临时表快得多。

EXPLAIN SELECT user_id, status, COUNT(*)  FROM orders  WHERE user_id IN (101, 102, 103)  GROUP BY user_id, status;

实际调优中最容易被忽略的是：索引字段顺序必须和 GROUP BY / ORDER BY 的列顺序完全一致，差一个位置，优化器就放弃使用。