mysql - 有没有办法加快这个查询？

Question

我怀疑有一种方法可以使它更快，但它超出了我的 MySQL 限制。

我有一个表格，其中包含从一些传感器收集的数据，每个事件的频率为 1Hz。表列是 activityId、transducerId(数据来自哪个传感器)、传感器报告的值和时间戳。给定的事件可以有 0 - 24 个传感器。

一秒钟的数据如下所示(根据传感器的数量提供或获取行):

我需要获取一个新表，其中包含以每个传感器命名的列，其中包含该传感器的数据和日期时间列。例如:

目前，我正在通过一系列非常长的查询和连接获取此表。这是我正在使用的查询:

SELECT cd.calculatedValue AS `301`, q1.`302` , q2.`303` , q3.`304` , q4.`305` , q5.`306` , q6.`307` , q7.`308` , q8.`309` , q9.`310` , q10.`311` , q11.`312` , q12.`313` , q13.`314` , cd.`datetime` 
FROM 
data cd 
JOIN 
(SELECT `calculatedValue` AS `302`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 302) AS q1 
ON cd.`datetime` = q1.`datetime` 
JOIN 
(SELECT `calculatedValue` AS `303`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 303) AS q2 
ON cd.`datetime` = q2.`datetime` 
JOIN 
(SELECT `calculatedValue` AS `304`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 304) AS q3 
ON cd.`datetime` = q3.`datetime` 
JOIN 
(SELECT `calculatedValue` AS `305`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 305) AS q4 
ON cd.`datetime` = q4.`datetime` 
JOIN 
(SELECT `calculatedValue` AS `306`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 306) AS q5 
ON cd.`datetime` = q5.`datetime` 
JOIN 
(SELECT `calculatedValue` AS `307`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 307) AS q6 
ON cd.`datetime` = q6.`datetime` 
JOIN 
(SELECT `calculatedValue` AS `308`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 308) AS q7 
ON cd.`datetime` = q7.`datetime` 
JOIN 
(SELECT `calculatedValue` AS `309`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 309) AS q8 
ON cd.`datetime` = q8.`datetime`
 JOIN 
 (SELECT `calculatedValue` AS `310`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 310) AS q9 
 ON cd.`datetime` = q9.`datetime` 
 JOIN 
 (SELECT `calculatedValue` AS `311`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 311) AS q10 
 ON cd.`datetime` = q10.`datetime` 
 JOIN 
 (SELECT `calculatedValue` AS `312`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 312) AS q11 
 ON cd.`datetime` = q11.`datetime` 
 JOIN 
 (SELECT `calculatedValue` AS `313`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 313) AS q12 
 ON cd.`datetime` = q12.`datetime` 
 JOIN 
 (SELECT `calculatedValue` AS `314`, `datetime` FROM `data` WHERE `activityId` = 74 AND `transducerId` = 314) AS q13 
 ON cd.`datetime` = q13.`datetime` 
 WHERE cd.`activityId` = 74 AND cd.`transducerId` = 301

这只需要几分钟的数据就需要很长时间，实际上表中会有数小时的数据，以及多达 10 个以上的传感器。

是否有更好的方法来执行此查询？

非常感谢。

Answer 1

就性能而言，这些派生表会吃掉你的午餐，连同你的午餐盒。这些内联 View 查询开始运行，并具体化为临时 MyISAM 表，然后外部查询引用未索引的临时 MyISAM 表来执行所有连接操作。

