MySQL 的记录存储结构、存储引擎与 Buffer Pool

在 MySQL 中，数据到底是如何进行存储的呢

数据库中，SQL 语句执行过程

连接器：权限控制及用户登录
查询缓存：每一个查询 sql 语句执行后，会把查到的表存储起来，下一次遇到相同语句时直接返回这个表，不过因为这个功能太费现在已经移除了
分析器：检查 SQL 语句语法是否正确
优化器：一个 sql 语句会有多种执行方案，数据库会自动选择最优的方案发送给执行器（优化器最先执行的是 from 语句）
执行器: 执行语句，然后从存储引擎返回数据

优化器做了什么

我们无法规定一个程序员应该到某种不会写出慢查询的水平才被允许取使用 MySQL，大多数用户还是会写出非常消耗性能的代码的。MySQL 的设计者制定了一些优化的规则，尽可能的让消耗效率的 sql 变的快速

1，条件化简：

比如 MySQL 会省略一些没用的括号；移除没用的条件，比如为了拼接动态 sql 而写的 1 = 1

MySQL 会执行一些简单的表达式计算，但是这些表达式只能是常量，比如a > 1+1，会自动优化为a > 2。但是，如果某个列不是以单独的形式作为条件的，MySQL 就不会优化，比如函数调用ABS(a) > 5，以及使用运算符a + 1 > 2，等

常量传递：可以计算出数值的列，会自动替换为常量。比如a = 5 and b > a 会自动替换为 b > 5 and a = 5。这个功能可以与索引近似常量消耗一起使用（查询条件是主键索引自动替换为常量），在查询时对性能的提升很大

2，外连接消除：

我们知道内连接一般比外连接消耗要低，因为内连接的驱动表可以随意更换。MySQL 设计者就尽可能的将满足条件的外连接转化为内连接以提升效率。当我们设定被驱动表的条件不为 null 时（where a is not null，或者a = 4），此时这种情况叫空值拒绝（reject-NULL），将查询优化为内连接

3，子查询优化

一般的子查询都是按照人的逻辑去处理的，需要什么查询什么

但是如果使用 in 的话，MySQL 就会做优化了

Select Sname From Student
Where St not in (Select St From SC, Course C, Teacher T
                 Where T.Tname='李明' and SC.Ct=C.Ct and T.Tt=C.Tt);

上面的查询，里面被查询出的内容可能会非常多，我们不能让外面的条件一个个遍历查询。因此需要在这里做文章，我的思路是将内查询出的数据丢进一个 map 中提高查询效率。MySQL 会将里面查出来的内容做一个简单的临时表，如果该表的内容相对来说不是很大，内存装得下，就建立基于内存的使用 memory 引擎的临时表并且建立哈希索引；如果数据太多了，就建立基于磁盘的 innodb 引擎的临时表

引擎

引擎用来进行创建、查询、更新和删除数据，负责对硬件的交互，mysql 上层则负责解析执行语句，将 sql 翻译为引擎可以运行的字节码。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能

存放数据的位置（数据目录）

MySQL 的数据存放位置不是安装位置，安装位置下的bin文件夹有很多可执行文件，是MySQL的主要命令，而数据目录是可以自己配置的，在数据目录下，存放着所有的数据库文件夹

MySQL的每个数据库都对应存放在一个与数据库同名的文件夹中，而数据库文件夹都在你设定的或者系统默认的Mysql存储路径下

数据目录的大体构造是这样的：

数据目录

• 数据库文件夹
• • db.otp 存放数据库的属性的文件，包括字符集的定义、比较规则等
• • .frm 表的frm文件，存放了表的属性，比如列规则等
• • .MYI、MYD mydata、myindex分别是myisam 的存放数据与索引的文件
• • .idb表示innodb引擎的独立表空间，存放表中数据的地方
• 4个自带的数据库，存放用户信息等重要信息
• ibdata1，系统表空间，在之前的版本是innodb存放数据的地方（所有表的数据都存放在一起）

