D04 Ubuntu16 安装SQLAdvisor

1. WSL ubuntu16 安装
2. 安装SQLAdvisor
3. 使用和测试

TOC

---

1. WSL ubuntu16 安装

略

2. 安装SQLAdvisor

1、拉代码
git clone https://github.com/Meituan-Dianping/SQLAdvisor
或者下载在解压

2、装依赖

apt-get install cmake libaio-dev libffi-dev
apt-get install libglib2.0-dev

#先装percona软件源 https://www.percona.com/doc/percona-server/5.6/installation/apt_repo.html
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
apt-get update
apt-get install libperconaserverclient18.1-dev

apt-get install g++

3、编译依赖项sqlparser

cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
make && make install

4、安装SQLAdvisor

cd  sqladvisor/
cmake -DCMAKE_BUILD_TYPE=debug ./
make

#报错：
/usr/include/glib-2.0/glib/gtypes.h:32:24: fatal error: glibconfig.h: No such file or directory

#处理报错
sudo find ./ iname "glibconfig.h"   2>/dev/null
/usr/lib/x86_64-linux-gnu/glib-2.0/include/glibconfig.h

#修改 CMakeLists.txt
#include_directories("/usr/lib64/glib-2.0/include") #依据find的路径修改为下面
include_directories("/usr/lib/x86_64-linux-gnu/glib-2.0/include")

#重新编译
rm CMakeCache.txt
cmake -DCMAKE_BUILD_TYPE=debug ./
make

3. 使用和测试

1、使用

# sqladvisor --help
用法：
  sqladvisor [OPTION…] sqladvisor

SQL Advisor Summary

帮助选项：
  -?, --help 显示帮助选项

应用程序选项：
  -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

#使用，注意传参空格
./sqladvisor -h xx -P xx -u xx -p 'xx' -d xx -q "sql" -v 1

$> cat sql.cnf
[sqladvisor]
username=xx
password=xx
host=xx
port=xx
dbname=xx
sqls=sql1;sql2;sql3....

cmd: ./sqladvisor -f sql.cnf -v 1

2、测试

aijie@DESKTOP-N85GQN8:~/dbplat/SQLAdvisor-master/sqladvisor$ ./sqladvisor -u ajtest -p ajtest -h 127.0.0.1 -P 3306 -d ajtest -q "select * from t1 where name = 'ccc';" -v 1
2019-05-15 16:41:28 14792 [Note] 第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `ajtest`.`t1` where (`name` = 'ccc')
2019-05-15 16:41:28 14792 [Note] 第2步：开始解析where中的条件:(`name` = 'ccc')
2019-05-15 16:41:28 14792 [Note] show index from t1
2019-05-15 16:41:28 14792 [Note] show table status like 't1'
2019-05-15 16:41:28 14792 [Note] select count(*) from ( select `name` from `t1` FORCE INDEX( PRIMARY ) order by id DESC limit 1) `t1` where (`name` = 'ccc')
2019-05-15 16:41:28 14792 [Note] 第3步：表t1的行数:2,limit行数:1,得到where条件中(`name` = 'ccc')的选择度:1
2019-05-15 16:41:28 14792 [Note] 第4步：表t1 的SQL太逆天,没有优化建议
2019-05-15 16:41:28 14792 [Note] 第5步: SQLAdvisor结束!