SQLAdvisor：MySQL SQL优化工具

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

SQLAdvisor项目地址 

一、安装

1、安装依赖项目

# yum install  cmake libaio-devel libffi-devel glib2 glib2-devel 

SQLAdvisor需要Percona-Server-shared-56安装包

# wget 
# rpm -ivh Percona-Server-shared-56-5.6.35-rel81.0.el5.x86_64.rpm 

编译sqladvisor时依赖perconaserverclient_r，所以上面安装了Percona-Server-shared-56安装包。不过需要特别配置软链接，设置如下：

# cd /usr/lib64
# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so 

2、下载SQLAdvisior安装包

# wget  

3、解压

# unzip SQLAdvisor-master.zip 

4、编译依赖项sqlparser

# cd ./SQLAdvisor-master
# cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
# make && make install 

注意

• DCMAKE_INSTALL_PREFIX为sqlparser库文件和头文件的安装目录，其中lib目录包含库文件libsqlparser.so，include目录包含所需的所有头文件。
• DCMAKE_INSTALL_PREFIX值尽量不要修改，后面安装依赖这个目录。

5、安装SQLAdvisor源码

# cd ./SQLAdvisor-master/sqladvisor/
# cmake -DCMAKE_BUILD_TYPE=debug ./
# make 

之后在当前目录下生成可执行文件sqladvisor。 
为了方便直接执行该命令，你可以在环境变量加上命令的路径。我这里是拷贝到了MySQL命令路径/usr/local/mysql/bin/下。

二、SQLAdvisor的使用

1、查看帮助

# 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, --dbnamedatabase name
  -q, --sqls  sqls
  -v, --verbose   1:output logs 0:output nothing 

2、命令行直接执行sqladvisor

2.1 单表查询

该SQL无需优化，所以sqladvisor没有给出SQL建议

# sqladvisor -h 127.0.0.1 -u root -p xxx  -P 3306 -d zabbix -q "SELECT itemid,round(500* MOD( CAST(clock AS UNSIGNED) +19571,21600)/(21600),0) AS i,COUNT(*) AS count,AVG(value) AS avg,MIN(value) as min,MAX(value) AS max,MAX(clock) AS clock FROM history  WHERE itemid=27520 " -v 1

2017-03-29 16:22:36 7883 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `itemid` AS `itemid`,round(((500 * ((cast(`clock` as unsigned) + 19571) % 21600)) / 21600),0) AS `i`,count(0) AS `count`,avg(`value`) AS `avg`,min(`value`) AS `min`,max(`value`) AS `max`,max(`clock`) AS `clock` from `zabbix`.`history` where (`itemid` = 27520) 

2017-03-29 16:22:36 7883 [Note] 第2步：开始解析where中的条件:(`itemid` = 27520) 

2017-03-29 16:22:36 7883 [Note] show index from history 

2017-03-29 16:22:38 7883 [Note] show table status like 'history' 

2017-03-29 16:22:39 7883 [Note] select count(*) from ( select `itemid` from `history` FORCE INDEX( history_1 ) order by clock DESC limit 10000) `history` where (`itemid` = 27520)  

2017-03-29 16:26:42 7883 [Note] 第3步：表history的行数:82491484,limit行数:10000,得到where条件中(`itemid` = 27520)的选择度:1111 

2017-03-29 16:26:42 7883 [Note] 第4步：开始验证 字段itemid是不是主键。表名:history 

2017-03-29 16:26:42 7883 [Note] show index from history where Key_name = 'PRIMARY' and Column_name ='itemid' and Seq_in_index = 1 

2017-03-29 16:26:42 7883 [Note] 第5步：字段itemid不是主键。表名:history 

2017-03-29 16:26:42 7883 [Note] 第6步：开始验证 字段itemid是不是主键。表名:history 

2017-03-29 16:26:42 7883 [Note] show index from history where Key_name = 'PRIMARY' and Column_name ='itemid' and Seq_in_index = 1 

