小米开源的数据库SQL性能分析工具SOAR,windows版安装使用

 软件  2019-08-17  admin  5271  7115

SOAR

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

功能特点

  • 跨平台支持(支持Linux, Mac环境,Windows环境理论上也支持,不过未全面测试)

  • 目前只支持 MySQL 语法族协议的SQL优化

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

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

  • 支持EXPLAIN信息丰富解读

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

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

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

使用方法

  1. 下载 https://github.com/XiaoMi/soar/releases  soar.windows-amd64 并修改文件名为 soar.exe,,放在soar文件夹

  2. 进入soar目录,cmd命令行输入,soar -version,输出如下信息,则安装成功


    Snipaste_2019-08-17_10-49-14.png

  3.输入 soar -h 获取帮助信息

Usage of soar:                                                                                
  -allow-charsets string                                                                      
        AllowCharsets (default "utf8,utf8mb4")                                                
  -allow-collates string                                                                      
        AllowCollates                                                                         
  -allow-drop-index                                                                           
        AllowDropIndex, 允许输出删除重复索引的建议                                                         
  -allow-engines string                                                                       
        AllowEngines (default "innodb")                                                       
  -allow-online-as-test                                                                       
        AllowOnlineAsTest, 允许线上环境也可以当作测试环境 (default true)                                     
  -alsologtostderr                                                                            
        log to standard error as well as files                                                
  -blacklist string                                                                           
        指定 blacklist 配置文件的位置,文件中的 SQL 不会被评审。一行一条SQL,可以是指纹,也可                                  
以是正则                                                                                          
  -check-config                                                                               
        Check configs                                                                         
  -cleanup-test-database                                                                      
        单次运行清理历史1小时前残余的测试库。                                                                   
  -column-not-allow-type string                                                               
        ColumnNotAllowType (default "boolean")                                                
  -config string                                                                              
        Config file path                                                                      
  -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 7)                                                          
  -log-output string                                                                          
        LogOutput, 日志输出位置 (default "C:\\dev\\soar\\log\\soar.log")                            
  -log_backtrace_at value                                                                     
        when logging hits line file:N, emit a stack trace                                     
  -log_dir string                                                                             
        If non-empty, write log files in this directory                                       
  -log_rotate_max_size uint                                                                   
        size in bytes at which logs are rotated (glog.MaxSize) (default 1887436800)           
  -logtostderr                                                                                
        log to standard error instead of files                                                
  -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-text-cols-count int                                                                    
        MaxTextColsCount, 表中含有的 text/blob 列的最大数量 (default 2)                                  
  -max-total-rows uint                                                                        
        MaxTotalRows, 计算散粒度时,当数据行数大于MaxTotalRows即开启数据库保护模式,不计算散粒                              
度 (default 9999999)                                                                           
  -max-value-count int                                                                        
        MaxValueCount, INSERT/REPLACE 单次批量写入允许的行数 (default 100)                               
  -max-varchar-length int                                                                     
        MaxVarcharLength (default 1024)                                                       
  -min-cardinality float                                                                      
        MinCardinality,索引列散粒度最低阈值,散粒度低于该值的列不添加索引,建议范围0.0 ~ 100.                               
