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

Welcome to reprint, please indicate the author and source. Author: Liu Chunlei blog: http://blog.itpub.net/28823725/

If you have any questions, please leave a message.

SQLAdvisor research and application-- 2017-05-23 Liu Chunlei, demand 1.1, reducing the number and impact of slow queries [1], to reduce the number and impact of online slow queries [2], to reduce the complexity of daily DBA optimization [3], to facilitate, fast and automatically optimize slow SQL [4], to visually display slow SQL results, etc.

II. Introduction of SQLAdvisor2.1 and SQLAdvisor

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 human intervention can be reduced, the work efficiency of DBA will undoubtedly be greatly improved.

SQLAdvisor is a SQL optimization tool developed and maintained by the DBA team of Meituan Dianping Company (Beijing): 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. At present, SQLAdvisor is widely used in the company, which is more mature and stable.

Meituan Dianping is committed to building SQLAdvisor into a highly intelligent SQL optimization tool, choosing to use a more mature and stable SQLAdvisor project open source, github address within the company. Hope to work with teams with similar needs in the industry to create an excellent SQL optimization product.

2.2.2.The SQLAdvisor structure flow chart

SQLAdvisor support SQLinsert, update, delete, select, insert select, select join, update T1 T2 and other common SQL support

2.4. SQLAdvisor Note: subqueries, or conditions, and conditions for using functions in SQL ignore not handling sql parameters passed in on the command line, and note that double quotes and backquotes in sql need to be escaped with\. It is recommended that you call the

2.5. SQLAdvisor installation 2.5.1, download package SQLAdvisor-master.zip

Percona-Server-shared-56-5.6.35-rel81.0.el6.x86_64.rpm

2.5.2. Install the dependency package:

Yum install-y cmake libaio-devel libffi-devel glib2 glib2-devel yum-y install Percona-Server-shared-56-5.6.35-rel81.0.el6.x86_64.rpm

Note: cd / usr/lib64 ln-s libperconaserverclient_r.so.18 libperconaserverclient_r.so

Install SQLAdvisor to extract: unzip SQLAdvisor-master.zip cmake-DBUILD_CONFIG=mysql_release-DCMAKE_BUILD_TYPE=debug-DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser. / make & & make install cd SQLAdvisor/sqladvisor cmake-DCMAKE_BUILD_TYPE=debug. / make sqladvisor command file appears and no error is reported to install the correct cp sqladvisor / usr/bin/sqladvisor

2.5.3. SQLAdvisor uses sqladvisor-- help

Usage:

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.5.4, test use

[root@dbmon SQLAdvisor] # sqladvisor-u * *-p * *-h * *-P 6001-d my-Q "select * from my_db where port=6001"-v 1

2017-05-04 11:42:03 27943 [Note] step 1: optimize the SQL:select `* `AS` * `from `my`.`my _ db`where (`port` = 6001) obtained by optimizing SQL resolution

2017-05-04 11:42:03 27943 [Note] step 2: start parsing the conditions in where: (`port` = 6001)

2017-05-04 11:42:03 27943 [Note] show index from my_db

2017-05-04 11:42:03 27943 [Note] show table status like 'my_db'

2017-05-04 11:42:03 27943 [Note] select count (*) from (select `port`from `my_ db` FORCE INDEX (ux_hostportservicename) order by host DESC,port DESC limit 353) `my_ db` where (`port` = 6001)

2017-05-04 11:42:03 27943 [Note] step 3: number of rows of table my_db: 707 lines of my_db: 353. get the choice in the where condition (`port` = 6001).

2017-05-04 11:42:03 27943 [Note] step 4: start verifying that the field port is the primary key. Table name: my_db

2017-05-04 11:42:03 27943 [Note] show index from my_db where Key_name = 'PRIMARY' and Column_name =' port' and Seq_in_index = 1

2017-05-04 11:42:03 27943 [Note] step 5: field port is not a primary key. Table name: my_db

2017-05-04 11:42:03 27943 [Note] step 6: start verifying that the field port is the primary key. Table name: my_db

2017-05-04 11:42:03 27943 [Note] show index from my_db where Key_name = 'PRIMARY' and Column_name =' port' and Seq_in_index = 1

2017-05-04 11:42:03 27943 [Note] step 7: field port is not a primary key. Table name: my_db

2017-05-04 11:42:03 27943 [Note] step 8: start verifying that the relevant index already exists in the table. Table name: my_db, field name: port, position in the index: 1

2017-05-04 11:42:03 27943 [Note] show index from my_db where Column_name = 'port' and Seq_in_index = 1

2017-05-04 11:42:03 27943 [Note] step 9: start exporting table my_db index optimization recommendations:

2017-04 11:42:03 27943 [Note] Create_Index_SQL:alter table my_db add index idx_port (port)

2017-05-04 11:42:03 27943 [Note] step 10: end of SQLAdvisor!

III. SQLAdvisor Automation, platform 3.1, Architecture

Note:

Use django and bootstrap,html to make it a platform

Automate by using python scripts, etc.

3.2. The implementation of the slow log online display and analysis platform is 3.2.1 and the platform interface

3.2.2. The specific implementation is to obtain the instance information and select the instance selection time according to the input library name. By default, the number of data items selected from 5 hours before the current time to the current time is 50 by default. Use pt-query-digest to analyze the slow log entry table: my_query_review_once,my_query_review_history_once SQLAdvisor analysis results, give recommendations, input table: my_auto_tuning_once

3.2.3. Use the input library name, select submit button, select instance, select sorting method, select time (default 5 hours ago to current time), number of entries (default 50), and the results are as follows:

3.3. Implementation of slow log statistical analysis platform 3.3.1, interface

3.3.2, the specific implementation of the use of pt-query-digest analysis of slow logs into the database, using SQLAdvisor analysis

Store the results in the table: my_auto_tuning,my_auto_tuning_history uses the django front end to display the results 3.3.3, log in to the django test platform and click on the platform-- > [6] to automatically optimize the analysis results.

3.4.The online SQL optimization analysis platform implements 3.4.1, interface 3.4.2, and inputs the library name. According to the library name, the instance information is obtained according to the input SQL, and the cluster number is analyzed by SQLAdvisor. The results are stored in the database and displayed by the front end of django.

3.4.3. Use the input library name, check the library name, enter SQL, and click submit for the cluster number.

The output is as follows:

Fourth, the advantages do not affect the current architecture, rapid deployment online

Convenient for DBA optimization, intuitive output, automatic analysis, report display, email to the development platform for operation, convenient and fast

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.