XiaoMi/soar调研/

最新推荐文章于 2024-04-30 16:37:00 发布

Alexyusheng

最新推荐文章于 2024-04-30 16:37:00 发布

阅读量544

点赞数

分类专栏： MySQL 文章标签： dba mysql优化

本文链接：https://blog.csdn.net/qq_42315096/article/details/120749584

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

XiaoMi/soar调研/

github ：https://github.com/xiaomi/soar

安装说明：https://github.com/XiaoMi/soar/blob/master/doc/install.md

源码下载：https://GOlang.google.cn/dl/

环境准备

环境依赖：

一般依赖

go1.17.1（官网上说是1.0+，但我编译的时候报错，所以我直接将 Go 升级为最高版本）
git

GO 下载&&安装&&环境变量

# Go 安装
tar -zxvf go1.17.1.linux-amd64.tar.gz

# 环境变量
vi /etc/profile
export PATH=$PATH:/usr/local/go/bin
# GOPATH环境变量：表示go的工作目录 USER_NAME 为用户名
export GOPATH="/home/USER_NAME/go"
export GOPATH="/home/root/go"

# Go 版本查看：
go version

# source更新环境变量:
source /etc/profile

soar 下载&&安装&&编译

# soar 工具下载
wget https://github.com/XiaoMi/soar/releases/download/0.9.0/soar.linux-amd64 -O soar
chmod a+x soar

# 生成二进制文件
go get -d github.com/XiaoMi/soar
cd ${GOPATH}/src/github.com/XiaoMi/soar && make
......
build Success!

安装验证

方法1：
echo 'select * from mz' | ./soar

方法2：
./soar  -query "select * from mz"

方法3：
cat test.sql
select * from mz
cat test.sql | ./soar

结果展示：
# Query: 88E82CE5233AD07E

★ ★ ★ ☆ ☆ 75分

```sql

SELECT  
  * 
FROM  
  mz

##最外层SELECT未指定WHERE条件

* **Item:**  CLA.001

* **Severity:**  L4

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

##  不建议使用SELECT * 类型查询

* **Item:**  COL.001

* **Severity:**  L1

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

优化建议组成

// Rule 评审规则元数据结构
type Rule struct {
    Item     string                  `json:"Item"`     // 规则代号
    Severity string                  `json:"Severity"` // 危险等级：L[0-8], 数字越大表示级别越高
    Summary  string                  `json:"Summary"`  // 规则摘要
    Content  string                  `json:"Content"`  // 规则解释
    Case     string                  `json:"Case"`     // SQL示例
    Position int                     `json:"Position"` // 建议所处SQL字符位置，默认0表示全局建议
    Func     func(*Query4Audit) Rule `json:"-"`        // 函数名
}

SQL评分（个人感觉没什么用）

当SQL的危险等级（Severity）不同的时候，会对当前的SQL有一个打分的机制。

不同类型的建议指定的Severity不同，严重程度数字由低到高依次排序。满分100分，扣到0分为止。L0不扣分只给出建议，L1扣5分，L2扣10分，每级多扣5分以此类推。当由时给出L1, L2两要建议时扣分叠加，即扣15分。

当然我们可以直接修改rules.go文件来更改扣分建议
vim /home/root/go/src/github.com/XiaoMi/soar/advisor/rules.go

常用命令

打印规则列表

$ soar -list-heuristic-rules

忽略某些规则

$ soar -ignore-rules "*.*"

#以安装验证的SQL为例
echo 'select * from mz' | ./soar -ignore-rules "CLA.001"
# Query: 88E82CE5233AD07E

★ ★ ★ ★ ☆ 95分

```sql

SELECT  
  * 
