小米开源工具SOAR&SOAR-WEB

2020-03-01 2657

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介： MySQL SOAR 优化

一、简介

1.1 SOAR

SOAR 是一个对 SQL 进行优化和改写的自动化工具。由小米人工智能与云平台的数据库团队开发与维护。

下载地址：
https://github.com/XiaoMi/soar/releases/download/0.11.0/soar.linux-amd64

1.1.1 功能特点

跨平台支持（支持 Linux, Mac 环境，Windows 环境理论上也支持，不过未全面测试）
目前只支持 MySQL 语法族协议的 SQL 优化
支持基于启发式算法的语句优化
支持复杂查询的多列索引优化（UPDATE, INSERT, DELETE, SELECT）
支持 EXPLAIN 信息丰富解读
支持 SQL 指纹、压缩和美化
支持同一张表多条 ALTER 请求合并
支持自定义规则的 SQL 改写

1.1.2 产品对比

1.2 SOAR-WEB

基于小米 soar 的开源 sql 分析与优化的 web 图形化工具，支持 soar 配置的添加、修改、复制，多配置切换，配置的导出、导入与导入功能。

下载地址：
https://codeload.github.com/xiyangxixian/soar-web/zip/master

二、基本使用

2.1 SOAR

2.1.1 体系结构

2.1.2 配置文件说明

配置文件为yaml格式。一般情况下只需要配置online-dsn, test-dsn, log-output等少数几个参数。即使不创建配置文件SOAR仍然会给出基本的启发式建议。
默认文件会按照/etc/soar.yaml, ./etc/soar.yaml, ./soar.yaml顺序加载，找到第一个后不再继续加载后面的配置文件。如需指定其他配置文件可以通过-config参数指定。
关于数据库权限online-dsn需要相应库表的SELECT权限，test-dsn需要root最高权限。

# 线上环境配置
online-dsn:
  addr: 127.0.0.1:3306
  schema: sakila
  user: root
  password: 1t'sB1g3rt
  disable: false
# 测试环境配置
test-dsn:
  addr: 127.0.0.1:3307
  schema: test
  user: root
  password: 1t'sB1g3rt
  disable: false
# 是否允许测试环境与线上环境配置相同
allow-online-as-test: true
# 是否清理测试时产生的临时文件
drop-test-temporary: true
# 语法检查小工具
only-syntax-check: false
sampling-statistic-target: 100
sampling: false
# 日志级别，[0:Emergency, 1:Alert, 2:Critical, 3:Error, 4:Warning, 5:Notice, 6:Informational, 7:Debug]
log-level: 7
log-output: ${your_log_dir}/soar.log
# 优化建议输出格式
report-type: markdown
ignore-rules:
- ""
# 黑名单中的 SQL 将不会给评审意见。一行一条 SQL，可以是正则也可以是指纹，填写指纹时注意问号需要加反斜线转义。
blacklist: ${your_config_dir}/soar.blacklist
# 启发式算法相关配置
max-join-table-count: 5
max-group-by-cols-count: 5
max-distinct-count: 5
max-index-cols-count: 5
max-total-rows: 9999999
spaghetti-query-length: 2048
allow-drop-index: false
# EXPLAIN相关配置
explain-sql-report-type: pretty
explain-type: extended
explain-format: traditional
explain-warn-select-type:
- ""
explain-warn-access-type:
- ALL
explain-max-keys: 3
explain-min-keys: 0
explain-max-rows: 10000
explain-warn-extra:
- ""
explain-max-filtered: 100
explain-warn-scalability:
- O(n)
query: ""
list-heuristic-rules: false
list-test-sqls: false
verbose: true

2.1.3 命令行参数

几乎所有配置文件中指定的参数都通通过命令行参数进行修改，且命令行参数优先级较配置文件优先级高。

soar -h

2.1.4 启动

wget https://github.com/XiaoMi/soar/releases/download/0.9.0/soar.linux-amd64 -O soar
chmod a+x soar
开发调试
如下指令如果您没有精力参与SOAR的开发可以跳过。

make deps 依赖检查
make vitess 升级Vitess Parser依赖
make tidb 升级TiDB Parser依赖
make fmt 代码格式化，统一风格
make lint 代码质量检查
make docker 启动一个MySQL测试容器，可用于测试依赖元数据检查的功能或不同版本MySQL差异
make test 运行所有的测试用例
make cover 代码测试覆盖度检查
make doc 自动生成命令行参数中-list-XX相关文档
make daily 每日构建，时刻跟进Vitess, TiDB依赖变化
make release 生成Linux, Windows, Mac发布版本
安装验证
echo 'select * from film' | ./soar

2.1.5 常用命令

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 * frm tb" | soar -only-syntax-check
At SQL 1 : syntax error at position 13 near 'frm'
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

清理测试环境残余的临时库表
如配置了-drop-test-temporary=false或soar异常中止，-test-dsn中会残余以optimizer_为前缀的临时库表。手工清理这些库表可以使用如下命令。

注意：为了不影响正在进行的其他SQL评审，-cleanup-test-database中会删除1小时前生成的临时库表。
./soar -cleanup-test-database

2.2 SOAR-WEB

2.2.1 环境需求

python3.x
Flask
pymysql
pycryptodome
pip install -r requirement.txt
注：若 Crypto 模块找不到, 则需要在 python 的依赖库目录 Lib\site-packages 中将 crypto 重命名为 Crypto 。

解压缩（ Windows 可略过此步骤）：
sudo -y apt-get install unzip 或者 sudo yum -y install unzip 
unzip soar-web.zip
cd soar-web-matster

2.2.2 启动

运行启动脚本：
Windows： run.bat
Linux or Mac： bash run.sh

守护进程支持：
启动服务：bash manage.sh start
关闭服务：bash manage.sh stop
重启服务：bash manage.sh restart
注：当主机上存在多个 python 版本时, 需更改 run.sh, run.bat, manage.sh 中的 python 版本指定为 3.x 的版本运行。

在浏览器上输入 http://IP:5077 进行访问。
如果需要改 IP 地址和端口号, 可在 config.py 中进行修改。

存储： 
所有的配置都是保存在浏览器 Local Storage 中的，多人之间使用是互不影响的，自己只能看到自己的配置，更换浏览器或者清除浏览器会造成配置丢失。

数据库连接：
数据库连接成功后，soar 可以通过表结构提供更正确优质的 sql 评估建议， 配置的正确性决定了 soar 的服务质量。

线上线下环境问题：
线上环境作为待 sql 评估环境，soar 在进行 sql 评估时，会根据 sql 语句，从 线上环境的数据库连接实例 拷贝数据表到 测试环境的数据库连接实例，然后在测试环境下执行 sql 语句进行分析。因此测试环境的数据库连接实例需要有最高权限。如果没有最高权限可能造成一些问题，如果没有权限可以启动一个空的 mysql docker 容器作为测试环境。如果仅仅做测试用，可将线上线下环境指定为一样。

参考文档

https://github.com/XiaoMi/soar
https://github.com/xiyangxixian/soar-web