0                                                                                             
  -online-dsn string                                                                          
        OnlineDSN, 线上环境数据库配置, username:password@tcp(ip:port)/schema (default "root:ro         
ot@tcp(127.0.0.1:3306)/chp?charset=utf8")                                                     
  -only-syntax-check                                                                          
        OnlySyntaxCheck, 只做语法检查不输出优化建议                                                        
  -print-config                                                                               
        Print configs                                                                         
  -profiling                                                                                  
        Profiling, 开启数据采样的情况下在测试环境执行Profile (default true)                                    
  -query string                                                                               
        待评审的 SQL 或 SQL 文件,如 SQL 中包含特殊字符建议使用文件名。                                               
  -report-css string                                                                          
        ReportCSS, 当 ReportType 为 html 格式时使用的 css 风格,如不指定会提供一个默认风格。CS                         
S可以是本地文件,也可以是一个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 "html")               
  -rewrite-rules string                                                                       
        RewriteRules, 生效的重写规则 (default "delimiter,orderbynull,groupbyconst,dmlorderby,h       
aving,star2columns,insertcolumns,distinctstar")                                               
  -sampling                                                                                   
        Sampling, 数据采样开关                                                                      
  -sampling-condition string                                                                  
        SamplingCondition, 数据采样条件,如: WHERE xxx LIMIT xxx                                      
  -sampling-statistic-target int                                                              
        SamplingStatisticTarget, 数据采样因子,对应 PostgreSQL 的 default_statistics_target (d          
efault 100)                                                                                   
  -show-last-query-cost                                                                       
        ShowLastQueryCost                                                                     
  -show-warnings                                                                              
        ShowWarnings                                                                          
  -spaghetti-query-length int                                                                 
        SpaghettiQueryLength, SQL最大长度警告,超过该长度会给警告 (default 2048)                              
  -sql-max-length-errors int                                                                  
        truncate queries in error logs to the given length (default unlimited)                
  -sql-max-length-ui int                                                                      
        truncate queries in debug UIs to the given length (default 512) (default 512)         
  -stderrthreshold value                                                                      
        logs at or above this threshold go to stderr                                          
  -test-dsn string                                                                            
        TestDSN, 测试环境数据库配置, username:password@tcp(ip:port)/schema (default "root:root         
@tcp(127.0.0.1:3306)/chp?charset=utf8")                                                       
  -trace                                                                                      
        Trace, 开启数据采样的情况下在测试环境执行Trace (default true)                                          
  -unique-key-prefix string                                                                   
        UkPrefix (default "uk_")                                                              
  -v value                                                                                    
        log level for V logs                                                                  
  -verbose                                                                                    
        Verbose                                                                               
  -version                                                                                    
        Print version info                                                                    
  -vmodule value                                                                              
        comma-separated list of pattern=N settings for file-filtered logging

  4.创建配置文件,在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,输出配置文件的内容则配置文件生效

5.在soar目录创建test.sql文件,写入sql语句,如

select * from book where id>1 ;
select *,count(1) from book where id>1000 ;

6.命令行输入 soar -query test.sql > ./index.html 则结果以html文件输出


github地址:https://github.com/XiaoMi/soar


已配置软件下载:

soar.zip


如果文章对您有帮助,点击下方的广告,支持一下作者吧!

相关推荐


Systemd Web 管理系统:简化服务管理

# Systemd Web Systemd Web 是一个 Systemd 的 Web UI 管理系统,通过直观的界面和易用的操作,让用户能够轻松地管理 Linux 系统和服务。该项目旨在简化 Systemd 的使用,使得无论是专业的系统管理员还是普通的用户,都能够方便地对系统进行操作。 ## [github地址] (https://github.com/topascend/systemd

systemctl 使用

Systemd是Linux系统工具,用来启动守护进程,已成为大多数发行版的标准配置。而systemctl是Systemd的主命令,用于管理系统。其实我们大部分服务都有使用systemctl管理,比如MySQL、Nginx等等。常见配置如下:[Unit] #单元描述 Description=GFAPP #在什么服务启动之后再执行本程序 After=mysql.service [Serv

go 的 Pool池对象 sync.Pool 创建mysql连接池

//在这个例子中,我们创建了一个sync.Pool,其中包含一个New函数,该函数创建一个MySQL连接 //在someFunction中,我们使用dbPool.Get()从池中获取一个MySQL连接 //然后我们使用连接进行一些操作,最后使用dbPool.Put()将连接放回池中 //我们可以通过设置dbPool的大小来初始化连接池中的连接数量 //例如,如果我们想要初始化10个连接,我

php 保存远程图片到本地

/** *保存远程图片到本地 *@paramstring$avatar *@returnbool|string */ functionsaveRemoteImage($url,$filename='') { if($url==''){ returnfalse; } try{ if($filename==''){ $ex