前言
🧨5月16号，一条SQL把数据库服务器干爆了🧨
这个问题出现过好多次了,但是这次这个SQL是我写的，加上最近有重要的业务在开展，产生了很多脏数据。
当时气氛一下就紧张起来了，快！快！快！紧急修复啊!
看了一下SQL，确实有问题😭 :
select count(*) from A left join B on A.b_id = B.id
本来A表中的b_id 是bigint，但是确实设置的是varchar类型, 完了不会是我的问题吧！
当时就先紧急发版本了，把这个SQL注释掉了！ 当时数据库的CPU已经被打满了，我肯定不敢再调试，但是作为一个老油条这个问题我也不能背锅呀😉。
✈️那就开始我们的问题定位，和甩锅之旅途吧✈️
问题定位
前言中已经透露了两个关键关键信息
- 第一就是
A.b_id = B.idA表中b_id类型存在问题 - 执行计划走的是
nested loop循环，执行计划如下
Finalize Aggregate (cost=97553.09..97553.10 rows=1 width=8)
-> Gather (cost=97552.88..97553.09 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=96552.88..96552.89 rows=1 width=8)
-> Nested Loop (cost=0.29..96550.62 rows=903 width=0)
-> Parallel Seq Scan on A_bak20250318 a (cost=0.00..92882.53 rows=903 width=6)
Filter: ((NOT del) AND p_group AND (m_status = 1) AND ((c_status)::integer = 3))
-> Index Scan using B_pkey4 on B b (cost=0.29..4.06 rows=1 width=8)
Index Cond: (id = (a.b_id)::bigint)
Filter: (((c_path)::text ~~ '9987.%'::text) OR ((_path)::text = '9987'::text))
当然，如果去掉
join肯定是能解决这个问题的，但是join在我们这种数据体量正常情况是肯定没有问题的(前提是join 两三个表)
猜想一：索引失效
很容易就想到了是不是字段类型的问题，导致索引失效走了nested loop，从而导致CPU飙高呢？
排除是索引失效导致CPU飙高的问题
- 仔细看上面的执行计划的话，会发现 索引是没有失效的，但是确实也进行了类型转换。
- 修正数据库字段，再看执行计划，和上面的唯一区别就是少了一个类型转换
- 对比（修改字段类型前后）执行时间都是3s左右
根据修改字段类型前后的执行计划、执行时间至少能够确认这并不是索引失效影响了SQL的执行计划推理，以及导致CPU飙高的问题
猜想二，改成子查询是否能解决呢
select count(*) from A where b_id in (select id from B..)...
执行计划和join查询一样，执行时间也差不多. 也排除了改成只查询就能解决这个问题
猜想三，数据库执行计划又出毛病儿呢，不能走nested loop
关闭当前会话的nested loop 算法(SET enable_nestloop = off;
)，再看执行时间和执行计划
Finalize Aggregate (cost=98340.72..98340.73 rows=1 width=8)
-> Gather (cost=98340.50..98340.71 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=97340.50..97340.51 rows=1 width=8)
-> Parallel Hash Join (cost=4453.23..97338.24 rows=903 width=0)
Hash Cond: ((a.b_id)::bigint = b.id)
-> Parallel Seq Scan on A_bak20250318 a (cost=0.00..92882.53 rows=903 width=6)
Filter: ((NOT del) AND p_group AND (p_status = 1) AND ((k_status)::integer = 3))
-> Parallel Hash (cost=4275.41..4275.41 rows=14226 width=8)
-> Parallel Seq Scan on B b (cost=0.00..4275.41 rows=14226 width=8)
Filter: (((c_path)::text ~~ '9987.%'::text) OR ((c_path)::text = '9987'::text))
执行时间：700ms ✌️没错这才是我SQL的真正执行时间，都是数据库和服务器的问题，
至少能甩给数据库。
✅在执行计划探测测段，估算的A的行数为900行左右，实际上达到了应该返回几十万行，所以数据库底层就把两个表当作了数据量很少的情况，这就引发了数据库走Nested loop 循环。这才是问题问题的根本所在。
导致 Nested loop 成本 小于了 Hash join 成本。
锅就甩到这儿呢，下面就分享点硬核知识了
✅解决方案
这条SQL本该执行Hash join但是却走了Nested loop，走Nested loop的原因我们也找到了，由于在执行计划阶段，预估行数的时候出了问题，导致算出得成本用Nested loop 更优，从而执行的时候选择了错误的方式。
知道了问题所在，那么就好解决了，先解决SQL中类型的转换的问题，然后更新统计信息ANALYZE VERBOSE your_table再次查看执行计划就回归正常了，走hash join,并且主表的探测行数快20W了
需要更准确的行数预估，就增加取样的行数吧（不知道怎么设置的看下文）
一劳永逸的方案：直接禁用nested loop循环（谨慎考虑，结合公司业务场景）；开启hint语法配置，指定执行计划（建议）
✅知识扩展（硬核知识）
如何分析执行计划(KingBase)
执行计划中的cost 和 rows 都很好理解，一个是执行成本(并不是耗时)，一个返回行数，下面以上面两个执行计划，给大家解释一下吧
关于
hash join和nested loop这两个联表算法，我就不再解释了，想了解的可以看一下我之前写的文章：
示例一：nested loop
Finalize Aggregate (cost=97553.09..97553.10 rows=1 width=8)
-> Gather (cost=97552.88..97553.09 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=96552.88..96552.89 rows=1 width=8)
-> Nested Loop (cost=0.29..96550.62 rows=903 width=0)
-> Parallel Seq Scan on A_bak20250318 a (cost=0.00..92882.53 rows=903 width=6)
Filter: ((NOT del) AND p_group AND (m_status = 1) AND ((c_status)::integer = 3))
-> Index Scan using B_pkey4 on B b (cost=0.29..4.06 rows=1 width=8)
Index Cond: (id = (a.b_id)::bigint)
Filter: (((c_path)::text ~~ '9987.%'::text) OR ((_path)::text = '9987'::text))
执行计划详解
- 顶层操作 - Finalize Aggregate
- 这是查询的最终操作，负责汇总各个并行工作进程的部分聚合结果。
- 预估会返回 1 行数据，处理成本在 97553.09 到 97553.10 之间。
- Gather 操作
- 该操作会协调 2 个并行工作进程，把它们的结果收集起来。
- 它的子操作是 Partial Aggregate，也就是部分聚合。
- 并行处理 - Partial Aggregate
- 每个工作进程都会进行部分聚合计算。
- 成本估计在 96552.88 到 96552.89 之间。
- 数据关联 - Nested Loop
- 采用嵌套循环的方式将表 A 和表 B 进行连接。
- 外层循环是对表 A 的扫描，内层循环则是对表 B 的索引扫描。
- 表 A 数据扫描 - Parallel Seq Scan on A_bak20250318
- 对表 A 进行并行顺序扫描，这是因为表数据没有合适的索引，所以选择并行处理来提高效率。
- 扫描条件为：
NOT del AND p_group AND m_status = 1 AND c_status::integer = 3。 - 预估会返回 903 行数据，成本为 92882.53，在整个查询成本中占比最大。
- 表 B 数据检索 - Index Scan using B_pkey4 on B
- 依据表 B 的主键索引（B_pkey4）来查找匹配的行。
- 索引条件是：
id = a.b_id，这表明是通过外键进行关联的。 - 过滤条件为：
c_path LIKE '9987.%' OR _path = '9987'。 - 每匹配到表 A 的一行数据，就会执行一次这个索引扫描，每次扫描成本约为 4.06。
示例二：hash join
Finalize Aggregate (cost=98340.72..98340.73 rows=1 width=8)
-> Gather (cost=98340.50..98340.71 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=97340.50..97340.51 rows=1 width=8)
-> Parallel Hash Join (cost=4453.23..97338.24 rows=903 width=0)
Hash Cond: ((a.b_id)::bigint = b.id)
-> Parallel Seq Scan on A_bak20250318 a (cost=0.00..92882.53 rows=903 width=6)
Filter: ((NOT del) AND p_group AND (p_status = 1) AND ((k_status)::integer = 3))
-> Parallel Hash (cost=4275.41..4275.41 rows=14226 width=8)
-> Parallel Seq Scan on B b (cost=0.00..4275.41 rows=14226 width=8)
Filter: (((c_path)::text ~~ '9987.%'::text) OR ((c_path)::text = '9987'::text))
看了[示例一]得分析之后，再看这个Hash join 的计划没那就没啥了。总成本从约 97,553 增加到约 98,340。
A表的预估rows都是903 偏离实际两个数量级了，这也是导致成本计算出了问题。
✅还有一个重要的关键词workers Planned :是一个与并行查询执行相关的重要参数。它表示优化器计划为当前查询分配的并行工作进程（Worker Processes）数量，这些进程将同时执行查询的某些操作，以提高处理速度。当一个查询满足以下条件时，优化器可能会选择并行执行：
- 数据量足够大：小表通常不值得并行处理。
- 操作支持并行：如顺序扫描、哈希连接、聚合等操作可以并行化。
- 资源允许：服务器有足够的 CPU 核心和内存来支持额外的工作进程
影响执行计划的因素
1.SQL 本身问题
- 链表方式、数量
- 索引失效
- 类型转换
- 数据量
2.统计信息准确性
优化器依赖表和索引的统计信息估算成本，若统计信息过时或不完整，会导致执行计划偏差，优化方式：
- 更新统计信息
-- KingBase（PostgreSQL）：更新统计信息
ANALYZE your_table;
ANALYZE VERBOSE your_table; --强制
-- MySQL：更新统计信息
ANALYZE TABLE your_table;
- 增加取样数量
-- PostgreSQL：增加统计目标
ALTER TABLE your_table ALTER COLUMN your_column SET STATISTICS 1000;
3.服务器硬件配置
- 内存不足：若
innodb_buffer_pool_size过小，频繁磁盘 I/O 会使随机读成本显著增加。 - 磁盘类型：SSD 的随机读写性能远高于 HDD，可降低
random_page_cost参数。 - CPU 核数：多核 CPU 可提升并行查询性能，需调整
max_parallel_workers_per_gather。
4.数据分布
- 数据倾斜：如某字段的大部分值集中在少数几个值上。
- 冷热数据分布：频繁访问的 “热数据” 若未缓存，会增加随机读成本。
总结
本次CPU打满，查看数据库这条SQL阻塞了10多条，只能说这条SQL确实消耗CPU资源，但是咱不背锅。通过对执行计划的分析，我们定位到是统计信息不准确，导致探测返回行数的与实际情况相差50倍，数据库做出了错误的执行计划。
最后还给大家总结了 执行计划相关的知识，希望各位大佬点赞收藏呀，感谢👍👍👍👍👍👍
推荐阅读：
