MySQL中避免全表扫描获取每组最后2行的优化建议咨询

咱们先拆解下你当前查询的问题：那个关联子查询(SELECT COUNT(*) ...)会让数据库对表中的每一行都重新遍历同组的记录来统计数量，相当于重复做全表扫描，效率自然上不去。下面给你两个高效的优化方案，核心是用更合理的SQL逻辑+索引来避免不必要的扫描。

方案一：用窗口函数实现一次扫描搞定 #

窗口函数是处理这类分组TopN问题的绝佳工具，它能在一次表扫描中完成分组、排序和编号，完全避免重复遍历。试试这个写法：

SELECT person, `group`, age
FROM (
    SELECT 
        person, 
        `group`, 
        age,
        -- 按group分组，每组内按age降序编号
        ROW_NUMBER() OVER (PARTITION BY `group` ORDER BY age DESC) AS row_rank
    FROM person
) AS ranked_data
WHERE row_rank <= 2
ORDER BY `group` ASC, age DESC;

这里的ROW_NUMBER()会给每个group里的记录按年龄从大到小分配一个序号，我们只需要筛选序号≤2的记录就行。整个过程只需要扫描一次表，然后在内存里完成分组排序，效率比原查询提升非常明显。另外注意group是SQL的关键字，所以要用反引号``括起来避免语法报错。

方案二：给原查询加合适的索引（兼容旧版本数据库） #

如果你的数据库版本不支持窗口函数（比如MySQL 5.7及以前），那可以通过创建复合索引来优化原查询，让它不再触发全表扫描。执行这条语句创建索引：

CREATE INDEX idx_group_age_desc ON person (`group`, age DESC);

这个索引是按group分组、再按age降序排列的复合索引。当数据库执行原查询的子查询时，它可以直接通过这个索引快速定位到同组的所有记录，并且直接统计年龄≥当前行的记录数，不需要再遍历整个表。

为什么这个索引有效？ #

索引本身已经帮我们完成了group的分组和age的排序，数据库不需要再对全表进行排序或扫描，直接利用索引就能完成计数逻辑，大幅减少IO开销。

总结 #

优先推荐窗口函数的写法，代码更简洁易懂，效率也最高。如果必须保留原查询的逻辑，那一定要创建上面的复合索引——这是避免全表扫描的关键。

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