mysql 如何去整理表数据，碎片整理

MySQL 的碎片是 MySQL 运维过程中比较常见的问题，碎片的存在十分影响数据库的性能，本文将对 MySQL 碎片进行一次讲解。

判断方法：

MySQL 的碎片是否产生，通过查看

show table status from table_nameG

这个命令中 Data_free 字段，如果该字段不为 0，则产生了数据碎片。

产生的原因：

1. 经常进行 delete *** 作

经常进行 delete *** 作，产生空白空间，如果进行新的插入 *** 作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用，久而久之就产生了碎片；

演示：

创建一张表，往里面插入数据，进行一个带有 where 条件或者 limit 的 delete *** 作，删除前后对比一下 Data_free 的变化。

删除前：

删除后：

Data_free 不为 0，说明有碎片；

2. update 更新

update 更新可变长度的字段(例如 varchar 类型)，将长的字符串更新成短的。之前存储的内容长，后来存储是短的，即使后来插入新数据，那么有一些空白区域还是没能有效利用的。

演示：

创建一张表，往里面插入一条数据，进行一个 update *** 作，前后对比一下 Data_free 的变化。

CREATE TABLE `t1` ( `k` varchar(3000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8

更新语句：update t1 set k='aaa'

更新前长度：223 Data_free：0

更新后长度：3 Data_free：204

Data_free 不为 0，说明有碎片；

产生影响：

1. 由于碎片空间是不连续的，导致这些空间不能充分被利用；

2. 由于碎片的存在，导致数据库的磁盘 I/O *** 作变成离散随机读写，加重了磁盘 I/O 的负担。

清理办法：

MyISAM：optimize table 表名；（OPTIMIZE 可以整理数据文件,并重排索引）

Innodb：

1. ALTER TABLE tablename ENGINE=InnoDB；(重建表存储引擎，重新组织数据)

2. 进行一次数据的导入导出

碎片清理的性能对比：

引用我之前一个生产库的数据，对比一下清理前后的差异。

SQL执行速度：

select count(*) from test.twitter_11

修改前：1 row in set (7.37 sec)

修改后：1 row in set (1.28 sec)

结论：

通过对比，可以看到碎片清理前后，节省了很多空间，SQL执行效率更快。所以，在日常运维工作中，应对碎片进行定期清理，保证数据库有稳定的性能。

   square-pavilion（四方阁）

 链接： https://github.com/ChenJincheng-Gencent/square-pavilion

  Square pavilion is a cube project for e-commerce.（四方阁是一个为电商而生的中台项目）。 在玄幻小说中，总有一些比较牛逼且左右逢源于各个势力之间的中立组织，这些组织通常带有“阁”字。因此本项目取名“四方阁”，取包容并蓄，吸取百家之长之意。欢迎有兴趣的小伙伴们一起努力，把这个项目完善、推广。

Github square-pavilion

---