mysql - 查询的结果集太大

Question

我有一个查询，它可以快也可以慢，具体取决于我获取的记录数。下表显示了我的 LIMIT 子句中的数字以及执行查询和获取结果所需的相应时间:

LIMIT | Seconds (Duration/Fetch)
------+-------------------------
   10 |  0.030/  0.0
  100 |  0.062/  0.0
 1000 |  1.700/  0.8
10000 | 25.000/100.0

如您所见，达到至少 1,000 时没问题，但达到 10,000 时确实很慢，主要是因为获取时间较长。我不明白为什么获取时间的增长不是线性的，但我从 70 多个表中获取了 200 多列，所以结果集获取时间很长的事实不是一个惊喜。

顺便说一下，我要获取的是某家银行所有账户的数据。我正在处理的银行有大约 160,000 个帐户，因此我最终需要从数据库中获取 160,000 行。

尝试一次获取 160,000 行显然不可行(至少不可行，除非我能以某种方式显着优化我的查询)。在我看来，我可以合理获取的最大块是 1,000 行，因此我编写了一个脚本，该脚本将使用 SELECT INTO OUTFILE、限制和偏移反复运行查询。然后，最后，我将我转储的所有 CSV 文件放在一起 cat。它有效，但速度很慢。这需要几个小时。我现在正在运行脚本，它在大约一个小时内只转储了 43,000 行。

我应该在查询优化级别解决这个问题，还是较长的提取时间表明我应该关注其他地方？你会推荐我做什么？

想看查询可以看here .

Answer 1

答案在很大程度上取决于您对数据的处理方式。对于非平凡的记录大小，通过 29 个连接查询 215 列永远不会很快。

如果您尝试向用户显示 160,000 条记录，您应该对结果进行分页并且一次只获取一页。这将使结果集保持足够小，即使是相对低效的查询也能快速返回。在这种情况下，您还需要检查用户需要多少数据才能选择或操作数据。很有可能您可以将其缩减为少数几个字段和一些聚合(计数、总和等)，这将使用户能够就他们想要使用哪些记录做出明智的决定。使用带有偏移量的 LIMIT 来拉取任意大小的单个页面。

如果您需要导出数据用于报告目的，请确保您只提取报告所需的确切数据。尽可能消除连接，并在需要子数据聚合的地方使用子查询。您需要为常用的连接和条件调整/添加索引。对于您提供的查询，ib.id 和您要加入的无数外键。您可以不使用 bool 列，因为没有足够的不同值来形成有意义的索引。

无论您想要完成什么，删除一些连接和列本身都会加快您的处理速度。 MySQL 需要执行大量繁重的工作来填充该查询是您的主要绊脚石。