MySQL插入查询优化

Question

MySQL插入查询优化

5

以下两种方法哪一种能更快地向表中插入大量行。

查询方法1：逐个执行查询。

INSERT INTO tbl_user(id, name, number) VALUES(NULL, 'A', '9999999999');
INSERT INTO tbl_user(id, name, number) VALUES(NULL, 'B', '9999999999');
INSERT INTO tbl_user(id, name, number) VALUES(NULL, 'C', '9999999999');

查询方法2：立即执行查询。

INSERT INTO tbl_user(id, name, number) VALUES(NULL, 'A', '9999999999'),
                                             (NULL, 'B', '9999999999'), 
                                             (NULL, 'C', '9999999999');

- Vivek Pipaliya

选择方法二，因为它只执行一次。 - Dave

方法2比第一个快。 - Gulmuhammad Akbari

这里展示了有多少人不知道发生了什么。你想要逐个执行。你永远不想选择选项2。为什么？因为如果你将第一个方法包装在事务块中并使用预处理语句 - 你永远不会因为max_packet_size而遇到错误。选项2也更加耗费资源来解析。你总是想要避免它，如果出现任何问题，调试也更加困难。因此 - 选项#3是最快的。在开头添加BEGIN TRANSACTION，在结尾添加COMMIT。祝好运。 - N.B.

3个回答

0

第二种方法（查询）比第一种方法更快。

因为在第一种方法中，它在表上执行三个不同的查询，而在第二种方法中，它只执行一次以将多个记录插入表中。

当您一次插入数百行时，您将看到主要差异。

- Alok Patel

0

第二个查询比第一个查询快得多。根据文档，在单个语句中执行多个插入的性能提高因素包括：

9.2.2.1 INSERT语句的速度

为了优化插入速度，将许多小操作合并为一个大操作。理想情况下，您只需建立一个连接，一次发送许多新行的数据，并延迟所有索引更新和一致性检查，直到最后。

插入一行所需的时间由以下因素确定，其中数字表示近似比例：

连接：（3）

将查询发送到服务器：（2）

解析查询：（2）

插入行：（1×行大小）

插入索引：（1×索引数）

关闭：（1）

如果您要同时从同一客户端插入多行数据，请使用带有多个VALUES列表的INSERT语句一次性插入多行。这比使用单独的单行INSERT语句快得多（在某些情况下快得多）。如果您要向非空表添加数据，则可以调整bulk_insert_buffer_size变量以使数据插入更快。

- Naruto

第二种方法根本不会更快。你只提供了一半的信息。如果有的话，它是更慢的。你引用的文件有一个有趣的观点-“为了优化插入速度，将许多小操作合并为单个大操作”-我们通过使用事务来做到这一点，而不是发送一个庞大的文本块给MySQL解析。我不会投票支持你的答案，因为它完全误导人。 - N.B.

那是一个优化的通用事项。请阅读我在回答中添加的下面一段话，不要将其视为引用。这又来自我添加的同一文档：“如果您同时从同一客户端插入许多行，请使用带有多个VALUES列表的INSERT语句一次性插入多行。这比使用单独的单行INSERT语句快得多（在某些情况下快得多）。" - Naruto

你认为发送一个512mb的字符串让MySQL解析比发送要插入的值更快，而这些值被包装在事务中吗？你知道事务对HDD I/O有什么作用吗？ - N.B.

非唯一索引是“延迟”的（通过“更改缓冲区”），因此它们可能比上述公式所暗示的影响要小。当然，这些索引最终仍需要更新。 - Rick James

@N.B. - 有些设置可能会导致512MB崩溃。将批处理限制在约1MB左右。 - Rick James

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- e4c5 · Accepted Answer

考虑到有几个参数，我认为我应该尝试一下基准测试，但首先

 CREATE TABLE `tbl_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

我可以用两行Python代码生成与问题中形式相同的SQL查询。

情景1：
许多单个插入语句，每个查询都完全相同。

INSERT INTO tbl_user VALUES(NULL,'A','9999999');
INSERT INTO tbl_user VALUES(NULL,'A','9999999');

1000行; 三次执行的平均运行时间为45.80秒
5000行; 单次运行220秒

情境2：
插入1000行的单个查询如下所示：

INSERT INTO tbl_user VALUES(NULL,'A','9999999'),
(NULL,'A','9999999'),
(NULL,'A','9999999'),
(NULL,'A','9999999'),

1000行数据的三次执行平均运行时间为0.17秒。
5000行数据的三次执行平均运行时间为0.48秒。
10000行数据的三次执行平均运行时间为1.06秒。

场景3：
与场景1类似，但在插入语句周围添加了START TRANSACTION和COMMIT。

1000行数据的三次执行平均运行时间为0.16秒。
5000行数据的三次执行平均运行时间为0.48秒。
10000行数据的三次执行平均运行时间为0.91秒。

结论：
场景2，即其他两个答案提出的方案，确实表现优异，比场景1好得多。根据这些数据，很难在2和3之间做出选择。需要进行更加严格的测试，尤其是在插入更大量的数据时。但如果没有这些信息，我可能会选择场景3，原因是解析非常大的字符串通常会有开销，构建也是如此。我猜想，如果我们尝试在单个语句中一次性插入约50,000条记录，实际上可能会慢得多。