Mysql中删除数据造成的碎片如何修复

我们都知道，在mysql (这里只探讨innodb) 中delete数据，并非真实删除，而是在这行数据上打了一个del的标记，所以这行占用的空间也并不会释放，但是空间可以被复用，所以期望用delete数据来释放空间的同学可以醒醒了。这样就造成了空间上的碎片，那么如果干掉这些碎片呢。

这里先说结论，alter table语句可以触发表重建，消除碎片空间。

mysql中的数据存储结构大概是下面这个样子的

而delete掉的标记会记录在头信息中。

做个实验，看看空间是否真的没有释放；

创建一张表user，并插入很多数据

查看表的文件大小

再随便插入几条

ok这里看到文件大小增加了16k，这是因为mysql的一页就是16k，所以文件大小是16k、16k的增长的。

这时候我们删除大量的数据再次查看文件大小，仍然是272k，索命，数据虽然删除，但是空间没有释放。

这里我们对主键执行一个alter table语句

再次查看文件大小

ok 文件大小明显的减少，这里说明主键的alter语句会重建表，并且释放碎片空间；

这时候我们再删除大量的数据再次查看文件大小，这里我们对普通列执行一个alter table语句

再次查看文件大小

ok 文件大小明显的减少，这里说明普通列的alter语句会重建表，并且释放碎片空间；

https://blog.51cto.com/dadaman/1957229

可以看到，当前表的碎片率超高了，50.6%。

有三种办法整理碎片

这三种 *** 作都是先创建一个临时表复制完成后再删除旧表，所以在执行 *** 作的过程中磁盘会先增大。

会锁表

https://dev.mysql.com/doc/refman/5.6/en/

https://dev.mysql.com/doc/refman/5.6/en/optimize-table.html

会锁表

pt-online-schema-change - ALTER tables 无需锁表。

整理结果很明显，整理后碎片率0.3%。

这里有几个参数需要介绍一下：

--dry-run

这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。

--execute

表明你已经阅读了文档，并且确认要 alter the table。你必须配置这个参数来 alter the table。如果你不配置，那么工具将只进行一些安全检查然后就退出了。这帮助确保你已经阅读了文档，并且了解如何使用该工具。如果你没有阅读这些文档，那么不会设置该参数。

--critical-load

每次chunk *** 作前后，会根据show global status统计指定的状态量的变化，默认是统计Thread_running。目的是为了安全，防止原始表上的触发器引起负载过高。这也是为了防止在线DDL对线上的影响。超过设置的阀值，就会终止 *** 作，在线DDL就会中断。提示的异常如上报错信息。

--max-lag

type: timedefault: 1s

lag 滞后偏移

暂停数据拷贝，直到所有replicas的lag值低于该值。在每个 data-copy query (each chunk)后，工具会通过Seconds_Behind_Master查询所有replica的 replication lag 。如果任何replica lag大于该值，那么工具会sleep --check-interval 秒，然后再次检查所有replica。如果你指定 --check-slave-lag ，那么工具会检查那台server，而不是所有server。如果你想控制哪个提供工具的监控，配置DSN值 --recursion-method 。

工具会等待直到replicas停止lagging。如果任一replica停止，工具会一直处于等待状态直到该replica启动。 在所有replicas运行并且lagging不大的情况下，数据拷贝继续。

工具在等待的时候，会打印进程报告。如果replica停止了，会立即打印进程报告，然后在每个进程报告期间重复。

--check-interval

type: timedefault: 1

Sleep time between checks for --max-lag .

--max-load

选项定义一个阀值，在每次chunk *** 作后，查看show global status状态值是否高于指定的阀值。该参数接受一个mysql status状态变量以及一个阀值，如果没有给定阀值，则定义一个阀值为为高于当前值的20%。注意这个参数不会像--critical-load终止 *** 作，而只是暂停 *** 作。当status值低于阀值时，则继续往下 *** 作。是暂停还是终止 *** 作这是--max-load和--critical-load的差别。

--charset

简写: -Atype: string

设置默认字符集。如果值为 utf8，设置 Perl’s binmode on STDOUT to utf8，传送 mysql_enable_utf8 参数到 DBD::mysql，然后在连接到MySQL后运行 SET NAMES UTF8 。其他的值也是在STDOUT设置 binmode，然后在连到MySQL后运行 SET NAMES 。

--check-replication-filters

检查复制中是否设置了过滤条件，如果设置了，程序将退出

--nocheck-replication-filters

不检查复制中是否设置了过滤条件

--set-vars

设置mysql的变量值

--check-slave-lag

检查主从延迟

--no-version-check

不检查版本，在阿里云服务器中一般加入此参数，否则会报错

以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执行效率更快。所以，在日常运维工作中，应对碎片进行定期清理，保证数据库有稳定的性能。

---