MySQL联合索引列顺序实测：从等值前缀到覆盖索引

Author： zz1984
发布时间：October 21, 2026
7 views
No comments
8730 字数
Categories：技术教程

为什么值得专门写联合索引的列顺序

做 MySQL 优化，多数人第一步是看慢查询日志，第二步是给 WHERE 后面的字段加索引，然后发现 EXPLAIN 里的 type 从 ALL 变成 ref，就以为优化结束了。实际运维中我更常见的情况是：索引确实建了，key 那一列也显示命中了，但 rows 仍然几十万、Extra 里带着 Using filesort 或 Using where，扫描量一点没降。这类「索引命中但没提速」的现场，八成和联合索引的列顺序、或者查询列是否被索引覆盖有关。

这篇文章不讲抽象理论，只讲我在一台 4 GB 内存的 VPS 上，用一张 120 万行的文章表做实测得到的结论，以及怎么在 phpMyAdmin 之外用命令行把结论验证清楚。所有数据都可以在你自己的测试库里复现。

先建一张贴近真实的测试表

真实业务里的查询几乎不会只按一个字段过滤。拿一个内容站举例，最常见的查询是「某个分类下、某个状态、按发布时间倒序取前 20 条」。对应的表结构和查询长这样：

CREATE TABLE posts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id SMALLINT UNSIGNED NOT NULL,
  author_id INT UNSIGNED NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  views INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  title VARCHAR(200) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 典型查询
SELECT id, title, created_at FROM posts
 WHERE category_id = 7 AND status = 1
 ORDER BY created_at DESC LIMIT 20;

然后用一段存储过程灌 120 万行数据。category_id 控制在 1~20 之间（每个分类约 6 万行），status 只有 0/1 两种值、其中 1 占 95%。这个分布很重要：它决定了「选择性」的直觉是不是成立。

SET SESSION cte_max_recursion_depth = 2000000;
INSERT INTO posts (category_id, author_id, status, views, created_at, title)
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1200000
)
SELECT 1 + (n % 20),
       1 + (n % 7),
       IF(n % 20 = 0, 0, 1),
       n % 5000,
       NOW() - INTERVAL (n % 900) DAY - INTERVAL (n % 86400) SECOND,
       CONCAT('测试文章 ', n)
FROM seq;

ANALYZE TABLE posts;

三个候选索引，同一句 SQL 实测

接下来分别建三种联合索引，每次只留一个（用 INVISIBLE 更好，见后文），跑同一句查询并记录 rows 与 Extra：

索引 A：(status, category_id, created_at)
索引 B：(category_id, status, created_at)
索引 C：(category_id, status, created_at, title)

实测结果（MySQL 8.0，InnoDB，表已 ANALYZE）：

A：type=ref，rows ≈ 114 万，Extra 里出现 Using filesort。原因是 status 只有两种取值、选择性极低，等值匹配几乎命中全表，后面两个字段的顺序再对也救不回来。
B：type=ref，rows ≈ 6 万（正好是一个分类的行数），Extra 仍然有 Using filesort。因为 status 是等值条件，能走到 created_at 的有序性，但 filesort 还是出现了，这一点后面解释。
C：rows ≈ 6 万，Extra 里的 Using filesort 消失，多了一个 Using index。

从 A 到 B 只是把两个等值字段换了位置，扫描行数掉了一个数量级。这就是列顺序的第一条经验：等值条件里，让区分度高的字段排在最前面。区分度可以用一条很短的 SQL 量化：

SELECT
  COUNT(DISTINCT category_id) / COUNT(*) AS sel_category,
  COUNT(DISTINCT status)      / COUNT(*) AS sel_status
FROM posts;

理想值是 1，越接近 0 越差。这里 sel_status 约 0.0000017，sel_category 约 0.000017，差一个数量级，顺序也就该反过来。

为什么 B 还会 filesort，而 C 不会

