0

对InnoDB 覆盖索引的疑问
首先, 我对覆盖索引的了解是, 如果创建了二级索引后, 要想做到覆盖索引, 那么你 select 的列就要被你的索引所覆盖 比如,当我创建一个二级索引 idx_name_age(name, age)
select name from user where name = "Jhon"; 语句, 自然就可以做到覆盖索引
这样就不用再回表走聚簇索引了

但是 当我 select id from user where id = 1; 时, 这个用到了主键索引, 也就是会直接走聚簇索引, 既然走聚簇索引的话, B+Tree的叶子节点是包含所有列的, 那我认为 select * from user where id = 1; 也是一样的

但explain的分析结果发现 select id from user where id = 1; 是主键索引, 并且做到了覆盖索引
但是 select * from user where id = 1; 虽然是主键索引, 但却不是覆盖索引, 这是为啥?

wahaha 233
2019-04-26 提问
3 个回答
1

已采纳

可以这样理解吧,
主键索引,那么就是按照主键排序,组成B+树,例如你的id,那么主键索引的索引键就是id,而剩余的行数据只是跟在索引键后面而形成聚簇表,主键索引并没有对除主键以外的数据进行索引;
那么,从覆盖索引的概念:select语句所有要查询的列都必须是索引键,也就是说对于主键索引,覆盖索引应该是只查主键,这样主键索引才起到“覆盖”的作用;而select *是需要把整行数据都查出来,并不只是查询主键了。

1

我是这样理解的,
首先索引这个对象,包含的是索引键;而主键索引,这个对象只包含主键列,也就是在你的例子里,user表的主键索引只包含id;
而innodb是聚簇表,数据行顺序根据主键来组织,并且形成树形结构。那么,MySQL就把主键索引融合到聚簇表里了,但是主键索引和聚簇表是两个东西,主键索引只包含id,聚簇表包含id和其他列。印象中,在ORACLE里,聚簇表也是有额外的主键索引的。只是在MySQL里把这两个东西合起来了。
那么覆盖索引,id=1也就是覆盖主键索引,而主键索引只包含id列,那么只访问id列的查询,才算是主键索引的“覆盖”,而你要select *,只不过是通过主键索引定位到id=1,然后读取聚簇表剩下的数据,这些数据并不属于主键索引,虽然两个东西是合为一体。

不过也就这一个特例,没必要这么纠结。

总是迟到 · 4月26日

展开评论
1

对于覆盖索引,有这样一个定义:

一个索引包含所有需要查询的字段的值,我们就称之为“覆盖索引”。

也就是说,覆盖索引并不是实质意义上的索引。只要一个索引满足部分 SQL 的查询条件,我们就认为这个索引是覆盖索引。其实就是给索引起了个别名。就像 idx_name_age 是一个索引,它也是你写的 SQL 的覆盖索引。

此外,为什么会出现覆盖索引呢?对于 InnoDB 而言,索引存放的是所有字段的值,如果一个查询需要的字段刚好是这个索引的所有字段,那么此次查询很可能就不用去库查询行数据,减少 IO,我们也就认为这个索引是覆盖索引了。

回到题主的问题,为什么是主键索引,不是覆盖索引,就是因为查询的所有字段不能从主键索引上直接拿到,还需要从内存或磁盘上读取,所以不是覆盖索引。

另外,对于覆盖索引,要注意的是,where 条件也会影响查询是否使用到覆盖索引。依然用 idx_name_age 索引,假设 user 表上有 birthday,那么下列查询也不会使用覆盖索引。

SELECT name, age FROM user WHERE birthday='2019-04-26';

题主可以考虑下,为什么上面这个查询没有使用到覆盖索引。

0

因为mysql的索引节点里面只保存了索引字段的值而不是整条记录

撰写答案

推广链接