时间戳

首页 > 解决方案 > MySql 使用索引改进 GROUP BY ORDER BY 查询

mysql - MySql 使用索引改进 GROUP BY ORDER BY 查询

问题描述

我有一张这样的桌子：

╔════╦══════════════╦═══════════╗
║ id ║  product     ║ date      ║
╠════╬══════════════╬═══════════╣
║  1 ║ P1           ║ 2020-08-01║
║  2 ║ P2           ║ 2020-08-01║
║  3 ║ P2           ║ 2020-08-03║
║  4 ║ P3           ║ 2020-08-04║
╚════╩══════════════╩═══════════╝

目标是查询每天销售了多少产品，所以查询是：

SELECT `date`, COUNT(id) AS `totalProductsSoldOnDate` 
FROM products 
GROUP BY `date` 
ORDER BY `date` ASC

输出是：

   date        totalProductsSoldOnDate
2020-08-01 |         2
2020-08-03 |         1
2020-08-04 |         1

好的。现在让我们假设我们有一个包含多条记录的场景，并且这个查询必须每天运行几次，我们现在需要关心性能以便尽快检索结果。

据我所知，在这种情况下，文献建议在 GROUP BY 和 ORDER BY 所涉及的列上放置索引，在这种情况下为date。

此时的问题是：这是正确的解决方案吗？

此外，假设放置索引很好：

  1. 对于这种情况，哪个是最有效的索引，为什么？
  2. 更重要的是：索引如何提高性能？即使有日期索引，我也需要扫描表上的所有行，或者我遗漏了什么？

标签： mysqlsqlperformanceindexing

解决方案

制作您的主键（日期，id）并制作您的表 InnoDB。这仍然需要全表扫描（因为您没有 WHERE 子句），但至少它应该避免文件排序。

如果您想比这更快，请创建一个包含所需输出的物化视图，并使用产品表上的触发器使其保持最新状态。然后你将能够运行类似的东西：

SELECT * FROM mv_name ORDER BY date;

推荐阅读