TiDB 数据库最强 SQL 审核工具，求挑战

2024-12-06
北京
本文字数：3176 字
阅读完需：约 10 分钟

作者：数据库性能诊断 DBdoctor 原文来源：https://tidb.net/blog/9a348677

业界 SQL 审核现状

SQL 审核是指对未上线的 SQL 进行检测，提前识别是否符合规范，以保证上线质量。Yearning、Archery 、Bytebase、goInception、SQLE 是当前国内主流的开源 SQL 审核工具，均是基于规则来进行 SQL 审核，可以检查规范性，但无法基于线上数据情况进行 SQL 性能评估，所以审核后的 SQL 上线后依然会存在性能隐患。

基于规则是否能做 SQL 性能审核？

每个公司都有自己的 SQL 规范标准，即不同的规则。规则越多，说明管理越严格。如以下常见的 SQL 审核规则：

1）建表需要有自增主键2）禁止使用触发器3）禁止使用存储过程4）禁止使用索引套函数5）查询条件中使用in（...）关键字元素个数不要超过5000

复制代码

通过这些规则可以将 SQL 语法层面的问题提前识别出来。大家也许有疑问，基于 DBA 经验沉淀的审核规则是否可靠？以上面第五条规则为例，以 5000 为个数限制该规则本身就有问题，实际还是从 SQL 涉及的表行记录长度、数据量、离散层度、IO 页扫描次数等多因素综合考虑，不应该是一个具体经验值。

SQL 规则审核能否覆盖性能的审核？我们手写一条规则来验证 SQL 是否有性能问题：

#待审核的SQL如下，pur_jit_item表有自增主键id，隔离级别RR，没有其他索引update pur_jit_item set qualified_qty=5316 where fatory_code=7 and mat_sap_code=3

复制代码

当字段少的时候，我们从上图中可以看到 SQL 规则审核命中的字段未加索引是存在有性能问题的，但如果字段过多、部分字段存在索引、字段区分度不好等场景时，规则该如何写？很多有经验的 DBA，试图通过列举所有场景的规则来实现性能识别是不可取的。从实际生产来看，要考虑的因素太多，同样的 SQL 在不同的数据集上的结果都可能有 N 种，无法准确命中。

综上所述，我们得出结论，以传统经验规则的方式做 SQL 性能审核并不可取，亟须一种低消耗快速的方式。

SQL 性能审核灵感

对数据库内核熟悉的应该了解过 MySQL、PgSQL 的 SQL 最优执行路径，通过 Cost-based Optimization 进行评估，然后进行最优路径选择。我们来看下 MySQL Cost 优化器评估过程。

1）建立评估标准

MySQL 系统表内置默认的代价计算标准，分为 Server 层 CPU 代价和存储引擎层 IO 代价，比如物理临时表 disk_temptable_create_cost 默认一次代价为 20。通过这种详细标准定义就可以将 SQL 在每个操作上的代价量化出来，得出具体的 Cost 值，进而为后续的路径选择提供数据支撑。

2）SQL 最优路径选择

Session 开启 OPTIMIZER_TRACE，SQL 执行后，Trace 日志中会记录该 SQL 详细的各条索引路径 Cost 消耗的数值，最终会从所有的路径中选择 Cost 消耗最低的路径作为 best access path（命中的索引）。

综上所述，实现旁路 Cost 优化器需要解决两个问题，首先仿照 MySQL 定义一套一样的 Cost 评估标准，然后梳理出每条索引路径的 Cost 依赖哪些统计信息并获取。

数据库 Cost 优化器源码解读

数据库最终的路径选择是基于 Cost 标准计算出来的，外置 Cost 计算标准和公式即可旁路来计算 Cost 消耗。下来我们来从 MySQL Access Type 维度一起拆解 Cost 优化器的内核计算过程。

1）源码解析 Cost 计算公式

Cost 的计算公式可以直接从代码中解读出来，比如以 Table scan 全表扫描为例，Table scan 的 Cost 分为 IO Cost 跟 CPU Cost 两个部分之和，大致的公式为：

IO-Cost:#Pages_In_Table * IO_BLOCK_READ_COST + CPU-Cost:#Records * ROW_EVALUATE_COST

复制代码

其中 IO-Cost 是通过 Table_Scan_Cost 来进行计算。这里有两个关键的变量 Records 跟 Pages_In_Table，分别表示这个表的行记录数和页数，在 INNODB 中这两个变量的值可通过 ha_innobase::info_low(ha_innobase::info) 和 ha_innobase::scan_time() 来进行获取，知道了这两个变量的值，就知道具体的 Cost 值。

