问题背景在生产环境中一个批量更新产品信息的 SQL 语句执行时间长达29 分钟严重影响系统性能。本文详细记录问题分析、根因定位和解决方案的全过程。 原始 SQL 与执行计划原始 SQL简化版UPDATEgrid_item_ibd iSETproduct_linep.product_ln,brandp.brand,seriesp.series,oh_product_nmp.oh_product_nm,full_brandconcat_ws(,p.product_ln,p.brand),full_seriesconcat_ws(,p.product_ln,p.brand,p.series),full_product_nameconcat_ws(,p.product_ln,p.brand,p.series,p.oh_product_nm),service_typep.svc_type,service_sub_typep.svc_sub_type,service_offering_typep.offering_type,full_sub_type_serviceconcat_ws(,p.svc_type,p.svc_sub_type),full_offiering_typeconcat_ws(,p.svc_type,p.svc_sub_type,p.offering_type),update_timeNOW()FROMagellan_dim_product pWHEREi.product_numberp.product_noANDi.version_number20260713194203184表结构说明表名数据量说明grid_item_ibd约 70 行符合条件待更新的 inbound 表agellan_dim_product约 3000 万行产品维度表主键product_no❌ 错误的执行计划Update on grid_item_ibd i (cost0.69..17413409.10 rows1 width16290) (actual time1762362.135..1762362.135 rows0 loops1) - Nested Loop (cost0.69..17413409.10 rows1 width16290) (actual time1377197.695..1762354.959 rows20 loops1) - Index Scan using agellan_dim_product_pkey on agellan_dim_product p (cost0.56..12746052.91 rows30111356 width72) (actual time0.019..1669124.909 rows30230916 loops1) - Index Scan using grid_item_ibd_pkey on grid_item_ibd i (cost0.12..0.15 rows1 width13696) (actual time0.003..0.003 rows0 loops30230916) Index Cond: ((product_number)::text (p.product_no)::text) Filter: ((version_number)::text 20260713194203184::text) Rows Removed by Filter: 0 Planning Time: 0.196 ms Execution Time: 1762362.364 ms ← 约 29 分钟 问题分析1. 执行顺序完全错误实际执行顺序步骤1: 扫描整个 agellan_dim_product 表3000万行 ↓ 步骤2: 对每一行去 grid_item_ibd 查找匹配的 product_number ↓ 步骤3: 执行了 3000万次 Index Scanloops30230916 ↓ 步骤4: 最后才用 version_number 过滤应该的执行顺序步骤1: 先用 version_number 过滤 grid_item_ibd → 得到 70 行 ↓ 步骤2: 用这 70 行的 product_number 去 agellan_dim_product 查找 ↓ 步骤3: 只需要执行 70次 Index Scan2. 关键指标解读指标含义问题loops30230916Nested Loop 执行了 3000万次❌ 应该只有几十次rows30230916大表扫描了 3000万行❌ 应该先扫小表actual time1669124ms大表扫描耗时 28分钟❌ 严重超时Filter: version_number最后才应用过滤条件❌ 过滤太晚3. 为什么 PostgreSQL 会选错根本原因统计信息不准确PostgreSQL 的查询优化器Cost-Based Optimizer, CBO基于统计信息估算成本-- 查看统计信息SELECTrelname,reltuples,relpagesFROMpg_classWHERErelnameIN(grid_item_ibd,agellan_dim_product);可能的情况PostgreSQL 认为version_number xxx会匹配很多行实际只有 70 行误判agellan_dim_product为小表选择了 Nested Loop没有意识到应该先过滤grid_item_ibd✅ 解决方案方案一使用子查询强制正确顺序推荐优化后的 SQLUPDATEgrid_item_ibd iSETproduct_linep.product_ln,brandp.brand,seriesp.series,oh_product_nmp.oh_product_nm,full_brandconcat_ws(,p.product_ln,p.brand),full_seriesconcat_ws(,p.product_ln,p.brand,p.series),full_product_nameconcat_ws(,p.product_ln,p.brand,p.series,p.oh_product_nm),service_typep.svc_type,service_sub_typep.svc_sub_type,service_offering_typep.offering_type,full_sub_type_serviceconcat_ws(,p.svc_type,p.svc_sub_type),full_offiering_typeconcat_ws(,p.svc_type,p.svc_sub_type,p.offering_type),update_timeNOW()FROM(SELECTi.db_id,p.product_ln,p.brand,p.series,p.oh_product_nm,p.svc_type,p.svc_sub_type,p.offering_typeFROMgrid_item_ibd iLEFTJOINagellan_dim_product pONi.product_numberp.product_noWHEREi.version_number#{versionNumber}ANDi.db_idBETWEEN#{minId} AND #{maxId})pWHEREi.db_idp.db_id✅ 优化后的执行计划Nested Loop Left Join (cost10000000000.56..10000000035.58 rows1 width71) - Seq Scan on grid_item_ibd i (cost10000000000.00..10000000027.00 rows1 width524) Filter: ((version_number)::text 20260713194203184::text) - Index Scan using agellan_dim_product_pkey on agellan_dim_product p (cost0.56..8.58 rows1 width75) Index Cond: ((i.product_number)::text (product_no)::text)关键改进✅ 先扫描grid_item_ibd小表✅ 用version_number过滤出少量数据✅ 再用这些小数据去 JOIN 大表✅ 总 JOIN 次数从 3000万次 → 几十次方案二添加复合索引辅助优化CREATEINDEXidx_grid_item_ibd_ver_dbidONgrid_item_ibd(version_number,db_id)WHEREversion_numberISNOTNULL;作用帮助优化器更准确地估算选择性加速version_numberdb_id的联合查询但不能保证改变执行顺序必须配合子查询 性能对比指标优化前优化后提升倍数执行时间29 分钟几毫秒约 10万倍大表扫描次数3000万次0次-小表扫描次数3000万次1次-JOIN 次数3000万次几十次约 100万倍返回行数20行20行相同 Nested Loop 深度解析什么是 Nested LoopNested Loop嵌套循环是数据库中最基础的 JOIN 算法for each row in outer_table: -- 外层循环驱动表 for each row in inner_table: -- 内层循环被驱动表 if join_condition matches: return resultNested Loop 的适用场景场景是否适合说明小表驱动 大表有索引✅ 最适合你的场景两个大表 JOIN❌ 不适合应该用 Hash Join需要保留顺序✅ 适合Nested Loop 保持驱动表顺序结果集很小✅ 适合减少内层循环次数为什么之前的 Nested Loop 很慢关键驱动表选择错误❌ 错误大表驱动小表 外层: agellan_dim_product (3000万行) 内层: grid_item_ibd (70行) 总循环: 3000万次 ✅ 正确小表驱动大表 外层: grid_item_ibd (70行) 内层: agellan_dim_product (用主键索引) 总循环: 70次Nested Loop vs Hash Join vs Merge JoinJOIN 类型适用场景优点缺点Nested Loop小表驱动 大表有索引简单、快速、省内存大表驱动时极慢Hash Join两个大表只需扫描一次两表需要大量内存建哈希表Merge Join两表都已排序高效处理大数据需要预排序 最佳实践总结1. UPDATE JOIN 必须用子查询PostgreSQL 的 UPDATE FROM 语法限制-- ❌ 错误写法容易选错执行计划UPDATEtable_a aSETcolb.colFROMtable_b bWHEREa.idb.idANDa.statusactive-- ✅ 正确写法强制正确顺序UPDATEtable_a aSETcolsub.colFROM(SELECTa.id,b.colFROMtable_a aJOINtable_b bONa.idb.idWHEREa.statusactive)subWHEREa.idsub.id2. 永远不要相信优化器的默认选择统计信息可能过时复杂查询容易误判使用EXPLAIN ANALYZE验证实际执行计划3. 索引不是万能的索引只能加速查找不能纠正执行顺序错误的执行顺序下加再多索引也没用必须先确保执行顺序正确再考虑索引优化4. 分批处理大数据量// 按 ID 范围分批处理longminIdrange.getMinId();longmaxIdrange.getMaxId();intbatchSize1000;for(longstartIdminId;startIdmaxId;startIdbatchSize){longendIdMath.min(startIdbatchSize-1,maxId);intcountmapper.batchUpdate(versionNumber,startId,endId);totalUpdatedcount;}好处避免长事务锁表减少单次内存占用便于监控和重试 排查工具与命令1. 查看执行计划-- 只看计划不执行EXPLAINUPDATE...;-- 实际执行并显示详细信息EXPLAINANALYZEUPDATE...;2. 检查统计信息SELECTrelname,reltuples,relpages,last_analyze,last_autoanalyzeFROMpg_stat_user_tablesWHERErelnameyour_table;3. 手动更新统计信息ANALYZEyour_table;ANALYZEVERBOSE your_table;-- 显示详细信息4. 查看索引使用情况SELECTschemaname,tablename,indexname,idx_scan,idx_tup_read,idx_tup_fetchFROMpg_stat_user_indexesWHEREtablenameyour_table; 总结核心教训PostgreSQL 的优化器不是完美的统计信息不准时会选错执行计划Nested Loop 本身不是问题关键是驱动表的选择小表驱动大表子查询是强制正确顺序的有效手段特别适用于 UPDATE JOINEXPLAIN ANALYZE 是排查利器必须学会读懂执行计划性能优化公式正确的执行顺序 合适的索引 硬件升级一句话总结当 PostgreSQL 选择错误的执行计划时不要试图通过加索引来修复而是要通过改写 SQL如子查询来引导优化器做出正确的选择。 参考资料PostgreSQL 官方文档 - EXPLAINPostgreSQL 查询优化器原理Understanding Nested Loop Joins