SQL 调优典型场景和案例

SQL 优化基本上是指调优 SQL 的执行计划，设计良好的索引组合，减少数据扫描行数是最有效的调优手段。除此之外，SQL 优化与应用和数据库的优化都密不可分。

SQL 调优的宏观概念是寻找“应用通过 SQL 请求从数据库中获取数据”的最佳实践。单纯针对一条 SQL 语句，我们能做的优化策略并不多，例如优化一条没有任何过滤条件的 SQL 根本无从下手，我们可能会产生疑问“这条 SQL 真的有必要吗”。由此，本章节主要从广义的角度来介绍 SQL 调优的典型场景和案例。

本章节汇总了 OceanBase 数据库常见的 SQL 调优场景和一些案例讲解，通过分析这些真实案例来展现常用的 SQL 诊断调优手段。

SQL 容量管理

SQL 性能消耗过大

SQL 逐渐变慢

场景解析：

SQL 性能在一段时间内逐渐变慢，典型的业务场景如下：

• 业务周期性的写入数据，数据量会随着业务写入批次而慢慢增多，SQL 性能随着数据量变多而变慢。
• 大数据集的分页查询，随着需要查询的页数增大，需要扫描的数据越多，分页 SQL 的性能也会越来越慢。

案例分析：

• 场景：电商订单历史查询

  某电商平台的订单历史查询 SQL 随着业务增长逐渐变慢。初始阶段查询 1000 条订单需 0.5 秒，但半年后查询相同数量订单耗时超过 10 秒。

• 原因： 订单表数据量从 100 万增长到 5000 万，且未建立覆盖 order_id 索引，导致全表扫描。

优化建议：

针对数据量级堆积导致的 SQL 逐渐变慢，优化建议如下：

• 可以增加全覆盖索引，使用 order + limit 控制单次处理行级，均匀的处理数据，保持 SQL 性能相对恒定。
• 可以保证业务生产和下游消费的均衡，尽量均匀的写入、均匀的消费，不要让数据产生堆积。如果有堆积场景，可以使用数据搬迁的方式，将堆积数据通过异步任务不断的均衡开，保证下游消费的性能相对恒定。

针对大数据集分页导致的 SQL 逐渐变慢，优化建议如下：

分页查询不建议使用 offset，因为大页的性能会很差，针对这种情况，可以使用主键 id 传递的方式，例如每次查询第 n 页的时候，将上次查询的那一页末尾或者主键 id 传递给下一页查询的 SQL，这样就可以根据“主键 id 或上一页结果+ ORDER BY LIMIT n”来快速获取数据。

SQL 请求量突增

场景解析：

SQL 执行次数突增，是指在短时间内 SQL 执行频率相比其他正常时间内的请求量大了非常多，从而导致 SQL 占用性能消耗变多，可能会打爆租户 CPU 从而影响数据库整体性能。典型的业务场景如下：

• 业务定时任务
• 促销活动
• 缓存击穿

案例分析：

• 场景：电商大促期间的库存查询 某电商在“双十一”大促期间，库存查询 SQL 请求量突增至平时的 10 倍，导致租户 CPU 占用率飙升至 95%，其他业务受影响。

• 原因：库存表未做流控，且未配置 CPU 超卖资源。

优化建议：

• 业务优化建议：在业务层增加前置流控，防止请求瞬间打爆数据库，常见场景包括缓存击穿、业务促销、定时任务等。
• DB 优化建议：需要给数据库 CPU 计算容量，增加一定的 buffer CPU，也可以开启租户级 CPU 超卖，在应急情况自动使用超卖的 CPU。
• SQL 应急建议：针对异常 SQL 的请求波动，为了恢复数据库整体服务，可以对异常 SQL 进行限流，牺牲单一 SQL 上的业务来恢复全局。

SQL 业务热点

读热点

场景解析：

读热点指在短时间内查询了同一行记录，所以单一账号在某段时间的 SQL 请求发生突刺。在场景上，SQL 读热点属于 SQL 执行次数突增中的一种，但是如果能定位到具体的账号，可以针对性的对账号进行流量控制，可以做到只影响这个账号的请求，减少止血动作产生的业务影响。

案例分析：

• 场景：银行 VIP 账户查询 某银行系统中，VIP 用户账户的余额查询请求在 1 小时内突增 10 倍，导致单表锁等待飙升。

• 原因： VIP 账户数据集中查询，未限制单账号请求速率。

优化建议：

在 SQL 应急阶段，可以将对应热点账号的请求做限流，降低并发恢复数据库。

写和锁热点

场景解析：

写热点往往会触发锁竞争，从而导致 SQL 大量失败重试或者锁等待，最终引发数据库异常。常见的写热点有对同一行记录执行 UPDATE 或者 SELECT FOR UPDATE。

案例分析：

• 场景：物流系统订单状态更新 某物流系统中，某热门订单因状态频繁变更（如 UPDATE status），导致 SELECT FOR UPDATE 产生大量锁冲突。

