一路向前!deadhorse.blog.chinaunix.net

从事Oracle架构设计、SQL优化、MySQL维护工作。欢迎交流 qq:463725310 我的站点:www.dbhelp.net

  • 博客访问: 1707712
  • 博文数量: 208
  • 博客积分: 2413
  • 博客等级: 大尉
  • 技术积分: 3020
  • 用 户 组: 普通用户
  • 注册时间: 2011-04-01 22:07
  • 认证徽章:
个人简介

作者:十字螺丝钉。多年Oracle、MySQL数据库架构设计、管理及维护经验。擅长SQL优化、排错等。 我的站点:www.dbhelp.net

文章分类

全部博文(208)

文章存档

2017年(7)

2016年(35)

2015年(16)

2014年(25)

2013年(25)

2012年(56)

2011年(44)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Mysql/postgreSQL


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

SQLAdvisor项目地址 https://github.com/Meituan-Dianping/SQLAdvisor

一、安装

1、安装依赖项目

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

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

# wget https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.35-81.0/binary/redhat/5/x86_64/Percona-Server-shared-56-5.6.35-rel81.0.el5.x86_64.rpm
# 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 https://codeload.github.com/Meituan-Dianping/SQLAdvisor/zip/master 

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工作流程

SQLAdvisor工作总流程

SQLAdivisor工作总流程.jpg

详细说明,请参考https://github.com/Meituan-Dianping/SQLAdvisor/blob/master/doc/THEORY_PRACTICES.md

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

阅读(276) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册