作为替代方案，考虑只使用一次击穿桌面，以获得几乎相同的结果。 (在您的查询中，如果任何传感器的日期时间行“缺失”，则不会返回任何行。

考虑使用 GROUP BY 操作，MySQL 可以使用适当的索引对其进行优化。

举个例子，像这样:

SELECT d.datetime
     , MAX(IF(d.transducerId = 301,d.calculatedValue,NULL)) AS `301`
     , MAX(IF(d.transducerId = 302,d.calculatedValue,NULL)) AS `302`
     , MAX(IF(d.transducerId = 302,d.calculatedValue,NULL)) AS `302`
     , MAX(IF(d.transducerId = 303,d.calculatedValue,NULL)) AS `303`
     , MAX(IF(d.transducerId = 304,d.calculatedValue,NULL)) AS `304`
     , MAX(IF(d.transducerId = 305,d.calculatedValue,NULL)) AS `305`
     , MAX(IF(d.transducerId = 305,d.calculatedValue,NULL)) AS `306`
     , MAX(IF(d.transducerId = 305,d.calculatedValue,NULL)) AS `307`
     , MAX(IF(d.transducerId = 305,d.calculatedValue,NULL)) AS `308`
     , MAX(IF(d.transducerId = 305,d.calculatedValue,NULL)) AS `309`
     , MAX(IF(d.transducerId = 305,d.calculatedValue,NULL)) AS `310`
     , MAX(IF(d.transducerId = 305,d.calculatedValue,NULL)) AS `311`
     , MAX(IF(d.transducerId = 305,d.calculatedValue,NULL)) AS `312`
     , MAX(IF(d.transducerId = 305,d.calculatedValue,NULL)) AS `313`
     , MAX(IF(d.transducerId = 305,d.calculatedValue,NULL)) AS `314`
  FROM `data` d
 WHERE d.activityId = 74 
 GROUP BY d.datetime

(您可以将 d.datetime 移动到 SELECT 列表的末尾，我通常首先使用 GROUP BY 列。)

如果没有合适的可用索引，此查询将像重型 cargo 火车在陡坡上挣扎一样猛烈地冒烟。

这个查询最合适的索引可能是

(activityID,datetime,transducerId,calculatedValue)

如果这是一个 InnoDB 表，并且簇键中的前导列是 (activityID,datetime)，就足够了。

理想情况下，此查询的 EXPLAIN 输出在 Extra 列中显示“Using where; using index”。我们绝对不希望在 EXPLAIN 中看到的是“使用文件排序”操作或任何我们可以提供帮助的派生表。

---

此查询与原始查询略有不同；如果在特定日期时间特定传感器“缺失”一行，则此查询将返回该日期时间的一行，但“缺失”传感器的值为 NULL，其中原始查询将省略整行。

---

如果您确实想使用 JOIN 操作，那么不使用内联 View 的等效项将比原始 View 更有效，尽管可能不如 GROUP BY 查询(在我上面的回答中)。

SELECT cd301.datetime
     , cd301.calculatedValue AS `301`
     , cd302.calculatedValue AS `302`
     , cd303.calculatedValue AS `303`
     , cd304.calculatedValue AS `304`
     , cd305.calculatedValue AS `305`
     , cd306.calculatedValue AS `306`
--     , cd307.calculatedValue AS `307`
--     ...
--     , cd314.calculatedValue AS `314`
  FROM `data` cd301
  JOIN `data` cd302
    ON cd302.activityId   = cd301.activityId
   AND cd302.datetime     = cd301.datetime
   AND cd302.transducerId = 302
  JOIN `data` cd303
    ON cd303.activityId   = cd301.activityId
   AND cd303.datetime     = cd301.datetime
   AND cd303.transducerId = 303
  JOIN `data` cd304
    ON cd304.activityId   = cd301.activityId
   AND cd304.datetime     = cd301.datetime
   AND cd304.transducerId = 304
  JOIN `data` cd305
    ON cd305.activityId   = cd301.activityId
   AND cd305.datetime     = cd301.datetime
   AND cd305.transducerId = 305
  JOIN `data` cd306
    ON cd306.activityId   = cd301.activityId
   AND cd306.datetime     = cd301.datetime
   AND cd306.transducerId = 306
 WHERE cd301.transducerId = 301

显然，需要对其进行扩展才能获得遵循相同模式的 307、308 ... 314。

同样，这种 JOIN 方法可能等同于甚至比 GROUP BY 更快，尽管与单行 GROUP BY 计划相比，EXPLAIN 将有更多行。