分页语句使用row_number引发的性能问题

背景今天给客户优化时发现客户在使用了分页语句中使用了row_number而引发了性能问题那客户是怎样使用row_number引发了性能问题在分页语句中如何处理我们来模拟实验下模拟这了减少复杂度我们用单表查询来模拟客户性能问题场景使用row_number获取排序序号order by 中使遥获取的序号rn来排序SELECT o_orderkey, o_custkey, o_orderstatus, row_number() over(ORDER BY o_orderkey) AS rn FROM orders ORDER BY rn LIMIT 10;分析我们通过执行计划来分析EXPLAINANALYZESELECTo_orderkey,o_custkey,o_orderstatus,row_number()over(ORDERBYo_orderkey)ASrnFROMordersORDERBYrnLIMIT10;QUERYPLAN------------------------------------------------------------------------------------------------------------------------------------------------------------Limit(cost944415.29..944415.32rows10width10)(actualtime31868.066..31868.068rows10loops1)-Sort(cost944415.29..963165.29rows7500000width10)(actualtime31868.063..31868.064rows10loops1)SortKey:(row_number()OVER(?))Sort Method:top-N heapsort Memory:25kB-WindowAgg(cost0.00..782342.99rows7500000width10)(actualtime5.350..30514.771rows7500000loops1)-IndexScanusingorders_pkeyonorders(cost0.00..669842.99rows7500000width10)(actualtime4.445..22712.714rows7500000loops1)Total runtime:31880.744ms(7rows)通过执行计划可以看到我们只需要返回10行而耗时30多秒 这是不合理的再细看执行计划发现这是扫描了全表的数据 (见执行计划里 rows7500000)我们只需要前面有效的10行数据能不能不扫描这么多行而现在这个语句又是什么了什么情况呢通过分析现有的PLAN可以看到实际在执行时是分为几步先把所有符合条件的数据都取出生成rn根据rn对结果排序排序好的数据取前10行与下面语句的PLAN是一样的 (因有了缓存下面执行时间会变短)EXPLAINANALYZESELECT*FROM(SELECTo_orderkey,o_custkey,o_orderstatus,row_number()over(ORDERBYo_orderkey)ASrnFROMorders)ORDERBYrnLIMIT10;QUERYPLAN-----------------------------------------------------------------------------------------------------------------------------------------------------------Limit(cost1019415.29..1019415.32rows10width18)(actualtime10535.425..10535.428rows10loops1)-Sort(cost1019415.29..1038165.29rows7500000width18)(actualtime10535.423..10535.424rows10loops1)SortKey:(row_number()OVER(?))Sort Method:top-N heapsort Memory:25kB-WindowAgg(cost0.00..782342.99rows7500000width10)(actualtime0.140..9302.427rows7500000loops1)-IndexScanusingorders_pkeyonorders(cost0.00..669842.99rows7500000width10)(actualtime0.112..5013.849rows7500000loops1)Total runtime:10550.207ms(7rows)优化分页语句的要点有两个1、 通过索引直接返回有序数据避免排序消耗2、 获取到需要的数据后停止扫描减少无用的扫描消耗我们改用常用的方式也就是直接根据原有列而row_number的结果来排序对比下前后效果EXPLAINANALYZESELECTo_orderkey,o_custkey,o_orderstatus,row_number()over(ORDERBYo_orderkey)ASrnFROMordersORDERBYo_orderkeyLIMIT10;QUERYPLAN---------------------------------------------------------------------------------------------------------------------------------------------Limit(cost0.00..1.04rows10width10)(actualtime0.191..0.216rows10loops1)-WindowAgg(cost0.00..782342.99rows7500000width10)(actualtime0.189..0.193rows10loops1)-IndexScanusingorders_pkeyonorders(cost0.00..669842.99rows7500000width10)(actualtime0.162..0.184rows11loops1)Total runtime:0.317ms(4rows)o_orderkey本身就是主键索引原始语句的PLAN中就已经可以看到(Index Scan using orders_pkey)所以这儿就不再展示表结构了改写后可以看到只访问了11行 (rows11) 而原来是 (rows7500000)因为返回的是有序数据所以改写后也少了 sort当然在该语句或类似语句城 row_number 已经没什么意义 我们可以改用 rownum 伪列来产生RNEXPLAINANALYZESELECTo_orderkey,o_custkey,o_orderstatus,rownumASrnFROMordersORDERBYo_orderkeyLIMIT10;QUERYPLAN---------------------------------------------------------------------------------------------------------------------------------------Limit(cost0.00..0.89rows10width10)(actualtime0.040..0.044rows10loops1)-IndexScanusingorders_pkeyonorders(cost0.00..669842.99rows7500000width10)(actualtime0.040..0.043rows10loops1)Total runtime:0.097ms(3rows)现在更减少了分析函数耗费的时间 (见前面的 WindowAgg)结论在磐维数据库中不要使用row_number会有全表扫描的风险要使用标准的分页模式