SQL优化利器 | 基于MySQL的SQL优化工具-SQLAdvisor

数据与人 2020-12-15

4871

简介

在数据库运维过程中，优化SQL是业务团队与DBA团队的日常任务。例行SQL优化，不仅可以提升程序性能，还能够降低线上故障的概率。

常用的SQL优化方式包括但不限于：业务层优化、SQL逻辑优化、索引优化等。其中索引优化通常通过调整索引或新增索引从而达到SQL优化的目的。

索引优化往往可以在短时间内产生非常巨大的效果。如果能够将索引优化转化成工具化、标准化的流程，减少人工介入的工作量，无疑会大大提高DBA的工作效率。

SQLAdvisor优化工具：输入SQL，输出索引优化建议。它基于MySQL原生词法解析，再结合SQL中的where条件以及字段选择度、聚合条件、多表Join关系等最终输出最优的索引优化建议。

是一款高智能化SQL优化工具。

主要功能：输出SQL索引优化建议。

原理分析

1、join 处理

join语法分为两种：join on 和 join using。并且join on 有时会存在where条件中。
分析Join条件首先会得到一个nested_join的table list,通过判断它的join_using_fields 字段是否为空来区分Join on 与Join using。
生成的table list 以二叉树的形式进行存储，以后序遍历的方式对二叉树进行遍历。
生成内部解析树时，right join 会转换成 left Join
join条件会存在当层的叶子节点上，如果左右节点都是叶子节点，会存在右叶子节点
每一个非叶子节点代表一次Join的结果。

2、where 处理

主要是提取sql语句的where条件。where条件中一般由AND 和 OR 连接符进行连接，因为OR 比较难以处理，所以忽略，只处理and连接符。
由于where 条件中可以存在 join条件，因此需要进行区分。
依次获取where条件，当条件中的操作符是like, 如果不是前缀匹配则丢弃这个条件。
根据条件计算字段的区分度按照高低进行倒序排，如果小于30则丢弃。同时使用最左原则将where条件进行有序排列。

3、计算区分度

通过“show table status like”获得表的总行数table_count。
通过计算选择表中已存在的区分度最高的索引best_index，同时Primary key > Unique key > 一般索引
通过计算获取数据采样的起始值offset与采样范围rand_rows：

4、添加备选索引

mysql_sql_parse_index()将条件按照选择度添加到备选索引链表中

5、group 与 order 处理

group 字段与order 字段能否用上索引，需要满足如下条件

涉及到的字段必须来自于同一张表，并且这张表必须是确定下来的驱动表
group by 优于 Order by，两者只能同时存在一个。
order by 字段的排序方向必须完全一致，否则丢弃整个Order by 字段列。
当order by 条件中包含主键时，如果主键字段为 order by 字段列末尾，忽略该主键，否则丢弃整个Order by 字段列

整个索引列排序优先级：等值>(group by | order by )> 非等值
该过程中设计的函数主要有：

mysql_sql_parse_group() 判断group后的字段是否均来自于同一张表
mysql_sql_parse_order() 判断order后的条件是否可以使用
mysql_sql_parse_group_order_add() 将字段依次按照规则添加到备选索引链表中

6、驱动表选择

经过前期的where解析、join解析，已经将SQL中表关联关系存储起来，并且按照一定逻辑将侯选驱动表确定下来
在侯选驱动表中，按照每一张表的侯选索引字段中第一个字段进行计算表中结果集大小
使用explain select * from table where field 来计算表中结果集
结果集小最小的被确为驱动表。
步骤中涉及的函数为：final_table_drived()，在该函数中，调用了函数get_join_table_result_set()来获取每张驱动候选表的行数。

7、添加被驱动表备选索引

通过上述过程，已经选择了驱动表，也通过解析保存了语句中的条件。
由于选定了驱动表，因此需要对被驱动表的索引，根据join条件进行添加。

安装SQLAdvisor

1、检查

[root@SQLAdvisor ~]# getenforce 
Disabled

安装依赖包

[root@SQLAdvisor ~]# yum -y  install cmake libaio-devel libffi-devel glib2 glib2-devel

2、# 配置Percona56 yum源;

$ yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

如果配置不成功采用手动方式，上传percona-release-0.1-3.noarch.rpm rpm包

rpm -ivh percona-release-0.1-3.noarch.rpm

# 安装Percona-Server-shared-56;

$ yum install Percona-Server-shared-56

3、如果上述方式安装不行，可以采用rpm包手动安装。

下载 tar 包

wget https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.25-73.1/binary/redhat/6/x86_64/Percona-Server-5.6.25-73.1-r07b797f-el6-x86_64-bundle.tar

