Shulou(Shulou.com)06/01 Report--

Analyze the tools that SQL gives index optimization suggestions (Meituan open source)

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, the company's internal development of SQLAdvisor has been transferred to github, open source and internal use are consistent.

Main function: output SQL index optimization recommendations

Detailed process of installation and testing Open Source introduction to https://www.oschina.net/news/82725/sqladvisor-opensource?from=20170312

Download address https://github.com/Meituan-Dianping/SQLAdvisor/

1. SQLAdvisor installation

Take redhat6 system as an example

1.1 pull the latest code on the premise of networking, git tools (yum install git) git clone https://github.com/Meituan-Dianping/SQLAdvisor.git has been installed

1.2 install dependencies

1.2.1. Yum install cmake libaio-devel libffi-devel glib2 glib2-devel 1.2.2. Configure percona56 yum source: yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Yum install-- enablerepo=Percona56 Percona-Server-shared-56 # # Percona56 is the name configured in yum

1.2.3. Configure soft links 1. Cd / usr/lib64/ 2. Ln-s libperconaserverclient_r.so.18 libperconaserverclient_r.so

Note 1. 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 path 2. 0 by default. Compiling sqladvisor depends on perconaserverclient_r, so you need to install Percona-Server-shared-56.

1.3Compilation dependency sqlparser

1. Cmake-DBUILD_CONFIG=mysql_release-DCMAKE_BUILD_TYPE=debug-DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser. / 2.make & & make install

Note that 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.

1.4 install SQLAdvisor source code

1. Cd SQLAdvisor/sqladvisor/ 2. Cmake-DCMAKE_BUILD_TYPE=debug. / 3. Make 4 Generate a sqladvisor executable file under this path, which is what we want.

2. SQLAdvisor 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 transfer call

. / sqladvisor-h xx-P xx-u xx-p 'xx'-d xx-Q "sql"-v 1 Note: when passing parameters on the command line, the parameter name and value need to be separated by a space

2.3 configuration file parameter transfer call

If you write the connection information into sql.cnf, you don't have to enter the user name and password every time, and you can also write the sql to be optimized.

$> 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

Personal test legend

Configuration file

Process

Personal arrangement, because the level is limited, you can comment, criticize and correct when you find mistakes, and you must correct them modestly!

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.