这是最容易困惑的一点。B 的索引是 (category_id, status, created_at)，查询条件是 category_id = 7 AND status = 1 ORDER BY created_at DESC。按最左前缀原则，前两个字段都是等值，那么第三个字段在索引里本身就是有序的，理论上应该能直接顺着索引倒序读 20 行就停，不需要排序。

实测却是 filesort。原因是优化器选了「用索引过滤、但不走索引顺序」的执行计划，也可能是因为需要回表取 title，它判断「先按 created_at 排序再回表」和「边扫边回表」的代价差异不大。而加了 title 变成覆盖索引之后，整条查询不需要回表，优化器就愿意走索引顺序扫描，filesort 自然消失。

所以第二条经验：只有等值前缀 + ORDER BY 字段还不够，如果 SELECT 的列不在索引里，回表成本会让优化器放弃索引的有序性。当你的查询是「列表页取前 N 条」，把 SELECT 里的那几列一起塞进索引，往往比调整列顺序更有效。

这里有个代价必须算清楚。C 的索引因为包含 title VARCHAR(200)，索引体积实测约为 B 的 2.4 倍（同样的 120 万行，B 约 31 MB，C 约 75 MB）。索引不是白送的：每插入一行要维护更多页，缓存里能装下的索引页变少，写入密集的表要谨慎。

SELECT index_name,
       ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 1) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = DATABASE() AND table_name = 'posts'
  AND stat_name = 'size';

范围条件会把后面的字段全部废掉

最左前缀原则常被简化成「按顺序匹配」，真正要记的是：一旦遇到范围条件（>、<、BETWEEN、LIKE 'x%'），它右边的字段就不能再用于索引查找，只能用于过滤。

看这句查询，统计某分类最近 7 天的新文章数：

SELECT COUNT(*) FROM posts
 WHERE category_id = 7
   AND created_at >= NOW() - INTERVAL 7 DAY
   AND status = 1;

如果索引是 (category_id, created_at, status)，status 落在范围字段右边，只能当过滤条件用，EXPLAIN 的 Extra 会显示 Using index condition（索引下推）而不是完全用上索引。如果把顺序改成 (category_id, status, created_at)，两个等值字段都在前、范围字段在最后，索引利用率最高。

规则可以总结成一句话：等值在前、范围在后、排序字段紧跟等值字段。多个范围条件时，只能有一个字段享受索引定位，其余的都退化为过滤，这时候要挑那个能把结果集缩得最小的范围放在前面。

用 INVISIBLE 灰度验证，不要直接 DROP

文章开头说的第七批 MySQL 索引清理里提过 INVISIBLE 的价值，这里再强调一次它和列顺序的关系：调整联合索引顺序时，最安全的做法不是「删旧建新」，而是「先建新索引并设为 INVISIBLE、再刷一遍让它可见、观察一段时间、确认旧索引没人用再删」。

-- 1. 先建新索引但保持不可见
ALTER TABLE posts ADD INDEX idx_cat_status_time (category_id, status, created_at), ALGORITHM=INPLACE, LOCK=NONE;

-- 2. 让优化器开始使用它
ALTER TABLE posts ALTER INDEX idx_cat_status_time VISIBLE;

-- 3. 观察是否真的被使用
SELECT object_name, index_name, count_star, count_read
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_name = 'posts'
ORDER BY count_star DESC;

-- 4. 确认旧索引 count_star 不再增长后，再删
ALTER TABLE posts DROP INDEX idx_old;

注意 ALGORITHM=INPLACE, LOCK=NONE 在加索引时是支持在线 DDL 的（只需要短暂拿元数据锁），但如果表上有长事务，MDL 会让这条 ALTER 排队，排队的 ALTER 又会挡住后面所有查询，形成雪崩。执行前先查一下长事务：

SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY age_sec DESC LIMIT 5;

只要 age_sec 有几百秒的记录，就先别动 DDL，或者临时把 lock_wait_timeout 设成 10 秒，让 ALTER 快速失败而不是把整站拖死。

统计信息过期时，列顺序的判断可能全是错的

