php - 使用生成的 GROUP BY 语句优化 SQL 查询

Question

我有这个问题:

   SELECT ROUND(AVG(temp)*multT + conT,2) as temp,
          FLOOR(timestamp/$secondInterval) as meh
     FROM sensor_locass
LEFT JOIN sensor_data USING(sensor_id)
    WHERE sensor_id = '$id'
      AND project_id = '$project'
 GROUP BY meh
 ORDER BY timestamp ASC

目的是选择绘制图表的数据，我使用一个像素值的数据的平均值来使图表忠实于数据。

到目前为止，优化包括添加索引、在 MyISAM 和 InnoDB 之间切换，但没有成功。

由于时间间隔随图形缩放和数据收集周期而变化，我无法为 GROUP BY 语句创建单独的列，但是查询速度很慢。有没有人有优化此查询或表以使此分组更快的想法，我目前在 timestamp、sensor_id 和 project_id 上有一个索引列，但不使用 timestamp 索引。

当使用查询运行 explain extended 时，我得到以下信息:

1   SIMPLE  sensor_locass   ref     sensor_id_lookup,project_id_lookup  sensor_id_lookup    4   const                               2       100.00  Using where; Using temporary; Using filesort
1   SIMPLE  sensor_data     ref     idsensor_lookup idsensor_lookup                         4   webstech.sensor_locass.sensor_id    66857   100.00

sensor_data 表目前包含 270 万个数据点，这只是我最终必须处理的数据量的一小部分。欢迎任何有用的想法、评论或解决方案

编辑表格定义:

CREATE TABLE `sensor_data` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `gateway_id` int(11) NOT NULL,
 `timestamp` int(10) NOT NULL,
 `v1` int(11) NOT NULL,
 `v2` int(11) NOT NULL,
 `v3` int(11) NOT NULL,
 `sensor_id` int(11) NOT NULL,
 `temp` decimal(5,3) NOT NULL,
 `oxygen` decimal(5,3) NOT NULL,
 `batVol` decimal(4,3) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `gateway_id` (`gateway_id`),
 KEY `time_lookup` (`timestamp`),
 KEY `idsensor_lookup` (`sensor_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2741126 DEFAULT CHARSET=latin1

CREATE TABLE `sensor_locass` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `project_id` int(11) NOT NULL,
 `sensor_id` int(11) NOT NULL,
 `start` date NOT NULL,
 `end` date NOT NULL,
 `multT` decimal(6,3) NOT NULL,
 `conT` decimal(6,3) NOT NULL,
 `multO` decimal(6,3) NOT NULL,
 `conO` decimal(6,3) NOT NULL,
 `xpos` decimal(4,2) NOT NULL,
 `ypos` decimal(4,2) NOT NULL,
 `lat` decimal(9,6) NOT NULL,
 `lon` decimal(9,6) NOT NULL,
 `isRef` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `sensor_id_lookup` (`sensor_id`),
 KEY `project_id_lookup` (`project_id`)
) ENGINE=MyISAM AUTO_INCREMENT=238 DEFAULT CHARSET=latin1

Answer 1

尽管每个人都有答案，但更改主键以优化对具有 238 行的表的搜索不会改变任何内容，尤其是当 EXPLAIN 显示单个键将搜索范围缩小到两行时。并且将 timestamp 添加到 sensor_data 上的主键也不会起作用，因为没有任何东西在查询时间戳，只是对其进行计算(除非您可以将时间戳值限制为 galymzhan建议)。

哦，您可以在查询中删除 LEFT，因为 project_id 上的匹配无论如何都会使其无关紧要(但不会减慢任何速度)。如果这些变量来自客户输入，请不要将变量直接插入查询以避免 $project_id = "'; DROP TABLES; --"输入 sql 注入(inject)漏洞。

调整堆大小可能会奏效一段时间，但如果需要扩展，则必须继续调整。

vdrmrt 建议的答案可能有效，但随后您需要使用 $secondInterval 的每个可能值填充聚合表，鉴于您所说的灵 active ，我认为这不太合理。同样，您可以考虑 rrdtool ，直接使用它或以与它相同的方式修改您的数据。我具体指的是它将原始数据保留一段给定的时间(通常是几天)，然后在越来越长的时间段内将数据点平均在一起。最终结果是您可以放大最近一段时间的高细节，但如果您进一步回顾，数据已经有效地有损压缩到很长一段时间内的平均值(例如，一天每秒一个数据点，一周每分钟一个数据点，一个月每小时一个数据点，等等)。您最初可以自定义这些平均值，但除非您同时保留原始数据和汇总数据，否则您将无法返回并进行调整。特别是，您无法动态放大到一些较旧的任意点的高细节(例如查看六个月前发生的 1 小时的每秒数据)。

因此，根据您的要求，您必须决定此类限制是否合理。

如果不是，那么我会争辩说您正试图在 MySQL 中做一些它不是为之设计的事情。我建议提取您需要的原始数据并在 php 中取平均值，而不是在您的查询中。正如已经指出的那样，您的查询花费很长时间的主要原因是因为 GROUP BY 子句迫使 mysql 处理内存中的所有数据，但由于它的数据太多，它实际上写入了该数据暂存盘。 (因此 using filesort)。但是，就可以在 php 中使用多少内存而言，您有更大的灵 active 。此外，由于您正在组合附近的行，因此您可以逐行提取数据，动态组合它，因此永远不需要在您的 php 进程中将所有行保留在内存中。然后您可以删除 GROUP BY 并避免文件排序。请改用 ORDER BY timestamp，如果 mysql 未正确优化它，请确保使用 FORCE INDEX FOR ORDER BY (timestamp)