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

Detailed introduction of SQLAdvisor

SQLAdvisor is a SQL optimization tool developed and maintained by Meituan Dianping's Beijing DBA team: input SQL and output index optimization recommendations. It is based on MySQL native lexical parsing, combined with where conditions in SQL and field selection, aggregation conditions, multi-table Join relations and other final output optimal index optimization suggestions.

SQLAdvisor architecture flowchart:

SQLAdvisor uses an example 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) feature:

Based on MySQL native lexical parsing, the performance, quasi-determination and stability of lexical parsing are fully guaranteed.

Support for common SQL (Insert/Delete/Update/Select)

Support multi-table Join and automatically logically select driver tables

Support for aggregation conditions Order by and Group by

Filter indexes that already exist in the table.

1. SQLAdvisor installation 1.1 pull the latest code git clone https://github.com/Meituan-Dianping/SQLAdvisor.git1.2 installation dependency 1. Yum | apt-get install cmake libaio-devel libffi-devel glib2 glib2-devel 2. Yum | apt-get install-- enablerepo=Percona56 Percona-Server-shared-56

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

1.3Compilation dependency sqlparser1. Cmake-DBUILD_CONFIG=mysql_release-DCMAKE_BUILD_TYPE=debug-DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser. / 2.make & & make install

Be careful

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.

Install SQLAdvisor source code 1. Cd SQLAdvisor/sqladvisor/2. Cmake-DCMAKE_BUILD_TYPE=debug. / 3. Make4. Generate a sqladvisor executable file under this path, which is what we want. 2. SQLAdvisor uses 2.1-- help output. / sqladvisor-- helpUsage: sqladvisor [OPTION...] SqladvisorSQL Advisor SummaryHelp Options: -?,-- help Show help optionsApplication 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 nothing2.2 command line parameter call. / sqladvisor-h xx-P xx-u xx-p 'xx'-d xx-Q "sql"-v 1 Note: when command line parameter is passed Parameter names and values need to be separated by spaces. 2.3.Profiles pass parameters to call $> cat sql.cnf [sqladvisor] username=xxpassword=xxhost=xxport=xxdbname=xxsqls=sql1 Sql2;sql3....cmd:. / sqladvisor-f sql.cnf-v 1

Software download address: http://v7.51cto.com/change.php?path=/wyfs02/M00/8E/A4/wKioL1jHsrPhSztyAavyE-3DdHk359.zip&skey=JgvctLUHe_6JK8VxuSm6CQ&stime=1489482991&sname=51CTO%E4%B8%8B%E8%BD%BD-SQLAdvisor-master.zip

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.