左匹配原则，聚集索引，回表查询，索引覆盖你真的懂了吗

最新推荐文章于 2024-09-12 22:31:31 发布

置顶大叔说码

最新推荐文章于 2024-09-12 22:31:31 发布

阅读量732

点赞数 1

分类专栏：数据结构 mysql 文章标签：索引左匹配聚集索引回表查询覆盖索引 mysql索引

本文链接：https://blog.csdn.net/CNYYGJ/article/details/105446410

版权

mysql 同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

数据结构

1 篇文章 0 订阅

订阅专栏

一个问题

有一张表test，这张表除了主键id外，还有a，b, c 三列

假设给这三个字段建一个复合索引 index_abc (a, b, c)，问，下面几种查询中，哪种查询会用到索引 index_abc ？

1. 查询一

select * from test where a > 1000 and b > 1000;

2. 查询二

select * from test where a > 1000 and c > 1000；

3. 查询三

select * from test where b > 1000 and c > 1000;

这是一个经典的面试题，由这个问题，我可以相关问你，什么是 左匹配原则？什么是 聚集索引？什么是 索引覆盖？什么是回表？

下面给大家捋一捋，以下试验基于MySQL5.7-InnoDB

左匹配原则

接着上面的问题，回到刚刚的三个查询上，首先，我们怎么知道查询有没有用到索引？有没有什么命令是可以帮助我们分析查询语句呢？答案当然是有的，那就 explain 命令

我们分别对上面的语句进行 explain，看看有哪些信息：

mysql> explain select * from test where a > 1000 and b > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

mysql> explain select * from test where a > 1000 and c > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

mysql> explain select * from test where b > 1000 and c > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | index_abc | 12      | NULL | 10120 |    11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+

我们可以看到，对查询语句执行 explain 后，返回了12列信息，各列说明如下：

Cloumn	Meaning
id	查询标识符
select_type	查询类型
table	输出行的表
partitions	匹配的分区
type	联接类型，确切的说是一种数据库引擎查找表的一种方式
possible_keys	可以选择的可能索引，但不一定被查询实际使用
key	实际选择的索引
key_len	所选键的长度
ref	与索引相比的列
rows	估计要查询的列
filtered	按表条件筛选的行百分比
Extra	其他信息

通常分析sql语句，我们只关注type，possible_keys，key，rows

对三条查询语句进行explain后，我们发现：

where a > 1000 and b > 1000 和 where a > 1000 and c > 1000条件的查询结果是一样的，其中type指明的索引查找方式为range，possible_keys 可能使用的索引为 index_abc，key 实际使用的索引为 index_abc
where b > 1000 and c > 1000 条件的查询中，type的值为index，possible_keys为NULL，key的值为 index_abc

上面的range 和 index有什么区别呢？

range：仅检索给定范围内的行，使用索引选择行
index：索引联接类型与 ALL 相同，只不过扫描索引树，有两种情况：
- 如果索引是查询的 覆盖索引（后文有讲），并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，"额外"（Extra）列表示使用索引。仅索引扫描通常比全部扫描快，因为索引的大小通常小于表数据
- 使用索引中的读取执行完整的表扫描，以按索引顺序查找数据行。使用索引不显示在"额外"列中，也就是说：如果不是覆盖索引，使用索引不显示在"额外"列中

换句话说，

range是使用了索引，并且能够在对应的索引树上使用快速查找的方法进行快速查找，是有范围的查找，使用了range，就一定用到了我们建的索引，而index只能是通过扫描整个索引树

上面也提到ALL，那么type还有哪几种比较常见的值呢？下面列举一下（具体其他类型值，看以参考官方文档）：

system：该表只有一行（= 系统表）。这是 const 联接类型的特殊情况
const：表示通过索引一次就找到了，因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量表最多有一个匹配行，在查询开始时读取该行。由于只有一行，因此优化器的其余部分可以将该行中的列中的值视为常量。将主键或 UNIQUE 索引的所有部分与常量值进行比较时，将使用 const
eq_ref：唯一性索引扫描，对于前一表中的每一行组合，将从此表中读取一行，常见于主键或唯一索引扫描。除了system 和 const 类型之外，这是最佳联接类型
ref：非唯一性索引扫描，对于前一表中的每一行组合，将从此表中读取具有匹配索引值的所有行
ALL：将遍历全表以找到匹配的行

