SQLAdvisor美团SQL索引优化建议工具

简介:

前言

Part1:写在最前

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


安装

Part1:构建安装环境

[root@HE3 ~]# yum install git

[root@HE3 ~]# git clone https://github.com/Meituan-Dianping/SQLAdvisor.git

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

[root@HE3 ~]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

[root@HE3 ~]# yum install Percona-Server-shared-56

[root@HE3 lib64]# cd /usr/lib64/

[root@HE3 ~]# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so


Warning:告1

这里该命令一直过不去yum install --enablerepo=Percona56 Percona-Server-shared-56,后直接安装的Percona-Server-shared-56通过

Warning:告2

跟据glib安装的路径,修改SQLAdvisor/sqladvisor/CMakeLists.txt中的两处include_directories针对glib设置的path。本文采用yum安装的git,故glib yum 安装默认不需要修改路径

Warning:告3

编译sqladvisor时依赖perconaserverclient_r, 因此需要安装Percona-Server-shared-56。有可能需要配置软链接例如:1. cd /usr/lib64/ 2. ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so

Warning:告4

有可能需要配置percona56 yum源: yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm


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

[root@HE3 ~]# make && make install

[root@HE3 ~]# cd SQLAdvisor/sqladvisor

[root@HE3 sqladvisor]# cmake -DCMAKE_BUILD_TYPE=debug ./

[root@HE3 sqladvisor]# make

在本路径下生成一个sqladvisor可执行文件,这即是我们想要的。


使用

Part1:对小表进行测试

[root@HE3 sqladvisor]# ./sqladvisor -h 127.0.0.1 -P 3306 -u root -p "MANAGER" -d helei1 -q "select * from helei1" -v 1

2017-03-21 20:37:53 8581 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `helei1`.`helei1` 

2017-03-21 20:37:53 8581 [Note] 第2步:表helei1 的SQL太逆天,没有优化建议 

2017-03-21 20:37:53 8581 [Note] 第3步: SQLAdvisor结束! 



Part2:对大表进行测试(有索引)

这里我们对表helei进行分析,改表在c1列上存在索引

[root@HE3 sqladvisor]# ./sqladvisor -h 127.0.0.1 -P 3306 -u root -p "MANAGER" -d helei1 -q "explain select * from helei where c1=88501;" -v 1

2017-03-21 21:19:23 8624 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `helei1`.`helei` where (`c1` = 88501) 

2017-03-21 21:19:23 8624 [Note] 第2步:开始解析where中的条件:(`c1` = 88501) 

2017-03-21 21:19:23 8624 [Note] show index from helei 

2017-03-21 21:19:23 8624 [Note] show table status like 'helei' 

2017-03-21 21:19:23 8624 [Note] select count(*) from ( select `c1` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c1` = 88501)  

2017-03-21 21:19:23 8624 [Note] 第3步:表helei的行数:200380,limit行数:10000,得到where条件中(`c1` = 88501)的选择度:10000 

2017-03-21 21:19:23 8624 [Note] 第4步:开始验证 字段c1是不是主键。表名:helei 

2017-03-21 21:19:23 8624 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1 

2017-03-21 21:19:23 8624 [Note] 第5步:字段c1不是主键。表名:helei 

2017-03-21 21:19:23 8624 [Note] 第6步:开始验证 字段c1是不是主键。表名:helei 

2017-03-21 21:19:23 8624 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1 

2017-03-21 21:19:23 8624 [Note] 第7步:字段c1不是主键。表名:helei 

2017-03-21 21:19:23 8624 [Note] 第8步:开始验证表中是否已存在相关索引。表名:helei, 字段名:c1, 在索引中的位置:1 

2017-03-21 21:19:23 8624 [Note] show index from helei where Column_name ='c1' and Seq_in_index =1 

2017-03-21 21:19:23 8624 [Note] 第9步:索引(c1)已存在 

2017-03-21 21:19:23 8624 [Note] 第10步: SQLAdvisor结束! 


可以看到,最后给出了该条SQL已经拥有有效索引的建议


Part2:对大表进行测试(无索引)

这里我们对表helei进行分析,改表在c5列上没有索引

[root@HE3 sqladvisor]# ./sqladvisor -h 127.0.0.1 -P 3306 -u root -p "MANAGER" -d helei1 -q "explain select * from helei where c5=74685;" -v 1

2017-03-21 21:20:53 8628 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `helei1`.`helei` where (`c5` = 74685) 

2017-03-21 21:20:53 8628 [Note] 第2步:开始解析where中的条件:(`c5` = 74685) 

2017-03-21 21:20:53 8628 [Note] show index from helei 

2017-03-21 21:20:53 8628 [Note] show table status like 'helei' 

2017-03-21 21:20:53 8628 [Note] select count(*) from ( select `c5` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c5` = 74685)  

