mysql是常用的关系型数据库，在业务开发中经常会遇到需要批量插入大量数据的场景，比如初始化数据、同步第三方数据、业务批量上报数据等。如果插入方式不当，可能会导致插入耗时过长，甚至阻塞其他数据库操作。掌握批量插入的性能优化技巧，能够有效提升数据写入效率，降低数据库压力。
批量插入性能优化的核心技巧
1. 合并单条插入为批量插入语句
最常见的低效插入方式是循环执行单条INSERT语句，每次插入都需要和数据库建立连接、解析SQL、执行写入，开销极大。最优的方式是将多条插入语句合并为一条，减少SQL解析和网络传输的次数。
单条插入的示例（低效）：
-- 循环执行这类语句，效率极低
INSERT INTO user_info (name, age, email) VALUES ('张三', 20, 'zhangsan@ipipp.com');
INSERT INTO user_info (name, age, email) VALUES ('李四', 22, 'lisi@ipipp.com');
INSERT INTO user_info (name, age, email) VALUES ('王五', 25, 'wangwu@ipipp.com');
合并为批量插入的示例（高效）：
-- 一条语句插入多条数据，减少交互次数
INSERT INTO user_info (name, age, email) VALUES
('张三', 20, 'zhangsan@ipipp.com'),
('李四', 22, 'lisi@ipipp.com'),
('王五', 25, 'wangwu@ipipp.com');
需要注意单条SQL的长度限制，mysql默认的max_allowed_packet参数是4M，如果批量数据量过大，可以适当调大这个参数，避免SQL语句被截断。
2. 合理控制事务范围
如果不手动控制事务，mysql默认每条插入语句都是一个独立的事务，每次插入都会触发事务提交，产生大量的日志刷盘操作。我们可以将整个批量插入操作放在一个事务中，插入完成后再统一提交，减少事务提交的次数。
事务控制的示例：
-- 开启事务
START TRANSACTION;
-- 执行批量插入
INSERT INTO user_info (name, age, email) VALUES
('张三', 20, 'zhangsan@ipipp.com'),
('李四', 22, 'lisi@ipipp.com');
INSERT INTO user_info (name, age, email) VALUES
('王五', 25, 'wangwu@ipipp.com'),
('赵六', 28, 'zhaoliu@ipipp.com');
-- 统一提交事务
COMMIT;
如果批量插入的数据量特别大，也可以分批次提交事务，比如每插入1万条数据提交一次，避免长事务占用过多资源。
3. 插入前关闭非必要索引和约束
插入数据时，mysql需要维护表上的所有索引和约束，比如主键索引、唯一索引、外键约束等，这些操作会增加插入的开销。如果是往空表或者大表中插入大量数据，可以在插入前暂时关闭非必要的索引和约束，插入完成后再重新开启。
关闭和开启索引的示例：
-- 关闭非唯一索引的更新，插入时不会维护这些索引
ALTER TABLE user_info DISABLE KEYS;
-- 执行批量插入操作
INSERT INTO user_info (name, age, email) VALUES
('张三', 20, 'zhangsan@ipipp.com'),
('李四', 22, 'lisi@ipipp.com');
-- 插入完成后重新开启索引更新，并重建索引
ALTER TABLE user_info ENABLE KEYS;
注意DISABLE KEYS只对非唯一索引生效，主键索引和唯一索引无法通过这种方式关闭，如果需要处理唯一索引的影响，可以在插入前删除唯一索引，插入后重新创建。
4. 调整数据库参数优化
可以通过调整mysql的一些参数来提升批量插入的性能：
- innodb_flush_log_at_trx_commit：默认值是1，每次事务提交都会把日志刷到磁盘，性能较低。批量插入时可以临时设置为2，日志会写到系统缓存，每秒刷一次磁盘，性能会有明显提升，注意设置后如果数据库宕机可能会丢失1秒内的数据，非核心数据场景可以使用。
- sync_binlog：默认值是1，每次事务提交都会同步binlog到磁盘，批量插入时可以临时设置为大于1的值，或者设置为0，减少binlog同步的次数。
- bulk_insert_buffer_size：MyISAM引擎的批量插入缓存大小，如果是MyISAM表可以适当调大这个参数，提升插入效率。
5. 避免使用触发器和高消耗函数
如果表上定义了触发器，每次插入数据都会触发触发器的执行，增加额外的开销。批量插入前可以暂时禁用触发器，插入完成后再启用。同时尽量避免在插入语句中使用UUID()、NOW()这类每次调用都会重新计算的函数，如果需要这类值，可以在应用层先计算好再传入SQL中，减少数据库的计算压力。
不同场景的优化策略选择
如果是小批量数据插入（比如单次插入几百条），优先使用合并插入语句的方式即可；如果是大批量数据插入（比如单次插入几万到几十万条），可以组合使用合并插入、事务控制、关闭索引的方式；如果是往已有大量数据的表中追加插入，需要评估索引的影响，避免关闭索引导致查询不可用。实际使用时可以根据数据量和业务场景灵活选择优化策略，达到最优的插入效率。