SQL性能分析工具SOAR介绍及实践

1.什么是soar?

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

2.它有哪些功能特点？

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

3.为什么要使用它？

有一定开发经验的开发人员，肯定知道sql查询优化的相关经验，比如：sql优化中有说到，当通配符“%”作为查询字符串的第一个字符时，索引不会被使用、尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替、不用“<>”或者“!=”操作符，对不等于操作符的处理会造成全表扫描，可以用“<” or “>”代替等等优化规则。SQL优化的规则可能太多，可能我们不会记得那么太牢，还有就是遇到一些复杂sql，如果对SQL的执行过程没有丰富的经验的话，我们也是没法写出一个性能更高的sql来，到此为止，有人会说了，为啥不用mysql的explain来查看优化sql呢，其实，mysql中的explain功能只能看出sql执行的一个大概，并没有给出我们该在SQL的哪些地方进行优化，那么，soar做到了，soar能给我们的每个SQL评分，并给出通俗易懂的执行过程，以及优化建议，不管你是有丰富开发经验的老手还是刚出茅庐的新手，都能优化sql性能，让你的sql飞起来。

4.如何get it（以windows系统来搭建soar）?

①去官网下载windows版本的soar，下载地址：https://github.com/XiaoMi/soar/releases/tag/v0.8.1

②将下载的soar.windows-amd64名字修改为soar.exe

③在命令框窗口，输入sonar -version，查看是否安装成功

④配置soar.yaml配置文件，注意参数log-output中的日志目录需要手动创建，否则日志无法输出到指定目录下，配置模板内容如下：

# 测试环境数据库配置 test-dsn:   user: root   password: admin@123456   net: tcp   addr: 172.10.8.119:3306   schema: example   charset: utf8mb4   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: D:/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

⑤在某个文件下创建一个query.sql（文件名字可自己定义）的文件，写入你要分析的sql语句，执行命令分析sql：soar -query D:\soar\query.sql > ./reports/report01.html

⑥现在以某个项目中的一个SQL优化分析报告作为例子分析下，如下图：

   explain执行解释截图：

优化建议截图（SQL评分，SQL评分不同类型的建议指定的Severity不同，严重程度数字由低到高依次排序。满分100分，扣到0分为止。L0不扣分只给出建议，L1扣5分，L4扣20分，每级多扣5分以此类推，即扣25分）：