一次迁移25GB亿级数据表，从数小时到分钟级的性能飞跃

在日常数据库管理中，大概率都遇到过这样的挑战：需要迁移一张包含数亿记录的大表，而不影响业务正常运行。传统的mysqldump方法不仅耗时数小时，还可能在整个过程中锁表，影响系统可用性。

本文将介绍三种经过实战检验的高效MySQL大表拷贝方案，让你在面对大数据迁移时，从容不迫。

为什么需要专门的大表迁移方案？

当数据量较小时（几万到几十万条），传统的mysqldump工具或INSERT INTO ... SELECT语句可能还能应付。但当数据量达到百万级甚至亿级时，这些方法会面临：

长时间锁表，导致业务停机
耗尽内存和CPU资源，影响数据库性能
导入导出速度极慢，迁移时间不可控
主从复制延迟，影响数据一致性

下面介绍三种高效安全的解决方案，帮你告别这些烦恼。

方案一：可传输表空间（推荐用于同服务器大数据量）

可传输表空间（Transportable Tablespace）是MySQL 5.6及以上版本提供的功能，它允许直接拷贝InnoDB表的物理文件（.ibd文件），避免了耗时的SQL解析和B+树节点分裂过程。

实施步骤

在目标库创建相同表结构

-- 在目标数据库创建与源表完全相同的表结构
CREATE TABLE `t_target` (
  `UNIQUE_KEY` varchar(32) NOT NULL,
  `DESC` varchar(64) DEFAULT NULL,
  `NUM_ID` int(10) DEFAULT '0',
  PRIMARY KEY (`UNIQUE_KEY`),
  KEY `index_NumID` (`NUM_ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

丢弃目标表的表空间

-- 此操作会删除目标表的ibd文件，只剩下frm文件
ALTER TABLE t_target DISCARD TABLESPACE;

在源库锁定表并准备文件

-- 在源数据库执行，此命令会锁定表并生成cfg文件
FLUSH TABLES t_source FOR EXPORT;

此命令会对表加锁，将脏数据从buffer pool同步到表文件，并生成一个t_source.cfg文件（存储表的数据字典信息）。

拷贝源表的ibd和cfg文件
保持源库的会话打开状态，将源表的t_source.cfg和t_source.ibd文件拷贝到目标库的数据目录中。修改文件属主为mysql用户。

# 示例拷贝命令
scp /var/lib/mysql/source_db/t_source.{cfg,ibd} target_server:/var/lib/mysql/target_db/
# 修改属主
chown mysql:mysql /var/lib/mysql/target_db/t_source.*

解锁源表

-- 在源库释放锁，使表恢复正常读写
UNLOCK TABLES;

导入表空间到目标表

-- 在目标库执行，导入表空间数据
ALTER TABLE t_target IMPORT TABLESPACE;

案例与性能

某企业需要迁移一张压缩后25GB、包含数亿条记录的表：

mysqldump方案：预计耗时4小时以上
可传输表空间方案：实际耗时约6分钟（导入时间）+ 文件传输时间

适用场景与限制

优点：速度极快，适合大数据量迁移，减少锁表时间
缺点：源库和目标库MySQL版本必须一致；只适用于InnoDB引擎；源库执行flush tables for export时表会不可写
适用场景：大数据量（几十万到数百万条）；A和B表在同一服务器且表结构完全一致

方案二：CSV文件导出与LOAD DATA INFILE

对于不同服务器间的大表迁移，导出CSV文件并结合LOAD DATA INFILE语句是一种性能优异的选择。这种方法避免了SQL解析和单条插入的开销，直接批量加载数据。

实施步骤

在源库导出数据为CSV文件

-- 导出数据到CSV文件
SELECT * INTO OUTFILE '/tmp/t_source_data.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM t_source;

如果需要筛选部分数据或字段，可以在SELECT语句中添加条件或指定字段。

将CSV文件传输到目标服务器

# 使用scp或其他工具将文件传输到目标服务器
scp /tmp/t_source_data.csv target_server:/tmp/

在目标库加载CSV数据

-- 加载CSV文件数据到目标表
LOAD DATA INFILE '/tmp/t_source_data.csv'
INTO TABLE t_target
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

高级技巧与注意事项

对于主从复制环境，使用LOCAL关键字让从库在本地查找文件：

LOAD DATA LOCAL INFILE '/tmp/t_source_data.csv'
INTO TABLE t_target
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

通过SHOW GLOBAL VARIABLES LIKE 'secure_file_priv'检查MySQL允许读取文件的位置
导入前禁用索引可以大幅提高导入速度：

-- 导入前禁用索引
ALTER TABLE t_target DISABLE KEYS;

-- 执行LOAD DATA INFILE

-- 导入后重新启用索引
ALTER TABLE t_target ENABLE KEYS;

性能对比

根据实际测试，对于5万多条数据的迁移：

mysqldump方案：约消耗1秒钟
CSV导出+LOAD DATA方案：导出约0.04秒，导入约0.27秒，比mysqldump快两倍多

适用场景与限制

优点：性能极高；节省磁盘空间（CSV文件比SQL文件更紧凑1；灵活性强（可筛选字段）
缺点：需要文件权限；不支持表结构迁移；CSV文件必须与目标表字段严格匹配
适用场景：大数据量（几十万到数百万条）；A和B表不在同一服务器但可传输文件；只需要迁移数据，表结构已提前创建

方案三：物理文件拷贝（适用于同服务器）

直接拷贝MySQL的物理数据文件是一种极为快速的迁移方法，特别适用于同一服务器上的数据库迁移。这种方法直接操作文件系统，完全绕过MySQL的SQL处理层。

实施步骤

停止MySQL服务（或确保没有写入操作）

# 停止MySQL服务
systemctl stop mysql

拷贝InnoDB文件
拷贝源表的.frm（表定义）和.ibd（表数据和索引）文件到目标数据库目录：

cp /var/lib/mysql/source_db/t_source.frm /var/lib/mysql/target_db/
cp /var/lib/mysql/source_db/t_source.ibd /var/lib/mysql/target_db/

修改文件权限

# 确保文件属主为mysql用户
chown mysql:mysql /var/lib/mysql/target_db/t_source.*

启动MySQL服务

# 启动MySQL服务
systemctl start mysql

在数据字典中注册表空间
对于InnoDB存储引擎，还需要在数据字典中注册表空间：

-- 在目标数据库执行
ALTER TABLE t_target IMPORT TABLESPACE;

案例与性能

某案例中，使用物理文件拷贝方式迁移近亿条数据量的大表，整个过程耗时仅5分钟左右（主要耗时在传输ibd文件）。

适用场景与限制

优点：速度极快；适合特别大的表；保持原始数据格式
缺点：需要停止MySQL服务或锁定表；要求目标服务器配置与源服务器严格匹配；可能出现一致性问题
适用场景：同服务器上的大数据量迁移；可以接受短暂停机的场景；MySQL版本和配置完全一致的环境

综合对比与选择指南

下表总结了三种方案的特性对比：

特性	可传输表空间	CSV导出+LOAD DATA	物理文件拷贝
性能	极高	高	极高
复杂性	中	中	高
是否需要额外工具	是	是	是
是否支持跨服务器	否	是（需传输文件）	否
是否支持数据转换	否	否	否
是否需要锁表	是（短暂）	是（导出时）	是（需停止服务）

如何选择？

根据你的具体需求选择最适合的方案：

同服务器大数据量迁移：优先选择可传输表空间方案
不同服务器间大数据量迁移：选择CSV导出+LOAD DATA INFILE方案
可以停机且同服务器迁移：考虑物理文件拷贝方案（速度最快）
中小数据量迁移：考虑使用INSERT INTO ... SELECT或mysqldump

总结

大数据量表迁移是数据库管理员经常会面临的挑战，选择正确的方案可以节省大量时间并减少业务影响。

对于同服务器大数据迁移，可传输表空间方案是最佳选择，它在速度和可靠性之间取得了良好平衡。
对于跨服务器大数据迁移，CSV导出+LOAD DATA INFILE方案提供了出色的性能和灵活性。
对于可以接受停机的场景，物理文件拷贝方案提供了极致的速度体验。

记住，无论选择哪种方案，都务必先在测试环境验证整个流程，并确保有完整的备份和回滚计划。提前规划、测试和验证是成功完成大数据迁移的关键。

欢迎在评论区分享你在MySQL大表迁移方面的经验和挑战！

星驰编程网

免费编程资源分享平台_编程教程_代码示例_开发技术文章

告别停机!3种高性能MySQL大表拷贝方案实战总结

为什么需要专门的大表迁移方案？

方案一：可传输表空间（推荐用于同服务器大数据量）

实施步骤

案例与性能

适用场景与限制

方案二：CSV文件导出与LOAD DATA INFILE

实施步骤

高级技巧与注意事项

性能对比

适用场景与限制

方案三：物理文件拷贝（适用于同服务器）

实施步骤

案例与性能

适用场景与限制

综合对比与选择指南

如何选择？

总结