【MySQL进阶之路 | 高级篇】常见索引(聚簇索引, 二级索引)

1. 常见索引概念

索引按照物理实现方式，可以分为两种，聚簇索引和非聚簇索引.我们也把非聚簇索引称为二级索引或辅助索引.

(1). 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式(所有的数据记录都存储在了叶子节点)，也就是说所谓的索引即数据，数据即索引.MySQL8.0，在InnoDB存储引擎中索引和数据都存储在物理磁盘的.ibd文件中.

特点 : 

1. 使用记录主键值大小进行记录和页的排序

• 页内记录是按照主键的大小顺序排成一个单向链表.
• 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表.
• 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中记录主键值的大小顺序排成双向链表.

2. b+树的叶子节点存储的是完整的用户记录.即这个记录存储了所有列的值，包括InnoDB自己添加的隐藏列.

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

即一张新表，当插入一条记录时，底层就会自动构造b+树来创建索引.而不是等到添加很多记录后，才想起来使用INDEX语句才构造b+树创建索引.

如果表中有主键PRIMARY KEY，就会以该主键列创建索引，如果没有主键，则会选择一个非空唯一键来创建索引，如果即没有主键也没有唯一键，则会使用记录头信息中的隐藏列row_id(InnoDB默认为表添加)来作为主键创建索引.

优点

• 数据访问更快.因为聚簇索引将索引和数据都保存在同一个b+树中，因此聚簇索引比非聚簇索引查找速度更快.
• 聚簇索引对于主键的排序查找和范围查找速度非常快.
• 按照聚簇索引排列顺序，查询显示一定范围数据时，由于数据是相对紧密相连的，数据库不用从多个数据块中提取数据，节省大量的I/O操作.

缺点

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能.因此，对于InnoDB表，我们一般都会定义一个自增的id列为主键.
• 更新主键的代价很高. 因为将会导致被更新的行移动.因此对于InnoDB表我们一般定义主键为不可更新的.
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次通过主键值找到行数据.

限制

• 对于MySQL数据库，目前只有InnoDB支持聚簇索引.而MyISAM不支持.
• 由于数据物理存储排序方式只能有一种方式，所以每个MySQL的表只能有一个聚簇索引.一般情况下，即该表的主键.
• 如果没有主键，会选择非空的唯一索引代替.如果也没有非空的唯一索引，InnoDB会隐式定义一个主键(row_id)作为聚簇索引.

(2). 二级索引

上面介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用.因为b+树中的数据都是按照主键值的顺序来排序的.那么如果我们想根据别的列作为搜索条件该咋办呢.

我们可以多建几个b+树. 不同的b+树中的数据采用不同的排序规则.比方说我们用c2列来构建一个b+树.

该b+树与上述聚簇索引的b+树有几点不同.

• 使用记录c2的列的大小进行记录和页的排序.
• b+树的叶子节点存储的不是完整的用户记录.而是c2列+主键这两个列的值.通过该主键值再进行回表操作去聚簇索引的b+树中查询完整的记录.
• 目标项记录不再是主键+页号，而是c2列+页号.

问 : 我们为什么需要回表.

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

因为这种按照非主键列建立的b+树需要一次回表的操作才可以定位到完整的用户记录.所以把这种b+树结构叫做二级索引.由于我们使用的是c2列的大小作为b+树的排序规则，所以我们称为这个b+树为c2列建立的索引.

与聚簇索引的区别.

• 聚簇索引的叶子节点存储的是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置.非聚簇索引不会影响数据表的物理存储结构.
• 一个表只能有一个聚簇索引.因为只有一种依据主键的排序方式.但可以有很多个非聚簇索引.也就是多个索引提供数据检索.
• 使用聚簇索引的时候，数据查询效率很高.但如果对数据进行插入删除更改等操作时，效率会比非聚簇索引的低.

3. 联合索引

我们也可以多个列的大小作为排序规则.也就是为多个列建立索引.比如我们想让b+树按照c2列和c3列的大小进行排序.这个包含两层含义.

• 先把各个记录和页按照c2列排序
• 当记录的c2列的值相同时，采用c3列进行排序.

以c2列c3列的大小为排序规则建立的b+树本质上也是二级索引.

2. InnoDB的b+树索引的注意事项

(1). 根页面位置万年不动

我们前面介绍b+树索引的时候，为了理解的方便，都是先把存储用户记录的叶子节点都画出来，然后接着画存储目录项记录的内节点.实际上，b+树的形成过程是这样的.

• 每当为某个表创建一个b+树索引的时候，都会为这个索引创建一个根节点页面.最开始表中没有数据，每个+树索引对应的根节点中既没有用户记录，也没有目录项记录.
• 随后向表中插入用户记录时，先将用户记录存储到这个根节点中.
• 当根节点中的可用空间用完时继续插入，此时会将根节点中的所有记录复制到一个新分配的页，比如页a，然后对这个新页进行页分裂操作，得到新页页b.这时新插入的记录根据主键值的大小会被分配到页a或页b中.而根节点便升级为存储目录项的记录的页.

一个b+树索引的根节点子诞生开始，便不再移动.这样我们只要对某个表建立一个索引，那么它的根节点的页号便被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从固定的地方取出根节点的页号，从而访问该索引.

(2). 内节点中目录项记录的唯一性

我们知道b+树索引的内节点(非叶子节点)目录项记录的内容是索引列+页号.但这个搭配对二级索引来说有点不严谨.假设有个表的数据是这样的.

如果二级索引中目录项记录的内容只是索引列+页号，那么为c2列建立索引后的b+树应该长这样.

当我们想插入一行记录时，values(9，1，'c')，新插入的记录并不知道该放到页4还是页5.

如果我们把主键值也添加到二级索引内节点的目录项记录中，这样能保证b+树每一层节点中各条目录项记录除页号外是唯一的.所以我们为c2列建立二级索引后的示意图应该是这样的.

所以目录项记录中也必须存储主键值.而数据项记录中由于需要回表操作需要存储主键值这是毋庸置疑的.

(3). 一个页面最少存储两条记录

废话.

3. 索引的代价

(1). 空间上的代价

每建立一个索引都要为它建立一个b+树，每一个b+树的每一个节点都是一个数据页，一个页默认的大小是16kb，一个很大的b+树由许多的数据页组成，那就是很大的存储空间.

(2). 时间上的代价

每次对表的数据进行增删改等操作时，都需要去修改各个b+树的索引结构.b+树的每层节点(也就是页)都是按照索引列的值从小到大的顺序排序而组成了双向链表.不论是叶子节点上的记录，还是内节点中的目录项，都是按照索引列的值从小到大顺序形成的一个单向列表.而增删改等操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页分裂，页面回收等操作来维护节点和记录的排序.如果我们创建了很多索引，会给性能带来很大的不便.