MySql如何建立高效的复合索引？

高效复合索引是 MySQL 查询优化的核心，设计得当能让查询速度提升几个数量级。下面从原则、步骤到实战案例，系统梳理如何建立。

1. 核心设计原则

① 最左前缀原则

复合索引 (A, B, C) 相当于创建了：

(A)
(A, B)
(A, B, C) 三个索引。

只有查询条件从索引最左列开始并不跳过中间列时，索引才能生效。若跳过 A 直接查 B，索引会失效（或仅做索引全扫描，效率极低）。

② 等值条件在前，范围条件在后

WHERE A = ? AND B > ? AND C = ?
应将 = 的列放到最前，> < LIKE 'prefix%' 等范围条件放到后面。
推荐索引 (A, C, B) 或 (C, A, B)，让等值条件先命中，最后处理 B 的范围。
原因：遇到第一个范围条件后，其后的索引列将无法用于精确查找。

③ 区分度高的列尽量靠前

区分度（选择性） = 不重复行数 / 总行数，值越大过滤效果越好。
例如 status 只有 0/1 区分度低，user_id 区分度高。
在满足前两条原则的前提下，优先把区分度高的列放在前面，能更快缩小扫描范围。
但绝不能为区分度而违反“等值 vs 范围”的先后顺序。

④ 覆盖索引，避免回表

如果查询所需的全部列都包含在索引中，MySQL 只需扫描索引树，不用回表聚簇索引取数据，IO 大幅减少。
示例：查询 SELECT A, B FROM t WHERE A=1，索引 (A, B) 即为覆盖索引。
设计时可以用 EXPLAIN 确认 Extra 出现 Using index。

⑤ 将 ORDER BY / GROUP BY 列纳入索引

若查询有排序，可利用索引天然有序性消除 filesort：
WHERE A=1 ORDER BY B 可直接用 (A, B)，排序直接从索引获得，不需要额外排序。
注意：

排序字段的顺序必须与索引一致（ORDER BY B, C 就需要索引 (A, B, C)，且 A 在前面等值条件中）。
所有排序列的升降序最好一致（MySQL 8.0 支持降序索引可处理 ASC, DESC 混合，8.0 以下要特别注意方向一致性）。

2. 实战设计步骤

假设有订单表 orders：

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  user_id     BIGINT NOT NULL,
  status      TINYINT NOT NULL,
  amount      DECIMAL(10,2),
  create_time DATETIME NOT NULL,
  ...
);

常运行的 SQL：

SELECT user_id, amount, create_time
FROM orders
WHERE user_id = 123
  AND status IN (1,2)
ORDER BY create_time DESC
LIMIT 20;

步骤 1：识别等值与范围

等值：user_id = ?，status IN (...)（IN 对索引而言视同多个等值，但若有多个 IN 要小心）。
范围/排序：ORDER BY create_time DESC（可看作按顺序取数据）。
没有 create_time > 这种直接范围条件，排序可享受索引顺序。

步骤 2：排列索引列顺序

等值列放最左：user_id, status 都是等值（区别度 user_id 通常远高于 status） → (user_id, status, …)
随后放置排序字段：create_time → (user_id, status, create_time)
检查是否能建为覆盖索引：SELECT 还需要 amount，若加进索引会变成 (user_id, status, create_time, amount)，此时 Extra 可显示 Using index，减少回表。

最终高效索引：

ALTER TABLE orders ADD INDEX idx_usr_sts_ct_amt (user_id, status, create_time, amount);

步骤 3：用 EXPLAIN 验证

EXPLAIN SELECT user_id, amount, create_time
FROM orders
WHERE user_id = 123 AND status IN (1,2)
ORDER BY create_time DESC LIMIT 20;

关键字段期望：

key：idx_usr_sts_ct_amt
type：ref（或 range，此处等值+IN 通常为 range）
Extra：Using where; Using index（若覆盖索引），而且没有 Using filesort。

3. 常见误区与注意事项

把选择性最高的列无条件放最左
错误示例：WHERE A=1 AND B>10，若 B 选择性极高，但仍应建索引 (A, B) 而非 (B, A)。因为 B 是范围，放第一列后 A 的等值条件就用不上了。
用多个单列索引替代复合索引
INDEX(a), INDEX(b) 对 WHERE a=? AND b=? 只能走一个较好索引，效率远不如 (a,b)。5.0 后虽有 Index Merge，但通常仍是复合索引更稳定高效。
索引列上使用函数或运算
WHERE DATE(create_time) = '2024-01-01' 会导致索引失效，应改为 WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。
忽略排序方向导致 filesort
如果 ORDER BY a ASC, b DESC，需要 MySQL 8.0 的降序索引才能完全消除排序，老版本会用到 filesort。
索引过多
索引会拖慢写入（INSERT/UPDATE/DELETE），并占用空间。根据慢查询日志，仅针对高频、高消耗 SQL 建立索引，并定期清理冗余索引（比如已有 (A,B) 则 (A) 是冗余的）。

4. 进阶技巧：利用索引下推（ICP）

MySQL 5.6+ 支持 Index Condition Pushdown，即使部分索引列被范围条件打断，也能提前过滤掉不符合的行。
如索引 (A, B, C)，查询 WHERE A=1 AND B LIKE 'x%' AND C=2，虽 C 无法精确定位，但存储引擎会在索引中直接判断 C=2，减少回表。所以不用因为 C 在范围后就不放入索引，能加速 ICP。

总结：高效复合索引速查口诀