How to use SQLAdvisor
This article will explain in detail how to use SQL Advisor for everyone. Xiaobian thinks it is quite practical, so share it with everyone for reference. I hope you can gain something after reading this article.
1. SQL Advisor installation
1.1 Pull the latest code
git clone https://github.com/Meituan-Dianping/SQLAdvisor.git
1.2 Install dependencies
1. yum | apt-get install cmake libaio-devel libffi-devel glib2 glib2-devel
2. yum | apt-get install --enablerepo=Percona56 Percona-Server-shared-56
note
1. Following the path of glib installation, modify SQLAvisor/sqladvisor/CMakeLists.txt to include_directories path for glib settings in two places. glib yum installation does not require path modification by default
2. sqladvisor compilation depends on perconaserverclient_r, so Percona-Server-shared-56 needs to be installed. For example:1. cd /usr/lib64/ 2. ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so
3. You may need to configure percona56 yum Source: yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
1.3 Compile dependency sqlparser
1. cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
2. make && make install
note
1. DCMAKE_INSTALL_PREFIX is the installation directory of sqlparser library files and header files, where lib directory contains library file libsqlparser. so and include directory contains all required header files.
2. Try not to modify the value of DCMAKE_INSTALL_PREFIX. The later installation depends on this directory.
1.4 Install SQL Advisor source code
1. cd SQLAdvisor/sqladvisor/
2. cmake -DCMAKE_BUILD_TYPE=debug ./
3. make
4. Generate a sqladvisor executable under this path, which is what we want.
2. SQL Advisor Usage
2.1--help output
./ 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 command line parameter call
./ sqladvisor -h xx -P xx -u xx -p 'xx' -d xx -q "sql" -v 1
Note: When passing parameters on the command line, parameter names and values need to be separated by spaces.
2.3 Configuration file parameter transfer call
$> 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
Output:
sql: SELECT id FROM crm_loan WHERE id_card = '1234567'
cmd: ./ sqladvisor -h xx -P xx -u xx -pxx -d xx -q "SELECT id FROM crm_loan WHERE id_card = '1234567'"
SQLAdvisor output: alter table crm_loan add index idx_id_card(id_card)
Output:
[root@test-5-67 sqladvisor]# ./ sqladvisor -h 127.0.0.1 -P 3306 -u root -p 'xxxxxxx' -d jxweb -q "select * from pro_Brand where pro_CategoryID=1" -v 1
2017-03-13 11:05:37 21830 [Note] Step 1: SQL parsing optimized:select `*` AS `*` from `jxweb`.` pro_Brand` where (`pro_CategoryID` = 1)
About "SQLAvisor how to use" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.