.frm文件

.frm文件存储数据表的框架结构，文件名与表名相同，每个表对应一个同名frm文件，与操作系统和存储引擎无关，即不管MySQL运行在何种操作系统上，使用何种存储引擎，都有这个文件

单独表空间与共享表空间

共享表空间会把表集中存储在一个系统表空间里。所有数据库的所有表的数据、索引文件全部放在一个文件中。该文件目录默认的是服务器的数据目录。默认的文件名为 ibdata1

独占表空间会为每一个表分别创建一个表空间，这时如果你使用的是 Innodb 引擎会在对应的数据库目录里每一个表都有.ibd文件（这个文件包括了单独一个表的数据内容以及索引内容），修改 innodb_file_per_table 来决定是否使用独占表空间

MyISAM 与 InnoDB 的不同

myisam 引擎会把数据存放成以下这样：

tab.frm：mysql 通用的存储表定义的文件
tab.MYD：存储数据
tab.MYI：存储索引

innodb 在开启独占表空间会将数据存放为：

tab.frm：mysql 通用的存储表定义的文件
tab.ibd：存放表中数据以及索引的地方

如果没有开启独占表空间，innodb 会把表中数据以及索引放在ibdata1中

MyISAM

有关 B+ 树的实现

B+Tree 叶节点的 data 域存放的是数据记录的地址，使用的是非聚集索引

数据的真实存储是按表与行存储的，表之间有双向链表，行之间有单向链表

InnoDB

四大特性

1，插入缓冲：在查询辅助索引且非唯一索引时会有一个缓存，每次对索引进行修改的时候，如果在缓冲池中的插入缓存中没有这个修改记录，会加入缓冲池，如果有则选择加入，并且按照一定的频率进行合并操作，将多个插入合并到一个查询索引操作中，减少 IO 次数

2，二次写：对缓冲池脏页进行刷新时，不是直接写磁盘，而是进行了以下步骤：

第一步：memcpy()函数将脏页先复制到内存中的doublewrite buffer；
第二步：通过doublewrite分两次，每次1M顺序的写入共享表空间的物理磁盘上。这个过程中，doublewrite页是连续的，因此这个过程是顺序的，所以开销并不大；
第三步：完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间文件中，此时写入是离散的，可能会较慢；
如果操作系统在第三步的过程中发生了崩溃，在恢复过程中，可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志

3，自动哈希：满足条件（使用某个索引次数较多时）时自动对二级索引改成哈希索引，可以带来速度的提升

4，预读：分为线性预读和随机预读，将数据从磁盘预先读取到 Buffer Pool 中，为了提高 IO 性能

有关 B+ 树的实现

使用B+树，使用聚集索引

索引的key是数据表的主键，InnoDB表数据文件本身就是主索引

其余的索引作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址。使用辅助索引查找时，先找到主键的值，再根据主键在B+树中搜索

MyISAM 与 InnoDB 的区别

InnoDB 支持事务，InnoDB 支持行级锁，InnoDB 支持外键，InnoDB 表必须有唯一索引，支持 MVCC，innoDB 支持数据库异常崩溃后的安全恢复

MyISAM 表格（数据）可以被压缩后进行查询操作

InnoDB 行格式

我们知道 innodb 是用页结构来存放数据的，每一页一般占16kb，而行中的记录被一个个的存放在页中，一般情况下，MySQL 一次读取磁盘中一页的数据量放到内存中，那页中的行结构又是怎么存储的呢

到目前为止有4中类型的行结构，不过都差不多，只有微小的细节差异。我们的建表语句就是定义行格式的语法，这里我们只了解COMPACT（契约）格式的行结构

额外信息

一个行数据由记录的额外信息与记录的真实数据一起构成，其中的变长字段长度列表是支持变长数据结构的实现，如果要存放一个非定长的01字节流，提前说好这个字节流到底有多大就可以了。因此，所有的变长字段（包括TEXT、VARCHAR等）都按照列的顺序逆序存放在了这个列表里

