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

Preface of SQLAdvisor Meituan SQL Index Optimization recommendation tool

Part1: write at the front

SQLAdvisor is an open source SQL index optimization recommendation tool by Meituan. It is a tool developed and maintained by the DBA team (Beijing) of the Technical Engineering Department of Meituan Dianping Company to analyze SQL index optimization recommendations. 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. This article records the initial installation and basic use of the tool.

Installation

Part1: building the installation environment

[root@HE3 ~] # yum install git

[root@HE3 ~] # git clone https://github.com/Meituan-Dianping/SQLAdvisor.git

[root@HE3 ~] # yum install cmake libaio-devel libffi-devel glib2 glib2-devel

[root@HE3 ~] # yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

[root@HE3 ~] # yum install Percona-Server-shared-56

[root@HE3 lib64] # cd / usr/lib64/

[root@HE3] # ln-s libperconaserverclient_r.so.18 libperconaserverclient_r.so

Warning: warning 1

Here this command has been unable to pass yum install-- enablerepo=Percona56 Percona-Server-shared-56, after the direct installation of Percona-Server-shared-56 through the

Warning: warning 2

Follow the path of the glib installation and modify the path of the two include_directories settings in SQLAdvisor/sqladvisor/CMakeLists.txt for glib. This article uses git installed by yum, so there is no need to modify the path for glib yum installation by default.

Warning: warning 3

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

Warning: warning 4

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

[root@HE3] # cmake-DBUILD_CONFIG=mysql_release-DCMAKE_BUILD_TYPE=debug-DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser. /

[root@HE3] # make & & make install

[root@HE3 ~] # cd SQLAdvisor/sqladvisor

[root@HE3 sqladvisor] # cmake-DCMAKE_BUILD_TYPE=debug. /

[root@HE3 sqladvisor] # make

Generate a sqladvisor executable file under this path, which is what we want.

Use

Part1: testing small tables

[root@HE3 sqladvisor] #. / sqladvisor-h 127.0.0.1-P 3306-u root-p "MANAGER"-d helei1-Q "select * from helei1"-v 1

2017-03-21 20:37:53 8581 [Note] step 1: optimize SQL:select `* `AS` * `from `helei1`.`helei1`

2017-03-21 20:37:53 8581 [Note] step 2: the SQL of table helei1 is too adverse and there are no optimization recommendations.

2017-03-21 20:37:53 8581 [Note] step 3: end of SQLAdvisor!

Part2: test large tables (indexed)

Here we analyze the table helei and change the table to have an index on column C1.

[root@HE3 sqladvisor] #. / sqladvisor-h 127.0.0.1-P 3306-u root-p "MANAGER"-d helei1-Q "explain select * from helei where C1 / 88501;"-v 1

2017-03-21 21:19:23 8624 [Note] step 1: optimize the SQL:select `* `AS` * `from `helei1`.`helei` where (`c1` = 88501) obtained by optimizing SQL resolution

2017-03-21 21:19:23 8624 [Note] step 2: start parsing the conditions in where: (`c1` = 88501)

2017-03-21 21:19:23 8624 [Note] show index from helei

2017-03-21 21:19:23 8624 [Note] show table status like 'helei'

2017-03-21 21:19:23 8624 [Note] select count (*) from (select `c1` from `helei` FORCE INDEX (PRIMARY) order by id DESC limit 10000) `helei` where (`c1` = 88501)

2017-03-21 21:19:23 8624 [Note] step 3: number of rows in the table helei: 200380 number of helei lines: 10000, get the choice in the where condition (`c1` = 88501): 10000

2017-03-21 21:19:23 8624 [Note] step 4: start verifying that field C1 is the primary key. Table name: helei

2017-03-21 21:19:23 8624 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name =' c1' and Seq_in_index = 1

2017-03-21 21:19:23 8624 [Note] step 5: field C1 is not the primary key. Table name: helei

2017-03-21 21:19:23 8624 [Note] step 6: start verifying that field C1 is the primary key. Table name: helei

2017-03-21 21:19:23 8624 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name =' c1' and Seq_in_index = 1

