优化MySQL插入以处理数据流

Question

我正在使用高速率数据流，并执行以下步骤将数据存储在MySQL数据库中。对于每一个新到达的物品。

• (1)分析收到的物品。
• (2)执行几个“插入.关于重复密钥更新”

我使用插入..。关于重复密钥更新消除了对数据库的另一次往返。

在试图提高整体性能的同时，我考虑以以下方式进行批量更新：

• (1)分析收到的物品。
• (2)生成带有“插入. ON重复键更新”的SQL语句并追加到文件中。

定期将文件中的SQL语句刷新到数据库。

两个问题：

• (1)这会否对资料库的负荷产生正面影响？
• (2)如何将语句刷新到数据库，以便在完成刷新后才能重建索引？(使用交易？)

更新:我使用的是Perl DBI + MySQL MyISAM。

谢谢您的评论。

Answer 1

您没有说明什么样的数据库访问环境(PERL )？JDBC？ODBC?)您正在运行，或者是哪种表存储引擎(MyISAM )？InnoDB?)你在吸毒。

首先你选择插入是对的..。在重复键更新时。很好的移动，除非你能保证唯一的钥匙。

其次，如果数据库访问环境允许，则应该使用准备好的语句。如果将一堆语句写入文件中，然后再让数据库客户端再次读取该文件，则肯定无法获得良好的性能。直接从使用传入数据流的软件包执行插入操作。

第三，选择合适的表存储引擎。MyISAM插入将比InnoDB更快，所以如果您要记录数据并稍后检索它，这将是一个胜利。但是InnoDB具有更好的事务完整性。如果您确实在处理大量的数据，并且不需要经常阅读，请考虑归档存储引擎。

最后，考虑在一批INSERT开始时执行一个START事务.命令，然后在固定行数(如100行左右)之后执行COMMIT和另一个开始事务。如果您使用的是InnoDB，这将大大加快速度。如果您使用的是MyISAM或归档文件，那就无关紧要了。

你的大赢家将来自准备好的陈述材料和存储引擎的最佳选择。

Answer 2

如果您的数据不需要立即进入数据库，您可以在某个地方缓存插入数据，然后发出一个更大的insert语句。

插入table_name (x，y，z)值(x1，y1，z1)，(x2，y2，z2)，(xN，yN，zN)重复更新.

为了明确起见，我将维护一个挂起的插入列表。在这种情况下，一个(x，z，y)三重子的列表。然后，一旦列表超过某个阈值(N)，就生成insert语句并发出它。

我没有精确的计时数据，但是与单独插入每一行相比，这提高了大约10倍的性能。

我也没有玩过N的值，但我发现1000可以很好地工作。我希望最优值会受到硬件和数据库设置的影响。

希望这有帮助(我也在使用MyIsam)。