大家好，这里是编程Cookbook，关注公众号「编程Cookbook」，获取更多面试资料。本文详细介绍MySQL索引的关键潜规则——最左前缀原则。
图示单值索引和联合索引
关注公众号「编程Cookbook」，获取更多编程学习/面试资料！
单值索引
- 单值索引（唯一索引、主键索引、全文索引等） 是指在数据库表中创建的、仅涉及单个列的索引。也就是说，单值索引是基于表中的单一列（例如，单个字段）创建的索引结构。单值索引底层的 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+ 树中。
最左前缀原则
关注公众号「编程Cookbook」，获取更多编程学习/面试资料！
- 最左优先：以最左边的字段为起点，任何连续的索引都能匹配上。
- 范围查询字段后停止：例如遇到
>、<、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的范围查找记录后，继续逐条过滤。
- 只有对索引最左边的列进行范围查找才用到 B+ 树索引。因此，只有
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： 会使用联合索引。联合索引左边列为常量，后面的列排序可以用到索引。
总结
- 联合索引 是通过多列的值来构建 B+ 树，而数据库通过 最左匹配原则 来利用这些索引。
- 查询时要注意查询条件的顺序和是否符合最左匹配原则，以便有效利用索引。
- 对于范围查询，只有最左侧的字段可以进行范围查询，后续字段则会失去索引效用。
关注公众号「编程Cookbook」，获取更多编程学习/面试资料！
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
