mysql - 如何对非常大的数据集的结果集进行分组、存储和过滤

Question

抱歉，问题标题有些含糊，所以这是一个有效的例子。

我有一个表，每个用户 (userid) 每隔几天都会在其中获取一个值。我想找到每个用户的最后一个值，按月分割，并将他们的数量计入一个范围。

这是一个示例表和代表性数据:

CREATE TABLE `datasource` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `userId` INT UNSIGNED NOT NULL ,
    `unixts` INT UNSIGNED NOT NULL ,
    `value` INT UNSIGNED NOT NULL ,
    INDEX ( `userId` )
);

INSERT INTO `datasource` 
    (`userId`, `unixts`, `value`)
VALUES 
    (1, UNIX_TIMESTAMP('2010-07-01'), 500),
    (1, UNIX_TIMESTAMP('2010-07-15'), 610),
    (1, UNIX_TIMESTAMP('2010-08-02'), 740),

    (2, UNIX_TIMESTAMP('2010-07-03'), 506),
    (2, UNIX_TIMESTAMP('2010-07-18'), 640),
    (2, UNIX_TIMESTAMP('2010-08-09'), 340),

    (3, UNIX_TIMESTAMP('2010-07-03'), 506),
    (3, UNIX_TIMESTAMP('2010-08-18'), 640)
;

现在，这是一个获取我所追求的结果的查询:

select
    month(FROM_UNIXTIME(unixts)) as month,
    sum( if( value >= 700, 1, 0) ) as '700 and up',
    sum( if( value BETWEEN 600 AND 699, 1, 0) ) as '600-699',
    sum( if( value BETWEEN 500 AND 599, 1, 0) ) as '500-599',
    sum( if( value <= 499, 1, 0) ) as '499 and below',
    count(*) as total
from
    datasource
where
    id in (
        select 
            max(id)
        from 
            datasource 
        where 
            unixts between UNIX_TIMESTAMP('2010-07-01') and UNIX_TIMESTAMP('2010-09-01')
        group by 
            userId, month(from_unixtime(unixts))
    )
group by
    month(FROM_UNIXTIME(unixts));

+-------+------------+---------+---------+---------------+-------+
| month | 700 and up | 600-699 | 500-599 | 499 and below | total |
+-------+------------+---------+---------+---------------+-------+
|     7 |          0 |       2 |       1 |             0 |     3 |
|     8 |          1 |       1 |       0 |             1 |     3 |
+-------+------------+---------+---------+---------------+-------+

此查询非常适合我们的小结果集。但是，如果您将 4400 万行扔到数据源表中，它就会陷入停顿。

是否有一种优化的方式来编写此查询，可以实现我想要的结果，而无需将 mysql 停用几天？

Answer 1

尝试解释选择...;

这将告诉您查询是如何工作的。然后，您可以查看是否出于任何原因正在进行全表扫描，并采取措施进行更正。这可能包括 Cfreak 提出的建议。或者，在此处发布结果，我们将看看我们能做些什么。