有可能提高此SQL查询的性能吗？

debugcn 发表于 Dev

prgrmnerd

我有一个具有超过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个时间段内的计数。是否有一种更聪明/更快的方法来获取相同的数据，或者我是将新行放入此表中时对这些数据进行计数的唯一选择吗？

费氏菌

我会在created_at字段上添加索引：

ALTER TABLE items ADD INDEX idx_created_at (created_at)

或（按照托马斯的建议），因为您还要过滤user_id的created_at和user_id上的复合索引：

ALTER TABLE items ADD INDEX idx_user_created_at (user_id, created_at)

然后将您的查询写为：

SELECT 'monthly' as description, COUNT(*) AS cnt FROM items
WHERE created_at >= '2015-07-01 00:00:00' AND user_id = 123456

UNION ALL

SELECT 'weekly' as description, COUNT(*) AS cnt FROM items
WHERE created_at >= '2015-07-26 00:00:00' AND user_id = 123456

UNION ALL

SELECT '30day' as description, COUNT(*) AS cnt FROM items
WHERE created_at >= '2015-06-30 07:57:56' AND user_id = 123456

UNION ALL

SELECT 'recent' as description, COUNT(*) AS cnt FROM items
WHERE created_at >= '2015-07-29 17:03:44' AND user_id = 123456

是的，输出有些不同。或者，您可以使用内联查询：

SELECT
  (SELECT COUNT(*) FROM items WHERE created_at>=... AND user_id=...) AS 'monthly',
  (SELECT COUNT(*) FROM items WHERE created_at>=... AND user_id=...) AS 'weekly',
  ...

如果需要平均值，则可以使用子查询：

SELECT
  monthly,
  weekly,
  monthly / total,
  weekly / total
FROM (
  SELECT
    (SELECT COUNT(*) FROM items WHERE created_at>=... AND user_id=...) AS 'monthly',
    (SELECT COUNT(*) FROM items WHERE created_at>=... AND user_id=...) AS 'weekly',
    ...,
    (SELECT COUNT(*) FROM items WHERE user_id=...) AS total
) s

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。