MySQL分析查询-提高性能

Question

我有一个mysql表，包含大约800万条记录，我需要对它进行一些分析，以获得平均值，如下表定义和查询所示。结果包含对过去1年数据的逐时分析(参数值的平均值)。

MySQL服务器版本: 8.0.15

表：

create table `temp_data` (
  `dateLogged`   datetime NOT NULL,
  `paramName`    varchar(30) NOT NULL,
  `paramValue`   float NOT NULL,
  `sensorId`     varchar(20) NOT NULL, 
  `locationCode` varchar(30) NOT NULL,
  PRIMARY KEY (`sensorId`,`paramName`,`dateLogged`),
  KEY `summary` (`locationCode`,`paramName`,`dateLogged`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED

查询：以下查询将基于行的参数转换为列，并在此过程中计算参数值的平均值

SELECT  dateLogged,
        ROUND(avg( ROUND(IF(paramName = 'temp1', paramValue, NULL),2) ),2) AS T1,
        ROUND(avg( ROUND(IF(paramName = 'temp2', paramValue, NULL),2) ),2) AS T2,
        ROUND(avg( ROUND(IF(paramName = 'temp3', paramValue, NULL),2) ),2) AS T3,
        ROUND(avg( ROUND(IF(paramName = 'temp4', paramValue, NULL),2) ),2) as T4
FROM temp_data where locationCode='A123' and paramName in ('temp1','temp2','temp3','temp4')
group by dateLogged order by dateLogged;

结果：

+---------------------+--------+---------+-------+-------+
| date                | T1     | T2      | T3    | T4    |
+---------------------+--------+---------+-------+-------+
| 2018-12-01 00:00:00 |  95.46 |   99.12 | 96.44 | 95.86 |
| 2018-12-01 01:00:00 | 100.38 |  101.09 | 99.56 | 99.70 |
| 2018-12-01 02:00:00 | 101.41 |  102.08 | 99.47 | 99.88 |
| 2018-12-01 03:00:00 |  98.79 |  100.47 | 98.59 | 99.75 |
| 2018-12-01 04:00:00 |  98.23 |  100.58 | 98.38 | 98.93 |
| 2018-12-01 05:00:00 | 101.03 |  101.80 | 99.37 | 99.88 |
   ...                   ...       ...      ...      ...
+---------------------+--------+---------+---------+-----+

问题：

现在，该表中有超过800万条记录，执行该查询大约需要35至40秒。

寻找关于如何提高查询性能的建议，并希望将其降到10秒以下。

注：

该表有长达一年的数据，之后的数据将被存档和删除。

描述结果：

+----+-------------+-----------+------------+------+-----------------+---------+---------+-------+---------+----------+--------------------------------------------------------+
| id | select_type | table     | partitions | type | possible_keys   | key     | key_len | ref   | rows    | filtered | Extra                                                  |
+----+-------------+-----------+------------+------+-----------------+---------+---------+-------+---------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | temp_data | NULL       | ref  | PRIMARY,summary | summary | 53      | const | 3524800 |    50.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+-----------+------------+------+-----------------+---------+---------+-------+---------+----------+--------------------------------------------------------+

Answer 1

由于temp1 -> temp4是固定的，我们可以使用生成列对其进行索引：

alter table temp_data add p1234 bool as (paramName IN ('temp1','temp2','temp3','temp4')) NOT NULL,
ADD KEY s1234 (locationCode, p1234, paramName, paramValue, dateLogged)

然后也更改查询：

SELECT  dateLogged, paramName,
        ROUND(avg( ROUND(paramValue,2) ),2)
FROM temp_data where locationCode='A123' and p1234
group by dateLogged, paramName
order by dateLogged, paramName;

处理应用程序代码中的T1 -> T4 paramName格式设置