VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
INSERT 都作为一次完整的事务提交来处理——解析、规划、执行、fsync。在大规模下,这种每行开销占据主导。下面介绍如何避免它。
多行 INSERT
最简单的优化:把多行合并到一条语句中。事务批处理
将多条 INSERT 包在一个事务中可以避免每条语句的提交开销:LOAD DATA INFILE
对于大型 CSV 导入,LOAD DATA INFILE 是最快的 MySQL 原生选项——比批量 INSERT 快得多,因为它跳过了对每一行的 SQL 解析。
secure_file_priv 指定的目录中:
LOCAL INFILE 要求服务器上 local_infile = ON,并且客户端启用了 --local-infile 选项。
INSERT … SELECT
对于在表之间复制数据(ETL 模式、暂存表),INSERT...SELECT 比把行读入应用程序再插回去更快:
批量加载期间禁用索引
对于 MyISAM 表,在批量插入期间禁用非唯一索引的更新可以显著提速:unique_checks 会产生损坏的索引。
性能比较
具体数字在很大程度上取决于行大小、索引、硬件和配置。请对您的具体情况进行基准测试。
常见问题
每个 INSERT 批次应该多大?
从 500–1000 行开始并进行基准测试。更大的批次减少每次提交的开销,但会增加内存用量和事务持续时间(持有锁的时间更长)。如果一个批次失败,整个批次都会回滚——更小的批次意味着更少的回滚工作。大多数应用程序发现最佳批次大小在 500 到 5000 行之间。我可以将 LOAD DATA INFILE 与 ON DUPLICATE KEY UPDATE 一起使用吗?
LOAD DATA 支持用 REPLACE 或 IGNORE 处理重复,但不支持 ON DUPLICATE KEY UPDATE。要在批量加载期间获得 upsert 语义,请先加载到暂存表,然后从暂存表向目标表使用 INSERT...SELECT...ON DUPLICATE KEY UPDATE。
批量插入会绕过触发器吗?
不会。触发器会对每个受影响的行触发,包括由LOAD DATA INFILE 和 INSERT...SELECT 加载的行。如果您的触发器做大量工作,它会按比例拖慢批量加载。对于纯数据迁移,您可能需要临时禁用触发器。
故障排除
这些加载技术在 VillageSQL 上无需改动即可使用,它是带扩展的 MySQL 直接替代品。
另请参阅
- MySQL 中的事务——在事务中批处理批量插入以兼顾安全和速度
- MySQL 中的模式迁移——批量插入常常是数据迁移工作的一部分
- MySQL 中的 UPSERT——处理加载期间的重复键
- MySQL 插入多行——多行语句本身
- MySQL INSERT SELECT——复制查询返回的行