MySQL中的碎片整理

最新推荐文章于 2024-07-19 11:52:29 发布

路～～～

最新推荐文章于 2024-07-19 11:52:29 发布

阅读量1.2k

点赞数

本文链接：https://blog.csdn.net/cxs_123/article/details/114635279

版权

前言

关于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

路～～～

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL中的碎片整理

前言关于MySQL中表碎片化(Table Fragmentation)产生的原因，MySQL的Engine不同，碎片化的原因可能也有所差别。这里没有深入理解、分析这些差别，此文仅以InnoDB引擎为主。若是有不足的地方，那我也没办法。InnoDB的"页合并"和"页分裂"我使用的是MySQL 5.7 版本，数据存储目录使用的是"/data/mysql/data"；为了不让你们发现不足，我从InnoDB存储引擎的存储数据的结构讲起。我们知道，假如在MySQL数据库中建立一个world数据库，然后再在w
复制链接

扫一扫