• 原因：未在 SQL 中添加 NOWAIT，导致事务长时间等待。

优化建议：

执行 SELECT FOR UPDATE 时建议使用关键字 NOWAIT，并且在业务代码层来实现重试机制，避免在数据库层产生锁竞争而拖垮数据库。在 SQL 应急阶段，可以将对应热点账号的请求做限流，降低锁并发。

大小账号

场景解析：

大小账号是由于业务数据倾斜引起的一种 SQL 优化场景，往往是由于表中某一类账号相比其他账号的数据量大很多引起的。当大账号的 SQL 查询执行时，需要扫描的数据量很大，会出现性能波动。

案例分析：

在如下业务场景中，特别容易出现大小账号场景。

当系统中存在不同的业务体量，数据量整体呈现一个倒金字塔的形状，即很少的业务占据整体绝大部分的数据。这就导致大业务的 SQL 请求，性能会比其他小业务要差很多。

优化建议：

针对日常优化，单纯从 SQL 层面没有可用的优化手段，但可以从业务架构层面提供如下优化方式：

• 将大小账号数据拆分，数据表维度除了水平分片，还可以按大小账号进行垂直拆分，尽量减少数据倾斜的情况。
• 将大小账号请求分流，不同的账号可能对应的 SQL 索引需求还不一样，如果能对账号维度进行分流可以有效控制 SQL 走的索引，还能进行优先级保障，例如：SQL 应急时暂时排查不到具体的账号，那可以只限流一个通道的 SQL，保障其他业务的稳定。
• 尽量采用分页查询方式，以 ID 分片传递，每次通过“id > 上一页末尾 id”的方式每次只查询一个分片的数据，然后在业务层进行数据汇总，这样不管是大商户还是小商户，在 SQL 层面性能保持恒定。

针对应急场景，除了扩容租户规格和限流 SQL 也没其他好手段，但可以限流对应大账号的 SQL 请求。

SQL性能优化

执行计划优化

执行计划变差

场景解析：

OceanBase 数据库在 SQL 优化阶段有一个 Plan Cache 机制，即：

1. SQL 第一次请求时会进行硬解析，根据当次 SQL 的参数值进行成本计算，生产相应的执行计划，并写入 Plan Cache 缓存中。
2. SQL 下一次请求进来时会查看 Plan Cache 中是否有该 SQL 缓存的计划，如果有，即命中计划缓存并采用对应执行计划去执行 SQL。

SQL 硬解析是个很消耗 CPU 的过程，Plan Cache 机制可以有效的提升 SQL 性能和整体 Database 吞吐。但是，Plan Cache 机制本身也会引入一些问题，例如 SQL 执行计划的“好坏”，取决于 SQL 硬解析当次入参的“好坏”。换言之，SQL 第一次请求进来的参数值并不一定满足大多数请求的场景，可能是个“少数派”，反而可能会导致“多数派”性能不佳。另外，如果数据分布变化了，老计划不适用于新场景，也会导致 SQL 性能下降。

案例分析：

• 场景：用户行为统计 SQL 性能波动 某社交平台的用户行为统计 SQL 在月初执行时性能良好（0.1 秒），但月末执行时变为全表扫描，耗时 10 秒。

• 原因： 初始参数是小范围 date 值（数据量小），而月末参数覆盖全月数据，但未触发索引。

优化建议：

正常走索引的执行计划性能明显好于异常的全表扫描计划，建议应急时绑定正确的计划。

Buffer 表

场景解析：

Buffer 表是指一种特殊业务场景下才会触发的一种 SQL 异常：

• 业务场景为数据执行 INSERT 后，且绝大部分数据很快会被 DELETE，即表存量数据很小，一般情况下该表上的 SQL 执行计划都是主表全表扫描，因为 OceanBase 数据库会对表的数据块“空洞”做回收，所以表的数据块高水位很低，主表扫描成本很低。

• 短时间内 INSERT 和 DELETE 的数据量级非常大，且表的数据块高水位没有及时回收、或者 INSERT 量级大于 DELETE 量级导致数据积压，导致真实扫描的表数据块数据较多，SQL 性能下降。

只有同时满足以上两种条件的小概率 SQL 场景才会触发 Buffer 表的 SQL 性能异常。另外，索引字段的超高频率执行 UPDATE 也会一定概率触发索引表 Buffer 情况，因为索引字段的 UPDATE 是通过INSERT 和 DELETE 来维护索引表的。

案例分析：

• 场景：临时任务表性能骤降 某任务调度系统中，临时任务表因短期插入 100 万条数据后未及时清理，导致查询时高水位未回收，全表扫描性能暴跌。

优化建议：

建议日常情况不做操作，Buffer 表属于低频小概率场景，触发条件苛刻，应急情况下通过 outline 绑定 settle_id 索引。如果为了保证 SQL 性能稳定，不至于突然恶化，也可以牺牲一些日常性能，可以在Hint 中指定 settle_id 字段的索引。

