mysql - 按 DATE(TIMESTAMP) 分组的 SQL 性能与 DATE 和 TIME 的单独列

Question

我在显示 MySQL 数据库中的数据时遇到了问题。我有一个包含所有用户请求的表格，格式为:

| TIMESTAMP Time / +INDEX | Some other params |

我想在我的网站上以表格的形式显示此数据，其中包含每天的请求数。

查询非常简单:

SELECT DATE(Time) as D, COUNT(*) as S FROM Stats GROUP BY D ORDER BY D DESC

但是当研究 EXPLAIN 时，这让我发疯:

Using index; **Using temporary; Using filesort**

MySQL 文档说它在硬盘上为这个查询创建了临时表。

1.000.000 条记录的速度有多快？ 100.000.000 有多快？

有什么方法可以将 INDEX 放在函数的结果上吗？也许我应该为 DATE 和 TIME 创建单独的列，而不是按 DATE 列分组？处理此类问题的其他好方法是什么？缓存？另一个数据库引擎？

Answer 1

如果您在 Time 列上有一个索引，则此操作将执行得很好。我猜你确实有那个索引，因为你的 EXPLAIN 输出说它正在使用索引。

为什么这行得通？因为 MySQL 可以按顺序访问该索引——它可以扫描索引——以满足您的查询。

不要被 Using temporary; 搞糊涂了；使用文件排序。这只是意味着 MySQL 需要创建并返回一个虚拟表，其中每天都有一行。这非常小，几乎肯定适合内存。 filesort 并不一定意味着文件已溢出到磁盘上的临时文件；这只是意味着 MySQL 必须对虚拟表进行排序。它必须对其进行排序以使最后一天排在第一位。

顺便说一句，如果您可以限制查询的日期范围，即使您的应用程序已使用多年，您也将获得可预测的查询性能。试试这个:

SELECT DATE(Time) as D, COUNT(*) as S 
  FROM Stats
 WHERE Time >= CURDATE() - INTERVAL 30 DAY 
  GROUP BY D ORDER BY D DESC