SQLAdvisor是由美团点评公司开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析,结合分析SQL中的where条件、聚合条件、多表Join关系 给出索引优化建议。目前SQLAdvisor在美团点评内部广泛应用。
一 安装依赖包
yum install cmake libaio-devel libffi-devel glib2 glib2-devel gcc-c++ -y
rpm -ivh percona-release-0.1-4.noarch.rpm
yum install Percona-Server-shared-56 -y
/*
假如遇到报错:
The GPG keys listed for the "Percona-Release YUM repository - x86_64" repository are already installed but they are not correct for this package.
Check that the correct key URLs are configured for this repository.
则:
yum update percona-release
再重新安装Percona-Server-shared-56
*/
cd /usr/lib64
ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so
二 安装sqladvisor
2.1 编译依赖项sqlparser
unzip SQLAdvisor-master.zip
mv SQLAdvisor-master SQLAdvisor
cd SQLAdvisor
cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
/*
假如报错:
Could not find module FindGit.cmake or a configuration file for package Git.
tar -xvf cmake-3.16.5-Linux-x86_64.tar.gz
cd cmake-3.16.5-Linux-x86_64/bin
mv /usr/bin/cmake /usr/bin/cmake_bak
ln -s 解压路径/cmake-3.16.5-Linux-x86_64/bin/cmake /usr/bin/cmake
*/
make
make install
2.2 安装SQLAdvisor源码
cd sqladvisor/
cmake -DCMAKE_BUILD_TYPE=debug ./
make
在本路径下生成一个sqladvisor可执行文件,这即是我们想要的。
三 sqladvisor使用示例
./sqladvisor -h xx -P xx -u xx -p 'xx' -d xx -q "sql" -v 1
/*
假如报错:
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
则将自己的socket文件连接到/var/lib/mysql/mysql.sock下,示例:
mkdir -p /var/lib/mysql
ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
*/
示例:
./sqladvisor -h localhost -P 3307 -u root -p '密码' -d eim -q "SELECT * FROM eim.pub_user_app_test WHERE id='000171e9b1a0ce0d'" -v 1
也可以将参数写到配置文件里:
vi sql.cnf,添加:
[sqladvisor]
username=xx
password=xx
host=xx
port=xx
dbname=xx
sqls=sql1;sql2;sql3....
#调用./sqladvisor -f sql.cnf -v 1
--本篇文章主要参考自:
SQLAdvisor/QUICK_START.md at master · Meituan-Dianping/SQLAdvisor · GitHub