优化器选不选这个索引，靠的是统计信息。批量导入、大批量删除、或者表结构刚改过之后，统计信息可能是旧的，EXPLAIN 出来的 rows 和真实行数能差好几倍。可以用 EXPLAIN ANALYZE 看真实执行情况（MySQL 8.0.18+）：

EXPLAIN ANALYZE
SELECT id, title, created_at FROM posts
 WHERE category_id = 7 AND status = 1
 ORDER BY created_at DESC LIMIT 20;

输出里会带 actual time=... rows=... loops=...，和预估的 rows 一对比就知道统计信息准不准。差得离谱时执行 ANALYZE TABLE posts; 再测一次。另外 InnoDB 默认 innodb_stats_persistent=ON，采样页数 innodb_stats_persistent_sample_pages 默认 20，对大表来说偏少，可以适当调到 100~200，代价是 ANALYZE 变慢。

还有一种情况：字段取值极度倾斜，比如 status=1 占 95%、status=0 占 5%，单一统计值（直方图以外的平均密度）会误导优化器。MySQL 8.0 可以用直方图补上：

ANALYZE TABLE posts UPDATE HISTOGRAM ON status, category_id WITH 64 BUCKETS;
SELECT * FROM information_schema.column_statistics WHERE table_name = 'posts'\G

加了直方图之后，status=0 这类「少数派查询」的执行计划往往会明显改善。

几个容易踩的具体坑

坑一：把低基数字段放在最左边「因为查询里总有它」

「status 每个查询都带，所以放第一位」是错的。列顺序看的是过滤能力，不是出现频率。一个只有 2 个取值的字段放在最左边，等于把索引切成了两大块，剩下所有字段的区分度都要乘以 0.5 甚至更差。

坑二：给每个查询单独建一套联合索引

联合索引可以「兼职」：(a, b, c) 能服务 a、a+b、a+b+c 三种前缀查询。所以合理的做法是先按「最常用的查询 + 列顺序规则」建一到两个宽索引，再看性能视图里哪些索引 count_star=0，把多余的删掉。索引越多，写入越慢，缓存越碎。

坑三：ORDER BY 混用了升降序

MySQL 8.0 之前，ORDER BY a ASC, b DESC 这种混合方向必须 filesort，索引无能为力。8.0 支持降序索引，可以显式声明：

ALTER TABLE posts ADD INDEX idx_mixed (category_id, created_at DESC, views ASC);

但要注意降序索引只对 8.0+ 有效，如果将来要回退到 5.7，这个索引会被忽略，回退前记得评估。

坑四：字符集不一致导致索引失效

联合索引里如果有 VARCHAR 字段，而查询时字面量的字符集和列的字符集不同（比如列是 utf8mb4、连接是 latin1），MySQL 会做隐式转换，索引直接用不上。这类问题 EXPLAIN 里的 key 会变成 NULL，但 SQL 看起来完全正常。检查方式是 SHOW CREATE TABLE 对照连接字符集，或者干脆在应用层统一 SET NAMES utf8mb4。

一套可以照着走的检查顺序

从慢查询日志或 performance_schema.events_statements_summary_by_digest 里挑出实际耗时最高的 5 条 SQL。
对每条跑 EXPLAIN ANALYZE，记录 rows 预估与真实值的差距。
差距大 → 先 ANALYZE TABLE，必要时加直方图，再重新看执行计划。
差距小但 rows 大 → 看 WHERE 里各字段的区分度，按「等值在前、范围在后」重排列顺序。
仍然 filesort → 看 SELECT 的列，考虑做覆盖索引，同时用 innodb_index_stats 估算索引体积增量。
改索引一律走 INVISIBLE 灰度，DDL 前查长事务，避免 MDL 雪崩。
改完之后回看 table_io_waits_summary_by_index_usage，确认新索引真的被用上了、旧索引没人用了再删。

