mysql - 一个mysql查询的优化

Question

我正在使用 MySQL 并有一个像这样的表 user_data:

user_id         int(10) unsigned
reg_date        int(10) unsigned
carrier         char(1)

reg_data是注册时间的unix时间戳(可以是一天中的任何一秒)，carrier是运营商的类型，可能的值只能是'D'，'A'或' V'.

我需要写一个sql语句来选择2013/01/01到2013/01/31每天不同运营商的注册用户数。所以理想的结果可能是:

2013/01/01   D   10
2013/01/01   A   31
2013/01/01   V   24
2013/01/02   D    9
2013/01/02   A   23
2013/01/02   V   14
....
2013/01/31   D   11
2013/01/31   A   34
2013/01/31   V   22

谁能帮我解答这个问题？我需要给出最佳答案，这意味着我可以在必要时添加索引，但我需要保持查询高效。

目前，我在 (reg_date, carrier) 上创建了一个索引并使用以下查询:

select FROM_UNIXTIME(reg_date, "%M %D %Y") as reg_day, carrier, count(carrier) as user_count
from user_data
where reg_date >= UNIX_TIMESTAMP('2013-01-01 00:00:00') and reg_date < UNIX_TIMESTAMP('2013-02-01 00:00:00')
group by reg_day, carrier
order by reg_date;

谢谢!

Answer 1

如果你不能改变表(存储单独的日期会有点帮助)，只有索引，那么:

创建复合索引:carrier, reg_date，然后将 carrier, reg_date 分组并按 reg_date, carrier 排序。

您可以仅为时间戳创建其他索引(它可能更适用于 WHERE 语句，具体取决于范围外的记录数)。

此外，您还可以使用完全 unix 时间戳，然后将其嵌入为子查询，外部查询可以将时间戳转换为人类可读的日期(这样转换是在组之后完成的，而不是对于每个单独的记录)。

创建索引:

CREATE INDEX bytime ON user_data (reg_date);
CREATE INDEX daily_group ON user_data (carrier, reg_date);

查询:

SELECT FROM_UNIXTIME(reg_day, "%M %D %Y") AS reg_day
    , carrier
    , user_count
FROM (
    SELECT FLOOR(reg_date / (60 * 60 * 24)) AS reg_day
        , carrier
        , count(carrier) AS user_count
    FROM user_data
    WHERE reg_date >= UNIX_TIMESTAMP('2013-01-01 00:00:00')
        AND reg_date < UNIX_TIMESTAMP('2013-02-01 00:00:00')
    GROUP BY carrier, reg_day
    ORDER BY reg_day, carrier
    ) AS a;