mysql如何更快地插入数百万条记录? [英] How can mysql insert millions records faster?

问题描述

我想在数据库中插入约数百万条记录，但是它的速度非常慢，速度约为每小时40,000条记录，我不认为我的硬件速度太慢，因为我发现diskio的速度低于2 MiB/s.我有许多表分开在不同的.sql文件中.一条记录也很简单，一条记录少于15列，而一列少于30个字符.我在archlinux下使用mysql 5.3完成了这项工作.你们有什么主意吗?还是这个速度不慢?

I wanted to insert about millions records into my database, but it went very slow with a speed about 40,000 records/hour, I dont think that my hardware is too slow, because i saw the diskio is under 2 MiB/s. I have many tables seperated in different .sql-files. One single record is also very simple, one record has less than 15 columns and one column has less than 30 characters. I did this job under archlinux with mysql 5.3. Do you guys have any ideas? Or is this speed not slow?

推荐答案

最可能是因为您要插入这样的记录:

It's most likely because you're inserting records like this:

INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");

每次需要INSERT时都发送一个新查询会降低性能.而是将这些查询合并为一个查询，像这样.

Sending a new query each time you need to INSERT something is bad for performance. Instead combine those queries into a single query, like this.

INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2"),
                                                 ("data1", "data2"),
                                                 ("data1", "data2"),
                                                 ("data1", "data2"),
                                                 ("data1", "data2");

您还可以在 MySQL中阅读有关插入速度的更多信息.文档.它清楚地描述了以下内容.

You can also read more about insert speed in the MySQL Docs. It clearly describs the following.

要优化插入速度，请将许多小操作合并为一个大操作.理想情况下，您进行单个连接，一次发送许多新行的数据，然后将所有索引更新和一致性检查延迟到最后.

To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.

如果数量巨大，当然不要将它们全部合并.假设您需要插入1000行，那么不要一次执行一次.但是，您可能不应该同样尝试在单个查询中包含所有1000行.而是将其分成较小的尺寸.

Of course don't combine ALL of them, if the amount is HUGE. Say you have 1000 rows you need to insert, then don't do it one at a time. But you probably shouldn't equally try to have all 1000 rows in a single query. Instead break it into smaller sizes.

如果仍然很慢，则可能是因为您的服务器很慢.

If it's still really slow, then it might just be because your server is slow.

请注意，您当然不需要组合查询中的所有空格，只是为了更好地了解答案.

这篇关于mysql如何更快地插入数百万条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！