这个变长字段长度列表还有以下特性：

• 如果允许存储的最大字节数超过255个字节（比如char（10）用utf8编码，就是最大30字节）并且真实数据占用的字节超过127（为什么最高位为0呢，因为MySQL只要读到1即可认为该数据需要2字节来表示最大长度），则使用2字节来表示真实数据占用的字节数，否则只使用1字节表示
• 该字段表中只存放非null的列的内容长度，同时该表也有可能为null
• 为什么是逆序呢？因为读取信息的时候是从隐藏列开始读的

第二个是null值列表，只有没定义not null 的列才可能会使用到这个表。每个允许存储null 的列对应一个二进制位，并且按列的顺序逆位存放，二进制位的值为1，代表该列的值为null，否则不为null。同时，null的列表必须用整数个字节位表示

第三个是记录头信息，一共40位

记录头信息

delected_flag代表此记录是否被删除，对，就算名义上删除了记录记录还是可能会存在磁盘中，被删除的记录空间叫可重用空间，我们可以将新的记录放到这些空间中。为什么这么做呢？如果真的将其删除也只不过要清一次磁盘，总不可能删除数据后将整个页重新排序吧，所以不如不清

min_rec_flag每个非叶子节点的最小目录项都会添加该标记

heap_no表示当前记录在页面中的相对位置，它从2开始，记录被分配heap_no后就不会再变化heap_no了，每一个记录的heap_no都比它前面的那个记录的大1

n_owned组中的普通节点的n_owned值为0，组中的组长，也就是相对位置在槽中的数据该值为组中所有节点的个数

record_type代表当前记录类型，一共有4种类型，0表示普通记录，1表示非叶子节点记录，2是Infimum记录，3是supremum记录

next_record标记下一条记录的物理地址与当前位置有多少字节。如果该值为正数，说明下一条记录在该记录后面，如果该值为负数，说明下一条记录在该记录前面。下一条记录是根据主键值的大小来决定的，该值指向记录的隐藏列位置

隐藏列

Innodb 中每一行数据都可能有三个隐藏字段，隐藏列的位置在额外信息与真实数据之间：

DB_TRX_ID 事务ID：表示最后一次插入或更新该行的事务id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除

DB_ROLL_PTR 回滚指针：指向该行的 undo log 。如果该行未被更新，则为空

DB_ROW_ID 行 ID：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引，也就是 InnoDB 的默认主键

DB_ROW_ID 不是一定会生成的，如果没有定义主键，MySQL 会自己找个not null 的唯一索引键来当主键，如果连这个都没有才会生成行 ID

我们生成行 ID DB_ROW_ID 的方式也比较有意思。数据库在内存中维持一个全局变量，该变量在每次向隐藏主键的表中插入一条数据的时候才会加一，并且将该值赋给该数据的隐藏主键。每当这个变量为256 的倍数的时候，就会将该变量的值刷新到系统表空间页号为7的的页面中一个叫 MAX ROW ID 的属性，该属性占用8字节，这么做是为了避免频繁刷盘。同时，在数据库重启的时候，会将 MAX ROW ID 加256并且拉到磁盘中，这么做避免了在 redo 日志中出现比该值还大的隐藏主键

对char类型的存放

有以下特性：

1，如果此时的字符集是定长的（比如ascii），不会在字段长度表中生成记录；如果此时的字符集是变长的，该列占用的长度还是会放在长度表中

2，char（m）至少要占m个字节，varchar（m）这没有这些规定。这么设计的理由是如果修改的新值大于旧值的字节长度但是不大于m字节的时候可以在这m个字节的空间中直接替换，而不用再存储空间重新分配记录空间

溢出列

当行记录的长度没有超过行记录最大长度时，所有数据都会存储在当前页

当行记录的长度超过行记录最大长度时，变长列（variable-length column）会选择外部溢出页（overflow page，一般是Uncompressed BLOB Page）进行存储

