MySQL 中 InnoDB 存储引擎页大小

Question

查看 mysql 的 InnoDB 引擎一页有多大

show global status like 'innodb_page_size';

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

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

估算一页能存放多少数据

我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么可以算出一棵高度为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。