MySQL数据库笔记——最左前缀原则原理及其注意事项

大家好，这里是Good Note，关注公主号：Goodnote，专栏文章私信限时Free。本文详细介绍MySQL索引的关键潜规则——最左前缀原则。

在这里插入图片描述

文章目录

图示单值索引和联合索引
单值索引
联合索引

最左前缀原则
示例分析
1. 全值匹配查询时
2. 匹配左边的列时
3. 匹配列前缀（%）
4. 匹配范围值
5. 精确匹配某一列并范围匹配另外一列
6. 排序

总结

历史文章

图示单值索引和联合索引

单值索引

单值索引（唯一索引、主键索引、全文索引等） 是指在数据库表中创建的、仅涉及单个列的索引。也就是说，单值索引是基于表中的单一列（例如，单个字段）创建的索引结构。单值索引底层的 B+ 树如下所示：

联合索引

与单值索引只在一个列上建立，联合索引建在多个列上的索引，通常用于优化多列查询。当查询条件中涉及多个列时，数据库引擎会使用最适合的索引来提高查询效率。
联合索引的列顺序非常重要，通常是根据查询中最常用的过滤列进行排序。

联合索引的结构依旧是一颗 B+ 树，只不过联合索引的键值数量不是一个，而是多个，如下图所示：
在这里插入图片描述

当索引为联合索引时，数据库会依据联合索引最左的字段来构建 B+ 树，也叫 最左前缀匹配原则。

在上图中，我们假如创建一个 (a, b) 的联合索引，其中，a, b 排序分析为：

a顺序：1，1，2，2，3，3
b顺序：1，2，1，4，1，2

可以发现：

a字段是有序排列，b字段是无序排列（因为 B+ 树只能选一个字段来构建有序的树）。
但在 a 相等的情况下，b 字段是有序的。

这符合我们的编程习惯，在平时的编程中，我们常常需要对两个字段进行排序：

首先按照第一个字段排序。
如果第一个字段相等，则用第二个字段排序。

这种排序思想同样被用到了 B+ 树中。

最左前缀原则

最左优先：以最左边的字段为起点，任何连续的索引都能匹配上。
范围查询字段后停止：例如遇到 >、<、BETWEEN、LIKE之后，下一个列就不会再使用索引进行匹配。

下面将通过一些例子，来讲清楚最左前缀原则应该遵守的规则。

示例分析

我们创建联合索引 (a, b, c)：

CREATE INDEX idx_abc ON table_name (a, b, c);

1. 全值匹配查询时

例如：

select * from table_name where a = '1' and b = '2' and c = '3'
select * from table_name where b = '2' and a = '1' and c = '3'
select * from table_name where c = '3' and b = '2' and a = '1'

这些查询都能用到索引，因为查询条件顺序调换不影响查询结果，因为 MySQL 中有查询优化器，会自动优化查询顺序。

2. 匹配左边的列时

例如：

select * from table_name where a = '1'
select * from table_name where a = '1' and b = '2'
select * from table_name where a = '1' and b = '2' and c = '3'

这些查询都从最左边开始连续匹配，用到了索引。

但如果查询条件没有从最左边开始，则不会使用联合索引，查询会转为全表扫描：

select * from table_name where b = '2'
select * from table_name where c = '3'
select * from table_name where b = '1' and c = '3'

这些没有从最左边开始，最后查询没有用到索引，用的是全表扫描。

select * from table_name where a = 1 and c = 3

部分索引，只有 a 列用到了索引，c 列无法使用索引。

3. 匹配列前缀（%）

对于字符型字段的匹配：

如果查询条件是前缀匹配（例如 a like 'As%'），可以使用索引，因为前缀是有序的。
如果查询条件是后缀或中缀匹配（例如 a like '%As' 或 a like '%As%'），则不能使用索引，需要全表扫描。

例子：

select * from table_name where a like 'As%'（前缀匹配，走索引查询）
select * from table_name where a like '%As'（全表查询）
select * from table_name where a like '%As%'（全表查询）

4. 匹配范围值

例如：

select * from table_name where a > 1 and a < 3：一个列进行范围查询，前缀匹配，走索引查询。
select * from table_name where a > 1 and a < 3 and b > 1：多个列同时进行范围查找时。
- 只有对索引最左边的列进行范围查找才用到 B+ 树索引。因此，只有 a 列用到了索引，b 列无法使用索引，查询会基于 1 < a < 3 的范围查找记录后，继续逐条过滤。

5. 精确匹配某一列并范围匹配另外一列

如果左边的列是精确查找，右边的列可以进行范围查找。例如：

select * from table_name where a = 1 and b > 3

在这种查询中，a = 1 的情况下，b 字段是有序的，可以进行范围查找，联合索引会加速查询。

6. 排序

order by的子句后面的顺序也必须按照索引列的顺序给出，比如：

select * from table_name order by a,b,c limit 10：使用索引。因为 B+ 树索引本身就是按照顺序排序的，所以可以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列。
select * from table_name order by b,c,a limit 10：这种查询没有按照索引列的顺序进行排序，所以无法使用索引。
select * from table_name order by a limit 10 和 select * from table_name order by a,b limit 10;：只用到了部分索引。
select * from table_name where a = 1 order by b,c limit 10：会使用联合索引。联合索引左边列为常量，后面的列排序可以用到索引。