有些行结构会保留一些数据在当前页，其余数据存放在溢出页，后面跟着20Byte的数据，用来存储指向溢出页的指针。而另外一些的实现则是将数据全部放再溢出列

如果数据太多了，一个页也放不下，则会进行生成指向下一个溢出页的指针

InnoDB 数据页结构

在MySQL的设定中，同一个表空间内的一组连续的数据页为一个extent（区），默认区的大小为1MB，页的大小为16KB。16*64=1024，也就是说一个区里面会有64个连续的数据页。连续的256个数据区为一组数据区

其实不用在意数据区有什么用，比较重要的是数据页，它里面储存了数据和其他东西（比如上下数据页的指针、上下界），它长下面这样

数据在页中的存储

记录是按照主键值由小到大连成的单向列表，在页中为了服务这些数据MySQL会存放一些额外信息

数据新增时会从Free Space部分申请一个记录大小的空间，这个Free Space就是尚未使用的部分，在申请之后，这部分空间就被划到user records部分

Infimum表示页中最小的数据，它可以算一条特殊的记录，而supremum是页中最大的数据。Infimum的作用是将文件中最小的指针快速找出来，supremum的作用是提供分组功能，supremum记录所在的组可以包含1到8条记录，除了Infimum一个人一组其他组都是4到8条记录

槽记录是单向链表，我们查找记录的时候不能一个个遍历，时间复杂度太高，为了快速找到需要的数据，需要用二分算法优化。我们将数据按1到8条记录一组分组，每个组长的相对位置都在槽中，这样又因为链表是由小到大的，只需要二分比较组长的大小即可快速找到需要的数据

页面头部（page header）

有了上面的说明，很轻松就能知道头部信息都代表了什么

PAGE_N_DIR_SLOTS：在Page Directory（页目录）中的Slot（槽）数。因为槽是不定长的，需要一个数来了解它有多少个

PAGE_HEAP_TOP：堆中还未使用的空间的指针，该指向的地址之后就是Free Space

PAGE_N_HEAP：第一位表示本记录是否为紧凑型记录，剩下15位表示堆中的记录数，在java中线程池源码中也使用了相同思想

PAGE_FREE：指向空闲列表的首指针，被删除的记录会链接成一个空闲链表

PAGE_GARBAGE：已删除记录占用的字节数

PAGE_LAST_INSERT：最后插入记录的位置

PAGE_DIRECTION：最后插入的方向。可能的取值为PAGE_LEFT（0x01），PAGE_RIGHT（0x02），PAGE_SAME_REC（0x03），PAGE_SAME_PAGE（0x04），PAGE_NO_DIRECTION（0x05）

PAGE_N_DIRECTION：一个方向连续插入记录的数量

PAGE_N_RECS：该页中记录的数量

PAGE_MAX_TRX_ID：修改当前页的最大事务ID，注意该值仅在Secondary Index定义

PAGE_LEVEL：当前页在索引树中的层级，0x00代表叶节点

PAGE_INDEX_ID：当前页属于哪个索引ID

PAGE_BTR_SEG_LEAF：B+树的叶节点中，文件段的首指针位置。注意该值仅在B+树的Root页中定义

PAGE_BTR_SEG_TOP：B+树的非叶节点中，文件段的首指针位置。注意该值仅在B+树的Root页中定义

文件头部（file header）

innodb中有很多类型的页，每一种页都以文件头部做为第一个组成部分，它描述了一些通用于各种页的信息

FIL_PAGE_SPACE_OR_CHKSUM：当MySQL版本小于MySQL-4.0.14，该值代表该页属于哪个表空间，这个一般不用了。之后版本的MySQL，该值代表页的checksum值，是该页的校验和，作用就是通过某种算法将长的字符串缩小为段的字符串，如果短的字符串都不一样，那长的一定不一样，对精细的信息模糊并且比较是为了优化性能

