mysql - 加速 GROUP BY、SUM 和 AVG 查询

Question

表 arg_rec 在我的测试机器上包含 800K 行，通常此表将包含超过 15M 行。我想运行以下查询:

SELECT STE_ID, PNT_NO, YR, MN, AVG(AVR_WS) AS AVR_WS, SUM(AVR_PW) FROM arg_rec GROUP BY STE_ID, PNT_NO, YR, MN;

此查询根据风力涡轮机数据提供每日平均风速和总功率。在我的测试机器上，此查询在执行 10 分钟后超时，合并索引为 STE_ID、PNT_NO、YR、MN，它只是主键列的一个子集。如果没有索引，查询将在几分钟后完成。

我正在运行几乎所有的 MySQL 安装，除了调整服务器之外，我还想了解更多有关处理此问题的其他方法，例如:

1. 是否可以基于此查询创建 View 并缓存结果？
2. 是否有更高级的索引功能来封装 YR、MN、DY、HR、MI、SC 对应于记录时间戳的年、月、日等字段这一事实？
3. 我是否最好只使用应用程序的业务层复制数据？

Answer 1

为了在 GROUP BY 查询中获得最佳性能，您必须添加覆盖索引:

ALTER TABLE arg_rec ADD KEY ix1(STE_ID, PNT_NO, YR, MN,AVR_WS, AVR_PW );

对于你添加的覆盖索引:

1. 先在 where 子句中使用的列，然后
2. group by 中使用的列，然后
3. 按顺序使用的列，然后
4. 用于选择的列。

详情请访问:Group By Optmization in MySQL

1. 您可以通过在 Query Cache Configuration 启用查询缓存战利品来在 MySQL 中缓存查询

2. 您可以将 YR、MN、DY、HR、MI、SC 存储在数据类型为 TIMESTAMP 的单个列中，这将提高索引和分组操作的性能。