陆金所AISQLReview体系演进和实践

2020-01-14 10:02:37 阅读：9380+ 作者：责任编辑NO。姜敏0568 责任编辑：责任编辑NO。姜敏0568

作者丨薛梁

AI SQL 审核系统是陆金所于 2019 下半年重点开发的一个智能化数据库优化工具，已经为陆金所节约了大量用于 SQL 审核的 DBA 和开发资源。陆金所数据架构团队负责人王英杰老师在 2019 年 12 月北京·ArchSummit 全球架构师峰会上介绍了陆金所 AI SQL Review 系统演进话题，希望陆金所处理 SQL 审核工作经验给大家带来一些新的思路。

陆金所 AI SQL 审核系统的项目背景

做过开发和 DBA 的同学应该都深有感触，SQL 的性能对核心系统的可用率至关重要。因为一条烂 SQL 拖慢整个系统甚至让系统宕机的案例比比皆是。所以在应用发版前，建立起一套行之有效的人工 SQL 审核流程，把 SQL 性能问题发现在上线前，是不少公司的主流做法。陆金所一直以来都非常重视应用发版前 SQL 的审核工作，为此还研发了一整套人工 SQL 审核系统。

人工 SQL 审核系统确实帮我们在上线前发现了大量 SQL 的性能问题，并进行了提前优化，避免了很多生产事故的发生。但人工 SQL 审核平台审核部分主要还是靠人工，所以非常依赖开发和 DBA 的人力资源，这样一来，为了有效运行人工 SQL 审核系统，将遭遇到开发和 DBA 的人力资源瓶颈。为什么这么说呢？

这里看一下人工 SQL 审核的流程，首先是代码提交，人工 SQL 审核平台会直接和代码库进行对接；

接下来是差异比较，人工 SQL 审核平台会读取出当前生产版本和待上线版本之间 SQL 代码的变动和执行计划差异，并自动生成比对报告；

之后是最消耗开发和 DBA 人力资源的人工审核阶段，由开发和 DBA 阅读比对报告并给出评估意见。评估意见被 SQL 审核系统作为标签数据永久保存；

最后是上线批准，在一个版本里每一笔 SQL 都经过审核并优化消除掉性能风险后，才允许上线提交到生产。

所以回顾整个人工 SQL 审核流程，会发现人工 SQL 审核系统具备以下特点：

人工 SQL 审核系统会对所有应用、每个版本下的每笔 SQL 进行非常细粒度和端到端的管理；

同时呢，人工 SQL 系统会提前自动完成代码和执行计划的差异比对；

接下来，DBA 会审核每个版本里发生变更的 SQL、导致的执行计划差异以及对性能的影响；

最后为了让审核结果能落地，发版平台将建立起对人工 SQL 审核系统的强依赖关系。

这是陆金所研发的 SQL 审核平台功能界面，它会自动到代码库里去查找相同一个 SQL 标签在不同版本里的代码和执行计划变动情况，并生成报告给开发和 DBA 进行审核。开发和 DBA 可以点击通过并选择通过的意见，如果性能存在问题，也可以点击不通过并选择性能问题的分类，并输入优化意见。人工 SQL 审核平台会保存上面所有的评审数据。

这个平台自从 2016 年上线以来收集了陆金所每个版本下每笔 SQL 语句的代码、执行计划、评审和优化意见。所以基于这套人工审核平台我们收集了大量的标签数据。

能够正常的看到图中是人工 SQL 审核流程第 3 个环节的详细展开，这同时也是一个对 SQL 审核打标签并进行标签收集的过程。整个流程基于 SQL 审核平台完成的事前准备开始，通过审核的直接具备上线条件，不通过的审核的将由 DBA 进行性能问题分类，并根据性能问题点设计对应的优化方案交由开发进行优化。优化完成后再次再次审核，一直到通过上线。其中无论是审核、分类和优化环节既是在做 SQL 的评审和优化，同时也是人工打标签的过程。而这个打标签的过程将消耗大量的开发和 DBA 人力资源。

