文章目录
- 每日一句正能量
- 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_scoreWHEREstatus=1ORDERBYscoreDESCLIMIT100;重点观察：
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_scoreWHEREstatus=1ORDERBYscoreDESCLIMIT100;理想计划：
Limit | Index Scan变化：
原：
扫描5000万 排序5000万后：
沿索引读取前100附近数据4.2 联合条件下设计复合索引
实际排行榜通常有过滤条件：
WHEREstatus=1ORDERBYscoreDESCLIMIT100如果只有：
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. 结果对比
测试示例：
| 方案 | 执行计划 | P95 | Buffer Read |
|---|---|---|---|
| 无索引 | Seq Scan + Sort | 18s | 大量 |
| Top-N优化 | Top-N Heap | 6s | 降低 |
| 排序索引 | Index Scan + Limit | 120ms | 极低 |
示例结果说明：
真正有效的优化通常来自：
减少排序输入而不是：
5.1 执行计划验证清单
每次优化后保存：
EXPLAINANALYZE重点比较：
优化前
Sort actual rows=50000000优化后
Index Scan actual rows≈100观察：
Execution Time Buffers Sort Method6. 风险与复盘
风险一：索引过多
排行榜索引提升读取：
但是增加：
INSERT成本 UPDATE成本 存储空间需要评估写入压力。
风险二：排序字段频繁变化
例如：
实时积分榜每秒大量更新score。
索引维护成本可能明显增加。
可考虑：
- 缓存排行榜；
- 定时汇总表；
- 异步计算。
风险三：分页深度问题
很多系统：
LIMIT100OFFSET1000000会导致大量跳过。
优化方式：
使用Keyset Pagination：
WHEREscore<:last_scoreORDERBYscoreDESCLIMIT100避免深分页扫描。
风险四：回退方案
上线索引优化前：
- 保存原SQL；
- 保存原执行计划；
- 新索引灰度；
- 观察P95/P99；
- 异常删除索引或恢复SQL。
不要只看：
单次SQL耗时还要看：
整体吞吐 CPU IO 锁等待总结
Top-N查询优化的核心不是“让排序更快”，而是：
减少需要排序的数据最佳实践：
执行计划分析 ↓ 确认Sort瓶颈 ↓ 设计匹配索引 ↓ 验证Index Scan ↓ 监控并发影响 ↓ 准备回退方案排行榜场景中，一个正确设计的排序索引，往往可以让几十秒级查询降低到毫秒级。
附录：实验SQL
EXPLAINANALYZESELECTuser_id,scoreFROMuser_scoreWHEREstatus=1ORDERBYscoreDESCLIMIT100;CREATEINDEXidx_status_scoreONuser_score(status,scoreDESC);ANALYZEuser_score;转载自：https://blog.csdn.net/u014727709/article/details/163863497
欢迎 👍点赞✍评论⭐收藏，欢迎指正
