mysql查询的优化

Question

我正在使用MySQL，并有一个如下所示的表user_data：

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

reg_data是注册时间的unix时间戳(可以是一天中的任何一秒)，运营商是运营商的类型，其可能的值只能是'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 caluse，这取决于作用域之外的记录数)。

此外，您可以完全使用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;