Mysql数据存储（ 聚簇索引） 与Oracle（ 堆表） 索引差异

mysql 索引和数据页是如何互相影响的？

我已经查看了好几次mysql官方关于innodb这部分的文档，也在网上搜索了很久，也没有明白下面的问题。

mysql每个数据表的数据是以数据页的形式存储在磁盘上的（每页默认16k），读取到缓冲池也是以页的形式放入到链表中。问题就来了，索引是独立存储的还是和数据页一起存储的，如果是和数据页一起存储的，那么这个索引只存这一页的数据吗？

如果是单独存储的，如果内存空间不够存储整棵B+树，但是我要查的数据又正好在不够的部分。按照官方文档的说法，会去磁盘读取对应的数据页到缓冲池，那么现在mysql如何知道要去找磁盘上的哪一页呢？

看了下问题，感觉你纠结的点在于对页的概念上，我尝试解答一下你的问题。

1. 页：关于页你说的是对的，没错，16k 为一页，页也是对应到 buffer pool 中的，一页中会保存很多的索引数据，因为 B+ 树的特性，在非叶子节点不保存数据，索引非叶子节点只保存关键值和下个节点的指针，如果是 int 为例，就是8字节+指针6字节，就是 14 字节，那一页就可以保存 大概 1170 条数据。

2. 聚簇：又因为mysql 的索引即数据，数据集索引，主键构成的聚簇索引就是原始数据，所以聚簇的叶子节点就是数据，对应磁盘上的数据段，下面存储有讲

------ Mysql 16 K Oracle 索引8K

3. 读取：如果不考虑预读的话，一次读取是一页，也就是 16K，也就是对应 mysql B+ 树上的一个节点。举个例子。在mysql 中是 8 个字节，指针 6 个字节，就是 14 个字节，根节点页 16k = 16384 字节，也就是根节点可以存放 1170 条数据，如果是高度为2的树的话，1170 对应到 1170 页，就是可以索引 1170 * 16k = 18720k 的记录，大约是 1170 (页) * 1170 (每一页可以保存检索的数据) = 1368900，对应磁盘是 18M，类推可以得出如果树的层高是3层的话，可以支撑 21.9G 的数据，大约是 16亿的数据量。(4层之后性能就慢一倍，这就是mysql分库分表的由来，3层树高是通用高度，由于叶子节点实际数据不止14字节（整数类型 int）（叶子节点以140字节计算）。所以1170*1170*1170/10=1.6亿)

4. 存储：在 mysql 8.0 中，物理上一个 tablespace 可以有多个文件，一个表空间 tablespace 中分多个 segment，比如索引段和数据段，又因为mysql 聚簇索引即为数据，所以数据段则为聚簇索引的叶子节点，索引段即为聚簇索引的非叶子节点。（不要总用oracle堆表去理解索引段）

------ Mysql 16 K Oracle 索引8K

索引为什么可以提高查询效率：

1. 小结一下，mysql 的索引是 B+ 树构成，查找一条记录的时间复杂度是 O(logn)，logn = 树的高度，因为是采用的 B+ 树结构，一般生产环境 3-4 的树高已经差不多是极限了，如果主键是 int，那么聚簇树层高是3的 B+树可以支持 21.9G 的数据，大概是 16亿条。int（8字节，一般表记录 1xx字节）

2. select 一条记录怎么找到数据的：这个又分为聚簇索引和非聚簇索引，如果是聚簇索引，叶子节点即为数据，查询到直接返回，如果是非聚簇叶子节点是聚簇id，需要回表（二级索引存键值+主键）

3. 如何分析执行效率：mysql 提供了 explain 语句，可以观察到你的 sql 是否走了索引，是否回表等。

4. hashcode：mysql 有 adaptive hash index，但是并不是你说的主键转换 hash code，索引查找是基于 B树查找的，adaptive hash index，是基于 B+ 树上构建的 hash 索引表，对于热点等值查询的 sql 进行了优化，把时间复杂度从 O(logn) 减少到了 O(1)。