解压

tar -zxvf Percona-Server-5.6.25-73.1-r07b797f-el6-x86_64-bundle.tar

找到对应包，安装即可。

rpm -ivh Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64.rpm

执行安装：

warning: Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY

安装依赖项

[root@SQLAdvisor ~]# yum -y  install cmake libaio-devel libffi-devel glib2 glib2-devel

这些依赖包要装的（如果不能直接yum需要手工安装）

安装cmake前需要安装gcc和gcc-c++依赖包（gcc-4.4.7-23.el6.x86_64.rpm，gcc-c -4.4.7-23.el6.x86_64.rpm）

查看版本，测试是否安装成功。

[root@xjfw3 ~]# cmake --version
cmake version 3.10.0-rc4
CMake suite maintained and supported by Kitware (kitware.com/cmake).

libaio-devel libffi-devel glib2 glib2-devel 依赖包一次安装（el6，el7查看操作系统版本选择安装）

建立软连接

[root@SQLAdvisor ~]# ln -s usr/lib64/libperconaserverclient_r.so.18 usr/lib64/libperconaserverclient_r.so

编译依赖项sqlparser

[root@SQLAdvisor ~]# cd SQLAdvisor/
[root@SQLAdvisor SQLAdvisor]# cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./

如果有报错可以执行下面的语句

cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DIGNORE_AIO_CHECK=value -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./

[root@SQLAdvisor SQLAdvisor]# make && make install

安装SQLAdvisor

[root@SQLAdvisor SQLAdvisor]# cd sqladvisor/
[root@SQLAdvisor sqladvisor]# cmake -DCMAKE_BUILD_TYPE=debug ./
[root@SQLAdvisor sqladvisor]# make




[root@SQLAdvisor sqladvisor]# ./sqladvisor --help

使用案例

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)

生成测试数据

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


生产测试数据
insert into user(name,age, sex) select concat(name, '1'), age+1, sex+1 from user;
insert into user(name,age, sex) select concat(name, '2'), age+2, sex from user;
insert into user(name,age, sex) select concat(name, '3'), age+2, sex from user;
insert into user(name,age, sex) select concat(name, '10'), age+2, sex from user;
insert into user(name,age, sex) select concat(name, '11'), age+4, sex from user;


--执行两次

结果

[mysql@mysql sqladvisor]$ ./sqladvisor -h 192.168.226.131  -P 16063  -u root -p '6yhn^YHN' -d test1 -q "select * from user where name = 'lisea'" -v 1
2020-7-24 07:36:20 35965 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `test1`.`user` where (`name` = 'lisea') 
2020-7-24 07:36:20 35965 [Note] 第2步：开始解析where中的条件:(`name` = 'lisea') 
2020-7-24 07:36:20 35965 [Note] show index from user 
2020-7-24 07:36:20 35965 [Note] show table status like 'user'
2020-7-24 07:36:20 35965 [Note] select count(*) from ( select `name` from `user` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `user` where (`name` = 'lisea')  
2020-7-24 07:36:20 35965 [Note] 第3步：表user的行数:1045485,limit行数:10000,得到where条件中(`name` = 'lisea')的选择度:10000
2020-7-24 07:36:20 35965 [Note] 第4步：开始验证 字段name是不是主键。表名:user
2020-7-24 07:36:20 35965 [Note] show index from user where Key_name = 'PRIMARY' and Column_name ='name' and Seq_in_index = 1
2020-7-24 07:36:20 35965 [Note] 第5步：字段name不是主键。表名:user
2020-7-24 07:36:20 35965 [Note] 第6步：开始验证 字段name是不是主键。表名:user
2020-7-24 07:36:20 35965 [Note] show index from user where Key_name = 'PRIMARY' and Column_name ='name' and Seq_in_index = 1
2020-7-24 07:36:20 35965 [Note] 第7步：字段name不是主键。表名:user
2020-7-24 07:36:20 35965 [Note] 第8步：开始验证表中是否已存在相关索引。表名:user, 字段名:name, 在索引中的位置:1
2020-7-24 07:36:20 35965 [Note] show index from user where Column_name ='name' and Seq_in_index =1
2020-7-24 07:36:20 35965 [Note] 第9步：开始输出表user索引优化建议: 
2020-7-24 07:36:20 35965 [Note] Create_Index_SQL：alter table user add index idx_name(name) 
2020-7-24 07:36:20 35965 [Note] 第10步: SQLAdvisor结束!

配置文件传参调用

[root@SQLAdvisor 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'

[root@SQLAdvisor sqladvisor]# ./sqladvisor -f sql.cnf -v 1

全文完

mysql

文章转载自数据与人，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。