常见的索引的概念

常见的索引的概念

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

聚簇索引

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

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

特点：

1. 术语"聚簇"表示数据行和相邻的键值聚簇的存储在一起。
   1. 页内的记录是按照主键的大小顺序排成一个单向链表。
   2. 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
   3. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成—个双向链表。
2. B+树的叶子节点存储的是完整的用户记录。

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

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

优点：

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

缺点：

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

限制：

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

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

上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该怎么办呢？肯定不能是从头到尾沿着链表依次遍历记录一遍。

答案：我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则，再建一棵B+树，效果如下图所示：（这里的数据项记录的不是完整的数据了，记录的是c2字段的值和c1字段的值）

这里的数据项记录的不是完整的数据了，记录的是c2字段的值和c1字段的值

这个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. 使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让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+树。

InnoDB的B+树索引的注意事项

根页面的位置万年不动

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

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

这个过程特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样我们对某个表建立一个索引，那么它的根节点的页号，便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从固定的地方取出根节点的页号，从而来访问这个索引。

内节点中目录项记录的唯一性

我们知道B+树索引的内节点中目录项记录的内容是索引列＋页号的搭配，但是这个搭配对于二级索引来说有点儿不严谨。还拿index_demo表为例，假设这个表中的数据是这样的：

c1	c3	c3
1	1	‘u’
3	1	‘d’
5	1	‘y’
7	1	‘a’

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yxMvrXCA-1647389388517)(C:/Users/losser/AppData/Roaming/Typora/typora-user-images/image-20220314205319490.png)]

如果我们想新插入一行记录，其中c1、c2、c3的值分别是:9、1、'c'，那么在修改这个为c2列建立的二级索引对应的B+树时便碰到了个大问题：由于页3中存储的目录项记录是由c2列＋页号的值构成的，页3中的两条目录项记录对应的c2列的值都是1，而我们新插入的这条记录的c2列的值也是1，那我们这条新插入的记录到底应该放到页4中，还是应该放到页5中啊?答案是:对不起，懵了。

为了让新插入记录能找到自己在那个页里，我们需要**保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。**所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

• 索引列的值
• 主键值
• 页号

也就是我们把主键值也添加到了二级索引内节点中的目录项记录了，这样就保证了B+树每一层节点中各个目录项记录除页号这个字段是唯一的，所以我们为c2列建立二级索引后的示意图实际上应该是这样子的：

在非叶子节点添加上了主键值。！！！

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0Q4cbqdy-1647389388518)(C:/Users/losser/AppData/Roaming/Typora/typora-user-images/image-20220314210049653.png)]

这样我们再插入记录（9，1，'c'）时，由于页3中存储的目录项记录是由c2列＋主键＋页号的值构成的，可以先把新记录的c2列的值和页3中各目录项记录的c2列的值作比较，如果c2列的值相同的话，可以接着比较主键值，因为B+树同一层中不同目录项记录的c2列＋主键的值肯定是不一样的，所以最后肯定能定位唯一的一条目录项记录，在本例中最后确定新记录应该被插入到页5中。

一个页面最少存储两条记录

一个B+树只需要很少的层级就可以轻松存储亿条记录，查询速度相当不错！这是因为B+树本质上就是一个多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问到的存储真实的数据的目录。那如果一个大的目录，只存放一个子目录是个啥效果呢？那就是目录层级非常非常多，而且最后的那个存放真实数据的目录中只存放一条记录。费了半天劲只能存放一条真实的用户记录？所以InnoDB的一个数据页至少可以存放两条记录。

MyISAM中的索引方案

---

B树索引适用存储引擎如表所示

索引/存储引擎	MyISAM	InnoDB	Memory
B-Tree索引	支持	支持	支持

即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。InnoDB和MyISAM默认的索引是BTree索引；而Memery默认的索引是Hash索引。

MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。

MyISAM索引的原理

下图是MylSAM索引的原理图。

我们知道**InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储︰**

• 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
• 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值＋数据记录地址的组合。（它是没有聚簇索引的，全部都可以理解成二级索引）

这里设计表一共三列，假设我们以Col1为主键，上图是一个MyISAM表二点主索引（Primary Key）示意。可以看出来**MyISAM的索引文件仅仅保存数据记录的地址。**在MyISAM中，主键索引和二级索引（Secondary key）在结构尚没有任何区别，只是主键索引要求key是唯一的，而二级索引的key可以重复。如果我们在Col2上建立一个二级索引，则次索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中检索索引的算法为：首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出data域的值，然后以data域的值为地址，读取响应数据的记录。

MyISAM和InnoDB对比

MyISAM的引入方式都是”非聚簇“的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中的索引的区别：

① 在InnoDB存储引擎中，我们只需要主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次**回表**操作，意味着MyISAM中建立的索引相当于全部都是二级索引。

② InnoDB中数据文件本省就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址

③ InnoDB的非聚簇索引data域存储相应记录的主键值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都应用主键作为data域。

④ MyISAM的回表操作是非常快速的，因为是拿着偏移量去文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里面找记录，虽然说也不慢，但还是比不上直接用地址去访问。

⑤ InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，MySQL系统会自动选择一个可以非空且唯一的标识数据记录的列作为主键。如果不存在这样的列，则MySQL会自动为InnoDB表生成一个隐含字段作为主键，这个字段为6个字节，类型为长整型。

小结：

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助。比如：

举例1：知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有二级索引都引用主键索引，过长的主键索引会令二级索引变得过大。

**说明：**在聚簇索引和非聚簇索引（尤其是非聚簇索引），因为一个数据页最多只能放16K，过长的话就会导致记录少变少，层级变高。

举例2：用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一棵B+Tree，非单调的主键会造成在插入新记录时，数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键是一个很好的选择。