深入浅出MySQL索引（二）InnoDB存储引擎的索引

本是古典何须时尚 2023-07-05 14:55 27阅读 0赞

[深入浅出MySQL索引（一）常见的索引模型][MySQL]  
[深入浅出MySQL索引（二）InnoDB存储引擎的索引][MySQL_InnoDB]

# 深入浅出MySQL索引（二）InnoDB存储引擎的索引 #

### 文章目录 ###

*  深入浅出MySQL索引（二）InnoDB存储引擎的索引
 *   *  一、InnoDB的索引模型
     *   *  1.1 主键索引
         *  1.2 普通索引
     *  二、索引维护
     *  三、覆盖索引
     *  四、最左前缀原则

在上一篇文章中介绍了为什么需要索引，以及常见的索引模型，本文讲介绍InnoDB的索引

InnoDB使用B+Tree来组织索引，每个索引都是一棵B+Tree，不过对于主键索引和普通索引有所不同，下面讲详细介绍

## 一、InnoDB的索引模型 ##

使用下面的语句创建一张表

create table users(
    	id int primary key,
      k int not null,
      name varchar(16),
      index(k)
    )engine=innodb;

其中我们指定id为主键索引，k为普通索引

我们插入数据指定id和k的值分别为(150,1)、(200,2)、(350,3)、(550,5) 和 (600,6)

### 1.1 主键索引 ###

MySQL将主键索引称为**聚簇索引**，为什么称为聚簇索引呢？因为数据表的数据和索引是一起构建在一棵B+Tree里的，也就是数据表的数据就存在于B+Tree的叶子节点中，例如上述表中id的索引如下所示

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ2MjIwMg_size_16_color_FFFFFF_t_70]

### 1.2 普通索引 ###

普通索引称为**辅助索引**，如上述表的k就是一个普通索引，普通索引于主键索引的区别是，普通索引的叶子节点存放的不是数据，而是主键值，如下所示

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ2MjIwMg_size_16_color_FFFFFF_t_70 1]

下面举个例子来说明如何使用普通索引来查询

执行下面的sql语句的查询过程是什么样的呢？

select * from users where k=2;

首先会通过k索引这颗B+Tree找到对应的主键为200，然后再使用200在主键索引这颗B+Tree检索出数据

这个操作称为**回表**

## 二、索引维护 ##

我们来看主键索引的B+Tree，如果添加一个主键值为700的数据行，那么只要再最后面追加数据就行了。但是如果添加一个主键值为400的数据行就比较麻烦了，此时需要将此页后面的数据往后移动，再插入数据，这是一个比较费劲的操作。更糟糕的情况是，如果此页满了，那么就需要分配新的页，原本放在一个页的数据就分为两个页存放，空间利用率就变低了，这个过程称为**页分裂**。当然如果两个页由于数据被删除导致利用率很低，就会发生**页合并**

基于上述的情况，一般将主键设置为自增主键是个比较合理的选择，自增主键可以通过`NOT NULL PRIMARY KEY AUTO_INCREMENT`在建表的时候指定

另外从普通索引的角度来看，普通索引的叶子节点存放的是主键，如果主键越大，建立普通索引占用的空间就越大，所以从性能和存储的角度考虑，主键使用自增主键也是一个比较合理的选择。

## 三、覆盖索引 ##

我们在建立索引的时候，不仅仅可以使用一个列来作为一个索引，还可以使用多个列来一个索引，称为**联合索引**

如上述表

create table users(
    	id int primary key,
      k int not null,
      name varchar(16),
      index(k)
    )engine=innodb;

现在我们要查找`k=5`的用户它的name，对应的sql语句如下

select name from users where k=5;

这条语句的查找过程是：首先要在k索引树上面查找对应的主键值，然后在主键索引上查找，最后检索出相应的数据行，也就是需要一次回表操作

假如现在我们建立(k,name)这样的联合索引，如下所示

create table users(
    	id int primary key,
      k int not null,
      name varchar(16),
      index(k),
      index(k,name)
    )engine=innodb;

那么现在就有一棵新的索引树，这个索引树的key是(k,name)联合，根据列的顺序来进行排序，比如先比较k，k相等的情况下再比较name

此时的B+Tree如下所示

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ2MjIwMg_size_16_color_FFFFFF_t_70 2]

由于此时name的值已经存在于索引中，所以再执行下面的sql语句就不需要再进行回表操作了

select name from users where k=5;

同样的，主键id的值也存在于索引中，所以执行下面的sql语句也不需要进行回表操作

select id, name from users where k=5;

## 四、最左前缀原则 ##

我们可以使用联合索引来加快查询，但是只有满足最左前缀原则的时候，才能使用联合索引来加速

最左索引原则解释起来可能会有点抽象，下面通过例子来说明

例如上面我们的建立来一个联合索引(k,name)

我们使用k来查询符合最左前缀原则，可以使用索引来加速，例如下面的sql语句

select * from users where k=5;

如果我们使用k和name来查询，符合最左前缀原则，可以使用索引来加速，例如下面的sql语句

select * from users where k=5 and name='xxx';

如果我们使用k和name的前面一部分来查询，符合最左前缀原则，可以使用索引来加速

比如表中有个一个数据，k和name的值为(5,‘Justin’)，可以使用下面的sql语句来查询

select * from users where k=5 and name='jus%';

如果我们单纯使用name来查询则不符合最左前缀原则，不能使用(k,name)联合索引来加速，如下面的sql语句

select * from users where name="xxx";

[MySQL]: https://blog.csdn.net/weixin_42462202/article/details/104335419
[MySQL_InnoDB]: https://blog.csdn.net/weixin_42462202/article/details/104336571
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ2MjIwMg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200215221734888.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ2MjIwMg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ2MjIwMg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200215222053398.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ2MjIwMg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ2MjIwMg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200215225602656.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ2MjIwMg==,size_16,color_FFFFFF,t_70