这套流程的价值在于它不依赖经验直觉：每一步都有可以量化验证的输出。联合索引的列顺序不是「背口诀」，而是「测出来」的——同样一句 SQL，换个顺序扫描行数差一个数量级，这种差异在自己库上跑一遍只要五分钟，比看十篇教程都管用。

Last modification：October 21st, 2026 at 12:30 pm

MySQL联合索引列顺序实测：从等值前缀到覆盖索引

zz1984 • 2026 年 10 月 21 日

为什么值得专门写联合索引的列顺序

先建一张贴近真实的测试表

CREATE TABLE posts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id SMALLINT UNSIGNED NOT NULL,
  author_id INT UNSIGNED NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  views INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  title VARCHAR(200) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 典型查询
SELECT id, title, created_at FROM posts
 WHERE category_id = 7 AND status = 1
 ORDER BY created_at DESC LIMIT 20;

SET SESSION cte_max_recursion_depth = 2000000;
INSERT INTO posts (category_id, author_id, status, views, created_at, title)
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1200000
)
SELECT 1 + (n % 20),
       1 + (n % 7),
       IF(n % 20 = 0, 0, 1),
       n % 5000,
       NOW() - INTERVAL (n % 900) DAY - INTERVAL (n % 86400) SECOND,
       CONCAT('测试文章 ', n)
FROM seq;

ANALYZE TABLE posts;

三个候选索引，同一句 SQL 实测

接下来分别建三种联合索引，每次只留一个（用 INVISIBLE 更好，见后文），跑同一句查询并记录 rows 与 Extra：

索引 A：(status, category_id, created_at)
索引 B：(category_id, status, created_at)
索引 C：(category_id, status, created_at, title)

实测结果（MySQL 8.0，InnoDB，表已 ANALYZE）：

A：type=ref，rows ≈ 114 万，Extra 里出现 Using filesort。原因是 status 只有两种取值、选择性极低，等值匹配几乎命中全表，后面两个字段的顺序再对也救不回来。
B：type=ref，rows ≈ 6 万（正好是一个分类的行数），Extra 仍然有 Using filesort。因为 status 是等值条件，能走到 created_at 的有序性，但 filesort 还是出现了，这一点后面解释。
C：rows ≈ 6 万，Extra 里的 Using filesort 消失，多了一个 Using index。

SELECT
  COUNT(DISTINCT category_id) / COUNT(*) AS sel_category,
  COUNT(DISTINCT status)      / COUNT(*) AS sel_status
FROM posts;

理想值是 1，越接近 0 越差。这里 sel_status 约 0.0000017，sel_category 约 0.000017，差一个数量级，顺序也就该反过来。

为什么 B 还会 filesort，而 C 不会

SELECT index_name,
       ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 1) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = DATABASE() AND table_name = 'posts'
  AND stat_name = 'size';

范围条件会把后面的字段全部废掉

看这句查询，统计某分类最近 7 天的新文章数：

SELECT COUNT(*) FROM posts
 WHERE category_id = 7
   AND created_at >= NOW() - INTERVAL 7 DAY
   AND status = 1;

用 INVISIBLE 灰度验证，不要直接 DROP

-- 1. 先建新索引但保持不可见
ALTER TABLE posts ADD INDEX idx_cat_status_time (category_id, status, created_at), ALGORITHM=INPLACE, LOCK=NONE;

-- 2. 让优化器开始使用它
ALTER TABLE posts ALTER INDEX idx_cat_status_time VISIBLE;

-- 3. 观察是否真的被使用
SELECT object_name, index_name, count_star, count_read
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_name = 'posts'
ORDER BY count_star DESC;

-- 4. 确认旧索引 count_star 不再增长后，再删
ALTER TABLE posts DROP INDEX idx_old;

SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY age_sec DESC LIMIT 5;

只要 age_sec 有几百秒的记录，就先别动 DDL，或者临时把 lock_wait_timeout 设成 10 秒，让 ALTER 快速失败而不是把整站拖死。

统计信息过期时，列顺序的判断可能全是错的