5. 主键冲突：这个问题其实可以再拆分成2个问题，第一个是怎么判断主键冲突，这个简单，有就有，没有就没有，第二个问题是要控制并发，这个和 mysql 的锁知识相关，在写入的时候会有区间锁和排他锁，来保证并发的问题。

1.B树索引的相关概念

      索引与表一样，也属于段（segment）的一种。里面存放了用户的数据，跟表一样需要占用磁盘空间。只不过，在索引里的数据存放形式与表里的数据存放形式非常的不一样。在理解索引时，可以想象一本书，其中书的内容就相当于表里的数据，而书前面的目录就相当于该表的索引。同时，通常情况下，索引所占用的磁盘空间要比表要小的多，其主要作用是为了加快对数据的搜索速度，也可以用来保证数据的唯一性。

      但是，索引作为一种可选的数据结构，你可以选择为某个表里的创建索引，也可以不创建。这是因为一旦创建了索引，就意味着oracle对表进行DML（包括INSERT、UPDATE、DELETE）时，必须处理额外的工作量（也就是对索引结构的维护）以及存储方面的开销。所以创建索引时，需要考虑创建索引所带来的查询性能方面的提高，与引起的额外的开销相比，是否值得。

      从物理上说，索引通常可以分为：分区和非分区索引、常规B树索引、位图（bitmap）索引、翻转（reverse）索引等。其中，B树索引属于最常见的索引，由于我们的这篇文章主要就是对B树索引所做的探讨，因此下面只要说到索引，都是指B树索引。

      B树索引是一个典型的树结构，其包含的组件主要是：

1)     叶子节点（Leaf node）：包含条目直接指向表里的数据行。

2)     分支节点（Branch node）：包含的条目指向索引里其他的分支节点或者是叶子节点。

3)     根节点（Root node）：一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。

可以用下图一来描述B树索引的结构。其中，B表示分支节点，而L表示叶子节点。

对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。比如从上图一可以看到，对于根节点块来说，包含三条记录，分别为（0 B1）、（500 B2）、（1000 B3），它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。

      对于叶子节点块来说，其所包含的索引条目与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）也具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。如果索引是创建在非分区表上或者索引是分区表上的本地索引的话，则该ROWID占用6个字节；如果索引是创建在分区表上的全局索引的话，则该ROWID占用10个字节。

      知道这些信息以后，我们可以举个例子来说明如何估算每个索引能够包含多少条目，以及对于表来说，所产生的索引大约多大。对于每个索引块来说，缺省的PCTFREE为10％，也就是说最多只能使用其中的90％。同时9i以后，这90％中也不可能用尽，只能使用其中的87％左右。也就是说，8KB的数据块中能够实际用来存放索引数据的空间大约为6488（8192×90％×88％）个字节。

      假设我们有一个非分区表，表名为warecountd，其数据行数为130万行。该表中有一个列，列名为goodid，其类型为char（8），那么也就是说该goodid的长度为固定值：8。同时在该列上创建了一个B树索引。

在叶子节点中，每个索引条目都会在数据块中占一行空间。每一行用2到3个字节作为行头，行头用来存放标记以及锁定类型等信息。同时，在第一个表示索引的键值的字段中，每一个索引列都有1个字节表示数据长度，后面则是该列具体的值。那么对于本例来说，在叶子节点中的一行所包含的数据大致如下图二所示：

 

从上图可以看到，在本例的叶子节点中，一个索引条目占18个字节。同时我们知道8KB的数据块中真正可以用来存放索引条目的空间为6488字节，那么在本例中，一个数据块中大约可以放360（6488/18）个索引条目。而对于我们表中的130万条记录来说，则需要大约3611（1300000/360）个叶子节点块。

mysql /14  16K可以放11xx个条目

      而对于分支节点里的一个条目（一行）来说，由于它只需保存所链接的其他索引块的地址即可，而不需要保存具体的数据行在哪里，因此它所占用的空间要比叶子节点要少。分支节点的一行中所存放的所链接的最小键值所需空间与上面所描述的叶子节点相同；而存放的索引块的地址只需要4个字节，比叶子节点中所存放的ROWID少了2个字节，少的这2个字节也就是ROWID中用来描述在数据块中的行号所需的空间。因此，本例中在分支节点中的一行所包含的数据大致如下图三所示：

