mysql - 我的查询需要什么样的索引？

Question

这是我的查询:

SELECT 1
FROM ( SELECT count(*) AS num_week,
              ifnull(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1  day))),0)  as num_day,
              ifnull(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1  hour))),0) as num_hour,
              ifnull(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 minute))),0) as num_1min
        FROM activate_account
       WHERE user_id   = ?  
         AND date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK))
      ) a 
WHERE num_week < 12 AND num_day < 6 AND num_hour < 4 AND num_1min < 1;

上面的查询需要什么索引？单列索引还是多列索引？这里可能的情况:

• activate_account(user_id, date_time)
• activate_account(date_time, user_id)
• activate_account(date_time)
• activate_account(user_id)

确定哪一个是该查询的最佳选择？

Answer 1

最适合您查询的索引位于 activate_account(user_id, date_time)。这满足 where 子句，首先是具有相等条件的列，然后是不等式。

您不需要子查询。这应该没问题:

SELECT count(*) AS num_week,
       COALESCE(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1  day))),0)  as num_day,
       COALESCE(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1  hour))),0) as num_hour,
       COALESCE(sum(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 minute))),0) as num_1min
FROM activate_account
WHERE user_id = ?  AND
      date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK))
HAVING num_week < 12 AND num_day < 6 AND num_hour < 4 AND num_1min < 1;