MySQL 大型数据集

Question

我有大量数据。我在 MySQL 表中加载了超过 40GB。我正在尝试执行简单的查询，例如 select * from tablename 但它需要大量的时间才能运行并最终超时。如果我设置一个限制，执行速度相当快，例如:select * from tablename limit 1000。该表有超过 2 亿条记录。

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

使用这些类型的数据集有什么技巧吗？

Answer 1

您需要做的第一件事是完全忽略所有建议其他一些令人敬畏的庞然大物技术的答案和评论。这绝对是胡说八道。这些东西不能以不同的方式工作，因为它们都受限于相同的问题 - 硬件。

现在，让我们回到 MySQL。 LIMIT 的问题在于 MySQL 获取了整个 数据集，然后获取了 LIMIT 从 OFFSET 开始的行数.这意味着如果您执行 SELECT * FROM my_table LIMIT 1000 - 它将获取所有 2 亿行，缓冲它们，然后它将开始从 0 到 999 计数并丢弃其余部分。

是的，这需要时间。是的，它看起来很愚蠢。但是，MySQL 不知道“开始”或“结束”是什么意思，因此在您告诉它之前，它无法知道限制和偏移量是什么。

为了改进您的搜索，您可以使用类似这样的东西(假设您有数字主键):

SELECT * FROM tablename WHERE id < 10000 LIMIT 1000;

在这种情况下，MySQL 将处理 PK 低于 10000 的所有行，而不是 2 亿行。更容易、更快，也更易读。数字可以随时调整，如果您使用脚本语言执行某种分页，您始终可以传输出现的最后一个数字 ID，以便 MySQL 可以从该 ID 开始搜索。

此外，您应该使用 InnoDB 引擎，并使用 innodb_buffer_pool_size 对其进行调整，这是让 MySQL 飞起来的神奇调味料。