结论先行：同一条 SQL 在 1.8 亿行表上，「二级索引过滤 + 回表」P99 = 38ms，改成「覆盖索引 + 松散索引扫描（loose index scan）」P99 = 2.1ms——18 倍差距，SQL 一个字没改，只改了索引定义和查询写法。差距的本质不是 CPU，而是回表把「B+ 树顺序扫」变成了「聚簇索引随机跳」，在 Buffer Pool 装不下热集时，每一跳都是一次物理读。下面是一次完整的量化过程。
一、现象与背景
一套用户行为平台，核心表 t_user_event：
- 规模：1.82 亿行、单表 96GB、InnoDB、MySQL 8.0.32（16C64G，NVMe，
innodb_buffer_pool_size=48G） - 已有索引：聚簇
id+ 二级idx_uid_time (user_id, created_at)+ 二级idx_uid_time_amt (user_id, created_at, amount)（后一个是为这条查询后来补的覆盖索引） - 问题查询（对账报表服务，稳定 1200 QPS）：
SELECT user_id, COUNT(*), MAX(amount)
FROM t_user_event
WHERE user_id BETWEEN 1000000 AND 1099999
AND created_at >= '2026-09-01'
GROUP BY user_id;
- 监控数字：P50 = 12ms，P99 = 38ms，且每 5 分钟随对账任务批量跑一次时 P99 会进一步飙到 90~140ms；
Innodb_buffer_pool_reads（物理读）基线 ~200/s，这条查询执行期间跳到 6000~9000/s。
第一反应是「Buffer Pool 太小」。但 48G 装 96G 的表，点查主键 P99 只有 0.4ms——说明瓶颈不在内存总量，而在这条查询的访问模式。
二、原理：回表为什么是 10 倍的随机读
idx_uid_time只覆盖过滤列。优化器选它做 range 过滤，命中行数估 12~18 万行，然后每一行都要拿id回聚簇索引取amount——18 万次随机定位。聚簇索引按id物理有序，而user_id在叶子页上是逻辑序，回表跳转对 Buffer Pool 来说是近乎随机的页访问。- 随机页访问命中 LRU 时，冷页要经历 old 链 → young 链的晋升过程，期间物理读 + 页内解析 + 可能的二次淘汰连锁发生——这正是别人复盘过的「LRU 冲刷」问题：点查热页被偶发大扫描的页提前挤出 Buffer Pool，P99 立刻放大一个数量级。
loose index scan（松散索引扫描）是 InnoDB 对GROUP BY前缀的专门优化：如果GROUP BY的列恰好是某个二级索引的左前缀，且聚合函数是COUNT/AVG/MIN/MAX/SUM，优化器可以只沿着二级索引的 B+ 树做「跳到下一个不同的 user_id 值」的稀疏遍历——1.8 亿行里user_id BETWEEN ...的 10 万个用户，只要访问 10 万个「每个用户段的首尾位置」，而不是 18 万行。这是顺序 B+ 树访问，物理读几乎为零。index_merge为什么没救场：MySQL 8.0 默认loose_index_merge=ON，但 index_merge 的三种模式（sort_union/intersection/firstmatch）在「两个索引各命中一部分行再合并」时，合并本身要排序或建临时表，代价经常超过单索引全扫——index_merge 不是免费的路径优化，它是把「选哪个索引」的决策延后到了执行期。
一句话：回表 = 把「索引上的顺序扫」变成「聚簇索引上的随机跳」，而松散索引扫描 = 把「扫 18 万行」变成「跳 10 万个用户段」。两者差 10~20 倍是结构性的，不是调参数能抹平的。
三、具体做法（可复现）
第 1 步：确认走的是哪条路径。
EXPLAIN FORMAT=JSON
SELECT user_id, COUNT(*), MAX(amount)
FROM t_user_event
WHERE user_id BETWEEN 1000000 AND 1099999
AND created_at >= '2026-09-01'
GROUP BY user_id;
看 access_type 和 attached_condition：如果 key=idx_uid_time 且没有 Using index（覆盖标记），就是回表路径。
第 2 步：确认覆盖索引 + 松散索引扫描的条件都满足。
松散索引扫描的硬性条件（缺一条就退化成回表）：
GROUP BY列必须是某个二级索引的左前缀（user_id是idx_uid_time_amt的第一列 ✓）- 聚合函数只能是
COUNT(*), MIN, MAX, SUM, AVG（本查询COUNT + MAX✓） - 过滤条件不能破坏「按前缀分组」的语义（
user_id BETWEEN是前缀范围 ✓，created_at >=在第二列 ✓） - 不能同时有
ORDER BY非前缀列（否则优化器放弃松散扫描）
第 3 步：强制走覆盖索引验证，再放开。
-- 先 FORCE INDEX 验证收益
EXPLAIN
SELECT user_id, COUNT(*), MAX(amount)
FROM t_user_event FORCE INDEX (idx_uid_time_amt)
WHERE user_id BETWEEN 1000000 AND 1099999
AND created_at >= '2026-09-01'
GROUP BY user_id;
EXPLAIN 结果里 Extra 列出现 Using index for group-by（松散索引扫描的标记）+ Using index（覆盖），说明路径正确。
第 4 步：压测验证（JMeter，1200 QPS 恒定负载，各跑 30 分钟）。
| 指标 | 回表路径（idx_uid_time） | 松散扫描（idx_uid_time_amt） | |---|---|---| | P50 | 12ms | 1.4ms | | P99 | 38ms | 2.1ms | | P999 | 142ms | 8.7ms | | 物理读/s（执行期间） | 6000~9000 | 30~60 | | 服务侧 CPU | 38% | 11% |
第 5 步：收尾——删掉旧的 idx_uid_time（被覆盖索引完全包含），释放 9.8GB 磁盘 + Buffer Pool 常驻开销。
四、踩过的坑 / 反直觉之处
EXPLAIN的rows估算偏了 40 倍。优化器估 4500 行，实际 18 万行——因为innodb_stats_persistent_sample_pages=20采样页太少，user_id分布倾斜（头部 5% 用户占 60% 事件量）。教训：对倾斜分布的列，rows估算不可信，用EXPLAIN ANALYZE（8.0.18+）看实际r_rows：
EXPLAIN ANALYZE
SELECT user_id, COUNT(*), MAX(amount)
FROM t_user_event
WHERE user_id BETWEEN 1000000 AND 1099999
AND created_at >= '2026-09-01'
GROUP BY user_id;
输出里 rows_examined_per_analyzer=182340 才是真实值。
-
开了
loose_index_merge=ON（默认）反而更慢。有两条查询的index_merge(sort_union)路径 P99 比单索引全扫还高 3 倍——合并排序的代价被低估了。默认值不是最优值，index_merge 需要按 SQL 逐条验证，不能全局打开。 -
覆盖索引不是「加一列就完事」。
idx_uid_time (user_id, created_at)加amount变成idx_uid_time_amt后，写入路径的维护成本 +1 棵 B+ 树。用performance_schema.table_io_waits_summary_by_index_usage验证：该表 INSERT QPS ~2400，多一棵索引让写 P99 从 3.1ms 涨到 3.6ms（+16%）。读 18 倍收益 > 写 16% 代价，这笔账划算；但如果读 QPS 只有 200，这笔账就不划算了——覆盖索引的取舍是读写比的问题，不是「覆盖就快」。 -
Using index for group-by没出现，但Using index出现了。这是最常见的误判：Using index只代表「不用回表取 SELECT 列」（覆盖索引生效了），不代表松散索引扫描生效。两个标记要同时出现才算命中松散扫描路径。 -
松散扫描的 18x 是「MIN/MAX 短路」专属，SUM / COUNT(*) 会静默退化。感谢 @林寒山-171 补充的这条边界：松散扫描的收益来自「对每个 user_id 段只读首/尾条目即可求 MIN/MAX」的短路。一旦把
MAX(amount)改成SUM(amount)，或把COUNT(*)换成需要逐行计数的COUNT(amount)（非空计数），优化器就退回 tight index scan，收益从 18x 掉到 2~3x，但EXPLAIN的type依然是index，肉眼几乎看不出差别。更阴的是 SELECT 里混入一个非聚合、非 GROUP BY 列（如SELECT user_id, MAX(amount), created_at），松散扫描立刻失效、type 同样不变。物理层根因也和 LRU 一致：松散扫描读的是二级索引叶子页（比聚簇页更密、更小），对 Buffer Pool 冲刷压力天然比回表小。落法：SQL review 卡点「报表 SQL 只允许 MIN/MAX 聚合列」+ 报表服务启动时对每批 SQL 跑EXPLAIN FORMAT=JSON断言using_index_for_group_by == true，断言失败拒绝上线——比靠人眼 review 靠谱。
五、结论与可复用清单
结论：在「GROUP BY 前缀 + 范围过滤 + 简单聚合」这个高频报表模式下，覆盖索引 + 松散索引扫描是 InnoDB 提供的最快路径，比回表快 10~20 倍是结构性的，不是调参能弥补的。前提是 GROUP BY 列严格匹配索引左前缀，且聚合列只用可短路的 MIN/MAX。
可复用清单：
- 写报表 SQL 前先确认：
GROUP BY列能否做成某个二级索引的左前缀？能的话，把 SELECT 列也塞进这个索引（覆盖），强制松散扫描路径。 - EXPLAIN 里同时看
Using index和Using index for group-by，缺一个就说明松散扫描没生效，回退到回表路径。 - 倾斜分布的列，
EXPLAIN的rows不可信，用EXPLAIN ANALYZE看r_rows。 - index_merge 不是免费优化，按 SQL 逐条压测，不要全局默认打开。
- 覆盖索引有写税，用
performance_schema.table_io_waits_summary_by_index_usage量化每个索引的读写占比，再决定加不加。 - Buffer Pool 物理读飙升 + P99 放大 10 倍 + P50 没动，这个组合的排查顺序：先怀疑访问模式（回表/大扫描），再怀疑 Buffer Pool 大小。
- 松散扫描的 18x 依赖 MIN/MAX 短路：聚合列一旦改成 SUM / COUNT(非空列)，收益静默退化到 2~3x 且 EXPLAIN type 不变——上线前用
EXPLAIN FORMAT=JSON断言using_index_for_group_by兜底。