soar sql优化工具

发表于： 2018-11-23 2018-11-23
分类： SRE
标签： MySQL, sql, 工具

SOAR(SQL Optimizer And Rewriter),小米出品，SQL审阅优化工具。
目前仅针对MySQL语法族进行开发和测试，其他使用SQL的数据库产品暂不支持

安装

cd /usr/bin
wget https://github.com/XiaoMi/soar/releases/download/0.9.0/soar.linux-amd64 -O soar
chmod a+x soar
echo 'select * from film' | ./soar

基本用法

help

[root@server bin]# soar -h
Usage of soar:
  -allow-drop-index
        AllowDropIndex, 允许输出删除重复索引的建议
  -allow-online-as-test
        AllowOnlineAsTest, 允许线上环境也可以当作测试环境
  -blacklist string
        blacklist 中的 SQ L不会被评审，可以是指纹，也可以是正则
  -cleanup-test-database
        单次运行清理历史1小时前残余的测试库。
  -config string
        Config file path
  -conn-time-out int
        ConnTimeOut, 数据库连接超时时间，单位秒 (default 3)
  -delimiter string
        Delimiter, SQL分隔符 (default ";")
  -drop-test-temporary
        DropTestTemporary, 是否清理测试环境产生的临时库表 (default true)
  -dry-run
        是否在预演环境执行 (default true)
  -explain
        Explain, 是否开启Explain执行计划分析 (default true)
  -explain-format string
        ExplainFormat [json, traditional] (default "traditional")
  -explain-max-filtered float
        ExplainMaxFiltered, filtered大于该配置给出警告 (default 100)
  -explain-max-keys int
        ExplainMaxKeyLength, 最大key_len (default 3)
  -explain-max-rows int
        ExplainMaxRows, 最大扫描行数警告 (default 10000)
  -explain-min-keys int
        ExplainMinPossibleKeys, 最小possible_keys警告
  -explain-sql-report-type string
        ExplainSQLReportType [pretty, sample, fingerprint] (default "pretty")
  -explain-type string
        ExplainType [extended, partitions, traditional] (default "extended")
  -explain-warn-access-type string
        ExplainWarnAccessType, 哪些access type不建议使用 (default "ALL")
  -explain-warn-extra string
        ExplainWarnExtra, 哪些extra信息会给警告 (default "Using temporary,Using filesort")
  -explain-warn-scalability string
        ExplainWarnScalability, 复杂度警告名单, 支持O(n),O(log n),O(1),O(?) (default "O(n)")
  -explain-warn-select-type string
        ExplainWarnSelectType, 哪些select_type不建议使用
  -ignore-rules string
        IgnoreRules, 忽略的优化建议规则 (default "COL.011")
  -index-prefix string
        IdxPrefix (default "idx_")
  -list-heuristic-rules
        ListHeuristicRules, 打印支持的评审规则列表
  -list-report-types
        ListReportTypes, 打印支持的报告输出类型
  -list-rewrite-rules
        ListRewriteRules, 打印支持的重写规则列表
  -list-test-sqls
        ListTestSqls, 打印测试case用于测试
  -log-level int
        LogLevel, 日志级别, [0:Emergency, 1:Alert, 2:Critical, 3:Error, 4:Warning, 5:Notice, 6:Informational, 7:Debug] (default 3)
  -log-output string
        LogOutput, 日志输出位置 (default "/dev/stderr")
  -markdown-extensions int
        MarkdownExtensions, markdown 转 html支持的扩展包, 参考blackfriday (default 94)
  -markdown-html-flags int
        MarkdownHTMLFlags, markdown 转 html 支持的 flag, 参考blackfriday
  -max-column-count int
        MaxColCount, 单表允许的最大列数 (default 40)
  -max-distinct-count int
        MaxDistinctCount, 单条 SQL 中 Distinct 的最大数量 (default 5)
  -max-group-by-cols-count int
        MaxGroupByColsCount, 单条 SQL 中 GroupBy 包含列的最大数量 (default 5)
  -max-in-count int
        MaxInCount, IN()最大数量 (default 10)
  -max-index-bytes int
        MaxIdxBytes, 索引总长度限制 (default 3072)
  -max-index-bytes-percolumn int
        MaxIdxBytesPerColumn, 索引中单列最大字节数 (default 767)
  -max-index-cols-count int
        MaxIdxColsCount, 复合索引中包含列的最大数量 (default 5)
  -max-index-count int
        MaxIdxCount, 单表最大索引个数 (default 10)
  -max-join-table-count int
        MaxJoinTableCount, 单条 SQL 中 JOIN 表的最大数量 (default 5)
  -max-pretty-sql-length int
        MaxPrettySQLLength, 超出该长度的SQL会转换成指纹输出 (default 1024)
  -max-query-cost int
        MaxQueryCost, last_query_cost 超过该值时将给予警告 (default 9999)
  -max-subquery-depth int
        MaxSubqueryDepth (default 5)
  -max-total-rows int
        MaxTotalRows, 计算散粒度时，当数据行数大于MaxTotalRows即开启数据库保护模式，不计算散粒度 (default 9999999)
  -max-varchar-length int
        MaxVarcharLength (default 1024)
  -online-dsn string
        OnlineDSN, 线上环境数据库配置, username:********@ip:port/schema
  -only-syntax-check
        OnlySyntaxCheck, 只做语法检查不输出优化建议
  -print-config
        Print configs
  -profiling
        Profiling, 开启数据采样的情况下在测试环境执行Profile
  -query string
        待评审的 SQL 或 SQL 文件，如 SQL 中包含特殊字符建议使用文件名。
  -query-time-out int
        QueryTimeOut, 数据库SQL执行超时时间，单位秒 (default 30)
  -report-css string
        ReportCSS, 当 ReportType 为 html 格式时使用的 css 风格，如不指定会提供一个默认风格。CSS可以是本地文件，也可以是一个URL
  -report-javascript string
        ReportJavascript, 当 ReportType 为 html 格式时使用的javascript脚本，如不指定默认会加载SQL pretty 使用的 javascript。像CSS一样可以是本地文件，也可以是一个URL
  -report-title string
        ReportTitle, 当 ReportType 为 html 格式时，HTML 的 title (default "SQL优化分析报告")
  -report-type string
        ReportType, 化建议输出格式，目前支持: json, text, markdown, html等 (default "markdown")
  -rewrite-rules string
        RewriteRules, 生效的重写规则 (default "delimiter,orderbynull,groupbyconst,dmlorderby,having,star2columns,insertcolumns,distinctstar")
  -sampling
        Sampling, 数据采样开关
  -sampling-statistic-target int
        SamplingStatisticTarget, 数据采样因子，对应 postgres 的 default_statistics_target (default 100)
  -show-last-query-cost
        ShowLastQueryCost
  -show-warnings
        ShowWarnings
  -spaghetti-query-length int
        SpaghettiQueryLength, SQL最大长度警告，超过该长度会给警告 (default 2048)
  -table-allow-charsets string
        TableAllowCharsets (default "utf8,utf8mb4")
  -table-allow-engines string
        TableAllowEngines (default "innodb")
  -test-dsn string
        TestDSN, 测试环境数据库配置, username:********@ip:port/schema
  -trace
        Trace, 开启数据采样的情况下在测试环境执行Trace
  -unique-key-prefix string
        UkPrefix (default "uk_")
  -verbose
        Verbose
  -version
        Print version info

