SQL索引优化工具SQLAdvisor介绍

燕雨石

2023-12-01

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

博文记录对该工具的初步安装和基本使用

一、软件的安装部署

[admin@git-server opt]# pwd
/opt
[admin@git-server opt]#  yum install git
[admin@git-server opt]# git clone https://github.com/Meituan-Dianping/SQLAdvisor.git
[admin@git-server opt]# yum install cmake libaio-devel libffi-devel glib2 glib2-devel
[admin@git-server opt]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
[admin@git-server opt]# yum install Percona-Server-shared-56
[admin@git-server opt]# cd /usr/lib64/
[admin@git-server opt]# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so

[admin@git-server opt]# cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
[admin@git-server opt]# make && make install
[admin@git-server opt]# cd SQLAdvisor/sqladvisor
[admin@git-server sqladvisor]# cmake -DCMAKE_BUILD_TYPE=debug ./
[admin@git-server sqladvisor]# make

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

命令参数如下：

[admin@git-server sqladvisor]# ./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

二、测试

测试一:对小表进行测试

[admin@git-server sqladvisor]#  ./sqladvisor -h 127.0.0.1 -P 3306 -u admin -p "test@6666" -d zixun3 -q "select * from  zx_addonarticle;" -v 1
2018-06-14 17:57:33 1050 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `zixun3`.`zx_addonarticle` 

2018-06-14 17:57:33 1050 [Note] 第2步：表zx_addonarticle 的SQL太逆天,没有优化建议 

2018-06-14 17:57:33 1050 [Note] 第3步: SQLAdvisor结束!

当然上面的命令也是可以写入配置文件，通过-f参数指定文件来操作的

[admin@git-server sqladvisor]# cat test01.cnf 
[sqladvisor]
username=admin
password=test@6666
host=127.0.0.1
port=3306
dbname=zixun3
sqls=select * from  zx_addonarticle;

[admin@git-server sqladvisor]# ./sqladvisor -f test01.cnf -v 1
2018-06-14 18:01:42 1198 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `zixun3`.`zx_addonarticle` 
2018-06-14 18:01:42 1198 [Note] 第2步：表zx_addonarticle 的SQL太逆天,没有优化建议 
2018-06-14 18:01:42 1198 [Note] 第3步: SQLAdvisor结束!

测试二：对大表有索引测试

MySQL [zhangyou]> show index from test_table01;;
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_table01 |          0 | PRIMARY     |            1 | id          | A         |      120201 |     NULL | NULL   |      | BTREE      |         |               |
| test_table01 |          0 | day         |            1 | day         | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| test_table01 |          0 | day         |            2 | planid      | A         |        1521 |     NULL | NULL   |      | BTREE      |         |               |
| test_table01 |          0 | day         |            3 | uid         | A         |      120201 |     NULL | NULL   |      | BTREE      |         |               |
| test_table01 |          0 | day         |            4 | siteid      | A         |      120201 |     NULL | NULL   |      | BTREE      |         |               |
| test_table01 |          0 | day         |            5 | zoneid      | A         |      120201 |     NULL | NULL   |      | BTREE      |         |               |
| test_table01 |          1 | idx_day_uid |            1 | day         | A         |         112 |     NULL | NULL   |      | BTREE      |         |               |
| test_table01 |          1 | idx_day_uid |            2 | uid         | A         |       10927 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
11 rows in set (0.00 sec)

[admin@git-server sqladvisor]# vim test.cnf 
[sqladvisor]
username=admin
password=test@6666
host=127.0.0.1
port=3306
dbname=zhangyou
sqls=select * from test_table01 where day='2016-04-22' and uid=26';