多计划抖动

场景解析：

该场景为 SQL 有多个索引可选择，在不同的数据分布下，执行计划可能会选择不同的索引。因为 OceanBase 数据库优化器总是在任何时候都希望找出最优解，它会根据当前的数据分布以及入参估算 SQL 执行成本，选择成本最低也就是性能最好的计划。随着业务数据写入或者变更，数据分布发生变化，在不同时间可能会生成不同的执行计划，且不同值的数据分布差异可能也会比较大，也会产生多计划之间来回变换的情况。

案例分析：

• 场景：订单状态查询计划波动 某订单系统中，SELECT * FROM orders WHERE status = 'PENDING' AND env = 'PROD' 的执行计划在不同 status 值下频繁切换索引（如 status 索引和 env 索引）。

优化建议：

针对有多个索引可选场景的执行计划抖动，可以从如下方面来优化：

• 尽量不要建太多互相冗余的索引，如本例中 status、env、env+status 三个索引有互相重叠，为了避免歧义，可以干掉如 status 单列的索引，防止其他场景误选，同时也可以节省索引表的存储成本。
• 尽量不要在枚举类型的字段上建索引，比如性别、年龄、类型、状态等这种枚举值很少的字段，除非有非常明确能过滤极少量值的枚举场景，举例如：status 状态字段，99% 数据是初始化，1% 数据是待处理，业务场景是 insert 初始化状态的数据，然后根据主键将 status 字段 update 为已完成，只有 1% 的异常数据是待处理，有一个补单机制需要查询出该表中待处理的数据，则此时 status 字段上的索引扫描性能会很好。

• 针对数据倾斜特别大的场景，需要从业务层来优化，可以参考大小账号章节的优化建议。

索引优化

索引选择错误

场景解析：

该场景为 SQL 有多个索引可选择，但执行计划选择的索引不是最佳性能，可能的原因如下：

• SQL Hint 中指定了不佳的索引。
• SQL Outline 曾绑定过不合适的索引。
• SQL 硬解析阶段入参的数据分布属于小概率场景，从而选择了错误的执行路径。
• SQL 优化阶段的数据分布生成的计划，不适合于业务运行一段时间变化后的数据分布情况。

案例分析：

• 场景：用户信息查询索引误选 某用户信息查询 SQL 本应走 user_id 索引，但因 OUTLINE 绑定 user_name 索引（数据倾斜严重），导致全表扫描。

优化建议：

建议取消 SQL Hint 中指定索引，让优化器自动选择，或者指定 idx_fund_inst_type_time 索引。

没有创建索引

场景解析：

该场景为 SQL 过滤条件没有合适的索引可以走，只能全表扫描或者走其他索引，导致性能低下。

案例分析：

• 场景：物流轨迹日志查询 某物流系统中，SELECT * FROM tracking WHERE dag_task_id = '123' 需扫描全表，因未建立 dag_task_id 索引。

优化建议：

• SQL 优化建议：在合适的字段上创建索引，本例中可以在 dag_task_id 上新增一个普通索引，因为普通索引最后会自带主键 task_id 字段，所以也能消除 task_id 的排序。
• 业务流程建议：建议在代码集成阶段进行 SQL Review，防止这种没有索引的 SQL 上线。

索引创建不合理

场景解析：

该场景为 SQL 能走上索引，但是性能仍然比较差，但是有合适的字段可以创建更好性能的索引。

案例分析：

• 场景：检测未支付的超时订单的 SQL（创建时间超过 60 分钟未支付） 某订单超时检测 SQL SELECT * FROM orders WHERE TIMESTAMPDIFF(MINUTE, gmt_create, NOW()) > 60 性能极差。

• 原始 SQL

  SELECT * FROM orders
  WHERE TIMESTAMPDIFF(MINUTE, gmt_create, NOW()) > 60
  AND scene = 'online_payment'
  AND effective = 1;
  

• 原因：

  • 函数操作导致索引失效：TIMESTAMPDIFF(gmt_create, NOW()) 的函数操作使 gmt_create 字段无法使用索引。
  • 索引设计不合理：表中存在 scene 和 gmt_create 的联合索引 idx_scene_time，但未包含 effective 状态字段，导致索引无法覆盖所有条件。

优化建议：

基于上述 SQL 场景的分析，优化建议如下：

• SQL优化有如下两个建议：

  • 去除 gmt_create 字段上的函数操作，可以将 TIMESTAMPDIFF(MINUTE, gmt_create, now()) > ? 改写为 gmt_create > DATE_SUB(now(), INTERVAL ? MINUTE)。
  • 将 scene + gmt_create 索引改为 scene + effective + gmt_create 索引，性能可以提升 3 倍左右。

• 业务流程建议：建议在代码集成阶段进行 SQL Review，防止这种谓词函数操作的 SQL 上线。