Mysql页大小-innodb

一、查看mysql的innodb引擎一页有多大

show global status like 'innodb_page_size';

可以看出innodb默认的一页大小为16384B=16384/1024=16kb。

在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k，而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。

名称	中文名	占用空间大小	简单描述
File Header	文件头部	38字节	页的一些通用信息
Page Header	页面头部	56字节	数据页专有的一些信息
Infimum+Supremum	最小记录和最大记录	26字节	两个虚拟的行记录
User Records	用户记录	不确定	实际存储的行记录内容
Free Space	空闲空间	不确定	页面中尚未使用的空间
Page Directory	页面目录	不确定	页中的某些记录的相对位置
File Trailer	文件尾部	8字节	校验页是否完整

二、估算一页能存放多少数据

我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。假设叶子结点的数据为1K,那么可以算出一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170 * 1170 * 16=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

三、怎么得到InnoDB主键索引B+树的高度？

上面我们通过推断得出B+树的高度通常是1-3，下面我们从另外一个侧面证明这个结论。在InnoDB的表空间文件中，约定page number为3的代表主键索引的根页，而在根页偏移量为64的地方存放了该B+树的page level。如果page level为1，树高为2，page level为2，则树高为3。即B+树的高度=page level+1；下面我们将从实际环境中尝试找到这个page level。

在实际操作之前，你可以通过InnoDB元数据表确认主键索引根页的page number为3，你也可以从《InnoDB存储引擎》这本书中得到确认。

SELECT
b.name, a.name, index_id, type, a.space, a.PAGE_NO
FROM
information_schema.INNODB_SYS_INDEXES a,
information_schema.INNODB_SYS_TABLES b
WHERE
a.table_id = b.table_id AND a.space <> 0;

可以看出数据库dbt3下的customer表、lineitem表主键索引根页的page number均为3，而其他的二级索引page number为4。关于二级索引与主键索引的区别请参考MySQL相关书籍，本文不在此介绍。

下面我们对数据库表空间文件做想相关的解析：

1、查看mysql的配置文件，找到数据目录位置：(示例：vim /etc/my.cnf)--->datadir=/home/mysql/data
2、进入数据目录cd /home/mysql/data
3、切换到对应的数据库目录cd suqian/

因为主键索引B+树的根页在整个表空间文件中的第3个页开始，所以可以算出它在文件中的偏移量：16384*3=49152（16384为页大小）。

另外根据《InnoDB存储引擎》中描述在根页的64偏移量位置前2个字节，保存了page level的值，因此我们想要的page level的值在整个文件中的偏移量为：16384*3+64=49152+64=49216，前2个字节中。

接下来我们用hexdump工具，查看表空间文件指定偏移量上的数据：

linetem表的page level为2，B+树高度为page level+1=3；

region表的page level为0，B+树高度为page level+1=1；

customer表的page level为2，B+树高度为page level+1=3；

这三张表的数据量如下：

总结：

lineitem表的数据行数为600多万，B+树高度为3，customer表数据行数只有15万，B+树高度也为3。可以看出尽管数据量差异较大，这两个表树的高度都是3，换句话说这两个表通过索引查询效率并没有太大差异，因为都只需要做3次IO。那么如果有一张表行数是一千万，那么他的B+树高度依旧是3，查询效率仍然不会相差太大。

region表只有5行数据，当然他的B+树高度为1。

四、Innodb行格式

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式 或者 记录格式。目前有4种不同类型的行格式，Compact、Redundant、Dynamic、Compressed。随着时间的推移，可能会有更多的行格式，但是原理基本都是相同的。

指定行格式的语法

我们可以在创建或修改表的语句中指定行格式

create table 表名 (列信息) row_format=行格式名称
alter table 表名 row_format=行格式名称

示例

create table record_format_demo(
    -> c1 varchar(10),
    -> c2 varchar(10) not null,
    -> c3 char(10),
    -> c4 varchar(10)
    -> ) charset=ascii row_format=compact;

