1.

2. SQL性能自动化测试实践 梁伟项

3. 目录 n n n n n n n 背景介绍 方案整体介绍 SQL采集模块 SQL解析及参数替换模块 SQL性能分析模块 运营策略 效果及待优化 唯品会

4. n背景介绍 唯品会

5. 一条慢SQL引发的惨案 现象：2019年4月25日，商品系统某核心应用新版本上线后不到半小时出现熔断 影响：20分钟内商家管理后台无法正常使用 原因分析：慢查询导致获取DB链接失败，服务熔断 涉事SQL：select global_sku_id, xxx... from global_sku_index where del_flag=0 and barcode = '190436050899'; • 表global_sku_index当时的索引情况： • • • • • 修复方案：给barcode字段添加单独索引 • 测试同学的解释：SQL对应的接口中有owner参数，我以为开发一定会传owner+barcode来查， 谁想到这个版本没传owner参数 唯品会

6. 测试痛点 u线上慢SQL问题特点 Ø影响大，且容易引起系统雪崩 Ø线下测试难发现 u线下测试难发现的原因 Ø部分测试人员没有意识去关注 Ø测试时间成本高，比如每次从代码提交记录中找出所有 新增和修改的sql语句就得花不少时间 Ø在测试环境做SQL性能分析的结果参考意义不大 Ø部分测试人员没有足够的专业能力做SQL性能分析 这么明显的慢 sql 你 测试时候都没发 现？让猴子测都能 发现！ 那你还是请 个猴子来 吧！ 唯品会

7. n整体方案介绍 唯品会

8. 概要介绍 • 本案通过向测试环境被测应用注入代码的方式收集SQL后，利用线上数据做参数替换，最后把SQL 提交到线上的SQL性能分析引擎分析，得到具有较高参考意义的分析结果和优化建议。 • 作用范围：mysql + java + mybatis系列，主要针对线下测试环境新增或者修改SQL语句。 序号 方案特点 序号 分析报告内容 1 应用接入简单，被测应用及测试人员无感知 1 2 通过sql特征提取算法，去重效果良好，可有效防止SQL重复 分析 当前执行计划中反馈什么风险，如做了全表扫描，使用了临时表 等 2 是否存在更优的索引方式，如建议新建或者修改某个索引，或者 强行走某个索引有更快的查询效率 3 推荐的索引方案会带来多少效益 3 4 全自动化采集和分析，风险自动邮件通知对应开发、测试负 责人 分析结果有较高参考意义 唯品会

9. 效果展示 唯品会

10. 系统架构图 唯品会

11. 业务流程图 唯品会

12. 业界开源方案对比 以上对比仅仅代表个人的认知，如有不正确还请指正。 方案 简介 是否开源 本案 唯品自研用于在测试阶段分析SQL性能 的系统,唯品会有另外一套自动分析线上 SQL性能的系统； 否 索引方案分析技术 优点 不足 基于CBO+RBO， 1、基于CBO算法为主，RBO 1、数据采样耗时长，使得 优先参考CBO结 为辅的算法，实时做线上数 分析效率偏低，目前分析一 论； 据采集，充分考虑线上数据 个线下新增或者修改SQL耗 分布对索引选择的影响，结 时30~60S；2、目前只支持 果更具参考意义；2、包括了 select语句。 线下SQL自动采集部分，使 得测试阶段做SQL性能分析 实现自动化；3、没有语法限 制 SQLAdvisor SQLAdvisor是由美团点评公司技术工 程部DBA团队（北京）开发维护的一个 分析SQL给出索引优化建议的工具，不 区分线上线下 是 RBO 执行效率高 1、基于RBO分支，忽略数 据分布对索引选择的影响； 2、有语法限制；如SQL中 的子查询、or条件、使用函 数的条件 会忽略不处理。 3、没有包括SQL采集部分 SOAR SOAR(SQL Optimizer And Rewriter) 是一个对 SQL 进行优化和改写的自动化 工具。 由小米人工智能与云平台的数据 库团队开发与维护。 是 RBO 执行效率高 功能丰富，除了索引分析， 还有SQL指纹，改写等。 1、基于RBO分析，忽略数 据分布对索引选择的影响； 2、没有包括SQL采集部分 唯品会 • CBO： 基于成本的优 化器 • RBO： 基于规则（启 发式）的优化 器

