哪个更快:多个单插入还是一个多行插入? [英] Which is faster: multiple single INSERTs or one multiple-row 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.

这篇关于哪个更快:多个单插入还是一个多行插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！