FIL_PAGE_OFFSET：每一个页都有自己的页号，通过页号来唯一标识一个页

FIL_PAGE_PREV，FIL_PAGE_NEXT：当前页的上一个页以及下一个页。B+Tree特性决定了叶子节点必须是双向列表

FIL_PAGE_LSN：该值代表该页最后被修改的日志序列位置LSN（Log Sequence Number）

FIL_PAGE_TYPE：页的类型，比如0x45BF，该值代表了索引页；0x000A溢出页；0x0002是undo日志页等

文件尾部（file trailer）

该部分用于检查页面的完整性，因为数据页是在内存中被修改的，然后才刷到磁盘中，如果因为一些情况导致刷入的数据不完整是非常有可能的，我们需要一些手段让磁盘知道接受数据的准确性。文件尾部由八个字节组成

前四个字节表示页的校验和，如果数据完整，这四个字节应该和文件头部的校验和一致

后四个字节表示最后一次修改的LSN的后四个字节，应该与文件头部的FIL_PAGE_LSN的后四个字节一致

独立表空间结构

如果是我写一个数据库，写完了页就可以直接用了。但是这么做会出现性能方面的问题，比如，磁盘 IO 数量过大，比如页太多不好管理，因此 MySQL 的底层做了优化，该优化就是解决这两个问题的，下面的概念具有以下几个特性：

• 从小的结构组成大的结构，化零为整，这都是为了方便管理
• 每个结构都有对应的头文件来管理，其中记录了相关属性、上下链表等内容
• 下面的优化初衷是为了解决把需要查找的页尽可能放在连续的空间中，而衍生出来的一系列问题，最终目的是为了减少磁盘 IO

区

区是一个抽象的概念，我们可以理解为，一段连续的表与一些散落的表构成了一个区。无论页的大小怎么变，区的大小默认总是为 1MB。为了保证区中的页的连续性，InnoDB 存储引擎一次从磁盘申请 4-5 个区，InnoDB 页的大小默认为 16kb，即一个区一共有 64（1MB/16kb=16）个连续的页

区被分为4类，也被称为区的四种状态

• 空闲的区
• 段中的区
• 满的区
• 未满的区

XDES ENTRY

这是管理区的数据结构

Segment id(8个字节)：每个段都有唯一的 id，该字段就代表区属于哪个段，当然必须当前区已经分配给某个段

List Node(12个字节)：这个部分将若干个extent descriptor entry，xdes 组成双向链表，这里面的结构就是Prev Node Page Number（4个字节），Prev Node Offset(2个字节)，Next Node Page Number（4个字节），Next Node Offset(2个字节)。若果我们在这里移动到某个位子，只要指向页号，和页内的偏移量即可

State(4个字节)：这就是前面我们说的，free空闲区，free_frag有空闲页的碎片区，full_frag没有空闲的碎片区，FSEG就是属于某个segment的区

Page State Bitmap(16个字节)：16个字节，也就是128个比特位。我们说一个区是64个页，所以这里就分为一个区代表2个比特位，这里两个比特位的意思代表什么呢？第一个比特位代表对应的页是否是空闲，第二个比特位暂时还没使用到

组

每256个区被划分为1组，每一组的开头的页都有特殊含义，你可能会猜到他们是用来管理组的，第一组开头的3个页是固定的

• FSP_HDR：用来登记整个表空间所有的属性以及该组所有区的属性
• IBUF_BITMAP：用来存储关于change_buffer的相关信息。数据的修改都是在内存中进行的，这是为了减少随机IO，每当数据库不忙的时候或者被修改的页被拉进内存的时候才会进行修改
• INODE：存放了很多被称为INODE_ENTRY类型的数据，INODE_ENTRY是用来管理组的

其他的组开头的两个页也是固定的：

• XDES：用来登记本组256个区的属性，和FSP_HDR对比只是没了一些功能
• IBUF_BITMAP

段

