SQL优化工具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索引优化建议
GitHup地址：https://github.com/Meituan-Dianping/SQLAdvisor
二、SQLAdvisor安装
2.1 拉取最新代码
|
1
|
$
git
clone https://github
.com/Meituan-Dianping/SQLAdvisor
.git
|
2.2 安装依赖项
|
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。
|
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包手动安装##########
或者
解压
tar -zxvf Percona-Server-5.6.25-73.1-r07b797f-el6-x86_64-bundle.tar
找到对应包，安装即可。
rpm -ivh Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64.rpm
我使用这个方案：
解压的时候报错了：
tar -zxvf Percona-Server-5.6.25-73.1-r07b797f-el6-x86_64-bundle.tar
gzip: stdin: not in gzip format
tar: Child returned status 1
tar: Error is not recoverable: exiting now
然后换成：tar -xvf Percona-Server-5.6.25-73.1-r07b797f-el6-x86_64-bundle.tar（去掉z参数）
之后执行安装：
warning: Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
##################################
需要配置软链接：
|
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
|
|
1
2
|
$
cd /usr/lib64/
$
ln -
s libperconaserverclient_r
.so
.18 libperconaserverclient_r
.so
|
2.3 编译依赖项sqlparser
|
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源码
|
1
2
3
|
$
cd /root/SQLAdvisor/sqladvisor/
$ cmake -DCMAKE_BUILD_TYPE=
debug
./
$
make
|
安装完成后，在本路径下生成一个sqladvisor可执行文件，这即是我们想要的。
可以把可执行文件sqladvisor复制到PATH路径中。
|
1
|
$
cp -frp /root/SQLAdvisor/sqladvisor/sqladvisor /usr/local/bin/
|
三、SQLAdvisor使用
2.1 帮助输出
|
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 命令行传参调用
|
1
|
$ sqladvisor -
h xx -
P xx -
u xx -
p
'xx' -
d xx -
q
"sql" -
v 1
|
2.3 配置文件传参调用
|
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
.
.
.
.
|
|
1
|
$ sqladvisor -
f sql
.cnf -
v 1
|
2.4 测试使用
|
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"
;
|
|
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架构和实践
五、SQLAdvisor Web
美团开源出来的SQLAdvisor SQL优化建议工具只有命令行，所以有网友就针对这个开发出了Web版本，告别命令行。
GitHub地址：https://github.com/zyw/sqladvisor-web
- 项目中使用的美团SQL分析工具是在CentOS上编译的，所以建议部署到CentOS上。
- 该项目是使用Python的Flask框架开发的。
- 使用CentOS自带的Python版本,版本号是2.7.5。
安装sqladvisor-web
下载依赖：
yum install python-devel mysql-community-devel -y- 1
下载源码：
git clone https://github.com/zyw/sqladvisor-web.git- 1
安装依赖：
cd sqladvisor-web
pip3 install django_admin_bootstrappedpip install -r requirements.txt
修改数据库配置
vim config.py
SQLALCHEMY_DATABASE_URI = os.environ.get('DEV_DATABASE_URL') or 'mysql://root:root@localhost/sqladvisor'其中把第二个root修改成你安装Mysql时设置的密码
初始化数据库：
python manage.py db init
python manage.py db migrate
python manage.py db upgrade启动系统：
解压sqlparser.tar.gz到/usr/local
python manage.py runserver --host 0.0.0.0 &默认监听5000端口，此时安装完成