insert into record_format_demo(c1,c2,c3,c4) values('aaaa','bbb','cc','d'),('eeee','fff',NULL,NULL);

MySQL [test]> select * from record_format_demo;
+------+-----+------+------+
| c1   | c2  | c3   | c4   |
+------+-----+------+------+
| aaaa | bbb | cc   | d    |
| eeee | fff | NULL | NULL |
+------+-----+------+------+

4.1、COMPACT行格式

1、变长字段长度列表

我们知道mysql支持一些变长的数据类型，比如varchar(M)、varbinary(M)、各种TEXT类型、各种BLOB类型，我们也可以把拥有这些数据类型的列称为变长字段，变长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。

在Compact行格式中，把所有变长字段的真实数据占用的字节长度存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放。

我们拿record_format_demo表中的第一条记录举例

列名	存储内容	内容长度（十进制）	内容长度（十六进制）
c1 varchar(10)	'aaaa'	4	0x04
c2 varchar(10)	'bbb'	3	0x03
c3 char(10)	'cc'	10	0xa
c4 varchar(10)	'd'	1	0x01

因为c3是定长字段，所以不会在变长字段长度列表中，又因为是按照字段顺序逆序存放所以最后变长字段长度列表的字节串用十六进制表示的效果就是（各字节之间实际上没有空格，用空格隔开只是方便理解） 01 03 04

另外需要注意一点的是，变长字段长度列表中只存储值为非NULL的列内容占用的长度，值为NULL的列的长度是不存储的。也就是对于第二条记录来说，因为c3为定长字段,c4列的值为NULL，所以第二条记录的变长字段长度列表只需要存储c1和c2列的长度即可，最终的效果 03 04

由于第一行记录中c1、c2、c4列中的字符串都比较短，也就是说内容占用的字节比较小，用1个字节就可以表示，但是如果变长列的内容占用的字节比较多，可能就需要用2个字节来表示。具体用1个字节还是2个字节来表示真实数据占用的字节数，Innodb有它的一套规则。

1、假设某个字符集中表示一个字符最多需要使用的字节数为W(utf8：3 gbk:2 ascii:1)
2、对于变长类型varchar(M)来说，这种类型表示能存储最多M个字符，最多占用字节为M*W
3、假设它实际存储的字符串占用的字节数是L

1、如果M*W <= 255，那么使用1个字节来表示真正字符串占用的字节数
也就是说Innodb在读记录的变长字段时先查看表结构，如果某个变长字段允许存储的最大字节数不大于255时，可以认为只使用1个字节来表示真正字符串占用的字节数。

2、如果M*W > 255，则分为两种情况
如果 L <= 127，则用1个字节来表示真正字符串的占用的字节数
如果 L > 127，则用2个字节来表示真正字符串的占用的字节数

InnoDB在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最大字节数大于255时，
该怎么区分它正在读的某个字节是一个单独的字段长度还是半个字段长度呢？
答案是：使用该字节的第一个二进制位作为标志位：
如果该字节的第一个位为0，那该字节就是一个单独的字段长度（使用一个字节表示的大于127的二进制的第一位都为0）
如果该字节的第一个位为1，那该字节就是半个字段长度。

对于一些占用字节数非常多的字段，Innodb会把一部分数据存放到所谓的溢出页中，在变长字段长度列表处只存储留在本页面中的长度
所以使用两个字节也可以存放查下来。

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

2、NULL值列表

我们知道表中的某些列可能存储NULL值，如果把这些NULL值都放到 记录的真实数据 中存储会占用地方，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中，他的处理过程是这样的

1、首先统计表中允许存储NULL的列有哪些
2、如果表中没有允许存储NULL的列，则NULL值列表也不存在，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列
二进制位表示的意义如下：1代表该列的值为NULL。0代表该列的值不为NULL。
3、mysql规定NULL值列表，必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。

3、记录头信息

除了 变长字段长度列表、NULL值列表 之外，还有一个用于描述记录的 记录头信息，它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思。

