前言
关于MySQL中表碎片化(Table Fragmentation)产生的原因,MySQL的Engine不同,碎片化的原因可能也有所差别。这里没有深入理解、分析这些差别,此文仅以InnoDB引擎为主。若是有不足的地方,那我也没办法。
InnoDB的"页合并"和"页分裂"
我使用的是MySQL 5.7 版本,数据存储目录使用的是"/data/mysql/data";为了不让你们发现不足,我从InnoDB存储引擎的存储数据的结构讲起。
我们知道,假如在MySQL数据库中建立一个world数据库,然后再在world数据库中创建city、country、countrylanguage三张表,最后再往表中插入相应的数据。那么在Linux系统下的"/data/mysql/data"目录下就会自动创建一个名为world的目录来对应MySQL中的world数据库,同时也会在"/data/mysql/data/world"目录下自动创建几个文件用以对应MySQL中world数据库对应的表。如下图:
其中一张表对应两个文件,例如MySQL中的city表对应"/data/mysql/data/world"目录下的"city.frm"、"city.ibd"文件。(至于为什么会这样对应,今天先不分享,因为要是都放在一起说,那就海了去了,后面会单独说)
和今天的内容有关系的,就是这个"xxx.ibd"文件。我们都知道MySQL数据库中"段"、“区”、“页"的概念,其中"页"是MySQL存储数据的基本单位,默认大小为16KB。在表没有分区的情况下,一张表就是一个"段”,所以一个"xxx.ibd"文件可以代表一个段,其中包含了很多"页"。
而我们往表中插入的数据,就是存储在这些"页"中,一个"页"可以包含2到N行数据记录。一个"页"可以容纳的行数与行大小有关,这是表结构涉设计时定义的。InnoDB中有一个规则,至少要在一个"页"中容纳两行。因此,行大小限制为8000字节。
如图所示:
每个"页"中存放的多条记录以树型的结构组织,这个树称为B树索引(每一个叶子节点为一"页")。一张表中的数据和辅助索引都是使用B树结构。维护表中所有数据的这棵B树索引称为聚集索引,通过主键来组织的。
换句话说,当您在表和行中组织数据时,InnoDB在枝节点、“页”、和记录中组织数据。记住InnoDB不能在单行基础上工作,InnoDB总是在"页"上操作。一旦"页"被加载,它就会扫描"页"以寻找所需要的数据。
TIP:关于B树的细节问题,可以参考这篇博客:传送门
- "页"内部
“页"可以是空的,也可以是被填充满的(100%)。行记录由主键组织。例如,如果您的表使用自增值,您将有序列ID=1,2,3,4等。
“页"还有一个重要属性:MERGE_THRESHOLD。这个参数的默认值是"页"的50%,它在InnoDB存储引擎的"页合并"活动中起着非常重要的作用:
在插入数据时,如果插入的记录可以容纳在该"页”,则按顺序填充该"页”。若是本"页"已经满了,下一条记录将插入到下一页:
- “页合并”
当你在MySQL中使用"delete"命令删除表中的一条记录时,此条记录并不会真的从"页"中擦除,而是将此记录标记为已经删除。因此delete删除数据后,表文件在磁盘上所占空间大小不会变,存储空间不会释放,只是把删除的数据行设置为不可见。虽未释放磁盘空间,但是下次插入数据的时候,仍然可以重用这部分空间。
当一个"页"删除足够多的数据,达到合并阈值(MERGE_THRESHOLD参数指定的值,默认是"页"大小的50%),InnoDB开始找相邻的"页"(之前和之后的)查看它们是否有机会合并连个"页",优化空间使用率。如果没有机会合并那么这些空出了的位置就会成为"页"上的空洞。
在这个例子中,第6"页"占用空间不足一半。而且第5"页"删除了很多记录,使用率不满50%。从InnoDB存储引擎的角度看,它们是可以合并的:
TIP:如果在相邻"页"有更新和删除操作,将产生合并。如果合并成功,在 INFORMATION_SCHEMA.INNODB_METRICS 表中的 index_page_merge_successful 指标将会增加。 - “页分裂”
如上所述,一个"页"最多可以填充100%,发生这种情况时,下一"页"将获取新纪录。
但是如果我们遇到以下情况呢?
第10"页"没有足够的空间容纳新的记录(或更新的记录)。遵循下一"页"的逻辑,这个记录应该在第11"页"上。然而:
第11"页"页满了,数据不能乱序插入。那该怎么办呢?
InnoDB将做的是:
1、创建一个新的"页"
2、确定原始"页"(第10"页")可以在哪里拆分,在记录级别进行拆分
3、移动记录
4、重新定义"页"之间的关系
新的第12"页"被创建:
第11"页"保持原样。改变的是"页"之间的关系:
第10"页"之前的"页"为第9"页",之后的为第12"页";
第12"页"之前的"页"为第10"页",之后的为第11"页";
第11"页"之前的"页"为第12"页",之后的"页"为第13"页"
即通过链表将第10"页"指向下一"页"的指针指向第12"页",将第12"页"指向下一"页"的指针指向第11"页"。
从上面可以看出,数据记录之间的逻辑顺序依然在,在B树上的路径也会依然遵循逻辑组织,因此仍然可以看到一致性。但是,记录的物理位置是无序的。
========================================================================================
MySQL中碎片概念
有了上面关于InnoDB存储引擎"页合并"和"页分裂"的基础支撑,我们再来梳理一下碎片的产生。
- 外部碎片
首先。理解外部碎片的这个"外"是相对"页"来说的。外部碎片指的是由于分页而产生的碎片。比如,在现有的聚集索引中插入新的一行,这行真好导致现有的"页"空间无法满足容纳新的记录,从而导致了分页。
这就是所谓的外部碎片。
因为在MySQL中,新的"页"是随着数据的增长而不断产生的,而聚集索引要求行之间连续,所以很多情况下分页后和原来的页在磁盘上并不连续。
由于分页会导致数据存储的不连续,这样就会大大提升IO消耗,造成读写性能下降;
tip:
在MySQL中,比"页"更大的单位是"区"。连续的64"页"的存储空间称为"区"(前提"页"的大小为16kB),"区"是作为磁盘分配的物理单元,
所以当"页"分割如果跨区后,可能由于物理地址的不连续,需要多次切"区"进行查找数据的位置,从而也就需要更多的扫描,这样就增加了IO的消耗
- 内部碎片
内部碎片就像上面介绍"页分裂"时,由于插入空间的不足,附近的"页"也没有存储空间而需要新创建一个"页",从而造成数据逻辑上连续但物理存储上不连续的情况。
这就是内部碎片。
内部碎片会造成记录分布在更多的"页"中,而增加了扫描的"页"数,也会降低查询的性能。
查看表的碎片
现在,碎片的概念明白了,那我们就在MySQL中实际操作一下,如何查看碎片:
1、实验准备
创建一张表:
CREATE TABLE TableForTest
( id INT NOT NULL PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
countrycode CHAR(10) NOT NULL
)ENGINE=INNODB CHARSET=utf8mb4;
插入数据:
DELIMITER $$
CREATE PROCEDURE test()
BEGIN
DECLARE var INT DEFAULT 1;
WHILE(var<10000) DO
INSERT INTO TableForTest VALUES(var,'ccc','xx');
SET var=var+1;
END WHILE;
END
$$
DELIMITER;
CALL test();
删除部分数据:
DELIMITER $$
CREATE PROCEDURE test_2()
BEGIN
DECLARE var INT DEFAULT 3;
WHILE(var<10000) DO
DELETE FROM TableForTest WHERE id=var;
SET var=var+5;
END WHILE;
END
$$
DELIMITER;
CALL test_2();
因为大量的使用"delete"删除数据,必然会造成数据碎片,所以未经碎片整理,我们来看看TableForTest表的状态:
show status from test like 'TableForTest';(test是TableForTest表所在的库)
碎片大小=数据总大小 - 实际表空间文件大小
- 数据总大小 = Data_length = 507904
- 实际表空间文件大小 = rows * Avg_row_length = 8160 * 62= 505920
- 碎片大小 = 507904 - 505920 = 1984B
2、通过查询information_schema.TABLES获取表的碎片化信息。
SELECT CONCAT(table_schema,'.',table_name) AS TABLE_NAME,
ENGINE AS TABLE_ENGINE,
table_type AS TABLE_TYPE,
table_rows AS TABLE_ROWS,
CONCAT(ROUND(data_length /( 1024*1024),2),'M') AS TB_DATA_SIZE,
CONCAT(ROUND(index_length /(1024*1024),2),'M') AS TB_IDX_SIZE,
CONCAT(ROUND((data_length+index_length)/(1024*1024),2),'M') AS TOTAL_SIZE,
CASE WHEN data_length=0 THEN 0
ELSE ROUND(index_length / data_length,2)END AS TB_INDX_RATE,
CONCAT(ROUND(data_free /1024/1024,2),'MB') AS TB_DATA_FREE,
CASE WHEN(data_length+index_length)=0 THEN 0
ELSE ROUND(data_free/(data_length+index_length),2)END AS TB_FRAG_RATE
FROM information_schema.TABLES
ORDER BY data_free DESC;
tip:round()函数:
1、round(X):返回最近似X的整数
2、round(X,D):返回X,其值保留到小数点后D位
碎片整理
一、使用"alter table 表名 engine=innodb"命令进行整理。
alter table TableForTest engine=innodb;
show status from test like 'TableForTest';
这其实是一个NULL操作,表面上看什么也不做,实际上重新整理了碎片。当执行优化操作时,实际执行的是一个空的ALTER命令,但是这个命令会起到优化作用,它会重建整个表,删掉未使用的空白空间。
二、使用"optimize table 表名"语句进行整理
optimize table 会重组表和索引的物理存储,减少对存储空间的使用和提升访问表时的IO效率。对每个表所做的确切更改取决于该表使用的存储引擎。
optimize table 操作支持存储引擎类型:INNODB、MYISAM、ARCHIVE、NDB;它会重组表数据和索引的物理页,对于减少空间占用和在访问表时优化IO有效果。optimize table 操作会暂时锁住表,而且数据量越大,耗费的时间也越长。
optimize table 操作后,使用不同存储引擎的表的变化。
对于MYISAM存储引擎:
- 如果表已有被删除的行或拆分行,修复该表。
- 如果未对索引页面进行排序,对它们进行排序。
- 如果表的统计信息不是最新的(并且无法通过对索引进行排序来完成修复),更新它们。
对于INNODB存储引擎:
对于使用INNODB存储引擎的表,optimize table 操作,会重建表以更新索引统计信息并释放聚集索引中未使用的空间。当您在INNODB表上运行时,它会显示在optimize table操作的输出中:
db01 [world]>optimize table TableForTest;
+-------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------+----------+----------+-------------------------------------------------------------------+
| test.TableForTest | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.TableForTest | optimize | status | OK |
+------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.80 sec)
optimize table 操作对InnoDB的普通表和分区表使用online DDL,从而减少了并发DML操作的停机时间。由optimize table操作触发表的重建,仅在操作的准别阶段和提交阶段期间短暂地进行独占表锁定。在准备阶段,更新元数据并创建中间表;在提交阶段,提交更改后的表的元数据。
optimize table操作在以下条件下使用"表复制"方法重建表:
- 启用old_alter_table系统变量时
- 启用mysqld-skip-new选项时
optimize table 操作对于包含FULLTEXT索引的InnoDB表不支持online DDL。而是使用复制表的方法。
针对InnoDB存储引擎,在考虑是否要对碎片进行整理时,请考虑服务器将要处理的事务的工作负载:
- 预计系统中有一定程度的碎片,且InnoDB存储引擎已经填充了93%的页面,可以进行整理碎片,从而回收空间
- 删除操作可能会留下空白,使用页面填充效果不好的话,可以进行碎片整理
TIP:optimize table操作是只适用于独立表空间。
三、使用填充因子
上面两种方式都涉及重建表和重建索引,重建索引固然能够解决问题,但是重建索引的代价不仅仅使麻烦,还会造成阻塞,影响使用。对于数据比较小的情况下,重建索引的代价并不大,但是当索引本身超过百兆的时候,重建索引的时间将会很耗时间。
填充因子的作用正是如此。对于默认值来说,填充因子为0(0和100表示的是一个概念),则表示页面可以100%使用。所以会遇到进行"update"、"insert"操作时,页空间不足致使分页的情况。通过设置填充因子,可以控制页空间的使用程度。
例如:对于填充因子设置为80来说,那么就代表一"页"空间要预留20%,为以后的"insert"、"update"操作留出空闲的空间,从而减少分页的次数。
查看填充因子参数:
db01 [world]>select @@innodb_fill_factor;
+----------------------+
| @@innodb_fill_factor |
+----------------------+
| 100 |
+----------------------+
1 row in set (0.32 sec)
这个参数很少会用到,若是更新的数据很多,可以考虑使用这个参数
tip:填充因子设置参考
1、当读写比例大于100:1时,不要设置填充因子,100%填充
2、当写的次数大于读的次数时,设置为50%-70%
3、当读写比例位于两者之间时,设置为80%-90%
除了以上的方式,也可以借助第三方"pt-online-schema-change"工具进行在线整理表结构、收集碎片等。
参考:https://www.cnblogs.com/VicLiu/p/12850283.html
https://blog.csdn.net/weixin_39574555/article/details/114338292
https://www.cnblogs.com/alipayhutu/p/3292031.html