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

1. Background

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.

In the process of database operation and maintenance, optimizing SQL is a daily task of business team and DBA team. Routine SQL optimization can not only improve program performance, but also reduce the probability of online failure.

At present, the commonly used SQL optimization methods include but not limited to: business layer optimization, SQL logic optimization, index optimization and so on. Index optimization usually achieves the purpose of SQL optimization by adjusting the index or adding new indexes. Index optimization can often produce great results in a short period of time. If the index optimization can be transformed into an instrumental and standardized process, and the workload of manual intervention can be reduced, it will undoubtedly greatly improve the work efficiency of DBA.

two。 Architecture flowchart

3. Environment

* os version

[root@SQLAdvisor ~] # cat / etc/redhat-release CentOS release 6.8 (Final) [root@SQLAdvisor ~] # uname-r2.6.32-642.3.1.el6.x86_64 [root@SQLAdvisor ~] # uname-nSQLAdvisor [root@SQLAdvisor ~] # getenforce Disabled

* mysql version

Mysql > show variables like 'version';+-+-+ | Variable_name | Value | +-+-+ | version | 5.7.18 | +-+-+ 1 row in set (0.00 sec)

4. Install SQLAdvisor

* get the latest code

[root@SQLAdvisor] # git clone https://github.com/Meituan-Dianping/SQLAdvisor.gitInitialized empty Git repository in / root/SQLAdvisor/.git/remote: Counting objects: 1460, done.remote: Total 1460 (delta 0), reused 0 (delta 0), pack-reused 1460Receiving objects: 100% (1460 MiB), 19.92 MiB | 209 KiB/s, done.Resolving deltas: 100% (368 reused), done.

* install dependencies

[root@SQLAdvisor ~] # yum-y install cmake libaio-devel libffi-devel glib2 glib2-devel [root@SQLAdvisor ~] # yum-y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm [root@SQLAdvisor ~] # yum-y install Percona-Server-shared-56 [root@SQLAdvisor ~] # ln-s / usr/lib64/libperconaserverclient_r.so.18 / usr/lib64/libperconaserverclient_r.so

* compile dependency sqlparser

[root@SQLAdvisor ~] # cd SQLAdvisor/ [root@SQLAdvisor SQLAdvisor] # cmake-DBUILD_CONFIG=mysql_release-DCMAKE_BUILD_TYPE=debug-DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser. / [root@SQLAdvisor SQLAdvisor] # make & & make install

* install SQLAdvisor

[root@SQLAdvisor SQLAdvisor] # cd sqladvisor/ [root@SQLAdvisor sqladvisor] # cmake-DCMAKE_BUILD_TYPE=debug. / [root@SQLAdvisor sqladvisor] # make

* SQLAdvisor Info

[root@SQLAdvisor sqladvisor] # / 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 nothing

5. test

* generate test data tables

Mysql > create database test1 character set utf8mb4;Query OK, 1 row affected (0.00 sec) mysql > create table user (- > id INT PRIMARY KEY AUTO_INCREMENT,-> name VARCHAR (64) NOT NULL,-> age int,-> sex int->) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;Query OK, 0 rows affected (0.13 sec) mysql > desc user +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | id | int (11) | NO | PRI | NULL | auto_increment | | name | varchar (64) | NO | | NULL | | age | int (11) | YES | | NULL | | sex | int (11) | YES | | NULL | | | +-+-+ 4 rows in set (0.01 sec) |

* generate test data

Mysql > insert into user (name,age, sex) select 'lisea', 25, 1 row affected query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > insert into user (name,age, sex) select concat (name,' 1'), age+1, sex+1 from user;Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > insert into user (name,age, sex) select concat (name,'2'), age+2, sex from user Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > insert into user (name,age, sex) select concat (name,'3'), age+2, sex from user;Query OK, 4 rows affected (0.18 sec) Records: 4 Duplicates: 0 Warnings: 0.mysql > insert into user (name,age, sex) select concat (name, '10'), age+2, sex from user Query OK, 512 rows affected (0.24 sec) Records: 512 Duplicates: 0 Warnings: 0mysql > insert into user (name,age, sex) select concat (name, '11'), age+4, sex from user;Query OK, 1024 rows affected (0.79 sec) Records: 1024 Duplicates: 0 Warnings: 0mysql > select count (1) from user;+-+ | count (1) | +-+ | 2048 | +-+ 1 row in set (sec)

* Command line parameter calls test SQLAdvisor [find non-indexed lines]