在整个系统高速迭代的过程中，如果要确保人工 SQL 审核流程落地，开发和 DBA 的人力资源将成为巨大的瓶颈。所以，当时我们就在思考，能否有更好的解决方案。

第二个部分是 AI SQL 审核系统的研发过程

基于此，研发团队开始了 AI SQL 审核系统的研发。

AI SQL 审核系统的研发条件有哪些呢？人工 SQL 审核平台上线三年后，积累下了大量真实的、和 SQL 审核相关的历史数据，包括 SQL 在每个应用版本的代码改动、生产环境执行计划变动、数据字典和统计信息，以及 DBA 的审核标签数据，和上线生产后的监控系统捕获的运行时效。所以可优先考虑一下，是否可以基于此尝试使用算法来模拟 DBA 对 SQL 的审核。

那么如何才能使用历史数据加 AI 算法来模拟 DBA 对 SQL 的审核呢，这个场景具体是要解决一个什么样的问题呢？这样的一个问题的核心是为了找到 SQL 代码、执行计划、统计信息、绑定变量和执行效率之间的相关性。并通过相关性基于 SQL 代码、执行计划、统计信息、绑定变量来预测执行效率。如果可以准确预测出 SQL 的执行效率，就能准确的评估出 SQL 是否具备上线条件。

上图展示了 AI SQL 审核系统实现对 SQL 执行效率预测的实现方案。如图所示，AI SQL 审核系统主要有三大部分所组成：输入为 SQL 审核平台以及其他自动化工具收集的 SQL 语句、执行计划、统计信息和绑定变量。目标是生产监控系统捕获的 SQL 的平均执行时间。我们大家都希望通过算法可以找到这两者之间的关系。

模型则是包含四个部分，分别是：1）特征嵌入，2) 特征压缩降维，3）特征裁剪，4）预测

能够正常的看到我们使用的预测模型是最为简单的线性预型，这么做的目的也是想让过拟合风险降到最低。下面对这四个个核心算法展开介绍。

特征嵌入的目标是对数据做处理，把 ai 模型不可读的 text 格式转化为 ai 模型可以计算的数字格式。这里对 SQL 语句做了词频逆文档频处理。tf-idf 是一种业界常用的方法，从词频来突出 SQL 包含了哪些表，哪些字段。对于执行计划里的非数字信息，比如表的连接，索引的扫描方式，嵌套查询的关联方式等，我们做了素数编码。素数编码可以完整保留每个元素的全部信息，同时又抹去了元素之间的为位置关系。降低了位置关系对模型训练的干扰，方便机器学习。经过处理后把 SQL 语句、执行计划、统计信息和绑定变量转化成一个非常宽的矩阵。这样就完成了对输入数据的前期处理。

为了能够更好的保证矩阵有解，并最大化降低过拟合风险。我们利用 VAEs 算法对稀疏的宽矩阵进行信息压缩降维，把上千个特征向量的矩阵压缩为 53 个特征向量的矩阵。在进行降维压缩的过程中，我们发现陆金所的 SQL 数据呈现明显的两极化，符合预期。

最后使用随机森林算法进行特征提取，从 53 个特征向量里保留 30% 信息增益最大的 38 个特征向量。从宏观 ROC 曲线能够正常的看到保留 30% 的特征覆盖的面积最大、鲁棒性最强，因此我们只保留前 30% 信息。我们将这种方法称为粗粒度学习。及把回归问题先划分成一个多分类问题，先粗力度的学习特征与目标的相关性。这里，将我们基于 DBA 经验将 SQL 的执行时间划分为 10 个区间，具体区间如下：

基于之前生成的 38 个特征，我们做了一个线性回归来对 SQL 执行效率预测。预测的方案如下：

首先我们选取了 300 条生产环境的 SQL 进行目标预测。

这 300 条 SQL 在监控系统中我们大家可以获取真实的执行时长，按照从低到高排序，绘制出橙色曲线。

