哪个更快:多个单 INSERT 或一个多行 INSERT?
我正在尝试优化将数据插入 MySQL 的代码的一部分.我应该链接 INSERT 以制作一个巨大的多行 INSERT 还是多个单独的 INSERT 更快?
I am trying to optimize one part of my code that inserts data into MySQL. Should I chain INSERTs to make one huge multiple-row INSERT or are multiple separate INSERTs faster?
推荐答案
https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html
插入一行所需的时间由以下因素决定，其中数字表示大概的比例:
The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:
- 连接:(3)
- 向服务器发送查询:(2)
- 解析查询:(2)
- 插入行:(1 × 行大小)
- 插入索引:(1 × 索引数)
- 结束:(1)
由此可见，发送一个大语句将为您节省每个插入语句 7 的开销，在进一步阅读文本时还说:
From this it should be obvious, that sending one large statement will save you an overhead of 7 per insert statement, which in further reading the text also says:
如果您同时从同一个客户端插入多行，请使用带有多个 VALUES 列表的 INSERT 语句一次插入多行.这比使用单独的单行 INSERT 语句要快得多(在某些情况下快很多倍).
If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.
相关文章