mysql - 可以提高此 SQL 查询的性能吗？

Question

我有一个超过 100,000,000 行的表，我有一个如下所示的查询:

SELECT
    COUNT(IF(created_at >= '2015-07-01 00:00:00', 1, null)) AS 'monthly',
    COUNT(IF(created_at >= '2015-07-26 00:00:00', 1, null)) AS 'weekly',
    COUNT(IF(created_at >= '2015-06-30 07:57:56', 1, null)) AS '30day',
    COUNT(IF(created_at >= '2015-07-29 17:03:44', 1, null)) AS 'recent'
FROM
    items
WHERE
    user_id = 123456;

表格如下所示:

CREATE TABLE `items` (
   `user_id` int(11) NOT NULL,
   `item_id` int(11) NOT NULL,
   `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`user_id`,`item_id`),
    KEY `user_id` (`user_id`,`created_at`),
    KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

解释看起来相当无害，减去了大量的行数:

1   SIMPLE  items   ref PRIMARY,user_id user_id 4   const   559864  Using index

我使用查询来收集特定用户在 4 个时间段内的计数。有没有一种更智能/更快的方法来获取相同的数据，或者我唯一的选择是在将新行放入此表时对这些数据进行统计？

Answer 1

如果您在 created_at 上有索引，我还会在 where 子句中添加 created_at >= '2015-06-30 07:57:56'，这是您的分割市场中可能的最低日期。

同样使用相同的索引，它可能会分成 4 个查询:

select count(*) AS '30day'
FROM
items
WHERE
    user_id = 123456
and created_at >= '2015-06-30 07:57:56'
union ....

等等