小米正式开源 SQL 智能优化与改写工具 SOAR

SOAR，即 SQL Optimizer And Rewriter，是一款 SQL 智能优化与改写工具，由小米运维 DBA 团队出品，于今日正式宣布开源。

整体架构图如下

功能特性

• 跨平台支持（支持 Linux、Mac 环境，Windows 环境理论上也支持，不过未全面测试）

• 支持基于启发式算法的语句优化

• 支持复杂查询的多列索引优化（UPDATE, INSERT, DELETE, SELECT）

• 支持 EXPLAIN 信息丰富解读

• 支持 SQL 指纹、压缩和美化

• 支持同一张表多条 ALTER 请求合并

• 支持自定义规则的 SQL 改写

SOAR主要由语法解析器，集成环境，优化建议，重写逻辑，工具集五大模块组成。下面将对每个模块的作用及设计实现进行简述，更详细的算法及逻辑会在各个独立章节中详细讲解。

语法解析和语法检查

一条SQL从文件，标准输入或命令行参数等形式传递给SOAR后首先进入语法解析器，这里一开始我们选用了vitess的语法解析库作为SOAR的语法解析库，但随时需求的不断增加我们发现有些复杂需求使用vitess的语法解析实现起来比较逻辑比较复杂。于是参考业办其他数据库产品，我们引入了TiDB的语法解析器做为补充。我们发现这两个解析库还存在一定的盲区，于是又引入了MySQL执行返回结果作为多多版本SQL方言的补充。大家也可以看到在语法解析器这里，SOAR的实现方案是松散的、可插拔的。SOAR并不直接维护庞大的语法解析库，它把各种优秀的语法解析库集成在一起，各取所长。

集成环境

集成环境区分线上环境和测试环境两种，分别用于解决不同场景下用户的SQL优化需求。一种常见的情况是已有表结构需要优化查询SQL的场景，可以从线上环境导出表结构和足够的采样数据到测试环境，在测试环境上就可以放心的执行各种高危操作而不用担心数据被损坏。另一种常见的情况是建一套全新的数据库，需要验证提供的数据字典中是否存在优化的可能。对于这种情况，很有可能你不需要知道线上环境在哪儿，完全只是想先试试看，如果报错了马上改对就是了。当然还有更多种组合的场景需求，将在集成环境一单分类说明。

优化建议

目前SOAR可以提供的优化建议有基于启发式规则(通常也称之为经验)的优化建议，基于索引优化算法给出的索引优化建议，以及基于EXPLAIN信息给出的解读。

启发式规则建议

下面这段代码是启发式规则的的元数据结构，它由规则代号，危险等级，规则摘要，规则解释，SQL示例，建议位置，规则函数等7部分组成。每一条SQL经过语法解析后会经过数百个启发式规则的逐一检查，命中了的规则将会保存在一个叫heuristicSuggest的变量中传递下去，与其他优化建议合并输出。这里最核心的部分，也是代码最多的部分在heuristic.go，里面包含了所有的启发式规则实现的函数。所有的启发式规则列表保存在rules.go文件中。

// Rule 评审规则元数据结构
type Rule struct {
    Item     string                  `json:"Item"`     // 规则代号
    Severity string                  `json:"Severity"` // 危险等级：L[0-8], 数字越大表示级别越高
    Summary  string                  `json:"Summary"`  // 规则摘要
    Content  string                  `json:"Content"`  // 规则解释
    Case     string                  `json:"Case"`     // SQL示例
    Position int                     `json:"Position"` // 建议所处SQL字符位置，默认0表示全局建议
    Func     func(*Query4Audit) Rule `json:"-"`        // 函数名
}

索引优化

关于索引优化，数据库经过几十年的发展，DBA沉淀了很多宝贵的经验，怎样把这些感性的经验转化为覆盖全面、逻辑可推导的算法是这种模块最大的挑战。很幸运的是SOAR并不是第一个尝试做这类算法整理的产品，有很多前人的著作、论文、博客等的知识储备。毫不夸张的说，为了写成这个模块我们读了不下5百万字的著作和论文，还不包括网络上各种大神的博客，这些老师们的知识结晶收集整理在鸣谢章节。使用到的算法在索引优化章节有详细的描述，虽然在某些算法理解上可能还存在一定争议，很希望与同行们共同讨论，共同进步，不断完善SOAR的算法。

EXPLAIN解读

