mysql - 排序非常大的mysql表并写入文件

Question

我有一个非常大的表，我正在尝试使用查询对其进行排序并写入文件:

 select * from t1 order by user_id,tstamp into outfile 'sample.tsv';

该表非常大(大约 600-7 亿行)并且在磁盘上使用了 ~180GB，尝试运行查询时出现“设备上没有剩余空间”错误，显然写入了这么多数据到 tmp 目录，它正在最大化 HDD 并中断查询。

我已将包含 tmp 文件夹的驱动器上的可用空间最大化(接近 1TB 可用空间!)，但查询仍然失败并出现相同的错误，因此除了在 tmp 目录中创建更多空间之外，我需要一个解决方案。

到目前为止，我想到的一个想法是迭代地分解排序，利用这样一个事实，即对于 56000 左右的用户 ID，平均只有 10-20000 条记录。使用一些伪代码，我想这看起来像这样:

for each unique uid:
    select * from t1 where user_id=uid order by tstamp
    append result to file

我假设这会起作用(使用 Python MySQL 包装器或类似工具)但是有没有更简单、纯 MySQL 的解决方案？

Answer 1

我不确定是否有比您通过用户 ID 提取的方法更好的方法。您可能想要使用更大的 block 。只要数据适合内存，排序就应该合理快速。

虽然对所有数据进行排序可能不可行，但您可以在 user_id 上创建索引。这样的索引会加快您的查询速度。如果没有索引，每次迭代都需要全表扫描。

在 user_id, tstamp 上建立索引可能不会帮助查询(即使可能)。问题是数据不适合内存。即使你有这样一个索引，当你去使用它时，你最终也会遇到一种叫做“颠簸”的情况。查询将开始读取索引。每条记录都在单独的页面上(非常接近)。很快，页面缓存就填满了内存。然后引擎查看索引中的下一条记录。它几乎肯定不在页面缓存中，因此它必须从磁盘读取页面。这种情况还在继续。每个记录读取都需要额外命中磁盘。

这个问题的解决方案是合并排序算法。在某些情况下，在数据库外排序更可行。但是 180 GB 的文件仍然很大。