如何最快最安全缩减InnoDB大表的列大小？

针对你这张5300万行、26.3GiB的InnoDB表，调整多个VARCHAR(200)列到更小长度的场景，新建表再迁移数据的方案确实是当前最优选择——比直接执行ALTER TABLE更可控、风险更低，且能最大限度减少对业务的影响。以下是具体操作步骤和注意事项：

一、前置准备 #

1. 确认数据安全边界：先检查所有要调整的VARCHAR列的实际数据长度，确保没有超过目标长度（比如name列要确认所有数据长度≤80），避免迁移时数据被截断：

   SELECT MAX(LENGTH(name)) FROM your_table;
   -- 其他需要调整的列同理执行
   

2. 全量备份原表：操作前必须对原表做全量备份，防止意外导致数据丢失。
3. 选择低峰期操作：尽量在业务访问量最低的时间段执行，减少对正常业务的干扰。

二、具体操作步骤 #

1. 创建结构调整后的新表 #

先复制原表结构（含主键、索引、约束），再修改需要调整的列长度：

-- 复制原表结构
CREATE TABLE new_your_table LIKE your_table;

-- 修改目标列长度，以name列为例，保留原约束（如NOT NULL）
ALTER TABLE new_your_table MODIFY COLUMN name VARCHAR(80) NOT NULL;
-- 其他需要调整的VARCHAR列重复执行上述ALTER语句

2. 批量迁移数据到新表 #

由于数据量极大，建议分批次插入，避免单次操作占用过多资源或锁表时间过长：

-- 关闭自动提交，提升插入效率
SET autocommit = 0;

-- 分批次插入，示例按主键id范围拆分，每次插入100万行
INSERT INTO new_your_table SELECT * FROM your_table WHERE id BETWEEN 1 AND 1000000;
COMMIT;

-- 重复执行上述INSERT+COMMIT，调整id范围，直到全量数据迁移完成

如果服务器资源充足（内存大、IO性能好），也可以直接执行全量插入，但分批次操作更稳妥：

INSERT INTO new_your_table SELECT * FROM your_table;

3. 数据一致性校验 #

迁移完成后必须严格校验数据，确保没有丢失或错误：

-- 核对总行数
SELECT COUNT(*) FROM your_table;
SELECT COUNT(*) FROM new_your_table;

-- 核对调整列的最大长度，确认无截断
SELECT MAX(LENGTH(name)) FROM your_table;
SELECT MAX(LENGTH(name)) FROM new_your_table;

-- 随机抽样核对数据完整性
SELECT * FROM your_table ORDER BY RAND() LIMIT 100;
SELECT * FROM new_your_table ORDER BY RAND() LIMIT 100;

4. 切换新旧表 #

校验无误后，在业务停写（或只读）状态下完成表切换：

-- 锁定原表为只读，防止切换过程中有新数据写入
LOCK TABLE your_table READ;

-- 补插最后一批可能新增的数据（如果业务未完全停写）
INSERT INTO new_your_table SELECT * FROM your_table WHERE id > [最后一批插入的最大id];
COMMIT;

-- 重命名表，完成切换
RENAME TABLE your_table TO old_your_table, new_your_table TO your_table;

-- 解锁表，恢复业务写入
UNLOCK TABLES;

5. 后续清理 #

确认业务正常运行一段时间后，再删除旧表释放空间：

DROP TABLE old_your_table;

三、为什么选择这个方案？ #

直接执行ALTER TABLE your_table MODIFY COLUMN name VARCHAR(80)虽然写法简单，但InnoDB会重建整个表，期间会占用大量磁盘IO和CPU资源，且锁表时间极长（对于5300万行的表，可能需要数小时甚至更久），对业务影响极大。

而新建表迁移的方式，优势在于：

• 操作可控，分批次插入可以随时暂停，不会影响原表正常使用；
• 即使迁移过程中出现问题，原表完全不受影响，风险更低；
• 可以利用服务器空闲资源逐步完成，对业务的冲击最小。

内容的提问来源于stack exchange，提问作者GenesisBits