大数据集下MySQL ROW_NUMBER()函数性能如何？索引能否优化？

一、大数据集下的性能表现 #

你给出的查询中，ROW_NUMBER() 是窗口函数，MySQL对这类函数的执行逻辑固定分为两步：

1. 先执行WHERE条件筛选，拿到所有符合other_col = 'value'的结果集
2. 对结果集按ORDER BY指定的ts_col、pk_col做排序，再依次计算行号

因为你的other_col基数极低，WHERE条件会筛选出大量数据，无索引的场景下性能会非常差：

• 第一步需要全表扫描，数据量越大扫描耗时越长
• 第二步排序如果内存不够，会触发磁盘临时表、文件排序（filesort），CPU、IO开销会陡增，千万级以上的表很容易出现查询超时，甚至拖垮整个实例的性能。

二、索引优化的效果和方案 #

添加合适的索引可以获得非常大的性能提升，最优方案是创建如下联合索引：

CREATE INDEX idx_opt ON 表名 (other_col, ts_col, pk_col);

这个索引的优化逻辑是：

• 首字段other_col匹配WHERE等值条件，可以直接定位到所有符合条件的行，完全避免全表扫描
• 后续ts_col、pk_col的顺序和窗口函数ORDER BY的顺序完全一致，InnoDB返回的行天然就是符合排序要求的有序结果，窗口函数计算行号时不需要再做额外排序，直接消除了filesort开销
• 如果你的查询不需要返回所有字段，可以把SELECT需要的字段追加到索引末尾，做成覆盖索引，还可以省去回表查询整行数据的开销，性能还能进一步提升。

特殊场景注意事项 #

如果other_col='value'对应的行占整个表的比例超过80%，MySQL优化器可能会判定走索引回表的开销比全表扫描更高，此时可以用FORCE INDEX强制走索引，或者在业务允许的前提下添加LIMIT限制返回行数。

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