SOAR
SOAR(SQL Optimizer And Rewriter)是一个对SQL进行优化和改写的自动化工具。 由小米人工智能与云平台的数据库团队开发与维护。
功能特点
- 跨平台支持(支持Linux, Mac环境,Windows环境理论上也支持,不过未全面测试)
- 目前只支持 MySQL 语法族协议的SQL优化
- 支持基于启发式算法的语句优化
- 支持复杂查询的多列索引优化(UPDATE, INSERT, DELETE, SELECT)
- 支持EXPLAIN信息丰富解读
- 支持SQL指纹、压缩和美化
- 支持同一张表多条ALTER请求合并
- 支持自定义规则的SQL改写
使用方法
-
下载 https://github.com/XiaoMi/soar/releases soar.windows-amd64 并修改文件名为 soar.exe,,放在soar文件夹
-
进入soar目录,cmd命令行输入,soar -version,输出如下信息,则安装成功
-
输入 soar -h 获取帮助信息(信息较长,未截取完整,可自行仔细查看)
-
创建配置文件,在soar目录创建log目录存放日志,创建目录etc,创建文件soar.yaml配置数据库等信息
# 线上环境配置
online-dsn:
user: "root"
password: 'root'
net: tcp
addr: 127.0.0.1:3306
schema: "demo"
charset: utf8
collation: utf8_general_ci
loc: UTC
tls: ""
server-public-key: ""
maxallowedpacket: 4194304
params: {}
timeout: 0
read-timeout: 0
write-timeout: 0
allow-native-passwords: true
allow-old-passwords: false
disable: false
# 测试环境配置
test-dsn:
user: "root"
password: 'root'
net: tcp
addr: 127.0.0.1:3306
schema: "demo"
charset: utf8
collation: utf8_general_ci
loc: UTC
tls: ""
server-public-key: ""
maxallowedpacket: 4194304
params: {}
timeout: 0
read-timeout: 0
write-timeout: 0
allow-native-passwords: true
allow-old-passwords: false
disable: false
# 是否允许测试环境与线上环境配置相同
allow-online-as-test: true
# 是否清理测试时产生的临时文件
drop-test-temporary: true
cleanup-test-database: false
# 语法检查小工具
only-syntax-check: false
sampling-statistic-target: 100
sampling: false
sampling-condition: ""
profiling: true
trace: true
explain: true
delimiter: ;
# 日志级别,[0:Emergency, 1:Alert, 2:Critical, 3:Error, 4:Warning, 5:Notice, 6:Informational, 7:Debug]
log-level: 7
log-output: C:\dev\soar\log\soar.log
# 优化建议输出格式
report-type: html
report-css: ""
report-javascript: ""
report-title: SQL优化分析报告
markdown-extensions: 94
markdown-html-flags: 0
ignore-rules:
- COL.011
rewrite-rules:
- delimiter
- orderbynull
- groupbyconst
- dmlorderby
- having
- star2columns
- insertcolumns
- distinctstar
blacklist: ""
max-join-table-count: 5
max-group-by-cols-count: 5
max-distinct-count: 5
max-index-cols-count: 5
max-text-cols-count: 2
max-total-rows: 9999999
max-query-cost: 9999
spaghetti-query-length: 2048
allow-drop-index: false
max-in-count: 10
max-index-bytes-percolumn: 767
max-index-bytes: 3072
allow-charsets:
- utf8
- utf8mb4
allow-collates: []
allow-engines:
- innodb
max-index-count: 10
max-column-count: 40
max-value-count: 100
index-prefix: idx_
unique-key-prefix: uk_
max-subquery-depth: 5
max-varchar-length: 1024
column-not-allow-type:
- boolean
min-cardinality: 0
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:
- Using temporary
- Using filesort
explain-max-filtered: 100
explain-warn-scalability:
- O(n)
show-warnings: false
show-last-query-cost: false
query: ""
list-heuristic-rules: false
list-rewrite-rules: false
list-test-sqls: false
list-report-types: false
verbose: false
dry-run: true
max-pretty-sql-length: 1024
测试配置是否成功,命令行输入 soar -print-config,输出配置文件的内容则配置文件生效
- 在soar目录创建test.sql文件,写入sql语句,如
select * from book where id>1 ;
select *,count(1) from book where id>1000 ;
- 命令行输入 soar -query test.sql > ./index.html 则结果以html文件输出
原文:http://centphp.com/view/155