加快GROUP BY，SUM和AVG查询 [英] Speeding up GROUP BY, SUM and AVG queries

问题描述

表arg_rec在我的测试机上包含800K行，通常该表将容纳15M行.我想运行以下查询:

The table arg_rec contains 800K rows on my test machine, normally this table will hold over 15M rows. I want to run the following query :

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上的组合索引仅是主键列的子集.没有索引，查询将在几分钟后完成.

This query gives daily average wind speed and total power from wind turbine data. On my test machine this query times out after 10 minutes of execution with a combined index on STE_ID, PNT_NO, YR, MN, which is only a subset of the primary key columns. Without the index the query completes after several minutes.

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

I am running with pretty much a stock MySQL installation, in addition to tweaking the server I would also like to know more about other ways to handling this problem, such as :

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

推荐答案

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

For best performance in GROUP BY queries you must add covering index as:

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

要添加索引，请添加:

1. 首先在where子句中使用的列，然后
2. 分组依据中使用的列，然后
3. 按以下顺序使用的列，然后
4. select中使用的列.

有关详细信息，请访问: MySQL中的按优化分组

Visit for details: Group By Optmization in MySQL

1. 您可以通过在查询缓存配置

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

You can store YR, MN, DY, HR, MI, SC in a single column having data type as TIMESTAMP which will increase performance of indexing and group by operation.

这篇关于加快GROUP BY，SUM和AVG查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！