2017-03-21 21:19:23 8624 [Note] step 7: field C1 is not the primary key. Table name: helei

2017-03-21 21:19:23 8624 [Note] step 8: start verifying that the relevant index already exists in the table. Table name: helei, field name: C1, position in the index: 1

2017-03-21 21:19:23 8624 [Note] show index from helei where Column_name = 'c1' and Seq_in_index = 1

2017-03-21 21:19:23 8624 [Note] step 9: index (C1) already exists

2017-03-21 21:19:23 8624 [Note] step 10: end of SQLAdvisor!

As you can see, finally, it is suggested that the SQL already has a valid index.

Part2: testing large tables (no index)

Here we analyze the table helei and change the table to have no index on column c5

[root@HE3 sqladvisor] #. / sqladvisor-h 127.0.0.1-P 3306-u root-p "MANAGER"-d helei1-Q "explain select * from helei where c5 / 74685;"-v 1

2017-03-21 21:20:53 8628 [Note] step 1: optimize the SQL:select `* `AS` * `from `helei1`.`helei` where (`c5` = 74685) obtained by optimizing SQL resolution

2017-03-21 21:20:53 8628 [Note] step 2: start parsing the conditions in where: (`c5` = 74685)

2017-03-21 21:20:53 8628 [Note] show index from helei

2017-03-21 21:20:53 8628 [Note] show table status like 'helei'

2017-03-21 21:20:53 8628 [Note] select count (*) from (select `c5` from `helei` FORCE INDEX (PRIMARY) order by id DESC limit 10000) `helei` where (`c5` = 74685)

2017-03-21 21:20:53 8628 [Note] step 3: number of rows of table helei: 201361 number of helei lines: 10000, get the choice in the where condition (`c5` = 74685): 10000

2017-03-21 21:20:53 8628 [Note] step 4: start verifying that field c5 is the primary key. Table name: helei

2017-03-21 21:20:53 8628 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name =' c5' and Seq_in_index = 1

2017-03-21 21:20:53 8628 [Note] step 5: field c5 is not the primary key. Table name: helei

2017-03-21 21:20:53 8628 [Note] step 6: start verifying that field c5 is the primary key. Table name: helei

2017-03-21 21:20:53 8628 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name =' c5' and Seq_in_index = 1

2017-03-21 21:20:53 8628 [Note] step 7: field c5 is not the primary key. Table name: helei

2017-03-21 21:20:53 8628 [Note] step 8: start verifying that the relevant index already exists in the table. Table name: helei, field name: c5, position in the index: 1

2017-03-21 21:20:53 8628 [Note] show index from helei where Column_name = 'c5' and Seq_in_index = 1

2017-03-21 21:20:53 8628 [Note] step 9: start the output table helei index optimization recommendations:

2017-03-21 21:20:53 8628 [Note] Create_Index_SQL:alter table helei add index idx_c5 (c5)

2017-03-21 21:20:53 8628 [Note] step 10: end of SQLAdvisor!

As you can see, finally, some suggestions for creating the index are given.

Part3: simultaneous analysis of multiple SQL

You can create a parameter file with any name, which is called helei.cnf. Enter regular database connection information and separate the SQL,SQL with a semicolon.

[root@HE3 sqladvisor] # cat helei.cnf

[sqladvisor]

Username=root

Password=MANAGER

Host=127.0.0.1

Port=3306

Dbname=helei1

Sqls=select * from helei where c1 / 88501 / select * from helei where c5 / 74685

Use the-f command here to load the configuration in helei.cnf

[root@HE3 sqladvisor] #. / sqladvisor-f helei.cnf-v 1

2017-03-21 21:27:35 8640 [Note] step 1: optimize the SQL:select `* `AS` * `from `helei1`.`helei` where (`c1` = 88501) obtained by optimizing SQL resolution

2017-03-21 21:27:35 8640 [Note] step 2: start parsing the conditions in where: (`c1` = 88501)

2017-03-21 21:27:35 8640 [Note] show index from helei

2017-03-21 21:27:35 8640 [Note] show table status like 'helei'

