分享｜MySQL高频面试考点2 - 关于索引的那些事
派派
5777
2021.02.22
发布于 未知归属地
MySQL春招我要上精选我要上推荐技术交流

在文章MySQL 高频面试题 - 为什么 B+ 树比 B 树更适合应用于数据库索引？中我们了解了，在InnoDB存储引擎中，MySQL的索引底层之一是B+树。那么具体索引树中是怎么组织数据的呢？什么是聚集索引（或者叫做聚簇索引），什么是覆盖索引呢？什么又是最左匹配原则呢？我们继续来探索关于面试中索引的那些常考点。

本文目录

主键索引和普通索引有什么区别 - 聚集索引和非聚集索引

非聚集索引就一定要回表吗？-- 联合索引和覆盖索引

提到联合索引，那一定不得不提的那些原则 - 最左前缀匹配原则

索引的不断优化 - 索引下推

正文

主键索引和普通索引有什么区别

我们在建一些需要明确有唯一标识字段的表的时候通常都会显示的建立主键索引，例如

create table user(
ID int primary key, 
a int not null, 
name varchar(16),
index(a))
engine=InnoDB;

这里的user表中，字段ID是主键。

在InnoDB引擎中，即使我们没有显示的定义主键，InnoDB引擎也会根据以下规则选择和创建主键：

选择建表时第一个定义的非空索引，如果没有，自动创建一个6字节大小的指针。

为什么在InnoDB引擎中，表需要（显示或隐式）主键呢？原因是因为

在InnoDB引擎中，表是索引组织表，表是根据主键顺序组织存放的。

索引树的一般结构

image.png

我们已经知道，在B+索引树中，数据存储在叶子节点中。

对于一般的辅助索引，也就是上面例子中的索引a,在索引树中的结构示例如下：

a索引树

也就是在叶子节点中，对于辅助索引，存储的key是索引值a，data是主键值ID。

而对于主键索引，例如上面例子中的主键为ID的索引树，结构如下图：

image.png

也就是在叶子节点中，对于主键索引，存储的key是主键ID，data是表的整行数据。

在InnoDB引擎中，主键索引也被叫做聚集索引，即叶子节点中，存储的是整行数据，相较于聚集索引，辅助索引（也成二级索引）存储的数据是主键值，称为非聚集索引。

回表

在上述例子的表中有两颗索引树，索引为a的索引树和索引为主键(表中字段名为ID)的索引树。

image.png

假设表中的数据如下图：
image.png

假设现在有一个查询语句：

select * from user where a = 100;

a索引树上没有我们所需要的整行数据（比如name）的值，因此这条查询语句的查找过程为先从a索引树上根据a筛选出符合条件的主键ID，再带着符合条件的主键ID去主键索引树上找到一个完整的行记录。
即查找过程如下：

从a索引树上找到a=100对应的主键ID为100；
到ID索引树上找到ID=100的行记录数据;

这个回到主键索引树上去查找的过程，叫做回表。

假设要查找的数据不在辅助索引树上，回到主键索引树上去查找势必要经过再一次的逻辑IO访问才能得到最终的数据。

减少回表：覆盖索引

那么，是不是索引的查询都需要经过回表呢？

假设现在有一个查询语句：

select ID from user where a = 100;

在这个查找语句中，明显id在a索引树上就能够查找到我们需要的ID的数据，所以查找过程只需要在a索引树上，这个时候，查询操作在a索引树上就能完成，不需要再经过主键索引树，这个时候就不需要回表。

即索引a就称为覆盖索引，即从辅助索引中就可以得到需要查找的数据，不需要再回到主键索引树上去查找。

覆盖索引的好处主要就是减少了大量的IO操作，主要体现在以下两个方面：

  1. 覆盖索引不需要回表，查询操作在辅助索引树上就可以完成，减少了回表次数，因此可以减少大量的IO次数；
  2. 对于统计而言，假设操作在覆盖索引上可以完成，也可以减少大量的IO操作。

例如

select count(*) from user;

我们用explain执行计划可以看到sql的一个大概的执行情况

 explain select count(*) from user;

结果如下图：
image.png

我们看到在key这一栏中，使用了 a 这个索引。
image.png

在Extra这一栏中，这条统计sql 用到了覆盖索引
image.png