2017-03-21 21:20:53 8628 [Note] 第3步:表helei的行数:201361,limit行数:10000,得到where条件中(`c5` = 74685)的选择度:10000 

2017-03-21 21:20:53 8628 [Note] 第4步:开始验证 字段c5是不是主键。表名:helei 

2017-03-21 21:20:53 8628 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1 

2017-03-21 21:20:53 8628 [Note] 第5步:字段c5不是主键。表名:helei 

2017-03-21 21:20:53 8628 [Note] 第6步:开始验证 字段c5是不是主键。表名:helei 

2017-03-21 21:20:53 8628 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1 

2017-03-21 21:20:53 8628 [Note] 第7步:字段c5不是主键。表名:helei 

2017-03-21 21:20:53 8628 [Note] 第8步:开始验证表中是否已存在相关索引。表名:helei, 字段名:c5, 在索引中的位置:1 

2017-03-21 21:20:53 8628 [Note] show index from helei where Column_name ='c5' and Seq_in_index =1 

2017-03-21 21:20:53 8628 [Note] 第9步:开始输出表helei索引优化建议: 

2017-03-21 21:20:53 8628 [Note] Create_Index_SQL:alter table helei add index idx_c5(c5) 

2017-03-21 21:20:53 8628 [Note] 第10步: SQLAdvisor结束!


可以看到,最后给出了创建索引的建议



Part3:多条SQL同时分析

可以创建任意名的参数文件,这里叫helei.cnf,输入常规的数据库连接信息和SQL,SQL之间用分号隔开。

[root@HE3 sqladvisor]# cat helei.cnf 

[sqladvisor]

username=root

password=MANAGER

host=127.0.0.1

port=3306

dbname=helei1

sqls=select * from helei where c1=88501;select * from helei where c5=74685;


这里使用-f命令来载入helei.cnf中的配置

[root@HE3 sqladvisor]# ./sqladvisor -f helei.cnf -v 1

2017-03-21 21:27:35 8640 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `helei1`.`helei` where (`c1` = 88501) 

2017-03-21 21:27:35 8640 [Note] 第2步:开始解析where中的条件:(`c1` = 88501) 

2017-03-21 21:27:35 8640 [Note] show index from helei 

2017-03-21 21:27:35 8640 [Note] show table status like 'helei' 

