MySQL 大型数据集

Question

我有一大批数据，超过40GB，已加载到MySQL表中。我试图执行简单的查询，例如select * from tablename，但运行时间太长，最终会超时。如果设置限制，则执行相对较快，例如：select * from tablename limit 1000。该表具有超过2亿条记录。

尝试在某些列上创建索引，但即使执行了3个小时也失败了。

如何处理这些类型的数据集的任何提示？

- xoail

---

你可以检查服务器上MySQL正在消耗的资源。你必须检查硬件...也许根据你的硬件可能性，你可以尝试另一种方法，比如Big Query。 - Emilio Gort

@EmilioGort - 什么？Big Query使用了一些“特殊”的方法，使得读取文件的相同代码变得神奇地更快了吗？ - N.B.

是的，它有这个功能...你应该试试。 - Emilio Gort

Answer 1

你需要做的第一件事是完全忽略所有建议使用其他了不起的、无稽之谈的技术的答案和评论。这些都是胡说八道。它们不能以不同的方式工作，因为它们都受到相同问题-硬件的限制。

现在，让我们回到MySQL。 LIMIT 的问题在于 MySQL 获取整个数据集，然后从 OFFSET 处开始取出 LIMIT 行数据。也就是说，如果你执行 SELECT * FROM my_table LIMIT 1000 - 它将获取所有的 2 亿行数据，缓存它们，然后从 0 到 999 开始计数并丢弃其余的。

是的，这需要时间。是的，看起来很愚蠢。然而，MySQL 不知道什么是“开始”或“结束”，所以除非你告诉它，否则它不知道 limit 和 offset 是什么。

为了改进你的搜索，你可以使用像这样的语句（假设你有一个数字主键）：

SELECT * FROM tablename WHERE id < 10000 LIMIT 1000;

在这种情况下，MySQL将仅处理主键低于10000的所有行，而不是200万行。更容易，更快速，也更可读。可以随时调整数字，如果您在脚本语言中执行某种分页，则始终可以传输最后一个存在的数字ID，以便MySQL可以从该ID开始进行搜索。

此外，您应该使用 InnoDB 引擎，并使用 innodb_buffer_pool_size 进行调整，这是让MySQL飞快运行的关键。

- N.B.

---

谢谢，我正在使用InnoDB引擎。选择1000条记录对我来说没问题。问题出现在我尝试选择全部或选择不同或任何形式的计数时。有什么提示吗？ - xoail

你有2亿条记录。无论你做什么，都需要时间，从磁盘读取和查找数据（特别是对于不同的查询）到在网络上传输数据。问题是，为什么你需要选择所有2亿条记录呢？ - N.B.

MySQL的速度较慢是因为它需要从磁盘中检索数据，这是一种较慢的操作，与从内存中读取数据相比，在像Spark这样的大数据解决方案中尤为明显。@xoail - bman

Answer 2

对于大型数据库，应该考虑使用替代方案，例如Apache Spark。MySQL从磁盘读取数据，这是一项缓慢的操作。没有什么能像基于MapReduce技术的技术那样快速。请查看此答案。事实上，对于大型数据库，查询变得非常具有挑战性。

无论如何，假设您想坚持使用MySQL，首先，如果您正在使用MyISAM，请确保将数据库存储转换为InnoDB。如果您有大量的读/写操作，则尤其重要。

分区也很重要，可以将表分成更易管理的小表。它还可以提高索引性能。

不要过于慷慨地添加索引。明智地定义索引。如果一个索引不需要是UNIQUE，则不要将其定义为一个。如果一个索引不需要包含多个字段，则不要包含多个字段。

最重要的是开始监视您的MySQL实例。使用SHOW ENGINE INNODB STATUS来调查MySQL实例的性能。

- bman