在业务系统开发中，经常需要处理批量数据入库的场景，比如批量导入用户信息、批量写入日志数据等。如果使用单条INSERT语句逐条写入，会产生大量的数据库连接交互开销，还会频繁触发事务提交和日志写入，严重影响整体性能。掌握SQL批量插入的优化技巧，能大幅提升数据写入效率，降低数据库负载。
为什么单条INSERT写入性能差
单条INSERT写入的性能瓶颈主要来自三个方面：首先是网络交互开销，每执行一条SQL都需要和数据库建立交互，多次往返会累积大量耗时；其次是事务开销，默认情况下每条INSERT都会触发一次事务提交，频繁的事务操作会产生大量日志写入和锁竞争；最后是解析开销，每条SQL都需要经过语法解析、优化器处理等流程，重复执行相同结构的SQL会浪费计算资源。
核心优化技巧
1. 合并INSERT语句，减少交互次数
把多条单条INSERT合并成一条包含多组值的INSERT语句，是最直接的优化方式。这样只需要一次数据库交互，就能完成多条数据的写入，大幅减少网络往返和SQL解析开销。
以MySQL为例，单条写入和批量合并写入的对比如下：
-- 单条写入，执行1000次这样的语句
INSERT INTO user (name, age, email) VALUES ('张三', 20, 'zhangsan@ipipp.com');
-- 合并为一条批量插入语句
INSERT INTO user (name, age, email) VALUES
('张三', 20, 'zhangsan@ipipp.com'),
('李四', 22, 'lisi@ipipp.com'),
('王五', 25, 'wangwu@ipipp.com');
需要注意合并的条数不是越多越好，一般建议单条批量插入的条数控制在500-1000条之间，具体可以根据数据库配置和网络情况调整，避免单条SQL过大导致解析超时或者内存占用过高。
2. 关闭自动提交，手动控制事务
默认情况下，很多数据库的自动提交是开启的，每条INSERT都会自动提交事务。我们可以关闭自动提交，在批量写入完成后再统一提交，减少事务提交的次数。
MySQL中手动控制事务的示例如下：
-- 关闭自动提交
SET autocommit = 0;
-- 执行批量插入
INSERT INTO user (name, age, email) VALUES
('张三', 20, 'zhangsan@ipipp.com'),
('李四', 22, 'lisi@ipipp.com');
-- 手动提交事务
COMMIT;
-- 恢复自动提交（可选）
SET autocommit = 1;
3. 暂时禁用非必要索引和约束
写入数据时，数据库需要同时维护对应的索引，还会校验唯一约束、外键约束等，这些操作会增加写入的耗时。如果是大批量数据导入，可以暂时禁用非必要的索引和约束，写入完成后再重新开启。
MySQL中禁用和启用索引的示例如下：
-- 禁用表的所有非唯一索引
ALTER TABLE user DISABLE KEYS;
-- 执行批量插入
INSERT INTO user (name, age, email) VALUES
('张三', 20, 'zhangsan@ipipp.com'),
('李四', 22, 'lisi@ipipp.com');
-- 重新启用索引
ALTER TABLE user ENABLE KEYS;
需要注意，禁用索引只适合大批量数据导入的场景，小批量写入不建议使用，而且操作前需要确认索引是否真的非必要，避免影响业务查询。
4. 使用数据库自带的批量导入工具
如果数据量非常大，比如百万级以上的数据导入，使用INSERT语句即使优化后效率也可能不够，这时候可以使用数据库自带的批量导入工具。比如MySQL的LOAD_DATA_INFILE，PostgreSQL的COPY命令，这些工具是数据库底层实现的批量导入逻辑，效率比INSERT语句高很多。
MySQL使用LOAD_DATA_INFILE的示例如下：
-- 从本地文件导入数据到user表，文件字段用逗号分隔，行用换行分隔 LOAD DATA INFILE '/tmp/user_data.csv' INTO TABLE user FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (name, age, email);
不同数据库的批量插入差异
不同数据库的批量插入语法和支持的特性略有不同，下面是常见数据库的实现方式对比：
| 数据库类型 | 批量插入语法 | 特殊注意事项 |
|---|---|---|
| MySQL | INSERT INTO 表名 (列1,列2) VALUES (值1,值2),(值3,值4) | 单条SQL建议不超过1000条，支持LOAD_DATA_INFILE导入 |
| PostgreSQL | INSERT INTO 表名 (列1,列2) VALUES (值1,值2),(值3,值4)，或使用COPY命令 | COPY命令效率更高，支持从文件或标准输入导入 |
| SQL Server | INSERT INTO 表名 (列1,列2) VALUES (值1,值2),(值3,值4)，或使用BULK INSERT | BULK INSERT适合大文件导入，需要对应文件权限 |
| Oracle | INSERT ALL INTO 表名 (列1,列2) VALUES (值1,值2) INTO 表名 (列1,列2) VALUES (值3,值4) SELECT 1 FROM DUAL | 也可以使用SQL*Loader工具做批量导入 |
优化时的注意事项
- 批量插入的条数需要合理控制，避免单条SQL过大导致数据库解析失败或者内存溢出，建议根据测试调整最优条数。
- 禁用索引和约束前，需要确认不会影响业务数据的正确性，导入完成后及时恢复索引和约束。
- 批量写入时如果中途出现异常，需要做好回滚处理，避免部分数据写入导致数据不一致。
- 如果表有自增主键，大批量插入时可以暂时调整自增步长或者关闭自增检查，减少自增锁的竞争。
实际开发中可以根据数据量大小、数据库类型、业务场景选择合适的优化方案，小批量数据用合并INSERT语句加手动事务的方式就能满足需求，大批量数据则建议结合数据库自带的导入工具，才能达到最优的写入性能。