名称	大小（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	表示下一条记录的相对位置

4、记录的真实数据

对于record_format_demo表来说，记录的真实数据除了c1、c2、c3、c4这几个我们自定义的列的数据以外，mysql会为每个记录默认添加一些列（也称为 隐藏列）

列名	是否必须	占用空间	描述	真实名称
row_id	否	6字节	行ID，唯一标识一条记录	DB_ROW_ID
transaction_id	是	6字节	事务ID	DB_TRX_ID
roll_pointer	是	7字节	回滚指针	DB_ROLL_PTR

4.2、Redundant行格式

Redundant行格式是Mysql5.0之前用的一种格式，也就是说它已经非常老了。

1、字段长度偏移列表

没有了“变长”两个字，意味着Redundant行格式会把该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列表。

多了个“偏移”两个字，这意味着计算列值长度的方式不想Compact行格式那么直观，它是采用两个相邻数值的差值来计算各个列值的长度。

比如第一条记录的 字段长度偏移列表 就是：25 24 1A 17 13 0C 06

因为它是逆序排放的，所以按照列的顺序排列就是：06 0C 13 17 1A 24 25

按照两个相邻数值的差值来计算各个列值的长度的意思就是

第一列（row_id）的长度就是0x06个字节，也就是个字节
第二列（transaction_id）的长度就是（0x0C-0x06）个字节，也就是6个字节
第三列（roll_pointer）的长度就是（0x13-0x0C）个字节，也就是7个字节
第四列（c1）的长度就是（0x17-0x13）个字节，也就是4个字节
第五列（c2）的长度就是（0x1A-0x17）个字节，也就是3个字节
第六列（c3）的长度就是（0x24-0x1A）个字节，也就是10个字节
第七列（c4）的长度就是（0x25-0x24）个字节，也就是1个字节

2、记录头信息

Redundant行格式的记录头信息占用6字节，48个二进制位

名称	大小（bit）	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树的每层非叶子结点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录
heap_no	13	表示当前记录在记录堆的位置信息
n_field	10	表示记录中列的数量
1byte_offs_flag	1	标记字段长度偏移列表中每个列对应的偏移量是使用1字节还是2字节表示的
next_record	16	表示下一条记录的相对位置

1byte_offs_flag 的值是怎么选的？

当记录的真实数据占用的字节数不大于127时，每个列对应的偏移量占用1字节。
当记录的真实数据占用的字节数大于127，但不大于32767，每个列对应的偏移量占用2个字节。
当记录的真实数据占用的字节数大于32767，此时记录已经存放到了溢出页中，在本页中只保留前768个字节和20个字节的溢出页面地址（当然这20个字节还记录了一些别的信息）
因为 字段长度偏移列表 处只需要记录每个列在本页面中的偏移就好了，所以每个列使用2个字节来存储偏移量就够了。

1byte_offs_flag
当它的值为1时。表明使用1个字节存储
当它的值为0时，表明使用2个字节存储

大家有没有疑惑，一个字节能表示的范围时0-255，为啥在记录的真实数据占用的存储空间大于127时就采用2个字节表示各个列的偏移量呢？ 其实这偏移量的最高比特位时用作NULL值判断的。Redundant行格式中的NULL值处理如下：

因为Redundant行格式并没有NULL值列表，所以设计的时候在 字段长度偏移列表 中的各个列对应的偏移量处做了一些特殊处理 
将列对应的偏移量值的第一个比特位作为是否为NULL的依据，该比特位也可以被称之为NULL比特位。
也就是说在解析一条记录的某个列时，首先看一下该列对应的偏移量的NULL比特位是不是为1，如果为1，那么该列的值就是NULL。

但是还有一点要注意，对于NULL值的列来说，该列的类型是否为定长类型决定了NULL值的实际存储方式
如果存储的NULL值的字段时定长类型的，则NULL值也将占用记录的真实数据部分，并把该字段对应的数据使用0x00填充
如果该存储NULL值的字段时变长数据类型的，则不在 记录的真实数据 处占用任何存储空间。

