SQLadvisor安装测试

SQLAdvisor的安装测试

在数据库运维过程中，优化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索引优化建议

安装前准备

准备好安装包和依赖包

 

$ git clone https://github.com/Meituan-Dianping/SQLAdvisor.git

 

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

$ yum install --enablerepo=Percona56 Percona-Server-shared-56

注意

• 如yum install 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
  
• 编译sqladvisor时依赖perconaserverclient_r, 因此需要安装Percona-Server-shared-56。有可能需要配置软链接例如
  •  

    $ cd /usr/lib64/

    $ ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so

安装步骤

1、编译依赖项sqlparser

 

$ 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、安装SQLAdvisor源码

 

$ cd SQLAdvisor/sqladvisor/

$ cmake -DCMAKE_BUILD_TYPE=debug ./

$ make

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

SQLAdvisor使用

准备：

在目标库上面：

grant all privileges on *.* to 'sql'@'192.168.127.129' dentified by '123123';

开放防火墙

1、--help输出

 

./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、 命令行传参调用

 

$ ./sqladvisor -h xx  -P xx  -u xx -p 'xx' -d xx -q "sql" -v 1

注意：命令行传参时，参数名与值需要用空格隔开

主要针对select和DML语句

a.select 

 

[root@nicole sqladvisor]# ./sqladvisor -h 192.168.127.128  -P 3306  -u sql -p '123123' -d sqladv -q "update t2 set name='haha' where id = 1;" -v 1

2017-07-16 00:15:57 3294 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `name` AS `name` from `sqladv`.`t2` where (`id` = 1) 

2017-07-16 00:15:57 3294 [Note] 第2步：开始解析where中的条件:(`id` = 1) 

2017-07-16 00:15:57 3294 [Note] show index from t2 

2017-07-16 00:15:57 3294 [Note] show table status like 't2' 

2017-07-16 00:15:57 3294 [Note] select count(*) from ( select `id` from `t2` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `t2` where (`id` = 1)  

2017-07-16 00:15:57 3294 [Note] 第3步：表t2的行数:24284,limit行数:10000,得到where条件中(`id` = 1)的选择度:10000 

2017-07-16 00:15:57 3294 [Note] 第4步：开始验证 字段id是不是主键。表名:t2 

2017-07-16 00:15:57 3294 [Note] show index from t2 where Key_name = 'PRIMARY' and Column_name ='id' and Seq_in_index = 1 

2017-07-16 00:15:57 3294 [Note] 第5步：字段id是主键。表名:t2 

2017-07-16 00:15:57 3294 [Note] 第6步：表t2 经过运算得到的索引列首列是主键,直接放弃,没有优化建议 

2017-07-16 00:15:57 3294 [Note] 第7步: SQLAdvisor结束! 

多表查询

 

2017-07-16 00:37:37 3776 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `t2`.`name` AS `name` from `sqladv`.`t2` join `sqladv`.`test1` where (`t2`.`id` = `test1`.`ID`) 

2017-07-16 00:37:37 3776 [Note] 第2步：开始解析join on条件:t2.id=test1.ID 

2017-07-16 00:37:37 3776 [Note] 第3步：开始选择驱动表,一共有2个候选驱动表 

2017-07-16 00:37:37 3776 [Note] explain select * from t2 

段错误 (core dumped)       -------（系统问题待解决）

b.update

 

[root@nicole sqladvisor]# ./sqladvisor -h 192.168.127.128  -P 3306  -u sql -p '123123' -d sqladv -q "update t2 set name='haha' where name = 'chenlei';" -v 1

2017-07-15 23:53:43 2689 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `name` AS `name` from `sqladv`.`t2` where (`name` = 'chenlei') 

2017-07-15 23:53:43 2689 [Note] 第2步：开始解析where中的条件:(`name` = 'chenlei') 

2017-07-15 23:53:43 2689 [Note] show index from t2 

2017-07-15 23:53:43 2689 [Note] show table status like 't2' 

2017-07-15 23:53:43 2689 [Note] select count(*) from ( select `name` from `t2` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `t2` where (`name` = 'chenlei')  

2017-07-15 23:53:43 2689 [Note] 第3步：表t2的行数:24284,limit行数:10000,得到where条件中(`name` = 'chenlei')的选择度:6 

2017-07-15 23:53:43 2689 [Note] 第4步：表t2 的SQL太逆天,没有优化建议 

2017-07-15 23:53:43 2689 [Note] 第5步: SQLAdvisor结束!

c.delete

 

[root@nicole sqladvisor]# ./sqladvisor -h 192.168.127.128  -P 3306  -u sql -p '123123' -d sqladv -q "delete from t2 where name = 'chenlei';" -v 1

2017-07-16 00:57:50 4581 [Note] 第1步: 对SQL解析优化之后得到的SQL:select  from dual where (`name` = 'chenlei') 

2017-07-16 00:57:50 4581 [Note] 第2步：开始解析where中的条件:(`name` = 'chenlei') 

2017-07-16 00:57:50 4581 [Note] show index from t2 

2017-07-16 00:57:50 4581 [Note] show table status like 't2' 

2017-07-16 00:57:50 4581 [Note] select count(*) from ( select `name` from `t2` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `t2` where (`name` = 'chenlei')  

2017-07-16 00:57:50 4581 [Note] 第3步：表t2的行数:24284,limit行数:10000,得到where条件中(`name` = 'chenlei')的选择度:6 

2017-07-16 00:57:50 4581 [Note] 第4步：表t2 的SQL太逆天,没有优化建议 

2017-07-16 00:57:50 4581 [Note] 第5步: SQLAdvisor结束!

d.DDL语句

 

[root@nicole sqladvisor]# ./sqladvisor -h 192.168.127.128  -P 3306  -u sql -p '123123' -d sqladv -q "alter table  test1 add index idx_sql (name);" -v 1

2017-07-16 00:53:57 4443 [Note] 第1步: 对SQL解析优化之后得到的SQL:select  from dual where 1 having 1 

2017-07-16 00:53:57 4443 [Note] 第2步：表test1 的SQL太逆天,没有优化建议 

2017-07-16 00:53:57 4443 [Note] 第3步: SQLAdvisor结束!

 

[root@nicole sqladvisor]# ./sqladvisor -h 192.168.127.128  -P 3306  -u sql -p '123123' -d sqladv -q "create table t4(id int);" -v 1

2017-07-16 00:43:55 4032 [Note] 第1步: 对SQL解析优化之后得到的SQL:select  from dual where 1 having 1 

2017-07-16 00:43:55 4032 [Note] 第2步：表t4 的SQL太逆天,没有优化建议 

2017-07-16 00:43:55 4032 [Note] 第3步: SQLAdvisor结束!

3、配置文件传参调用

 

$> cat sql.cnf

[sqladvisor]

username=xx

password=xx

host=xx

port=xx

dbname=xx

sqls=sql1;sql2;sql3....

 

$ ./sqladvisor -f sql.cnf  -v 1

总结

DDL语句：相比较来说去哪网的Inception在这方面更专业，可以直接执行，如果审核通过的话。

select，DML语句：SQLadvisor更加专业，大量的索引审核，对成本进行比较。

按需使用，也可两者搭配。