踩坑实录：1W 数据查询引发 CPU 危机，我的优化避坑指南

提前退休的java猿
2,040 阅读10分钟

一、前言

2025年4月16日，天气阴，8点30刚出健身房，心里默念又是充满希望的一天✨

打开钉钉，一条@消息映入眼帘😱，感谢领导昨天晚上没有call🗽

image.png

昨天晚上 8点半，生产环境SQL占用cpu过高 🧨

🏁上面的SQL数据最多的表为3W条，其他表都是几千的数据量。为啥会导致cpu标高❓

执行了几次执行时间在20s左右，两三个人同时执行，直接卡死

二、分析过程

看到这个问题，我基本上已经定位到问题了。估计是 人大金仓 的 执行计划 和 服务器的 CPU 以及磁盘的性能存在问题！因为这个SQL 迁移之前也没问题的。

前两天也出现过SQL 超时的问题，还写了一篇文章: 小小的改动，竟然效率提高了1000倍
这次的问题 按照道理 应该也是一样，因为join了多张表，同时SQL的执行计划走的是Loop join导致消耗了很多cpu资源 。
💀联系了研发经理之后，用工具测试了一下性能，新环境的 性能 粗略计算的性能只有原来环境的三分之一（但是配置比以前环境的都高）

仅仅是服务器原因同样SQL执行时间不可能存在1000 倍的差距。关键还是数据库本身的优化器有关系。连表查询kingbase 可能走 Nested Loop 进行连表，有时候不会走 hash join. 走 Nested Loop 加上join的表有好几个，因此导致导致消耗掉了大量的cup性能.

✅看一下上面SQL的执行计划吧三层nested loop loop中的遍历次数粗略计算 10000 * 100 * 30000 次

1744902419989.png

2.1 为什么会走nested loop 循环呢？

看了两个官网的说明一个是mysql 官方文档，一个是postgrel sql 的官方问题。（人大金仓内核也是通过 postgrel sql 改的）

  • mysql：MySQL 8.0.18 及以后的版本❣尽可能的用 hash join,并且 8.0.20 开始禁用 block nested loop 官方文档：dev.mysql.com/doc/refman/…

  • pgsql: 当连表如果查询使用的关系少于 geqo_threshold(默认12)，会寻找❣最优的方式执行（nested loop、merge join、hash join）；官方文档：www.postgresql.org/docs/curren…

  • kingbase: 连表查询同pgsql 三种方式，官网上说了nested loop ❣适用于：内外表数据量不大的情况 或者内表数据量很小，外表数据量大的情况；官方文档：bbs.kingbase.com.cn/docHtml?rec…

    是不是人大金仓的官方文档是根据pgsql 写的读后感哦🤣 ，数据量小？多少算小，多少算大？

✅看了mysql和pgsql 的官方文档，至少来说很少走nested loop 去实现连表，如果走 nested loop 正常来说效率也挺高的。

那么kingbase的实现方式 和 pgsql底层实现一样的话，也很少会走nested loop循环。 kingbase 说的内外表数据量不大的情况适用于 nested loop，大小也没明确说明，再加上之前也遇到过一次loop导致SQL慢查询的问题，因为一个类型转换影响了 kingbase 的执行效率 。

✅假设论点：因为某些条件导致kingbase优化做出错误的抉择，从而走nested loop

论据一：

类型转换导致使用nested loop，这个问题就是条件中存在列类型转换导致 使用loop，改正确的类型之后，就是走的hash join了。

论据二：

按照我们的假设，我把【前言】报错的SQL 所有条件去掉。然后真的变成hash join，然后再一个条件一个条件加上最后加到这个条件ut.id IS NULL的时候，就变成nested loop 循环了。看看不加 这个条件的计划吧

1744902707232.png

🚀优化之后的时间就来到了700ms

✅结论：就是where 条件会影响 执行计划，而且效率直接指数级下降

应该只有kingbase容易出现这个问题吧💔

✅解决方案：

既然问题已经定位到了，那么就很容易解决这个问题了

  1. 删除ut.id is null 这个条件改成内存过滤
  2. 修改SQL再封装一层(不用join用子查询也行)，影响它的执行计划，修改如下：
    select * (select ...IFNULL(ut."id" , -1) AS cid from...) where cid = -1
  3. 使用 hint 语法指定走hash join 实现，修改如下（部分数据库支持，需要开启配置）：
    SELECT /*+Hashjoin( sr ut su uo,syr)*/ su."id",.............

2.2为什么Nested Loop如此消耗性能

  1. 什么是Nested loop ？

    翻译过来就是嵌套循环，如下MySql官网的距离说明

    image.png

    image.png

  2. Nested loop 时间复杂度
    知道嵌套循环之后，我们就可以大概估算出，在最极端的情况3表join的时间复杂度 为 O(r1 * r2 * r3),假设数量级在w的级别，10 * 1000 * 10000 = 1亿 ，可以看到数量不多的情况 使用Nested Loop 也会出现上亿次的计算。

所以Nested loop 比较消耗cpu 资源的;推荐阅读：:mysql 官网nested-loop

