vlambda博客
学习文章列表

今天为什么会提到MySQL的数据碎片？事情是这样的周五跟同事说了一下系统中有一张表有一些容量问题，一开始想是不是需要分库分表了。但是考虑到分库分表的迁移成本问题，以及必要性问题，咨询了DBA的相关同事，DBA询问了一些系统参数（QPS，TPS，表数据占用空间（表数据4G、索引1G、碎片22G）等），给的结果：单实例足够支撑了。

跟我想象的答案不是很一样啊？是哪里出了问题。

紧接着我反驳道：我们的表数据目标会打到 ***W，现在查询比较慢，并且磁盘碎片还比较多。

DBA：你给我的这些参数单库就够用了呀，查询慢就优化SQL，碎片多就提工单进行碎片清理。

我：优化SQL我能理解，我只看到数据碎片，整理数据碎片我也没干过呀，有没有推荐的解决方案呀。

DBA：你直接在平台提工单就好了。给了我一个SQL，alert table table_name engine = innodb;

我：看似朴实无华的一句SQL，让我产生了很多疑惑，什么是数据碎片，有啥用，咋优化呢？

每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。

你说这样有数据碎片，我就相信了，无图无真相啊。

那咱们来模拟一下。

1. 创建数据库

   mysql> create database db_test;
   Query OK, 1 row affected (0.01 sec)
   

2. 创建测试表

   mysql> use db_test;
   Database changed
   mysql> create table tb_test (c1 varchar(64));
   Query OK, 0 rows affected (0.04 sec)
   

添加一些测试数据

mysql> insert into tb_test values ('this is row 1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_test values ('this is row 2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_test values ('this is row 3');
Query OK, 1 row affected (0.00 sec)

查看一下碎片情况

mysql> show table status from db_test\G;

当前数据碎片为0

删除数据，再次检测

mysql> delete from tb_test where c1 = 'this is row 2';;
Query OK, 1 row affected (0.01 sec)

查看碎片情况

mysql> show table status from db_test\G;

这些大量的数据碎片，会影响我们的读写速度，就如我之前叨叨里面写的一样，我们这个频繁读写的表中，数据占4G、索引占1G、碎片占用22G。由于数据库在不断的读写过程中，不断有数据被写入，也不断有数据被删除，当有数据被删除时，后面的数据记录不可能全部前移，这就形成了闲置的空间，这就是“多余”数据。通俗地说，这些“多余”，就是数据库的“碎片”，因为它们造成了数据的不连贯，当然也就影响了数据库的读写速度。

因此，优化这些有“多余”数据的数据表，也就是整理数据库碎片。

MySQL提供了清理碎片的方法：

OPTIMIZE TABLE tab_name;

我们来实操一下看看效果：

mysql> OPTIMIZE TABLE tb_test;
+-----------------+----------+----------+----------+
| Table           | Op       | Msg_type | Msg_text |
+-----------------+----------+----------+----------+
| db_test.tb_test | optimize | status   | OK       |
+-----------------+----------+----------+----------+
1 row in set (0.03 sec)

注意：对于该方法，需要注意的是执行的时候会产生表锁，因此对于体积巨大的列表应尤其注意使用。OPTIMIZE TABLE 只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含varchar、text、blob、float等可变长度的文本数据类型的表进行整理即可。一般根据实际情况，两周或一个月进行碎片清理即可。

此外针对innodb引擎，不仅可以使用OPTIMIZE TABLE tab_name;来清理碎片，还可以使用alter table tb_test engine = innodb;。

老黑的博客

蚂蚁金服架构师，专注Java技术栈。

3篇原创内容

Official Account

点个在看你最好看