2）如何获取 Cost 公式中的具体变量值

针对公式中的变量，这里以已存在的索引为例，MySQL InnoDB 通过分析随机采样的索引叶子页来估算索引的统计信息（采样 20 个页，该统计信息可以从系统表中直接获取），通过采样来估算并进行索引路径的选择。这里旁路实现的话可以完全参照 MySQL 已有的逻辑进行变量值提取。

3）对所有路径进行该 SQL 的 Cost 计算

通过计算所有路径的 Cost，我们能得出最终 Cost 消耗最小的路径即最优路径。

综上所述，我们发现外置 Cost 优化器是可行的，外置采集该 SQL 表的统计信息，然后仿照 MySQL 原有的分析评估行为，即可得出该 SQL 在生产环境实际的 Cost 值。但这里存在一个问题，针对不存在的索引如何获取统计信息呢？

基于 eBPF 的旁路 Cost 优化器原理

要实现对生产库的 SQL 性能评估，需要找出这条 SQL 的所有可能的索引执行路径。基于系统表对已存在的索引统计信息获取比较简单，但针对生产库上不存在的索引（避免在源库上直接进行 DDL 等风险较高的重操作）无法直接获取到统计信息，需要基于该 SQL 表在线上实际数据进行统计信息提取。这里我们可以考虑通过 eBPF 技术去获取关键数据指标。

在数据倾斜的场景中，每条 SQL 的 where 条件字段的范围值不一样，索引执行路径选择的真实 Cost 会不一样，获取采样页尤为重要。下面详细介绍 SQL 性能评估的流程：

1）SQL Parse 解析 SQL 表的条件字段、查询字段。

2）找出涉及减少 Cost 消耗的所有路径。针对第一步得到的字段，然后进行排列组合，建立出 A(n,m) 个候选索引路径。

3）模拟 MySQL 的页采样逻辑，进行详细页分析并进行统计信息提取，还原 MySQL 生产库上的真实采样统计信息。针对上面 Case 中的数据倾斜等场景，就可以避免统计信息不准导致生产执行路径和推荐路径不一致的情况。

4）利用贪心算法可计算出每个候选索引的 Cost，最后得出 Cost 代价最小的即为当前的最优索引的执行路径。

5）若该最优索引与之前原有路径 Cost 消耗相差较大，则说明 SQL 存在性能问题。

综上所述，单条 SQL 是否存在性能问题可以工程化实现，但在实际生产中还需要关注到表维度的全局最优，需要建更少的索引来满足各业务 SQL 的性能吞吐，减少维护索引导致的开销。

表维度全局最优如何实现？

单条 SQL 的性能评估可以按照上面的方式实现，但在实际生产中由于索引本身的维护是需要消耗资源的，因此索引不是越多越好，需要建立全局最优的索引，按照 DBA 的经验可采取“去冗余”和“去未使用索引”两种方式来进行优化，但在实际中往往由于担心对业务有性能影响而不敢进行索引的操作，只做加法不做减法。下面将详细展开如何从表维度求全局最优解：

1）SQLParse 解析 SQL 对应的库表、字段，并进行最优索引的推荐。

2）结合全量的审计日志分析，统计数据库实例涉及该表的所有的指纹 SQL。

3）分析所有指纹 SQL 并提取公共高频字段、指纹 SQL 特有字段等进行分类。

4）按照字段分类进行索引的排列组合，对每条指纹 SQL 进行 Cost 计算并与原 Cost 进行对比，在不衰减性能的情况下，得出多条最优路径。

5）基于上一步的多条最优路径，得出全局 Cost 最优索引路径。

6）若该索引与之前原有路径 Cost 消耗相差较大，则说明 SQL 存在性能问题。

7）该推荐的索引路径为最佳方案。

综上所述，具有基于外置 Cost 优化器的全局最优索引推荐方案是可行的，基于该方案在 SQL 上线前即可实现 SQL 性能审核，并评估出基于生产数据模型的全局最优索引推荐。

DBdoctor，SQL 性能审核限时免费体验

当前，DBdoctor 工具已具备该全局最优推荐，上线前识别 SQL 性能问题，提前规避故障，DBdoctor 通过自研外置 Cost 优化器，创新性实现了覆盖性能诊断的 SQL 审核，能够基于线上数据模型，在发布上线前快速、精准评估 SQL 性能问题，并给出优化建议，提前规避线上故障的发生。赶紧免费下载体验吧，可在公众号自助申请专业版 License！