2017-03-29 16:26:43 7883 [Note] 第7步：字段itemid不是主键。表名:history 

2017-03-29 16:26:43 7883 [Note] 第8步：开始验证表中是否已存在相关索引。表名:history, 字段名:itemid, 在索引中的位置:1 

2017-03-29 16:26:43 7883 [Note] show index from history where Column_name ='itemid' and Seq_in_index =1 

2017-03-29 16:26:43 7883 [Note] 第9步：索引(itemid)已存在 

2017-03-29 16:26:43 7883 [Note] 第10步: SQLAdvisor结束! 

2.2 多表关联查询

注意最后给出了SQL优化建议

# sqladvisor -h 10.5.5.46 -u root -p xxxx  -P 3306 -d 17ugo -q "SELECT p.goods_id AS goods_id,p.goods_sn AS goods_sn,p.start_time AS start_time, p.end_time AS end_time,g.is_limit*1 AS is_limit,g.promote_start_date,g.promote_end_date,p.price AS price,g.is_on_sale*1 AS is_on_sale FROM 17ugo.ecs_ugo_goods g,17ugo.ecs_ugo_biku_goods_price p  WHERE g.iv_code = p.goods_sn AND g.is_promote = 0 AND p.is_on = 1 AND p.start_time<1490745300  AND p.end_time>1490745300 ORDER BY p.add_time DESC" -v 1

2017-03-29 16:30:36 11861 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `p`.`goods_id` AS `goods_id`,`p`.`goods_sn` AS `goods_sn`,`p`.`start_time` AS `start_time`,`p`.`end_time` AS `end_time`,(`g`.`is_limit` * 1) AS `is_limit`,`g`.`promote_start_date` AS `promote_start_date`,`g`.`promote_end_date` AS `promote_end_date`,`p`.`price` AS `price`,(`g`.`is_on_sale` * 1) AS `is_on_sale` from `17ugo`.`ecs_ugo_goods` `g` join `17ugo`.`ecs_ugo_biku_goods_price` `p` where ((`g`.`iv_code` = `p`.`goods_sn`) and (`g`.`is_promote` = 0) and (`p`.`is_on` = 1) and (`p`.`start_time` < 1490745300) and (`p`.`end_time` > 1490745300)) order by `p`.`add_time` desc 

2017-03-29 16:30:36 11861 [Note] 第2步：开始解析join on条件:g.iv_code=p.goods_sn 

2017-03-29 16:30:36 11861 [Note] 第3步：开始解析where中的条件:(`g`.`is_promote` = 0) 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_goods 

2017-03-29 16:30:36 11861 [Note] show table status like 'ecs_ugo_goods' 

2017-03-29 16:30:36 11861 [Note] select count(*) from ( select `is_promote` from `ecs_ugo_goods` FORCE INDEX( PRIMARY ) order by goods_id DESC limit 10000) `g` where (`g`.`is_promote` = 0)  

2017-03-29 16:30:36 11861 [Note] 第4步：表ecs_ugo_goods的行数:75745,limit行数:10000,得到where条件中(`g`.`is_promote` = 0)的选择度:1 

2017-03-29 16:30:36 11861 [Note] 第5步：开始解析where中的条件:(`p`.`is_on` = 1) 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] show table status like 'ecs_ugo_biku_goods_price' 

2017-03-29 16:30:36 11861 [Note] select count(*) from ( select `is_on` from `ecs_ugo_biku_goods_price` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `p` where (`p`.`is_on` = 1)  

2017-03-29 16:30:36 11861 [Note] 第6步：表ecs_ugo_biku_goods_price的行数:33239,limit行数:10000,得到where条件中(`p`.`is_on` = 1)的选择度:1 

2017-03-29 16:30:36 11861 [Note] 第7步：开始解析where中的条件:(`p`.`start_time` < 1490745300) 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] show table status like 'ecs_ugo_biku_goods_price' 