做过SQL优化的人对EXPLAIN应该都不陌生，但对于新手来说要记住每一个列代表什么含义，每个关键字背后的奥秘是什么需要足够的脑容量来记忆才行。统计了一下SOAR只在EXPLAIN信息的注解一项差不多写了200行代码，按平均行长度120计算，算下来一个DBA要精通EXPLAIN优化就要记住不下2万字的文档。SOAR能帮每为DBA节约了这部分脑容量。不过关于EXPLAIN解读还远不止这些，想了解更多可以参考EXPLAIN信息解读章节。

重写逻辑

上面提到的优化建议是我们早期实现的主要功能，早期的功能还只是停留在建议上，对于一些初级用户看到建议也不一定会改写。为了进一步简化SQL优化的成本，SOAR又进一步挖掘了自动SQL重写的功能。现在提供几十种常见场景下的SQL等价转写，不过相比SQL优化建议还有很大的改进空间。这部分的功能和逻辑将在重写逻辑一章中详细说明。

工具集

除了SQL优化和改写以外，为了方便用户使用以及美化输出展现形式，SOAR还提供了一些辅助的小工具，比如markdown转HTML工具，SQL格式化输出工具等等。你可以在常用命令中找到这些小工具的使用方法。

常用命令

基本用法

echo "select title from sakila.film" | ./soar -log-output=soar.log

指定配置文件

vi soar.yaml
# yaml format config file
online-dsn:
    addr:     127.0.0.1:3306
    schema:   sakila
    user:     root
    password: "1t'sB1g3rt"
    disable:  false

test-dsn:
    addr:     127.0.0.1:3306
    schema:   sakila
    user:     root
    password: "1t'sB1g3rt"
    disable:  false
echo "select title from sakila.film" | ./soar -test-dsn="root:1t'sB1g3rt@127.0.0.1:3306/sakila" -allow-online-as-test -log-output=soar.log

打印所有的启发式规则

$ soar -list-heuristic-rules

忽略某些规则

$ soar -ignore-rules "ALI.001,IDX.*"

打印支持的报告格式

$ soar -list-report-types

以指定格式输出报告

$ soar -report-type json

语法检查工具

$ echo "select * from tb" | soar -only-syntax-check
$ echo $?
0

$ echo "select * fromtb" | soar -only-syntax-check
At SQL 0 : syntax error at position 16 near 'fromtb'
$ echo $?
1

慢日志进行分析示例

$ pt-query-digest slow.log > slow.log.digest
# parse pt-query-digest's output which example script
$ python2.7 doc/example/digest_pt.py slow.log.digest > slow.md
SQL指纹
$ echo "select * from film where col='abc'" | soar -report-type=fingerprint

输出

select * from film where col=?

将UPDATE/DELETE/INSERT语法转为SELECT

$ echo "update film set title = 'abc'" | soar -rewrite-rules dml2select,delimiter  -report-type rewrite

输出

select * from film;

合并多条ALTER语句

$ echo "alter table tb add column a int; alter table tb add column b int;" | soar -report-type rewrite -rewrite-rules mergealter

输出

ALTER TABLE `tb` add column a int, add column b int ;

SQL美化

$ echo "select * from tbl where col = 'val'" | ./soar -report-type=pretty


输出

SELECT
  *
FROM
  tbl
WHERE
  col  = 'val';

EXPLAIN信息分析报告

$ soar -report-type explain-digest << EOF
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1131 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
EOF
##  Explain信息

| id | select\_type | table | partitions | type | possible_keys | key | key\_len | ref | rows | filtered | scalability | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1  | SIMPLE | *film* | NULL | ALL | NULL | NULL | NULL | NULL | 0 | 0.00% | ☠️ **O(n)** |  |


### Explain信息解读

#### SelectType信息解读

* **SIMPLE**: 简单SELECT(不使用UNION或子查询等).

#### Type信息解读

* ☠️ **ALL**: 最坏的情况, 从头到尾全表扫描.

markdown转HTML

通过指定-report-css, -report-javascript, -markdown-extensions, -markdown-html-flags这些参数，你还可以控制HTML的显示格式。

$ cat test.md | soar -report-type md2html > test.html

有兴趣的伙伴们，可以研究研究，折腾折腾。

Github：https://github.com/XiaoMi/soar

荐阅读

·end·

—写文不易，你的转发就是对我最大的支持—

我们一起愉快的玩耍吧

目前40000+人已关注加入我们

关注公众号点击菜单“微信群” 入群一起交流吧！

喜欢,就扫码关注给它增加一个读者吧！