InnoDB表大容量插入

Question

我有一个包含大约5亿行的MySQL数据表。我们需要通过读取这些数据来运行计算，并且需要将计算出的数据(原始数据的标准化形式)写入另一个InnoDB表中。

我们目前的设置是一个虚拟云，其中有一台机器和数据库，因此机器与数据库的连接非常快。

对数据进行计算(以及读取数据)的速度非常快，整个过程的瓶颈是将标准化数据插入到InnoDB表中(标准化数据包含一些索引，但不是很长，这会减慢插入速度)。

不幸的是，我们不能修改某些系统变量，比如innodb_log_file_size (我们使用的是Amazon AWS)，这将有助于提高插入性能。

将所有这些数据推送到MySQL的最佳方案是什么？由于计算过程很简单，我几乎可以编写一个Python脚本来获取标准化数据并以任何格式输出它。在计算发生时即时插入这些数据是非常缓慢的，而且随着时间的推移变得越来越慢。

我猜问题是，将批量数据插入InnoDB表的最佳过程是什么(就输入格式和实际导入而言)？

Answer 1

我的第一个直觉是让你调整你的缓冲变量..但正如您所说的，您不能更改太多服务器配置参数，这里有另一个选择……

执行计算并将输出转储到csv中。为此，您可以使用'SELECT ... INTO OUTFILE‘命令。然后连接到目标CSV，执行“set autocommit=0”，然后执行“load data local infile”将这个CSV加载回目标表。最后，将自动提交重新设置为1。

我还可以建议许多其他选项(如右分区模式、主键顺序插入等)，但我需要知道数据库的结构、传入的数据集和索引。

Answer 2

在本例中，由于您没有在基表上执行任何操作-并且最有可能的是更新辅助innodb表中的数据，因此我建议您执行以下步骤

1. 使用 --where (--where "id>91919“或--where "update_time > now() - interval 1 hour ")选项获取mysqldump。如果可能，避免锁定表
2. 将数据恢复到临时数据库表
3. 在临时数据库上执行计算并更新辅助表
4. 删除创建的临时数据库/表。

Answer 3

你的是时间序列数据吗？上周也有类似的问题。加载分区，它变得更快。我也在http://www.ajaydivakaran.com/2013/03/12/mysql-innodb-when-inserts-start-slowing-down/上优化了我的设置，但如果你不能优化，那就使用分区来更快地插入。