2017-03-29 16:30:36 11861 [Note] select count(*) from ( select `start_time` from `ecs_ugo_biku_goods_price` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `p` where (`p`.`start_time` < 1490745300)  

2017-03-29 16:30:36 11861 [Note] 第8步：表ecs_ugo_biku_goods_price的行数:33239,limit行数:10000,得到where条件中(`p`.`start_time` < 1490745300)的选择度:1 

2017-03-29 16:30:36 11861 [Note] 第9步：开始解析where中的条件:(`p`.`end_time` > 1490745300) 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] show table status like 'ecs_ugo_biku_goods_price' 

2017-03-29 16:30:36 11861 [Note] select count(*) from ( select `end_time` from `ecs_ugo_biku_goods_price` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `p` where (`p`.`end_time` > 1490745300)  

2017-03-29 16:30:36 11861 [Note] 第10步：表ecs_ugo_biku_goods_price的行数:33239,limit行数:10000,得到where条件中(`p`.`end_time` > 1490745300)的选择度:10 

2017-03-29 16:30:36 11861 [Note] 第11步：开始选择驱动表,一共有2个候选驱动表 

2017-03-29 16:30:36 11861 [Note] explain select * from ecs_ugo_goods 

2017-03-29 16:30:36 11861 [Note] 第12步：候选驱动表ecs_ugo_goods的结果集行数为:75745 

2017-03-29 16:30:36 11861 [Note] explain select * from ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] 第13步：候选驱动表ecs_ugo_biku_goods_price的结果集行数为:33239 

2017-03-29 16:30:36 11861 [Note] 第14步：选择表ecs_ugo_biku_goods_price为驱动表 

2017-03-29 16:30:36 11861 [Note] 第15步：开始解析order by 条件 

2017-03-29 16:30:36 11861 [Note] 第16步：开始验证 字段add_time是不是主键。表名:ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_biku_goods_price where Key_name = 'PRIMARY' and Column_name ='add_time' and Seq_in_index = 1 

2017-03-29 16:30:36 11861 [Note] 第17步：字段add_time不是主键。表名:ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] 第18步：开始添加order by 字段 

2017-03-29 16:30:36 11861 [Note] 第19步：开始验证 字段add_time是不是主键。表名:ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_biku_goods_price where Key_name = 'PRIMARY' and Column_name ='add_time' and Seq_in_index = 1 

2017-03-29 16:30:36 11861 [Note] 第20步：字段add_time不是主键。表名:ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] 第21步：开始验证 字段iv_code是不是主键。表名:ecs_ugo_goods 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_goods where Key_name = 'PRIMARY' and Column_name ='iv_code' and Seq_in_index = 1 

2017-03-29 16:30:36 11861 [Note] 第22步：字段iv_code不是主键。表名:ecs_ugo_goods 

2017-03-29 16:30:36 11861 [Note] 第23步：开始验证 字段iv_code是不是主键。表名:ecs_ugo_goods 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_goods where Key_name = 'PRIMARY' and Column_name ='iv_code' and Seq_in_index = 1 

2017-03-29 16:30:36 11861 [Note] 第24步：字段iv_code不是主键。表名:ecs_ugo_goods 

2017-03-29 16:30:36 11861 [Note] 第25步：开始验证表中是否已存在相关索引。表名:ecs_ugo_goods, 字段名:iv_code, 在索引中的位置:1 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_goods where Column_name ='iv_code' and Seq_in_index =1 

2017-03-29 16:30:36 11861 [Note] 第26步：索引(iv_code)已存在 

2017-03-29 16:30:36 11861 [Note] 第27步：开始验证 字段add_time是不是主键。表名:ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_biku_goods_price where Key_name = 'PRIMARY' and Column_name ='add_time' and Seq_in_index = 1 

2017-03-29 16:30:36 11861 [Note] 第28步：字段add_time不是主键。表名:ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] 第29步：开始验证 字段add_time是不是主键。表名:ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_biku_goods_price where Key_name = 'PRIMARY' and Column_name ='add_time' and Seq_in_index = 1 

2017-03-29 16:30:36 11861 [Note] 第30步：字段add_time不是主键。表名:ecs_ugo_biku_goods_price 

