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

Editor to share with you how to install and use SQLAdvisor, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

A brief introduction to SQLAdvisor

SQLAdvisor is a tool developed and maintained by DBA team (Beijing) of Technical Engineering Department of Meituan Dianping Company to analyze SQL and give suggestions for index optimization. It is based on the original MySQL lexical parsing, combined with the analysis of where conditions, aggregation conditions and multi-table Join relations in SQL to give suggestions for index optimization. At present, SQLAdvisor is widely used in Meituan Dianping, including Meituan pay, hotel tourism, takeout, group purchase and other product lines. The company's internal development of SQLAdvisor has been fully transferred to github, with open source and internal use consistent.

Main function: output SQL index optimization recommendations

II. SQLAdvisor installation

1. Dependent package installation

Rpm-Q cmake libaio-devel libffi-devel glib2 glib2-devel

Yum install cmake libaio-devel libffi-devel glib2 glib2-devel

Yum install-enablerepo=Percona56 Percona-Server-shared-56 (if you install percona database, you can create library files in the form of soft links, ln-s / usr/local/mysql/lib/libperconaserverclient.so.18 / usr/lib64/libperconaserverclient.so,)

Be careful

Follow the path of the glib installation and modify the path of the two include_directories settings in SQLAdvisor/sqladvisor/CMakeLists.txt for glib. Glib yum installation does not need to modify the path by default

Compiling sqladvisor depends on perconaserverclient_r, so you need to install Percona-Server-shared-56. Soft links may need to be configured, for example: 1. Cd / usr/lib64/ 2. Ln-s libperconaserverclient_r.so.18 libperconaserverclient_r.so

It may be necessary to configure the percona56 yum source: yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

2. Download the software package

Https://github.com/Meituan-Dianping/SQLAdvisor

Git clone https://github.com/Meituan-Dianping/SQLAdvisor.git

3. Software package installation

(1) decompress the installation package

Unzip SQLAdvisor-master.zip

(2) install sqlparser parsing

Cd SQLAdvisor-master

Cmake-DBUILD_CONFIG=mysql_release-DCMAKE_BUILD_TYPE=debug-DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser.

Make

Make install

Note:

DCMAKE_INSTALL_PREFIX is the installation directory for the sqlparser library files and header files, where the lib directory contains the library files and the libsqlparser.so,include directory contains all the required header files.

The DCMAKE_INSTALL_ premium value should not be modified as much as possible, and the subsequent installation depends on this directory.

(3) install sqladvisor

Cd sqladvisor/

Cmake-DCMAKE_BUILD_TYPE=debug. /

Make

# # at this time, the executable file sqladvisor is generated in the compilation directory, which can be put into the PATH path through ln-s sqladvisor / usr/bin/ and executed through. / sqladvisor.

III. Use of SQLAdvisor

1. Command help documentation view

[root@node1] # 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. Command line executes SQLAdvisor

[root@node1 sqladvisor] # sqladvisor-h 10.1.0.10-u dbuser-p abc.1234-P 3306-d dbtest-Q "select * from T2 where id=3;"-v 1

2017-03-12 08:32:31 23537 [Note] step 1: optimize the SQL:select `* `AS` * `from `dbtest`.`t2` where (`id` = 3) obtained by optimizing SQL resolution

2017-03-12 08:32:31 23537 [Note] step 2: start parsing the conditions in where: (`id` = 3)

2017-03-12 08:32:31 23537 [Note] show index from T2

2017-03-12 08:32:31 23537 [Note] step 3: end of SQLAdvisor! There are no indexes in the table

3. SQLAdvisor is used with parameter files.

[root@node1 sqladvisor] # cat sql.cnf

[sqladvisor]

Username=dbuser

Password=abc.1234

Host=10.1.0.10

Port=3306

Dbname=dbtest

Sqls=select * from T2 where id=3

[root@node1 sqladvisor] # sqladvisor-f sql.cnf-v 1

2017-03-12 08:34:03 24195 [Note] step 1: optimize the SQL:select `* `AS` * `from `dbtest`.`t2` where (`id` = 3) obtained by optimizing SQL resolution

2017-03-12 08:34:03 24195 [Note] step 2: start parsing the conditions in where: (`id` = 3)

2017-03-12 08:34:03 24195 [Note] show index from T2

2017-03-12 08:34:03 24195 [Note] step 3: end of SQLAdvisor! There are no indexes in the table

IV. Appendix to SQLAdvisor

1. Error message and solution

Error message 01:

Scanning dependencies of target sqladvisor

[100%] Building CXX object CMakeFiles/sqladvisor.dir/main.cc.o

Linking CXX executable sqladvisor

/ usr/bin/ld: cannot find-lperconaserverclient_r

Collect2: ld returned 1 exit status

Make [2]: * * [sqladvisor] Error 1

Make [1]: * * [CMakeFiles/sqladvisor.dir/all] Error 2

Make: * * [all] Error 2

# # because the percona library file libperconaserverclient_r.so is missing or does not exist under / usr/lib64, the solution is to install the percona library package or ln-s libperconaserverclient_r.so.18 libperconaserverclient_r.so through soft links

2. Structure flow chart (refer to source documentation)

The above is all the contents of the article "how to install and use SQLAdvisor". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.