从上图可以看到，在本例的分支节点中，一个索引条目占16个字节。根据上面叶子节点相同的方式，我们可以知道一个分支索引块可以存放大约405（6488/16）个索引条目。而对于我们所需要的3611个叶子节点来说，则总共需要大约9个分支索引块。

      这样，我们就知道了我们的这个索引有2层，第一层为1个根节点，第二层为9个分支节点，而叶子节点数为3611个，所指向的表的行数为1300000行。但是要注意，在oracle的索引中，层级号是倒过来的，也就是说假设某个索引有N层，则根节点的层级号为N，而根节点下一层的分支节点的层级号为N-1，依此类推。对本例来说，9个分支节点所在的层级号为1，而根节点所在的层级号为2。

聚簇索引

InnoDB在每行数据都增加三个隐藏字段，一个唯一行号，一个记录创建的版本号，一个记录回滚的版本号。

在多版本并发控制中，为了保证数据操作在多线程过程中，保证事务隔离的机制，降低锁竞争的压力，保证较高的并发量。在每开启一个事务时，会生成一个事务的版本号，被操作的数据会生成一条新的数据行（临时），但是在提交前对其他事务是不可见的，对于数据的更新（包括增删改）操作成功，会将这个版本号更新到数据的行中，事务提交成功，将新的版本号更新到此数据行中，这样保证了每个事务操作的数据，都是互不影响的，也不存在锁的问题。

 

undo-log
undo log是为回滚而用，具体内容就是copy事务前的数据库内容（行）到undo buffer，在适合的时间把undo buffer中的内容刷新到磁盘。undo buffer与redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内容会也会被刷新到磁盘；与redo log不同的是，磁盘上不存在单独的undo log文件，所有的undo log均存放在主ibd数据文件中（表空间），即使客户端设置了每表一个数据文件也是如此。

InnoDB存储引擎在数据库每行数据的后面添加了三个字段
6字节的事务ID(DB_TRX_ID)字段：用来标识最近一次对本行记录做修改(insert|update)的事务的标识符，即最后一次修改(insert|update)本行记录的事务id。至于delete操作，在innodb看来也不过是一次update操作，更新行中的一个特殊位将行表示为deleted，并非真正删除。
7字节的回滚指针(DB_ROLL_PTR)字段：指写入回滚段(rollback segment)的 undo log record (撤销日志记录记录)。如果一行记录被更新, 则 undo log record 包含 ‘重建该行记录被更新之前内容’ 所必须的信息。
6字节的DB_ROW_ID字段：包含一个随着新行插入而单调递增的行ID，当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。
结合聚簇索引的相关知识点，如果表中没有主键或合适的唯一索引，也就是无法生成聚簇索引的时候，InnoDB会帮我们自动生成聚集索引，但聚簇索引会使用DB_ROW_ID的值来作为主键；如果有主键或者合适的唯一索引，那么聚簇索引中也就不会包含 DB_ROW_ID了 。
 

Mysql 索引和Oracle索引的差别。

这个B+树与上边介绍的聚簇索引有几处不同：

• 　　使用记录a2列的大小进行记录和页的排序

• 　　页内的记录是按照a2列的大小顺序排成一个单向链表。

• 　　各个存放用户记录的页也是根据页中记录的a2列大小顺序排成一个双向链表。

• 　　存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的a2列大小顺序排成一个双向链表。

• 　　B+树的叶子节点存储的并不是完整的用户记录，而只是a2列+主键这两个列的值。

和Oracle最大的差别是Oracle存的是A2+rowid的值，这样可以直接回表。 Mysql 回表时还要一次索引查询，多了几个IO。

2.2.3.1. 页的结构

2.2.3.1. checksum 检测页是否损坏

页是磁盘写入的最小单元，一个页是 16k，如果磁盘不支持 16k 的原子写入，即意味着在某些极端情况写，写入会会部分成功，可能写入了4k宕机了，那怎么识别当前这个页是否是损坏的页。