好，回到上面三条查询语句上，为什么where条件为a > 1000 and b > 1000 和 a > 1000 and c > 1000 的 type 是 range（用到索引）, 而where条件为 b > 1000 and c > 1000 的 type 是 index 呢？这里面索引树（B+树）的构建方式及存储结构有关

那么复合索引B+树是怎样的呢？看图，一图胜百字

对于索引来说只不过比单值索引多了几列，而这些索引列全都出现在索引树上。对于复合索引，存储引擎会首先根据第一个索引列排序，如上图我们可以单看第一个索引列，如，1 1 4 15 18....他是单调递增的；如果第一列相等则再根据第二列排序，依次类推就构成了上图的索引树

以创建的索引 index_abc (a, b, c)为例，如上图所示，每个结点都有三个键值，从上往下分别对应这a，b，c三个索引列

构造索引树时，首先使用多列索引的第一列构建的索引树，以 index_abc (a, b, c) 为例就是优先使用a列构建，当b列值相等时再以c列排序

因此，索引的第一列也就是a列可以说是从左到右单调递增的，但我们看b列和c列并没有这个特性，它们只能在a列值相等的情况下这个小范围内递增，看上图的左下角的结点可理解这点

划重点：由于复合索引树建的时候就是按照当初你建立索引时（index_abc (a, b, c)）对应索引列的顺序从左到右来建的，因此你使用的时候你也得按照从左到右的规则来用，这就是索引的 左匹配原则

所以为什么上面 where a > 1000 and b > 1000 和 where a > 1000 and c > 1000 条件查询的type是range，而 where b > 1000 and c > 1000 的type是index 你明白来吗？

回表，聚集索引

我们都知道，B+树有个特点就是，其叶子结点存的是关键字和数据，非叶子结点存的都是索引关键字，那么复合索引构造的B+树中，其叶子结点存的数据是什么呢？答案该条数据的主键值

划重点：也就是说，利用复合索引查找数据的流程是，先在复合索引的B+树上找到对应数据的主键值（ID，注：MyISAM的索引叶子节点存储记录指针）,然后再根据这个主键（ID）值，到主键索引树（B+树）上查找这个ID所在的行记录（主键索引树的页子结点存储的关键字和对应的行记录数据），最后查找结束。这个查找流程操作也叫 回表查询

有没有注意到，B+树中，有的叶子结点存储的行记录，有点存储的是主键值

划重点：

叶子结点存储行记录的索引又叫 聚集索引，InnoDB必须要有，且只有一个聚集索引：
- 如果定义了主键，则主键索引就是 聚集索引
- 如果没有定义主键，则第一个not NULL unique列是聚集索引
- 否则，InnoDB会创建一个隐藏的row-id作为聚集索引
叶子结点存储主键值叫普通索引，也叫 非聚集索引

覆盖索引

还是上面的例子，我们再次看一下 where条件为 b > 1000 and c > 1000 的查询 explain后的信息

mysql> explain select * from test where b > 1000 and c > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | index_abc | 12      | NULL | 10120 |    11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+

按照我们刚刚讲的索引的 左匹配原则，这个查询应该没有有效用上我们建的索引 index_abc ，为什么key（实际使用到的索引）列却是 index_abc？这里就涉及到了 覆盖索引

什么是覆盖索引？覆盖索引 就是：SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据（即回表查询）

不难理解，因为我们的test表本来就只有四个字段，id, a, b, c，其中（a, b, c）建立列索引，id又是主键，复合索引树的叶子结点存的就是主键值，所以 select * from test where b > 1000 and c > 1000 查找的数据通过复合索引树就可以全部得到，不需要回表，因此这里面用到了索引，这个索引树实际是什么索引的索引树呢？，当然是index_abc了，因为b, c 列包含在复合索引列中

为什么possible_keys列（可能使用到的索引）为NULL，因为搜索引擎找不到以b列开头的索引

所以，使用列索引覆盖，Extra列也就有列Using index

最后，为什么 a > 1000 and b > 1000 和 b > 1000 and a > 1000，explain的结果一样呢？

mysql> explain select * from test where a > 1000 and b > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

mysql> explain select * from test where b > 1000 and a > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

这就该我们mysql 查询优化器 干活了，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

至此，索引的左匹配原则，聚集索引，回表查询，覆盖索引就分享完了

如有不妥之处，欢迎指正，交流