配置文件

配置文件为yaml格式。一般情况下只需要配置online-dsn, test-dsn, log-output等少数几个参数。即使不创建配置文件SOAR仍然会给出基本的启发式建议。

默认文件会按照/etc/soar.yaml, ./etc/soar.yaml, ./soar.yaml顺序加载，找到第一个后不再继续加载后面的配置文件。如需指定其他配置文件可以通过-config参数指定。

关于数据库权限online-dsn需要相应库表的SELECT权限，test-dsn需要root最高权限。

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:3307
  schema: test
  user: root
  password: 1t'sB1g3rt
  disable: false
# 是否允许测试环境与线上环境配置相同
allow-online-as-test: true
# 是否清理测试时产生的临时文件
drop-test-temporary: true
# 语法检查小工具
only-syntax-check: false
sampling-data-factor: 100
sampling: true
# 日志级别，[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

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

完整例子

[root@server ~]# echo 'select * from history' | soar

Query: F374908F161C8738

★ ★ ★ ☆ ☆ 75分


SELECT
  *
FROM
  history

Explain信息

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	scalability	Extra
1	SIMPLE	history	NULL	ALL		NULL			☠️ 73414	☠️ 100.00%	☠️ O(n)	NULL

Explain信息解读

SelectType信息解读

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

Type信息解读

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

最外层SELECT未指定WHERE条件

Item: CLA.001
Severity: L4
Content: SELECT语句没有WHERE子句，可能检查比预期更多的行(全表扫描)。对于SELECT COUNT(*)类型的请求如果不要求精度，建议使用SHOW TABLE STATUS或EXPLAIN替代。

不建议使用SELECT * 类型查询

Item: COL.001
Severity: L1
Content: 当表结构变更时，使用*通配符选择所有列将导致查询的含义和行为会发生更改，可能导致查询返回更多的数据。

admin

发表回复取消回复