浅谈SQL诊断的技术要点

背景

  众所周知，数据库系统是一个非常复杂的软件系统，它给使用者服务提供简单接口的同时，开放了其生命周期中各个环节的可配置性，以定制化地应对纷繁复杂的业务场景。这些环节包括了数据备份、数据安全、性能、可用性、升级更新、变更管理等，它们和业务项目支持、数据逻辑设计和终端体验优化一起组成了数据库管理人员的所有工作日常。

  自从2017下半年数据库业界提出“自动驾驶数据库”（Autonomous Database）这一概念以来，包含华为云在内的全球各大云数据库厂商都开始了云数据库在“自动优化”这一话题上的探索，这期间形成了有关“自动驾驶数据库”的各个方向要素的基础理论，并在众多环节落地产生了成果，华为云RDS的数据库SQL诊断功能就是其中之一。

    SQL诊断功能将赋能开发、测试人员守护自己的SQL质量，并将DBA从日常优化工作中解放出来，推动数据库开发流程的转变。下面从产品使用和实现原理两个方面，详细为大家介绍SQL诊断在华为云DAS的最佳实践。

SQL诊断

 1 功能入口

  数据库管理服务（DAS）的SQL窗口支持多语句拆分、格式化、执行计划功能，在它们的基础上我们加上了优化功能，如图1所示。

   通过即将推出的“SQL诊断”按钮，我们能对输入到SQL窗口中的语句进行诊断，得到诊断建议，如图2所示。

  对于较复杂的语句，DAS需要较长时间（超过3秒）才能完成诊断任务，会提示用户在后台任务列表页等待诊断结果。

  2 诊断引擎实现细节

  讨论实现细节前，我们先整体过一遍SQL语句诊断的业务流程。

   首先用户输入一条或多条SQL语句在DAS前台发起诊断请求，服务器收到待诊断语句后创建诊断任务，进行语句解析，获得任务涉及的库、表、字段，在用户DB上获取响应的Schema信息后，开始有针对性的进行库、表的数据采样，基于我们的语句规则库对语句生成语句建议，基于获取到的Schema信息生成候选索引，根据候选索引生成执行计划并计算执行代价，选择执行代价最低的执行计划生成诊断结果。

  上述标粗关键字即为SQL诊断服务的关键步骤，下面我们举个例子，来解释它们每一步都具体做了什么操作。

  2.1 语句解析

  我们以上文图中的SQL为例，这是一个从test_person表中获取所有phone为13999213411并且gmt_create小于2005-01-01 00:00:00的人员列表的简单查询语句。开发人员和DBA很清楚这条SQL的含义，但是需要解析才能让机器明白它的结构化信息：

  2.2 Schema信息

  从语句解析中得到待诊断语句设计的库名、表名，随后我们就需要拿到关于它们的Schema结构信息，在MySQL中我们直接通过系统表information_schema.tables/information_schema. columns/information_schema.statistics获取。

  2.3 数据采样

 我们的数据采样算法来源于《Random sampling for histogram construction: how much is enough?》，随机采样的数量可以用如下公式来确定：

   其中，R是采样行数，是差别率（即1-为可信度），是表记录数，是直方图大小。用于衡量采样直方图与真实表记录数据的误差因子。由于log函数的存在，R对N的依赖较弱，这样对大数据量表的采样比较友好。

我们对公式中的可调节参数有一套反馈机制，可以做到依据代价计算的准确性动态调整。此外，工程上的采样操作比较耗时，我们引入了主动采样来提高采样在整个诊断过程中的效率。

  2.4 生成语句建议

  目前我们的规则库涵盖了华为云数据库专家对安全、HINT、排序、翻页、数据类型、字符集等在编写SQL语句时的各个方面的最佳实践，以下图中的SQL语句为例，通过语句解析，发现该语句结构命中了三条规则，我们为它生成三条语句建议。

  2.5 生成候选索引

   在语句解析后，我们可以获取用户输入SQL语句的所有QueryStatement，通常一条复杂的SQL语句包含多个QueryStatement。每个QueryStatement的Where条件谓词、Group By、Order By等涉及字段都将用于索引生成算法。

  此过程中我们会做一些细节处理，例如：不考虑主键字段，因为在MySQL  InnoDB引擎中非主键索引和主键已经有映射关系；操作符为OR时，不考虑两侧谓词在索引合并（Index Merge）情况下的候选索引；在已有前文所述的采样数据时，过滤性低于一定阈值的字段将被直接舍弃，不参与候选索引的生成，等等。

  2.6 计算执行代价

   代价计算是整个索引推荐流程的重点，我们采用了与MySQL内核相似的算法，来计算根据所有候选索引生成执行路径的执行代价。依据每一条原有索引和新生成候选索引的结构，得到谓词的先后比较顺序，然后在采样数据的基础上，估算谓词的过滤性及比较次数，进而估算其耗时。

此过程中，因为SQL查询中不必要的“回表”会导致额外的执行成本，我们着重区分了覆盖索引与常规索引的代价计算逻辑。

另外，索引并不是越多越好，尤其是多个字段的联合索引，会带来不小的额外磁盘占用，因此在代价公式中我们还以一定权重加入了磁盘占用成本。

  2.7 生成索引建议

  通过上一步的计算，我们根据带来最小执行代价的候选索引生成DDL语句，形成一个有效的索引建议。

 总结

   本文对华为云数据管理服务（DAS）的SQL语句诊断功能做了简单介绍，并简要讲述了SQL诊断的关键步骤和部分细节，希望大家能对SQL诊断的整体原理有初步认识。

   DAS的SQL诊断功能预计在2019年一季度面向用户推出，欢迎关注，与提供宝贵意见~

   此外，后续我们还将发布一系列技术文章，进一步讨论SQL诊断技术，敬请期待、品鉴。