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-releaseCentOSrelease6.8(Final)[root@SQLAdvisor~]#uname-r2.6.32-642.3.1.el6.x86_64[root@SQLAdvisor~]#uname-nSQLAdvisor[root@SQLAdvisor~]#getenforceDisabled

* mysql version

mysql>showvariableslike'version';+---------------+--------+|Variable_name|Value|+---------------+--------+|version|5.7.18|+---------------+--------+1rowinset(0.00sec)

4. 安装SQLAdvisor

* 获取最新代码

[root@SQLAdvisor~]#gitclonehttps://github.com/Meituan-Dianping/SQLAdvisor.gitInitializedemptyGitrepositoryin/root/SQLAdvisor/.git/remote:Countingobjects:1460,done.remote:Total1460(delta0),reused0(delta0),pack-reused1460Receivingobjects:100%(1460/1460),19.92MiB|209KiB/s,done.Resolvingdeltas:100%(368/368),done.

* 安装依赖项

[root@SQLAdvisor~]#yum-yinstallcmakelibaio-devellibffi-develglib2glib2-devel[root@SQLAdvisor~]#yum-yinstallhttp://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm[root@SQLAdvisor~]#yum-yinstallPercona-Server-shared-56[root@SQLAdvisor~]#ln-s/usr/lib64/libperconaserverclient_r.so.18/usr/lib64/libperconaserverclient_r.so

*编译依赖项sqlparser

[root@SQLAdvisor~]#cdSQLAdvisor/[root@SQLAdvisorSQLAdvisor]#cmake-DBUILD_CONFIG=mysql_release-DCMAKE_BUILD_TYPE=debug-DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser./[root@SQLAdvisorSQLAdvisor]#make&&makeinstall

* 安装SQLAdvisor

[root@SQLAdvisorSQLAdvisor]#cdsqladvisor/[root@SQLAdvisorsqladvisor]#cmake-DCMAKE_BUILD_TYPE=debug./[root@SQLAdvisorsqladvisor]#make

* SQLAdvisor Info

[root@SQLAdvisorsqladvisor]#./sqladvisor--helpUsage:sqladvisor[OPTION...]sqladvisorSQLAdvisorSummaryHelpOptions:-?,--helpShowhelpoptionsApplicationOptions:-f,--defaults-filesqlsfile-u,--usernameusername-p,--passwordpassword-P,--portport-h,--hosthost-d,--dbnamedatabasename-q,--sqlssqls-v,--verbose1:outputlogs0:outputnothing

5. 测试

* 生成测试数据表

mysql>createdatabasetest1charactersetutf8mb4;QueryOK,1rowaffected(0.00sec)mysql>createtableuser(->idINTPRIMARYKEYAUTO_INCREMENT,->nameVARCHAR(64)NOTNULL,->ageint,->sexint->)ENGINE=INNODBDEFAULTCHARSET=utf8mb4;QueryOK,0rowsaffected(0.13sec)mysql>descuser;+-------+-------------+------+-----+---------+----------------+|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||+-------+-------------+------+-----+---------+----------------+4rowsinset(0.01sec)

* 生成测试数据

mysql>insertintouser(name,age,sex)select'lisea',25,1;QueryOK,1rowaffected(0.01sec)Records:1Duplicates:0Warnings:0mysql>insertintouser(name,age,sex)selectconcat(name,'1'),age+1,sex+1fromuser;QueryOK,1rowaffected(0.02sec)Records:1Duplicates:0Warnings:0mysql>insertintouser(name,age,sex)selectconcat(name,'2'),age+2,sexfromuser;QueryOK,2rowsaffected(0.02sec)Records:2Duplicates:0Warnings:0mysql>insertintouser(name,age,sex)selectconcat(name,'3'),age+2,sexfromuser;QueryOK,4rowsaffected(0.18sec)Records:4Duplicates:0Warnings:0......mysql>insertintouser(name,age,sex)selectconcat(name,'10'),age+2,sexfromuser;QueryOK,512rowsaffected(0.24sec)Records:512Duplicates:0Warnings:0mysql>insertintouser(name,age,sex)selectconcat(name,'11'),age+4,sexfromuser;QueryOK,1024rowsaffected(0.79sec)Records:1024Duplicates:0Warnings:0mysql>selectcount(1)fromuser;+----------+|count(1)|+----------+|2048|+----------+1rowinset(0.01sec)

* 命令行传参调用测试SQLAdvisor　[查找非索引行]

