• Index failure 、 Not making full use of the index .—— Build a valid index
• There are many related query proposals join.—— Optimize SQL
• Server tuning and parameter settings .—— adjustment my.cnf
• Excessive data .—— Sub database and sub table

sql There are many techniques for query optimization , In the general direction, it can be divided into Physical query optimization and Logical query optimization Two blocks .

• Physical query optimization is through Indexes and Table joins Methods and other technologies , The key point is to master the use of index ;
• Logical query optimization is through sql Equivalent transformation To improve query efficiency , In other words, the execution efficiency of another query writing method may be higher ;

1、 Data preparation

1.1 Build table

First build ２ A watch ： Class table （class）、 Student list （student）

CREATE TABLE `class` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `className` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 `monitor` INT NULL ,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `student` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `stuno` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `classId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
 #CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1.2 Building storage functions

structure ２ Storage functions ： Generate random string function （rand_string）、 Generate random numbering function （rand_num）

SET GLOBAL log_bin_trust_function_creators=1; 

# Randomly generate strings 
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO  
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

# Used to randomly generate number to number 
DELIMITER //
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
DECLARE i INT DEFAULT 0;  
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
RETURN i;  
END //
DELIMITER ;

1.3 Build stored procedures

Build stored procedures , Go to class Table and student Insert data in the table

# To create stu A stored procedure that inserts data into a table 
DELIMITER //
CREATE PROCEDURE  insert_stu(  START INT ,  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    # Set up manual commit transactions 
 REPEAT  # loop 
 SET i = i + 1;  # assignment 
 INSERT INTO student (stuno, NAME ,age ,classId ) VALUES ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  # Commit transaction 
END //
DELIMITER ;


# Execute stored procedures , Go to class Add random data to table 
DELIMITER //
CREATE PROCEDURE `insert_class`(  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END //
DELIMITER ;

1.4 Execute stored procedures

Execute stored procedures ： Go to class Table to add 1 Ten thousand data 、 Go to stu Table to add 100 Ten thousand data

# Execute stored procedures , Go to class Table to add 1 Ten thousand data  
CALL insert_class(10000);

# Execute stored procedures , Go to stu Table to add 100 Ten thousand data  
CALL insert_stu(100000,1000000);

Check whether the insertion is successful ：

mysql> SELECT COUNT(*) FROM class;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.12 sec)

1.5 Build stored procedures for deleting indexes

For the convenience of deleting all indexes of a table later , Set up a stored procedure ：

DELIMITER //
CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
       DECLARE done INT DEFAULT 0;
       DECLARE ct INT DEFAULT 0;
       DECLARE _index VARCHAR(200) DEFAULT '';
       DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
# Each cursor must use a different declare continue handler for not found set done=1 To control the end of the cursor 
       DECLARE  CONTINUE HANDLER FOR NOT FOUND SET done=2 ;      
# If no data is returned , The program continues , And change the done Set to 2
        OPEN _cur;
        FETCH _cur INTO _index;
        WHILE  _index<>'' DO 
               SET @str = CONCAT("drop index " , _index , " on " , tablename ); 
               PREPARE sql_str FROM @str ;
               EXECUTE  sql_str;
               DEALLOCATE PREPARE sql_str;
               SET _index=''; 
               FETCH _cur INTO _index; 
        END WHILE;
   CLOSE _cur;
END //
DELIMITER ;

2、 Index failure 11 In this case

2.1 Match all values as much as possible

When there is no index , At this time, the index is not used for retrieval ：

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997565 |     0.10 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

１） To field age Build index idx_age, At this time, the query will use the index ：idx_age

mysql> CREATE INDEX idx_age ON student(age);
Query OK, 0 rows affected (7.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
+----+-------------+---------+------------+------+---------------+---------+---------+-------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+-------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age       | idx_age | 5       | const | 37626 |     1.00 | Using where |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+-------+----------+-------------+

２） Then check the field age, classId Build index idx_age_classid, At this time, the query will use the index ：idx_age_classid

mysql> CREATE INDEX idx_age_classid ON student(age,classId);
Query OK, 0 rows affected (10.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
+----+-------------+---------+------------+------+-------------------------+-----------------+---------+-------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys           | key             | key_len | ref         | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-------------------------+-----------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid | idx_age_classid | 10      | const,const |   22 |    10.00 | Using where |
+----+-------------+---------+------------+------+-------------------------+-----------------+---------+-------------+------+----------+-------------+

３） Then check the field age, classId, Name Build index idx_age_classid_name, At this time, the query will use the index ：idx_age_classid_name

mysql> CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);
Query OK, 0 rows affected (10.94 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                                | key                  | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age_classid_name | 73      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+

Therefore, when used AND when , If there are multiple indexes of multiple columns that can be matched , The optimizer will choose the one that matches the most .

2.2 Best left prefix match

When building a federated index , The retrieved data will match from the leftmost side of the union index .

１） Although in １ China has been built ３ An index ：idx_age,idx_age_classid,idx_age_classid_name. But this ３ The leftmost index column of each index is age, So search classid and name when , You cannot use indexes .

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997565 |     1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

For multi-column indexes , The index to be used for filter conditions must be in the order in which the index is created , Meet in turn , Once you skip a field , The fields after the index cannot be used .

2.3 Primary key insertion order

The order of inserting the primary key varies from big to small , Then it may happen Page splitting , In this way, there will be serious performance loss . Therefore, it is best to make the primary key value of the inserted record increase in turn .

2.4 Calculation 、 Function causes the index to fail

The use of functions will also result in invalid indexes ！

For example, give name Add index , Using functions sql But cannot use the index created .

mysql> CREATE INDEX idx_name ON student(NAME);
Query OK, 0 rows affected (11.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_name      | idx_name | 63      | NULL |   54 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997565 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

2.5 Index invalidation due to type conversion

Type conversion can also cause index invalidation .

Like the following name yes varchar type , Need first 123 Turn into ’123’, So there is no index .

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123; 
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | idx_name      | NULL | NULL    | NULL | 997565 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 4 warnings (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123'; 
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_name      | idx_name | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+

2.6 The column index to the right of the range condition fails

First, put student The indexes of the table are deleted ：

CALL proc_drop_index('dbtest1','student');

Then align the column age,classId,NAME Create a federated index , Search for sql Inside classId Dexter name The index is not used , because key_len Only 10, Because the column index on the right of the range condition fails .

mysql> CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);
Query OK, 0 rows affected (11.93 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student 
    -> WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ; 
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_age_classId_name | idx_age_classId_name | 10      | NULL | 37590 |    10.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+

Application development , If you want to query the range , Should be Range queries Put it in where Last .

At this point, first align the columns age,NAME,classId Create index , Then put the range query to the end , You can use all index columns .

mysql> CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);
Query OK, 0 rows affected (10.71 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student 
    -> WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;
+----+-------------+---------+------------+-------+---------------------------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                         | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------------------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_age_classId_name,idx_age_name_cid | idx_age_name_cid | 73      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------------------------------+------------------+---------+------+------+----------+-----------------------+

2.7 Not equal to index failure

Even for columns name Create an index , If used, it is not equal to , You can't use the index ：

mysql> CREATE INDEX idx_name ON student(NAME);
Query OK, 0 rows affected (7.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | idx_name      | NULL | NULL    | NULL | 997565 |    50.15 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

2.8 is null You can use index ,is not null Index not available

and 2.7 The principle is the same ,is not null You can't use indexes

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; 
+----+-------------+---------+------------+------+---------------------------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys                         | key                  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------------------------------+----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age_classId_name,idx_age_name_cid | idx_age_classId_name | 5       | const |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------------------------------+----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
+----+-------------+---------+------------+------+---------------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                         | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------------------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | idx_age_classId_name,idx_age_name_cid | NULL | NULL    | NULL | 997565 |    50.00 | Using where |
+----+-------------+---------+------------+------+---------------------------------------+------+---------+------+--------+----------+-------------+

therefore , When designing tables , It is best to set the field to NOT NULL constraint , For example, you can INT Type field , The default value is set to ０, Set the default value of string type to empty string ’'.

Empathy ,not like You can't use indexes , It can also lead to a full table scan .

2.9 like Use wildcards % Invalid start index

and 2.7 The principle is the same ,like Use wildcards % Index cannot be used at the beginning .

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%'; 
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_name      | idx_name | 63      | NULL | 1483 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997565 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

2.10 OR There are non indexed columns before and after , Index failure

OR Before and after ２ When all the columns in the condition are indexes , The query uses the index . Only one condition column is index , The query will still perform a full table scan .

2.11 The character set of database and table should be used uniformly utf8mb4

The unified character set can avoid the chaos caused by character set conversion , Different character sets need to be converted before comparison , Will cause index invalidation .