4.3、行溢出数据

varchar(M)最多能存储的数据

我们知道对于varchar(M)类型的列最多可以占用65535个字节，其中的M代表该类型最多存储的字符数量。如果我们使用ascii字符集的话，一个字符就代表一个字节，我们看看varchar(65535)是否可用。结果是报错的，从报错信息里可以看出，mysql对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。所以mysql服务器建议我们把存储类型改为TEXT或者BLOB类型。这个65535个字节除了本身的数据之外，还包括一些其他数据(storage overhead)，比如说我们为了存储一个varchar(M)类型的列，其实需要占用3部分存储空间。（真实数据/真实数据占用字节的长度/NULL值标识，如果该列有not null属性则可以没有这部分存储空间）。

如果该varchar类型的列没有not null属性，那最多只能存储65532个字节的数据，因为真实数据的长度可能占用2个字节，null值标识需要占用1个字节。

如果varchar类型的列有not null属性，那最多只能存储65533个字节的数据，因为真实数据的长度可能占用2个字节，不需要null值标识。

如果varchar(M)类型使用的不是ascii字符集，那M的最大取值取决于该字符集表示一个字符最多需要的字节数。在累的值允许为NULL的情况下，gbk字符集表示一个字符最多需要2个字节，那该字符集下，M的最大取值就是32766(65532/2)。utf8字符集表示一个字符最多需要3个字节，那在该字符集下，M的最大取值就是21844（65532/3）。

记录中的数据太多产生的溢出

我们以ascii字符集下的varchar_size_demo表为例，插入一条记录：
MySQL [test]> create table varchar_size_demo(
    -> c varchar(65532)
    -> ) charset=ascii row_format=Compact;

MySQL [test]> insert into varchar_size_demo(c) values(repeat('a',65532));
Query OK, 1 row affected (0.13 sec)

前面说过，mysql中磁盘和内存交互的基本单位是页，也就是说mysql是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。而一个页的大小一般是16KB，也就是16384字节，而一个varchar(M)类型的列最多可以存储65532个字节，这样就可能造成一个页存放不下一条记录的尴尬情况。

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

注意：不只是varchar(M)类型的列，其他的TEXT、BLOB类型的列在存储数据非常多的时候也会发生 行溢出

行溢出的临界点

mysql 中规定一个页中至少存放两条记录，至于为什么这么规定我们之后再说，现在看一下这个规定造成的影响。以上面的varchar_size_demo表为例，他只有一个列c，我们往这个表中插入两条记录，每条记录最少插入多少字节的数据才会行溢出呢？这要分析一下页中的空间都是如何利用的。

1、每个页除了存放我们的记录以外，也需要存储一些额外信息，乱七八糟的额外信息加起来需要136个字节空间，其他的空间都可以被用来存储记录。
2、每个记录需要的额外信息是27字节。
2-1、2个字节用于存储真实数据的长度
2-2、1个字节用于存储列是否是NULL值
2-3、5个字节的头信息
2-4、6字节的row_id列
2-5、6字节的transaction_id列
2-6、7字节的roll_pointer列

假设一个列中存储的数据字节数为n，那么发生行溢出现象时需要满足这个式子：136 + 2(27+n) > 16384。n > 8098。也就是说如果一个列中存储的数据不大于8098个字节，那就不会发生行溢出，否则就会发生行溢出。不过这个8098个字节的结论只是针对只有一个列的varchar_size_demo表来说的，如果表中有多个列，那么上面的式子和结论都要改一改，所以重点就是：不用关注这个临界点是什么，只要知道如果我们想一个行中存储了很大的数据时，可能发生行溢出现象。

4.4、Dynamic和Compressed行格式

这两个行格式和Compact行格式挺像的，只不过在处理 行溢出 数据时有点分歧，它们不会在 记录的真实数据处 存储字段的真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

Compressed行格式和Dynamic不同点就是：Compressed行格式会采用压缩算法对页面进行压缩，可以节省空间。