2.2.1 hash join 为什么这么快

  • 原理：Hash Join 通常分为两个阶段。在第一个阶段，它会在内存中为较小的表（这里假设两个 1000 条记录的表）创建一个哈希表。然后，在第二个阶段，它会扫描较大的表（10000 条记录的表），对于每一条记录，通过哈希函数计算其连接键的值，并在哈希表中查找匹配的记录。
  • 效率分析：如果内存足够容纳哈希表，Hash Join 的效率通常较高。对于这三个表的连接，假设连接条件合理，它可以快速地将两个较小的表与大表进行匹配。一般来说，其时间复杂度接近线性，即与三个表的总记录数成正比。在理想情况下，它可能只需要扫描每个表一次，因此总的扫描次数相对较少

✅时间复杂度对比：假设三个表（数据量为100、1000、10000）join查询,走nested loop 的时间复杂度为O（100100010000），那么hash join的复杂度O（100+1000+1000）
ps: 当然上面这个计算方式是简单粗暴的理解。 虽然我用的是kingbase，但是我有时候看一些文档，还是会去mysql、pg-sql看的，底层的这些核心算法逻辑，还是相通的。

2.2.2 nested loop 这么慢为什么不禁用呢

有些场景 nested loop 还是有优势的：

  • 小数据集情况： 当参与连接的表数据量都很小时，Nested Loop Join 可能比 Hash Join 更高效。因为 Hash Join 需要构建哈希表，这个过程存在一定的开销，像创建哈希表、分配内存等。而对于小数据集，Nested Loop Join 的简单嵌套循环操作开销更小，能更快完成连接。比如，表 A 有 10 条记录，表 B 有 20 条记录，使用 Nested Loop Join 进行简单的遍历比较，消耗的时间和资源会比构建哈希表要少。

  • 存在合适索引的情况： 若连接列上有高效的索引，Nested Loop Join 可以利用索引快速定位匹配的记录，减少不必要的比较操作。例如，表 A 是订单表，表 B 是客户表，连接列是客户 ID，且客户 ID 在表 B 上有索引。当执行连接操作时，Nested Loop Join 可以根据表 A 中的客户 ID，通过索引快速在表 B 中找到匹配的记录，这种情况下性能可能会优于 Hash Join。

  • 连接条件复杂的情况： 对于一些复杂的连接条件，尤其是包含范围查询、函数调用等，索引可以帮助 Nested Loop Join 筛选出部分符合条件的记录，减少需要扫描的数据量。而 Hash Join 通常更适合简单的等值连接，对于复杂连接条件处理起来可能不如 Nested Loop Join 灵活。

当然上面这些条件，也不是说绝对的就用 nested loop. 还是各个数据库的优化器有关系，优化器通常会选择最优的一种方式执行。直到我遇到了kingbase,以后连表 数据量超过 1w的都得 explain了🤑

2.3 如何解决Nested loop循环问题

2.3.1 数据库本身层面是如何优化的

  1. hash join 算法 （mysql）
    ✔MySQL（8.0.18 及更高版本）会尽可能的去使用hash 算法进行join。
    ✔MySQL 8.0.20 block nested loop 已经被移除，所以mysql 高版本使用loop循环的方式就更少。

    所以 mysql 的高版本对于 多表join，性能还是比较好的。但是数据量大的话，join表多话，就需要关注性能问题了。文章推荐：在sql 中谨慎使用多表join

  2. merge join 、hash join （pgsql、kinbase）
    pgsql 对于join 来说，就有三种选择 nested loop join、merge join、hash join ，当连表如果查询使用的关系少于 geqo_threshold，会寻找最优的方式执行。

  3. 修改配置参数 比如geqo_threshold连表的阈值，或者关闭 nested loop查询。（部分数据库）

2.3.2 写法层面

  • Explain： explain 分析查询是否使用loop，对where 条件进行排查，优化成hash join
  • 连表键：能否优化连表键，连表的键尽量走索引的吧
  • 分解查询：在内存中自己做关联，即先从数据库中把数据查出来之后，再次查询，然后再进行数据封装。
  • 考虑数据冗余：在某些情况下，可以考虑数据冗余来减少 JOIN 的需要。
  • 宽表：就是基于一定的join关系，把数据库中多张表的数据打平做一张大宽表，可以同步到ES或者干脆直接在数据库中直接查都可以
  • hint: 指定优化器的链接实现方式（部分数据库支持SELECT /*+Hashjoin( sr ut su uo,syr)*/ su."id",.............)

三、总结

本篇文章，分析了join查询在数据库中实现的核心算法nested loop 和 hash join 这两者之间的区别。以及分享了在kingbase 中where条件问题导致，join 查询走了nested loop 方式，导致CPU报警的案例。通过本篇文章，让我们了解join查询潜在风险，以及如何解决nested loop导致CPU飚高的的问题。

所以啊各位，连表查询的时候小心咯！可能换了一个数据版本环境就GG了💥
当然mysql 和 pgsql 应该问题不大的，国产库的话 升级环境就小心了💥
别以为，你的数据就几千，几万条，连起表来也能把你服务器干蹦☢

🙈kingbase 官方说明： image.png

avatar
文章
阅读
粉丝