温故MySQL - 02 什么是聚簇索引？非聚簇索引？联合索引？回表？

问题01：什么是B+树索引？

如图：建立一张表，并向表中插入一些数据：

create table index_demo(
	c1 int,
	c2 int,
	c3 char(1),
	primary key(c1)
) ROW_FORMAT=COMPACT;

答案：

每个索引都对应一颗B+树，B+树分为好多层，最下面一层是叶子节点，其余的是非叶子节点。B+树每个节点内的记录按照索引列值的大小排成了单向链表，每层节点按照索引列值的大小排成了双向链表。

InnoDB会自动为主键建立聚簇索引，如果没有显式的指定主键或者没有声明不允许为null的unique键，就会自动添加主键。聚簇索引的叶子节点包含完整的用户记录。

InnoDB可以为一些列建立二级索引，二级索引叶子节点的用户记录只包含索引列+主键列，如果想通过二级索引查找完整的用户记录，需要执行回表操作，即通过二级索引的叶子节点找到主键值后，再到聚簇索引中查找完整的用户记录。

问题02：什么是聚簇索引？

create table index_demo(
	c1 int,
	c2 int,
	c3 char(1),
	primary key(c1)
) ROW_FORMAT=COMPACT;

以主键列的大小作为数据页、页中记录的排序规则，建立一颗B+树：

答案：

聚簇索引具有以下2个特点：

（1）按照主键值c1列的大小进行数据页和页内记录的排序。B+树页内的记录按照主键值的大小排成了一个单向链表，页与页之间按照主键值的大小排成了一个双向链表；

（2）叶子节点存储了完整的用户记录。即存储了所有列的值 ；

具有以上2个特点的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点中。在InnoDB存储引擎中，聚簇索引就是数据的存储方式，即索引即数据，数据即索引。 聚簇索引将数据与索引放到了一块，索引结构的叶子节点就保存了完整的用户记录。

解读：

① InnoDB的主键生成策略是什么？

InnoDB的主键生成策略：优先使用用户自定义的主键作为主键，如果用户没有定义主键，则选取一个不允许存储null值的unique建作为主键，如果表中连不允许存储null值的unique建都没有定义，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键，即自动添加主键，而InnoDB存储引擎会自动为主键建立聚簇索引。

② 何时会使用聚簇索引？

对于聚簇索引来说，由于B+树中的记录都是按照主键大小进行排序的，因此在搜索条件为主键时聚簇索引才能发挥作用。

select * from index_demo where id=1;

③ 如何根据聚簇索引查找id=20的这条数据？

1、将页33从磁盘读入内存，由于1<20<90，则定位到页30；

2、将页30从磁盘读入内存，在[1,5,12,35]中根据二分法定位到id=20所在的页9；

3、将页9从磁盘读入内存，在[12,20,30]中根据二分法定位到id=20的用户记录；（每个数据页都有页目录）

问题03：建立聚簇索引的优点和缺点？

答案：

建立聚簇索引的优点：

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

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

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

建立聚簇索引的缺点：

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

（2）更新主键的代价很高 ，因为将会导致被更新的行移动。因此一般定义主键为不可更新；

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

问题04：什么是非聚簇索引/二级索引/辅助索引？

create table index_demo(
	c1 int,
	c2 int,
	c3 char(1),
	primary key(c1)
) ROW_FORMAT=COMPACT;

以c2的大小作为数据页、页中记录的排序规则，建立一颗B+树：

答案：

非聚簇索引具有以下特点：

（1）按照c2列的大小进行数据页和页内记录的排序。B+树页内的记录按照c2列的大小排成了一个单向链表，页与页之间按照c2列的大小排成了一个双向链表；

（2）B+树的叶子节点存储的并不是完整的用户记录，而是c2列+主键列；（索引列+主键列）

（3）B+树的非叶子节点存储的并不是主键+页号，而是c2列+页号；（索引列+页号）

这种以非主键列的大小为排序规则而建立的B+树需要执行回表操作才可以定位到完整的用户记录，所以这种B+树也称为二级索引或辅助索引、非聚簇索引。非聚簇索引将数据与索引分开存储，索引结构叶子节点指向了数据对应的位置。

解读：

假如我们想要查找满足搜素条件c2=4的记录，就可以使用上面的非聚簇索引：

因为c2列并没有使用唯一性约束，满足c2=4的记录可能有多条，其实我们只需要在B+树的叶子节点出定位到第一条满足搜素条件c2=4的那条记录，然后沿着记录组成的单向链表一直向后扫描即可，搜索完了本页记录后可以顺序的跳到下一个页中的第一条记录继续向后扫描。

查询过程如下：

1、通过页33确定第一条符合c2=4条件的目录项记录所在的页。根据页33，因为2<4<67，可以快速定位到第一条符合c2=4条件的目录项记录所在的页为页30；

2、通过页30确定第一条符合c2=4条件的用户记录所在的页。根据页30，因为2<4<=4，可以定位第一条符合条件的用户记录所在的页为页10或页28；

