Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.