常用命令

基本用法

  1. echo "select title from sakila.film" | ./soar -log-output=soar.log

指定配置文件

  1. vi soar.yaml
  2. # yaml format config file
  3. online-dsn:
  4.     addr:     127.0.0.1:3306
  5.     schema:   sakila
  6.     user:     root
  7.     password: "1t'sB1g3rt"
  8.     disable:  false
  10. test-dsn:
  11.     addr:     127.0.0.1:3306
  12.     schema:   sakila
  13.     user:     root
  14.     password: "1t'sB1g3rt"
  15.     disable:  false
  1. 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

打印所有的启发式规则

  1. $ soar -list-heuristic-rules

忽略某些规则

  1. $ soar -ignore-rules "ALI.001,IDX.*"

打印支持的报告格式

  1. $ soar -list-report-types

以指定格式输出报告

  1. $ soar -report-type json

语法检查工具

  1. $ echo "select * from tb" | soar -only-syntax-check
  2. $ echo $?
  3. 0
  5. $ echo "select * fromtb" | soar -only-syntax-check
  6. At SQL 0 : syntax error at position 16 near 'fromtb'
  7. $ echo $?
  8. 1

慢日志进行分析示例

  1. $ pt-query-digest slow.log > slow.log.digest
  2. # parse pt-query-digest's output which example script
  3. $ python2.7 doc/example/digest_pt.py slow.log.digest > slow.md

SQL指纹

  1. $ echo "select * from film where col='abc'" | soar -report-type=fingerprint

输出

  1. select * from film where col=?

将UPDATE/DELETE/INSERT语法转为SELECT

  1. $ echo "update film set title = 'abc'" | soar -rewrite-rules dml2select,delimiter  -report-type rewrite

输出

  1. select * from film;

合并多条ALTER语句

  1. $ echo "alter table tb add column a int; alter table tb add column b int;" | soar -report-type rewrite -rewrite-rules mergealter

输出

  1. ALTER TABLE `tb` add column a int, add column b int ;

SQL美化

  1. $ echo "select * from tbl where col = 'val'" | ./soar -report-type=pretty

输出

  1. SELECT
  2.   *
  3. FROM
  4.   tbl
  5. WHERE
  6.   col  = 'val';

EXPLAIN信息分析报告

  1. $ soar -report-type explain-digest << EOF
  2. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
  4. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  5. |  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1131 |       |
  6. +----+-------------+-------+------+---------------+------+---------+------+------+-------+
  7. EOF
  1. ##  Explain信息
  3. | id | select\_type | table | partitions | type | possible_keys | key | key\_len | ref | rows | filtered | scalability | Extra |
  4. |---|---|---|---|---|---|---|---|---|---|---|---|---|
  5. | 1  | SIMPLE | *film* | NULL | ALL | NULL | NULL | NULL | NULL | 0 | 0.00% | ☠️ **O(n)** |  |
  8. ### Explain信息解读
  10. #### SelectType信息解读
  12. * **SIMPLE**: 简单SELECT(不使用UNION或子查询等).
  14. #### Type信息解读
  16. * ☠️ **ALL**: 最坏的情况, 从头到尾全表扫描.

markdown转HTML

通过指定-report-css, -report-javascript, -markdown-extensions, -markdown-html-flags这些参数，你还可以控制HTML的显示格式。

  1. $ cat test.md | soar -report-type md2html > test.html