文章目录每日一句正能量1. 背景与问题窗口函数语法很优雅但它可能在返回1行之前先处理5000万行2. 环境与数据窗口函数的四个性能维度2.1 第一维排序数据量2.2 第二维分区大小2.3 第三维窗口帧2.4 第四维最终是否真的需要所有排名结果3. 复现过程10万到1亿行窗口函数什么时候跨过性能边界3.1 10万行3.2 100万行3.3 1000万行3.4 1亿行3.5 并发会更早触达边界4. 方案实施六类窗口函数优化与替代方案4.1 方案一全局Top-N不要先给所有行编号4.2 方案二每组最新一条先减少窗口输入4.3 方案三让索引提供窗口需要的顺序4.4 方案四索引消掉 Sort不等于消掉 WindowAgg4.5 方案五高频最新状态查询使用状态表4.6 方案六累计指标先预聚合再开窗口4.7 方案七移动窗口先改变粒度4.8 ROWS和RANGE不能随便替换4.9 ROW_NUMBER、RANK、DENSE_RANK不能互换4.10 热分区要单独压测4.11 work_mem实验仍然有价值但它不是终点4.12 参数实验用SET LOCAL4.13 执行计划缓存也要考虑5. 结果对比每组最新一条从68秒降到420msE0原SQLE1ANALYZEE2work_mem 256MBE3预过滤最近30天E4复合有序索引E5预计算latest_state5.1 示例数据汇总5.2 10万~1亿规模实验5.3 必须同时观察Temp GB/min5.4 结果校验比普通查询更复杂6. 风险与复盘窗口函数最容易出现“SQL很优雅但成本隐藏得很深”6.1 风险一WHERE rn1并不一定提前结束计算6.2 风险二索引解决Sort却没有解决输入规模6.3 风险三work_mem调大引发并发内存风险6.4 风险四预计算表一致性6.5 风险五排名语义被改6.6 风险六窗口Frame默认值6.7 风险七只压测均匀数据推荐诊断顺序回退方案最终复盘附录 A最小执行计划附录 B窗口索引附录 C不同规模压测附录 D最低验收门禁每日一句正能量明天和意外你不知道哪个会先到珍惜当下拥抱你爱的人。行动吧爱不是未来的计划是此刻就能给出的拥抱。主题分析函数 / 窗口函数 / 流水排名重点ROW_NUMBER、RANK、DENSE_RANK、WindowAgg、Sort、PARTITION BY、ORDER BY、窗口帧、work_mem、索引有序访问、预计算适用场景KingbaseES 中账户流水排名、每组最新一条、Top-N、累计金额、环比、移动窗口、经营分析等大数据量分析函数场景。1. 背景与问题窗口函数语法很优雅但它可能在返回1行之前先处理5000万行窗口函数是分析 SQL 中最好用的一类能力。例如每个账户取最新一笔流水SELECT*FROM(SELECTaccount_id,txn_time,amount,balance,ROW_NUMBER()OVER(PARTITIONBYaccount_idORDERBYtxn_timeDESC)ASrnFROMaccount_flow)tWHERErn1;业务最终每个账户1行如果有80万个账户最终只返回80万行很多人因此认为SQL并没有处理多少数据但真实执行计划可能是Seq Scan 5000万流水 ↓ Sort(account_id, txn_time DESC) ↓ WindowAgg ↓ 给5000万行编号 ↓ 外层过滤 rn1 ↓ 返回80万行也就是说WHERE rn1并没有天然让数据库只处理每组第一行KingbaseES 官方执行计划分析文档就给出了一个ROW_NUMBER()示例其计划结构是Sort → WindowAgg → Subquery Filter官方示例同时说明涉及Row_Number()时也可能出现返回行数估算问题。因此窗口函数调优第一原则是不要根据最终返回行数判断窗口函数成本要根据进入 WindowAgg 的行数、排序量和最大分区大小判断。2. 环境与数据窗口函数的四个性能维度示例表CREATETABLEaccount_flow(flow_idBIGINTPRIMARYKEY,account_idBIGINT,txn_timeTIMESTAMP,amountNUMERIC(18,2),balanceNUMERIC(18,2),statusINT);业务账户流水 最新流水 账户内排名 累计交易金额 前一笔/后一笔交易测试规模10万 100万 1000万 1亿2.1 第一维排序数据量排名函数需要ORDER BY例如ROW_NUMBER()OVER(PARTITIONBYaccount_idORDERBYtxn_timeDESC)如果输入没有所需顺序就很可能出现 Sort。KingbaseES 官方文档说明窗口规范中的ORDER BY用来定义分区内部的顺序排名类窗口函数需要这个顺序来确定排名。所以窗口函数性能首先受排序行数影响。2.2 第二维分区大小两个 SQL 都处理 1000 万行。场景A100万个账户 每个账户约10行场景B100个账户 每个账户10万行总行数一样但窗口处理特征完全不同。尤其热账户可能拥有极大分区。所以不能只统计平均分区行数至少要记录P50 P95 MAX partition rows2.3 第三维窗口帧例如SUM(amount)OVER(PARTITIONBYaccount_idORDERBYtxn_timeROWSBETWEEN6PRECEDINGANDCURRENTROW)只需要有限移动窗口。而ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW代表从分区起点累计到当前行。Kingbase 官方窗口表达式文档说明ROWS和RANGE用来定义窗口帧窗口函数是在该帧上计算而不是只由函数名称决定。因此同样SUM OVER不同 Frame 的成本和语义可能完全不同。2.4 第四维最终是否真的需要所有排名结果如果业务全账户完整名次窗口排名就是合理需求。但如果只是全局Top100却写ROW_NUMBER()OVER(ORDERBYscoreDESC)再rn100通常没有必要给全部数据编号。此时ORDER BY LIMIT 100往往更直接。3. 复现过程10万到1亿行窗口函数什么时候跨过性能边界下面用同一逻辑SELECTaccount_id,txn_time,ROW_NUMBER()OVER(PARTITIONBYaccount_idORDERBYtxn_timeDESC)rnFROMaccount_flow;保持分布模式 选择性 work_mem近似一致。3.1 10万行示例Sort Method: quicksort Temp: 0 P95: 0.18s这个规模直接使用窗口函数非常合理。为了所谓“优化”引入汇总表 复杂缓存可能反而增加维护成本。3.2 100万行示例P95: 1.6s Temp: 0仍处于可接受区间。如果报表 SLA3秒没有必要过度改造。3.3 1000万行计划开始出现Sort Method: external merge Disk: 2.4GBP9518s这时性能性质发生变化CPU排序 → 磁盘临时文件KingbaseES 官方执行计划文档明确指出当排序超过work_mem后会使用磁盘临时文件并在执行计划中看到类似Sort Method: external merge Disk: ...官方示例也展示了调高work_mem后从 external merge 变为内存 quicksort 的情况。3.4 1亿行示例Temp: 28GB P95: 220s P99: 310s如果业务每分钟跑一次全量排名这已经不是简单 SQL 参数调优问题。真正问题是是否应该每个请求都重新计算1亿行的完整排名3.5 并发会更早触达边界单查询Temp28GB如果5个分析用户同时跑临时 IO 就可能140GB级而提高work_mem也不能无限解决。官方参数手册明确提醒work_mem是每个内部排序/Hash 操作的内存额度一个复杂查询可有多个操作多会话又能并发所以总内存可能是参数值的很多倍。因此窗口函数真正的性能边界不是单SQL还能不能跑完而是在目标并发和SLA下是否还能稳定运行4. 方案实施六类窗口函数优化与替代方案4.1 方案一全局Top-N不要先给所有行编号错误倾向SELECT*FROM(SELECT*,ROW_NUMBER()OVER(ORDERBYscoreDESC)rnFROMuser_score)tWHERErn100;业务只需要Top100可以直接SELECT*FROMuser_scoreORDERBYscoreDESCLIMIT100;如果有CREATEINDEXidx_score_descONuser_score(scoreDESC);就有机会沿索引读取前100避免全量WindowAgg4.2 方案二每组最新一条先减少窗口输入典型每个账户最新流水原5000万历史流水 全部参与窗口但业务只关注最近30天活跃账户先过滤WITHrecentAS(SELECT...FROMaccount_flowWHEREstatus1ANDtxn_time:start_time)...示例5000万 →900万P9539s →12s收益来自少排序4100万行4.3 方案三让索引提供窗口需要的顺序窗口PARTITIONBYaccount_idORDERBYtxn_timeDESC可以评估CREATEINDEXidx_flow_account_timeONaccount_flow(account_id,txn_timeDESC);如果还有高选择性status1可以比较CREATEINDEXidx_flow_status_account_timeONaccount_flow(status,account_id,txn_timeDESC);是否更合适。关键不是“窗口函数一定走索引”而是这个索引能否减少扫描 并提供接近窗口要求的顺序必须看真实计划。4.4 方案四索引消掉 Sort不等于消掉 WindowAgg这是非常重要的边界。即使计划从Seq Scan → Sort → WindowAgg变成Index Scan → WindowAgg如果仍然需要给900万行全部编号WindowAgg 还在。所以索引优化只是解决排序不一定解决窗口计算规模这也是为什么高频“每组最新一条”最终可能需要预计算状态表4.5 方案五高频最新状态查询使用状态表如果业务每天数亿流水但线上接口只需要每账户最新余额每次ROW_NUMBER()扫描历史流水并不经济。可以维护CREATETABLEaccount_latest_state(account_idBIGINTPRIMARYKEY,txn_timeTIMESTAMP,flow_idBIGINT,balanceNUMERIC(18,2));由业务事务 CDC 流处理 定时任务更新。查询800万历史流水变成每账户1行本质上把“历史分析”转换为“状态维护”。4.6 方案六累计指标先预聚合再开窗口例如SUM(amount)OVER(PARTITIONBYshop_idORDERBYbiz_date)直接基于流水明细做累计。如果每家门店每天数十万流水窗口输入极大可以先shop_id biz_date聚成日金额。再SUM(day_amount)OVER(PARTITIONBYshop_idORDERBYbiz_date)窗口输入从亿级流水变成门店×天几万/几十万级。4.7 方案七移动窗口先改变粒度比如过去7天销售额如果直接流水级Window一行是一笔交易。可以先按日聚合。然后SUM(day_amount)OVER(PARTITIONBYshop_idORDERBYbiz_dateROWSBETWEEN6PRECEDINGANDCURRENTROW)这样窗口只处理每日粒度而不是每笔流水4.8 ROWS和RANGE不能随便替换ROWS BETWEEN 6 PRECEDING前6行不等于过去6天如果一天多行语义完全不同。RANGE又可能根据排序值定义范围。所以性能改写必须同时做窗口帧语义验证不能为了快改错业务口径。4.9 ROW_NUMBER、RANK、DENSE_RANK不能互换假设分数100 100 90ROW_NUMBER1 2 3RANK1 1 3DENSE_RANK1 1 2官方窗口函数文档也分别定义了三者不同的排名行为。所以排行榜优化不能为了方便索引/Limit 而把RANK偷偷变成ROW_NUMBER4.10 热分区要单独压测平均每账户20条流水看起来很好。但一个超级账户19000条可能拖慢窗口。压测应该记录partition P50 P95 MAX并专门构造hot key测试。4.11 work_mem实验仍然有价值但它不是终点例如32MB external merge Temp 12GB P95 68s 256MB Temp 3.8GB P95 39s说明排序内存确实是瓶颈之一但5000万行仍然需要WindowAgg所以结构优化空间更大。4.12 参数实验用SET LOCAL推荐BEGIN;SETLOCALwork_mem128MB;EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;ROLLBACK;不要第一步全局work_mem512MB因为排行榜、报表、Hash、Sort都会共享该风险。4.13 执行计划缓存也要考虑窗口 SQL 往往带日期 账户类型 区域不同参数输入规模可能差很多。KingbaseES 支持执行计划缓存并且统计信息等对象状态变化时相关缓存计划会失效。所以需要分别测试普通参数 热点参数 长周期参数避免一套计划适配所有窗口规模5. 结果对比每组最新一条从68秒降到420ms假设业务5000万流水 80万账户 每账户取最新一条E0原SQLSeq Scan → Sort → WindowAgg → Filter rn1 Input: 5000万 Temp: 12GB P95: 68sE1ANALYZE估算改善。P95: 61s收益有限因为还是5000万输入E2work_mem 256MBTemp: 12GB →3.8GB P95: 61s →39s证明 Sort 落盘确实是瓶颈。但还不够。E3预过滤最近30天Window input: 5000万 →900万 Temp: 1.1GB P95: 12sE4复合有序索引(account_id,txn_timeDESC)计划Index Scan → WindowAggSort 消失或显著减少。Temp≈0 P955.4s但窗口仍要处理900万行E5预计算latest_state查询直接读每账户最新状态输入约80万P95420ms5.1 示例数据汇总实验计划Window输入TempP95E0Seq ScanSortWindowAgg5000万12GB68sE1统计修复5000万11GB61sE2work_mem增大5000万3.8GB39sE3预过滤900万1.1GB12sE4有序索引900万≈05.4sE5latest_state80万00.42s以上为方法示例数据不是生产实测。5.2 10万~1亿规模实验数据量SortTempP95建议10万quicksort00.18s直接窗口100万quicksort01.6s通常可接受1000万external merge2.4GB18s索引/预过滤1亿external merge28GB220s预计算/异步同样属于示例方法数据。真正的“边界”应该由你的硬件 SLA 并发 分区倾斜 索引共同决定。5.3 必须同时观察Temp GB/min窗口函数并发时单SQLP95还不够。比如10并发 每条Temp 2GB 一分钟跑3轮临时文件流量就可能非常高。建议监控Temp GB/min 磁盘写MB/s 磁盘读延迟 CPU 数据库进程内存5.4 结果校验比普通查询更复杂窗口函数尤其要验证排序稳定性 并列排名 NULL 同时间戳 分区边界 窗口Frame例如最新一条两条txn_time完全相同如果没有第二排序键ORDERBYtxn_timeDESC结果可能不稳定。建议ORDERBYtxn_timeDESC,flow_idDESC形成确定性顺序。6. 风险与复盘窗口函数最容易出现“SQL很优雅但成本隐藏得很深”6.1 风险一WHERE rn1并不一定提前结束计算执行计划可能仍然Sort全部 WindowAgg全部 最后过滤所以必须看WindowAgg actual rows不能凭语法猜。6.2 风险二索引解决Sort却没有解决输入规模从SortWindowAgg变成Index ScanWindowAgg是好事。但如果1亿行仍全部WindowAgg实时成本依旧可能不可接受。6.3 风险三work_mem调大引发并发内存风险官方参数文档提醒一个复杂查询可能多个排序/Hash节点同时使用工作内存多会话也可能并发。因此窗口 SQL 最好采用分析用户 报表连接池 会话级参数隔离。6.4 风险四预计算表一致性latest_state快但必须解决什么时候更新 失败如何重放 乱序事件怎么办 数据延迟多少如果流水先到T2 后到T1更新逻辑不能把状态倒退。建议至少使用txn_time flow_id 或版本号判定新旧。6.5 风险五排名语义被改ROW_NUMBER无并列RANK有跳号DENSE_RANK无跳号Top-N替代时必须保留真实业务定义。6.6 风险六窗口Frame默认值有 ORDER BY 时窗口聚集的默认 Frame 可能与开发者想象不同。last_value尤其常见名字看似“分区最后一个”实际会受当前窗口帧影响。所以必须明确ROWS BETWEEN ...而不要完全依赖默认语义。6.7 风险七只压测均匀数据真实流水经常有超级商户 超级账户 批量结算账户窗口分区高度倾斜。均匀随机测试可能严重乐观必须加入热点分区。推荐诊断顺序窗口函数慢查询建议固定按1. EXPLAIN ANALYZE 2. WindowAgg输入actual rows 3. Sort Method / Disk 4. PARTITION数量与最大分区 5. estimated vs actual 6. Frame语义 7. 预过滤 8. 索引有序访问 9. Top-N/预聚合/预计算替代 10. 不同规模并发验收回退方案如果索引、SQL改写或预计算方案上线后出现排名错误 最新状态错误 P95恶化 写TPS下降 预计算延迟执行1. 停止扩大新路径 2. Feature Flag切回原窗口SQL 3. 恢复原会话work_mem 4. 停止新读流量后再暂停预计算刷新 5. 暂时保留新索引用于取证 6. 保存前后执行计划与Temp/CPU/IO监控 7. 重新验证并列排名、最新记录和窗口边界最终复盘窗口函数成本可以近似理解为输入数据量 排序成本 分区规模 窗口帧成本因此真正的优化优先级通常是减少输入 利用已有顺序 减少窗口计算范围 改成更直接的Top-N 预聚合 预计算状态 最后才是单纯增加work_mem如果只记住一句话窗口函数的性能边界不在于ROW_NUMBER()本身有多快而在于为了得到最终那几行结果数据库到底要排序、编号和维护多少行数据。当你看到1亿流水 → 完整Sort → WindowAgg → 最后rn1真正应该问的不是work_mem还能不能再加而是这1亿行真的需要在查询时重新编号吗这才是窗口函数从 SQL 技巧走向工程优化的分界线。附录 A最小执行计划EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECTaccount_id,txn_time,ROW_NUMBER()OVER(PARTITIONBYaccount_idORDERBYtxn_timeDESC)rnFROMaccount_flow;重点Sort Sort Method Memory/Disk WindowAgg actual rows Buffers Execution Time附录 B窗口索引CREATEINDEXidx_flow_account_timeONaccount_flow(account_id,txn_timeDESC);必须通过真实计划确认是否减少/消除了Sort。附录 C不同规模压测10万 100万 1000万 1亿必须保持分区分布 热点比例 过滤选择性 参数 缓存策略尽量一致。附录 D最低验收门禁[ ] WindowAgg输入量已量化 [ ] Sort是否落盘已确认 [ ] 热分区已测试 [ ] P95/P99达到SLA [ ] Temp GB/min在预算内 [ ] 并发内存在预算内 [ ] ROW_NUMBER/RANK语义正确 [ ] 最新一条排序具有确定性 [ ] Frame边界验证 [ ] 新索引写成本可接受 [ ] 预计算数据延迟达到SLA [ ] 回退路径已验证转载自https://blog.csdn.net/u014727709/article/details/163949132欢迎 点赞✍评论⭐收藏欢迎指正
![[光学原理与应用-521]：对光的错误理解与纠偏](http://pic.xiahunao.cn/yaotu/[光学原理与应用-521]：对光的错误理解与纠偏)