[root@SQLAdvisorsqladvisor]#./sqladvisor-h127.0.0.1-P3306-uroot-p'123'-dtest1-q"select*fromuserwherename='lisea'"-v12017-10-2705:35:4934059[Note]第1步:对SQL解析优化之后得到的SQL:select`*`AS`*`from`test1`.`user`where(`name`='lisea')2017-10-2705:35:4934059[Note]第2步：开始解析where中的条件:(`name`='lisea')2017-10-2705:35:4934059[Note]showindexfromuser2017-10-2705:35:4934059[Note]showtablestatuslike'user'2017-10-2705:35:4934059[Note]selectcount(*)from(select`name`from`user`FORCEINDEX(PRIMARY)orderbyidDESClimit1024)`user`where(`name`='lisea')2017-10-2705:35:4934059[Note]第3步：表user的行数:2048,limit行数:1024,得到where条件中(`name`='lisea')的选择度:10242017-10-2705:35:4934059[Note]第4步：开始验证字段name是不是主键。表名:user2017-10-2705:35:4934059[Note]showindexfromuserwhereKey_name='PRIMARY'andColumn_name='name'andSeq_in_index=12017-10-2705:35:4934059[Note]第5步：字段name不是主键。表名:user2017-10-2705:35:4934059[Note]第6步：开始验证字段name是不是主键。表名:user2017-10-2705:35:4934059[Note]showindexfromuserwhereKey_name='PRIMARY'andColumn_name='name'andSeq_in_index=12017-10-2705:35:4934059[Note]第7步：字段name不是主键。表名:user2017-10-2705:35:4934059[Note]第8步：开始验证表中是否已存在相关索引。表名:user,字段名:name,在索引中的位置:12017-10-2705:35:4934059[Note]showindexfromuserwhereColumn_name='name'andSeq_in_index=12017-10-2705:35:4934059[Note]第9步：开始输出表user索引优化建议:2017-10-2705:35:4934059[Note]Create_Index_SQL：altertableuseraddindexidx_name(name)2017-10-2705:35:4934059[Note]第10步:SQLAdvisor结束!

*命令行传参调用测试SQLAdvisor　[查找索引行]

[root@SQLAdvisorsqladvisor]#./sqladvisor-h127.0.0.1-P3306-uroot-p'123'-dtest1-q"select*fromuserwhereid=1"-v12017-10-2705:36:4634062[Note]第1步:对SQL解析优化之后得到的SQL:select`*`AS`*`from`test1`.`user`where(`id`=1)2017-10-2705:36:4634062[Note]第2步：开始解析where中的条件:(`id`=1)2017-10-2705:36:4634062[Note]showindexfromuser2017-10-2705:36:4634062[Note]showtablestatuslike'user'2017-10-2705:36:4634062[Note]selectcount(*)from(select`id`from`user`FORCEINDEX(PRIMARY)orderbyidDESClimit1024)`user`where(`id`=1)2017-10-2705:36:4634062[Note]第3步：表user的行数:2048,limit行数:1024,得到where条件中(`id`=1)的选择度:10242017-10-2705:36:4634062[Note]第4步：开始验证字段id是不是主键。表名:user2017-10-2705:36:4634062[Note]showindexfromuserwhereKey_name='PRIMARY'andColumn_name='id'andSeq_in_index=12017-10-2705:36:4634062[Note]第5步：字段id是主键。表名:user2017-10-2705:36:4634062[Note]第6步：表user经过运算得到的索引列首列是主键,直接放弃,没有优化建议2017-10-2705:36:4634062[Note]第7步:SQLAdvisor结束!

* 配置文件传参调用

[root@SQLAdvisorsqladvisor]#catsql.cnf[sqladvisor]username=rootpassword=123host=127.0.0.1port=3306dbname=test1sqls=select*fromuserwherename='lisea'[root@SQLAdvisorsqladvisor]#./sqladvisor-fsql.cnf-v12017-10-2705:40:1434070[Note]第1步:对SQL解析优化之后得到的SQL:select`*`AS`*`from`test1`.`user`where(`name`='lisea')2017-10-2705:40:1434070[Note]第2步：开始解析where中的条件:(`name`='lisea')2017-10-2705:40:1434070[Note]showindexfromuser2017-10-2705:40:1434070[Note]showtablestatuslike'user'2017-10-2705:40:1434070[Note]selectcount(*)from(select`name`from`user`FORCEINDEX(PRIMARY)orderbyidDESClimit1024)`user`where(`name`='lisea')2017-10-2705:40:1434070[Note]第3步：表user的行数:2048,limit行数:1024,得到where条件中(`name`='lisea')的选择度:10242017-10-2705:40:1434070[Note]第4步：开始验证字段name是不是主键。表名:user2017-10-2705:40:1434070[Note]showindexfromuserwhereKey_name='PRIMARY'andColumn_name='name'andSeq_in_index=12017-10-2705:40:1434070[Note]第5步：字段name不是主键。表名:user2017-10-2705:40:1434070[Note]第6步：开始验证字段name是不是主键。表名:user2017-10-2705:40:1434070[Note]showindexfromuserwhereKey_name='PRIMARY'andColumn_name='name'andSeq_in_index=12017-10-2705:40:1434070[Note]第7步：字段name不是主键。表名:user2017-10-2705:40:1434070[Note]第8步：开始验证表中是否已存在相关索引。表名:user,字段名:name,在索引中的位置:12017-10-2705:40:1434070[Note]showindexfromuserwhereColumn_name='name'andSeq_in_index=12017-10-2705:40:1434070[Note]第9步：开始输出表user索引优化建议:2017-10-2705:40:1434070[Note]Create_Index_SQL：altertableuseraddindexidx_name(name)2017-10-2705:40:1434070[Note]第10步:SQLAdvisor结束!

６. 总结

以需求驱动技术，技术本身没有优略之分，只有业务之分。