一、简介
在数据库运维过程中，优化SQL是业务团队与DBA团队的日常任务。例行SQL优化，不仅可以提升程序性能，还能够降低线上故障的概率。
目前常用的SQL优化方式包括但不限于：业务层优化、SQL逻辑优化、索引优化等。其中索引优化通常通过调整索引或新增索引从而达到SQL优化的目的。索引优化往往可以在短时间内产生非常巨大的效果。如果能够将索引优化转化成工具化、标准化的流程，减少人工介入的工作量，无疑会大大提高DBA的工作效率
SQLAdvisor是由美团点评公司DBA团队(北京)开发维护的SQL优化工具：输入SQL，输出索引优化建议。 它基于MySQL原生词法解析，再结合SQL中的where条件以及字段选择度、聚合条件、多表Join关系等最终输出最优的索引优化建议。目前SQLAdvisor在公司内部大量使用，较为成熟、稳定。
美团点评致力于将SQLAdvisor打造成一款高智能化SQL优化工具，选择将已经在公司内部使用较为成熟的、稳定的SQLAdvisor项目开源，github地址。希望与业内有类似需求的团队，一起打造一款优秀的SQL优化产品。
目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。
主要功能：输出SQL索引优化建议
GitHup地址：
二、SQLAdvisor安装
2.1 拉取最新代码
$ git clone https://github.com/Meituan-Dianping/SQLAdvisor.git
| 1 | $ git clone https : / / github .com / Meituan - Dianping / SQLAdvisor .git |
2.2 安装依赖项
$ yum install cmake libaio-devel libffi-devel glib2 glib2-devel
| 1 | $ yum install cmake libaio - devel libffi - devel glib2 glib2 - devel |
跟据glib安装的路径，修改SQLAdvisor/sqladvisor/CMakeLists.txt中的两处include_directories针对glib设置的path。glib yum安装默认不需要修改路径。
另外，编译sqladvisor时依赖perconaserverclient_r, 因此需要安装Percona-Server-shared-56。
# 配置Percona56 yum源; $ yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm # 安装Percona-Server-shared-56; $ yum install Percona-Server-shared-56
| 1 2 3 4 5 | # 配置Percona56 yum源; $ yum install http : / / www .percona .com / downloads / percona - release / redhat / 0.1 - 3 / percona - release - 0.1 - 3.noarch.rpm # 安装Percona-Server-shared-56; $ yum install Percona - Server - shared - 56 |
如果yum安装不行，可以采用rpm包手动安装。参考：https://github.com/Meituan-Dianping/SQLAdvisor/issues/12
需要配置软链接：
$ rpm -ql Percona-Server-shared-56 /usr/lib64/libperconaserverclient.so.18 /usr/lib64/libperconaserverclient.so.18.1.0 /usr/lib64/libperconaserverclient_r.so.18 /usr/lib64/libperconaserverclient_r.so.18.1.0
| 1 2 3 4 5 | $ rpm - ql Percona - Server - shared - 56 / usr / lib64 / libperconaserverclient .so . 18 / usr / lib64 / libperconaserverclient .so . 18.1.0 / usr / lib64 / libperconaserverclient_r .so . 18 / usr / lib64 / libperconaserverclient_r .so . 18.1.0 |
$ cd /usr/lib64/ $ ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so
| 1 2 | $ cd / usr / lib64 / $ ln - s libperconaserverclient_r .so . 18 libperconaserverclient_r .so |
2.3 编译依赖项sqlparser
$ cd /root/SQLAdvisor/ $ cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./ $ make && make install
| 1 2 3 | $ cd / root / SQLAdvisor / $ 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值尽量不要修改，后面安装依赖这个目录。
2.4 安装SQLAdvisor源码
$ cd /root/SQLAdvisor/sqladvisor/ $ cmake -DCMAKE_BUILD_TYPE=debug ./ $ make
| 1 2 3 | $ cd / root / SQLAdvisor / sqladvisor / $ cmake - DCMAKE_BUILD_TYPE = debug . / $ make |
安装完成后，在本路径下生成一个sqladvisor可执行文件，这即是我们想要的。
可以把可执行文件sqladvisor复制到PATH路径中。
$ cp -frp /root/SQLAdvisor/sqladvisor/sqladvisor /usr/local/bin/
| 1 | $ cp - frp / root / SQLAdvisor / sqladvisor / sqladvisor / usr / local / bin / |
三、SQLAdvisor使用
2.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, --dbname database name -q, --sqls sqls -v, --verbose 1:output logs 0:output nothing
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | $ 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 |
2.2 命令行传参调用
$ sqladvisor -h xx -P xx -u xx -p 'xx' -d xx -q "sql" -v 1
| 1 | $ sqladvisor - h xx - P xx - u xx - p 'xx' - d xx - q "sql" - v 1 |
2.3 配置文件传参调用
$ cat sql.cnf [sqladvisor] username=xx password=xx host=xx port=xx dbname=xx sqls=sql1;sql2;sql3....
| 1 2 3 4 5 6 7 8 | $ cat sql .cnf [ sqladvisor ] username = xx password = xx host = xx port = xx dbname = xx sqls = sql1 ; sql2 ; sql3 . . . . |
$ sqladvisor -f sql.cnf -v 1
| 1 | $ sqladvisor - f sql .cnf - v 1 |
2.4 测试使用
$ cat sqladvisor.cnf [sqladvisor] username=root password=zabbix host=localhost port=3306 dbname=blog sqls=SELECT * FROM wp_posts where post_date>"2015-07-16 18:44:27";
| 1 2 3 4 5 6 7 8 | $ cat sqladvisor .cnf [ sqladvisor ] username = root password = zabbix host = localhost port = 3306 dbname = blog sqls = SELECT * FROM wp_posts where post_date > "2015-07-16 18:44:27" ; |
$ sqladvisor -f sqladvisor.cnf -v 1 2017-04-13 17:39:59 23848 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `blog`.`wp_posts` where (`post_date` > '2015-07-16 18:44:27') 2017-04-13 17:39:59 23848 [Note] 第2步：开始解析where中的条件:(`post_date` > '2015-07-16 18:44:27') 2017-04-13 17:39:59 23848 [Note] show index from wp_posts 2017-04-13 17:39:59 23848 [Note] show table status like 'wp_posts' 2017-04-13 17:39:59 23848 [Note] select count(*) from ( select `post_date` from `wp_posts` FORCE INDEX( PRIMARY ) order by ID DESC limit 3763) `wp_posts` where (`post_date` > '2015-07-16 18:44:27') 2017-04-13 17:39:59 23848 [Note] 第3步：表wp_posts的行数:7527,limit行数:3763,得到where条件中(`post_date` > '2015-07-16 18:44:27')的选择度:1 2017-04-13 17:39:59 23848 [Note] 第4步：表wp_posts 的SQL太逆天,没有优化建议 2017-04-13 17:39:59 23848 [Note] 第5步: SQLAdvisor结束!
| 1 2 3 4 5 6 7 8 9 | $ sqladvisor - f sqladvisor .cnf - v 1 2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] 第 1步 : 对 SQL解析优化之后得到的 SQL : select ` * ` AS ` * ` from ` blog ` . ` wp_posts ` where ( ` post_date ` > '2015-07-16 18:44:27' ) 2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] 第 2步：开始解析 where中的条件 : ( ` post_date ` > '2015-07-16 18:44:27' ) 2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] show index from wp _posts 2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] show table status like 'wp_posts' 2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] select count ( * ) from ( select ` post_date ` from ` wp_posts ` FORCE INDEX ( PRIMARY ) order by ID DESC limit 3763 ) ` wp_posts ` where ( ` post_date ` > '2015-07-16 18:44:27' ) 2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] 第 3步：表 wp _posts的行数 : 7527 , limit行数 : 3763 ,得到 where条件中 ( ` post_date ` > '2015-07-16 18:44:27' )的选择度 : 1 2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] 第 4步：表 wp _posts 的 SQL太逆天 ,没有优化建议 2017 - 04 - 13 17 : 39 : 59 23848 [ Note ] 第 5步 : SQLAdvisor结束 ! |
四、SQLAdvisor工作原理
详情看：
五、SQLAdvisor Web
美团开源出来的SQLAdvisor SQL优化建议工具只有命令行，所以有网友就针对这个开发出了Web版本，告别命令行。
GitHub地址：
- 项目中使用的美团SQL分析工具是在CentOS上编译的，所以建议部署到CentOS上。
- 该项目是使用Python的Flask框架开发的。
- 使用CentOS自带的Python版本,版本号是2.7.5。