原标题:Innodb页合并和页分裂
作者:Marco Tusa 、 Sri Sakthivel
译者:孟维克,知数堂优秀校友
原文链接:
https://www.percona.com/blog/2017/04/10/innodb-page-merging-and-page-splitting/
https://www.percona.com/blog/2020/06/24/mysql-table-fragmentation-beware-of-bulk-insert-with-failure-or-rollback/
InnoDB页合并和页分裂
如果您遇到全球少数的MySQL顾问之一,请他审核您的SQL语句和表结构设计,我相信他会告诉您一些有关好的主键设计的重要性。特别是对InnoDB,我相信他已经想您解释了索引合并和页分裂。这两个概念与性能密切相关,在设计任意索引(不仅仅是主键)时都应该考虑这方面因素。
对您来说,这听起来可能有点胡言乱语,也许您是对的。这不是一件容易的事情,尤其是在讨论内部原理时。这不是您经常要处理的事情,而且通常您根本不想处理它。
但有时这是必要的。如果是这样,这篇文章就是为您准备的。
在这篇文章中,我想解释一些InnoDB幕后操作中最不清楚的部分:索引页创建、页合并和页分裂。
在InnoDB中,所有的数据就是一个索引。您可能也听过,对吧?但这到底是什么意思呢?
表&文件
假设您已经安装了MySQL,5.7最新版本,您在windmills schema中有一个名为wmills的表。在数据目录中(通常是 /var/lib/mysql/ )您会看到它包含有:
data/
windmills/
wmills.ibd
wmills.frm
这是因为参数 innodb_file_per_table 从MySQL5.6开始已经设置为1。这样设置,schema中每个表都是一个文件(如果是分区表,则有多个文件)。
这里重要的是名为 wmills.ibd 的文件。这个文件被分为N个段。每个段都与一个索引相关联。
尽管文件不会因删除数据而收缩,段本身会增长或收缩,下一级为区。一个区仅存在一个段中,并且固定尺寸为1MB(在默认页大小的情况下)。页是区的下一级,默认大小为16KB。
因此,一个区最多可包含64页。一个页可以包含2到N行。一个页可以容纳的行数与行大小有关,这是表结构设计时定义的。InnoDB中有一个规则,至少要在一个页中容纳两行。因此,行大小限制为8000字节。
如图所示:
InnoDB使用B+树。
根节点,分支节点和叶子节点
每个页(叶子节点)包含由主键组织的2~N行。树有专门的页管理不同的子树。这些被称为内部节点(INodes)。
enter image deion here
这个图片仅是示例,并不能说明下面的实际输出。
细节如下:
ROOT NODE #3: 4 records, 68 bytes
NODE POINTER RECORD ≥ ( id= 2) → #197
INTERNAL NODE #197: 464 records, 7888 bytes
NODE POINTER RECORD ≥ ( id= 2) → #5
LEAF NODE #5: 57 records, 7524 bytes
RECORD: ( id= 2) → (uuid= "884e471c-0e82-11e7-8bf6-08002734ed50", millid= 139, kwatts_s= 1956, date= "2017-05-01", location= "For beauty's pattern to succeeding men.Yet do thy", active= 1, time= "2017-03-21 22:05:45", strrecordtype= "Wit")
表结构如下:
CREATETABLE`wmills`(
`id`bigint( 11) NOTNULLAUTO_INCREMENT,
`uuid`char( 36) COLLATEutf8_bin NOTNULL,
`millid`smallint( 6) NOTNULL,
`kwatts_s`int( 11) NOTNULL,
`date`dateNOTNULL,
`location`varchar( 50) COLLATEutf8_bin DEFAULTNULL,
`active`tinyint( 2) NOTNULLDEFAULT'1',
`time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
`strrecordtype`char( 3) COLLATEutf8_bin NOTNULL,
PRIMARY KEY( `id`),
KEY`IDX_millid`( `millid`)
) ENGINE= InnoDB;
所有类型的B+树都有一个称为根节点的入口点。我们已经在第3页找到了它。根页包含了索引ID、INodes数量等信息。INode页包含关于页本身、值的范围等信息。最后,我们有叶节点,这是我们可以找到数据的地方。在本例中,我们可以看到叶节点5有57条记录,总共7524字节。这行下面是一条记录,您可以看到行数据。
这里的概念是,当您在表和行中组织数据时,InnoDB在分支节点、页和记录中组织数据。记住InnoDB不能以单行基础上工作是非常重要的。InnoDB总是在页上操作。一旦页被加载,它就会扫描页以寻找所请求的行/记录。
现在都清楚了么?让我们继续。
页内部
页可以是空,也可以是被填充满(100%)