跳转到主要内容

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
逐行插入是让大型导入变慢的最快方法。MySQL 把每条单行 INSERT 都作为一次完整的事务提交来处理——解析、规划、执行、fsync。在大规模下,这种每行开销占据主导。下面介绍如何避免它。

多行 INSERT

最简单的优化:把多行合并到一条语句中。
批次大小很重要。每条语句几百到几千行通常是最优的——更大的批次会增加内存压力并延长事务持续时间。请通过基准测试找到适合您工作负载的最佳值;每批 500–2000 行是常见的起点。

事务批处理

将多条 INSERT 包在一个事务中可以避免每条语句的提交开销:
如果没有显式事务,每条 INSERT 都会自动提交。自动提交意味着每一行都单独刷到磁盘。把 500 条插入包在一个事务中只做一次刷盘。 将它与多行 INSERT 结合使用:把行分成每条语句 500–1000 行的组,再把每个批次包在一个事务中。

LOAD DATA INFILE

对于大型 CSV 导入,LOAD DATA INFILE 是最快的 MySQL 原生选项——比批量 INSERT 快得多,因为它跳过了对每一行的 SQL 解析。
该文件必须可被 MySQL 服务器进程读取。默认情况下,文件路径被限制在 secure_file_priv 指定的目录中:
要从客户端机器而不是服务器加载文件:
LOCAL INFILE 要求服务器上 local_infile = ON,并且客户端启用了 --local-infile 选项。

INSERT … SELECT

对于在表之间复制数据(ETL 模式、暂存表),INSERT...SELECT 比把行读入应用程序再插回去更快:

批量加载期间禁用索引

对于 MyISAM 表,在批量插入期间禁用非唯一索引的更新可以显著提速:
对于 InnoDB,这样做没有同样的效果。InnoDB 会自动使用”变更缓冲区”来处理二级索引更新。要获得最大的 InnoDB 批量加载性能:
加载完成后请立即重新启用这些设置。对实际存在重复的数据禁用 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 直接替代品。

另请参阅