造了一个小轮子, 解决 SQL 审核问题

如何优雅的解决 SQL review 的问题？一个低效率的 SQL 上线一定伴随着回滚 + hotfix， 那么我们如何从流程上确保每个新上线的 SQL 都被 review 过，确保没有问题？ 

我们来梳理一下一个新的 SQL 的 review 流程：

1. 开发人员整理本次 feature 添加或者修改的 SQL， 提交给 DBA；

2. DBA 通过自己的专业知识，配合 EXPLAIN 结果，评估 SQL 是否正确的使用了索引， 如果没有，则通过修改索引，甚至建议开发人员修改数据存储的方式，确保数据库不会因为一个低效 SQL 搞垮。

问题来了，如何确保这个手动的流程每次都准确无误的进行？ 

• 开发小哥会不会忘记通知 DBA 同学有新 SQL 上线了？

• 开发小哥会不会漏掉了其中的一些 SQL？

嗯，作为工具小能手，是时候造个小工具来解决这个老大难问题了。

首先，我们来看一下一个优秀的互联网公司技术团队，必备的几个前提：

1. 所有代码写单元测试，核心逻辑必须有单元测试保障；

2. 代码提交后有持续集成，自动执行单元测试；

3. 所有代码必须经过 code review 才允许 merge 到 master。

那么，如果有一个工具，

1. 自动在跑完 CI 后捕捉所有的 SQL；

2. 根据 SQL 的特征进行去重，然后自动执行 EXPAIN 输出执行计划；

3. 将结果写到 repository 的文件中，通过 code review 来请 DBA 同学 review。

是不是就可以避免上述手动的问题？ 我们一步一步来。

目的

开发一个小工具(暂且取名为 sql-review-helper)，自动捕捉 CI 过程中执行的 SQL 并根据 SQL 特征归类到一个文件，通过 code review 进行 SQL 审核，避免低效的 SQL 上线导致线上故障。

自动捕捉所有的 SQL

MySQL 中如何捕捉所有的 SQL？SLOW LOG？不是的，MySQL 中还有一个 General Query Log， 通过

SET global general_log = 'ON';
SET global log_output = 'table';

开启 MySQL 的 General Query Log。当然，log_output 也可以是 file，只不过在 CI 环境中，大家通常是通过 gitlab 中的 service 方式启动一个运行在 docker 中的 MySQL 实例，写到 table 中容易将数据导出来。

sql-review-helper prepare--host XXX--user XXX--password XXX--port 3306

获取所有的 SQL, 计算 SQL 特征

接下来就是确保业务中所有的 SQL 被执行的过程。因此 sql-review-helper 的使用是需要前提条件的：

所有的数据库操作必须全部在 CI 过程中被执行，确保所有的 SQL 被记录（这不是废话么，难不成不写单元测试？ ）。

接下来，就是要获取所有的 SQL。

SQL 会有很多，因此需要借助 sql-chief 对 SQL 计算 fingerprint, 识别相似的 SQL(关于 SQL fingerprint 算法参见SQL 特征识别)。

更进一步, 将每个类型的 SQL 的 EXPLAIN 也自动执行, 让低效率的 SQL 一目了然。

输出结果

在 CI 完成后, 检查 CI 过程中执行的所有 SQL 并输出到文件。为了方便在 code review 中做 SQL review, 输出文件必须保证稳定, 因此结果文件按照 fingerprint 排序。

• explain_result 就是 SQL 被 EXPLAIN 的结果, 出现  Using where; Using temporary; Using filesort 就要注意了；

• fingerprint 是 SQL 被计算的指纹信息；

• parameterized_sql 是 SQL 参数被替换成 ? 并格式化的 SQL, fingerprint 字段就是基于这个 SQL 计算而来。

示例 sql 文件: 

---
#
# WARNING:
#   This file is generated by sql-review-helper,
#   including all SQL statements executed grouped by sql-fingerprint
#
#   Manual modification of this file is prohibited
#
# new sql
- explain_result:
   Extra: null
   filtered: 100.0
   id: 1
   key: null
   key_len: null
   partitions: null
   possible_keys: null
   ref: null
   rows: 1
   select_type: SIMPLE
   table: general_log
   type: ALL
fingerprint: sql-73b5bc996402ca9f3b95f482a42d0b62
parameterized_sql: 'SELECT *
   FROM mysql.general_log
   LIMIT ?'
# new sql
- explain_result:
   Extra: Using index condition; Using where; Using temporary; Using filesort
   filtered: 11.11
   id: 1
   key: sqlambda_index
   key_len: '48'
   partitions: null
   possible_keys: sqlambda_index
   ref: null
   rows: 2
   select_type: SIMPLE
   table: test_sqls
   type: range
fingerprint: sql-a06cd31ea45e4746219d23357d000109
parameterized_sql:|
    SELECT sqlambda_id, uuid, username, substr(sql_params, ?, ?) AS sql_params, substr(input_sql, ?, ?) AS input_sql
           , create_time, finish_time, update_time, status
   FROM test_sqls
   WHERE sqlambda_id LIKE ?
   ORDER BY create_time DESC
   LIMIT ?, ?

如果有新的 SQL，就可以在 code review 过程中像争论变量命名一样争论 SQL 了。

如何使用

使用也很简单:

1. 在 CI 的 image 中安装 sql-review-helper；

2. 在执行 CI前通过执行 sql-review-helper prepare 准备好SQL 捕捉；

3. 在本地正常执行 CI, 确保所有业务 SQL 都被执行；

4. 最后执行 sql-review-helper generate --passwd $MYSQL_ROOT_PASSWORD --output-file sql-to-review.yml --db your_database

• --output-file sql-to-review.yml: 指定结果文件

• 如果 sql-to-review.yml 有修改, 通过 git add && git commit 提交；

5. 剩下的就是在 code review 过程针对新的 SQL 做 review 了。

额外用处

线上 MySQL 都有 Slow Query 监控，并且我们已经把 Slow Query 使用同样的sql-chief 服务计算了 fingerprint 并存储到了 ELK，因此如果线上发现了 Slow Query，可以从 ELK 中拿着 SQL 的 fingerprint 到 sql-to-review.yml 中查看 SQL 相关的信息，甚至 git blame 一下看到底是谁写的……

总结

通过sql-review-helper，可以避免手动提交 SQL Review 遗漏的情况. 当然, 这种方式也有一定的局限性, 例如分库分表查询的问题。你们是如何解决 SQL 审核问题的, 欢迎评论。