MySQL碎片清理

为什么产生？

经过大量增删改的表，都可能存在碎片

MySQL数据结构是B+树，
删除某一记录，只会标记为删除，后续插入一条该区间的记录，就会复用这个位置。
删除整个数据页的记录，则整个页标记为“可复用”，但磁盘文件的大小是不会变的。

产生碎片就要定期清理

官方建议不要频繁(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
建议：碎片空间大或者占比>50% 再做整理。

# 查询现状碎片情况
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, AUTO_INCREMENT, TABLE_COMMENT, DATA_FREE, ROUND(data_free / 1024 / 1024 / 1024, 2) AS free_G
FROM `information_schema`.tables
WHERE 1 = 1AND `TABLE_SCHEMA` NOT IN ('information_schema', 'mysql')
HAVING free_G > 1
ORDER BY `DATA_FREE` DESC
LIMIT 10;

如何清理？

官方做法

ALTER TABLE tbl_name ENGINE=INNODB

You can also use ALTER TABLE tbl_name FORCE to perform a “null” alter operation that rebuilds the table.

会重建表，这个过程会不会锁表？影响数据读写？

搜集相关知识，有人提供方案

ALTER TABLE tbl_name ENGINE=INNODB, ALGORITHM=INPLACE

锁表时间很短，几乎不影响业务读写，属于Online DDL
详情参考资料3


至于 inplace 和 copy 的差异简单来讲：

copy 基于临时表，锁表时间长，容易丢数据
inplace 基于临时文件,锁表时间短，不易丢数据。

那么需要指定 ALGORITHM 吗？
搜索MySQL官方文档，发现

The ALGORITHM clause is optional. If the ALGORITHM clause is omitted, MySQL uses ALGORITHM=INPLACE for storage engines and ALTER TABLE clauses that support it. Otherwise, ALGORITHM=COPY is used.

不指定ALGORITHM的情况下，如果alter语句支持 inplace,就使用inplace,否则就用copy

哪些表操作支持呢？

官方给了回答

所以整理碎片 直接执行即可

ALTER TABLE tbl_name ENGINE=INNODB

开源工具

除了mysql官方的实现方式，业界也有一些知名开源工具解决大表的变更，gh-ost就是一例。

关键步骤如下：详细点击

创建影子表
存量数据同步
增量数据同步-by binLog
锁表，更换表名
删除原表

参考资料

• MySQL整理碎片
• MySQL Online DDL
• mysql重建表
• mysql在线修改表结构的影响
  -MySQL Online DDL工具gh-ost
• gh-ost漏洞