InnoDB中常见索引

目录

• 常见索引概念
  • 1、聚簇索引
  • 2、二级索引（辅助索引、非聚簇索引）
  • 3、联合索引
• 索引的代价

常见索引概念

索引按照物理实现方式，索引可以分为2种:聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

1、聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子节点)，也就是所谓的索引即数据，数据即索引。

术语“聚簇”表示数据行喝相邻的键值聚簇的存储在一起。

特点：

​ 1、使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

​ ● 页内的记录是按照主键的大小顺序排成一个单向链表。

​ ● 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。

​ ● 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

​ 2、B+树的叶子节点存储的是完整的用户记录。

​ 所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列)。

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引。

优点：

● 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

● 聚簇索引对于主键的排序查找和范围查找速度非常快

● 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的IO操作。

缺点：

● 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键

● 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新

● 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

限制：

● 对于MySQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MylSAM并不支持聚簇索引。
● 由于数据物理存储排序方式只能有一种，所以每个MysQL的表只能有一个聚簇索引。一般情况下就是该表的主键。

● 如果没有定义主键，Innodb会选择非空的唯一索引代替。如果没有这样的索引，Innodb会隐式的定义一个主键来作为聚簇索引。
● 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长。

2、二级索引（辅助索引、非聚簇索引）

上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该怎么办呢?肯定不能是从头到尾沿着链表依次遍历记录一遍。
答案:我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则，再建一棵B+树，效果如下图所示:

这个B+树与上边介绍的聚簇索引有几处不同:

​ ● 使用记录c2列的大小进行记录和页的排序，这包括三个方面的含义：

​ ○ 页内的记录是按照c2列的大小顺序排成一个单向链表。

​ ○ 各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。

​ ○ 存放目录项记录的页分为不同的层次，在统一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表。

​ ● B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。

​ ● 目录项记录中不再是主键+页号的搭配，而变成了c2列+页号的搭配。

所以如果我们现在想通过c2列的值查找某些记录的话就可以使用我们刚刚建好的这个B+树了。以查找c2列的值为4的记录为例，查找过程如下:

1、确定目录项记录页

根据根页面，也就是页44，可以快速定位到目录项记录所在的页为页42因为（2<4<9）。

2、通过目录项记录页确定用户记录真实所在的页。
在页42中可以快速定位到实际存储用户记录的页，但是由于c2列并没有唯一性约束，所以c2列值为4的记录可能分布在多个数据页中，又因为2 < 4 ≤ 4，所以确定实际存储用户记录的页在页34和页35中。

3、在真实存储用户记录的页中定位到具体的记录。

到页34和页35中定位到具体的记录。

4、但是这个B+树的叶子节点中的记录只存储了c2和c1(也就是主键)两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。

概念：回表

我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查-一遍，这个过程称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B+树!

问题: 为什么我们还需要一次回表操作呢?直接把完整的用户记录放到叶子节点不OK吗?

如果把完整的用户记录放到叶子节点是可以不用回表。但是太占地方了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。

因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引(英文名secondary index )，或者辅助索引。由于我们使用的是c2列的大小作为B+树的排序规则，所以我们也称这个B+树是为c2列建立的索引。
非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

小结:聚簇索引与非聚簇索引的原理不同，在使用上也有一些区别:
1．聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
2．一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。
3．使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

3、联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义:
● 先把各个记录和页按照c2列进行排序。
● 在记录的c2列相同的情况下，采用c3列进行排序

为c2和c3列建立的索引的示意图如下:

如图所示，我们需要注意以下几点：

● 每条目录项记录都由c2、c3、页号这三个部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。

● B+树叶子节点处的用户记录由c2、c3和主键c1列组成

注意一点，以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下:

● 建立联合索引只会建立如上图一样的1棵B+树。
● 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：

● 空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

● 时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录〈也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好又少的索引，我们得学学这些索引在哪些条件下起作用的。