使用 38 个特征配和线性回归算法，我们预测出了这 300 个 SQL 的执行时长。即在不执行 SQL 的情况下，依靠数据（数据包括 SQL 文本、执行计划、统计信息和绑定变量）和模型来预测 SQL 的执行时长。

预测结果见蓝色曲线，能够正常的看到预测结果存在一定的噪音和毛刺，但这些噪音可以接受的，因为总体趋势和生产环境的执行时长呈现高度一致性。因此基于这套模型，可以对 SQL 的执行效率进行一个大体上较为准确的预测。然后再基于预测结果，智能评估 SQL 是否具备上线条件。

最终将这个结果，作为 SQL 审核平台的第一道环节。如果 AI 审核通过即直接上线，人工不再审核。SQL 审核总量里 80% 的 SQL 都是符合性能预期的，这些 SQL 因为由 AI 直接审核后上线，所以也节省了 80% 的人工审核工作量。

如果对 SQL 的审核能够正常的使用 AI 来智能评估，那对于性能不好的 SQL，是否也能够最终靠算法来模拟开发和 DBA 对 SQL 进行调优。即把 SQL 审核流程中分类和优化这两个步骤，也通过模型来模拟。基于此，我们开始了第二版本的研发。

如果把 AI 的学习过程当做是个搜索问题，那么之前使用的方法需要对问题的解做完全遍历，如左图（频率派）。简而言之，频率派需要大量的数据来支持模型做完全遍历搜索找到最优解。对于 SQL 优化问题 SQL 对应的问题有很多种，如果让频率派学到一个最优解。那么每一种 SQL 问题都要对应一大批训练数据。其中的人力成本不可估计，这对我们来说是不切实际的。

因此我们选择了贝叶斯派 (右图)。贝叶斯派，基于先验可以对搜索空间做缩减，这样一能提高训练速度，二也解决了数据不足的问题。

这个版本只是一个“建议系统”，及对 SQL 提出可修改的建议。不同于 AI 预测 SQL 执行性能，对 SQL 给出优化建议，需要模型了解 SQL 的结构关系，比如多表之间的连接关系，单表字段与索引之间的关系等等。同时 SQL 性能优化系统对一个 SQL 往往会生成多个优化建议。对于大部分 n 到 1（n 个输入一个输出）的机器学习算法，并不能很好的解决问题。

这里我们提出了一种机器学习 + 搜索结合的方法，即基于贝叶斯的启发式搜索方法。启发式搜索分为三个部分：

首先还是预处理，为了能让模型理解 SQL 的结构，我们专门开发 LUParser SQL 解析器，将 SQL 语句解析成一个有向图（后面会具体介绍）作为搜索的信息之一。这一点和预测 SQL 执行时长时对 SQL 代码处理的词频逆文档频不一样。除了结构信息以外，执行计划，统计信息，绑定变量我们都一并保留，并作特征嵌入，嵌入方法与之前一样这里就不再介绍。

启发式搜索的核心是启发函数，我们基于输入数据计算出，输入与搜索节点之前的条件概率。

最后基于启发值的大小搜索出一套 SQL 优化建议。

这里主要介绍一下技术核心陆金所 SQL 解析器 LUParser：

LUParser 是在 AST 树基础上，进一步将列，表，视图等的关系解析成一个有向图。这种方式可以帮助机器更好的理解 SQL 里的关联关系，方便对 SQL 做系统优化。有向图的一个好处是能够保留比树更多的信息，能够最终靠有向图基于不同的需求，解析成不同的树。这里展示一个复杂的多层半连接嵌套 SQL 语句解析结果。能够正常的看到有向图可以非常清晰的把包含三层嵌套子查询的 SQL 全部信息都展示出来。

下面是 LUPareser 的计算过程，主要有三部组成：

首先正则化分词，将 SQL 语句转换成精度不高的 AST 树。这里使用基于关键字 + 正则化做了个粗粒度的转换。

之后会做深度优先完全遍历，主要目的是将 AST 树中的 token 封装成图的节点。

