设计索引
一、页中记录的格式（以compact行格式为列）
- record_type：记录头信息的一项属性，表示记录的类型，0表示普通记录、1目录项记录、2表示最小记录、3 表示最大记录。
- next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。
- **各个列的值 **：这里只记录在表中的三个列，分别是c1、c2 和c3 。
- 其他信息 ：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。
二、一个简单的索引设计方案
1、下一个数据页中用户记录的主键值必须大于上一个数据页中用户记录的主键值。
2、给每个页都建立一个目录项。
由于数据页在物理存储上是不连续的，如果想从多个数据页中根据该记录的主键值快速定位该记录的所在页，就需要建立一个目录页，每一个数据页对应一个目录项，目录页中存储目录项，每个目录项包括两部分：key（数据页的用户记录中最小的主键值）、page_no（数据页的编号）。
查找主键值为20的记录，分为两步：
第一步（确定页）：先从目录页中根据二分法快速确定出主键值为20的记录在目录项3中（因为 12 < 20 <209），它对应的页是页9。
第二步（确定记录）：在页中使用二分法找到主键值为20的记录。
3、目录项纪录的页
- 目录项记录的record_type值是1，而普通用户记录的record_type值是0。最小纪录record_type值是2，最大纪录record_type值是3。
- 目录项记录只有主键值和页的编号两个列
4、多个目录项纪录的页
5、目录项记录页的目录页
数据页之间用双向链表指针连接，数据页中的记录用单向链表指针连接。
三、常见的索引概念
索引按照物理实现方式：
- 聚簇索引
- 非聚簇索引（二级索引或者辅助索引）
3.1聚簇索引
聚簇索引是一种数据存储方式(所有的用户记录都存储在了叶子节点)，也就是所谓的索引即数据，数据即索引。
上图的特点
1、使用记录主键列c1的大小进行记录和页的排序，这包括三个方面的含义：
- 页内的记录是按照主键的大小顺序排成一个单向链表 。
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表 。
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
2、B+树的叶子节点存储的是完整的用户记录（存储了所有列的值（包括隐藏列））。
3、目录项记录中是主键值+页号。
优点
- 数据访问更快：因为聚簇索引将索引和数据保存在同一个B+树中
- 聚簇索引对于主键的排序查找和范围查找速度非常快：因为索引已经排好序了
- 节省了大量的io操作：按照聚簇索引排列顺序查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据。
缺点
- 插入速度严重依赖于插入顺序
- 更新主键的代价很高
- 二级索引访问需要两次索引查找：第一次找到主键值，第二次根据主键值找到行数据
3.2非聚簇索引
非聚簇索引是一种数据存储方式（InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM的非聚簇索引data域存储相应记录的地址）
上图的特点
这个B+树与上边的聚簇索引存在不同：
1、使用记录非主键列c2的大小进行记录和页的排序，这包括三个方面的含义:
- 页内的记录是按照c2列的大小顺序排成一个单向链表。
- 各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表。
2、B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键值这两个列的值。
3、目录项记录中是c2列+页号。（为了保证内节点中目录项记录的唯一性：c2列+主键值+页号）
非聚簇索引需要进行回表
根据c2列的值查找到完整的用户记录：
- 根据以c2列大小排序的B+树确定我们要查找记录的主键值。
- 根据查找的主键值在聚簇索引中查找一下完整的用户记录，这就叫做回表。
3.3聚簇索引和非聚簇索引对比
- 聚簇索引的叶子节点存储的是数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
- 一个表只能有一个聚簇索引，但可以有多个非聚簇索引
- 使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新的效率会比非聚簇索引低。
四、INNODB和MYISAM的数据结构
INNODB存储引擎：
- 使用B+树的聚簇索引：叶子节点存储的是完整的用户记录
- 使用B+树的非聚簇索引：叶子节点存储的data域存储的是主键值
MYISAM存储引擎：(不支持聚簇索引)
- 使用B+树的非聚簇索引：叶子节点存储的data域存储的是主键列或非主键列+数据记录的物理地址
4.1INNODB存储引擎
INNODB中索引和数据存放在一个文件中：
- 按照主键列的大小在B+树中进行排序是聚簇索引，叶子节点存储的是完整的用户记录
- 按照非主键列的大小在B+树中进行排序是非聚簇索引（查找数据时需要回表），叶子节点存储的data域存储的是非主键列+主键值
以主键列c1的大小进行排序
以非主键列c2的大小进行排序
4.2MYISAM存储引擎
MYISAM中索引和数据分开存储：
- 按照主键列或者非主键列的大小在B+树中进行排序是非聚簇索引，叶子节点存储的data域存储的是主键列或非主键列+数据记录的物理地址
- 将表中的记录按照记录的插入顺序单独存储在一个数据文件中
以主键列c1的大小进行排序
以非主键列c2的大小进行排序
4.3MyISAM与InnoDB对比
- 在InnoDB存储引擎中需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中根据主键值对非聚簇索引需要进行一次回表操作。
- InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM非聚簇索引记录的是数据的地址。
- InnoDB的数据文件本身就是索引文件；而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
- MyISAM的回表速度比INNODB的速度快，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录。
- InnoDB要求表必须有主键。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。
五、建立索引的优点和缺点
优点
- 数据库的IO成本
- 数据的唯一性
- 对于关联查询提高查询速度
- 减少查询中分组和排序的时间
缺点
- 创建索引和维护索引要耗费时间
- 索引需要占磁盘空间
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度 。
六、mysql数据结构的类型
6.1 HASH结构
HASH效率高，为什么索引结构还设计成树型
树的查询、插入、修改的平均复杂度都是O(log2N)
哈希的查询、插入、修改的平均复杂度都是O(1)
- hash索引只能满足等值查询（=、<>、IN），不能满足范围查询：因为hash存储是无序的。
- 在hash索引中不能使用order by：因为hash是无序的
- 在hash索引中不能使用联合索引：因为hash值是将联合索引键合并后一起计算的，无法对联合索引中单独的键进行查询。
- 不能使用在重复值多的列上
6.2 B树
- B树在插入和删除节点的时候如果导致树不平衡，就通过自动调整节点的位置来保持树的自平衡。
- 关键字集合分布在整棵树中,即叶子节点和非叶子节点都存放数据。搜索有可能在非叶子节点结束
- 其搜索性能等价于在关键字全集内做一次二分查找。
- 所有叶子节点位于同一层。
- 每个中间节点包含k-1个关键字和k个孩子（孩子节点的数量等于关键字的数量加1）
6.3 B+树和B树的差异：
- B+树有k个孩子的节点就有k个关键字，也就是孩子数量 = 关键字数；而B树中，孩子数量 = 关键字数+1。
- B+树非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最小值。
- B+树非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录 。
- B+树所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。
B树和B+树都可以作为索引的数据结构，为什么在MySQL中采用的是B+树：
- B+树查询效率更稳定。因为B+树每次只有访问到叶子节点才能找到对应的数据，而在B树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。|
- B+树的查询效率更高。同样的磁盘页大小，B+树的非叶子节点可以存储更多的节点关键字。因此通常B+树比B树更矮胖（阶数更大，深度更低)，查询所需要的磁盘IO也会更少。
- 在查询范围上，B+树的效率也比B树高。这是因为所有关键字都出现在B+树的叶子节点中，叶子节点之间会有指针，数据又是递增的，这使得我们范围查找可以通过指针连接查找。而在B树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多。
为了减少IO，索引树会一次性加载吗？
- 数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也会很大，超过几个G。
- 当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是:逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。
B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO
Hash 索引与 B+ 树索引是在建索引的时候手动指定的吗？
B+树的实际形成过程
- 每当为某个表创建一个B+树索引(聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
- 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
- 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。
这个过程特别注意的是:一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是工nnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。
R树
R-Tree在MySQL很少使用，仅支持 geometry数据类型
七、数据页内部的结构
7.1磁盘与内存的交互基本单位：数据页
以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
7.2页的上层结构
- 区(Extent)是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB= 1MB。
- 段(Segment)由一个或多个区组成。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。
- 表空间（Tablespace)是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。
为什么要有区
B+树的每一层中的页都会形成一个双向链表，如果以页为单位来分别存储空间的话，双向链表相邻的两个也之间的物理位置可能离的远，就会导致随机IO，消耗内存。如果数据量大的情况下需要按照区为单位分配存储空间，一个区中就可以有物理位置上连续的64个页，就会是顺序IO。（顺序IO的速度比随机IO快）
为什么要有段
如果把数据页和索引页都放入一个区中，在进行查找的时候还需要区分数据页和索引页，这样效率很低。因此把数据页单独存放一个区，索引页单独存放一个区。存放数据页的区的集合就是一个数据段。存放索引页的区的集合就是一个索引段。
段是一个逻辑概念，由若干个零散的页面和一些完整的区组成。
为什么要有碎片区
1、一个索引会生成两个段，而段以区为单位申请存储空间时，一个区占用1MB的存储空间，如果只存储少量的数据，就需要2MB的存储空间。如果再添加一个索引还需要申请2MB的存储空间。就会造成空间的浪费。这时提出了碎片区。
2、碎片区是直属于表空间，如果每个段的数据很少那么该碎片区域对其他的数据段共享，可以存放数据。
- 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
- 当某个段已经占用了32个碎片区页面之后，就会申请以完整的区为单位来分配存储空间。
区的分类
区的大致分类：
- 空闲的区(FREE)：现在还没有用到这个区中的任何页面。
- 有剩余空间的碎片区(FREE_FRAG)：表示碎片区中还有可用的页面。
- 没有剩余空间的碎片区(FULL_FRAG)：表示碎片区中的所有页面都被使用，没有空闲页面。
- 附属于某个段的区(FSEG)：每一个索引都可以分为叶子节点段和非叶子节点段。
空闲的区、有剩余空间的碎片区、没有剩余空间的碎片区**都属于表空间**
附属于某个段的区**属于某个端**
7.3数据页中的页目录（Page Directory）
页目录分组的个数如何确定
1、记录头信息中的n_owned字段：页目录中每个组中最后一条记录的n_owned字段会表示该组一共有多少条记录。
2、InnoDB规定:对于最小记录所在的分组只能有1条记录，最大记录所在的分组拥有的记录条数只能在1到8条之间，剩下的分组中记录的条数范围只能在是4到8条之间。
- 初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
- 之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
- 在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的地址偏移量
3、如何查找数据，针对上图
页目录结构下如何快速查找记录
在一个数据页中查找指定主键值的记录：
- 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。
- 通过记录的next_record属性遍历该槽所在的组中的各个记录。
7.4数据页中的用户记录（User Records）的格式
用户记录的格式：compact、Redundant、Dynamic、Compressed
mysql5.7和8.0的默认格式：Dynamic
7.4.1compact行格式（mysql5.7版本之前）
1、变长字段长度列表
在Compact行格式中，把所有变长字段（varchar、varbinary、text、blob）的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。
2、NULL值列表：该值为1时代表为NULL值、该值为0时代表不为NULL值
3、记录头信息
- delete_mask：表示该记录是否被删除（0表示未删除、1表示已删除）
- min_rec_mask：B+树的每个非叶子结点中的最小记录会把该值标志位1
- record_type：值为0普通记录；值为1目录项记录；值为2最小记录；值为3最大记录
- heap_no：值为0最小记录；值为1最大记录；其他记录依次加1
- n_owned：页目录中每个组中最后一条记录的n_owned字段会记录该组一共有多少条记录
- next_record：下一条记录的真实数据的地址偏移量
4、记录的真实数据（自己定义的列、三个隐藏列）
隐藏列：
一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。
7.4.2Redundant行格式（mysql5.0以及之前的版本）
与compact行格式的记录头信息相比：
- redundant行格式多了n_field和1byte_offs_flaf两个属性
- redundant行格式没有record_type属性
7.4.3dynamic和compressed行格式(mysql5.7之后)
行溢出：如果一个16KB页中存放一个varchar(65535）的数据可能不够，这时就会出现行溢出。
dynamic和compressed：存放BLOB数据时采用完全的行溢出方式（在数据页中只存放20字节的指针指向溢出页的地址），实际的数据都存放在溢出页中。
compact和redundant：采用部分行溢出，只会存储该列的一部分数据768个字节，再使用20字节的指针指向溢出页的地址。