段是管理区的，常见的段有数据段、索引段、回滚段等，在InnoDB存储引擎中，对段的管理都是由引擎自身所完成的

常用的是索引段与数据段，他们分别聚集了非叶节点与叶节点，这么做是为了让可能被查找到的叶的物理位置相对靠近，可以减少IO次数

每个段开始，先用32页（page）大小的碎片页来存放数据，在使用完这些页之后才是64个连续页的申请。这样做的目的是，对于一些小表或者是undo类的段，可以开始申请较小的空间，节约磁盘开销

XDES ENTRY链表

每个索引都会维护两个段，每个段都会维护三个XDES链表，这是为了方便管理段中的区

• free链表：链表中的区包含的都是空闲页面，在新增数据的时候会遍历这个链表，优先将数据插入这些链表对应的区中
• not_full链表：没有被装满的区的链表
• full链表：区已经被装满了

为了找到这三个链表，MySQL会设置一个链表基节点，这个base node被放在表空间的固定位置

INODE INDEX

这个结构被用来管理段，结构中部分属性如下：

• segment id：用来唯一标识一个段
• 3个list base node：三个链表相关的基础节点
• fragment array entry：段是一些零散页面以及一些完整的区的集合，该结构就用于查找零散页面

由于每个段对应一个inode index数据结构，那我们如何知道某个段对应那个index呢？MySQL在索引的根节点中记录了这些信息，根数据页中的表头有page header部分，index 的偏差值就存放在这里

系统表空间

系统表空间除了多出来一部分存放基本系统表以及其他主要信息以外，和独立表空间没什么区别。而存放的这些元数据开头一般为SYS

其包含了数据库运行所要求的基本信息，如：数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体等等等等

Buffer Pool

如果 CPU 每找一页的数据就从磁盘中拉一次数据，那 MySQL 的性能以及流行程度一定会大打折扣，因为磁盘读取实在是太慢了，因此需要在内存中做一个缓存

Buffer Pool 就是 MySQL 存储引擎为了加速数据的读取速度而设计的缓冲机制，而且为了提高修改效率，Mysql 的所有当前读操作都是在 Buffer Pool 中执行的

用于存储并管理的数据结构

当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中

将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其起来，这样将来有请求再次访问该页面时，就可以省去磁盘的开销了

很简单对不对，接下来让我们看看其实现。Buffer Pool 中的页也是和在磁盘上默认的页一样的，为了更好的管理这些在中的缓存页，设计的大叔为每一个缓存页都创建了一些所谓的控制信息，这些控制信息包括该页所属的表空间编号、页号、缓存页在中的地址、链表节点信息等等

这些管理文件存储的实现大同小异，都是用一小块文件记录大数据的重要属性。MySQL 在磁盘中是这样，内存中也是这样，redis 的部分数据结构，Linux 文件管理等等这些也差不多

用于优化性能的数据结构

为了内存中的页更加快速的增删改查，MySQL 实现了很多链表，这里介绍三个比较重要的

空闲链表：读取一个页到中的时候该放到哪个缓存页的位置呢？或者说怎么区分中哪些缓存页是空闲的，哪些已经被使用了呢？

我们可以把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中，这个链表也可以被称作空闲链表，直接从表头获取一个页面插入即可。并且将这个表头元素从链表中删除

用哈希表来判断该页已经在缓存页中，表空间号加页号可以唯一确定一个页

flash 链表：数据的修改都是在磁盘中的，MySQL 按一定频率将数据刷入磁盘中， 为了知道那些是脏的数据页，我们不得不再创建一个存储脏页的链表，凡是修改过的缓存页对应的控制块都会作为一个节点加入到这个链表中，需要刷页的时候直接遍历这个链表即可

LRU 链表：内存大小毕竟是有限的，如果需要缓存的页占用的内存大小超过了大小的时候，继续将页面拉进内存需要将某些旧的缓存页从中移除，我们参考操作系统中的 LRU 算法实现了这个链表，每次移除链表末尾的页面