FROM  
  mz ```

##  不建议使用SELECT * 类型查询

* **Item:**  COL.001

* **Severity:**  L1

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

打印支持的报告格式

$ soar -list-report-types

语法检查工具

$ echo "select * from mz" | soar -only-syntax-check

$ echo "select * frommz" | soar -only-syntax-check
At SQL 0 : syntax error at position 16 near 'frommz'

SQL指纹

SQL 指纹指将一条 SQL 中的字面值替换成其他固定符号。可以用来做 SQL 脱敏或者 SQL 归类。

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

输出

select * from mz where col=?

将UPDATE/DELETE/INSERT语法转为SELECT

$ echo "update mz set dba = 'abc'" | soar -rewrite-rules dml2select,delimiter  -report-type rewrite

输出

select * from mz;

合并多条ALTER语句

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

输出

ALTER TABLE `mz` add column a int, add column b int ;

SQL美化

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

输出

SELECT  
  * 
FROM  
  mz  
WHERE  
  col  = 'val';

EXPLAIN信息分析报告

在这里插入图片描述

工具测试

案例

尽量不用Select *

echo 'SELECT * FROM mz WHERE a = 123;' | ./soar 
# Query: 00D9A34797B3F9DF
★ ★ ★ ★ ☆ 95分

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

改写Or为IN()

echo 'SELECT a FROM mz WHERE a = 123 or a = 222 ;' | ./soar 
# Query: B1392DCB03D1DE3B
★ ★ ★ ★ ☆ 100分
 = 222

##  OR查询索引列时请尽量使用IN谓词
* **Item:**  ARG.008
* **Severity:**  L1
* **Content:**  IN-list谓词可以用于索引检索，并且优化器可以对IN-list进行排序，以匹配索引的排序序列，从而获得更有效的检索。请注意，IN-list必须只包含常量，或在查询块执行期间保持常量的值，例如外引用。

改写Or为Union

echo 'SELECT a FROM mz WHERE a = 123 or b = "测试" ;' | ./soar 
# Query: BE88A6C83D6E8BE9
★ ★ ★ ★ ☆ 95分

##  OR查询索引列时请尽量使用IN谓词
* **Item:**  ARG.008
* **Severity:**  L1
* **Content:**  IN-list谓词可以用于索引检索，并且优化器可以对IN-list进行排序，以匹配索引的排序序列，从而获得更有效的检索。请注意，IN-list必须只包含常量，或在查询块执行期间保持常量的值，例如外引用。

避免负向查询和%前缀模糊查询

%前缀模糊查询

echo 'SELECT a FROM tbl WHERE a like "%est";' | ./soar
# Query: C15F7A5947239AFF
★ ★ ★ ★ ☆ 80分

##  不建议使用前项通配符查找
* **Item:**  ARG.001
* **Severity:**  L4
* **Content:**  例如“％foo”，查询参数有一个前项通配符的情况无法使用已有索引。

负向查询

echo 'SELECT a FROM mz WHERE a != 0;' | ./soar 
# Query: A43CF84521838793
★ ★ ★ ★ ☆ 95分

##  '!=' 运算符是非标准的
* **Item:**  STA.001
* **Severity:**  L0
* **Content:**  "<>"才是标准SQL中的不等于运算符。

/****那就测试下<>/****

echo 'SELECT * FROM mz WHERE a <> 0;' | ./soar 
# Query: 96BA20EC8863A38D
★ ★ ★ ★ ☆ 95分

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

Count不要使用在可Null的字段上面

echo 'SELECT count(a) FROM mz ' | ./soar 
# Query: 6FF2DE9F51E93F69

★ ★ ★ ☆ ☆ 75分

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

##  不建议使用COUNT(col)或COUNT(常量)
* **Item:**  FUN.005
* **Severity:**  L1
* **Content:**  不要使用COUNT(col)或COUNT(常量)来替代COUNT(\*),COUNT(\*)是SQL92定义的标准统计行数的方法，跟数据无关，跟NULL和非NULL也无关。

echo 'SELECT count(a) FROM mz where id = 1' | ./soar
# Query: C1C66F0877D1A5FB

★ ★ ★ ★ ☆ 90分

##  指定了WHERE条件或非MyISAM引擎时使用COUNT(*)操作性能不佳
* **Item:**  FUN.002
* **Severity:**  L1
* **Content:**  COUNT(\*)的作用是统计表行数，COUNT(COL)的作用是统计指定列非NULL的行数。MyISAM表对于COUNT(\*)统计全表行数进行了特殊的优化，通常情况下非常快。但对于非MyISAM表或指定了某些WHERE条件，COUNT(\*)操作需要扫描大量的行才能获取精确的结果，性能也因此不佳。有时候某些业务场景并不需要完全精确的COUNT值，此时可以用近似值来代替。EXPLAIN出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN并不需要真正去执行查询，所以成本很低。

##  不建议使用COUNT(col)或COUNT(常量)
* **Item:**  FUN.005
* **Severity:**  L1
* **Content:**  不要使用COUNT(col)或COUNT(常量)来替代COUNT(\*),COUNT(\*)是SQL92定义的标准统计行数的方法，跟数据无关，跟NULL和非NULL也无关。

减少Count(*)

echo 'SELECT count(*) FROM mz where id = 1' | ./soar 
# Query: ED0ADC5B79B65731

★ ★ ★ ★ ☆ 100分

使用Union ALL 而不用Union

echo 'SELECT a FROM mz1 where id = 1 UNION   SELECT * FROM mz2 where id = 1 UNION   SELECT * FROM mz3 where id = 1 ;' | ./soar 
# Query: A3C01672F2C16A6E
★ ★ ★ ☆ ☆ 65分

##  如果您不在乎重复的话，建议使用UNION ALL替代UNION
* **Item:**  SUB.002
* **Severity:**  L2
* **Content:**  与去除重复的UNION不同，UNION ALL允许重复元组。如果您不关心重复元组，那么使用UNION ALL将是一个更快的选项。

分解链接，保证高并发

echo 'Select * from tag JOIN tag_post on tag_post.tag_id=tag.id JOIN post on tag_post.post_id=post.id WHERE tag.tag="wasd";' | ./soar 
# Query: 389790AA0277DF13
★ ★ ★ ★ ☆ 100分
 
OK

echo 'SELECT a  FROM mz where a = (select a from mz2 where id = 1 )' | ./soar
# Query: 54F07BDE792CAB12

★ ★ ★ ★ ☆ 80分

##  MySQL对子查询的优化效果不佳
* **Item:**  SUB.001
* **Severity:**  L4
* **Content:**  MySQL将外部查询中的每一行作为依赖子查询执行子查询。 这是导致严重性能问题的常见原因。这可能会在 MySQL 5.6版本中得到改善, 但对于5.1及更早版本, 建议将该类查询分别重写为JOIN或LEFT OUTER JOIN。

Group By 去除排序

echo 'SELECT a FROM tbl where a = 1 group by a' | ./soar 
# Query: 21B94D13243AC243
★ ★ ★ ☆ ☆ 70分

##  请为GROUP BY显示添加ORDER BY条件
* **Item:**  CLA.008
* **Severity:**  L2
* **Content:**  默认MySQL会对'GROUP BY col1, col2, ...'请求按如下顺序排序'ORDER BY col1, col2, ...'。如果GROUP BY语句不指定ORDER BY条件会导致无谓的排序产生，如果不需要排序建议添加'ORDER BY NULL'。

同数据类型的列值比较

echo 'SELECT a  FROM tbl where a = 11 and b = test' | ./soar 
# Query: D288099B2EBD5368
★ ★ ★ ★ ☆ 100分

OK

结论

在这里插入图片描述

对于这款开源产品，对于一个 SQL优化的新手还是非常不错的，给出建议等也非常通俗易懂，但是建议好像是根据关键字形成的固定建议，所以对于复杂的 SQL来说不太友好，还有一个就是对索引建议不明确，还是要靠自己的优化经验来优化。只能给一些我们日常的SQL优化规则上的建议。

Alexyusheng

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
XiaoMi/soar调研/

XiaoMi/soar调研/于佳庆github ：https://github.com/xiaomi/soar安装说明：https://github.com/XiaoMi/soar/blob/master/doc/install.md源码下载：https://GOlang.google.cn/dl/环境准备环境依赖：一般依赖go1.17.1（官网上说是1.0+，但我编译的时候报错，所以我直接将 Go 升级为最高版本）gitGO 下载&&安装&&环境变
复制链接

扫一扫