使用 SOAR 优化 SQL

2022-08-12 20:43:31 602

介绍

soar是由小米开源的SQL优化器和重写器

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

使用

• 基于 ubuntu20.04 lts

按文档安装后

https://github.com/XiaoMi/soar/blob/master/doc/install.md

现有 category 和 goods 表; 除主键外, 没有任何索引

写如下sql

SELECT  
  * 
FROM  
  (
SELECT  
  g.NAME, c.NAME  AS  cats  
FROM  
  goods  g  
  LEFT JOIN  category  c  ON  g. cats_id  = c. id
) g  
WHERE  
  g.NAME  = '充电器'

在soar可执行文件的目录定义配置文件soar.yaml

test-dsn:
    addr:     192.168.101.128:3307
    schema:   localdb
    user:     root
    password: "123456"
    disable:  false
# 是否允许测试环境与线上环境配置相同
allow-online-as-test: true
# 是否清理测试时产生的临时文件
drop-test-temporary: true
# 语法检查小工具
only-syntax-check: false
sampling-statistic-target: 100
sampling: true
# 日志级别，[0:Emergency, 1:Alert, 2:Critical, 3:Error, 4:Warning, 5:Notice, 6:Informational, 7:Debug]
log-level: 7
log-output: ./soar.log
# 优化建议输出格式
report-type: markdown		#可以为markdown和html
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 * FROM (SELECT g.NAME, c.NAME AS cats FROM goods g LEFT JOIN category c ON g.cats_id = c.id ) g WHERE g.NAME = '充电器';" | ./soar  -report-type markdown > result.md

结果以markdown格式输出

以下是result.md文件内容

Query: D4968C97EF14B742

☆ ☆ ☆ ☆ ☆ 15分

SELECT  
  * 
FROM  
  (
SELECT  
  g. NAME, c. NAME  AS  cats  
FROM  
  goods  g  
  LEFT JOIN  category  c  ON  g. cats_id  = c. id
) g  
WHERE  
  g.NAME  = '充电器'

Explain信息

Explain信息解读

SelectType信息解读

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

Type信息解读

• ☠️ ALL: 最坏的情况, 从头到尾全表扫描.
• eq_ref: 除const类型外最好的可能实现的连接类型. 它用在一个索引的所有部分被连接使用并且索引是UNIQUE或PRIMARY KEY, 对于每个索引键, 表中只有一条记录与之匹配. 例:'SELECT * FROM ref_table,tbl WHERE ref_table.key_column=tbl.column;'.

Extra信息解读

• Using where: WHERE条件用于筛选出与下一个表匹配的数据然后返回给客户端. 除非故意做的全表扫描, 否则连接类型是ALL或者是index, 且在Extra列的值中没有Using Where, 则该查询可能是有问题的.

为localdb库的goods表添加索引

• Item: IDX.001
• Severity: L2
• Content: 为列cats_id添加索引,散粒度为: 100.00%;
• Case: ALTER TABLE `localdb`.`goods` add index `idx_cats_id` (`cats_id`) ;

为localdb库的category表添加索引

• Item: IDX.002
• Severity: L2
• Content: 为列NAME添加索引,散粒度为: 100.00%;
• Case: ALTER TABLE `localdb`.`category` add index `idx_NAME` (`NAME`(191)) ;

建议使用AS关键字显示声明一个别名

• Item: ALI.001
• Severity: L0
• Content: 在列或表别名(如"tbl AS alias")中, 明确使用AS关键字比隐含别名(如"tbl alias")更易懂。

参数比较包含隐式转换，无法使用索引

• Item: ARG.003
• Severity: L4
• Content: `goods`.`cats_id` (int) VS `category`.`id` (int unsigned) datatype not match

最外层SELECT未指定WHERE条件

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

不建议使用SELECT * 类型查询

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

MySQL对子查询的优化效果不佳

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

看起来还是很强大的, 尤其是对于经验不足的同学

• mysql
• soar