基于每一页最后有8个字节的 file tailer，通过 checksum 算法来识别文件内容是否和尾部的8个字节一致，如果是一致的则证明这页磁盘是完整的。

2.3. double write buffer

上面说的 check sum 是为了发现磁盘是否损坏，但是如果磁盘已经损坏了，怎么解决呢？此时通过 redo log 已经无法恢复了，因为 redo log 是记录修改记录，无法识别，这页哪里损坏了。double write 的设计就是为了解决这个问题。

double write 在内存和磁盘上都有一个大小为 2M 的空间，128个连续的页，在写入磁盘之前，会先写入 double write，当写入达到了 16k（即一个页的大小）之后，再开始写入磁盘文件。

2.3.1. 异常恢复

如果出现了写入磁盘突然发生断电，发生了磁盘部分写的问题的时候，当mysql 重启的时候，会基于文件的 checksum 算法来判断磁盘页是否完整，如果磁盘页是不完整的，在通过 double write buffer，来替代这页数据，当这页数据被加载了之后，内存中也就变成了脏页，之后在通过 checkpoint 刷盘即可。

2.3.2. 性能影响

双写是肯定会对性能有影响的，但是 double write 是顺序写，而写磁盘文件是随机磁盘 IO，顺序写的性能要比随机写的性能高很多，所以总体对性能有影响，但不会很大

oracle 没有双写 只有redo记录变化矢量，所以磁盘坏了需要从backup或者adg恢复

作者|孔宇(梓轩)

　　前言

　　大家都知道 MySQL 的数据都是保存在磁盘的，那具体是保存在哪个文件呢？MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件自然也不同。InnoDB 是我们常用的存储引擎，也是 MySQL 默认的存储引擎。本文主要以 InnoDB 存储引擎展开讨论。

　　InnoDB简介

　　InnoDB是一个将表中的数据存储到磁盘上的存储引擎。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级。所以当我们想从表中获取某些记录时，InnoDB存储引擎需要一条一条的把记录从磁盘上读出来么？想要了解这个问题，我们首先需要了解InnoDB的存储结构是怎样的。

　　InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位innodb_page_size选项指定了MySQL实例的所有InnoDB表空间的页面大小。这个值是在创建实例时设置的，之后保持不变。有效值为64KB，32KB，16KB(默认值 )，8kB和4kB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

　　InnoDB 行格式

　　我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。一行记录可以以不同的格式存在InnoDB中，行格式分别是compact、redundant、dynamic和compressed行格式。可以在创建或修改的语句中指定行格式：

　　-- 创建数据表时,显示指定行格式CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;-- 创建数据表时,修改行格式ALTER TABLE 表名 ROW_FORMAT=行格式名称;-- 查看数据表的行格式show table status like '';

　　mysql5.0之前默认的行格式是redundant，mysql5.0之后的默认行格式为compact ， 5.7之后的默认行格式为dynamic

　　compact格式

　　记录的额外信息

　　记录的额外信息：分别是变长字段长度列表、NULL值列表和记录头信息

　　1：变长字段长度列表

　　mysql中支持一些变长数据类型（比如VARCHAR(M)、TEXT等），它们存储数据占用的存储空间不是固定的，而是会随着存储内容的变化而变化。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放

　　变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的 。

　　并不是所有记录都有这个 变长字段长度列表 部分，比方说表中所有的列都不是变长的数据类型的话，这一部分就不需要有

　　2：NULL值列表

　　NULL值列表：Compact格式会把所有可以为NULL的列统一管理起来，存在一个NULL值列表，如果表中没有允许为NULL的列，则NULL值列表也不复存在了。

　　为什么要有NULL值列表？

　　表中的某些列可能存储NULL值，如果把这些NULL值都放到记录的真实数据中存储会很浪费空间，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中，它的处理过程是这样的：

　　首先统计表中允许存储NULL的列有哪些。

　　根据列的实际值，用0或者1填充NULL值列表，1代表该列的值为空，0代表该列的值不为空。

