MySQL插入行的时间太长

Question

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

my.cnf：

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

我也尝试增加缓冲区限制、缓存限制，但都没有成功。

- Sujit Tiwari

---

@Sujit：没有必要使用那种语气。我是在为你辩护而和Nick说话的。 - Lightness Races in Orbit

问题含义模糊。"太多时间"指的是什么？性能分析器显示了什么？你的数据库结构是怎样的？你插入了什么数据？使用了哪种存储引擎？ - Lightness Races in Orbit

@Sujit：在评论中仅回答一个问题而不提供问题所需的所有必要信息是没有帮助的。目前投票关闭。 - Lightness Races in Orbit

你正在处理的行数根本微不足道。使用InnoDB的MySQL可以轻松处理拥有数百万条记录的表格，没有问题。你存储的是什么数据？你一定在某个地方遇到了巨大瓶颈。 - Jords

@Sujit: 到目前为止，你所有的问题都有很多负分（除了一个得分为0）。我建议你阅读一下 Stack Overflow 的常见问题解答，并查看一些得分较高的问题，了解什么样的问题在这里被认为是“好问题”。请参考此建议修改你的提问。 - Lightness Races in Orbit

Answer 1

不要自动假设你的服务器设置有问题。默认设置可能是可以的。即使在旧机器上，插入10000行数据应该也很容易，但这取决于你如何进行插入。

这里我将描述3种插入数据的方法，从慢到快：

如果你要插入许多行数据，则以下方法非常慢：

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 文件：

1,Wouter
2,Wouter
3,Wouter

然后运行类似以下的命令

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

你使用上述方法中的哪一个？

- Wouter van Nifterick

---

+1：非常好的建议，附有良好的示例。您知道在复合插入语句中可以连接多少组值吗？ - IAbstract

INSERT INTO mytable (id, name) VALUES 我正在使用这个方法。 - Sujit Tiwari

只是为了确认一下：我不是在谈论“values”语法。你是一次发送多个值，还是为每一行发送INSERT INTO ....？ - Wouter van Nifterick

我已经修改了示例以消除混淆。Sujit，你现在能再次回答一下你看到的是什么吗：你使用第一种方法还是第二种方法？无论如何，如果是第一种方法，那么开始使用第二种方法。尝试像这样分块发送1000行左右的数据。这应该会大大提高您的导入速度。 - Wouter van Nifterick

@wounder 一次性传递多个值。我知道没有人能够解决这个问题。在发布这个问题之前，我尝试了几乎所有的替代方法，但我认为这个专家论坛中的某个人可能会帮助我。但是我得到了负面评价。非常失望。 - Sujit Tiwari

Answer 2

除了@Wouter提供的建议外，您应该检查您的索引。如果您要插入10k+行，则可能需要在插入过程之前禁用键。不要忘记在之后启用键。

参考：MySql 5.5文档：

此功能可明确激活MyISAM表。 ALTER TABLE ... DISABLE KEYS告诉MySQL停止更新非唯一索引。然后应使用ALTER TABLE ... ENABLE KEYS重新创建缺少的索引。 MySQL使用比逐个插入键快得多的特殊算法来执行此操作，因此在执行大量插入操作之前禁用键应该会加快速度。使用ALTER TABLE ... DISABLE KEYS需要INDEX特权...

- IAbstract

---

+1.. 好建议。 尽管我认为Sujit不再关注（他没有回答我的问题），并且通过查看他的其他问题，我认为他不会回到这个页面。 但我将Stack Overflow视为某种维基百科。 总有一天，有人会通过Google进入这个页面，你的答案将很有帮助。 - Wouter van Nifterick

我发现这个页面很有用！我有一个类似的问题。我需要每个用户插入/比较大约500行数据，可能每5天一次。发现这篇文章非常有用。 - Prakash Raman