PostgreSQL慢SQL优化分享

SQL优化的核心在于如何正确读懂执行计划，充分了解SQL慢的具体原因，并根据执行计划让优化器选择合适的扫描方式、连接方式、合理利用索引，减少无效数据扫描。一、什么是执行计划大白话解释：写的SQL是 “需求”（比如 ：查询 user 中 id=100 的用户信息），而执行计划就是数据库经过计算给出的具体 “实施方案”。它会根据数据库库表索引等的实际情况判断：通过哪种方式（是直接扫描整个用户表，还是通过索引快速定位到目标行）获取数据更快，更节省资源，从而为用户提供最优的方案。虽然数据库优化器会提供最优的计划，但偶尔也会因统计信息过时、索引不合理等情况，产生 “低效计划”，这就是我们需要分析执行计划的核心原因。核心结论：看懂执行计划，就能搞清楚SQL执行的执行情况，找到SQL慢的“根因”，从而有针对性地对进行优化。二、如何获取执行计划EXPLAIN + SQL： 查看预估执行计划，不实际执行SQL，只返回优化器预估的执行计划，速度快，适合快速排查SQL的执行逻辑。EXPLAIN ANALYZE + SQL：实际执行计划，不仅输出执行计划，还会实际执行SQL，返回真实的扫描行数、执行时间、循环次数等。注：生产环境注意避免执行EXPLAIN ANALYZE，避免复杂耗时SQL对生产环境数据库产生性能影响，可先在测试环境复现。三、执行计划解析树形结构与执行顺序执行计划为自底向上执行的树形结构叶子节点：缩进最深、最底层中间节点：缩进介于两者之间根节点：无缩进、最顶层执行顺序：缩进越多 → 层级越深 → 越靠近叶子节点（先执行）；缩进越少 → 层级越浅 → 越靠近根节点（后执行）当多个叶子节点缩进层级相同（属于同一父节点的子节点）时，执行顺序遵循“左到右 / 上到下”的核心规则核心指标名词解释cost（0.00..100.00）：..前是 “启动成本”（返回第一行的成本），..后是 “总的执行成本”，（数值越小，效率越高）rows：预估行数，可与 EXPLAIN ANALYZE 中的 actual rows 对比，若差值过大，说明统计信息不准确。width：字节数，预估当前返回每行的数据字节大小，值越大，内存和IO开销越高。EXPLAIN ANALYZE 专属字段actual time（0.00..100.00）： 实际启动时间…实际执行时间actual rows: 实际行数，行数过大，可优化过滤条件，减少后续数据处理压力loops：循环次数（嵌套循环会多次执行）扫描类型解析执行计划的扫描类型是优化器基于成本（IO成本、CPU成本）选择的最优方案，优化器会计算每种扫描方式的成本，优先选择成本最低的扫描方案。扫描方式是执行计划中的核心部分，直接决定SQL语句的执行速度，以下为四种常见的扫描方式1.Seq Scan：全表扫描，多数情况下效率最低，也是SQL优化的重点排查方向名词解释：Filter: 行级过滤条件Rows Removed by Filter: 被 Filter 条件过滤掉的行数触发条件：查询选择性低（返回行数/表总行数 20%~30%），优化器认为走索引的随机 IO 成本 全表顺序 IO 成本表数据量极小查询条件没有索引表的统计信息不准确，优化器无法获取准确的返回行数索引失效（如字段有隐式类型转换）性能特点：优点：顺序IO，磁盘效率高，不需要扫描索引+回表操作缺点：扫描所有记录，表数据量大时，过滤条件复杂时，CPU和IO开销极高样例：-- 查询sqlSELECT*FROMusersWHEREage10;-- explainSeq Scanonusers(cost=0.00..19424.00rows=990000width=100)(actualtime=0.02..200.10rows=990000loops=1)Filter:(age10)-- 过滤条件：只保留age10的行RowsRemovedbyFilter:10000-- 被过滤的行数2.Index Scan：索引扫描（先遍历索引，再回表读取行数据），多数情况下效率高名词解释：Index Cond: 索引筛选条件Filter：回表过滤条件触发条件：查询选择性高，优化器认为随机IO的成本 全表/位图扫描查询条件存在匹配的有效索引优化器计算索引扫描的成本最低。性能特点：优点：过滤条件在索引扫描时完成，无全表扫描，CPU/IO 开销低缺点：表中的数据的物理位置是无序的，索引扫描会根据索引键频繁 “随机访问” 数据表，磁盘随机IO成本高样例：-- sqlEXPLAINANALYZESELECT*FROMusersWHEREage=88;-- explainIndexScanusingidx_users_ageonusers(cost=0.43..8.45rows=10width=100)(actualtime