MySQL 5.7使用CREATE TABLE LIKE复制表后体积异常增大问题

架构老林

2026-8-28

问题根本原因

该体积异常是MySQL 5.7 InnoDB存储引擎的索引填充机制、批量插入行为、压缩配置未生效三个因素共同导致的：

二级索引随机插入引发低填充率：执行INSERT INTO dest SELECT * FROM src时，默认全表扫描返回的结果虽然主键meta_id是顺序递增的，聚簇索引插入填充率能维持在InnoDB默认的90%左右，但表上的post_id普通索引、meta_key前缀索引的插入是完全随机的，会触发大量B+树页分裂，分裂后的索引页填充率通常不足40%，预留了大量空存储空间，这部分是体积膨胀的核心来源。运行已久的源表src经过长期写入、页合并优化，索引填充率稳定在合理区间，不会存在这类批量插入导致的空页问题。
压缩配置未实际生效：依赖全局默认开启的压缩功能时，5.7.38版本中CREATE TABLE ... LIKE创建的新表不会自动继承源表的KEY_BLOCK_SIZE压缩参数，仅设置ROW_FORMAT=COMPRESSED不会真正启用页压缩；后续执行OPTIMIZE TABLE时，未正确配置压缩参数的表不会触发完整的索引页重建，仅会整理聚簇索引碎片，对二级索引的大量空空间无效。
长字段存储影响极小：表中longtext类型的meta_value字段在DYNAMIC/COMPRESSED行格式下使用溢出页存储，批量插入时溢出页碎片率很低，不是体积膨胀的诱因。

可行缩小体积方案

以下方案均适配MySQL 5.7.38版本，无需升级权限即可操作：

方案一（推荐，体积控制最优、速度最快）：重建表时采用「先插数据、后建二级索引」的逻辑
二级索引在数据全量插入完成后批量构建时，会先对索引值排序再顺序写入页，填充率能接近100%，完全避免随机插入导致的页分裂空耗，操作语句如下：

-- 删除已膨胀的目标表
DROP TABLE IF EXISTS dest;
-- 基于源表创建表结构
CREATE TABLE dest LIKE src;
-- 移除所有二级索引，仅保留主键，显式配置压缩参数（KEY_BLOCK_SIZE可与源表保持一致，utf8mb4场景下8K是通用最优值）
ALTER TABLE dest 
  DROP KEY post_id, 
  DROP KEY meta_key,
  ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
-- 按主键顺序批量插入全量数据，聚簇索引顺序写入无额外碎片
INSERT INTO dest SELECT * FROM src ORDER BY meta_id;
-- 数据插入完成后批量创建二级索引
ALTER TABLE dest 
  ADD KEY `post_id` (`post_id`),
  ADD KEY `meta_key` (`meta_key`(191));

操作完成后dest表体积会与源表基本一致，甚至因无历史碎片略小于源表。

方案二（无需重插数据，适合业务低峰操作）：强制全表重建修复碎片
针对已经写入完成的膨胀表，显式指定压缩参数执行强制表重建，会同步重建聚簇索引和所有二级索引，将页填充率调整到合理区间：
```
ALTER TABLE dest FORCE, ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
```
注意该操作执行期间会持有表元数据锁，大表场景下需要避开业务高峰执行。

注意事项：MySQL 5.7版本不要依赖全局innodb_compression_default参数自动给新表开启压缩，CREATE TABLE ... LIKE在多个小版本中存在不继承源表压缩参数的bug，建表时显式指定KEY_BLOCK_SIZE才能保证压缩正常生效。

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