MySQL--------SQL优化审核工具实战

最新推荐文章于 2023-09-15 10:47:42 发布

ddf1463

最新推荐文章于 2023-09-15 10:47:42 发布

阅读量230

点赞数

文章标签： git 数据库运维

原文链接：http://www.cnblogs.com/xxr1/p/7748187.html

版权

1. 背景

SQLAdvisor是由美团点评公司技术工程部DBA团队（北京）开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系给出索引优化建议。目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。

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

目前常用的SQL优化方式包括但不限于：业务层优化、SQL逻辑优化、索引优化等。其中索引优化通常通过调整索引或新增索引从而达到SQL优化的目的。索引优化往往可以在短时间内产生非常巨大的效果。如果能够将索引优化转化成工具化、标准化的流程，减少人工介入的工作量，无疑会大大提高DBA的工作效率。板面的做法和配料

２. 架构流程图

3. 环境

* os version

 
         [root@SQLAdvisor ~] 
         # cat /etc/redhat-release  
        
         CentOS release 6.8 (Final) 
        
         [root@SQLAdvisor ~] 
         # uname -r 
        
         2.6.32-642.3.1.el6.x86_64 
        
         [root@SQLAdvisor ~] 
         # uname -n 
        
         SQLAdvisor 
        
         [root@SQLAdvisor ~] 
         # getenforce  
        
         Disabled

* mysql version

 
         mysql> show variables like  
         'version' 
         ; 
        
         +---------------+--------+ 
        
         | Variable_name | Value  | 
        
         +---------------+--------+ 
        
         | version       | 5.7.18 | 
        
         +---------------+--------+ 
        
         1 row  
         in  
         set  
         (0.00 sec)

4. 安装SQLAdvisor

* 获取最新代码

 
    
         [root@SQLAdvisor ~] 
         # 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 
         . 
        
 
  

* 安装依赖项

 
         [root@SQLAdvisor ~] 
         # yum -y  install cmake libaio-devel libffi-devel glib2 glib2-devel 
        
         [root@SQLAdvisor ~] 
         # yum -y  install  
        
         [root@SQLAdvisor ~] 
         # yum -y  install Percona-Server-shared-56  
        
         [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 ./ 
        
         [root@SQLAdvisor SQLAdvisor] 
         # make && make install

* 安装SQLAdvisor

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

* SQLAdvisor Info

 
         [root@SQLAdvisor 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. 测试

* 生成测试数据表

 
         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 
        
         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　[查找非索引行]

 
         [root@SQLAdvisor 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　[查找索引行]

 
         [root@SQLAdvisor 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结束!

* 配置文件传参调用

 
         [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 
        
         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结束!

转载于:https://www.cnblogs.com/xxr1/p/7748187.html

ddf1463

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL--------SQL优化审核工具实战

1. 背景 SQLAdvisor是由美团点评公司技术工程部DBA团队（北京）开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系给出索引优化建议。目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。 ...
复制链接

扫一扫

MySQL--------SQL优化审核工具实战

“相关推荐”对你有帮助么？