(MYSQL) SQL Optimization Tool-Sqladvisor installation using the detailed

First, Sqladvisor Introduction
Sqladvisor is an analytical SQL provided by the company's technical engineering Department's DBA team (Beijing) to develop and maintain a tool for index optimization recommendations. Based on the original ecological lexical analysis of MySQL, this paper combines the Where condition, aggregation condition and multi-table join relation in SQL to give the index optimization suggestions . Currently sqladvisor is widely used in the United States, including corporate payment, hotel tours, take-away, buy and other product lines, the company's internal development of sqladvisor to GitHub, open source and internal use consistent.

Second, Sqladvisor installation

1. Pull up the latest code

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

　　

　　2. Installation dependencies

1. Yum Install cmake libaio-devel libffi-devel glib2 glib2-devel
2. Yum Install--enablerepo=percona56 percona-server-shared-56

Attention:

　　　　1. Follow the path of the glib installation, modifying two include_directories paths in the Sqladvisor/sqladvisor/cmakelists.txt for the glib settings. GLib yum installation does not require a path modification by default

　　　　

2. When compiling the Sqladvisor, the perconaserverclient_r is dependent, so the percona-server-shared-56 needs to be installed. There may be a need to configure soft links such as: 1. Cd/usr/lib64/2. Ln-s libperconaserverclient_r.so.18 libperconaserverclient_r.so
　　　　

3. There may be a 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

　　3. Compile the dependency sqlparser

1. Cmake-dbuild_config=mysql_release-dcmake_build_type=debug-dcmake_install_prefix=/usr/local/sqlparser./2. Make && make install

　　

Attention:

　　　　1. Dcmake_install_prefix is the installation directory for the Sqlparser library file and header file, where the Lib directory contains the library file Libsqlparser.so,include directory containing all the header files required.

　　　　

2. Dcmake_install_prefix value try not to modify, after installation depends on this directory.

　　4. Install Sqladvisor Source code

1. CD SQLADVISOR/SQLADVISOR/2. Cmake-dcmake_build_type=debug./3. Make

Generate a sqladvisor executable file under this path, which is what we want. It can be placed in the path path through Ln-s sqladvisor/usr/bin/, executed through the./sqladvisor

Third, Sqladvisor Use

1. command Help document View

[Email protected] sqladvisor]#/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 execution Sqladvisor

./sqladvisor-  H 192.168.2.117-u root-p 123456-p 3306-d volunteer_pu-q "SELECT COUNT (*) as Count from Activity_app Ly as a where a.delstatus= 0 and a.activity=66 and A.status=1 "-V 1

　　3, with the use of file parameters

./sqladvisor-f SQL.CNF-  v 1

Parameter file format:

[Sqladvisor]username=rootpassword=123456host=192.168.2.117port=3306dbname=volunteer_pusqls =select Count (*) as Count from Activity_apply as a where a.delstatus= 0 and a.activity=66 and A.status=1;   

Iv. error messages and solutions

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
# #由于缺少percona的库文件libperconaserverclient_r. So or does not exist under/USR/LIB64, the solution is to install the Percona library package or through a soft link ln-s libperconaserverclient_r.so.18 libperconaserverclient_r.so

(MYSQL) SQL Optimization Tool-Sqladvisor installation using the detailed