mysql插入行花费的时间太长 [英] mysql is taking too long to insert rows

问题描述

我有一个页面，用户可以在其中导入他们的联系人.最初它可以正常工作多达3000个联系人，但是当我尝试导入10000个联系人时，它开始花费了太多时间，现在的情况是，即使是100个联系人，它也花费了太多时间.我在mysql my.cnf文件中尝试过，并将最大数据包大小增加到256 mb.我的php.ini最大上传限制为512 mb，内存限制为512 mb.我尝试了几种方法来解决此问题，但没有成功.

I have one page where users can import their contacts. Initially it was working fine upto 3000 contacts but when I tried to import 10000 contacts it started taking too much time and now the situation is that for even 100 contacts it is taking too much time. I tried in mysql my.cnf file and increased the max packet size to 256 mb. My php.ini max upload limit is 512 mb, memory limit is 512 mb. I tried several methods to solve this problem but no success.

my.cnf:

[mysqld]
set-variable = max_connections=500000
log-slow-queries
safe-show-database
local-infile=0
max_allowed_packet=256M

我还尝试增加缓冲区限制，缓存限制，但那里也没有成功.

I also tried to increase buffer limit, cache limit but no success there either.

推荐答案

不要自动认为您的服务器设置有误.默认设置可能很好.即使在旧机器上，插入10000行也应该是小菜一碟，但这取决于您如何进行插入.

Don't automatically assume that your server settings are wrong. The default settings are probably fine. Inserting 10000 rows should be a piece of cake, even on an old machine, but it depends on how you do your inserts.

在这里，我将介绍3种插入数据的方法，从慢到快:

Here I'll describe 3 methods for inserting data, ranging from slow to fast:

如果要插入许多行，则以下操作非常慢:

The following is extremely slow if you have many rows to insert:

INSERT INTO mytable (id,name) VALUES (1,'Wouter');
INSERT INTO mytable (id,name) VALUES (2,'Wouter');
INSERT INTO mytable (id,name) VALUES (3,'Wouter');

这已经快得多了:

INSERT INTO mytable (id, name) VALUES
  (1, 'Wouter'),
  (2, 'Wouter'),
  (3, 'Wouter');

(语法错误)

通常这是最快的:

具有如下所示的CSV文件:

Have CSV file that looks like this:

1,Wouter
2,Wouter
3,Wouter

然后运行类似

LOAD DATA FROM INFILE 'c:/temp.csv' INTO TABLE mytable

您使用哪种上述方法?

这篇关于mysql插入行花费的时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！