Mysql数据碎片的产生与优化

Mysql数据碎片的产生与优化

Mysql常用的数据存储引擎一般就两个，一个是InnoDB，一个是MyISAM。而无论那种存储引擎都可能阐述数据碎片。

碎片的产生

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

 

简单来说就是，数据删除了之后，使得数据不连续了，中间产生了空白，当再查询新数据的时候，Mysql会利用这些空白，但是总是不会完全占满的。甚至有时候一行数据被分在了多个地方。

 

这种额外的破碎的存储空间在读取效率方面比正常情况要低得多。

 

使用show table status like '表名'，可以查看到数据碎片信息，返回数据中，Data_free大于0时即说明存在数据碎片。

我们可以使用命令：

select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;

查询出所有有数据碎片的表，以及碎片的数量（字节）。

对于MyISAM类型的存储引擎，可以直接使用optimize table 表名，来优化表。因为这类型的存储引擎索引和数据是分开的，所以使用这个命令可以整理数据文件并重排索引。

对于InnoDB类型的存储引擎，我们则可以使用alter table 表名 engine = innodb;命令来重建表。

这些命令的执行会临时锁住表，当数据量越大的时候耗时也越多，所以不宜经常执行此操作。比较好的方式是做个定时Script，比如每周执行一次（这个可以根据具体业务的碎片产生速度来定）。

这样当访问量比较大的时候，整体命中及效率都会得到很大的提升。

 

show STATUS like 'innodb_row_lock%'  查看锁的一些情况