有1.5亿行的MySQL数据库的性能问题

Question

当前设置：

• 托管vps服务器
• 大表的InnoDB存储
• 10.0.36-MariaDB
• 18 ram冲压
• 8×2.40 GHz CPU。

我有一个相当大的MariaDB数据库表，用于商业智能分析数据。当前表数据在1.5-1.8亿行之间波动，这取决于正在导入的数据。

数据库通过自定义编写的java文件填充，数据通过使用表索引的方法通过自定义PHP读取。数据是日期分开的，目前该表保存了大约2-3年的数据。

我的问题是，每当我们看到数据出现错误时，我们通常需要重新导入大量的数据，这可以追溯到很长一段时间。这意味着java文件正在消耗成千上万个报表文件，并一个接一个地导入。

我已经对导入过程进行了一些速度优化，一个报告不会花很长时间，除非报告是300 10左右--然后根据数据库的繁忙程度，大约需要10-15分钟。

每日报告导入通常有200-400个文件，当办公室里没有人的时候，这需要一个小时的时间。所有这些都是由cron作业和自定义报告运行的，它们从各种来源下载脚本。

每天的导入和数据获取是相当快的--然而，当我们想要将数据提取到一个度量图中每天都可见的位置时，如果多个人试图这样做，或者如果要导入一些大的东西，则可能需要10-15分钟或更长的时间。

正在获取的数据不是一个查询，我每天将数据作为一个单独的请求分开--根据时间周期的长短，这会加快抓取10次或更多次。

在一个完美的世界中，我想优化结构或任何可能的设置，这样我就可以更快地导入数据，更快地获取更长的时间框架数据。我们目前的方法是为分析数据分离一个数据库，并有几个辅助表。

知道我们每月增长500万到800万行(这可能会增加)，我能对这么多的数据做什么样的优化呢？

Answer 1

• INSERTing一次一行可以通过收集100行并将它们插入到一个批处理中来加速：INSERT INTO t (a,b,c) VALUES (1,2,3), (11,22,33), ...，这可能会使事情的速度提高10倍。
• 检查每个批次后是否有错误。如果批处理失败，将其写入文件以进行修复和重新导入。(这将改进错误处理传递。)
• 假设数据只是“新”数据，那么就应该很容易地总结每天的数据并将其写入新的表中。这可能会加快抓取10倍的年数据来绘制图形。
• 每天的200-400文件可能由多个进程处理。建议不要将其分解为比CPU更多的进程。

将所有这些组合在一起可能会将经过的时间从小时缩短到分钟。

如果您想进一步讨论问题，请提供SHOW CREATE TABLE，或者更多关于数据及其处理的细节。innodb_buffer_pool_size的价值是什么？

Answer 2

您可以在导入数据之前执行几种优化技术，这些技术可能会减少整个处理时间，例如暂时禁用索引或对表进行分区。但是，所有这些解决方案可能无法解决一个大问题，即当数据进一步增长时，您需要升级服务器和硬件基础设施时会发生什么？记住这一点，任何其他云数据库解决方案(如SingleStore )都将有助于满足您的需求，因为它提供的服务是按需付费的模式，并且比MySQL更快。此外，您不需要担心扩展，因为它自己处理它。