背景：目前需要对业务日志大表A进行迁移，只保留最近3个月的数据，遇到的问题是迁移完成后，新表B的磁盘空间占用没有减少。

MySQL 版本：8.0.36，InnoDB存储引擎。

注意：主键id是自增的，bigint类型

A表有3.6亿数据，磁盘占用 100GB，最近3个月的数据，约4000万数据，我们的方案是建新表B，然后将A表的数据导入进来。
表B结构和A表一样，但是未指定 AUTO_INCREMENT 的值，也就是默认从1开始。

操作步骤：

  1. 新建B表。
  2. 通过存储过程将A表最近3个月的数据导入B表（id是从300908629开始插入）。
  3. 交换表名完成数据迁移。RENAME TABLE source_table TO source_table_old,source_table_new TO source_table;

存储过程如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
DELIMITER //

CREATE PROCEDURE xxx_New_20250710_Part1()
BEGIN
    DECLARE start_size INT DEFAULT 0;
    DECLARE batch_size INT DEFAULT 100000;
    DECLARE rows_inserted INT DEFAULT batch_size; -- 初始设为 batch_size 以进入循环
    DECLARE start_id BIGINT DEFAULT 300908628;
    DECLARE end_id BIGINT DEFAULT 341345765;
    DECLARE current_max_id BIGINT;

    WHILE rows_inserted >= batch_size DO
        
        INSERT INTO xxx_New_20250710
        SELECT *
        FROM xxx
        WHERE id > start_id and id <= end_id
        ORDER BY id
        LIMIT start_size, batch_size;

        -- 获取实际插入行数
        SET rows_inserted = ROW_COUNT();

        SELECT MAX(id) INTO current_max_id from (
            SELECT id FROM xxx
            WHERE id > start_id and id <= end_id
            ORDER BY id
            LIMIT start_size, batch_size
        ) AS t;

        -- 更新 start_id（只有当 current_max_id 有效时才更新）
        IF current_max_id IS NOT NULL THEN
            SET start_id = current_max_id;
        ELSE
            -- 如果没有数据了，直接退出循环
            SET rows_inserted = 0;
        END IF;

        do sleep(0.5);

    END WHILE;
END //

DELIMITER ;

数据导入完毕后，B表磁盘占用没有减少。
原因是：索引碎片化（物理存储不连续），当创建新表 B 时，若未显式指定 AUTO_INCREMENT 的起始值（默认为 1），但实际插入的数据主键 ID 从 3 亿开始。

InnoDB 会尝试按主键顺序存储数据，但由于插入的 ID 远大于默认值（1），存储引擎需要预留大量空洞（Empty Pages） 来应对“理论上”的主键范围（1 ~ 3亿），导致物理存储不连续，空间利用率低。

所以导致，虽然逻辑数据量仅 4000 万，但物理磁盘占用仍接近原表（100GB）。

解决方案：

方法一：建表时显式指定 AUTO_INCREMENT（推荐）

1
2
3
4
CREATE TABLE B (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=300000000;  -- 关键：设置起始 ID

通过 CREATE TABLE ... AUTO_INCREMENT=300000000 明确告知 InnoDB 主键起始值，存储引擎会跳过预留空洞，直接从 3 亿开始分配物理页。数据物理存储连续，无碎片，因此空间占用会降至合理范围。

方法二：重建表优化空间（会锁表，谨慎使用）

OPTIMIZE TABLE = (ALTER TABLE B ENGINE=InnoDB) + (analyze table)

1
2
3
4
5
-- 重建表（InnoDB 会重新整理物理存储）
ALTER TABLE B ENGINE=InnoDB;

-- 或使用 OPTIMIZE TABLE（效果相同）
OPTIMIZE TABLE B;

最后，对于一直有增量的数据表进行迁移，建议使用开源的一些工具进行数据迁移，如：Github 开源的 gh-ost