文章目录每日一句正能量1. 背景与问题2. 环境与数据3. 复现过程3.1 无索引情况下3.2 Top-N并不等于少量计算4. 方案实施4.1 建立排序方向匹配的索引4.2 联合条件下设计复合索引4.3 Top-N排序与内存4.4 参数调整5. 结果对比5.1 执行计划验证清单优化前优化后6. 风险与复盘风险一索引过多风险二排序字段频繁变化风险三分页深度问题风险四回退方案总结附录实验SQL每日一句正能量“最深的期待不是填满而是在心里精心留白。”我们常以为期待是渴望某种到来最深的那种是为未知留下神圣的空隙。像国画的留白不画满反而让山水有了呼吸。心里有白万物才能涌入。1. 背景与问题排行榜是互联网系统中最典型的Top-N业务例如商品销量Top100用户积分排名游戏战力榜新闻热榜风控规则排序。业务通常写成SELECTuser_id,scoreFROMuser_scoreORDERBYscoreDESCLIMIT100;很多开发人员认为只返回100行所以SQL一定很快。实际情况并非如此。如果没有合适索引数据库可能执行全表扫描 ↓ 读取百万行 ↓ 完整排序 ↓ 取前100真正消耗时间的是排序的数据量而不是最终返回的数据量Top-N优化核心目标让数据库尽可能早知道哪些数据排在前面而不是先把所有数据排序完成。2. 环境与数据测试环境KingbaseES 表 user_score 数据量 5000万行 字段 user_id score create_time status初始化CREATETABLEuser_score(user_idBIGINTPRIMARYKEY,scoreINTEGER,create_timeTIMESTAMP,statusINTEGER);排行榜查询SELECTuser_id,scoreFROMuser_scoreWHEREstatus1ORDERBYscoreDESCLIMIT100;重点观察Sort节点 执行时间 Buffers 临时文件3. 复现过程3.1 无索引情况下执行计划可能类似Limit | Sort | Seq Scan user_score问题Seq Scan读取5000万行 Sort处理大量数据 Limit最后才生效如果排序空间不足内存排序 ↓ 临时文件 ↓ 磁盘排序性能进一步下降。需要关注Sort Method Disk Usage Buffers read3.2 Top-N并不等于少量计算例如ORDERBYscoreDESCLIMIT10数据库仍然需要回答谁是最高的10个人如果不知道score顺序只能扫描更多数据。因此返回10行 ≠ 只处理10行4. 方案实施4.1 建立排序方向匹配的索引排行榜核心索引CREATEINDEXidx_score_rankONuser_score(scoreDESC);再次执行EXPLAINANALYZESELECTuser_id,scoreFROMuser_scoreWHEREstatus1ORDERBYscoreDESCLIMIT100;理想计划Limit | Index Scan变化原扫描5000万 排序5000万后沿索引读取前100附近数据4.2 联合条件下设计复合索引实际排行榜通常有过滤条件WHEREstatus1ORDERBYscoreDESCLIMIT100如果只有score索引数据库可能仍需要过滤大量无效行。更合理CREATEINDEXidx_status_scoreONuser_score(status,scoreDESC);原因索引顺序status分组 score排序可以减少扫描范围。4.3 Top-N排序与内存当无法完全利用索引时数据库可能使用Top-N Heap而不是完整排序。完整排序5000万行全部排序Top-N维护当前最大的100行内存压力明显下降。但注意如果N变大LIMIT1000000Top-N优势会下降。4.4 参数调整排序相关参数需要结合业务关注work_mem maintenance_work_mem 临时文件大小提高排序内存可能减少磁盘落盘。但是并发环境下单SQL排序内存 × 并发连接数可能造成内存压力。因此不能简单调大。5. 结果对比测试示例方案执行计划P95Buffer Read无索引Seq Scan Sort18s大量Top-N优化Top-N Heap6s降低排序索引Index Scan Limit120ms极低示例结果说明真正有效的优化通常来自减少排序输入而不是5.1 执行计划验证清单每次优化后保存EXPLAINANALYZE重点比较优化前Sort actual rows50000000优化后Index Scan actual rows≈100观察Execution Time Buffers Sort Method6. 风险与复盘风险一索引过多排行榜索引提升读取但是增加INSERT成本 UPDATE成本 存储空间需要评估写入压力。风险二排序字段频繁变化例如实时积分榜每秒大量更新score。索引维护成本可能明显增加。可考虑缓存排行榜定时汇总表异步计算。风险三分页深度问题很多系统LIMIT100OFFSET1000000会导致大量跳过。优化方式使用Keyset PaginationWHEREscore:last_scoreORDERBYscoreDESCLIMIT100避免深分页扫描。风险四回退方案上线索引优化前保存原SQL保存原执行计划新索引灰度观察P95/P99异常删除索引或恢复SQL。不要只看单次SQL耗时还要看整体吞吐 CPU IO 锁等待总结Top-N查询优化的核心不是“让排序更快”而是减少需要排序的数据最佳实践执行计划分析 ↓ 确认Sort瓶颈 ↓ 设计匹配索引 ↓ 验证Index Scan ↓ 监控并发影响 ↓ 准备回退方案排行榜场景中一个正确设计的排序索引往往可以让几十秒级查询降低到毫秒级。附录实验SQLEXPLAINANALYZESELECTuser_id,scoreFROMuser_scoreWHEREstatus1ORDERBYscoreDESCLIMIT100;CREATEINDEXidx_status_scoreONuser_score(status,scoreDESC);ANALYZEuser_score;转载自https://blog.csdn.net/u014727709/article/details/163863497欢迎 点赞✍评论⭐收藏欢迎指正 排序与Top-N查询优化——排行榜场景下的执行计划、索引设计与性能实验
文章目录每日一句正能量1. 背景与问题2. 环境与数据3. 复现过程3.1 无索引情况下3.2 Top-N并不等于少量计算4. 方案实施4.1 建立排序方向匹配的索引4.2 联合条件下设计复合索引4.3 Top-N排序与内存4.4 参数调整5. 结果对比5.1 执行计划验证清单优化前优化后6. 风险与复盘风险一索引过多风险二排序字段频繁变化风险三分页深度问题风险四回退方案总结附录实验SQL每日一句正能量“最深的期待不是填满而是在心里精心留白。”我们常以为期待是渴望某种到来最深的那种是为未知留下神圣的空隙。像国画的留白不画满反而让山水有了呼吸。心里有白万物才能涌入。1. 背景与问题排行榜是互联网系统中最典型的Top-N业务例如商品销量Top100用户积分排名游戏战力榜新闻热榜风控规则排序。业务通常写成SELECTuser_id,scoreFROMuser_scoreORDERBYscoreDESCLIMIT100;很多开发人员认为只返回100行所以SQL一定很快。实际情况并非如此。如果没有合适索引数据库可能执行全表扫描 ↓ 读取百万行 ↓ 完整排序 ↓ 取前100真正消耗时间的是排序的数据量而不是最终返回的数据量Top-N优化核心目标让数据库尽可能早知道哪些数据排在前面而不是先把所有数据排序完成。2. 环境与数据测试环境KingbaseES 表 user_score 数据量 5000万行 字段 user_id score create_time status初始化CREATETABLEuser_score(user_idBIGINTPRIMARYKEY,scoreINTEGER,create_timeTIMESTAMP,statusINTEGER);排行榜查询SELECTuser_id,scoreFROMuser_scoreWHEREstatus1ORDERBYscoreDESCLIMIT100;重点观察Sort节点 执行时间 Buffers 临时文件3. 复现过程3.1 无索引情况下执行计划可能类似Limit | Sort | Seq Scan user_score问题Seq Scan读取5000万行 Sort处理大量数据 Limit最后才生效如果排序空间不足内存排序 ↓ 临时文件 ↓ 磁盘排序性能进一步下降。需要关注Sort Method Disk Usage Buffers read3.2 Top-N并不等于少量计算例如ORDERBYscoreDESCLIMIT10数据库仍然需要回答谁是最高的10个人如果不知道score顺序只能扫描更多数据。因此返回10行 ≠ 只处理10行4. 方案实施4.1 建立排序方向匹配的索引排行榜核心索引CREATEINDEXidx_score_rankONuser_score(scoreDESC);再次执行EXPLAINANALYZESELECTuser_id,scoreFROMuser_scoreWHEREstatus1ORDERBYscoreDESCLIMIT100;理想计划Limit | Index Scan变化原扫描5000万 排序5000万后沿索引读取前100附近数据4.2 联合条件下设计复合索引实际排行榜通常有过滤条件WHEREstatus1ORDERBYscoreDESCLIMIT100如果只有score索引数据库可能仍需要过滤大量无效行。更合理CREATEINDEXidx_status_scoreONuser_score(status,scoreDESC);原因索引顺序status分组 score排序可以减少扫描范围。4.3 Top-N排序与内存当无法完全利用索引时数据库可能使用Top-N Heap而不是完整排序。完整排序5000万行全部排序Top-N维护当前最大的100行内存压力明显下降。但注意如果N变大LIMIT1000000Top-N优势会下降。4.4 参数调整排序相关参数需要结合业务关注work_mem maintenance_work_mem 临时文件大小提高排序内存可能减少磁盘落盘。但是并发环境下单SQL排序内存 × 并发连接数可能造成内存压力。因此不能简单调大。5. 结果对比测试示例方案执行计划P95Buffer Read无索引Seq Scan Sort18s大量Top-N优化Top-N Heap6s降低排序索引Index Scan Limit120ms极低示例结果说明真正有效的优化通常来自减少排序输入而不是5.1 执行计划验证清单每次优化后保存EXPLAINANALYZE重点比较优化前Sort actual rows50000000优化后Index Scan actual rows≈100观察Execution Time Buffers Sort Method6. 风险与复盘风险一索引过多排行榜索引提升读取但是增加INSERT成本 UPDATE成本 存储空间需要评估写入压力。风险二排序字段频繁变化例如实时积分榜每秒大量更新score。索引维护成本可能明显增加。可考虑缓存排行榜定时汇总表异步计算。风险三分页深度问题很多系统LIMIT100OFFSET1000000会导致大量跳过。优化方式使用Keyset PaginationWHEREscore:last_scoreORDERBYscoreDESCLIMIT100避免深分页扫描。风险四回退方案上线索引优化前保存原SQL保存原执行计划新索引灰度观察P95/P99异常删除索引或恢复SQL。不要只看单次SQL耗时还要看整体吞吐 CPU IO 锁等待总结Top-N查询优化的核心不是“让排序更快”而是减少需要排序的数据最佳实践执行计划分析 ↓ 确认Sort瓶颈 ↓ 设计匹配索引 ↓ 验证Index Scan ↓ 监控并发影响 ↓ 准备回退方案排行榜场景中一个正确设计的排序索引往往可以让几十秒级查询降低到毫秒级。附录实验SQLEXPLAINANALYZESELECTuser_id,scoreFROMuser_scoreWHEREstatus1ORDERBYscoreDESCLIMIT100;CREATEINDEXidx_status_scoreONuser_score(status,scoreDESC);ANALYZEuser_score;转载自https://blog.csdn.net/u014727709/article/details/163863497欢迎 点赞✍评论⭐收藏欢迎指正