大纲
最左前缀原则的介绍
最左前缀原则指的是：使用组合索引（联合索引）时，查询条件需要从索引的最左列开始匹配，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效 (这一列后面的字段的索引会失效)。比如，针对 A、B、C 三个字段建立了一个组合索引，那么在写一个 SQL 时就一定要提供 A 字段的查询条件，这样才能让组合索引生效。这是由于在建立 A、B、C 三个字段的组合索引时，MySQL 底层的 B+ 树是按照 A、B、C 三个字段从左往右去比较大小进行排序的，也就是说 B+ 树的索引结构依赖于从左到右逐层递进地有序搜索路径。如果查询条件中缺少了最左边的列，B+ 树将无法确定初始的搜索路径，从而无法利用索引进行高效查询。
最左前缀原则的使用
使用案例一
组合索引 + where 子句的使用
![]()
使用案例二
组合索引 + where 子句的使用
假设 MySQL 8.0 数据库中有一张表 t_user，表里面有 id、phone、name、age、status 这些字段，而且 phone、name、age 这三个字段创建了组合索引 idx_phone_name_age。
1
2
| # 创建组合索引
mysql> create index idx_phone_name_age on t_user(phone, name, age);
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
| # 索引不会失效，因为遵循最左前缀原则
mysql> explain select * from t_user where phone = '123456' and name = 'Amy' and age = 20;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ref | idx_phone_name_age | idx_phone_name_age | 221 | const,const,const | 1 | 100.00 | NULL |
+
# 索引不会失效，最左前缀原则和过滤条件的顺序无关
mysql> explain select * from t_user where name = 'Amy' and age = 20 and phone = '123456';
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ref | idx_phone_name_age | idx_phone_name_age | 221 | const,const,const | 1 | 100.00 | NULL |
+
# 索引部分失效，因为跳跃了 name 这里一列
mysql> explain select * from t_user where phone = '123456' and age = 20;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ref | idx_phone_name_age | idx_phone_name_age | 63 | const | 4 | 25.00 | Using index condition |
+
# 索引全部失效，因为跳跃了 phone 这里一列，将不遵循最左前缀原则
mysql> explain select * from t_user where name = 'Amy' and age = 20;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+
|
使用案例三
组合索引 + where 子句 + order by 子句的使用
假设 MySQL 8.0 数据库中有一张表 t_user，表里面有 id、phone、name、age、status 这些字段，而且 phone、name、age 这三个字段创建了组合索引 idx_phone_name_age。
1
| mysql> create index idx_phone_name_age on t_user(phone, name, age);
|
- 无过滤条件（如
where 条件，不包括 limit 条件），索引不会生效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| # 索引全部失效，因为没有过滤条件
mysql> explain select * from t_user order by phone, name, age;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using filesort |
+
# 索引全部失效，因为没有过滤条件，不包括 `limit` 条件
mysql> explain select * from t_user order by phone, name, age limit 5;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using filesort |
+
# 索引不会失效，因为有过滤条件
mysql> explain select * from t_user where phone = '123456' order by phone, name, age;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ref | idx_phone_name_age | idx_phone_name_age | 63 | const | 4 | 100.00 | NULL |
+
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| # 索引部分生效
mysql> explain select * from t_user where phone = '123456' and age > 20 order by phone, name, age;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ref | idx_phone_name_age | idx_phone_name_age | 63 | const | 4 | 33.33 | Using index condition |
+
# 索引完全生效，因为触发了覆盖索引
mysql> explain select phone, age from t_user where phone = '123456' and age > 20 order by phone, name, age;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ref | idx_phone_name_age | idx_phone_name_age | 63 | const | 4 | 33.33 | Using where; Using index |
+
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| # 排序字段的顺序和索引的顺序一致时，不会触发 `filesort`
mysql> explain select phone, age from t_user where phone = '123456' order by phone, name, age;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ref | idx_phone_name_age | idx_phone_name_age | 63 | const | 4 | 100.00 | Using index |
+
# 排序字段的顺序和索引的顺序不一致时，会触发 `filesort`
mysql> explain select phone, age from t_user where phone = '123456' order by phone, age, name;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ref | idx_phone_name_age | idx_phone_name_age | 63 | const | 4 | 100.00 | Using index; Using filesort |
+
|
- 多个索引字段排序时，排序方向（升序、降序）不一致对索引的影响
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| # 多个索引字段排序时，排序方向（升序、降序）一致，不会触发 `filesort`
mysql> explain select phone, age from t_user where phone = '123456' order by phone desc, name desc, age desc;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ref | idx_phone_name_age | idx_phone_name_age | 63 | const | 4 | 100.00 | Backward index scan; Using index |
+
# 多个索引字段排序时，排序方向（升序、降序）不一致，会触发 `filesort`
mysql> explain select phone, age from t_user where phone = '123456' order by phone desc, name asc, age desc;
+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+
| 1 | SIMPLE | t_user | NULL | ref | idx_phone_name_age | idx_phone_name_age | 63 | const | 4 | 100.00 | Using index; Using filesort |
+
|
最左前缀原则的原理
最左前缀原则指的是在使用组合索引时，查询必须从索引的最左边开始匹配。换句话说，如果针对 A、B、C 三个字段建立了一个组合索引，那么在查询中需要至少包含字段 A，才能有效地利用索引。具体原因可以从 B+ 树的结构来分析。
B+ 树的结构
B+ 树是一种平衡树，每个节点包含多个键，并且所有的叶子节点处在同一层。B+ 树有以下几个特性：
- 叶子节点之间形成一个有序的链表。
- 所有的值（Value）都只在叶子节点上出现。
- 非叶子节点只存储键（Key），用于指导搜索方向。
提示
更多关于 MySQL 索引底层的 B+ 树数据结构的介绍，请看 这里。
底层原理分析
- (1) B+ 树的键排序：B+ 树的非叶子节点包含键，这些键按照字典顺序排序。在组合索引 (A、B、C) 中，键的排序是先按照列 A 排序，在列 A 相同的情况下，再按照列 B 排序，最后按照列 C 排序。因此，B+ 树的节点实际上形成了一种层次排序结构。
- (2) 搜索路径的确定：在 B+ 树中进行搜索时，从根节点开始，根据节点中的键判断搜索路径。例如，根节点包含列 A 的不同值范围，如果查询条件中不包含列 A，B+ 树无法判断该走哪条路径，从而无法使用索引。
- (3) 最左前缀匹配：如果查询条件中包含了列 A，那么 B+ 树可以使用列 A 的值来定位到相应的子节点。如果查询条件进一步包含列 B 和列 C，B+ 树可以继续使用这些列的值来精确定位。但是，如果查询条件中缺少列 A，B+ 树将无法进行初步的路径选择，导致无法使用索引。
案例分析说明
假设有一个表 my_table，在 A、B、C 三个字段上建立了一个组合索引，数据如下：
B+ 树索引的结构如下：
1
2
3
4
5
6
7
| A
/ \
1 2
/ \ / \
B B B B
/ \ / \/ \ / \
2 3 1 2 1 2
|
- 查询
SELECT * FROM my_table WHERE A = 1 AND B = 2 可以有效利用索引，因为从根节点 A 出发，找到值为 1 的分支，再在 B 节点找到值为 2 的分支。 - 查询
SELECT * FROM my_table WHERE B = 2 则无法利用索引，因为无法确定从根节点 A 的哪个分支开始搜索。
总结
最左前缀原则之所以存在，正是因为 B+ 树的索引结构依赖于从左到右逐层递进地有序搜索路径。如果查询条件中缺少了最左边的列，B+ 树将无法确定初始的搜索路径，从而无法利用索引进行高效查询。
最佳实践
最佳实践一
组合索引的创建应该遵从以下规则：
- (1) 在创建组合索引的时候，过滤性（唯一性）最好的字段在索引字段顺序中，位置越靠左边越好
- (2) 组合索引出现范围查询时，应该尽量把这个字段放在索引顺序的最右边
- (3) 在创建组合索引的时候，应该尽量包含
where 子句中的字段 - (4) 应该尽量避免造成索引失效的情况
最佳实践二
索引的使用应该注意以下几点内容：
- (1) 如果排序字段不在索引列上，MySQL 就会触发
filesort，导致查询性能降低；有两种排序算法：单路排序和双路排序 - (2) 无过滤不索引，即如果没有过滤条件（如
where 条件，不包括 limit 条件），索引不会生效 - (3) 在 SQL 语句中，如果索引字段的顺序写错了，会触发
filesort - (4) 使用
order by 查询时，如果索引字段的位置非最左，会触发 filesort - (5) 使用
order by 查询时，如果索引字段的排序方向（升序、降序）不一致，会触发 filesort - (6) 熟练使用
explain，必要时使用 optimizer_trace
最佳实践三
利用最左前缀原则优化 Order By 语句，最关键的几点如下：
- (1) 在 SQL 语句中，一定要有过滤条件（如
where 条件），否则执行 order by 时索引会失效 - (2) 首先要对 SQL 进行分析，检查必要的查询字段、过滤字段、排序字段是否按顺序创建好了素引
- (3) 如果查询字段没有创建索引，可能会产生回表操作，导致触发
filesort，降低查询性能 - (4) 排序字段的顺序和索引的顺序不一致时，会触发
filesort，降低查询性能 - (5) 多个索引字段排序时，如果排序方向（升序、降序）不一致，会触发
filesort，降低查询性能 - (6) 使用
explain 观察查询类型和索引利用情况 - (7) 尽可能减少不必要的
filesort