2017-03-21 21:27:35 8640 [Note] select count (*) from (select `c1` from `helei` FORCE INDEX (PRIMARY) order by id DESC limit 10000) `helei` where (`c1` = 88501)

2017-03-21 21:27:35 8640 [Note] step 3: number of rows of table helei: 200674 number of helei lines: 10000, get the choice in the where condition (`c1` = 88501): 10000

2017-03-21 21:27:35 8640 [Note] step 4: start verifying that field C1 is the primary key. Table name: helei

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name =' c1' and Seq_in_index = 1

2017-03-21 21:27:35 8640 [Note] step 5: field C1 is not the primary key. Table name: helei

2017-03-21 21:27:35 8640 [Note] step 6: start verifying that field C1 is the primary key. Table name: helei

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name =' c1' and Seq_in_index = 1

2017-03-21 21:27:35 8640 [Note] step 7: field C1 is not the primary key. Table name: helei

2017-03-21 21:27:35 8640 [Note] step 8: start verifying that the relevant index already exists in the table. Table name: helei, field name: C1, position in the index: 1

2017-03-21 21:27:35 8640 [Note] show index from helei where Column_name = 'c1' and Seq_in_index = 1

2017-03-21 21:27:35 8640 [Note] step 9: index (C1) already exists

2017-03-21 21:27:35 8640 [Note] step 10: end of SQLAdvisor!

2017-03-21 21:27:35 8640 [Note] step 1: optimize the SQL:select `* `AS` * `from `helei1`.`helei` where (`c5` = 74685) obtained by optimizing SQL resolution

2017-03-21 21:27:35 8640 [Note] step 2: start parsing the conditions in where: (`c5` = 74685)

2017-03-21 21:27:35 8640 [Note] show index from helei

2017-03-21 21:27:35 8640 [Note] show table status like 'helei'

2017-03-21 21:27:35 8640 [Note] select count (*) from (select `c5` from `helei` FORCE INDEX (PRIMARY) order by id DESC limit 10000) `helei` where (`c5` = 74685)

2017-03-21 21:27:35 8640 [Note] step 3: number of rows of table helei: number of lines of helei: 10000, get the choice in the where condition (`c5` = 74685): 10000

2017-03-21 21:27:35 8640 [Note] step 4: start verifying that field c5 is the primary key. Table name: helei

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name =' c5' and Seq_in_index = 1

2017-03-21 21:27:35 8640 [Note] step 5: field c5 is not the primary key. Table name: helei

2017-03-21 21:27:35 8640 [Note] step 6: start verifying that field c5 is the primary key. Table name: helei

2017-03-21 21:27:35 8640 [Note] show index from helei where Key_name = 'PRIMARY' and Column_name =' c5' and Seq_in_index = 1

2017-03-21 21:27:35 8640 [Note] step 7: field c5 is not the primary key. Table name: helei

2017-03-21 21:27:35 8640 [Note] step 8: start verifying that the relevant index already exists in the table. Table name: helei, field name: c5, position in the index: 1

2017-03-21 21:27:35 8640 [Note] show index from helei where Column_name = 'c5' and Seq_in_index = 1

2017-03-21 21:27:35 8640 [Note] step 9: start the output table helei index optimization recommendations:

2017-03-21 21:27:35 8640 [Note] Create_Index_SQL:alter table helei add index idx_c5 (c5)

2017-03-21 21:27:35 8640 [Note] step 10: end of SQLAdvisor!

You can see that there is an one-time suggested output for the two SQL in helei.cnf.

Warning: warning 5

Subqueries in SQL, or conditions, and conditions for using functions are ignored and not processed.

When passing in the sql parameter on the command line, note that both double quotes and backquotes in sql need to be escaped with\. It is recommended that you call the

-- Summary.

As you can see, SQLAdvisor gives optimization and advice on index creation for the query SQL, which is not bad, and further in-depth testing will be carried out later. As the author's level is limited and the writing time is very short, it is inevitable that there will be some errors or inaccuracies in the article. I urge readers to criticize and correct them.

Reference: https://github.com/Meituan-Dianping/SQLAdvisor/blob/master/doc/QUICK_START.md

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.