SQL Optimization · Classic Case · Index Chapter

Introduction

In the work of these years, due to the SQL problem caused by a number of database failures, the following six years of work encountered in the SQL problem summary collation, restore the original, give the analysis of the problem ideas and solve the problem of the method, to help users in the process of using the database can take a few detours. A total of four parts: index, SQL rewrite, parameter optimization, optimizer section Four, today will introduce the first part: Index chapter.

Index problems are the most frequently occurring in SQL problems, and common indexing problems include: No indexes, implicit conversions. When the database has access to the table of SQL No index results in a full table scan, if the data volume of the table is large, scan a large amount of data, application requests become slow to occupy the database connection, the connection accumulation will quickly reach the maximum number of connections to the database settings, new application requests will be rejected to cause the failure. Implicit conversions are when an incoming value in an SQL query condition is inconsistent with the data definition of the corresponding field, causing the index to be unusable. Common Hermit conversions The table structure of a field is defined as a character type, but the SQL pass-in value is a number, or the field definition collation is case-sensitive, and in a multi-table associated scenario, its table's associated field case sensitivity definitions vary. Implicit conversions can cause the index to become unusable, resulting in the number of slow SQL stacked database connections running full.

No index case:

Table structure

CREATE TABLE `user` (……mo bigint NOT NULL DEFAULT ‘‘ ,KEY ind_mo (mo) ……) ENGINE=InnoDB;SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1

Execution plan

mysql> explain  SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1;           id: 1  select_type: SIMPLE        table: user         type: ALLpossible_keys: NULL          key: NULL         rows: 707250         Extra: Using where

From the above SQL to see the execution plan all, represents the SQL execution plan is a full table scan, each execution needs to scan 707250 rows of data, which is very consumption performance, how to optimize? Add an index.
Verifying the filtering of MO fields

mysql> select count(*) from user where mo=13772556391;|   0    |

You can see that the filtering of the Mo field is very high, and further verification can be done through the select count (*) as all_count,count (distinct mo) as distinct_cnt from user, through-contrast All_ The values of count and distinct_cnt are compared, and it is very effective to add indexes on the Mo field if the all_cnt and distinct_cnt are very different.

Add index

mysql> alter table user add index ind_mo(mo);mysql>SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1;Empty set (0.05 sec)

Execution plan

mysql> explain  SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1\G;*************************** 1. row ***************************               id: 1      select_type: SIMPLE            table: user             type: index    possible_keys: ind_mo              key: ind_mo             rows: 1            Extra: Using where; Using index

Implicit conversion case One

Table structure

  CREATE TABLE `user` (  ……  mo char(11) NOT NULL DEFAULT ‘‘ ,  KEY ind_mo (mo)  ……  ) ENGINE=InnoDB;

Execution plan

mysql> explain extended select uid from`user` where mo=13772556391 limit 0,1;mysql> show warnings;Warning1：Cannot use  index ‘ind_mo‘ due to type or collation conversion on field ‘mo‘                                                                        Note：select `user`.`uid` AS `uid` from `user` where (`user`.`mo` = 13772556391) limit 0,1

How to Solve

mysql> explain   SELECT uid FROM `user` WHERE mo=‘13772556391‘ LIMIT 0,1\G;*************************** 1. row ***************************              id: 1     select_type: SIMPLE           table: user            type: ref   possible_keys: ind_mo             key: ind_mo            rows: 1           Extra: Using where; Using index

In the above case, because the table structure defines the MO field after the string data type, and the application passed in is a number, resulting in implicit conversions, the index is not available, so there are two scenarios:
First, change the table structure MO to the numeric data type.
Second, modify the app to change the character type passed in to the data type.

Implicit conversion case Two

Table structure

CREATE TABLE `test_date` (     `id` int(11) DEFAULT NULL,     `gmt_create` varchar(100) DEFAULT NULL,     KEY `ind_gmt_create` (`gmt_create`)) ENGINE=InnoDB AUTO_INCREMENT=524272;

5.5 Version Execution plan