[admin@git-server sqladvisor]# ./sqladvisor -f test.cnf -v 1
2018-06-14 18:08:24 1281 [Note] 第1步: 对SQL解析优化之后得到的SQL:select had some error 
2018-06-14 18:08:24 1281 [Note] 第2步：开始解析where中的条件:(`day` = '2016-04-22') 
2018-06-14 18:08:24 1281 [Note] show index from test_table01 
2018-06-14 18:08:24 1281 [Note] show table status like 'test_table01' 
2018-06-14 18:08:24 1281 [Note] select count(*) from ( select `day` from `test_table01` FORCE INDEX( day ) order by day DESC,planid DESC,uid DESC,siteid DESC,zoneid DESC,adstypeid DESC limit 10000) `test_table01` where (`day` = '2016-04-22')  
2018-06-14 18:08:24 1281 [Note] 第3步：表test_table01的行数:120201,limit行数:10000,得到where条件中(`day` = '2016-04-22')的选择度:10000 
2018-06-14 18:08:24 1281 [Note] 第4步：开始解析where中的条件:(`uid` = 26) 
2018-06-14 18:08:24 1281 [Note] show index from test_table01 
2018-06-14 18:08:24 1281 [Note] show table status like 'test_table01' 
2018-06-14 18:08:24 1281 [Note] select count(*) from ( select `uid` from `test_table01` FORCE INDEX( day ) order by day DESC,planid DESC,uid DESC,siteid DESC,zoneid DESC,adstypeid DESC limit 10000) `test_table01` where (`uid` = 26)  
2018-06-14 18:08:24 1281 [Note] 第5步：表test_table01的行数:120201,limit行数:10000,得到where条件中(`uid` = 26)的选择度:10000 
2018-06-14 18:08:24 1281 [Note] 第6步：开始验证 字段day是不是主键。表名:test_table01 
2018-06-14 18:08:24 1281 [Note] show index from test_table01 where Key_name = 'PRIMARY' and Column_name ='day' and Seq_in_index = 1 
2018-06-14 18:08:24 1281 [Note] 第7步：字段day不是主键。表名:test_table01 
2018-06-14 18:08:24 1281 [Note] 第8步：开始验证 字段day是不是主键。表名:test_table01 
2018-06-14 18:08:24 1281 [Note] show index from test_table01 where Key_name = 'PRIMARY' and Column_name ='day' and Seq_in_index = 1 
2018-06-14 18:08:24 1281 [Note] 第9步：字段day不是主键。表名:test_table01 
2018-06-14 18:08:24 1281 [Note] 第10步：开始验证表中是否已存在相关索引。表名:test_table01, 字段名:day, 在索引中的位置:1 
2018-06-14 18:08:24 1281 [Note] show index from test_table01 where Column_name ='day' and Seq_in_index =1 
2018-06-14 18:08:24 1281 [Note] 第11步：开始验证 字段uid是不是主键。表名:test_table01 
2018-06-14 18:08:24 1281 [Note] show index from test_table01 where Key_name = 'PRIMARY' and Column_name ='uid' and Seq_in_index = 1 
2018-06-14 18:08:24 1281 [Note] 第12步：字段uid不是主键。表名:test_table01 
2018-06-14 18:08:24 1281 [Note] 第13步：开始验证表中是否已存在相关索引。表名:test_table01, 字段名:uid, 在索引中的位置:2 
2018-06-14 18:08:24 1281 [Note] show index from test_table01 where Column_name ='uid' and Seq_in_index =2 
2018-06-14 18:08:24 1281 [Note] 第14步：索引(day,uid)已存在 
2018-06-14 18:08:24 1281 [Note] 第15步: SQLAdvisor结束!

测试三：对大表无索引进行测试

[admin@git-server sqladvisor]# vim test.cnf 
[sqladvisor]
username=admin
password=test@6666
host=127.0.0.1
port=3306
dbname=zhangyou
sqls=select * from test_table01 where  zoneid=42 and views=198;

