主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。
非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。
也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。
聚簇索引
聚簇索引有两个特点:
-
使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:
- 页内的记录是按照主键的大小顺序排成一个单向链表。
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
- 存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
-
B+
树的叶子节点存储的是完整的用户记录。所谓完整的用户记录,就是指这个记录中存储了所有列的值(包括隐藏列)。
我们把具有这两种特性的B+
树称为聚簇索引
,所有完整的用户记录都存放在这个聚簇索引
的叶子节点处。这种聚簇索引
并不需要我们在MySQL
语句中显式的使用INDEX
语句去创建,InnoDB
存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是,在InnoDB
存储引擎中,聚簇索引
就是数据的存储方式(所有的用户记录都存储在了叶子节点
),也就是所谓的索引即数据,数据即索引。
二级索引
聚簇索引
只能在搜索条件是主键值时才能发挥作用,因为B+
树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该咋办呢?难道只能从头到尾沿着链表依次遍历记录么?
不,我们可以多建几棵B+
树,不同的B+
树中的数据采用不同的排序规则。比方说我们用c2
列的大小作为数据页、页中记录的排序规则,再建一棵B+
树,效果如下图所示:
这个B+
树与上边介绍的聚簇索引有几处不同:
- 使用记录
c2
列的大小进行记录和页的排序,这包括三个方面的含义:- 页内的记录是按照
c2
列的大小顺序排成一个单向链表。 - 各个存放用户记录的页也是根据页中记录的
c2
列大小顺序排成一个双向链表。 - 存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的
c2
列大小顺序排成一个双向链表。
- 页内的记录是按照
B+
树的叶子节点存储的并不是完整的用户记录,而只是c2列+主键
这两个列的值。- 目录项记录中不再是
主键+页号
的搭配,而变成了c2列+页号
的搭配。
所以如果我们现在想通过c2
列的值查找某些记录的话就可以使用我们刚刚建好的这个B+
树了。以查找c2
列的值为4
的记录为例,查找过程如下:
-
确定
目录项记录
页根据
根页面
,也就是页44
,可以快速定位到目录项记录
所在的页为页42
(因为2 < 4 < 9
)。 -
通过
目录项记录
页确定用户记录真实所在的页。在
页42
中可以快速定位到实际存储用户记录的页,但是由于c2
列并没有唯一性约束,所以c2
列值为4
的记录可能分布在多个数据页中,又因为2 < 4 ≤ 4
,所以确定实际存储用户记录的页在页34
和页35
中。 -
在真实存储用户记录的页中定位到具体的记录。
到
页34
和页35
中定位到具体的记录。 -
但是这个
B+
树的叶子节点中的记录只存储了c2
和c1
(也就是主键
)两个列,所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。
看到步骤4的操作了么?我们根据这个以c2
列大小排序的B+
树只能确定我们要查找记录的主键值,所以如果我们想根据c2
列的值查找到完整的用户记录的话,仍然需要到聚簇索引
中再查一遍,这个过程也被称为回表
。也就是根据c2
列的值查询一条完整的用户记录需要使用到2
棵B+
树!!!
为什么我们还需要一次回表
操作呢?直接把完整的用户记录放到叶子节点
不就好了么?你说的对,如果把完整的用户记录放到叶子节点
是可以不用回表
,但是太占地方了呀!相当于每建立一棵B+
树都需要把所有的用户记录再都拷贝一遍,这就有点太浪费存储空间了。因为这种按照非主键列
建立的B+
树需要一次回表
操作才可以定位到完整的用户记录,所以这种B+
树也被称为二级索引
(英文名secondary index
),或者辅助索引
。由于我们使用的是c2
列的大小作为B+
树的排序规则,所以我们也称这个B+
树为为c2列建立的索引。
联合索引
我们也可以同时以多个列的大小作为排序规则,也就是同时为多个列建立索引,比方说我们想让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+
树。