如何对大型数据集进行分组、分桶和过滤结果集

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崩溃数天的情况下实现我想要的结果？

- chuyskywalker

Answer 1

尝试使用“EXPLAIN select...;”命令。

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

- Brian Hooper

Answer 2

在值列上创建一个索引。

create index value_index ON datasource(value)

你只需要做一次。但这会稍微减慢插入的速度。

- Cfreak