Mysql 磁盘清理相关要点

1. 为啥磁盘还是满的 ?

应该是 MySQL 并没有真正清理掉这部分数据，而是假删除。这种假删除的行为在 Linux 中并不稀罕，属于常规操作，算是一种策思想，所以断定 MySQL 也这么干了。

查看碎片信息命令：

SELECT * from 
(
    SELECT CONCAT(table_schema,'.',table_name) AS 'table_name', 
    table_rows AS 'Number of Rows', 
    CONCAT(ROUND(data_length/(1024*1024),6),' M') AS 'data_size', 
    CONCAT(ROUND(index_length/(1024*1024),6),' M') AS 'index_size' , 
    CONCAT(ROUND(data_free/(1024*1024),6),' M') AS'data_free',
    ENGINE as 'engine'
    FROM information_schema.TABLES 
    WHERE table_schema = #{库名}
) t ORDER BY data_free DESC;

• data_size ：数据的大小
• index_size ：索引的大小
• data_free ：数据在使用中的留存空间
• engine ：表引擎名称
  其中 data_free 代表磁盘碎片的大小， 也就是需要消灭清理的地方。

2. 磁盘清理神器

不同的 MySQL 存储引擎清理方式有所不同。

SHOW ENGINES;  // 查看引擎命令

MySQL 中有多种存储引擎，常用的有 MyISAM 和 InnoDB，先看看这两个有什么特点：

3.1 MyISAM 引擎

MyISAM 基于 ISAM 存储引擎，并对其进行扩展。

• 支持 B-tree/FullText/R-tree 索引类型；
• 锁级别为表锁，表锁优点是开销小，加锁快；缺点是锁粒度大，发生锁冲动概率较高，容纳并发能力低，这个引擎适合查询为主的业务；
• 此引擎不支持事务，也不支持外键；
• BLOB 和 TEXT 列可以被索引；
• 强调了 快速读取操作，比如他存储表的行数，只需要直接读取已经保存好的值而不需要进行全表扫描。

3.2 InnoDB 引擎

• 支持事务，支持回滚，支持外键；
• 支持 Hash/B-tree 索引类型；
• 锁级别为行锁，行锁优点是适用于高并发的频繁修改，高并发是性能优于 MyISAM；
• 系统小号较大，不仅缓存自身，也缓存数据，相比于 MyISAM 需要更大的内存。

3.3 操作命令

InnoDB 可以选择的操作命令包括：

OPTIMIZE TABLE tablename
ALTER TABLE tablename ENGINE = InnoDB

实际上运行上述清理命令时，MySQL 会锁定表，清理的数据越大，消耗的时间越久，因此这个操作一定要在夜深人静的时候操作。
命令好像是一句废话，它实际执行的是一个空的 ALTER 命令会重建整个表，删除未使用的空白空间。

4. MySQL 为什么会有碎片

以 InnoDB 存储引擎为例，来看看为什么会出现碎片。

• 当执行删除一些行，这些行只是标记为“已删除”，而不是真的从索引中物理删除了，因而空间并没有真正的被释放回收。
• 大量随机删除操作，会造成不连续的空白空间，当插入数据时，这些空白空间会被优先利用起来，但是肯定不会被全部利用起来，也就会存在数据碎片。
• 大量 UPDATE 操作，InnoDB 的最小物理存储分配单位是页，在更新变长时 UPDATE 也可能导致页分裂，频繁的也分裂，页会变得稀疏，并且被不规则的填充，最终会有碎片，比如原来 256 字节修改后是 128 字节，那么可能出现 128 字节左右的空洞无法被利用。