速度提升10倍！详解MySQL大表拷贝的终极技巧

内容分享5天前发布

1 6 0

一次生产事故中，原本需要10小时的大表拷贝操作，被我用一种方法压缩到45分钟完成。以下是经过实战检验的终极技巧。

为什么传统拷贝方式效率低下？

直接使用CREATE TABLE new_table SELECT * FROM old_table或INSERT INTO new_table SELECT * FROM old_table拷贝大表时，你会遇到：

锁表问题：长时间阻塞写操作
事务日志暴涨：undo log和redo log快速增长
内存压力：可能导致大量磁盘临时文件
主从延迟：在复制环境中尤为明显

下面介绍的五种方法能有效解决这些问题。

方法一：分批插入（推荐指数：⭐⭐⭐）

原理与步骤

通过分页查询减少单次事务大小和锁持有时间，每次处理一小部分数据。

-- 设置每次处理的数据量
SET @batch_size = 10000;
SET @max_id = (SELECT MAX(id) FROM old_table);
SET @min_id = (SELECT MIN(id) FROM old_table);

WHILE @min_id <= @max_id DO
    INSERT INTO new_table 
    SELECT * FROM old_table 
    WHERE id BETWEEN @min_id AND @min_id + @batch_size - 1;
    
    SET @min_id = @min_id + @batch_size;
END WHILE;

适用场景

中小规模表（几十GB以下）
需要在线操作，对业务影响要小
有自增主键或唯一递增字段

方法二：导出导入（推荐指数：⭐⭐⭐⭐）

原理与步骤

使用mysqldump导出数据再导入，结合特定参数优化性能。

# 导出数据（不包含表结构）
mysqldump -uusername -ppassword 
    --single-transaction 
    --quick 
    --no-create-info 
    database old_table > old_table_data.sql

# 导入数据
mysql -uusername -ppassword database < old_table_data.sql

关键参数解析：

–single-transaction：创建一致性快照，不锁表
–quick：逐行检索数据，减少内存使用
–no-create-info：不包含建表语句

适用场景

需要跨服务器拷贝
表结构已预先创建好
有文件传输带宽

方法三：物理拷贝（推荐指数：⭐⭐⭐⭐⭐）

原理与步骤

直接拷贝MySQL的底层数据文件（.ibd），速度最快。

-- 1. 锁定表获取一致性状态
FLUSH TABLES old_table FOR EXPORT;

-- 2. 拷贝.ibd文件（在操作系统层面执行）
-- cp /var/lib/mysql/database/old_table.ibd /backup/

-- 3. 解锁表
UNLOCK TABLES;

-- 4. 新表继承旧表结构
CREATE TABLE new_table LIKE old_table;

-- 5. 卸载新表的表空间
ALTER TABLE new_table DISCARD TABLESPACE;

-- 6. 将拷贝的.ibd文件移动到新表目录
-- mv /backup/old_table.ibd /var/lib/mysql/database/new_table.ibd

-- 7. 导入表空间
ALTER TABLE new_table IMPORT TABLESPACE;

适用场景

极大数据量（TB级别）
追求最快拷贝速度
可接受短暂表锁定

方法四：利用Shell管道（推荐指数：⭐⭐⭐⭐）

原理与步骤

使用管道将导出和导入操作串联，避免中间文件。

mysqldump -uusername -ppassword 
    --single-transaction 
    --quick 
    database old_table | 
mysql -uusername -ppassword 
    -C database

性能优化技巧：

# 增加网络缓冲区大小
mysqldump ... | mysql --max_allowed_packet=512M ...

# 使用压缩传输（适合跨机房）
mysqldump ... | gzip | ssh user@remote "gunzip | mysql ..."

适用场景

跨服务器传输
磁盘IO成为瓶颈
需要避免中间文件存储

方法五：基于二进制日志的同步（推荐指数：⭐⭐⭐⭐）

原理与步骤

先拷贝基础数据，再通过二进制日志追增量。

-- 1. 记录开始位置
SHOW MASTER STATUS;
-- 记下File和Position

-- 2. 拷贝基础数据（使用前述任何方法）
INSERT INTO new_table SELECT * FROM old_table;

-- 3. 应用从开始位置到目前的所有二进制日志
mysqlbinlog --start-position=123456 
    mysql-bin.000001 | mysql -uusername -ppassword

适用场景

要求业务零中断
可接受最终一致性
有二进制日志 enabled

终极技巧对比分析

方法	推荐指数	速度	锁表时间	复杂度	适用数据量
分批插入	⭐⭐⭐	中等	很短	低	中小表
导出导入	⭐⭐⭐⭐	中快	很短	中	大表
物理拷贝	⭐⭐⭐⭐⭐	极快	中等	高	超大表
Shell管道	⭐⭐⭐⭐	快	很短	中	大表
二进制日志同步	⭐⭐⭐⭐	依赖增量	无	高	所有规模