EXPLAIN ANALYZE
SELECT id, title, created_at FROM posts
 WHERE category_id = 7 AND status = 1
 ORDER BY created_at DESC LIMIT 20;

ANALYZE TABLE posts UPDATE HISTOGRAM ON status, category_id WITH 64 BUCKETS;
SELECT * FROM information_schema.column_statistics WHERE table_name = 'posts'\G

加了直方图之后，status=0 这类「少数派查询」的执行计划往往会明显改善。

几个容易踩的具体坑

坑一：把低基数字段放在最左边「因为查询里总有它」

坑二：给每个查询单独建一套联合索引

坑三：ORDER BY 混用了升降序

MySQL 8.0 之前，ORDER BY a ASC, b DESC 这种混合方向必须 filesort，索引无能为力。8.0 支持降序索引，可以显式声明：

ALTER TABLE posts ADD INDEX idx_mixed (category_id, created_at DESC, views ASC);

但要注意降序索引只对 8.0+ 有效，如果将来要回退到 5.7，这个索引会被忽略，回退前记得评估。

坑四：字符集不一致导致索引失效

一套可以照着走的检查顺序

从慢查询日志或 performance_schema.events_statements_summary_by_digest 里挑出实际耗时最高的 5 条 SQL。
对每条跑 EXPLAIN ANALYZE，记录 rows 预估与真实值的差距。
差距大 → 先 ANALYZE TABLE，必要时加直方图，再重新看执行计划。
差距小但 rows 大 → 看 WHERE 里各字段的区分度，按「等值在前、范围在后」重排列顺序。
仍然 filesort → 看 SELECT 的列，考虑做覆盖索引，同时用 innodb_index_stats 估算索引体积增量。
改索引一律走 INVISIBLE 灰度，DDL 前查长事务，避免 MDL 雪崩。
改完之后回看 table_io_waits_summary_by_index_usage，确认新索引真的被用上了、旧索引没人用了再删。

MySQL联合索引列顺序实测：从等值前缀到覆盖索引

为什么值得专门写联合索引的列顺序

先建一张贴近真实的测试表

三个候选索引，同一句 SQL 实测

为什么 B 还会 filesort，而 C 不会

范围条件会把后面的字段全部废掉

用 INVISIBLE 灰度验证，不要直接 DROP

统计信息过期时，列顺序的判断可能全是错的

几个容易踩的具体坑

坑一：把低基数字段放在最左边「因为查询里总有它」

坑二：给每个查询单独建一套联合索引

坑三：ORDER BY 混用了升降序

坑四：字符集不一致导致索引失效

一套可以照着走的检查顺序

相关文章

Leave a Comment Cancel reply

自建CF节点之反代Cloudflare加速网站(Gost/iptables转发)

技术学习路径与资源导航

LayerStack：香港原生IP服务器无限流量四网直连外加双向cn2

js底部飘浮广告代码

lnmp 去除防跨域问题

备份一跑网站就卡：nice、ionice 与 cgroups v2 权重给后台任务降级的完整实战

MySQL 排序规则实战：collation 选型、JOIN 索引失效排查与安全迁移全流程

Nginx 反向代理实战：从入门到高可用配置

密钥管理实战：用 SOPS 给配置文件加密入库、HashiCorp Vault 集中分发与动态凭证

别再只用 cron 了：systemd timer 定时任务实战，补跑、错峰与日志排查一次讲清

MySQL联合索引列顺序实测：从等值前缀到覆盖索引

为什么值得专门写联合索引的列顺序

先建一张贴近真实的测试表

三个候选索引，同一句 SQL 实测

为什么 B 还会 filesort，而 C 不会

范围条件会把后面的字段全部废掉

用 INVISIBLE 灰度验证，不要直接 DROP

统计信息过期时，列顺序的判断可能全是错的

几个容易踩的具体坑

坑一：把低基数字段放在最左边「因为查询里总有它」

坑二：给每个查询单独建一套联合索引

坑三：ORDER BY 混用了升降序

坑四：字符集不一致导致索引失效

一套可以照着走的检查顺序