4.1 索引组织表
- 在InnoDB存储引擎中,表都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table)。在 InnoDB存储引擎表中,每张表都有个主键(Primary Key),如果在创建表时没有显式地定义主键,则InnoDB存储引擎会按如下方式选择或创建主键∶
- 首先判断表中是否有非空的唯一索引(Unique NOT NULL),如果有,则该列即为主键。
- 如果不符合上述条件,InnoDB存储引擎自动创建一个6字节大小的指针。
- 当表中有多个非空唯一索引时,InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。这里需要非常注意的是,主键的选择根据的是定义索引的顺序,而不是建表时列的顺序。看下面的例子∶
- _rowid可以显示表的主键,因此通过上述查询可以找到表z的主键。此外,虽然 c、d列都是非空唯一索引,都可以作为主键的候选,但是在定义的过程中,由于d列首先定义为唯一索引,故 InnoDB存储引擎将其视为主键。
- 另外需要注意的是,_rowid 只能用于查看单个列为主键的情况,对于多列组成的主键就显得无能为力了,如∶
4.2 InnoDB逻辑存储结构
从 InnoDB存储引擎的逻辑存储结构看,所有数据都被逻辑地存放在一个空间中,称之为表空间(tablespace)。表空间又由段(segment)、区(extent)、页(page)组成。页在一些文档中有时也称为块(block),InnoDB存储引擎的逻辑存储结构大致如图所示。
4.2.1 表空间
- 表空间可以看做是 InnoDB存储引擎逻辑结构的最高层,所有的数据都存放在表空间中。在默认情况下 InnoDB存储引擎有一个共享表空间 ibdata1,即所有数据都存放在这个表空间内。如果用户启用了参数
innodb_file_per_table
,则每张表内的数据可以单独放到一个表空间内。 - 如果启用了
innodb_file_per_table
的参数,需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap 页,其他类的数据,如回滚(undo)信息,插入缓冲索引页、系统事务信息,二次写缓冲(Double write buffer)等还是存放在 - InnoDB存储引擎不会在执行rollback 时去收缩这个表空间。虽然 InnoDB不会回收这些ibdata*空间,但是会自动判断这些 undo信息是否还需要,如果不需要,则会将这些空间标记为可用空间,供下次 undo 使用。
- 回想一下,在第2章中提到的 master thread每10秒会执行一次的 full purge操作,很有可能的一种情况是∶用户再次执行上述的 UPDATE 语句后,会发现 ibdata1不会再增大了。
4.2.2 段
- 表空间是由各个段组成的,常见的段有数据段、索引段、回滚段等。InnoDB存储引擎表是索引组织的(index organized),因此数据即索引,索引即数据。那么数据段即为B+树的叶子节点(Leaf node segment),索引段即为B+树的非索引节点(Non-leaf node segment)。
- 在 InnoDB存储引擎中,对段的管理都是由引擎自身所完成。
4.2.3 区
- 区是由连续页组成的空间,在任何情况下每个区的大小都为1MB。为了保证区中页的连续性,InnoDB存储引擎一次从磁盘申请4~5个区。在默认情况下,InnoDB存储引擎页的大小为16KB,即一个区中一共有64个连续的页。
- InnoDB 1.0.x版本开始引入压缩页,即每个页的大小可以通过参数KEYBLOCK SIZE设置为2K、4K、8K,因此每个区对应页的数量就应该为512、256、128。
- InnoDB 1.2.x版本新增了参数
innodb_page _size
,通过该参数可以将默认页的大小设置为 4K、8K,但是页中的数据库不是压缩。这时区中页的数量同样也为256、128。总之,不论页的大小怎么变化,区的大小总是为1M。 - 在用户启用了参数
innodb_file_per_talbe
后,创建的表默认大小是96KB。区中是64个连续的页,创建的表的大小至少是1MB才对啊?其实这是因为在每个段开始时,先用32个页大小的碎片页(fragment page)来存放数据,在使用完这些页之后才是64个连续页的申请。这样做的目的是,对于一些小表,或者是 undo 这类的段,可以在开始时申请较少的空间,节省磁盘容量的开销。这里可以通过一个很小的示例来显示InnoDB存储引擎对于区的申请方式∶
4.2.4 页
同大多数数据库一样,InnoDB有页(Page)的概念(也可以称为块),页是InnoDB 磁盘管理的最小单位。在InnoDB存储引擎中,默认每个页的大小为16KB。而从InnoDB 1.2.x版本开始,可以通过参数innodb_page_size
将页的大小设置为4K、8K、16K。若设置完成,则所有表中页的大小都为innodb_page_size
,不可以对其再次进行修改。除非通过 mysqldump 导入和导出操作来产生新的库。
在 InnoDB存储引擎中,常见的页类型有∶
- 数据页(B-tree Node)
- undo 页(undo Log Page)
- 系统页(System Page)
- 事务数据页(Transaction system Page)
- 插入缓冲位图页(Insert Buffer Bitmap)
- 插入缓冲空闲列表页(Insert Buffer Free List)
- 未压缩的二进制大对象页(Uncompressed BLOB Page)
- 压缩的二进制大对象页(compressed BLOB Page)
4.2.5 行
InnoDB存储引擎是面向列的(row-oriented),也就说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的,最多允许存放16KB/2-200行的记录,即 7992行记录。
4.3 InnoDB 行记录格式
- InnoDB存储引擎,记录是以行的形式存储的。这意味着页中保存着表中一行行的数据。在 InnoDB 1.0.x 版本之前,InnoDB存储引擎提供了 Compact 和 Redundant 两种格式来存放行记录数据,这也是目前使用最多的一种格式。Redundant 格式是为兼容之前版本而保留的,如果阅读过InnoDB的源代码,用户会发现源代码中是用
PHYSICAL RECORD(NEW STYLE)
和PHYSICAL RECORD(OLD STYLE)
来区分两种格式的。在 MySQL 5.1版本中,默认设置为 Compact行格式。用户可以通过命令SHOW TABLE STATUS LIKE 'table_name'
来查看当前表使用的行格式,其中 row format 属性表示当前所使用的行记录结构类型。
4.3.1 Compact 行记录格式
- Compact 行记录是在 MySQL5.0中引入的,其设计目标是高效地存储数据。简单来说,一个页中存放的行数据越多,其性能就越高。图显示了Compact 行记录的存储方式∶
从图可以观察到,Compact 行记录格式的首部是一个非 NULL 变长字段长度列表,并且其是按照列的顺序逆序放置的,其长度为∶
- 若列的长度小于255字节,用1字节表示;
- 若大于255个字节,用2字节表示。
变长字段的长度最大不可以超过2字节,这是因在 MySOL数据库中VARCHAR类型的最大长度限制为65535。变长字段之后的第二个部分是NULL标志位,该位指示了该行数据中是否有NULL值,有则用1表示。该部分所占的字节应该为1字节。接下来的部分是记录头信息(record header),固定占用5字节(40 位),每位的含义见表4-1。
最后的部分就是实际存储每个列的数据。需要特别注意的是,NULL 不占该部分任何空间,即 NULL除了占有 NULL标志位,实际存储不占有任何空间。另外有一点需要注意的是,每行数据除了用户定义的列外,还有两个隐藏列,事务 ID列和回滚指针列,分别为6字节和7字节的大小。若InnoDB表没有定义主键,每行还会增加一个6字节的 rowid列。
接下去用一个具体示例来分析 Compact 行记录的内部结构∶
在上述示例中,创建表mytest,该表共有4个列。t1、t2、t4都为VARCHAR变长字段类型,t3为固定长度类型 CHAR。接着插入了3条有代表性的数据,然后将打开表空间文件mytest.ibd(这里启用了innodb_file_per_table
,若没有启用该选项,打开默认的共享表空间文ibdatal)。
- 现在第一行数据就展现在用户眼前了。需要注意的是,变长字段长度列表是逆序存放的,因此变长字段长度列表为03 02 01,而不是01 02 03。此外还需要注意 InnoDB每行有隐藏列TransactionID和 Roll Pointer。同时可以发现,固定长度 CHAR字段在未能完全占用其长度空间时,会用0x20来进行填充。
- 接着再来分析下 Record Header 的最后两个字节,这两个字节代表 next recorder,0x2c代表下一个记录的偏移量,即当前记录的位置加上偏移量 0x2c 就是下条记录的起始位置。所以InnoDB 存储引擎在页内部是通过一种链表的结构来串连各个行记录的。
- 第二行将不做整理,除了RowID不同外,它和第一行大同小异,有兴趣的读者可以用上面的方法自己试试。现在来关心有NULL值的第三行
- 第三行有NULL值,因此NULL标志位不再是00而是06,转换成二进制为00000110,为1的值代表第2列和第3列的数据为NULL。在其后存储列数据的部分,用户会发现没有存储NULL列,而只存储了第1列和第4列非NULL的值。因此这个例子很好地说明了∶不管是CHAR类型还是VARCHAR类型,在compact 格式下NULL 值都不占用任何存储空间。
4.3.2 Redundant 行记录格式
Redundant是MySQL5.0版本之前InnoDB的行记录存储方式,MySQL5.0支持Redundant 是为了兼容之前版本的页格式。Redundant行记录采用如图4-3所示的方式存储。
Redundant行记录格式的首部是一个字段长度偏移列表,同样是按照列的顺序逆序放置的。若列的长度小于255字节,用1字节表示;若大于255字节,用2字节表示。第二个部分为记录头信息(record header),不同于Compact行记录格式,Redundant 行记录格式的记录头占用6字节(48 位),每位的含义见表4-2。从表4-2中可以发现,n_fields 值代表一行中列的数量,占用10位。同时这也很好地解释了为什么 MySOL 数据库一行支持最多的列为1023。另一个需要注意的值为1byte_offs_flags,该值定义了偏移列表占用1字节还是2字节。而最后的部分就是实际存储的每个列的数据了。
4.3.3 行溢出数据
-
InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为 BLOB、LOB 这类的大对象列类型的存储会把数据存放在数据页面之外。但是,这个理解有点偏差,BLOB 可以不将数据放在溢出页面,而且即便是VARCHAR列数据类型,依然有可能被存放为行溢出数据。
-
首先对VARCHAR数据类型进行研究。MySQL数据库的VARCHAR类型可以存放65535字节。但是,这是真的吗?真的可以存放65535字节吗?如果创建VARCHAR长度为65535的表,用户会得到下面的错误信息∶
-
从错误消息可以看到InnoDB存储引擎并不支持65535长度的VARCHAR。这是因为还有别的开销,通过实际测试发现能存放 VARCHAR类型的最大长度为65 532。 -
还需要注意上述创建的 VARCHAR长度为65 532的表,其字符类型是 latin1 的,如果换成 GBK 又或 UTF-8的,会产生怎样的结果呢?
-
VARCHAR(N)中的N指的是字符的长度。而文档中说明VARCHAR类型最大支持65535,单位是字节。
-
此外需要注意的是,MySQL官方手册中定义的65535 长度是指所有VARCHAR列的长度总和,如果列的长度总和超出这个长度,依然无法创建。
-
3个列长度总和是66000,因此 InnoDB存储引擎再次报了同样的错误。即使能存放65532个字节,但是有没有想过,InnoDB存储引擎的页为16KB,即16384字节,怎么能存放65532字节呢?因此,在一般情况下,InnoDB存储引擎的数据都是存放在页类型为B-tree node 中。但是当发生行溢出时,数据存放在页类型为 Uncompress BLOB页中。行溢出数据存放采用4-4的方式。
- 那多长的VARCHAR是保存在单个数据页中的,从多长开始又会保存在BLQB顶呢? InnoDB存储引擎表是索引组织的,即 B+Tree 的结构,这样每个页中至少应该有两条行记录(否则失去了B+Tree 的意义,变成链表了)。因此,如果页中只能存放下一条记录,那么 InnoDB存储引擎会自动将行数据存放到溢出页中。
- 如果在一个页至少放入两行数据,那Varchar类型的行数据不会放到BLOB页中去,这个阈值长度为8098。
- 另一个问题是,对于TEXT或BLOB的数据类型,用户总是以为它们是存放在Uncompressed BLOB Page 中的,其实这也是不准确的。是放在数据页中还是 BLOB页中,和前面讨论的VARCHAR一样,至少保证一个页能存放两条记录,如∶
发现数据并没有保存在BLOB页中。
当然既然用户使用了BLOB列类型,一般不可能存放长度这么小的数据。因此在大多数的情况下BLOB 的行数据还是会发生行溢出,实际数据保存在 BLOB 页中,数据页只保存数据的前768字节。
4.3.4 Compressed 和 Dynamic 行记录格式
- InnoDB 1.0.x 版本开始引入了新的文件格式(file format,用户可以理解为新的页格式),以前支持的 Compact 和Redundant 格式称为 Antelope 文件格式,新的文件格式称为 Barracuda 文件格式。
- Barracuda 文件格式下拥有两种新的行记录格式∶Compressed和 Dynamic。
- 新的两种记录格式对于存放在 BLOB中的数据采用了完全的行溢出的方式,如图 4-5 所示,在数据页中只存放 20个字节的指针,实际的数据都存放在 Off Page 中,而之前的 Compact 和 Redundant 两种格式会存放 768个前缀字节。
- Compressed 行记录格式的另一个功能就是,存储在其中的行数据会以 zlib 的算法进行压缩,因此对于 BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储。
4.3.5 CHAR的行结构存储
- 通常理解VARCHAR 是存储变长长度的字符类型,CHAR 是存储固定长度的字符类型。而在前面的小节中,用户已经了解行结构的内部的存储,并可以发现每行的变长字段长度的列表都没有存储 CHAR类型的长度。
- 从MySQL 4.1版本开始,CHAR(N)中的N指的是字符的长度,而不是之前版本的字节长度。也就说在不同的字符集下,CHAR类型列内部存储的可能不是定长的数据。例如下面的这个示例∶
- 往 char(2)中,插入了我们,ab两个数据
- 前两个记录’ab和’我们’字符串长度都为2,但是在内部存储时’ab’占2字节,'我们’占4个字节。
- 因此对于多字节的字符编码,CHAR类型不再代表固定长度的字符串了。例如,对于UTF-8下 CHAR(10)类型的列,其最小可以存储10字节的字符,而最大可以存储30字节的字符。因此,对于多字节字符编码的 CHAR数据类型的存储,InnoDB存储引擎在内部将其视为变长字符类型。这也就意味着在变长长度列表中会记录 CHAR 数据类型的长度。
- 上述例子清楚地显示了InnoDB存储引擎内部对CHAR类型在多字节字符集类型的存储。CHAR类型被明确视为了变长字符类型,对于未能占满长度的字符还是填充 0x20。因此可以认为在多字节字符集的情况下,CHAR和 VARCHAR的实际行存储基本是没有区别的。
4.4 InnoDB数据页结构
页是 InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-tree Node 的页存放的即是表中行的实际数据了。
注意 InnoDB公司本身并没有详细介绍其页结构的实现,MySOL 的官方手册中也基本没有提及InnoDB存储引擎页的内部结构。本节通过阅读源代码来了解InnoDB的页结构,此外结合了Peter 对于InnoDB 页结构的分析。Peter 写这部分内容的时间很久远了,在其之后InnoDB引入了Compact 格式,页结构已经有所改动,因此可能出现对页结构分析错误的情况,如有错误,希望可以指出。
InnoDB数据页由以下 7个部分组成,如图4-6所示。
- File Header(文件头)
- Page Header(页头)
- Infimun 和 Supremum Records
- User Records(用户记录,即行记录)
- Free Space(空闲空间)
- Page Directory(页目录)
- File Trailer(文件结尾信息)
- 其中File Header、Page Header、File Trailer 的大小是固定的,分别为38、56、8字节,这些空间用来标记该页的一些信息,如 Checksum,数据页所在 B+树索引的层数等。User Records、Free Space、Page Directory 这些部分为实际的行记录存储空间,因此大小是动态的。
4.4.1 File Header
File Header用来记录页的一些头信息,由表4-3中8个部分组成,共占用38字节。
4.4.2 Page Header
接着 File Header部分的是Page Header,该部分用来记录数据页的状态信息,由14个部分组成,共占用56字节,如表4-5所示。
4.4.3 Infimum 和 Supremum Record
- 在InnoDB存储引擎中,每个数据页中有两个虚拟的行记录,用来限定记录的边界。Infimum 记录是比该页中任何主键值都要小的值,Supremum指比任何可能大的值还要大的值。这两个值在页创建时被建立,并且在任何情况下不会被删除。在 Compact 行格式和 Redundant行格式下,两者占用的字节数各不相同。图 4-7显示了Infimum 和Supremum 记录。
4.4.4 User Record 和 Free Space
- User Record就是之前讨论过的部分,即实际存储行记录的内容。再次强调,InnoDB 存储引擎表总是 B+ 树索引组织的。
- Free Space很明显指的就是空闲空间,同样也是个链表数据结构。在一条记录被删除后,该空间会被加入到空闲链表中。
4.4.5 Page Directory
- Page Directory(页目录)中存放了记录的相对位置(注意,这里存放的是页相对位置,而不是偏移量),有些时候这些记录指针称为 Slots(槽)或目录槽(Directory Slots)。与其他数据库系统不同的是,在 InnoDB中并不是每个记录拥有一个槽,InnoDB 存储引擎的槽是一个稀疏目录(sparse directory),即一个槽中可能包含多个记录。伪记录Infimum 的n_owned值总是为1,记录 Supremum 的n_owned 的取值范围为【1,8】,其他用户记录n_owned 的取值范围为【4,8】。当记录被插入或删除时需要对槽进行分裂或平衡的维护操作。
- 在 Slots 中记录按照索引键值顺序存放,这样可以利用二叉查找迅速找到记录的指针。假设有(‘i’,‘d’,‘c’,‘b’,‘e’,‘g’,‘1’,‘h’,‘f’,'j,‘k’,‘a’),同时假设一个槽中包含4条记录,则 Slots 中的记录可能是(‘a’,‘e’,‘i’)。
- 由于在 InnoDB存储引擎中Page Direcotry 是稀疏目录,二叉查找的结果只是一个粗略的结果,因此 InnoDB存储引擎必须通过 recorder header中的 next_record来继续查找相关记录。同时,Page Directory很好地解释了recorder header中的n_owned值的含义,因为这些记录并不包括在 Page Directory 中。
- 需要牢记的是,B+ 树索引本身并不能找到具体的一条记录,能找到只是该记录所在的页。数据库把页载入到内存,然后通过Page Directory 再进行二叉查找。只不过二叉查找的时间复杂度很低,同时在内存中的查找很快,因此通常忽略这部分查找所用的时间。
4.4.6 File Trailer
-
为了检测页是否已经完整地写入磁盘(如可能发生的写入过程中磁盘损坏、机器关机等),InnoDB存储引擎的页中设置了File Trailer 部分。
-
File Trailer只有一个FIL_PAGE_END_LSN部分,占用8字节。前4字节代表该页的 checksum值,最后4字节和 File Header中的 FIL PAGE LSN相同。将这两个值与File Header中的FIL_PAGE_SPACE_OR_CHKSUM和 FIL_PAGE_LSN值进行比较,看是否一致(checksum 的比较需要通过 InnoDB 的 checksum 函数来进行比较,不是简单的等值比较),以此来保证页的完整性(not corrupted)。
-
在默认配置下,InnoDB存储引擎每次从磁盘读取一个页就会检测该页的完整性,即页是否发生 Corrupt,这就是通过 File Trailer部分进行检测,而该部分的检测会有一定的开销。用户可以通过参数
innodb_checksums
来开启或关闭对这个页完整性的检查。 -
-
MySQL 5.6.6版本开始新增了参数
innodb_checksum_algorithm
,该参数用来控制检测 checksum 函数的算法,默认值为 crc32,可设置的值有∶innodb、crc32、none、strict_innodb、strict_crc32、strict none。 -
-
innodb为兼容之前版本 InnoDB页的 checksum 检测方式,crc32为 MySQL 5.6.6版本引进的新的 checksum算法,该算法较之前的 innodb 有着较高的性能。但是若表中所有页的 checksum 值都以 strict 算法保存,那么低版本的 MySQL数据库将不能读取这些页。none表示不对页启用checksum 检查。
-
strict *正如其名,表示严格地按照设置的 checksum算法进行页的检测。因此若低版本 MySQL 数据库升级到MySQL 5.6.6或之后的版本,启用 strict_crc32将导致不能读取表中的页。启用strict_crc32方式是最快的方式,因为其不再对innodb和 crc32算法进行两次检测。故推荐使用该设置。若数据库从低版本升级而来,则需要进行mysql_upgrade 操作。
4.4.7 InnoDB数据页结构示例分析
通过前面各小节的介绍,相信读者对 InnoDB存储引擎的数据页已经有了一个大致的了解。本小节将通过一个具体的表,结合前面小节所介绍的内容来具体分析一个数据页的内部存储结构。首先建立一张表t,并导入一定量的数据∶