MariaDB向MS SQL插入数据慢的原因及50万行数据迁移优化咨询

DevOps 小满

2026-8-23

问题原因分析

你遇到的两种插入方式速度差异，核心原因在于数据提交的批量性和CONNECT引擎的处理机制：

逐行提交 vs 批量提交
当使用INSERT INTO Tabxxx SELECT * FROM Tabyyy时，CONNECT引擎的ODBC表类型默认会逐行将数据发送到SQL Server，每一行都单独执行INSERT操作。这会带来大量的网络往返开销，同时SQL Server需要为每一行单独写入事务日志、执行约束检查，累计下来性能损耗极大。
而多值INSERT INTO Tabxx VALUES(...),(...)...是将一批数据打包成单条SQL语句发送，大幅减少了网络交互次数，SQL Server可以批量处理日志写入和约束检查，效率自然更高。
CONNECT引擎的ODBC表映射限制
Tabxxx作为直接映射远程SQL Server表的CONNECT表，其写入逻辑更偏向于单条记录的ODBC API调用，没有针对批量插入做优化；而ExternCommand通过执行完整的批量INSERT语句，相当于将批量操作的控制权直接交给SQL Server，避免了中间层的逐行处理开销。
事务提交开销
如果没有显式开启事务，第一种方式可能会为每一行自动提交事务，事务提交的IO开销被重复执行；而批量INSERT通常在单个事务内完成，事务提交的开销被分摊到所有批量行上，进一步放大了性能差异。

50万行数据迁移加速方案

针对50万行的迁移需求，可以从以下几个方向优化：

分批次执行批量INSERT
利用ExternCommand的方式，将50万行数据分成多个批次（比如每批次500-1000行，避免单条SQL语句过长），生成对应的多值INSERT语句执行。示例：
```
SELECT * FROM ExternCommand WHERE cmd = "INSERT INTO DTB_INTERCHANGE.dbo.Tabxxx VALUES (val1,val2,...),(valN,valN+1,...)";
```
可以通过MariaDB的存储过程或脚本自动生成批次化的INSERT语句，避免手动操作。
调整CONNECT引擎的批量参数
修改Tabxxx表的OPTION_LIST，添加批量相关参数（需确认CONNECT引擎版本支持），让CONNECT引擎自动批量发送数据：
```
ALTER TABLE `Tabxxx` OPTION_LIST = 'Memory=2,BatchSize=1000';
```
同时可以调整BLOCK_SIZE为更大的值（比如10000），配合批量提交逻辑。

显式开启事务批量插入
无论使用哪种方式，都显式开启事务，分批次插入后再提交，减少事务提交的次数：

START TRANSACTION;
INSERT INTO Tabxxx SELECT * FROM Tabyyy WHERE ID BETWEEN 1 AND 10000;
INSERT INTO Tabxxx SELECT * FROM Tabyyy WHERE ID BETWEEN 10001 AND 20000;
-- 重复执行直到所有数据插入完成
COMMIT;

使用SQL Server原生批量导入工具
脱离数据库间的ODBC连接，先将MariaDB的数据导出为CSV格式文件：
```
SELECT * INTO OUTFILE '/tmp/tabyyy_data.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM Tabyyy;
```
然后使用SQL Server的bcp命令或SSIS工具批量导入，这是性能最高的方式之一，原生工具针对批量导入做了大量优化（比如大容量日志模式、跳过部分约束检查等）。
优化数据库配置
- SQL Server端：将数据库恢复模式临时改为大容量日志模式，减少批量插入时的日志写入开销；关闭不必要的约束和索引（迁移完成后再重建）。
- MariaDB端：确保Tabyyy表的ID列有索引，加速SELECT查询速度；调整查询缓存或临时表参数，提升数据读取效率。

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