mysql索引最左前缀法则、使用场景

提示：mysql索引最左前缀使用的规则，以及索引失效和部分字段索引失效

---

索引使用法则

最左前缀法则

联合索引（多列索引）要遵守最左前缀法则(最左边的字段必须存在,跳过某一字段后面字段索引失效)造成索引失效或者部分索引失效
1、创建表

city  | CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Info` json DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
1
2
3
4
5
6
7
8

2、创建联合索引（多列索引）

mysql> create index idx_Name_CountryCode_District on city (Name,CountryCode,District);

Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

1
2
3
4
5

create index idx_字段1_字段2......字段n

3、查看索引
show index city;

| Table | Non_unique | Key_name                      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| city  |          0 | PRIMARY                       |            1 | ID          | A         |        4079 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| city  |          1 | idx_Name_CountryCode_District |            1 | Name        | A         |        3998 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| city  |          1 | idx_Name_CountryCode_District |            2 | CountryCode | A         |        4056 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| city  |          1 | idx_Name_CountryCode_District |            3 | District    | A         |        4078 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+


1
2
3
4
5
6
7
8
9

Name，CountryCode，District三个字段创建的联合索引正常显示！

上述步骤创建联合索引以及查看联合索引等工作完成，

索引执行explain分析

遵守联合索引最左前缀法则（索引有效）

1、全表执行计划查询

mysql> explain select * from city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4079 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


1
2
3
4
5
6
7
8
9

type:全表扫描
key：未使用到索引

2、第一个索引字段Name执行查询：

mysql> explain select * from city where Name='Jabaliya'
    -> ;
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | idx_Name_CountryCode_District | idx_Name_CountryCode_District | 140     | const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8
9

key：使用的所以有Name_CountryCode_District
key_len;索引长度=140也是Name索引长度=140

3、前两个字段Name、CountryCode索引查询执行

mysql> explain select * from city where Name='Jabaliya'and CountryCode='VIR';
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | idx_Name_CountryCode_District | idx_Name_CountryCode_District | 152     | const,const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------+------+----------+-----------------------+

1
2
3
4
5
6
7

key_len：152，上述Name索引字段为140，CountryCode字段索引长度等于152-140=12

4、三个字段Name、CountryCode、District索引查询执行

mysql> explain select * from city where Name='Jabaliya'and CountryCode='VIR' and District='Manicaland';
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref               | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | idx_Name_CountryCode_District | idx_Name_CountryCode_District | 232     | const,const,const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8

key_len:District索引长度等于=232-152

上述三种索引查询情况下索引都正常履行了自身的职责，遵守了联合索引（多列索引）的最做前缀法则，所有索引都正常，未造成索引失效

未遵守联合索引最左前缀法则（索引失效或部分索引失效）

1.第一个索引字段、第三个索引字段联合查询执行：

mysql> explain select * from city where Name='Jabaliya'and  District='Manicaland';
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | idx_Name_CountryCode_District | idx_Name_CountryCode_District | 140     | const |    1 |    10.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------+------+----------+-----------------------+

1
2
3
4
5
6
7

当执行第一个字段索引和第三个字段索引时，跳过第二个字段索引进行查询的时，key_len显示为140，和使用explain select * from city where Name='Jabaliya’查询的索引长度一样，name的索引字段等于140，而District索引长度未0，表示District字段索引失效了，当联合索引跳过中间索引时会造成部分索引失效

2、第二个索引字段和第三个索引字段查询执行

mysql> explain select * from city where  CountryCode='VIR' and District='Manicaland';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4079 |     1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

1
2
3
4
5
6
7
8

key：null 未使用到索引，
key_len：null
跳过第一个字段索引未遵守联合索引最左前缀法则，造成CountryCode、District索引已失效，

思考

 explain select * from city where  District='Manicaland' and Name='Nablus' and  CountryCode='PSE';
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys                 | key                           | key_len | ref               | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | idx_Name_CountryCode_District | idx_Name_CountryCode_District | 232     | const,const,const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------------------+-------------------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

总结：执行查询时只要遵守联合索引最左前缀法则和索引查询时的前后位置无任何关系！联合索引遵守idx_Name_CountryCode_District顺序，和 explain select * from city where District=‘Manicaland’ and Name=‘Nablus’ and CountryCode=‘PSE’;无关系，只要查询中遵守最左前缀法则即可