也就是说，这个统计操作里面，由于统计覆盖索引可以满足统计需求，所以优化器选择了需要更少IO次数的覆盖索引进行统计。

从上面我们可以知道，覆盖索引由于不需要经过回表，有效的减少的IO次数，所以是一种有效的数据库的优化的手段。

因为在数据库中，涉及磁盘的随机IO的访问是数据库中最耗时的操作之一。

讲到覆盖索引，那么就不得不提联合索引。

联合索引和最左前缀原则

我们现在创建一个表:

create table Test(
    ID int primary key,
    a int not null,
    b int not null,
    c int not null,
    d int not null,
    KEY indext (a,b,c)
)engine=innodb;

其中 indext(a,b,c)就是联合索引。

image.png

由于联合索引索引树的建立是根据索引顺序进行排序，假设表中的数据如下：

image.png

那么索引为（a,b,c）的索引树结构如下：

image.png

即索引根据第一个字段进行排序，如果第一个字段相同，再根据第二个字段进行排序，以此类推。

因此创建联合索引的好处是，创建了一个联合索引indext(a,b,c)，实际上是创建了三个索引:

  1. 索引a;
  2. 索引(a,b);
  3. 索引(a,b,c);

即MySQL建立联合索引时会遵循最左前缀匹配的原则，在检索数据时从联合索引的最左边开始匹配。

现在有三个select 语句

select ID from Test where a = 1;
select ID from Test where a = 1 and b = 1;
select ID from Test where a = 1 and b = 1 and c = 1;

我们用explain执行计划查看执行结果:

explain select ID from Test where a = 1;

image.png

从红框中key这一栏，我们看到查询语句用到了联合索引indext,但是从key_len这一栏我们看到只用到了联合索引的一部分，4个字节，这是因为在这个查询语句中用到了索引a。

用explain执行计划查看 select ID from Test where a = 1 and b = 1

explain select ID from Test where a = 1 and b = 1;

image.png

用到了联合索引的前8个字节也就是索引(a，b)

用explain执行计划查看 select id from Test where a = 1 and b = 1 and c = 1;

explain select ID from Test where a = 1 and b = 1 and c = 1;

image.png

用到了联合索引的前12个字节也就是索引(a, b, c)

关于索引失效的说明

当查询列中含有like 模糊查询且"%"位于前面时，会导致索引失效。

例如对于下面联合索引为（zipcode,lastname）的表格

create table people(
    ID int primary key,
    zipcode varchar(15),
    firstname varchar(15),
    lastname varchar(15),
    address varchar(15),
    KEY indext (zipcode,lastname)
)engine=innodb;

对于以下查询语句

select * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%ni%';

其中lastname包含模糊查询，根据最左前缀原则，此时生效的索引部分仅为zipcode。

索引下推

在MySQL5.6之前，对于以下查询语句

select * FROM people
WHERE zipcode='95040'
AND lastname LIKE '%ni%';

存储引擎会先在索引树上找到符合满足zipcode的主键ID，然后根据找到的主键ID一条一条进行回表查询。

image.png

由于对于不符合最左前缀的部分（lastname）没有利用筛选，对于上面的数据，就需要4次回表。

这样的查询效率其实是比较低下的，其实还有进一步优化的空间，比如可以根据lastname的匹配条件在索引树上做进一步筛选，减少回表次数。

image.png

这样的回表次数就从原来的四次减少到了两次。

Mysql5.6做了这样的优化，叫做索引下推（index condition pushdown），简称ICP。

即对于上面的查询语句，在根据生效索引前缀部分zipcode在索引树上筛选出符合条件主键ID后，再根据lastname做出进一步筛选，减少回表次数。

总结

聚集索引：索引树的叶子结点存储的是行数据记录。
回表：查询需要由辅助索引树上筛选出主键值再回到主键索引树上去查找数据的过程。
覆盖索引：查询过程仅需要在辅助索引树上就可以完成，不需要经过回表。
联合索引：多个字段组成的索引，根据第一个字段顺序>第二个字段顺序>第三个字段顺序进行组织。
最左前缀匹配：在检索数据时从联合索引的最左边开始，从左到右进行匹配。
索引下推：MySQL5.6开始的优化，可以在辅助索引遍历过程中，对辅助索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

以上就是本篇文章的全部内容啦~
喜欢的话给我点个赞吧~

评论 (1)