建立图链接。图链接是按照子查询到表到字段的顺序依次建立的。整一个完整的过程也只需要一次遍历。

整个算法下来我们应该对 SQL 做三次遍历即可解析完成。

介绍完特征处理，下面会着重介绍一下启发式搜索：

首先要构建一个搜索地图：

1.1 结合历史 DBA 评审话术和 DBA 经验，划分出一个状态库（搜索地图）。状态大致上可以分为索引优化，表连接优化，分页排序优化和子查询优化等。同时我们的状态还在不断的补充完善中。

1.2 这些状态都是一个个相互独立的方法。一个状态可以有多种输出，但必须输出一个数值，用于计算启发值。

构建基于贝叶斯的启发函数

2.1 启发函数用于构建状态与状态之间的转换。

2.2 启发函数有两个输入，一当前状态的位置（S），二 SQL 信息（X）。启发函数输出为每个候选状态的分数。分数越高则被选择的概率越大。

搜索终止条件：

只允许同一个状态至多能被使用一次。如果搜索发现没有可选状态或者候选状态为空时搜索终止。

下面介绍一下启发函数的训练方法，大致上可以分为两个部分：

基于历史评审意见，生成地图并计算两个状态之间的条件概率：

a. 我们首先对建议做分词，滤重等预处理。

b. 将处理好的词，通过同义词转换，抓取关键字等等，映射成搜索地图。

c. 在给定 SQL 特征下（X），统计状态与状态之间的联合概率和条件概率。

d. 通过链式法则生成启发式函数。

在计算条件概率时会对部分状态之间的联合分布做限定：比如，索引问题更倾向于查找区分度和直方图。

上线前对接代码库和发版平台，对审核不通过的 SQL，先给出一轮优化建议供开发和 DBA 参考。上线后对接监控平台，监控平台捕获慢查询后，调用 AI SQL 审核服务接口，自动给出优化建议。同时 AI 明确优化建议优于 CBO 的执行计划才会给出建议反馈。

我们的建议模型基于贝叶斯派模，虽然模型输出可控，但也会受限于 DBA 的经验。于是我们思考，能否开发一种激励算法，来鼓励模型去尝试对 SQL 做不同的优化。为此我们构思出了一种基于强化学习 + 对抗模型的 SQL 改写优化模型，希望模型通过不断的尝试改写 SQL 来学得如何去优化一条查询语句。该想法目前还处于研发阶段。

整个算法由三个神经网络模型组成。分别为：SQL 生成器，SQL 优化器，SQL 恶化器。

SQL 生成器：

1.1 目的：在 AI 能够改写 SQL 之前，我们大家都希望 AI 可以准确的写 SQL。所以我们先训练了一个 SQL 生成器。

1.2 训练方法：SQL 生成器的输入由 LUParser 提供，主要有两个信息，1) SQL 的结构 2) 链接关系。目前我们已成功训练出 SQL 生成器。

对抗模型：

2.1 继承 SQL 生成器：将训练好的 SQL 生成器直接“迁移”给 SQL 优化器，和 SQL 恶化器，省去他们学习如何写 SQL 的时间。

2.2 对抗体制：优化器不断地优化恶化器提供的差 SQL。反之，恶化器不断地恶化优化器提供的好 SQL。这里需要强化学习的介入，来保证优化器和恶化器处理后的 SQL 和之前 SQL 的结果一致。

强化学习：

3.1 目的：为了能够更好的保证优化器和恶化器处理后 SQL 的结果一致，我们引入了强化学习。

3.2 方法：以优化器为例，当优化器收到恶化器传递给的 SQL 后，优化器通过强化学习，会不断地尝试对该 SQL 做改写。直到生成满足条件的 SQL 才会传给恶化器。

对抗模型优点：

理论上如果单使用强化学习，模型是可以学到如何优化 SQL 的。但是这里存在一个弊端，只用强化学习，会让模型接触的数据范围过窄，训练出来的模型泛化性过低。模型很容易学到只对某个 SQL 做优化的方法。