mysql页面大小_MySQL innodb_page_size

原标题：MySQL innodb_page_size

墨墨导读：Page是MySQL Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，了解page的一些特性，可以更容易理解MySQL。

innodb_page_size作为innodb和OS交互单位。文件系统对文件的buffer IO，也是page为单位进行处理的。Linux的默认page的大小4096字节，当要将数据写入到文件的时候，会先在内存里，然后将对应的page cache，整个的从内存刷到磁盘上。但是如果要写入的文件区域，因为还没有被缓存或者被置换出去了等原因，在内存里不存在对应的page cache，则需要先将对应page的内容从磁盘上读到内存里，修改要写入的数据，然后在将整个page写回到磁盘；在这种情况下，会有一次额外的读IO开销，IO的性能会有一定的损失。假如mysql的16K的页表数据支持起，那就是一次内存IO光是虚拟地址到物理地址的转换就要去内存查4次页表，再算上真正的内存访问，需要5次内存IO才能获取一个内存数据。

操作系统PAGE：

在操作系统层面，每个进程都有自己独立的地址空间，看到的都是操作系统虚拟出来的地址空间，虚拟地址最终还是要落在实际内存的物理地址上进行操作的。操作系统就会通过页表的机制来实现进程的虚拟地址到物理地址。其中每一页的大小都是固定的。

获取当前操作系统的page size：

####X86：

[root@ens8 ~] # getconf PAGESIZE

4096

####ARM：

root@ens8ARM :~# getconf PAGESIZE

65536

页表级数：

页表级数越少，虚拟地址到物理地址的映射会很快，但是需要管理的页表项会很多，能支持的地址空间也有限。

相反页表级数越多，需要的存储的页表数据就会越少，而且能支持到比较大的地址空间，但是虚拟地址到物理地址的映射就会越慢。

备注：ARM系统来说需要编译指定才可以。因为Linux 默认4kb页是通过长期的跟MySQL磨合，才有现在的稳定的表现。

MySQL数据库Page

InnoDB逻辑存储单元主要分为表空间、段、区和页。层级关系为tablespace -> segment -> extent(64个page，1M) -> page。(非常经典的图)

在看看innodb_page_size的官方描述：

第一个系统表空间数据文件(ibdata1)的最小文件大小取决于innodb_page_size值

innodb_page_size只能在初始化MySQL实例之前配置，不能在之后修改。如果没有指定值，则使用默认页面大小初始化实例。

MySQL 5.7增加了对32KB和64KB页面大小的支持。对于32KB和64KB的页面大小，最大行长度约为16000字节。当innodb_page_size设置为32KB或64KB时，不支持ROW_FORMAT= compression。对于innodb_page_size=32k，区段大小为2MB。对于innodb_page_size=64KB，区段大小为4MB。当使用32KB或64KB的页面大小时，innodb_log_buffer_size应该至少设置为16M(默认)。

默认的16KB或更大的页面大小适用于各种工作负载，特别是涉及表扫描的查询和涉及批量更新的DML操作。对于涉及许多小写操作的OLTP工作负载，较小的页面大小可能更有效，在这种工作负载中，当单个页面包含许多行时，可能会出现争用问题。对于通常使用较小块的SSD存储设备，较小的页面也可能是有效的。保持InnoDB页面大小接近存储设备块大小，可以最大限度地减少被重写到磁盘的未更改数据量.

对于row，index，tablesapce的影响

Page对Row的影响：

对于4KB、8KB、16KB和32KB的页大小，最大行大小(不包括存储在页外的任何可变长度的列)略小于页大小的一半。例如，默认innodb_page_size为16KB的最大行大小约为8000字节。然而，对于InnoDB页面大小为64KB的页面，最大行大小大约是16000字节。LONGBLOB和LONGTEXT列必须小于4GB，包括BLOB和文本列在内的总行大小必须小于4GB。

Page对index的影响：

如果在创建MySQL实例时通过指定innodb_page_size选项将InnoDB页面大小减少到8KB或4KB，索引键的最大长度将按比例降低，这是基于16KB页面大小的3072字节限制。也就是说，当页面大小为8KB时，最大索引键长度为1536字节，而当页面大小为4KB时，最大索引键长度为768字节。

不同的Page大小，表空间限制:

Pages对字段的影响 ：

对于4KB、8KB、16KB和32KB的innodb_page_size设置，最大行长度略小于数据库页的一半。例如，对于默认的16KB InnoDB页面大小，最大行长度略小于8KB。对于64KB的页面，最大行长度略小于16KB。

如果一行不超过最大行长度，则所有行都存储在本地页中。如果一行超过最大行长，则选择可变长度列用于外部页外存储，直到该行符合最大行长限制为止。可变长度列的外部离页存储因行格式不同而不同:

COMPACTRow Formats：

当一个可变长度的列被选择用于外部页外存储时，InnoDB将前768个字节本地存储在行中，其余的存储在外部溢出的页面中。每个这样的列都有自己的溢出页列表。768字节的前缀伴有一个20字节的值，该值存储列的真实长度，并指向存储其余值的溢出列表

DYNAMICRow Formats：

当一个可变长度的列被选择用于外部页外存储时，InnoDB在本地的行中存储一个20字节的指针，其余的则在外部存储到溢出的页面中，LONGBLOB和LONGTEXT列必须小于4GB，包括BLOB和TEXT列在内的总行长度必须小于4GB。

Page如何计算记录数

page构成结构：

除数据外ROW额外信息存在哪些：

16k页为基准，能保存多少记录数：

按照上面Page结构图，可以如下计算：

1.page大小(16*1024=16384)- 必要信息(File Header38字节+page header56字节+虚拟最大最小记录26字节+Page Directory4字节+File Trailer8字节)=16252字节

2.约每4条记录占用一个slot，一个slot大小占用2字节

3.row header(5字节+可变长+非空占位符)+ 主键key长度(如果没有显示声明默认会创建6字节row id)+ trxid6字节+ rollptr7字节

4.假如单行长度计算公式为：row header5字节 + 主键索引列4字节 + 指针4字节 = 13字节

单个page最多能容纳最多行数为 单行长度N+N/4*2 = 16252，

N为1203测试：

通过sysben压测，发现MySQL默认页16K 相比8K 对CPU压力较小，但8k页的情况下所有指标都会有所提升。

sysbench压测是基于主键，8k的页来说，行小于4k的数据来说性能提升，假如大于4k的数据，性能肯定会有下降，因为会出现行溢出，会导致读取列需要多一个IO。所以不同的业务场景可以有效的调整innodb page size 进行调试。当然硬件也要支持，传统的SAS硬盘会存在IO效率下降，更上一层的硬盘(SSD，PCIE)能提供更高的IOPS。

总结

那么innodb_page_size的如何设置，按照个人理解，生产环境中，可以选择16kb 和8kb的长度。

可以考虑一下方面：

1.遵守单行略小于页大小的一半。不能发生行溢出现象，随之而来的要求是尽量主键操作，分配跟多的内存

2.硬件设备的支持，很多高端服务器cpu使用率也就在30%以内的服务器，完全可以使用8kb，提高整体性能。

innodb_page_size设置问题，最终还是io性能方面的优化。需要有效的利用MySQL的一些特性(索引组织表，尽量走主键避免回表，尽量减少随机读写等)结合实际情况进行配置。

前几天一起工作的同事聊到，现在出现新的高端内存。又能放到内存，又能保存数据的内存条！

墨天轮原文链接：https://www.modb.pro/db/40387(复制到浏览器中打开或者点击“阅读原文”)返回搜狐，查看更多

责任编辑：