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 秒以下。

注意:

该表最多包含 1 年的数据，超过该时间的数据将被存档和删除

描述结果:

+----+-------------+-----------+------------+------+-----------------+---------+---------+-------+---------+----------+--------------------------------------------------------+
| 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 是固定的，我们可以使用 generated columns对此建立索引:

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 格式