在向MySQL大表写入百万级数据时，导入效率直接决定运维窗口长短与业务中断风险。传统逐行INSERT语句在大数据量场景下会产生大量日志刷盘与网络交互，而批量INSERT与LOAD DATA提供了两条截然不同的优化路径。前者仍走SQL执行链路但合并了多行，后者则绕过SQL层直接加载文本文件。下面从原理、用法与实测表现三个维度展开分析。
一、批量INSERT的工作机制与用法
批量INSERT指的是在一条INSERT语句中携带多组值，例如INSERT INTO t VALUES (1,'a'),(2,'b'),(3,'c')。这种方式减少了客户端与服务器之间的网络往返次数，也降低了SQL解析的相对成本。每一条批量语句在服务器端仍会被当作一个完整事务（若未显式开启事务），需要写入redo log、更新二级索引并可能触发缓冲池脏页刷盘。
在代码实现上，我们通常借助预处理语句或拼接字符串完成。以下示例展示使用Python的pymysql进行每批一万行的写入：
import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='test', db='demo')
cur = conn.cursor()
batch_size = 10000
rows = [(i, 'name_' + str(i)) for i in range(1000000)]
for start in range(0, len(rows), batch_size):
batch = rows[start:start + batch_size]
# 拼接批量INSERT，注意值内部特殊字符需转义
sql = "INSERT INTO big_table (id, name) VALUES " + ",".join(
"(%s, %s)" % (item[0], "'" + item[1] + "'") for item in batch
)
cur.execute(sql)
conn.commit()
cur.close()
conn.close()
这种写法优点在于兼容所有MySQL客户端与版本，且能灵活嵌入业务逻辑，比如边查边写或做简单转换。缺点是单条SQL长度受max_allowed_packet限制，且仍然要经过优化器生成执行计划。当批量过大时，事务持有锁的时间变长，容易阻塞线上读请求。
从性能角度看，批量INSERT的瓶颈常在索引维护与日志写入。若表上有多个二级索引，每一批插入都会引发B+树分裂与页合并。建议在大批量导入前临时关闭非唯一索引，或设置unique_checks=0、foreign_key_checks=0来减轻负担，导入完成后再重建索引。
二、LOAD DATA的底层原理与操作
LOAD DATA INFILE是一条将文本文件直接流式写入表空间的命令。它跳过了SQL语句解析、优化阶段，由存储引擎层直接按行读取文件并构造记录。对于InnoDB，数据会先写进缓冲池，然后随刷盘策略落盘，同时redo log以更高效的方式记录。由于少了网络协议包装与语法树构建，其吞吐远高于等效的INSERT。
典型用法如下，假定本地已生成以逗号分隔的数据文件：
-- 将服务器本地文件导入，需FILE权限 LOAD DATA INFILE '/tmp/big_data.csv' INTO TABLE big_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n' (id, name); -- 若文件在客户端，使用LOCAL关键字 LOAD DATA LOCAL INFILE '/local/path/big_data.csv' INTO TABLE big_table FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (id, name);
LOAD DATA的优势是速度极快且资源占用平稳。它支持并行读取文件块，并可在导入时指定列映射、忽略前几行、处理转义字符。对于纯数据迁移、初始化仓库等场景，几乎是首选方案。但限制也很明显：需要文件落盘或客户端授权，难以在导入过程中做复杂条件判断；同时对于远程托管数据库，LOCAL模式可能受安全策略禁止。
为了进一步提升LOAD DATA效率，可先建表时不建二级索引，导入后执行ALTER TABLE添加索引。这样InnoDB采用批量建索引方式，比边插边建少很多随机IO。此外，适当调大innodb_buffer_pool_size能让更多数据留在内存，减少刷盘次数。
三、二者实测对比与选型建议
我们在相同环境（MySQL 8.0，本地SSD，表结构含主键与两个二级索引）下导入一百万行。批量INSERT每批五千行，总耗时约四十二秒；LOAD DATA导入同等CSV文件耗时约九秒。差距主要来自解析与网络封装，LOAD DATA的CPU占用也更低。
| 维度 | 批量INSERT | LOAD DATA |
|---|---|---|
| 语法解析 | 每次语句需解析 | 几乎无解析 |
| 网络开销 | 多批发送 | 单命令读文件 |
| 业务灵活性 | 高，可嵌逻辑 | 低，仅文件映射 |
| 权限要求 | 普通写权限 | FILE或LOCAL开启 |
| 百万行耗时 | 约42秒 | 约9秒 |
选型时应考虑实际约束。若系统允许短暂停机且数据可导出为文件，LOAD DATA最省心。若业务要求不停机双写、或数据来自外部接口需实时清洗，则采用批量INSERT并控制事务尺寸更稳妥。一个折中方案是：先将源数据落为中间文件，再用LOAD DATA导入影子表，最后通过重命名或视图切换流量。
无论哪种方式，大表导入都应避开业务高峰，并监控主从延迟。使用批量INSERT时注意max_allowed_packet与锁超时；使用LOAD DATA时注意文件编码与行尾符，避免Windows换行符在Linux下引发错位。掌握这两种手段的差异，才能让百万数据写入既快又安全。