3、在真正存储用户记录的页中定位到符合c2=4条件的用户记录。到页10或者页28中定位到具体的用户记录；

4、这个B+树的叶子节点值存储了c2列和c1列（主键），在这个B+树的叶子节点处定位到第一条符合条件的那条用户记录后，我们需要根据该记录中的主键信息到聚簇索引中查找完整的用户记录，这个通过携带主键信息到聚簇索引中重新定位完整用户记录的过程就称为回表。然后再返回到这颗B+树的叶子节点处，找到刚才定位到的符合条件的那条用户记录，并沿着记录组成的单向链表向后继续搜索其他满足条件c2=4的记录，每找到一条的话就继续进行回表操作，重复这个动作，知道下一条记录不满足c2=4的这个条件为止。

问题05：什么是回表？

create table index_demo(
	c1 int,
	c2 int,
	c3 char(1),
	primary key(c1)
) ROW_FORMAT=COMPACT;

答案：

select * from index_demo where c2=5;

对于按照c2列大小排序的B+树，其叶子节点只存储了c2列和主键，因此在定位到c2=5的用户记录后，还需要根据该记录中的主键信息到聚簇索引中查找完整的用户记录， 这个过程称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B+树！

解读：

由于我们的查询列表是*，也就是需要读取完整的用户记录，所以从二级索引中每获取到一条用户记录，就需要根据该用户记录的主键值执行回表操作，也就是到聚簇索引中找到相应的聚簇索引记录。

问题06：回表有什么代价？

create table single_table(
	id int not null auto_increment, 
	key1 varchar(100),         
	primary key(id),          # 聚簇索引
	key idx_key1(key1),       # 二级索引
)Engine=InnoDB CHARSET=utf8;

select * from single_table where key1>'a' and key2<'c';

由于idx_key1索引的叶子节点存储的不是完整的用户记录，仅包含key1,id这两个列，而查询列表是*，这意味着我们需要每条二级索引记录对应的聚簇索引记录，也就是执行回表操作。

答案：

idx_key1中符合条件的二级索引记录所在的页面其页号是尽可能相邻的，即使这些页面的页号不相邻，但是起码一个页面可以存放很多记录，即在执行一次页面IO后，就可以把很多二级索引记录从磁盘加载到内存中，总之，读取二级索引记录，付出的代价是较小的。

二级索引记录对应的id值的大小是毫无规律的，我们每读取一条二级索引记录，就会根据二级索引记录的id值到聚簇索引中执行回表操作。如果对应的二级索引记录所在的页面不在内存中，就需要将该页面从磁盘加载到内存中。由于要读取很多id值并不连续聚簇索引记录，而且这些聚簇索引记录分布在不同的数据页中，这些数据页的页号毫无规律，因此会造成大量的随机IO

解读：

需要执行回表操作的记录数越多，使用二级索引进行查询的性能也就越低，某些查询宁愿使用全表扫描也不使用二级索引。执行回表操作的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引+回表的方式。

一般情况下，可以给查询语句指定limit子句来限定查询返回的记录数，这可能会让查询优化器倾向于选择二级索引+回表的方式进行查询，原因是回表的记录越少，性能提升就越高，比如，上面的查询语句就可以改写成下面的形式：

select * from single_table where key1>'a' and key1< 'c';

添加了limit 10子句后的查询语句更容易让查询优化器采用二级索引+回表的方式来执行。

问题07：聚簇索引和二级索引的区别？

答案：

（1）聚簇索引的各个数据页和页内记录都是按照主键值大小排序的，数据页内的记录按照主键值的大小排成了单向链表，数据页和数据页之间按照主键值的大小顺序排成了双向链表。

（2）聚簇索引的叶子节点存储了完整的用户记录，索引即数据，数据即索引，通过聚簇索引可以直接获取到数据。

（3）非聚簇索引的叶子节点仅存储了索引列+主键，想要获取完整的用户记录，需要在非聚簇索引中获取到符合条件的用户记录后，再根据用户记录的主键信息执行回表操作，即到聚簇索引中查找聚簇索引记录。

问题08：聚簇索引和非聚簇索引在查询上的区别？

答案：

聚簇索引的叶子节点中存储了完整的用户记录，因此只需要查找一颗B+树即可，而非聚簇索引在没有索引覆盖的情况下，需要查找2颗B+树，还需要执行回表操作，即先在非聚簇索引中查找到用户记录，再根据用户记录中的主键信息到聚簇索引中获取到完整的用户记录。

问题09：非聚簇索引与聚簇索引的非叶子节点存放的是什么？

答案：

非聚簇索引的非叶子节点存储了主键值+页号

聚簇索引的非叶子节点存储了索引列（非主键列，非不允许存储null值的unique键）+页号

问题10：什么是联合索引？

答案：

以多个列的大小为排序规则建立的B+树索引为联合索引，即同时为多个列建立的索引。

比如，同时为c2列和c3列建立索引，让B+树同时按照c2列和c3列的大小进行排序，先让数据页和页内的记录按照c2列进行排序，在记录的c2列相同的情况下，再按照c3列进行排序。

