哪个更快:多个单个INSERT或一个多行INSERT? [英] 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?

推荐答案

插入行所需的时间由以下因素决定，其中数字表示大致比例:

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)

• Connecting: (3)
• Sending query to server: (2)
• Parsing query: (2)
• Inserting row: (1 × size of row)
• Inserting indexes: (1 × number of indexes)
• Closing: (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.

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