sql 排列组合_【唯实践】线下SQL性能自动化分析实践

Part 01

场景还原

去年9月，某个业务线的核心应用在新代码上线30分钟后服务熔断，不断告警，服务无法使用。从表象来看是新代码中SQL语句产生慢查询，导致大量业务线程获取连接失败。测试连同开发对涉案表进行了一轮分析，得出结论如下：

涉案表索引定义：表中有定义一个由owner+barcode+vendor_id组成的联合索引；

涉事SQL：select global_sku_id, xxx... from global_sku_index where del_flag=0 and barcode = '190436050899';

根因分析：涉事SQL对应的接口定义中是有owner，barcode和vendor_id这三个入参，但代码逻辑只传了barcode一个作为查询条件，而又没有单独的barcode索引或者barcode开头的联合索引，所以最终没有命中索引。

漏测原因复盘：测试同学只关注了功能的正确与否，并没有关注SQL的查询效率。

Part 02

案例背后

长期以来,数据库慢查询问题，基本都是等到代码发布上线造成了慢查询才被识别出来。从产品质量建设和保障角度来说这已经后知后觉。尽管如此，慢查询问题在测试阶段的验证工作却一直得不到有效的改善，主要原因有几点：

★测试人员没有意识去关注SQL语句的性能。

★测试阶段做SQL性能分析的时间成本高，且手段有限。

PS：测试阶段做SQL语句性能分析的过程包括：在测试分支中找出所有新增或者修改的SQL语句---从线上找到合适的替换参数---手工完成参数替换后把SQL语句拿到线上的数据库执行一下看响应时间或者explain一下看执行计划的情况；

★做SQL语句的性能分析需要有足够的专业知识和经验。

针对这种现状，今年下半年唯品会运营中台测试团队和DBA团队合作开发了线下SQL语句自动化分析工具，目标是让每一条新增或者修改的SQL语句在上线前先拿到线上去做一次执行计划风险评估和更优索引分析，尽量避免有风险的SQL语句遗留上线。接下来将为你介绍这个工具的主要功能和技术实现。

Part 03

工具介绍

一、架构介绍

整个工具的实现分为三部分：线下SQL采集agent；SQL分析管理系统；SQL性能分析引擎。具体见下方架构图，接下来的流程解析也会对架构图中的每个模块做介绍。

二、工作流程及实现解析

工具主要包含4个流程：SQL采集、SQL解析、SQL参数替换、SQL性能分析，接下来将逐个进行简短介绍。

SQL采集及实现

SQL采集agent实时采集测试过程中触发的SQL语句，并调SQL分析管理系统的接口把SQL语句投递到一个消息队列。

工具中SQL采集由采集agent负责，它是基于阿里开源的jvm-sandbox基础上封装的jar包，是在mybatis 框架的参数替换方法出口处做一个切片，主要工作是收集一些关键信息，如SQL指纹、替换参数后的SQL语句以及数据库的URL和名称信息，另外也会收集一些测试环境信息，如容器id，代码分支信息。采集到SQL后就通过http接口投递到SQL分析管理平台。

SQL解析及实现

SQL分析管理系统从消息队列中拉取SQL语句做去重处理，只把新增或者修改的SQL语句保存到DB待分析。

SQL语句解析及语法树构建是整个工具的基础依赖模块。SQL去重，参数替换，SQL性能分析等功能都是需要先完成SQL解析才能进行下一步操作。目的是从SQL语句中解析出SQL每一部分：操作类型(select，update，delete， insert)，查询字段，涉及到的表名称，关系表达式，查询表达式，函数调用，分组分页，排序等。

在开发过程中我们有使用过jsqlparse， calcite，druid这三种开源工具，但最后是选择阿里的druid,因为jsqlparse、calcite这两个在处理某些包含特定关键词的SQL时抛异常。

SQL参数替换及实现

SQL分析管理系统的定时任务定时从DB捞出未分析的SQL语句，先做线上参数替换，然后提交到SQL性能分析引擎去做性能分析。

要让SQL分析结果有参考价值，就应该把SQL语言拿到线上数据库上去进行分析。所以参数替换的目的就是把SQL中的参数替换为线上数据，然后再提交到线上去做分析。工具中做参数替换的概要流程：

★首先分别解析SQL指纹以及它对应的测试环境完成了参数替换的SQL，识别出SQL语句中的所有表名称和子表达式；

★通过SQL分析引擎的接口，根据库名和表名称采集100行线上数据待用；

★遍历每个子表达式中是否包含待替换参数，如果有需要替换的，则判断它是否能够对应上表中的某个字段，如果可以对应上的，就用步骤2中采集到的线上数据中对应字段的值，按正确类型做替换；如果对应不上的直接用测试环境中对应的替换值来替换。具体流程如下：

SQL性能分析及实现

SQL分析管理系统获取性能分析结果后经由结果做判断，将有风险的SQL语句通过邮件汇报给对应域的测试负责人。

SQL性能分析引擎运行于生产环境，主要有两大功能：一是通过接口提供线上数据采样功能；另一个是执行更优索引方案分析。这里着重介绍更优索引方案分析部分。我们做更优索引方案分析是基于CBO为主、RBO为辅的方式进行。分析流程如下：

★收到SQL语句先进行SQL解析，识别所有的表，根据内部维护的映射关系找到表对应的库和库版本信息，创建对应的分析环境；

★从线上备库按规则采样3万行数据填充到测试库；

★解析出SQL中的所有查询条件；

★对所有查询条件做排列组合，依次建立对应的索引，并让SQL走指定的索引执行一次，得到所有索引的执行耗时；

★挑出执行耗时最小的作为最优索引做推荐，并给出相对于走默认索引提升的倍数；

★当有多个相同耗时的索引方案时，根据DBA经验写的规则挑选出最优索引方案；

★最后把该SQL的默认执行计划内容及索引方案分析结果返回给SQL分析平台。

Part 04

工具输出结果

工具最终输出的是被认为有风险的SQL信息。一个SQL有没有风险目前是从两个维度去衡量：

★SQL在生产的默认执行计划中有没有风险。比如explain结果的type字段出现了ALL，index等，或者Extra自动出现了使用临时表等。

★经过SQL分析引擎分析后认为有更优索引方案，且性能提升10倍以上。

以上两个维度只要出现一个，就认为该SQL有风险，值得关注，会被通过邮件告知对应的测试负责人确认。

当然风险告警邮件中除了上面的风险项，还包含其他内容：域名，测试空间ID，代码分支，数据库名称，SQL指纹信息，完成参数替换的SQL信息，SQL在生产库的执行计划等。

☝告警邮件示例

Part 05

适用范围及接入

目前采集agent只封装了mybatis系列的切片，且SQL分析引擎只支持mysql数据库，所以目前只支持java+mybatis+mysql的应用接入。接入方式很简单，只需要把要接入的域名，测试环境空间ID，和对应的测试负责人信息通过页面配置进来就可以，日常测试无感知。

Part 06

待优化方向

工具在公司内部中台已经运营了几个月，也陆续发现了一些被确认的SQL问题，但仍然存在一些待改善之处：

★参数替换逻辑没有能够保证替换参数后的SQL 100%符合业务场景，例如某个字段在采集回来的100行数据中全部是同一个值，导致替换in表达式里的条件变少或不符合实际意义。

★SQL分析引擎目前只能串行执行，且每条SQL分析耗时在30~60S，后续考虑做并行处理。

后续有更多实践收获再来与大家细聊。

“唯技术”一档专为唯品技术人发声的公众号

欢迎投稿！！

只要是技术相关的文章尽管砸过来！