MySQL 在排序期间耗尽 1.5TB 的磁盘空间

Question

我有一个大约有 14 亿条记录的表，格式如下:

mysql> describe _2009all;+---------------+--------------+------+-----+---------+-------+| Field         | Type         | Null | Key | Default | Extra |+---------------+--------------+------+-----+---------+-------+| userId        | int(11)      | YES  | MUL | NULL    |       || type          | varchar(50)  | YES  |     | NULL    |       || kind          | varchar(50)  | YES  |     | NULL    |       || description   | varchar(255) | YES  |     | NULL    |       || bundleVersion | varchar(255) | YES  |     | NULL    |       || bundleId      | varchar(255) | YES  |     | NULL    |       || time          | bigint(20)   | YES  |     | NULL    |       |+---------------+--------------+------+-----+---------+-------+7 rows in set (0.02 sec)

整个数据库占用不到 0.4 TB，我有大约 1.5 TB 的可用磁盘空间。

我正在尝试准备数据以供分析，我会查看每个用户在一段时间内所做的事情。所以我运行以下语句:

create table sorted2009 AS (select * from _2009all order by userid,time);

该语句(显然)需要很长时间才能运行，但在某些时候，它会耗尽所有可用磁盘空间并且我收到“错误 3 (HY000):写入文件时出错”错误。

关于如何创建排序表的任何想法？提前致谢。

澄清

Martin:只有 1 个分区。

Andy:我要解决的问题是“select * from _2009all order by userid,time”的结果是我将在接下来的几个月里经常检查的内容。因此，我不想在每次要检查结果时都运行此查询，而是想将结果放在一个表中以便更快地访问。

我最初认为随着时间的推移建立索引会浪费空间，因为时间戳大多是唯一的。但是我只是给_2009all中的time字段加了一个索引，索引占用的空间还是比较合理的。但是现在要对两个索引进行排序(我假设您所说的“索引遍历”是什么意思):

mysql> select * from _2009all order by userId,time limit 2;...2 rows in set (25 min 36.48 sec)

是的，我认为 25 分钟太长了。但是，如果您的建议另有所指，我愿意接受其他选择。

bot43:

将索引添加到时间字段后:

mysql> explain select * from _2009all order by userid,time;+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows       | Extra          |+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+|  1 | SIMPLE      | _2009all | ALL  | NULL          | NULL | NULL    | NULL | 1384378798 | Using filesort |+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+1 row in set (0.05 sec)mysql> explain select userId,type,kind,description,bundleVersion,bundleId,time from _2009all order by userid,time+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows       | Extra          |+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+|  1 | SIMPLE      | _2009all | ALL  | NULL          | NULL | NULL    | NULL | 1384378798 | Using filesort |+----+-------------+----------+------+---------------+------+---------+------+------------+----------------+1 row in set (0.00 sec)

关于 DBMS 以任何该死的方式存储表的好处。我想我需要另一种解决方案。

Answer 1

你的表上有哪些索引？

你不能放一个composite index吗？在两列上？

ALTER TABLE `_2009all` ADD INDEX ( `userId` , `time` ) ;