13. nSQL采集模块 唯品会

14. SQL采集 • 采集模块构成 ØSQL拦截Agent ①功能：通过代码注入，收集做sql分析需要信息并通过接口投递到收集模块的队列。 ②原理：基于jvm-sandbox，封装mybatis系列持久层框架的拦截代码，切点为mybatis框架的参数替换方法出口，重点在于适配不同的数据库连接框架，比如 c3p0,druid等。 ③收集内容：sql 指纹，真实的sql语句，被测容器环境信息，代码分支信息，数据库名称等。 Ø环境监控模块 ① 功能：高频的循环监控配置内的测试环境有没有注入Agent ② 原理：通过被测环境有无Agent包及sandbox日志的关键信息，判断是否需要启动注入命令 ØSQL收集模块 ①功能：暴露一个接口，介绍Agent传送过来的sql信息投递到队列，让去重模块去去重处理或者入库 ②原理：主要是通过提取SQL关键特征做缓存的方式做去重 •SQL关键特征内容 SQL归属的库名，SQL语句涉及的所有表名、查询字段，调用的函数名称，关系表达式，查询表达式中的字段名+操作符，以及分组， 排序，分页等信息。 唯品会

15. 去重算法 唯品会

16. nSQL解析及参数替换模块 唯品会

17. SQL解析及参数替换模块 • SQL解析 ①功能：构建SQL抽象语法树，把SQL的不同部分映射为不同类，便于访问和统计。 ②技术方案：目前了解到的java有三种sql解析工具：jsqlparser，calcite，druid；从试用情况来看， jsqlparser， calcite 都存在某个特殊SQL语句解析报异常的情况，而druid对sql类型的支持会好一些。 • 参数替换 ①功能：把测试环境的SQL参数替换为线上的参数，目的是利用线上数据库进行sql性能分析。 ②几个关键点 a. 外传参数的类型要传递正确，这个可以从测试环境的真实sql中获取。 b. 时间范围的参数可以直接用测试环境的真实SQL的参数直接替换。 c. 同一个字段的区间查询的，要注意大小顺序，比如对于id> a AND id < b 这种区间查询，要保证替换后a比b小，否则SQL无意义。 d. 对于无字段对应的外传参数，比如一个函数的参数是需要外传的，这个可以直接用测试环境的真实SQL的参数直接替换。 e. IN条件中参数个数大于1时，要注意替换后参数的唯一性，比如不要替换成in （1,1,1）这种。 f. 其他外传参数都需要通过线上的参数采样接口按正确的名称和数量去采样后替换为线上的。 唯品会

18. nSQL性能分析模块 唯品会

19. SQL性能分析模块---执行计划风险 • 目的 判断当前SQL在生产上的执行计划是否有风险 • 主要关注指标 Ø执行响应时间 Ø执行计划中的type字段值，判断是否存在全表扫描或者全索引扫描 Ø执行计划中的Extra字段值，判断是否有使用临时表，join buffer，文件排序等。 唯品会

20. SQL性能分析模块---更优索引方案分析 • 目标：推算是否有更优的索引方案 • 方案：CBO + RBO协同分析 Ø CBO算法实现 性能提升倍数最大的计算方式 假设该 SQL 在生产库上的执行计划中选择的索引 为 A ，当前索引为 B ： 在分析库中选择 A 索引的执行 cost / 在分析库中 选择 B 索引的执行 cost Ø RBO分析逻辑： 根据经验写入一些固定优化规则，如order by的时候，如果cost一样，会自动加入by后的列（如果是主键会忽略） 唯品会

21. n运营策略 唯品会

22. 运营策略 唯品会

23. n方案效果及待优化 唯品会

24. 效果及待优化 • 效果 • • • • • • 运营时长：3个月+ 接入微服务112 共收集SQL 37000+ 每天收集到新增或者修改sql最多时候112,最少时候为0； 发现风险SQL 900+ 有效Bug数为11 • 待优化 • 参数替换后sql没法保证百分百的符合业务逻辑 • SQL索引分析引擎数据抽样耗时较长，经常需要多次抽样才能保证抽样后数据的执行计划跟生产一致 • 资源限制，目前SQL分析引擎只能串行执行 唯品会

25.