分页语句使用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会有全表扫描的风险要使用标准的分页模式

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

相关推荐

Blender接入Hyper3D Rodin的MCP协议实战指南

NVIDIA驱动回滚避坑指南：精准版本筛选与安全降级实战

JSP+MySQL个人记事本全解析：从Servlet到WAR部署实践

更多资讯

华为分阶段发布应用

有哪些科研工具

GlusterFS 集群部署记录文档

Atlas 300V 24G推理加速卡部署YOLO实战：模型转换与ACL推理全解析

肌电信号分类数据集与代码：从预处理到SVM/CNN的完整流水线

GO [ 映射表 ]

今日更新

AI元人文：从工具使用到思维重构的深度探索

Python+CNN车牌识别实战：从数据预处理到模型训练与部署

Vim基础操作全攻略：保存退出、模式切换与高频命令实战

本周精选

深入解析Transformer多头注意力机制与工程优化

OpenClaw 的 Skills 跑学习任务，模型通道改到 TaoToken 通道行不行？

ChatGPT报错Oops, an error occurred! 全链路排查指南

本月热门

基于YOLO的焊缝缺陷检测：从数据标注到工业部署全流程实践

分布式服务的决策记录

创业产品的首版范围

读完文章，想聊聊您的网站？