[root@SQLAdvisor sqladvisor] #. / sqladvisor-h 127.0.0.1-P 3306-u root-p '123'-d test1-Q "select * from user where name =' lisea'"-v 12017-10-27 05:35:49 34059 [Note] step 1: optimize SQL:select `* `AS` * `from `test1`.`user` where (`name` = 'lisea') 2017-10-27 05:35:49 34059 [Note] Step 2: start parsing the conditions in where: (`name` = 'lisea') 2017-10-27 05:35:49 34059 [Note] show index from user 2017-10-27 05:35:49 34059 [Note] show table status like' user' 2017-10-27 05:35:49 34059 [Note] select count (*) from (select `name` from `user`FORCE INDEX (PRIMARY) order by id DESC limit 1024) `user`where (`name` = 'lisea') 2017-10-27 05:35:49 34059 [Note] step 3: number of rows in table user: 2048 Number of limit lines: 1024, get the selection in the where condition (`name` = 'lisea'): 1024 2017-10-27 05:35:49 34059 [Note] step 4: start verifying whether the field name is the primary key. Table name: user 2017-10-27 05:35:49 34059 [Note] show index from user where Key_name = 'PRIMARY' and Column_name =' name' and Seq_in_index = 1 2017-10-27 05:35:49 34059 [Note] step 5: field name is not the primary key. Table name: user 2017-10-27 05:35:49 34059 [Note] step 6: start verifying that the field name is the primary key. Table name: user 2017-10-27 05:35:49 34059 [Note] show index from user where Key_name = 'PRIMARY' and Column_name =' name' and Seq_in_index = 1 2017-10-27 05:35:49 34059 [Note] step 7: field name is not the primary key. Table name: user 2017-10-27 05:35:49 34059 [Note] step 8: start verifying that the relevant index already exists in the table. Table name: user, field name: name Position in the index: 1 2017-10-27 05:35:49 34059 [Note] show index from user where Column_name = 'name' and Seq_in_index = 1 2017-10-27 05:35:49 34059 [Note] step 9: start the output table user index optimization recommendations: 2017-10-27 05:35:49 34059 [Note] Create_Index_SQL:alter table user add index idx_name (name) 2017-10-27 05:35:49 34059 [Note] step 10: SQLAdvisor end!

* Command line parameter calls test SQLAdvisor [find index line]

[root@SQLAdvisor sqladvisor] #. / sqladvisor-h 127.0.0.1-P 3306-u root-p '123'-d test1-Q "select * from user where id = 1"-v 12017-10-27 05:36:46 34062 [Note] step 1: optimize SQL:select `* `AS` * `from `test1`.`user`where (`id` = 1) 2017-10-27 05:36:46 34062 [Note] 2 Step: start parsing the conditions in where: (`id` = 1) 2017-10-27 05:36:46 34062 [Note] show index from user 2017-10-27 05:36:46 34062 [Note] show table status like 'user' 2017-10-27 05:36:46 34062 [Note] select count (*) from (select `id`from `user`FORCE INDEX (PRIMARY) order by id DESC limit 1024) `user`where (`id` = 1) 2017-10-27 05:36:46 34062 [Note] step 3: number of rows in table user: 2048 Number of limit lines: 1024, get the selection in the where condition (`id` = 1): 1024 2017-10-27 05:36:46 34062 [Note] step 4: start verifying whether the field id is the primary key. Table name: user 2017-10-27 05:36:46 34062 [Note] show index from user where Key_name = 'PRIMARY' and Column_name =' id' and Seq_in_index = 1 2017-10-27 05:36:46 34062 [Note] step 5: field id is the primary key. Table name: user 2017-10-27 05:36:46 34062 [Note] step 6: table user after the calculation of the index first column is the primary key, directly give up, there is no optimization recommendation 2017-10-27 05:36:46 34062 [Note] step 7: SQLAdvisor end!

* configuration file parameter transfer call

[root@SQLAdvisor sqladvisor] # cat sql.cnf [sqladvisor] username=rootpassword=123host=127.0.0.1port=3306dbname=test1sqls=select * from user where name= 'lisea' [root@SQLAdvisor sqladvisor] #. / sqladvisor-f sql.cnf-v 12017-10-27 05:40:14 34070 [Note] step 1: SQL:select `* `AS` * `from `test1`.user` where (`name` =' lisea') 2017-10-27 05:40:14 34070 [Note] step 2: Start parsing the conditions in where: (`name` = 'lisea') 2017-10-27 05:40:14 34070 [Note] show index from user 2017-10-27 05:40:14 34070 [Note] show table status like' user' 2017-10-27 05:40:14 34070 [Note] select count (*) from (select `name` from `user`FORCE INDEX (PRIMARY) order by id DESC limit 1024) `user`where (`name` = 'lisea') 2017-10-27 05:40:14 34070 [Note] step 3: number of rows in table user: 2048 Number of limit lines: 1024, get the selection in the where condition (`name` = 'lisea'): 1024 2017-10-27 05:40:14 34070 [Note] step 4: start verifying whether the field name is the primary key. Table name: user 2017-10-27 05:40:14 34070 [Note] show index from user where Key_name = 'PRIMARY' and Column_name =' name' and Seq_in_index = 1 2017-10-27 05:40:14 34070 [Note] step 5: field name is not the primary key. Table name: user 2017-10-27 05:40:14 34070 [Note] step 6: start verifying that the field name is the primary key. Table name: user 2017-10-27 05:40:14 34070 [Note] show index from user where Key_name = 'PRIMARY' and Column_name =' name' and Seq_in_index = 1 2017-10-27 05:40:14 34070 [Note] step 7: field name is not the primary key. Table name: user 2017-10-27 05:40:14 34070 [Note] step 8: start verifying that the relevant index already exists in the table. Table name: user, field name: name Position in the index: 1 2017-10-27 05:40:14 34070 [Note] show index from user where Column_name = 'name' and Seq_in_index = 1 2017-10-27 05:40:14 34070 [Note] step 9: start the output table user index optimization recommendations: 2017-10-27 05:40:14 34070 [Note] Create_Index_SQL:alter table user add index idx_name (name) 2017-10-27 05:40:14 34070 [Note] step 10: SQLAdvisor end!

6. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

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.