MySQL row_number()窗口函数分组TopN查询性能优化咨询

• 性能损耗核心确认：当前慢查询的根因是窗口函数计算阶段触发了全量聚合结果的临时表构建与文件排序，你现有的(dataId, category, name)联合索引已经覆盖了分组阶段的全表扫描需求，无法消除排序阶段的开销。
• 优先选择预计算方案：由于你需要创建无过滤条件的视图供后续多表关联使用，每次查询实时计算全量排名的投入产出比极低。可以通过新建汇总表、使用数据库物化视图的方式，定时预聚合dataId、category、name维度的计数结果，甚至直接预生成每个(dataId,category)分组下排名前5的名称数据，查询时直接读取预计算结果即可，完全规避实时分组、排序的开销，以当前40万行的数据规模，预计算任务的运行成本可以忽略。
• 缩小单次排序的数据集范围：你最终仅需要每个分组的前5条数据，不需要为全部26万行聚合结果计算排名。可以先提取去重后的全量(dataId,category)组合，再通过lateral join（MySQL 8.0.14及以上版本支持）或关联子查询的方式，针对单个分组单独执行分组、排序、取前N操作，将单次全量大排序拆分为多个小排序任务，既可以降低单次排序的内存占用，多数场景下也能避免临时表落盘带来的性能损耗。
• 调整数据库排序相关配置：检查tmp_table_size、max_heap_table_size、sort_buffer_size参数配置，确保内存临时表、排序缓冲区的容量足够容纳26万行的排序数据集，避免排序阶段内存不足触发临时表写磁盘，将磁盘排序替换为内存排序后，查询耗时会有明显下降。
• 替代写法性能验证：可以测试用户变量手动实现分区排名的写法，部分场景下该写法的临时表开销低于原生窗口函数，需要注意不同数据库版本下用户变量的求值顺序差异，保证排名逻辑符合预期。

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