MySQL 5.7按分组高效取Top N行的索引优化问题

核心疑问解答 #

1. 你的预期完全正确 #

你创建的(groupable, orderable)联合索引完全可以满足「直接取分组内排序后前N条、不扫描全组」的需求，EXPLAIN返回的rows=8是优化器的预估值，并非实际执行时的扫描行数：

• MySQL 5.7的优化器在计算EXPLAIN的rows字段时，不会将LIMIT限制纳入预计算逻辑，只会统计匹配groupable=3条件的总行数（你的测试数据里刚好是8行），所以才会显示rows=8。
• 实际执行时，InnoDB的二级索引是有序存储的，test_index_1索引下，所有groupable=3的记录已经按orderable从小到大排序，引擎只需要顺着索引顺序取前2条就终止查询，实际仅扫描2行。

你可以通过执行以下语句验证实际扫描行数：

-- 先清空会话状态
FLUSH SESSION STATUS;
-- 执行目标查询
SELECT id FROM test WHERE groupable = 3 ORDER BY orderable LIMIT 2;
-- 查看实际读取行数
SHOW SESSION STATUS LIKE 'Handler_read_next';

返回的Handler_read_next值为2，和LIMIT数量一致，证明没有全组扫描。

2. 多分组取前N条的优化方案 #

针对groupable IN (3,4,5)这类多分组批量取前N条的场景，针对MySQL 5.7版本有两种无全表/全组扫描的实现方案：

方案1：UNION ALL拼接单分组查询（分组数量较少时最优）

如果IN范围内的分组值数量可控，直接拼接每个分组的独立查询，每个查询都走联合索引，总扫描行数为「分组数 * N」，效率最高：

(SELECT id FROM test WHERE groupable = 3 ORDER BY orderable LIMIT 2)
UNION ALL
(SELECT id FROM test WHERE groupable = 4 ORDER BY orderable LIMIT 2)
UNION ALL
(SELECT id FROM test WHERE groupable = 5 ORDER BY orderable LIMIT 2);

方案2：关联子查询（分组数量较多时适用）

如果分组值数量多，无法手动拼接UNION ALL，可以用关联子查询实现，同样依赖(groupable, orderable)联合索引避免全扫描：

SELECT t1.id
FROM test t1
WHERE t1.groupable IN (3,4,5)
AND (
    SELECT COUNT(*)
    FROM test t2
    WHERE t2.groupable = t1.groupable 
      AND t2.orderable < t1.orderable
) < 2; -- 此处数值对应要取的前N条

冗余索引清理建议 #

你当前创建的索引存在冗余，可删除无用索引降低写入开销：

• test_index_3 (orderable)：完全被test_index_2 (orderable, groupable)的前缀索引覆盖，无需单独保留
• test_index_4 (groupable)：完全被test_index_1 (groupable, orderable)的前缀索引覆盖，无需单独保留

内容的提问来源于stack exchange，提问作者Nikita Rybak