　　如果表中没有允许存储 NULL 的列，则 NULL值列表 也不存在了。

　　3：记录头信息

　　名称

　　大小(单位:bit)

　　描述

　　预留位1

1

　　未使用

　　预留位2

1

　　未使用

　　delete_mask

1

　　标记改记录是否被删除

　　min_rec_mask

1

　　B+树非叶子节点中最小记录都会添加该标记

　　n_owned

4

　　当前记录拥有的记录数

　　heap_no

13

　　当前记录在记录堆的位置信息

　　record_type

3

　　记录类型 

　　0：普通记录 

　　1：B+树非叶子节点记录

　　2：最小记录

　　3：最大记录

　　next_record

16

　　下一条记录的相对位置

　　redundant 格式

　　与compact 格式相比, 没有了 变长字段列表以及 NULL值列表, 取而代之的是 记录了所有真实数据的偏移地址表 ，偏移地址表 是倒序排放的, 但是计算偏移量却还是正序开始的从row_id作为第一个, 第一个从0开始累加字段对应的字节数。在记录头信息中, 大部分字段和compact 中的相同，但是对比compact多了。

　　n_field(记录列的数量)、1byte_offs_flag(字段长度列表每一列占用的字节数)，少了record_type字段。

　　因为redundant是mysql 5.0 以前就在使用的一种格式, 已经非常古老, 使用频率非常的低，这里就不过多表述。

　　dynamic 格式

　　在现在 mysql 5.7 的版本中,使用的格式就是 dynamic。

　　dynamic 和 compact 基本是相同的，只有在溢出页的处理上面,有所不同。

　　在compact行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的前768个字节的数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址，从而可以找到剩余数据所在的页。

　　这种在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中的情况就叫做行溢出，存储超出768字节的那些页面也被称为溢出页（uncompresse blob page）。

　　dynamic中会直接在真实数据区记录 20字节 的溢出页地址, 而不再去额外记录一部分的数据了。

　　行溢出临界点

　　MySQL中规定一个页中至少存放两行记录。简单理解：因为B+树的特性，如果不存储至少2条记录，则这个B+树是没有意义的，形不成一个有效的索引。

　　每个页除了存放我们的记录以外，也需要存储一些额外的信息，大概132个字节。

　　每个记录需要的额外信息是27字节。假设一个列中存储的数据字节数为n，如要要保证该列不发生溢出，则需要满足：132 + 2×(27 + n)

　　compressed 格式

　　compressed 格式将会在Dynamic 的基础上面进行压缩处理特别是对溢出页的压缩处理，存储在其中的行数据会以zlib的算法进行压缩，因此对于blob、text这类大长度类型的数据能够进行非常有效的存储。但compressed格式其实也是以时间换空间，性能并不友好，并不推荐在常见的业务中使用。

　　InnoDB 数据页结构

　　数据页代表的这块16KB大小的存储空间可以被划分为多个部分，不同部分有不同的功能

　　名称

　　中文名

　　大小

　　描述

　　File Header

　　文件头部

　　38字节

　　页通用信息

　　Page Header

　　页面头部

　　56字节

　　页专有信息

　　infimun + supermun

　　最小记录和最大记录

　　26字节

　　虚拟的行记录

　　User Rcords

　　用户记录

　　不确定

　　实际存储的行记录内容

　　Free Space

　　空闲空间

　　不确定

　　页中未使用的空间

　　Page Directory

　　页面目录

　　不确定

　　页中一些记录的相对位置

　　File Tariler

　　文件尾部

　　8字节

　　校验页的完整性

　　每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：

　　为了方便讲述

　　先创建一个表： CREATE TABLE test(         a1 INT,         a2 INT,         a3 VARCHAR(100),         PRIMARY KEY (a1)     ) CHARSET=ascii ROW_FORMAT=Compact;test表中插入几条记录：INSERT INTO test VALUES(1, 10, 'aaa'); INSERT INTO test VALUES(2, 20, 'bbb'); INSERT INTO test VALUES(3, 30, 'ccc'); INSERT INTO test VALUES(4, 40, 'ddd');

