SQL优化思想⓷--时间都去哪儿了

SQL优化思想系列

SQL优化思想⓵--不优化或许是最好的优化！

SQL优化思想⓶--让SQL跑得更慢一些！

引言 SQL优化思想已经讨论了两集，不论是“ 不优化是最好的优化 ”，还是“ 变慢是更好的选择” ，都在强调 做事要有 批判性思维 ，三思而后行。 接下来，将 探讨SQL优化本身的思路，看看如何让SQL跑得更快。毕竟， 衡量SQL快慢的标准就是 其运行时间的长短 。 围绕 “时间” 这个主题，结合超市购物 的 生活场景， 让我们步入正文，共同 探寻在XXX平台优化案例中，SQL的 时间都去哪儿了 。

0

时间都去哪儿了( 等待时间 +工作时间）

Q：L老师，听说您刚解决了困扰XXX平台许久的问题，到底是怎么做的？ L：我与他们一起分析SQL执行慢时， 时间都花在哪些环节 ，然后有的放矢地进行优化，最终成功解决问题。 M：哦，时间花在哪些环节呢？不就是执行环节吗？还有其他环节？ L：前些日子你抱怨在超市购物时，买单的时间比购物过程花的时间还长。其实，这事便能解答你的疑问。 M：哦，是吗？ L：你在超市买单时，通常会在收银台 排队 ，这就类似于SQL的 等待时长 。等到轮到你时，才能与收银员进行面对面 结算 ，这就类似于SQL的 工作时长 。 超市买单时间=等待时间 +工作时间 。 当你抱怨买单慢时，需明确耗时主要是在等待上还是在工作上。 M：我购物时，收银台的结算倒是很快，时间主要耗在排队上，也就是等待时间太长了。

L：是的，数据库的SQL也是如此。 SQL执行时间= 等待时间+工作时间 。接下来，我们就通过类比超市买单的场景，来探讨XXX平台的SQL优化案例。

1

减少CPU持有等待时间(增加收银台）

L：倘若我们期望让买单的时间加快，却发现收银柜台的队伍排得很长，顾客们等得非常焦虑，该如何是好？ W：可以考虑增加收银台的数量，假如收银台由1个变成10个， 排队等候买单的时间自然就减少了。 L：没错！有点类似数据库中的 减少 CPU持有等待时长的思路，在 ZZZ平台的A模块 中 ， 部分SQL就是这么优化提速的，具体示例如下。

SELECT calculate_total(order_id)FROM ordersWHERE status = 'pending';

在高并发情况下，CPU持有等待时间显著增加。解决方案可以是增加CPU个数，或者采用分布式数据库架构来分担压力。

-- 在分布式架构中分片查询
SELECT calculate_total(order_id) FROM shard_1.orders WHERE status = 'pending';
SELECT calculate_total(order_id) FROM shard_2.orders WHERE status = 'pending';
...

通过将查询分布到多个分片，能够有效减少单个CPU的负担，从而降低持有等待时长，提高整体查询效率。

2

减少硬解析等待时间(单件再相乘）

L：排队是需要等待，其实在收银台结算交易时也存在等待时长。你购买的多个货物不可能同时结算，要一件一件来，这间隙时间便是等待时间。比如 你买了5袋相同的洗衣液（品牌批次价格均一致），如果收银员够聪明的话，他会怎么做？ W：他结算时不需要刷5遍条形码，而是只刷1次，然后数量乘以5即可。 L：聪明！这与 数据库中 减少硬解析等待时长的思路类似。在 XXX平台的B模块 中，部分SQL就是这么优化提速的 ， 请看示例。 原始查询（导致硬解析）。

SELECT * FROM users WHERE username = 'user1';
SELECT * FROM users WHERE username = 'user2';
...

-- 每次都重新解析

优化后查询（使用绑定变量）。

SELECT * FROM users WHERE username = :username;
-- 只需解析一次

通过使用绑定变量，SQL可以复用执行计划，减少硬解析次数，从而降低等待时长，提高查询效率。

3

减少日志切换等待时间(多鱼装一袋） L：再举一个和洗衣液不同的例子， 你买了5只相同品种的鱼，分别包装称重（重量不同，价位不一样），收银员就不得不进行5次结算， 有没有提高效率的办法呢 ？ M：如果这5只鱼放在一起称重，那就从5袋变成1袋，收银员就会从 结算5次缩减为只结算1次 了。 L：很棒！这类似 数据库中 减少日志切换等待时长的思路，在 XXX平台的C模块 中，部分SQL就是这么优化提速的 ， 请看示例。 原始代码示例（循环内提交）：

FOR i IN 1..1000 LOOP
    INSERT INTO orders VALUES (i, 'Order_' || i);
    COMMIT; -- 这里就是问题所在
END LOOP;

优化后代码示例（循环外提交）：

FOR i IN 1..1000 LOOP
    INSERT INTO orders VALUES (i, 'Order_' || i);
END LOOP;
COMMIT; -- 只提交一次

通过 将COMMIT操作移出循环内 ，让该SQL从 提交多次变成了只提交1次 ， 减少了不必要的日志切换等待， 让SQL执行得更快。

4

减少锁相关等待时间(开老人通道）

L：有些老年人在 从购物车拿出货物时 ，可能会因为阿尔茨海默等各种原因 忽然就 发呆 了 ，此时收银台的 结算交易就会停滞 ，后面的顾客就不得不进行长时间等待，对此你有什么好办法吗？ Q：可以为老人们开通特殊收银通道，由工作人员帮助其完成结算。 L：机智！这类似于 数据库中的减少 锁相关等待时长的思路，在 XXX平台的D模块 中，部分SQL就是这么优化提速的 ， 请看示例。 原始查询（其他事务更新相同account记录时，可能存在锁等待）：

UPDATE accounts
SET balance = balance - 100 WHERE account_id = 123;
-- 其他事务也更新account

优化思路则是要考虑 尽快释锁资源 ，除了 及时的提交 外，还包括在account_id字段上建立恰当的索引等，以 加快更新速度 ，从而缩减锁等待时间。

结语

SQL运行中的相关等待事件远不止上述四个， 限于篇幅，就不一一展开了。如果让大家觉得意犹未尽，敬请谅解 。以下是对执行时间的一个脑图总结。

值得一提的是，在某一个SQL中，等待时长和工作时长往往是多段的。 如 T_等待=T_cpu等待+T_硬解析+T_日志切换+T_锁等待.... 同理，T_工作也是如此，往往是 多个子工作时间的集合 。 通过减少等待时长，我们已经显著提升了SQL的执行效率。然而，在等待时长已经得到充分优化的情况下 ， 如何进一步 降低工作时间 ，也就是加快交易本身的速度呢？敬请期待。

未完待续...

注

1. 本集的SQL优化以OLTP领域的 事务型SQL 为主。因为OLAP领域的分析型SQL任务本身较大， 这些等待事件对其整体性能的影响就不大了 ；
2. 这是SQL优化思想系列，目的是希望能在思想层面给读者带来启发，提升其大局观。所以具体的 技术细节本文就不做深究 ，点到为止。

更多精彩原创内容见公众号 点关注 不迷路 往期回顾，欢迎留言与转发

• 小朋友都能懂的人工智能⓵
• 小朋友都能懂的人工智能⓶ -卷积神经网络初探
  
• 小朋友都能懂的人工智能⓷ -惊世骇俗的狗故事

• 小朋友都能懂的人工智能⓸ -狗大师的修仙之路

• 小朋友都能懂的人工智能 ⓹ -不可思议的大模型

• 小朋友都能懂的人工智能⓺ -注意，句中高能！

• 小朋友都能懂的人工智能⓻ 无名故事与GPT4训练
• 小朋友都能懂的人工智能 ⓼ 无名故事与GPT4 推理
• 小朋友都能懂的人工智能⓽Hi AI,Database is all you need
• 小朋友都能懂的人工智能⓾大白话数据库
  

• 小朋友都能懂的人工智能⑪一滴墨汁成就一代画师

• 小朋友都能懂的人工智能⑫从画师到视频大师

• 小朋友都能懂的人工智能⑬AI时代，未来就业走势

• 数据库二十年目睹之怪现状⓵ 太！多！了！

• 数据库二十年目睹之怪现状⓶ 测评现形记

• 数据库二十年目睹之怪现状⓷ 隐蔽的套壳

• 数据库二十年目睹之怪现状⓸ 小黑入狱记

• 从DTCC专场变换窥探数据库风云

• 从围棋收官到秦楚大战的数据库SQL实现（上）

• 从围棋收官到秦楚大战的数据库SQL实现（中）

• 从围棋收官到秦楚大战的数据库SQL实现（下）

• ⓵悟！从12306改签困惑到数据库设计—高铁随记

• ⓶惊！12306业务系统升级也能被普通人推动？

• ⓷妙！探索12306车内换座的最优分配法

• 国产数据库第一股背后的秘密

• SQL优化思想⓵--不优化或许是最好的优化！

• SQL优化思想⓶--让SQL跑得更慢一些！

• “作弊”有理！——OceanBase2024发布会勾起我二十年前的回忆

预告：《超融合数据库》即将出版 。