如图，为c2列和c3列建立索引：

B+树的非叶子节点的目录项记录由c2列+c3列+页号组成，叶子节点的用户记录由c2列+c3列+主键c1列组成；

数据页和页内记录是先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。

注意：

以c2列和c3列的大小为排序规则建立的B+树索引为联合索引，也称为复合索引或多列索引。它本质上也是一个二级索引，它的索引列包括c2列和c3列。

问题11：如何根据联合索引查询？

create table single_table(
	id int not null auto_increment, 
	key1 varchar(100),         
	key2 int,
	kay3 varchar(100),
	key_part1 varchar(100),
	key_part2 varchar(100),
	key_part3 varchar(100),
	primary key(id),          # 聚簇索引
	key idx_key1(key1),       # 二级索引
	unique key uk_key2(key2), # 二级索引，而且该索引是唯一二级索引
	key idx_key3(key3),       # 二级索引
	key idx_key_part(key_part1,key_part2,key_part3) # 二级索引，也是联合索引
) Engine=InnoDB CHARSET=utf8;

联合索引的索引列包含多个列，以single_table表中的idx_key_part为例，它采用的排序规则如下：
(1) 先按照key_part1列的值进行排序；
(2) 在key_part1列的值相同的情况下，再按照key_part2列的值进行排序；
(3) 在key_part1和key_part2列的值都相同的情况下，再按照key_part3列的值进行排序；

如下为idx_key_part索引的简化示意图，省去目录项记录和单双向链表：

例1、对于查询语句：

select * from single_table where key_part1='a';

由于二级索引记录idx_key_part是先按照key_part1列的值排序的，所以符合key_part1='a’条件的所有记录肯定是相邻的，我们可以定位到符合key_part1='a’条件的第一条记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合key_part1='a’的条件为止。（如果本页面中的记录扫描完了，就根据叶子节点的双向链表继续向下扫描）。

由于我们的查询列表是*，也就是需要读取完整的用户记录，所以从上述扫描区间中每获取一条二级索引记录，就需要根据二级索引记录的id列的值执行回表操作，也就是到聚簇索引中找到相应的聚簇索引记录。

例2、对于查询语句：

select * from single_table where key_part1='a' and key_part2='b';

由于二级索引记录是按照key_part1列的值排序的，在key_part1列的值相等的情况下再按照key_part2列进行排序，所以符合key_part1='a'和key_part2='b'条件的二级索引记录肯定是相邻的。我们可以定位到符合key_part1='a' and key_part2='b'条件的第一条记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合key_part1='a'或key_part2='b'条件为止。

例3、对于查询语句Q5来说:

seelct * from single_table where key_part2='a';

由于二级索引记录不是直接按照key_part2列的值排序的，所以符合key_part2='a’的二级索引记录可能并不相邻，也就是说我们不能通过这个key_part2='a’搜索条件来减少需要扫描的记录数量。在这种情况下，我们是不会使用idx_key_part索引执行查询的。

问题12：MyISAM的索引是怎么实现的？

Innodb和MyISAM默认的索引是Btree索引；而Memory默认的索引是Hash索引。

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

MyISAM存储引擎将表中的记录按照记录的插入顺序单独存储在一个文件中，这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少个记录，这样一来，我们就可以通过行号快速访问到一条记录，如图所示：

如上图，由于在插入数据时并没有刻意按照主键大小排序，所以我们不能再这些数据上使用二分法进行查找。

使用MyISAM存储引擎的表会把索引信息单独存储在另外一个文件中，称为索引文件。MyISAM会为表的主键单独创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值和行地址的组合。也就是先通过索引找到对应的行的地址，再通过行地址去找对应的记录。

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

MyISAM会直接在索引叶子节点处存储该条记录在数据文件中的地址偏移量。由此可以看出MyISAM的回表操作时十分快速的，因为它是拿着地址偏移量直接到文件中取数据，而InnoDB是通过获取主键之后再去聚簇索引中找记录，虽然说不慢，但是也比不上直接用地址去访问。

可以看到对于非聚簇索引，不管是以主键为排序规则还是以非主键为排序规则，它的结构都是相同的，即叶子节点存放的都是相应的列+行地址。

问题13：B+树的叶子节点可以存储哪些东西？

create table index_demo(
	c1 int,
	c2 int,
	c3 char(1),
	primary key(c1)
) ROW_FORMAT=COMPACT;

答案：

① 数据页和页内记录都按照主键大小排序的B+树：

聚簇索引的数据页和页内记录都是按照主键值大小进行排序的，并且叶子节点存储的是完整的用户记录，即行记录。

② 数据页和页内记录都按照c2列大小排序的B+树：

二级索引的数据页和页内记录都是按照索引列c2大小进行排序的，叶子节点仅存储了索引列+主键。

③ 数据页和页内记录都按照主键大小排序B+树：

叶子节点存储的主键+行地址值；

④ 数据页和页内记录都按照非主键大小排序B+树：

叶子节点存储的主键+行地址值；

问题14：MyISAM 与 InnoDB索引的区别？

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

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

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

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

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