一 概述

一般情况下在做 MySQL 的备份还原时，都习惯性的使用 mysqldump 和 mysql < backup.sql 的方式进行。最近刚好遇到一个问题：在做数据库的迁移时，把导出来的数据导到新的实例上时，发现新旧两个库所占用的磁盘空间大小不一样，新迁移的库比原有的库占用磁盘空间更大。

二 具体问题

其实根据经验的第一反应，就应该是产生了表空间的碎片，需要进行 optimize table 的操作。

2.1 为什么会有碎片？

经过查阅资料，在使用 InnoDB 作为存储引擎时，它的最小的物理存储单元是 页(page)，也就是说数据库每次进行读写时都是以 页 为单位进行操作，默认情况下每一页是 16KB(16384)，也就是最多可以连续使用的存储空间是 16KB。

SHOW VARIABLES LIKE 'innodb_page_size';

-- 返回：
-- +------------------+-------+
-- | Variable_name    | Value |
-- +------------------+-------+
-- | innodb_page_size | 16384 |
-- +------------------+-------+

因此当我直接创建一个空表时，它的容量也是 16KB:

-- 1. 创建表
CREATE TABLE t1(id int, name varchar(10));

-- 2. 查看表空间大小
SELECT
  table_schema AS '数据库',
  table_name AS '表名',
  table_rows AS '记录数',
  data_length as '数据容量(Bytes)',
  index_length '索引容量(Bytes)'
FROM
  information_schema.tables
WHERE
  table_schema='sretalk' /* 数据库名 */
ORDER BY
  table_rows DESC,
  index_length DESC;

-- 返回:
-- +-----------+--------+-----------+---------------------+---------------------+
-- | 数据库    | 表名   | 记录数    | 数据容量(Bytes)     | 索引容量(Bytes)     |
-- +-----------+--------+-----------+---------------------+---------------------+
-- | sretalk   | t1     |         0 |               16384 |                   0 |
-- +-----------+--------+-----------+---------------------+---------------------+

一般情况下进行大量的 INSERT、UPDATE 有可能导致 页分裂 从而产生碎片（简单来说，就是一个页满了但还需要插入新行时，MySQL 可能会对页进行分裂成两个，并将新行插入到其中一页）。

而 DELETE 就更好理解，因为 DELETE 语句实际上是给该数据打了个 已删除 的标签，并不是进行物理性的删除，因此就这部分的磁盘空间也不会被释放。并且如果被标记为已删除的空间一直没有找到合适大小的数据进行填充复写，就会形成一个表空间的空洞，这部分也是碎片的一种。

而使用 mysqldump 的方式进行导出的数据，基本都是以 INSERT INTO xxxx 的语句进行记录，也就是说当导出的数据量大时，在导入到新库时也等于在进行一次大批量的插入动作，也就有可能产生一定的表空间碎片。

-- 查看表状态
SHOW TABLE STATUS LIKE '%t1%'\G;

-- *************************** 1. row ***************************
--            Name: t1
--          Engine: InnoDB
--         Version: 10
--      Row_format: Dynamic
--            Rows: 30360
--  Avg_row_length: 52
--     Data_length: 1589248
-- Max_data_length: 0
--    Index_length: 0
--       Data_free: 4194304
--  Auto_increment: NULL
--     Create_time: 2024-05-17 16:38:47
--     Update_time: 2024-05-17 16:38:52
--      Check_time: NULL
--       Collation: utf8_general_ci
--        Checksum: NULL
--  Create_options:
--         Comment:
-- 1 row in set (0.00 sec)

同时也使用以下 SQL 一并检查当前 MySQL 实例的所有已经产生碎片的表，以及碎片率：

SELECT
  table_schema AS '数据库名', 
  table_name AS '表名', 
  engine AS '数据库引擎',
  table_rows AS '当前行', 
  ROUND(data_free/1024/1024, 2) AS '总碎片空间大小(MB)', 
  ROUND((data_length+index_length)/1024/1024,2) '表大小(MB)', 
  ROUND(data_free/(data_free + data_length+index_length),2) AS '碎片率'
FROM
  information_schema.tables   
WHERE
  table_schema not in ('information_schema', 'mysql') 
AND
  data_free > 0 
ORDER BY 
  '总碎片空间大小(MB)' DESC,
  '碎片率' DESC;

2.2 如何处理？

只有一种处理方式，就是清理表空间的碎片，但不同的数据库引擎所推荐的清理方式：

-- MyISAM:
OPTIMIZE TABLE 表名;

-- InnoDB:
ALTER TABLE 表名 ENGINE=InnoDB;

无论哪种操作，在清理碎片时都会进行锁表。并且数据量越大，清理的时间也越长，锁表的时间也越长。在生产环境上需要注意不要在工作时间进行表空间的清理，并且清理周期不需要太频繁，一般以周或者月为单位即可。

同时，如果数据库有做主从等架构的，在执行表空间清理时会影响主从的同步，因此可以在进行清理前先禁止记录 binlog，再分别在主从两边进行清理：

-- 禁止记录
SET sql_bin_log = OFF;

-- 清理碎片
ALTER TABLE 表名 ENGINE=InnoDB;

-- 开启记录
SET sql_bin_log = ON;

Notes:
要注意的是，该动作只是重新进行整理碎片，但是不排除因为数据大小不一的问题，在整理完之后依然可能存在空洞，只能清理了一部分的 Data_free。