如何快速向数据库表插入超1亿条测试用虚拟数据？

一、先搞定数据生成，别拖插入后腿 #

• 离线生成数据：别在插入时实时生成字段值，用Faker这类库提前生成CSV/Parquet格式的批量数据文件，或者直接用数据库自带的压测工具，比如PostgreSQL的pgbench、MySQL的mysqlslap，原生支持高并发数据生成，比自己写脚本效率高一大截。
• 复用基础数据：预先生成一批常用字段的取值集合，插入时随机挑选，减少重复计算的CPU消耗。

二、用批量插入替代单条插入，效率直接起飞 #

• 事务批量提交：关闭自动提交，每1万-10万条数据打包成一个事务提交，避免频繁刷日志和锁竞争。举个MySQL的例子：

  START TRANSACTION;
  INSERT INTO test_table (col1, col2) VALUES (val1, val2), (val3, val4), ...; -- 一次性塞几万条
  COMMIT;
  

• 用数据库原生导入工具：这是最快的方式，直接跳过SQL解析层写磁盘：
  • MySQL用LOAD DATA INFILE或mysqlimport
  • PostgreSQL用COPY FROM
  • SQL Server用BULK INSERT，Oracle用SQL*Loader
• 调大SQL长度限制：比如MySQL的max_allowed_packet参数，确保单条INSERT能容纳足够多的数据行。

三、临时阉割数据库的“安全机制”，插入完再恢复 #

• 先关索引和约束：插入时维护索引的开销远大于插入本身，临时关闭主键外键、唯一约束、普通索引，插完数据再重新创建，速度能提升数倍。
• 调优数据库参数：
  • 增大日志缓冲区：比如MySQL的innodb_log_buffer_size、PostgreSQL的wal_buffers，减少刷盘次数
  • 调大事务日志文件：MySQL的innodb_log_file_size，避免频繁切换日志文件
  • 关闭同步提交：测试环境可以牺牲一点一致性换速度，MySQL设innodb_flush_log_at_trx_commit=2，PostgreSQL设synchronous_commit=off
  • 延迟检查点：PostgreSQL调大checkpoint_timeout，插完再手动触发检查点

四、并行化+硬件拉满，榨干性能 #

• 多进程并行导入：拆分数据文件成多个小文件，用多个进程同时调用导入工具，或者多个数据库连接并行执行批量INSERT，注意控制并发数，别把数据库搞崩。
• 上SSD：数据库数据目录和日志目录一定要放SSD，IO性能比HDD高N倍，这是提升插入速度的硬件关键。
• 调大连接池：给数据库开足够的连接数，让并行插入的线程能拿到连接干活，但别超过数据库最大连接限制。

五、插入后的收尾工作 #

• 重建索引和约束：插完数据后再创建索引，比插入时维护快得多，PostgreSQL可以用CREATE INDEX CONCURRENTLY避免锁表。
• 更新统计信息：执行ANALYZE test_table;（PostgreSQL）或ANALYZE TABLE test_table;（MySQL），让数据库优化器能准确生成执行计划。

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