[admin@git-server sqladvisor]# ./sqladvisor -f test.cnf -v 1
2018-06-14 18:15:09 1348 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `zhangyou`.`test_table01` where ((`zoneid` = 42) and (`views` = 198)) 
2018-06-14 18:15:09 1348 [Note] 第2步：开始解析where中的条件:(`zoneid` = 42) 
2018-06-14 18:15:09 1348 [Note] show index from test_table01 
2018-06-14 18:15:09 1348 [Note] show table status like 'test_table01' 
2018-06-14 18:15:09 1348 [Note] select count(*) from ( select `zoneid` from `test_table01` FORCE INDEX( day ) order by day DESC,planid DESC,uid DESC,siteid DESC,zoneid DESC,adstypeid DESC limit 10000) `test_table01` where (`zoneid` = 42)  
2018-06-14 18:15:09 1348 [Note] 第3步：表test_table01的行数:120201,limit行数:10000,得到where条件中(`zoneid` = 42)的选择度:10000 
2018-06-14 18:15:09 1348 [Note] 第4步：开始解析where中的条件:(`views` = 198) 
2018-06-14 18:15:09 1348 [Note] show index from test_table01 
2018-06-14 18:15:09 1348 [Note] show table status like 'test_table01' 
2018-06-14 18:15:09 1348 [Note] select count(*) from ( select `views` from `test_table01` FORCE INDEX( day ) order by day DESC,planid DESC,uid DESC,siteid DESC,zoneid DESC,adstypeid DESC limit 10000) `test_table01` where (`views` = 198)  
2018-06-14 18:15:09 1348 [Note] 第5步：表test_table01的行数:120201,limit行数:10000,得到where条件中(`views` = 198)的选择度:2000 
2018-06-14 18:15:09 1348 [Note] 第6步：开始验证 字段zoneid是不是主键。表名:test_table01 
2018-06-14 18:15:09 1348 [Note] show index from test_table01 where Key_name = 'PRIMARY' and Column_name ='zoneid' and Seq_in_index = 1 
2018-06-14 18:15:09 1348 [Note] 第7步：字段zoneid不是主键。表名:test_table01 
2018-06-14 18:15:09 1348 [Note] 第8步：开始验证 字段zoneid是不是主键。表名:test_table01 
2018-06-14 18:15:09 1348 [Note] show index from test_table01 where Key_name = 'PRIMARY' and Column_name ='zoneid' and Seq_in_index = 1 
2018-06-14 18:15:09 1348 [Note] 第9步：字段zoneid不是主键。表名:test_table01 
2018-06-14 18:15:09 1348 [Note] 第10步：开始验证表中是否已存在相关索引。表名:test_table01, 字段名:zoneid, 在索引中的位置:1 
2018-06-14 18:15:09 1348 [Note] show index from test_table01 where Column_name ='zoneid' and Seq_in_index =1 
2018-06-14 18:15:09 1348 [Note] 第11步：开始验证 字段views是不是主键。表名:test_table01 
2018-06-14 18:15:09 1348 [Note] show index from test_table01 where Key_name = 'PRIMARY' and Column_name ='views' and Seq_in_index = 1 
2018-06-14 18:15:09 1348 [Note] 第12步：字段views不是主键。表名:test_table01 
2018-06-14 18:15:09 1348 [Note] 第13步：开始验证表中是否已存在相关索引。表名:test_table01, 字段名:views, 在索引中的位置:2 
2018-06-14 18:15:09 1348 [Note] show index from test_table01 where Column_name ='views' and Seq_in_index =2 
2018-06-14 18:15:09 1348 [Note] 第14步：开始输出表test_table01索引优化建议: 
~~*2018-06-14 18:15:09 1348 [Note] CreateIndexSQL：alter table testtable01 add index idxzoneidviews(zoneid,views) *~~
2018-06-14 18:15:09 1348 [Note] 第15步: SQLAdvisor结束!

可以看到，最后给出了创建索引的建议

测试四：多条SQL同时分析：

[admin@git-server sqladvisor]# cat /opt/SQLAdvisor/sqladvisor/test.cnf 
[sqladvisor]
username=admin
password=test@6666
host=127.0.0.1
port=3306
dbname=zhangyou
sqls=select * from test_table01 where  zoneid=42 and views=198;select * from test_table01 where day='2016-04-22' and uid=26';
[admin@git-server sqladvisor]#

参考博文地址：https://blog.51cto.com/suifu/1909169

转载于:https://blog.51cto.com/wujianwei/2129528

SQL索引优化工具SQLAdvisor介绍

一、软件的安装部署

二、测试

相关阅读

相关文章

相关问答

相关文档