文章目录每日一句正能量1. 背景与问题Nested Loop不是“坏算法”真正危险的是把百万行误当成一百行2. 环境与数据同样的表大小外表从100行变成100万行计划性质会完全变化2.1 场景A点查3个客户2.2 场景B查整个区域3. 复现过程一个估算只有120行的计划为什么实际循环了82万次3.1 先找 Outer3.2 再看 Inner loops3.3 为什么优化器会接受82万轮3.4 执行 ANALYZE4. 方案实施什么时候应该保留 Nested Loop什么时候应该让它离开4.1 用 Outer Rows × Inner Probe Cost 做第一层判断4.2 Inner 有高选择性索引是经典优势条件4.3 Inner Seq Scan 是高危信号4.4 检查隐式转换导致索引失效4.5 把过滤尽量推到驱动侧4.6 SQL结构要明确表达“小驱动集”4.7 多列相关性仍估不准时考虑扩展统计4.8 enable_nestloop只用于诊断性对照5. 结果对比一次38秒的 Nested Loop 如何优化到190msE0原始计划E1ANALYZE 后E2增加复合过滤索引E3进一步缩小驱动集5.1 实验结果5.2 监控随机IO5.3 并发会改变最优结论6. 风险与复盘误用 Nested Loop 的根源往往不是算法而是错误的行数世界观6.1 看到 Nested Loop 就判坏计划6.2 只看表总行数6.3 忽略 loops6.4 全局关闭 enable_nestloop6.5 只加索引不看写成本6.6 参数敏感和计划缓存6.7 SQL改写必须验证业务语义推荐的 Nested Loop 判定方法回退方案最终复盘附录 A最小诊断SQL附录 B诊断性算法对照附录 C最低验收门禁每日一句正能量“我站在‘现在’甲板上朝着‘未来’的方向吹着此刻的风。”甲板是当下未来是海平线而吹过的风是唯一的真实。既不沉溺过往也不焦虑远方只是稳稳站在现在感受这一秒的风掠过耳际。主题连接算法 / Nested Loop / 小表驱动查询重点外表驱动行数、Inner Scan、Loops、索引探测、基数估算、enable_nestloop、统计信息、阈值实验、SQL/索引前后对比适用场景KingbaseES 在线交易、客户查询、订单明细、批量报表等包含多表 Join 的系统尤其适用于“Nested Loop 有时极快、有时突然退化”的执行计划诊断。1. 背景与问题Nested Loop不是“坏算法”真正危险的是把百万行误当成一百行数据库性能讨论里Nested Loop 经常被简单概括成小表用 Nested Loop 大表用 Hash Join这句话方向没有完全错但工程上远远不够。因为真正决定 Nested Loop 成本的不是“表文件有多大”而是外表最终驱动多少行 × 内表每次探测需要多少成本一个 10 亿行订单表如果条件最终只取 3 个 customer_id并且内表连接键上存在高选择性索引那么 Nested Loop 很可能是最优方案。相反一个只有 100 万行的表如果外层实际输出 80 万行然后对另一个没有合适索引的表重复扫描 80 万次Nested Loop 就会变成灾难。KingbaseES 官方执行计划文档对 Nested Loop 的原理描述很直接扫描外表的每条记录再和内表进行连接传统复杂度可理解为接近O(m×n)官方把它归为适合数据量不大的场景并提供enable_nestloop参数控制规划器对这类计划的偏好。但生产实践里“数据量不大”应该进一步解释成进入循环的实际外表行数足够小并且内表单次探测足够便宜。因此诊断 Nested Loop 时我更关心四个数字Outer actual rows Inner loops Inner per-loop cost/time Buffer reads/hits而不是只看Nested Loop四个字。2. 环境与数据同样的表大小外表从100行变成100万行计划性质会完全变化示例环境KingbaseES V9 customer 5000万行 trade_order 12亿行 customer PK customer_id 订单索引 idx_order_customer(customer_id) 业务 客户订单查询 区域批量分析2.1 场景A点查3个客户SELECTo.order_id,o.amountFROMcustomer cJOINtrade_order oONo.customer_idc.customer_idWHEREc.customer_idIN(101,102,103);外表实际只有约 3 行内表通过customer_id索引探测。假设单次探测约 0.03ms那么累计索引探测成本几乎可以忽略。此时建立一张大 Hash 表反而可能比直接循环探测更贵。2.2 场景B查整个区域SELECTo.order_id,o.amountFROMcustomer cJOINtrade_order oONo.customer_idc.customer_idWHEREc.region_id1;如果region_id1实际对应 820,000 个客户同一个内表索引扫描就要执行 82 万次。单次探测只有 0.03ms也会被循环次数放大到约 24.6 秒还没有计算上层节点、结果处理、缓存 Miss、并发争抢。所以Nested Loop 的阈值是成本阈值不是固定行数阈值。3. 复现过程一个估算只有120行的计划为什么实际循环了82万次假设慢 SQL 的计划核心部分Nested Loop - Index Scan on customer rows120 actual rows820000 - Index Scan on trade_order loops820000执行时间38s3.1 先找 OuterNested Loop 一定有驱动侧。Outer 实际输出多少行基本决定 Inner 被重复访问多少轮。这里Outer actual rows820000已经是高风险信号。3.2 再看 Inner loops内表节点loops820000意味着同一访问路径被执行 82 万轮。即使内表单轮只有0.03ms也有0.03 × 820000 ≈ 24.6s这就是典型的“单次极便宜总次数极昂贵”。3.3 为什么优化器会接受82万轮因为规划时它估算的 Outer 只有 120 行。estimated 120 actual 820000误差超过 6800 倍。优化器是在一个错误的行数世界里做出了一个看起来合理的计划。因此真正的问题不是Nested Loop算法错了而是为什么Outer被低估6800倍KingbaseES 官方 SQL 优化建议明确指出统计信息过旧、缺少多列统计以及严重的数据倾斜都可能使优化器产生非最优计划。3.4 执行 ANALYZEANALYZEcustomer;ANALYZEtrade_order;重新执行。假设estimated customer rows760000 actual820000规划器已经知道外表接近 80 万行因此选择 Hash Join。示例 P9538s → 11.8s这说明基数估算确实是主要根因之一。4. 方案实施什么时候应该保留 Nested Loop什么时候应该让它离开4.1 用 Outer Rows × Inner Probe Cost 做第一层判断一个很实用的近似模型Nested Loop Probe Cost ≈ Outer Rows × Inner Probe Cost例如100 × 0.03ms ≈ 3ms 10,000 × 0.03ms ≈ 300ms 100,000 × 0.03ms ≈ 3s 1,000,000 × 0.03ms ≈ 30s但不能因此规定超过10万行禁止Nested Loop如果数据完全驻留缓存单次内表探测只有 0.002ms100万 × 0.002ms ≈ 2s某些低频批处理仍可能接受。反过来如果一次内表探测要 2ms1万次≈20秒已经很差。4.2 Inner 有高选择性索引是经典优势条件典型理想计划Outer 少量行 Inner Index Scan / Index Only Scan例如CREATEINDEXidx_order_customerONtrade_order(customer_id);外表 100 行时就是大约 100 次精准索引探测。4.3 Inner Seq Scan 是高危信号如果计划Nested Loop Outer rows5000 - Seq Scan inner_table loops5000意味着内表被完整扫描 5000 次。除非内表极小否则必须解释。建议建立计划门禁Large Loop Inner Seq Scan 必须Review4.4 检查隐式转换导致索引失效连接条件o.customer_idc.customer_id如果一边是 BIGINT、一边是 VARCHAR迁移兼容处理中又增加 CAST就可能让原本可索引的连接变成过滤扫描。迁移后尤其要检查数据类型 函数包装 Collation 隐式转换4.5 把过滤尽量推到驱动侧原始region_id1 → 820000行但其中status1只有 8200 行。建立CREATEINDEXidx_customer_region_statusONcustomer(region_id,status,customer_id);让外表先收敛到 8200 行。这时即使仍采用 Nested Loop也可能非常合理。4.6 SQL结构要明确表达“小驱动集”例如WITHdriverAS(SELECTcustomer_idFROMcustomerWHEREregion_id:regionANDstatus1)SELECT...FROMdriver cJOINtrade_order oONo.customer_idc.customer_id;这里不是迷信 CTE而是让 SQL 设计明确表达“先缩小驱动集”最终是否成功必须由执行计划证明。4.7 多列相关性仍估不准时考虑扩展统计例如region_id status高度相关。单列统计可能错误地假设选择率独立。如果普通 ANALYZE 后 estimated/actual 仍严重偏离应继续检查数据倾斜 多列相关性而不是直接禁止 Nested Loop。4.8 enable_nestloop只用于诊断性对照测试会话可以BEGIN;SETLOCALenable_nestloopoff;EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;ROLLBACK;用来回答如果规划器不倾向Nested Loop其他计划表现如何但不要把全局关闭 Nested Loop 当长期修复。KingbaseES 官方参数文档说明enable_nestloopoff也不是绝对禁止只是使规划器尽量优先选择其他连接方式。Join 开关是实验工具不是统计信息和索引治理的替代品。5. 结果对比一次38秒的 Nested Loop 如何优化到190msE0原始计划estimated outer120 actual outer820000 Inner Index Scan loops820000 P9538s Buffer Reads920万E1ANALYZE 后estimated760000 actual820000 JoinHash Join P9511.8s Buffer Reads210万说明统计修复有效。E2增加复合过滤索引CREATEINDEXidx_customer_region_statusONcustomer(region_id,status,customer_id);驱动集820000 → 8200优化器重新选择Nested Loop此时loops8200 P952.1s这个结果非常重要Nested Loop 在 E0 是坏计划在 E2 又重新变成合理计划。算法没变真正变化的是驱动行数。E3进一步缩小驱动集业务条件进一步将 Outer 缩小到620行最终Nested Loop loops620 P95190ms此时如果为了“消灭 Nested Loop”强制 Hash Join反而可能增加构建 Hash 表的固定成本。5.1 实验结果实验Outer EstOuter ActualJoinInner LoopsP95E0120820000Nested Loop82000038sE1760000820000Hash Join111.8sE290008200Nested Loop82002.1sE3650620Nested Loop620190ms以上均为方法演示数据不是生产实测。5.2 监控随机IONested Loop Index Scan 的一个重要特征是大量重复索引探测。缓存命中很高时它会很快缓存不足时大量物理随机 IO 会显著放大延迟。因此同时观察Buffers hit Buffers read 磁盘读延迟 CPU不能只记录总耗时。5.3 并发会改变最优结论单会话Nested Loop190ms Hash Join250ms不代表 100 并发仍然如此。Nested Loop 可能放大索引页竞争 随机IO CPUHash Join 可能放大工作内存 临时文件最终应该比较系统吞吐 P95/P99 CPU IO 内存而不是只选单 SQL 冠军。6. 风险与复盘误用 Nested Loop 的根源往往不是算法而是错误的行数世界观6.1 看到 Nested Loop 就判坏计划Outer 只有 3 行、Inner 是主键索引时Nested Loop 往往正是经典最优方案。6.2 只看表总行数10 亿行表不等于不能 Nested Loop。如果最终驱动只产生 3 行它仍然可能非常快。表大小 ≠ 驱动结果大小6.3 忽略 loopsInner 单轮 0.02ms 看起来很快。但loops2,000,000累计就是完全不同的数量级。6.4 全局关闭 enable_nestloop这可能修复 1 条坏 SQL却让数千条点查 SQL 失去优秀计划。不要用全局算法开关治疗局部统计问题。6.5 只加索引不看写成本新增复合索引会带来INSERT/UPDATE成本 WAL 磁盘空间 维护成本必须同时做读写验收。6.6 参数敏感和计划缓存普通 region620行热点 region82万行同一参数化 SQL 使用同一缓存计划时可能并不同时适合两类参数。KingbaseES 支持执行计划缓存当表定义、函数定义、统计信息等变化时相关缓存计划会失效。参数敏感场景需要结合真实 JDBC/PBE 调用方式单独验证。6.7 SQL改写必须验证业务语义过滤前推和 Join 结构重写以后要比较行数 主键集合 NULL 重复 金额 排序性能提升不能以改变结果为代价。推荐的 Nested Loop 判定方法可以把生产判断浓缩成三个维度Outer Actual Rows × Inner Probe Cost × Concurrency绿色Outer很小 Inner高选择性索引 缓存命中高保留 Nested Loop。黄色Outer中等 循环几万~几十万和 Hash/Merge 做实验对比。红色Outer很大 Inner loops巨大 Inner还是Seq Scan estimated/actual严重失真优先处理统计、索引、过滤和 Join 顺序。回退方案如果 SQL 改写或索引灰度后出现P95恶化 写TPS下降 锁/IO上升 结果差异则1. 停止扩大流量 2. Feature Flag恢复旧SQL 3. 恢复会话级planner参数 4. 新索引先保留证据确认无其他SQL依赖后再删 5. 保存前后计划和监控 6. 重新执行结果校验尤其不要把enable_nestloopoff作为长期回退配置。最终复盘Nested Loop 的核心成本模型非常直观外表驱动多少次 × 内表每次探测多贵因此真正要问的不是Nested Loop好不好而是为什么外表有这么多行 优化器原来估多少 内表为什么采用这个访问路径 每次探测是索引还是全扫 这个计划在热点参数和并发下还成立吗如果只记住一句话Nested Loop 最适合“小结果集驱动 低成本内表探测”它最危险的误用则是优化器把一个实际上会循环几十万、几百万次的外表错误地估成了几十或几百行。因此调优顺序应该始终是执行计划 → actual rows / loops → 统计信息 → 内表索引 → 过滤前推 → SQL改写 → Join算法对照 → 并发验收这比简单地“Nested Loop 慢 → 禁掉”更稳、更可解释也更适合生产系统。附录 A最小诊断SQLEXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;重点看Outer actual rows Inner loops Inner scan type Rows Removed by Filter Buffers hit/read Execution Time附录 B诊断性算法对照BEGIN;SETLOCALenable_nestloopoff;EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;ROLLBACK;只用于实验不建议作为长期全局修复。附录 C最低验收门禁[ ] estimated/actual无重大未解释偏差 [ ] 大循环中不存在未解释Inner Seq Scan [ ] P95/P99达到SLA [ ] 随机IO无显著回归 [ ] 索引写成本可接受 [ ] SQL改写结果差异0 [ ] 热点/普通参数均验证 [ ] 并发测试完成 [ ] SQL/索引回退方案就绪转载自https://blog.csdn.net/u014727709/article/details/163863447欢迎 点赞✍评论⭐收藏欢迎指正