　　这些记录，就如下图所示，存储在User Rcords里

　　delete_mask这个属性标记着当前记录是否被删除。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已。所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

　　min_rec_maskB+树的每层非叶子节点中的最小记录都会添加该标记，min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。

　　n_owned在页目录分组时使用，每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。

　　heap_no这个属性表示当前记录在本页中的位置，从图中可以看出来，我们插入的4条记录在本页中的位置分别是：2、3、4、5。heap_no值为0和1的记录，称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录。

　　record_type这个属性表示当前记录的类型，一共有4种类型的记录，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录。

　　next_record它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。比方说第一条记录的next_record值为32，意味着从第一条记录的真实数据的地址处向后找32个字节便是下一条记录的真实数据。下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定Infimum记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）。

　　从图中可以看出来，我们的记录按照主键从小到大的顺序形成了一个单链表。

　　Page Directory（页目录）

　　现在我们了解了记录在页中按照主键值由小到大顺序串联成一个单链表，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。因此在页结构中专门设计了页目录这个模块，专门给记录做一个目录，通过二分查找法的方式进行检索，提升效率。

　　1：将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。

　　2：每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。

　　3：将每个组的最后一条记录的地址偏移量单独提取出来，用作查找。

　　注意：这个页目录是为主键服务的。

　　需要注意的是：

　　第一：第一个小组，也就是最小记录所在的分组只能有1个记录;

　　第二：最后一个小组，就是最大记录所在的分组，只能有1-8条记录;

　　第三：剩下的分组中记录的条数范围只能在是 4-8 条之间；

　　分组是按照下边的步骤进行：

　　初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。

　　之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。

　　在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。

　　我们再添加8条记录看看效果：

　　INSERT INTO test VALUES(5, 50, 'eee'); INSERT INTO test VALUES(6, 60, 'fff'); INSERT INTO test VALUES(7, 70, 'ggg'); INSERT INTO test VALUES(8, 80, 'hhh'); INSERT INTO test VALUES(9, 90, 'iii');INSERT INTO test VALUES(10, 100, 'jjj');INSERT INTO test VALUES(11, 110, 'kkk');INSERT INTO test VALUES(12, 120, 'lll');

　　这里为了便于理解，图中只保留了用户记录头信息中的n_owned和next_record属性。

　　因为各个槽代表的记录的主键值都是从小到大排序的，所以我们可以使用二分法来进行快速查找。

　　所以在一个数据页中查找指定主键值的记录的过程分为两步：

　　1.通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最大的那条记录。

　　2.通过记录的next_record属性遍历该槽所在的组中的各个记录。

　　比方说我们查找主键值为x的记录，计算中间槽的位置(min+max)/2 =mid,查看mid槽对应的主键值y，若x

　　y，则max不变，min=mid。依此类推。

　　举例：我们想找主键值为6的记录，过程是这样的计算中间槽的位置：(0+3)/2=1，所以查看槽1对应记录的主键值为4，因为4

　　注意：若查到数据在槽2的分组中，由于槽2是指向最后一个记录，所以需要向上找一个槽位，定位到上一个槽位最后一行，然后再向下找。

　　File Header（文件头部）

　　File Header针对各种类型的页都通用，也就是说不同类型的页都会以File Header作为第一个组成部分，它描述了一些针对各种页都通用的一些信息，比方说这个页的编号是多少，它的上一个页、下一个页是谁等。

　　FIL_PAGE_OFFSET每一个页都有一个单独的页号，就跟你的身份证号码一样，InnoDB通过页号来可以唯一定位一个页。

　　FIL_PAGE_PREV和FIL_PAGE_NEXTFIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了。

　　B+树索引

　　InnoDB数据页的主要组成部分。各个数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录。再通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽。

　　在一个页中的查找：

　　以主键为搜索条件这个查找过程我们已经很熟悉了，可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

　　以其他列作为搜索条件对非主键列的查找的过程可就不这么幸运了，因为在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。

　　在很多页中查找：

　　1：定位到记录所在的页。

　　2：从所在的页内中查找相应的记录。

