SQL Index Optimization Tool sqladvisor Introduction

Sqladvisor is an open source SQL Index optimization recommendation tool by the company's technical engineering Department DBA Team (Beijing) to develop and maintain an analytic SQL 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.

The initial installation and basic use of the tool is recorded in the blog post

I. Installation and deployment of software

  [[[email protected] opt]# pwd/opt[[email protected] opt]# yum install git[[email protected] opt]# git clone https://github.com/meituan-dianping/sqladvisor.git[[email protected] opt]# yum install CMake Libaio-devel libffi-devel glib2 glib2-devel[[email protected] opt]# yum install http://www.percona.com/downloads /percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm[[email protected] opt]# Yum Install Percona-server-shared-56[[email protected] opt]# cd/usr/lib64/[[email protected] opt]# ln-s libperconaserverclient_r.so.18 libperconaserverclient_r.so[[email protected] opt]# cmake-dbuild_config=mysql _release-dcmake_build_type=debug-dcmake_install_prefix=/usr/local/sqlparser./[[email protected] opt]# make && make install[[email protected] opt]# CD sqladvisor/sqladvisor[[email protected] sqladvisor]# Cmake-dcmake_build_type=debug./[[email protected] sqladvisor]# make  

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

The command parameters are as follows:

[[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

Second, the test

Test one: Test the small table

[[email protected] sqladvisor]#  ./sqladvisor -h 127.0.0.1 -P 3306 -u admin -p "[email protected]" -d zixun3 -q "select * from  

Of course, the above command can also be written to the configuration file, using the-f parameter to specify the file to manipulate

[[email protected] sqladvisor]# cat test01.cnf [sqladvisor]username=admin[email protected]host=127.0.0.1port=3306dbname=zixun3sqls=select * from  zx_addonarticle;

Test two: Have an index test on a large table

MySQL [zhangyou]> Show index from TEST_TABLE01; +--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+-- ------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------------+------------+-------------+--------------+-------------+-----------+------------ -+----------+--------+------+------------+---------+---------------+|          test_table01 | 0 |            PRIMARY | 1 | ID |      A |     120201 | NULL |      NULL | |         BTREE |               | ||          test_table01 | 0 |            Day | 1 | Day |          A |     10 | NULL |      NULL | |         BTREE |               | ||          test_table01 | 0 |            Day | 2 | Planid |        A |     1521 | NULL |      NULL | | BTREE |               | ||          test_table01 | 0 |            Day | 3 | UID |      A |     120201 | NULL |      NULL | |         BTREE |               | ||          test_table01 | 0 |            Day | 4 | SiteID |      A |     120201 | NULL |      NULL | |         BTREE |               | ||          test_table01 | 0 |            Day | 5 | ZoneID |      A |     120201 | NULL |      NULL | |         BTREE |               | ||          test_table01 | 1 |            Idx_day_uid | 1 | Day |         A |     112 | NULL |      NULL | |         BTREE |               | ||          test_table01 | 1 |            Idx_day_uid | 2 | UID |       A |     10927 | NULL |      NULL | |         BTREE |               | |+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+- -------+------+------------+---------+---------------+11 rows in Set (0.00 sec) 

  [[email protected] sqladvisor]# vim test.cnf [sqladvisor]username=admin[email protected]host= 127.0.0.1port=3306dbname=zhangyousqls=select * from test_table01 where day= ' 2016-04-22 ' and uid=26 ';  

[[email protected] sqladvisor]#./sqladvisor-f test.cnf-v 12018-06-14 18:08:24 1281 [Note] step 1th: sql:s after SQL parsing optimization Elect had some error 2018-06-14 18:08:24 1281 [Note] 2nd step: Start parsing conditions in Where: (' Day ' = ' 2016-04-22 ') 2018-06-14 18:08:24 1281 [No  TE] Show index from TEST_TABLE01 2018-06-14 18:08:24 1281 [Note] Show table status like ' Test_table01 ' 2018-06-14 18:08:24 1281 [Note] Select COUNT (*) from (select "Day" from "test_table01 ' Force INDEX" Day "order by day Desc,planid Desc,uid Desc,siteid Desc,zoneid Desc,adstypeid DESC limit 10000) ' test_table01 ' Where (' Day ' = ' 2016-04-22 ') 2018-06-14 18:08:24 1281 [Note] 3rd step: Table test_table01 rows: 120201,limit number of rows: 10000, gets the selection in the Where condition (' Day ' = ' 2016-04-22 '): 10000 2018-06-14 18:08:24 1281 [note] Step 4th: Start parsing the condition in Where: (' uid ' = ') 2018-06-14 18:08:24 1281 [note] Show index from TEST_TABLE01 2018-06-14 18:08:2 4 1281 [note] Show table status like ' test_table01 ' 2018-06-14 18:08:24 1281 [note] SELECT COUNT (*) from (SELECT ' UID ' fr Om ' test_table01 ' Force INDEX ( Day) Order by day Desc,planid desc,uid desc,siteid desc,zoneid desc,adstypeid DESC limit 10000) ' test_table01 ' Where (' u id ' = ') 2018-06-14 18:08:24 1281 [Note] 5th step: Table test_table01 rows: 120201,limit rows: 10000, gets the selection of the Where condition (' uid ' = 26): 10000 201 8-06-14 18:08:24 1281 [Note] Step 6th: Start verifying that the field day is not the primary key. Table name: test_table01 2018-06-14 18:08:24 1281 [Note] Show index from test_table01 where key_name = ' PRIMARY ' and column_name = ' Day ' and Seq_in_index = 1 2018-06-14 18:08:24 1281 [Note] 7th step: Field Day is not a primary key. Table name: test_table01 2018-06-14 18:08:24 1281 [Note] 8th step: Start verifying that the field day is not the primary key. Table name: test_table01 2018-06-14 18:08:24 1281 [Note] Show index from test_table01 where key_name = ' PRIMARY ' and column_name = ' Day ' and Seq_in_index = 1 2018-06-14 18:08:24 1281 [Note] 9th step: Field Day is not a primary key. Table name: test_table01 2018-06-14 18:08:24 1281 [Note] 10th step: Start verifying that a related index already exists in the table. Table name: test_table01, Field name: Day, position in index: 1 2018-06-14 18:08:24 1281 [Note] Show index from test_table01 where column_name = ' Day ' and Seq_in_index =1 2018-06-14 18:08:24 1281 [Note] Step 11th: Start validation The field UID is not a primary key. Table name: test_table01 2018-06-14 18:08:24 1281 [Note] Show index from test_table01 where key_name = ' PRIMARY ' and column_name = ' UID ' and Seq_in_index = 1 2018-06-14 18:08:24 1281 [Note] 12th step: The field UID is not a primary key. Table name: test_table01 2018-06-14 18:08:24 1281 [Note] 13th step: Start verifying that a related index already exists in the table. Table name: test_table01, field name: UID, position in index: 2 2018-06-14 18:08:24 1281 [Note] Show index from test_table01 where column_name = ' uid ' and Seq_in_index =2 2018-06-14 18:08:24 1281 [note] 14th step: Index (DAY,UID) already exists 2018-06-14 18:08:24 1281 [note] 15th step: Sqladvisor End  !

Test three: test for large tables without indexes

[[email protected] sqladvisor]# vim test.cnf [sqladvisor]username=admin[email protected]host=127.0.0.1port=3306dbname=zhangyousqls=select * from test_table01 where  zoneid=42 and views=198;

[[email protected] sqladvisor]#./sqladvisor-f test.cnf-v 12018-06-14 18:15:09 1348 [Note] step 1th: sql:s after SQL parsing optimization Elect ' * ' as ' * ' from ' zhangyou '. ' Test_table01 ' where (' zoneid ' = a) and (' views ' = 198)) 2018-06-14 18:15:09 1348 [Note 2nd Step: Start parsing the condition in Where: (' zoneid ' = 2018-06-14) 18:15:09 1348 [Note] Show index from TEST_TABLE01 2018-06-14 18:15:09 1348 [note] Show table status like ' test_table01 ' 2018-06-14 18:15:09 1348 [note] Select COUNT (*) from (SELECT ' ZoneID ' from ' TEST_TABLE01 ' Force INDEX [day] order by day Desc,planid desc,uid desc,siteid desc,zoneid desc,adstypeid DESC limit 10000 ) ' Test_table01 ' where (' zoneid ' = a) 2018-06-14 18:15:09 1348 [Note] 3rd step: Table test_table01 rows: 120201,limit rows: 10000, get where Selection of conditions (' zoneid ' = 42): 10000 2018-06-14 18:15:09 1348 [Note] 4th step: Start parsing conditions in Where: (' views ' = 198) 2018-06-14 18:15:09 1348 [N OTE] Show index from TEST_TABLE01 2018-06-14 18:15:09 1348 [Note] Show table status like ' Test_table01 ' 2018-06-14 18:15:0 9 1348 [Note] Select count(*) from (select "views" from ' test_table01 ' Force INDEX (day) Order by day Desc,planid Desc,uid Desc,siteid Desc,zoneid Desc,adstypeid DESC limit 10000) ' test_table01 ' where (' views ' = 198) 2018-06-14 18:15:09 1348 [Note] 5th step: Table Test_table01 Number of rows: 120201,limit: 10000, gets the selection of the Where condition (' views ' = 198): 2018-06-14 18:15:09 1348 [Note] 6th step: Start verifying that the field ZoneID is not the primary key. Table name: test_table01 2018-06-14 18:15:09 1348 [Note] Show index from test_table01 where key_name = ' PRIMARY ' and column_name = ' ZoneID ' and Seq_in_index = 1 2018-06-14 18:15:09 1348 [Note] 7th step: Field ZoneID is not a primary key. Table name: test_table01 2018-06-14 18:15:09 1348 [Note] 8th step: Start verifying that the field ZoneID is not the primary key. Table name: test_table01 2018-06-14 18:15:09 1348 [Note] Show index from test_table01 where key_name = ' PRIMARY ' and column_name = ' ZoneID ' and Seq_in_index = 1 2018-06-14 18:15:09 1348 [Note] 9th step: Field ZoneID is not a primary key. Table name: test_table01 2018-06-14 18:15:09 1348 [Note] 10th step: Start verifying that a related index already exists in the table. Table name: test_table01, field name: ZoneID, Position in index: 1 2018-06-14 18:15:09 1348 [Note] Show index from TEST_TABLE01 WHere column_name = ' ZoneID ' and Seq_in_index =1 2018-06-14 18:15:09 1348 [Note] 11th step: Start verifying that the field views are not the primary key. Table name: test_table01 2018-06-14 18:15:09 1348 [Note] Show index from test_table01 where key_name = ' PRIMARY ' and column_name = ' Views ' and Seq_in_index = 1 2018-06-14 18:15:09 1348 [Note] 12th step: Field views are not primary keys. Table name: test_table01 2018-06-14 18:15:09 1348 [Note] 13th step: Start verifying that a related index already exists in the table. Table name: test_table01, Field name: Views, position in index: 2 2018-06-14 18:15:09 1348 [Note] Show index from test_table01 where column_name = ' vie WS ' and Seq_in_index =2 2018-06-14 18:15:09 1348 [note] 14th step: Start output Table TEST_TABLE01 Index optimization recommendations: ~~*2018-06-14 18:15:09 1348 [note] C Reateindexsql:alter Table testtable01 Add index idxzoneidviews (zoneid,views) *~~2018-06-14 18:15:09 1348 [Note] 15th step: SQL  The Advisor is over!

As you can see, the recommendations for creating an index are given at the end

Test four: Multiple SQL simultaneous analysis:

[[email protected] sqladvisor]# cat /opt/SQLAdvisor/sqladvisor/test.cnf [sqladvisor]username=admin[email protected]host=127.0.0.1port=3306dbname=zhangyousqls=select * from test_table01 where  

Reference Post address: http://blog.51cto.com/suifu/1909169

SQL Index Optimization Tool sqladvisor Introduction