MySQL--------SQL优化审核工具实战
1. 背景
SQLAdvisor是由美团点评公司技术工程部DBA团队(北京)开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析,结合分析SQL中的where条件、聚合条件、多表Join关系 给出索引优化建议。目前SQLAdvisor在美团点评内部广泛应用,公司内部对SQLAdvisor的开发全面转到github上,开源和内部使用保持一致。
在数据库运维过程中,优化SQL是业务团队与DBA团队的日常任务。例行SQL优化,不仅可以提升程序性能,还能够降低线上故障的概率。
目前常用的SQL优化方式包括但不限于:业务层优化、SQL逻辑优化、索引优化等。其中索引优化通常通过调整索引或新增索引从而达到SQL优化的目的。索引优化往往可以在短时间内产生非常巨大的效果。如果能够将索引优化转化成工具化、标准化的流程,减少人工介入的工作量,无疑会大大提高DBA的工作效率。
2. 架构流程图
3. 环境
* os version
1
2
3
4
5
6
7
8
9
10
11
|
[[email protected] ~] # cat /etc/redhat-release
CentOS release 6.8 (Final) [[email protected] ~] # uname -r
2.6.32-642.3.1.el6.x86_64 [[email protected] ~] # uname -n
SQLAdvisor [[email protected] ~] # getenforce
Disabled |
* mysql version
1
2
3
4
5
6
7
|
mysql> show variables like 'version' ;
+---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 5.7.18 | +---------------+--------+ 1 row in set (0.00 sec)
|
4. 安装SQLAdvisor
* 获取最新代码
1
2
3
4
5
6
|
[[email protected] ~] # git clone https://github.com/Meituan-Dianping/SQLAdvisor.git
Initialized empty Git repository in /root/SQLAdvisor/ .git/
remote: Counting objects: 1460, done .
remote: Total 1460 (delta 0), reused 0 (delta 0), pack-reused 1460 Receiving objects: 100% (1460 /1460 ), 19.92 MiB | 209 KiB /s , done .
Resolving deltas: 100% (368 /368 ), done .
|
* 安装依赖项
1
2
3
4
5
6
7
|
[[email protected] ~] # yum -y install cmake libaio-devel libffi-devel glib2 glib2-devel
[[email protected] ~] # yum -y install
[[email protected] ~] # yum -y install Percona-Server-shared-56
[[email protected] ~] # ln -s /usr/lib64/libperconaserverclient_r.so.18 /usr/lib64/libperconaserverclient_r.so
|
* 编译依赖项sqlparser
1
2
3
4
5
|
[[email protected] ~] # cd SQLAdvisor/
[[email protected] SQLAdvisor] # cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
[[email protected] SQLAdvisor] # make && make install
|
* 安装SQLAdvisor
1
2
3
4
5
|
[[email protected] SQLAdvisor] # cd sqladvisor/
[[email protected] sqladvisor] # cmake -DCMAKE_BUILD_TYPE=debug ./
[[email protected] sqladvisor] # make
|
* SQLAdvisor Info
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
[[email protected] sqladvisor] # ./sqladvisor --help
Usage: sqladvisor [OPTION...] sqladvisor
SQL Advisor Summary Help Options: -?, --help Show help options
Application Options: -f, --defaults- file sqls file
-u, --username username
-p, --password password
-P, --port port
-h, --host host
-d, --dbname database name
-q, --sqls sqls
- v , --verbose 1:output logs 0:output nothing
|
5. 测试
* 生成测试数据表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> create database test1 character set utf8mb4;
Query OK, 1 row affected (0.00 sec) mysql> create table user( -> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(64) NOT NULL,
-> age int,
-> sex int
-> )ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.13 sec) mysql> desc user; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(64) | NO | | NULL | | | age | int(11) | YES | | NULL | | | sex | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
|
* 生成测试数据
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
|
mysql> insert into user(name,age, sex) select 'lisea' , 25, 1;
Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into user(name,age, sex) select concat(name, '1' ), age+1, sex+1 from user;
Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into user(name,age, sex) select concat(name, '2' ), age+2, sex from user;
Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into user(name,age, sex) select concat(name, '3' ), age+2, sex from user;
Query OK, 4 rows affected (0.18 sec) Records: 4 Duplicates: 0 Warnings: 0 . . . . . . mysql> insert into user(name,age, sex) select concat(name, '10' ), age+2, sex from user;
Query OK, 512 rows affected (0.24 sec) Records: 512 Duplicates: 0 Warnings: 0 mysql> insert into user(name,age, sex) select concat(name, '11' ), age+4, sex from user;
Query OK, 1024 rows affected (0.79 sec) Records: 1024 Duplicates: 0 Warnings: 0 mysql> select count(1) from user;
+----------+ | count(1) | +----------+ | 2048 | +----------+ 1 row in set (0.01 sec)
|
* 命令行传参调用测试SQLAdvisor [查找非索引行]
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
|
[[email protected] sqladvisor] # ./sqladvisor -h 127.0.0.1 -P 3306 -u root -p '123' -d test1 -q "select * from user where name = 'lisea'" -v 1
2017-10-27 05:35:49 34059 [Note] 第1步: 对SQL解析优化之后得到的SQL: select `*` AS `*` from `test1`.`user` where (`name` = 'lisea' )
2017-10-27 05:35:49 34059 [Note] 第2步:开始解析where中的条件:(`name` = 'lisea' )
2017-10-27 05:35:49 34059 [Note] show index from user 2017-10-27 05:35:49 34059 [Note] show table status like 'user' 2017-10-27 05:35:49 34059 [Note] select count(*) from ( select `name` from `user` FORCE INDEX( PRIMARY ) order by id DESC limit 1024) `user` where (`name` = 'lisea' )
2017-10-27 05:35:49 34059 [Note] 第3步:表user的行数:2048,limit行数:1024,得到where条件中(`name` = 'lisea' )的选择度:1024
2017-10-27 05:35:49 34059 [Note] 第4步:开始验证 字段name是不是主键。表名:user 2017-10-27 05:35:49 34059 [Note] show index from user where Key_name = 'PRIMARY' and Column_name = 'name' and Seq_in_index = 1
2017-10-27 05:35:49 34059 [Note] 第5步:字段name不是主键。表名:user 2017-10-27 05:35:49 34059 [Note] 第6步:开始验证 字段name是不是主键。表名:user 2017-10-27 05:35:49 34059 [Note] show index from user where Key_name = 'PRIMARY' and Column_name = 'name' and Seq_in_index = 1
2017-10-27 05:35:49 34059 [Note] 第7步:字段name不是主键。表名:user 2017-10-27 05:35:49 34059 [Note] 第8步:开始验证表中是否已存在相关索引。表名:user, 字段名:name, 在索引中的位置:1 2017-10-27 05:35:49 34059 [Note] show index from user where Column_name = 'name' and Seq_in_index =1
2017-10-27 05:35:49 34059 [Note] 第9步:开始输出表user索引优化建议: 2017-10-27 05:35:49 34059 [Note] Create_Index_SQL:alter table user add index idx_name(name) 2017-10-27 05:35:49 34059 [Note] 第10步: SQLAdvisor结束! |
* 命令行传参调用测试SQLAdvisor [查找索引行]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[[email protected] sqladvisor] # ./sqladvisor -h 127.0.0.1 -P 3306 -u root -p '123' -d test1 -q "select * from user where id = 1" -v 1
2017-10-27 05:36:46 34062 [Note] 第1步: 对SQL解析优化之后得到的SQL: select `*` AS `*` from `test1`.`user` where (` id ` = 1)
2017-10-27 05:36:46 34062 [Note] 第2步:开始解析where中的条件:(` id ` = 1)
2017-10-27 05:36:46 34062 [Note] show index from user 2017-10-27 05:36:46 34062 [Note] show table status like 'user' 2017-10-27 05:36:46 34062 [Note] select count(*) from ( select ` id ` from `user` FORCE INDEX( PRIMARY ) order by id DESC limit 1024) `user` where (` id ` = 1)
2017-10-27 05:36:46 34062 [Note] 第3步:表user的行数:2048,limit行数:1024,得到where条件中(` id ` = 1)的选择度:1024
2017-10-27 05:36:46 34062 [Note] 第4步:开始验证 字段 id 是不是主键。表名:user
2017-10-27 05:36:46 34062 [Note] show index from user where Key_name = 'PRIMARY' and Column_name = 'id' and Seq_in_index = 1
2017-10-27 05:36:46 34062 [Note] 第5步:字段 id 是主键。表名:user
2017-10-27 05:36:46 34062 [Note] 第6步:表user 经过运算得到的索引列首列是主键,直接放弃,没有优化建议 2017-10-27 05:36:46 34062 [Note] 第7步: SQLAdvisor结束! |
* 配置文件传参调用
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
|
[[email protected] sqladvisor] # cat sql.cnf
[sqladvisor] username=root password=123 host=127.0.0.1 port=3306 dbname=test1 sqls= select * from user where name = 'lisea'
[[email protected] sqladvisor] # ./sqladvisor -f sql.cnf -v 1
2017-10-27 05:40:14 34070 [Note] 第1步: 对SQL解析优化之后得到的SQL: select `*` AS `*` from `test1`.`user` where (`name` = 'lisea' )
2017-10-27 05:40:14 34070 [Note] 第2步:开始解析where中的条件:(`name` = 'lisea' )
2017-10-27 05:40:14 34070 [Note] show index from user 2017-10-27 05:40:14 34070 [Note] show table status like 'user' 2017-10-27 05:40:14 34070 [Note] select count(*) from ( select `name` from `user` FORCE INDEX( PRIMARY ) order by id DESC limit 1024) `user` where (`name` = 'lisea' )
2017-10-27 05:40:14 34070 [Note] 第3步:表user的行数:2048,limit行数:1024,得到where条件中(`name` = 'lisea' )的选择度:1024
2017-10-27 05:40:14 34070 [Note] 第4步:开始验证 字段name是不是主键。表名:user 2017-10-27 05:40:14 34070 [Note] show index from user where Key_name = 'PRIMARY' and Column_name = 'name' and Seq_in_index = 1
2017-10-27 05:40:14 34070 [Note] 第5步:字段name不是主键。表名:user 2017-10-27 05:40:14 34070 [Note] 第6步:开始验证 字段name是不是主键。表名:user 2017-10-27 05:40:14 34070 [Note] show index from user where Key_name = 'PRIMARY' and Column_name = 'name' and Seq_in_index = 1
2017-10-27 05:40:14 34070 [Note] 第7步:字段name不是主键。表名:user 2017-10-27 05:40:14 34070 [Note] 第8步:开始验证表中是否已存在相关索引。表名:user, 字段名:name, 在索引中的位置:1 2017-10-27 05:40:14 34070 [Note] show index from user where Column_name = 'name' and Seq_in_index =1
2017-10-27 05:40:14 34070 [Note] 第9步:开始输出表user索引优化建议: 2017-10-27 05:40:14 34070 [Note] Create_Index_SQL:alter table user add index idx_name(name) 2017-10-27 05:40:14 34070 [Note] 第10步: SQLAdvisor结束! |