mysql> explain  select * from test_date where gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 1 MINUTE) AND   DATE_ADD(NOW(), INTERVAL 15 MINUTE) ;+----+-------------+-----------+-------+----------------+----------------+---------+------+------+-------------+| id | select_type | table | type | possible_keys  | key | key_len | ref  | rows | Extra       |+----+-------------+-----------+-------+----------------+----------------+---------+------+------+-------------+|1|SIMPLE| test_date |range| ind_gmt_create|ind_gmt_create|303| NULL | 1 | Using where |

5.6 Version Execution Plan

mysql> explain select * from test_date where gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 1 MINUTE) AND   DATE_ADD(NOW(), INTERVAL 15 MINUTE) ; +----+-------------+-----------+------+----------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys  | key  | key_len | ref | rows | Extra|+----+-------------+-----------+------+----------------+------+---------+------+---------+-------------+| 1 | SIMPLE| test_date | ALL | ind_gmt_create | NULL | NULL | NULL | 2849555 | Using where |+----+-------------+-----------+------+----------------+------+---------+------+---------+-------------+|Warning|Cannot use range access on index ‘ind_gmt_create‘ due to type on field ‘gmt_create‘   

The above case is an implicit conversion that occurs after the user has upgraded to version 5.5 to 5.6, resulting in a database CPU pressure of 100%, so we must use the time-type data type when defining the Time field.

Implicit conversion case Three

Table structure

  CREATE TABLE `t1` (  `c1` varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,  `c2` varchar(100) DEFAULT NULL,  KEY `ind_c1` (`c1`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `t2` (  `c1` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  `c2` varchar(100) DEFAULT NULL,  

Execution plan

mysql> explain     select t1.* from  t2 left  join  t1 on t1.c1=t2.c1 where t2.c2=‘b‘;+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+| id | select_type | table | type | possible_keys |key| key_len | ref   | rows   | Extra    |+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+| 1 | SIMPLE | t2 | ref  | ind_c2 | ind_c2 | 303     | const |    258 | Using where ||1  |SIMPLE |t1  |ALL   | NULL   | NULL   | NULL    | NULL  | 402250 |    |

Modify COLLATE

mysql> alter table t1 modify column c1 varchar(100) COLLATE utf8_bin ;                Query OK, 401920 rows affected (2.79 sec)Records: 401920  Duplicates: 0  Warnings: 0

Execution plan

mysql> explain   select t1.* from  t2 left  join  t1 on t1.c1=t2.c1 where t2.c2=‘b‘;+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref | rows  | Extra       |+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+|  1 | SIMPLE| t2| ref | ind_c2| ind_c2 | 303     | const      |   258 | Using where ||  1 |SIMPLE| t1|ref| ind_c1  | ind_c1 | 303     | test.t2.c1 | 33527 |             |+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+

You can see that the collate of the field has been modified to use the index, so be aware that the definition of the Collate property of the table fields is consistent.

Common pitfalls of two indexes

• Myth One: Establish a single-column index for each field of the query condition, for example, the query condition is: a=? and b=? and c=?.
  An index of 3 single-column query criteria was created on the table ind_a (A), Ind_b (B), Ind_c (C), should be created with the appropriate single-column index or combined index based on the filtering of the condition.

• Myth Two: Set up a composite index on all fields of the query, for example, the query condition is select A,b,c,d,e,f from T where g=?.
  Ind_a_b_c_d_e_f_g (A,B,C,D,E,F,G) was created on the table.

Index Best Practices

• When using the index, we can view the SQL execution plan by explain+extended, determine whether the index is used, and an implicit conversion has occurred.
• Because the common implicit conversions are caused by improper field data types and collation definitions, we want to avoid the definition of database fields in the design development phase and avoid implicit conversions.
• Since MySQL does not support function indexing, you should avoid adding functions, such as Date (Gmt_create), to the query criteria at development time.
• All on-line SQL has to undergo rigorous audits to create the right index.

SQL Optimization · Classic Case · Index Chapter