"MySQL Advanced" index analysis and optimization notes (on)

Studying "MySQL Advanced" teacher Gao Yang's notes on the index course, this article focuses on the analysis of where index

Build a table

Operating environment: MySQL 5.7.32

# 建表
CREATE TABLE test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);

insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

select * from test03;

# 建立 c1, c2, c3, c4 复合索引
create index idx_test03_c1234 on test03(c1, c2, c3, c4);
show index from test03;

Index analysis

explain runs on MySQL 5.7.32

explain select * from test03 where c1 = 'a1';
explain select * from test03 where c1 = 'a1' and c2 = 'a2';
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3';
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';

1. explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';

+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref                     | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 164     | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+

Full value matching, the query uses the indexes of all 4 fields (compound index is used to judge by the key field, the type type is ref, and 4 indexes are used for judgment by the ref field)

2. explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c4 = 'a4' and c3 = 'a3';

+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref                     | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 164     | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+

Full value matching, the query uses the indexes of all 4 fields, indicating that it has nothing to do with the writing order of the fields, as long as there is no break in the middle.

3. explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 > 'a3' and c4 = 'a4';

+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 123     | NULL |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+

The query uses the indexes of the three fields c1, c2, and c3 (the composite index is used to judge by the key field, the type type is range, and the three indexes are used to judge by the index length key_len). Since c3 is the range, the c4 index is invalid. To (all invalid after the range), the type also changed from ref to range.

4. explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c4 > 'a4' and c3 = 'a3';

+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 164     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+

The query uses four indexes c1, c2, c3, and c4 (judged by the index length key_len). Since c4 is a range, the type type has also changed from ref to range.

5. explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c4 = 'a4' order by c3;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+

The query uses the indexes of the two fields c1 and c2;
When sorting, since the c1, c2, c3, and c3 indexes can be used for sorting, file sorting (filesort) will not appear.

6. explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by c3;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+

The query uses the indexes of the two fields c1 and c2;
When sorting, since the c1, c2, c3, and c3 indexes can be used for sorting, file sorting (filesort) will not appear.

7. explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by c4;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+

The indexes of the two fields c1 and c2 are used in the query;
When sorting, because c3 is broken between c4 and c1 and c2, index sorting cannot be used, and file sorting will occur.

8.1 explain select * from test03 where c1 = 'a1' and c5 = 'a5' order by c2, c3;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+

Only use the index of one field in c1 when querying;
When sorting, the c1, c2, and c3 indexes are used for sorting, so there is no filesort (file sorting).

8.2 explain select * from test03 where c1 = 'a1' and c5 = 'a5' order by c3, c2;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+

Use the index of c1 when querying;
When sorting, c3 and c2 do not conform to the order of the composite index, so index sorting cannot be used, and filesort will appear.

9. explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by c2, c3;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+

Use the index of the c1 and c2 fields when querying;
When sorting, the c1, c2, and c3 field indexes are used for sorting, so there is no filesort.

10.1 explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c2, c3;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+

Use the index of the c1 and c2 fields when querying;
When sorting, the c1, c2, and c3 field indexes are used for sorting, so there is no filesort.

10.2 explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c3, c2;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 82      | const,const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+

Use the index of the c1 and c2 fields when querying;
When sorting, the c3 field index is used for sorting, and the c2 field is already a fixed value and does not need to be sorted, so there is no filesort.

10.3 explain select * from test03 where c1 = 'a1' and c5 = 'a5' order by c3, c2;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+

The c1 index is used when querying;
When sorting, c1, c3, and c2 are not in the order of the compound index, so filesort will appear when sorting.

11. explain select * from test03 where c1 = 'a1' and c4 = 'a4' group by c2, c3;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+

The c1 index is used in the query, and the gap between c4 and c1 is broken, so the query only uses the c1 index;
group by groups basically need to be sorted, and can be analyzed by order by.
When sorting, c1, c2, and c3 conform to the order of the composite index, so filesort will not appear when sorting, and no temporary tables will appear in grouping.

12. explain select * from test03 where c1 = 'a1' and c4 = 'a4' group by c3, c2;

+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                                  |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 41      | const |    1 |    20.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+

The c1 index is used in the query, and the gap between c4 and c1 is broken, so the query only uses the c1 index;
When sorting, c1, c3, and c2 are not in the order of the composite index, so filesort will appear when sorting, and temporary tables will also appear in grouping.

Summary of composite index index usage

Where statement	Whether the index is used
where a = 3	Y, use a
where a = 3 and b = 5	Y, use a, b
where a = 3 and b = 5 and c = 4	Y, use a, b, c
where b = 3 or where b = 3 and c = 4 or where c = 4	N
where a = 3 and c = 5	A is used, but c cannot be used because b is broken in the middle
where a = 3 and b > 5 and c = 5	A and b are used, because c cannot be used after the range, b is broken (all after the range is invalid)
where a = 3 and b like 'kk%' and c = 4	Y, a, b, and c are used ( is different from the range of greater than less than sign, and the index can be used here)
where a = 3 and b like '%kk' and c = 4	Y, only a is used
where a = 3 and b like '%kk%' and c = 4	Y, used a
where a = 3 and b like 'k%kk%' and c = 4	Y, used a, b, c

Summary of optimization formulas

The full value matches my favorite, and the leftmost prefix must be obeyed;
The first brother can't die, and the middle brother can't be broken;
Less calculation on the index column, all invalid after the range;
Write the LIKE percentage to the right, and the coverage index does not write the star;
Not equal to null value and or, use less for index invalidation;

[Reference]

Station B "MySQL Advanced" 44. Index
Station B "MySQL Advanced" 45. Index optimization Q&A supplement and summary formula
MySQL Advanced (Gao Yang) Table Building SQL Statement Encyclopedia