　　在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们上面聊过的查找方式去查找指定的记录。

　　索引

　　同样的，我们以上面建的表test为例，清空插入的数据，此时test表为一张空数据的表，为了便于讲述，我们可以简单的把test表的行格式理解如下：

　　一个简单的索引方案：我们为根据主键值快速定位一条记录在页中的位置而设立的页目录，目录中记录的数据页需要满足下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。

　　假设我们的每个数据页最多能存放3条记录（实际上一个数据页非常大，可以存放下很多记录），这时候我们向test表插入三条记录，那么数据页就如图所示：

　　test表中插入几条记录：INSERT INTO test VALUES(1, 10, 'aa'); INSERT INTO test VALUES(2, 20, 'bb'); INSERT INTO test VALUES(4, 40, 'dd');

　　此时我们再来插入一条记录：

　　INSERT INTO test VALUES(3, 30, 'cc');

　　因为上面定义了，一个页最多只能放3条记录，所以我们不得不再分配一个新页：

　　页1中用户记录最大的主键值是4，而页2中有一条记录的主键值是3，因为4 > 3，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为3的记录的时候需要伴随着一次记录移动，也就是把主键值为4的记录移动到页2中，然后再把主键值为3的记录插入到页1中。最后形成如图所示。

　　这个过程叫做页分裂。

　　真实数据存储中，数据页的编号并不是连续的，当我们在test表中插入多条记录后，可能是这样的效果：

　　因为这些16KB的页在物理存储上可能并不挨着，所以如果想从这么多页中根据主键值快速定位某些记录所在的页，我们需要给它们做个目录，每个页对应一个目录项，每个目录项由页中记录的最小主键值和页号组成。我们为上面几个页做目录，则如图：

　　比方说我们想找主键值为5的记录，具体查找过程分两步：

　　1：先从目录项中根据二分法快速确定出主键值为5的记录在目录2中（因为 4

　　2：再根据前边说的在页中查找记录的方式去页23中定位具体的记录。

　　这个目录有一个别名，称为索引。

　　InnoDB中的索引方案

　　在InnoDB中复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录。

　　用record_type来区分普通的用户记录还是目录项记录。

　　如果表中的数据太多，以至于一个数据页不足以存放所有的目录项记录，会再多整一个存储目录项记录的页。所以如果此时我们再向上图中插入一条主键值为10的用户记录的话：

　　在查询时我们需要定位存储目录项记录的页，但是这些页在存储空间中也可能不挨着，如果我们表中的数据非常多则会产生很多存储目录项记录的页，那我们怎么根据主键值快速定位一个存储目录项记录的页呢？其实也简单，为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据，所以现在各个页的示意图就是这样子：

　　用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。

　　聚簇索引

　　我们上边介绍的B+树本身就是一个目录，或者说本身就是一个索引。它有两个特点：

　　1：使用记录主键值的大小进行记录和页的排序

　　2：B+树的叶子节点存储的是完整的用户记录。

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

二级索引

原文中这处是不是有问题，这里还是A1的值不是A2索引那列的值

这个B+树与上边介绍的聚簇索引有几处不同：

• 　　使用记录a2列的大小进行记录和页的排序

• 　　页内的记录是按照a2列的大小顺序排成一个单向链表。

• 　　各个存放用户记录的页也是根据页中记录的a2列大小顺序排成一个双向链表。

• 　　存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的a2列大小顺序排成一个双向链表。

• 　　B+树的叶子节点存储的并不是完整的用户记录，而只是a2列+主键这两个列的值。

和Oracle最大的差别是Oracle存的是A2+rowid的值，这样可以直接回表。 Mysql 回表时还要一次索引查询，多了几个IO。

• 　　目录项记录中不再是主键+页号的搭配，而变成了a2列+页号的搭配。

　　索引的代价

　　1：空间上的代价每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间。

　　2：时间上的代价每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。

　　B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。

　　总结

　　通过对InnoDB存储逻辑分析，我们可以清楚的了解到mysql中是怎样对数据进行存储的。并且对索引树的结构进行分析，帮助我们在工作中更加合理的使用索引。