mysql - 我应该如何使用 BETWEEN 和 GROUP BY 为查询建立索引？

Question

我正在创建一个表来存储用户对网站的访问，并希望在继续使用该表之前正确索引该表。

我已经删除了 {HOUR | 的用法日 | MONTH}(visit_time)，带有生成(存储)的列，这样我就不会在 GROUP BY 子句中使用函数。

SELECT COUNT(id)
     , hour 
  FROM Visits
 WHERE site_id = 9 
   AND start_time BETWEEN '2019-07-23 21:29:49' AND '2019-07-29 21:03:33'
GROUP BY hour, day, month, year 
ORDER BY hour;

我在优化 mysql 查询方面没有太多经验，并且会在我的应用程序中经常使用此查询。截至目前，我在 site_id、小时、日、月、年上有一个复合索引，但我觉得 start_time 应该包含在我的索引中。我的另一个担心是降低该表的插入性能，因为它会被频繁插入。

Answer 1

对于此查询:

SELECT COUNT(*), hour 
FROM Visits
 WHERE site_id = 9 AND
       start_time BETWEEN '2019-07-23 21:29:49' AND '2019-07-29 21:03:33'
GROUP BY hour, day, month, year 
ORDER BY hour;

最佳索引可能位于(site_id、start_time、小时、日、月、年)。您可能应该在 SELECT 中包含日、月、年。

这是查询的覆盖索引。这意味着查询中的所有列都在索引中，因此只需要扫描索引。

但是，只有前两列被真正使用。第一个是因为相等条件，第二个是因为BETWEEN。索引不会用于聚合，因此需要额外的排序。