MySQL高级之索引一

一、索引的基础知识

1. 基本概念

• 索引（Index）是帮助MySQL高效获取数据的数据结构。索引的本质就是数据结构。可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。 这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现高级查找算法。比如下图的二叉树的结构。
  

2. 索引的优缺点

2.1 优点

• 提高数据检索的效率，降低数据库的IO成本
• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性 。
• 对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
• 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间 ，降低了CPU的消耗。

2.2 缺点

• 创建索引和维护索引要耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。
• 索引需要占磁盘空间 ，除了数据表占数据空间之 外，每一个索引还要占一定的物理空间，存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

二、索引的数据结构

假设我们建立了一个表Index_demo，其具体格式如下

CREATE TABLE index_demo(
	c1 INT,
	c2 INT,
	c3 CHAR(1),
	PRIMARY KEY(c1)
) ROW_FORMAT = Compact

1. Compact行格式简介

• Compact行格式是Innodb存储引擎实际存储记录的一种格式，其格式如下（ 此行格式中有很多隐藏字段，我们只说需要用到的两个，其余的暂不提及，和一些额外信息一起放到其他信息中以做表示）
  

1. record_type：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、 2 表示最小记录、 3 表示最大记录、 1 表示目录项记录。
2. next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。
3. 各个列的值：这里只记录在index_demo表中的三个列，分别是 c1 、 c2 和 c3 。
4. 其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

2. 聚簇索引数据结构详解-B+树

2.1 聚簇索引结构的演变

众所周知，在MySQL中，我们的数据采用的格式是Compact行格式，数据都是以页为单位进行存储的，每页大小为16KB。

2.1.1 数据数目不过页

• 当数据量较少时，一个页就能存放下所有的数据，此时为了方便查找，需要对页中的数据进行排序。在页中我们定义一个最小记录项（record_type = 2）和最大记录项目（record_type = 3），这两项实际不存储数据，用来标记页中数据的下限和上限。同时，真正的数据信息的record_type = 0，用来表示这是数据，此外，我们将所有的数据按照主键的从小到大的顺序进行存放，页中的条数据以单向链表的形式进行连接。
  

2.1.2 目录数目不过页

• 当数据数目逐渐增多，一页无法存放下需要存储的数据，此时需要创建新的页来进行数据的存储。同样的为了方便数据的查询，需要进行一些新的规则定义。
• 对于数据页，定义三个规则：①新页中存放的数据的最小主键值需要大于旧页中存放的最大的主键值，② 新页存放数据的方式和旧页相同（主键从小到大，单向链表连接），③ 不同数据页之间采用双向链表的方式进行连接。（注意，此时如果插入的数据的主键小于原页面中最大的主键，要对两个页面的数据进行重新排布，确保符合定义的规则，这个创建新页并重新排布的过程，实际上称为页的分裂）。
• 定义一个目录页，目录页中存放目录项，所有的目录项的record_type = 1，用来表示，这是一个目录项，每个目录项中存放两个信息：① 每页中存放的所有数据的主键的最小值 ② 页的地址。
  

2.1.3 目录数目过页

• 当数据数目再次增多，一个目录、页，无法存放下所有的数据页的信息，此时需要创建新的页来进行目录项的存储，同样的为了方便数据的查询，又需要进行一些新的规则定义。
• 首先，依然采用页的分裂的规则：①新页中存放的目录项的最小主键值需要大于旧页中存放的最大的主键值，② 新页存放目录项的方式和旧页相同（主键从小到大，单向链表连接），③ 不同数据页之间采用双向链表的方式进行连接。（同样的，也存在页的分裂）。
• 此外，我们要再次定义目录页，来存放目录页的目录项，规则依然如同上一步：所有的目录项的record_type = 1，用来表示，这是一个目录项，每个目录项中存放两个信息：① 每页中存放的所有目录的主键的最小值 ② 页的地址。
  

2.1.4 总结

• 至此，数据库存放数据的结构也就成型了，这也正是MySQL用来存储数据的结构。这种存放数据的结构，还有一个名称，就是聚簇索引。也就是说，MySQL的聚簇索引真正实现了索引即数据，数据即索引。
• 那么，我们再来总结一下，整个存储结构的特征：
  1. 数据都放在了叶子节点之内，非叶子节点中只有指针用来指向叶子节点或非叶子节点的数据页地址。
  2. 每一个页中的每一项，都是按照主键的顺序，从小到大以单向链表的形式进行连接。
  3. 同一层中的相邻页之间都是以双向链表的形式进行连接。

2.1.5 更正

之前为了方便对数据结构进行阐述，对于整个的聚簇索引的形成过程进行了略微改动，接下来进行还原。

• 根页面万年不动
  一个B+树索引的根页面自诞生之日起，便不会再移动。一开始，我们向数据库中插入记录时，都会直接将数据项放入根页面中，当根页面中的可用空间用完时，如果还继续插入记录，此时会将根页面中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。而根页面便升级为存储目录项记录的页。当根页面成为了目录页，并且当存放的目录项超过存储空间时，也会进行类似的操作。
• 聚簇索引不一定是主键
  当我们定义了主键之后，Innodb引擎会自动的为我们以主键生成聚簇索引；但是如果没有定义，Innodb会为我们选取字段唯一的非空的列来生成聚簇索引；如果这样的字段也不存在，则默认会选择一个隐藏列来生成聚簇索引。但是，我们一般要求表必须有主键，所以后两种情况实际可以忽略。

3. 非聚簇索引数据结构

3.1 从点到线：从聚簇到非聚簇

• 非聚簇索引的数据结构也是B+树的结构，我们通过分析非聚簇索引和聚簇索引的区别，也就可以掌握非聚簇索引的知识了。

3.1.1 区别一：叶子节点不存放数据

• 聚簇索引的叶子节点存放的是真实的数据，具有数据的全部信息。
• 非聚簇索引的叶子节点中仅仅存放了建立了索引的那条数据的索引字段值和主键值
  1. 如果我们通过非聚簇索引查询数据时，实际上，会先查询到该数据的主键值，然后，再利用主键，到聚簇索引中进行查询，得到全部的数据信息。
  2. 这个再次利用主键查询数据过程的过程，就成为回表。

3.1.2 区别二：不通过主键排序数据

• 聚簇索引是数据库在创建表中形成的一个索引，它天生就在存储数据的同时，为我们建立了针对主键的索引，方便我们利用主键对数据进行操作。
• 非聚簇索引实际上与数据存储没有关系，只是为了查询方便，所以我们需要用什么字段建立索引，就用什么字段代替聚簇索引中主键的位置即可。

3.1.3 注意点：主键不可或缺

• 聚簇索引由于是利用主键建立的索引，那就必然能保证，每一个目录项都是唯一的，不会重复，不会出现操作数据无法进行精确定位的情况。
• 非聚簇索引是我们人为对于某一个字段建立的索引，当我们存放的多条数据在该字段的值相同时，有可能在操作数据时出现通过该值定位到多个目录页，此时就不知道应该深入那个页进行操作了（比如插入新的数据时，更新二级索引就会出现这个问题），此时，为了区分不同的目录项，我们需要确保每一个目录项都是独一无二的，所以需要加上一个字段内容独一无二的列—主键进行区分，此时如果索引字段出现重复，可以在此基础上，再按照主键的排序进行进精确定位。

3.2 举例

假设存在下表，c1为主键列，c2、c3为普通列，此时，我们针对c2建立的非聚簇索引如下图所示

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