MySQL高效数据迁移：三种批量插入与导出实战方案

1. MySQL数据迁移的三种高效方案刚接手数据库迁移任务时我像大多数新手一样用最笨的逐条INSERT语句结果导入10万条数据花了近半小时。直到踩过几次坑才发现MySQL其实内置了三种高效的批量操作方案今天就把这些实战经验分享给大家。先说说这三种方法的适用场景当需要从其他表复制数据时INSERT...SELECT是首选处理程序生成的批量数据时VALUES多行插入最方便而面对CSV等格式文件时LOAD DATA INFILE的速度能快到让你怀疑人生。在企业级数据同步场景中这三种方法配合secure_file_priv安全配置能解决90%的数据迁移需求。2. INSERT...SELECT表间数据搬运工2.1 基础用法与性能优势上周帮电商团队迁移用户数据时我用这个语句5分钟完成了200万条记录转移INSERT INTO new_users (id, name, email) SELECT user_id, username, contact_email FROM legacy_accounts WHERE register_date 2020-01-01;这种方式的精髓在于完全在服务器内部完成数据传输。实测对比显示相比客户端逐条插入速度提升约20倍。特别是在云数据库场景下避免了网络往返开销当源表和目标表都在同一MySQL实例时引擎甚至可以直接操作内存数据页。2.2 复杂查询的处理技巧遇到需要联表查询的情况时建议先用EXPLAIN分析执行计划。曾有个坑是同事在迁移时用了三表JOIN导致性能骤降后来改成临时表方案-- 先创建临时表存储中间结果 CREATE TEMPORARY TABLE temp_orders AS SELECT o.id, u.name, p.sku FROM orders o JOIN users u ON o.user_idu.id JOIN products p ON o.product_idp.id; -- 再从临时表导入目标表 INSERT INTO report_orders SELECT * FROM temp_orders;2.3 事务与锁的注意事项大批量操作要特别注意锁问题。有次在业务高峰期执行迁移导致前端请求阻塞。后来学乖了采用两种优化策略对于MyISAM表使用DELAYED关键字注意MySQL 8.0已移除该特性对InnoDB表合理设置事务隔离级别并分批次提交START TRANSACTION; INSERT INTO...SELECT...LIMIT 10000; COMMIT; -- 循环执行直到完成3. VALUES多行插入程序生成数据的首选3.1 基础语法进化史从MySQL 5.7开始VALUES语法支持单语句插入多行数据。对比下面两种写法新版本性能提升明显-- 传统方式不推荐 INSERT INTO logs (time, event) VALUES (NOW(), start); INSERT INTO logs (time, event) VALUES (NOW(), click); INSERT INTO logs (time, event) VALUES (NOW(), close); -- 现代方式推荐 INSERT INTO logs (time, event) VALUES (NOW(), start), (NOW(), click), (NOW(), close);实测插入1万条数据单条语句批量插入比逐条执行快47倍。但要注意单个SQL语句大小限制建议每批控制在1MB左右。3.2 预处理语句实战在Java/Python等程序中一定要使用预处理语句防止SQL注入。这是我在Spring项目中的典型用法// Java示例 String sql INSERT INTO products (name, price) VALUES (?, ?); PreparedStatement ps connection.prepareStatement(sql); for(Product p : productList) { ps.setString(1, p.getName()); ps.setDouble(2, p.getPrice()); ps.addBatch(); // 添加到批处理 if(i % 1000 0) { ps.executeBatch(); // 每1000条执行一次 } } ps.executeBatch(); // 处理剩余记录3.3 性能调优参数这几个关键参数直接影响插入性能max_allowed_packet控制单个SQL最大尺寸建议设为16M-64Minnodb_buffer_pool_sizeInnoDB缓冲池大小应为可用内存的70-80%autocommit批量操作前设置为0完成后手动提交曾经有个项目插入速度异常慢最后发现是没设置rewriteBatchedStatementstrue这个JDBC参数加上后性能直接翻倍。4. LOAD DATA INFILE文件导入的终极武器4.1 安全配置先行第一次用这个命令时遇到了ERROR 1290原来是没配置好secure_file_priv。正确的操作流程-- 查看当前允许的目录 SHOW VARIABLES LIKE secure_file_priv; -- 修改my.cnf文件 [mysqld] secure_file_priv /var/lib/mysql-import/记得给目录赋权chown -R mysql:mysql /var/lib/mysql-import chmod 750 /var/lib/mysql-import4.2 完整参数详解处理CSV文件时这个模板能解决90%的格式问题LOAD DATA INFILE /var/lib/mysql-import/data.csv INTO TABLE transactions CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY ESCAPED BY \\ LINES TERMINATED BY \n IGNORE 1 LINES (transaction_date, amount, user_var) SET user_id NULLIF(user_var,);特殊技巧包括用变量暂存数据后再转换NULLIF处理空字符串SET子句进行数据清洗4.3 事务与性能平衡默认情况下LOAD DATA INFILE是自动提交的要保证原子性需要mysql --single-transaction -e LOAD DATA INFILE...对于亿级数据导入我通常这样做先删除目标表索引使用CONCURRENT选项Percona特有导入完成后重建索引最后执行ANALYZE TABLE更新统计信息5. 数据导出SELECT INTO OUTFILE实战5.1 基础导出示例把查询结果导出为CSVSELECT id, name, salary INTO OUTFILE /var/lib/mysql-export/employees.csv CHARACTER SET utf8mb4 FIELDS TERMINATED BY | ENCLOSED BY LINES TERMINATED BY \n FROM employees WHERE department IT;注意字段分隔符的选择复杂数据建议用|或\t避免与内容冲突。5.2 大数据集分块技巧导出千万级数据时我常用分页方式避免内存溢出-- 导出脚本示例 SET page0; SET pagesize100000; PREPARE stmt FROM SELECT * INTO OUTFILE CONCAT(/var/lib/mysql-export/chunk_,?) FIELDS TERMINATED BY , FROM large_table LIMIT ? OFFSET ? * ?; WHILE page 10 DO EXECUTE stmt USING page, pagesize, page, pagesize; SET page page 1; END WHILE;5.3 与客户端工具对比虽然mysqldump也能导出数据但SELECT INTO OUTFILE在某些场景优势明显导出速度比mysqldump快3-5倍支持自定义列和精确条件过滤输出格式更灵活如生成Excel兼容的CSV不过要注意导出的文件默认保存在服务器端需要用scp等方式传输到本地。