2017-03-29 16:30:36 11861 [Note] 第31步：开始验证表中是否已存在相关索引。表名:ecs_ugo_biku_goods_price, 字段名:add_time, 在索引中的位置:1 

2017-03-29 16:30:36 11861 [Note] show index from ecs_ugo_biku_goods_price where Column_name ='add_time' and Seq_in_index =1 

2017-03-29 16:30:36 11861 [Note] 第32步：开始输出表ecs_ugo_biku_goods_price索引优化建议: 

2017-03-29 16:30:36 11861 [Note] Create_Index_SQL：alter table ecs_ugo_biku_goods_price add index idx_add_time(add_time) 

2017-03-29 16:30:36 11861 [Note] 第33步: SQLAdvisor结束! 

3、参数封装在配置文件执行sqladvisor

3.1 配置文件内容：

# cat sql.cnf 
[sqladvisor]
username=root
password=xxxx
host=127.0.0.1
port=3306
dbname=zabbix
sqls=SELECT itemid,round(500* MOD( CAST(clock AS UNSIGNED) +19571,21600)/(21600),0) AS i,COUNT(*) AS count,AVG(value) AS avg,MIN(value) as min,MAX(value) AS max,MAX(clock) AS clock FROM history  WHERE itemid=27520 

3.2 执行sqladvisor

# sqladvisor  -f sql.cnf  -v 1

2017-03-29 16:33:25 13047 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `itemid` AS `itemid`,round(((500 * ((cast(`clock` as unsigned) + 19571) % 21600)) / 21600),0) AS `i`,count(0) AS `count`,avg(`value`) AS `avg`,min(`value`) AS `min`,max(`value`) AS `max`,max(`clock`) AS `clock` from `zabbix`.`history` where (`itemid` = 27520) 

2017-03-29 16:33:25 13047 [Note] 第2步：开始解析where中的条件:(`itemid` = 27520) 

2017-03-29 16:33:25 13047 [Note] show index from history 

2017-03-29 16:33:26 13047 [Note] show table status like 'history' 

2017-03-29 16:33:26 13047 [Note] select count(*) from ( select `itemid` from `history` FORCE INDEX( history_1 ) order by clock DESC limit 10000) `history` where (`itemid` = 27520)  

2017-03-29 16:37:15 13047 [Note] 第3步：表history的行数:80133990,limit行数:10000,得到where条件中(`itemid` = 27520)的选择度:1111 

2017-03-29 16:37:15 13047 [Note] 第4步：开始验证 字段itemid是不是主键。表名:history 

2017-03-29 16:37:15 13047 [Note] show index from history where Key_name = 'PRIMARY' and Column_name ='itemid' and Seq_in_index = 1 

2017-03-29 16:37:15 13047 [Note] 第5步：字段itemid不是主键。表名:history 

2017-03-29 16:37:15 13047 [Note] 第6步：开始验证 字段itemid是不是主键。表名:history 

2017-03-29 16:37:15 13047 [Note] show index from history where Key_name = 'PRIMARY' and Column_name ='itemid' and Seq_in_index = 1 

2017-03-29 16:37:15 13047 [Note] 第7步：字段itemid不是主键。表名:history 

2017-03-29 16:37:15 13047 [Note] 第8步：开始验证表中是否已存在相关索引。表名:history, 字段名:itemid, 在索引中的位置:1 

2017-03-29 16:37:15 13047 [Note] show index from history where Column_name ='itemid' and Seq_in_index =1 

2017-03-29 16:37:15 13047 [Note] 第9步：索引(itemid)已存在 

2017-03-29 16:37:15 13047 [Note] 第10步: SQLAdvisor结束! 

三、SQLAdvisor工作流程

详细说明，请参考

转载请注明： 
十字螺丝钉 
QQ:463725310 
site: (有更多更新的内容，欢迎访问) 
http://blog.chinaunix.net/uid/23284114.html 
E-MAIL:houora#gmail.com(#请自行替换为@)