mysql - 如何提高 mysql 中大量插入的性能？

Question

我正在研究提高 mysql 插入性能的方法。

关于这个场景，在忙碌的一天，每 3 秒大表大约 3000 行，中表另外 100 行，小表大约 30 行并发。这必须持续 24 小时，然后我选择只有 30.000-40.000 行的重要部分，然后我刷新所有 3 个表。

所以，我使用的是 mysql 5.5.29。所有表都使用innodb，很少有索引加主键。

关于这个问题，我看到一条关于对这些插入使用内存引擎表然后将它们移动到主表的评论:

“使用 LOCK TABLE .. WRITE 可能会加快插入速度，但是在处理和加载行时，您将锁定任何读取器。选择主键(应该接近 0 秒)最多需要 0.2当我的加载程序忙于加载 1000 行的批处理时的秒数。

一个非常好的解决方法是将新行放入一个临时表中，然后不时地在单个事务中刷新该表。这使得插入非常快，因为所有检查和转换都已在临时表中完成。

CREATE TEMPORARY TABLE ..._temp (....) ENGINE=MEMORY;

-- Loop from here --
DELETE FROM .._temp;
INSERT INTO .._temp ... VALUES (...);
INSERT INTO .._temp ... VALUES (...);

LOCK TABLE ... WRITE, ..._temp READ;
INSERT INTO ... SELECT * FROM ..._temp;
UNLOCK TABLES;
-- Repeat until done --

DROP TABLE ..._temp;

我在我的系统中看到的是，INSERT 的运行速度与锁定表时的速度大致相同，但客户端在加载程序运行时根本不会注意到任何性能损失。”

Source

在那种情况下使用内存引擎有意义吗？我应该在需要访问行的情况下使用 innodb 来避免表锁，还是我应该更喜欢 myisam 以提高速度？

Answer 1

如果您需要每天删除表，但需要先从表中取出数据。最好的办法是用一个新的空表快速替换现有的完整表，然后在完整表上执行您需要的任何数据操作。以下是您的操作方法。

首先创建一个表，就像您要操作的表一样:

CREATE TABLE big_table_temp LIKE big_table;

您现在有一个与大表具有相同架构的空表。

然后重命名表以将它们换出:

RENAME TABLE big_table TO big_table_archive, big_table_temp TO big_table;

这样做的好处是这个操作基本上是瞬时的，因为 MySQL 只是重命名磁盘上的二进制数据库文件。确保像这样在一个查询中同时进行重命名操作，因为您希望整个操作成功或失败。

然后您可以根据需要对 big_table_archive 进行操作，从中提取数据，而不必担心阻止对 big_table 的实时查询。