MySQL--------SQL Optimization Audit tool combat

1. Background

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. At present, Sqladvisor is widely used in the United States, the company's internal development of sqladvisor to GitHub, open source and internal use consistent.

In the database operations process, optimizing SQL is the Daily task of the business team and the DBA team. Routine SQL optimizations can not only improve program performance, but also reduce the probability of online failures.

The most commonly used SQL optimization methods include but are not limited to: Business layer optimization, SQL logic optimization, index optimization, etc. Index optimizations typically achieve SQL optimization by adjusting the index or adding new indexes. Index optimization can often produce very large effects in a short period of time. If you can transform index optimization into a tool-and-standardized process, reducing the amount of manual intervention will undoubtedly greatly increase DBA productivity.

2. Architecture Flowchart

650) this.width=650; "src=" Https://s4.51cto.com/oss/201710/26/b2dde84cb23e7d7f1b98f450505e6222.jpg "title=" 123123.jpg "alt=" B2dde84cb23e7d7f1b98f450505e6222.jpg "/>

3. Environment

* OS version

[Email protected] ~]# cat/etc/redhat-release CentOS release 6.8 (Final) [[email protected] ~]# uname-r2.6.32-642.3.1.el6 . X86_64[[email protected] ~]# uname-nsqladvisor[[email protected] ~]# Getenforce Disabled

* MySQL version

Mysql> Show variables like ' version '; +---------------+--------+| variable_name | Value |+---------------+--------+| Version | 5.7.18 |+---------------+--------+1 row in Set (0.00 sec)

4. Installing Sqladvisor

* Get the latest code

[[email protected] ~]# 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 14 60Receiving objects:100% (1460/1460), 19.92 MiB | 209 kib/s, done. Resolving deltas:100% (368/368), done.

* Installation Dependencies

[[email protected] ~]# yum-y install cmake libaio-devel libffi-devel glib2 glib2-devel[[email protected] ~]# yum-y INS tall [[email protected] ~]# yum-y install percona-server-shared-56 [[email protected] ~]# ln-s/usr/lib64/libperconase Rverclient_r.so.18/usr/lib64/libperconaserverclient_r.so

* compile dependencies sqlparser

[Email protected] ~]# CD Sqladvisor/[[email protected] sqladvisor]# cmake-dbuild_config=mysql_release-dcmake_build_ Type=debug-dcmake_install_prefix=/usr/local/sqlparser./[[email protected] sqladvisor]# make && make INSTALL

* Installation Sqladvisor

[Email protected] sqladvisor]# CD Sqladvisor/[[email protected] sqladvisor]# cmake-dcmake_build_type=debug./[[email Protected] sqladvisor]# make

* sqladvisor Info

[[email protected] 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. Testing

* Generate test Data Sheet

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 ( 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 (one)      | NO   | PRI | NULL     | auto_increment | |  name  | varchar ( | no   |     |)  NULL    |                 | |  age   | int (one)      | YES  |      | NULL    |                 | |  sex   | int (one)      | YES  |      | NULL    |                 |+-------+-------------+------+-----+---------+----------------+4  rows in set  (0.01 SEC) 

* generate test Data

Mysql> insert into user (name,age, sex)  select  ' Lisea ', 25, 1; 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&nBSP; (0.18 sec) records: 4  duplicates: 0  warnings: 0......mysql>  insert into user (Name,age, sex)  select concat (name,  '),  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,  ' one '), 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   (0.01 SEC)

* command-line parameters call Test Sqladvisor [Find non-indexed rows]

[[email protected] 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]  The 1th step:  sql:select  ' * '  AS  ' * '  from  ' test1 ' after the SQL parsing optimization. ' User '  where  (' Name '  =  ' Lisea ')  2017-10-27 05:35:49 34059 [Note]  2nd step: Start parsing 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]  3rd: Table user number of rows: 2048,limit: 1024, gets the selection in the Where condition (' name '  =  ' Lisea '):1024  2017-10-27 05:35:49 34059 [note]  4th step: Start validation   field name is not 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]  5th step: Field name is not a primary key. Table name:user 2017-10-27 05:35:49 34059 [note]  6th step: Start validation   field name is not 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]  7th step: Field name is not a primary key. Table Name: user 2017-10-27 05:35:49 34059 [note]  Step 8th: Start verifying that a related index already exists in the table. Table name:user,  The position of the field name:name,  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]  9th step: 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]  10th step:  sqladvisor End!

* command line parameter call Test sqladvisor [find index row]

[[email protected] 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]  1th step:   sql:select  ' * '  AS  ' * '  from  ' test1 ' after SQL parsing optimization. ' User '  where  (' id ')  = 1)  2017-10-27 05:36:46 34062 [Note]  2nd step: Start parsing 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 ' &NBsp;where  (' id '  = 1)   2017-10-27 05:36:46 34062 [Note]  3rd Step: Table User number of rows: 2048,limit: 1024, gets the selection of the Where condition (' id '  = 1):1024 2017-10-27 05:36:46  34062 [note]  The 4th step: Start validation   Field ID is not a 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]  5th step: The field ID is the primary key. Table name:user 2017-10-27 05:36:46 34062 [note]  6th step: Table user  The first column of the indexed column is the primary key, discard directly, no optimization suggestions  2017-10-27 05:36:46 34062 [Note]  7th step:  sqladvisor End!

* configuration file parameter call

[[Email protected] sqladvisor]# cat sql.cnf [sqladvisor]username=rootpassword=123host =127.0.0.1port=3306dbname=test1sqls=select * from user where name =  ' Lisea ' [[Email protected] sqladvisor]# ./sqladvisor -f sql.cnf -v 12017-10-27  05:40:14 34070 [Note]  1th Step:  sql:select  ' * '  AS  ' * '   after SQL parsing optimization from  ' test1 '. ' User '  where  (' name '  =  ' Lisea ')  2017-10-27 05:40:14 34070  [Note]  2nd Step: Start parsing 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]  3rd step: Table User line number: 2048,limit: 1024, get Where condition (' The choice of name '  =  ' lisea ':1024 2017-10-27 05:40:14 34070 [note]  4th step: Start validation   The field name is not a 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]  5th step: Field name is not a primary key. Table name:user 2017-10-27 05:40:14 34070 [note]  6th step: Start validation   field name is not 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]  7th step: Field name is not a primary key. Table name:user 2017-10-27 05:40:14 34070 [note]  Step 8th: Start verifying that a related index already exists in the table. Table name:user,  The position of the field name:name,  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]  9th step: 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]  10th step:  sqladvisor End!

6. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1976570

MySQL--------SQL Optimization Audit tool combat