2017-03-21 21:27:35 8640 [Note] select count(*) from ( select `c1` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c1` = 88501)  

2017-03-21 21:27:35 8640 [Note] 第3步:表helei的行数:200674,limit行数:10000,得到where条件中(`c1` = 88501)的选择度:10000 

2017-03-21 21:27:35 8640 [Note] 第4步:开始验证 字段c1是不是主键。表名:helei 

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1 

2017-03-21 21:27:35 8640 [Note] 第5步:字段c1不是主键。表名:helei 

2017-03-21 21:27:35 8640 [Note] 第6步:开始验证 字段c1是不是主键。表名:helei 

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c1' and Seq_in_index = 1 

2017-03-21 21:27:35 8640 [Note] 第7步:字段c1不是主键。表名:helei 

2017-03-21 21:27:35 8640 [Note] 第8步:开始验证表中是否已存在相关索引。表名:helei, 字段名:c1, 在索引中的位置:1 

2017-03-21 21:27:35 8640 [Note] show index from helei where Column_name ='c1' and Seq_in_index =1 

2017-03-21 21:27:35 8640 [Note] 第9步:索引(c1)已存在 

2017-03-21 21:27:35 8640 [Note] 第10步: SQLAdvisor结束! 

2017-03-21 21:27:35 8640 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `helei1`.`helei` where (`c5` = 74685) 

2017-03-21 21:27:35 8640 [Note] 第2步:开始解析where中的条件:(`c5` = 74685) 

2017-03-21 21:27:35 8640 [Note] show index from helei 

2017-03-21 21:27:35 8640 [Note] show table status like 'helei' 

2017-03-21 21:27:35 8640 [Note] select count(*) from ( select `c5` from `helei` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `helei` where (`c5` = 74685)  

2017-03-21 21:27:35 8640 [Note] 第3步:表helei的行数:201067,limit行数:10000,得到where条件中(`c5` = 74685)的选择度:10000 

2017-03-21 21:27:35 8640 [Note] 第4步:开始验证 字段c5是不是主键。表名:helei 

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1 

2017-03-21 21:27:35 8640 [Note] 第5步:字段c5不是主键。表名:helei 

2017-03-21 21:27:35 8640 [Note] 第6步:开始验证 字段c5是不是主键。表名:helei 

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name ='c5' and Seq_in_index = 1 

2017-03-21 21:27:35 8640 [Note] 第7步:字段c5不是主键。表名:helei 

2017-03-21 21:27:35 8640 [Note] 第8步:开始验证表中是否已存在相关索引。表名:helei, 字段名:c5, 在索引中的位置:1 

2017-03-21 21:27:35 8640 [Note] show index from helei where Column_name ='c5' and Seq_in_index =1 

2017-03-21 21:27:35 8640 [Note] 第9步:开始输出表helei索引优化建议: 

2017-03-21 21:27:35 8640 [Note] Create_Index_SQL:alter table helei add index idx_c5(c5) 

2017-03-21 21:27:35 8640 [Note] 第10步: SQLAdvisor结束! 


可以看到这里对helei.cnf中的两个SQL进行了一次性建议输出。


Warning:告5

  • SQL中的子查询、or条件、使用函数的条件 会忽略不处理。

  • 命令行传入sql参数时,注意sql中的双引号、反引号 都需要用\转义。建议使用配置文件形式调用


——总结——

可以看出,SQLAdvisor对查询SQL给出了索引创建的优化和建议,还是不错的,后续会进一步进行深度测试。由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。



参考:https://github.com/Meituan-Dianping/SQLAdvisor/blob/master/doc/QUICK_START.md






 本文转自 dbapower 51CTO博客,原文链接:http://blog.51cto.com/suifu/1909169,如需转载请自行联系原作者


相关文章
|
2天前
|
SQL 存储 关系型数据库
慢查询优化方案-SQL篇【JavaPub版】
慢查询优化方案-SQL篇【JavaPub版】
3 0
|
3天前
|
SQL 关系型数据库 MySQL
mysqldiff - Golang 针对 MySQL 数据库表结构的差异 SQL 工具
Golang 针对 MySQL 数据库表结构的差异 SQL 工具。https://github.com/camry/mysqldiff
34 7
|
8天前
|
SQL 存储 关系型数据库
PolarDB产品使用合集之有的sql里面有自定义存储函数 如果想走列存有什么优化建议吗
PolarDB是阿里云推出的一种云原生数据库服务,专为云设计,提供兼容MySQL、PostgreSQL的高性能、低成本、弹性可扩展的数据库解决方案,可以有效地管理和优化PolarDB实例,确保数据库服务的稳定、高效运行。以下是使用PolarDB产品的一些建议和最佳实践合集。
|
9天前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL优化(3/3)-limit 优化、count 优化、update 优化、SQL优化 小结
MySQL数据库——SQL优化(3/3)-limit 优化、count 优化、update 优化、SQL优化 小结
14 0
|
9天前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL优化(2/3)-order by 优化、group by 优化
MySQL数据库——SQL优化(2/3)-order by 优化、group by 优化
12 0
|
9天前
|
存储 SQL 关系型数据库
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
20 1
|
9天前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
11 0
|
9天前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(4)-SQL性能分析-profile详情、explain(profile查看指令,explain执行计划中各个字段的含义)
MySQL数据库——索引(4)-SQL性能分析-profile详情、explain(profile查看指令,explain执行计划中各个字段的含义)
11 2
|
9天前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(3)-索引语法(创建索引、查看索引、删除索引、案例演示),SQL性能分析(SQL执行频率,慢查询日志)
MySQL数据库——索引(3)-索引语法(创建索引、查看索引、删除索引、案例演示),SQL性能分析(SQL执行频率,慢查询日志)
15 2
|
9天前
|
SQL 关系型数据库 MySQL
MySQL数据库——图形化界面工具(DataGrip),SQL(2)-DML(插入、修改和删除数据)
MySQL数据库——图形化界面工具(DataGrip),SQL(2)-DML(插入、修改和删除数据)
18 1