使用 soar 优化 MySQL

MySQL MySQL, soar 评论

介绍

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

SOAR

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

功能特点

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

终端运行

下载二进制文件

在 https://github.com/XiaoMi/soar/releases 中下载合适的二进制文件

运行效果

test.sql

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

soar -query test.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
# Query: F5422F7F42F440DF

☆ ☆ ☆ ☆ ☆ 0分

sql
CREATE  TABLE  `users` (
  `id` int( 10) UNSIGNED  NOT  NULL, `name` varchar( 255) COLLATE  utf8mb4_unicode_ci  NOT  NULL, `email` varchar( 255) COLLATE  utf8mb4_unicode_ci  NOT  NULL, `email_verified_at` TIMESTAMP  NULL  DEFAULT  NULL, `password` varchar( 255) COLLATE  utf8mb4_unicode_ci  NOT  NULL, `remember_token` varchar( 100) COLLATE  utf8mb4_unicode_ci  DEFAULT  NULL, `created_at` TIMESTAMP  NULL  DEFAULT  NULL, `updated_at` TIMESTAMP  NULL  DEFAULT  NULL
) ENGINE= InnoDB  DEFAULT  CHARSET= utf8mb4  COLLATE= utf8mb4_unicode_ci


##  建议为表添加注释

* **Item:**  CLA.011

* **Severity:**  L1

* **Content:**  为表添加注释能够使得表的意义更明确，从而为日后的维护带来极大的便利。

##  请为列添加默认值

* **Item:**  COL.004

* **Severity:**  L1

* **Content:**  请为列添加默认值，如果是 ALTER 操作，请不要忘记将原字段的默认值写上。字段无默认值，当表较大时无法在线变更表结构。

##  列未添加注释

* **Item:**  COL.005

* **Severity:**  L1

* **Content:**  建议对表中每个列添加注释，来明确每个列在表中的含义及作用。

##  为列指定了字符集

* **Item:**  COL.014

* **Severity:**  L5

* **Content:**  建议列与表使用同一个字符集，不要单独指定列的字符集。

##  未指定主键或主键非 bigint

* **Item:**  KEY.007

* **Severity:**  L4

* **Content:**  未指定主键或主键非 bigint，建议将主键设置为 bigint unsigned。

##  不建议使用复数做列名或表名

* **Item:**  KWR.003

* **Severity:**  L1

* **Content:**  表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。

##  不使用明文存储密码

* **Item:**  SEC.002

* **Severity:**  L0

* **Content:**  使用明文存储密码或者使用明文在网络上传递密码都是不安全的。如果攻击者能够截获您用来插入密码的SQL语句，他们就能直接读到密码。另外，将用户输入的字符串以明文的形式插入到纯SQL语句中，也会让攻击者发现它。如果您能够读取密码，黑客也可以。解决方案是使用单向哈希函数对原始密码进行加密编码。哈希是指将输入字符串转化成另一个新的、不可识别的字符串的函数。对密码加密表达式加点随机串来防御“字典攻击”。不要将明文密码输入到SQL查询语句中。在应用程序代码中计算哈希串，只在SQL查询中使用哈希串。

web 可视化

为了更好地进行使用，社区出现了 web 可视化工具，如 soar-web https://github.com/xiyangxixian/soar-web

由于这个源之前存在的一个中文问题#issue71，我 fork 了项目，并且制作了一个 docker

docker run -d -p 5077:5077 rovast/soar-web，在本地访问 http://127.0.0.1:5077 即可。

新增启发规则

如果我们需要限定查询的规则中必须包含 last_updated_at 字段，可对 soar 进行如下修改后编译

soar/advisor/rules.go 增加一条规则

1
2
3
4
5
6
7
8
"MY_KEY.001": {
    Item:     "MY_KEY.001",
    Severity: "L4",
    Summary:  "缺少数据库必须字段 last_updated_at",
    Content:  "数据库必须字段 last_updated_at",
    Case:     "CREATE TABLE tbl （`last_updated_at` DATETIME COMMENT '最后更新时间';",
    Func:     (*Query4Audit).RuleRequiredFields,
},

soar/advisor/heuristic.go

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
// MY_KEY.001 数据库必须字段
func (q *Query4Audit) RuleRequiredFields() Rule {
	var rule = q.RuleOK()
	switch s := q.Stmt.(type) {
	case *sqlparser.DDL:
		if s.Action == "create" {
			if s.TableSpec == nil {
				return rule
			}

			// 必须包含 last_updated_at
			hitCount := 0
			for _, col := range s.TableSpec.Columns {
				switch col.Name.String() {
				case "last_updated_at":
					// 检测字段类型
					if col.Type.Type != "datetime" && col.Type.Type != "timestamp" {
						return HeuristicRules["MY_KEY.001"]
					}

					hitCount++
					break
			}

			if hitCount != 1 {
				rule = HeuristicRules["MY_KEY.001"]
			}
		}
	}
	return rule
}

基本思路就是新增 rule, soar 每次执行的时候会遍历 rule 去执行响应的函数检测。编译后可以看看自己的规则是否生效了。

启发规则汇总

可以参考另外一篇文章 《soar 启发规则汇总 && 常见 MySQL 优化案例》

rovast但行好事

该用户很懒，没有介绍自己。