mysql - 优化 MySQL 中的嵌套查询(特别是 GROUP BY)

Question

表格:

CREATE TABLE `temperature` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `hive_id` int(10) unsigned NOT NULL,
   `value` decimal(4,1) NOT NULL,
   `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
   `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
   PRIMARY KEY (`id`),
   UNIQUE KEY `idplusdate` (`hive_id`,`created_at`),
   KEY `hive_id` (`hive_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=360001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

查询:

SELECT 
            hives.guid as hive_guid,

            temperature.id as Temperature_id,
            temperature.hive_id as Temperature_hive_id,
            temperature.value as Temperature_value,
            temperature.created_at as Temperature_created_at,
            temperature.updated_at as Temperature_updated_at

FROM hives

INNER JOIN (
            SELECT 
                *,
                @num := if(@hive_id = hive_id, @num + 1, 1) as row_number,
                @hive_id := hive_id as dummy
             FROM
                  (SELECT * 
                  FROM temperature FORCE INDEX (idplusdate)
                  ORDER BY hive_id, created_at desc) T
             GROUP BY hive_id, created_at 
             HAVING row_number <= 2
          ) temperature
ON hives.id = temperature.hive_id

WHERE hives.guid IN ('tfdb3560-200a-45f7-ab0e-d699fty8w9b9');

解释:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY hives   ref PRIMARY,hives_guid_index    hives_guid_index    110 const   1   Using where; Using index
1   PRIMARY <derived2>  ref <auto_key0> <auto_key0> 4   XXX.hives.id    359 NULL
2   DERIVED <derived3>  ALL NULL    NULL    NULL    NULL    359640  Using temporary; Using filesort
3   DERIVED temperature ALL NULL    NULL    NULL    NULL    359640  Using filesort

好的，所以我有一个表 hives ，其中包含带有 GUID 的项目(对于此查询来说不是很重要)。我还有一个温度表，其中包含每个 hive 的多个传感器读数。该查询的目标是获取特定 GUID 的最后 N 个(在本例中为 2 个)传感器读数(请记住，此查询将与多个 GUID 一起使用，这就是我使用 WHERE IN 的原因)。我知道对于这样一个平凡的任务来说，查询有点复杂，但这是我发现的对于大型数据集最好的查询(如果您有任何建议，请分享)

本例的预期结果是:

tfdb8560-200a-45f7-ab0e-d699fty8w9b9    2879    8   29.6    9/28/2014 12:00 9/28/2014 12:00
tfdb3560-200a-45f7-ab0e-d699fty8w9b9    2880    8   26.6    9/28/2014 18:00 9/28/2014 18:00

由于表有很多行(本例中为 360k，预计有数百万行)，因此查询需要 3-4 秒来执行。我希望降低这次时间，并且我发现 GROUP BY 是长时间的罪魁祸首(因为它显然没有任何索引可供分组)。

因此，只要最终结果相同，我会采取任何方法来缩短查询时间。谢谢!

Answer 1

您的查询过于复杂。如果我理解正确的话，你根本不需要 group by 。这是 FROM 子句的替代版本:

FROM hives INNER JOIN
     (SELECT t.*,
             (@num := if(@hive_id = hive_id, @num + 1,
                         if(@hive_id := hive_id, 1, 1)
             ) as row_number
      FROM temperature t CROSS JOIN
           (select @num := 0, @hive_id := '') vars
      ORDER BY hive_id, created_at desc
     ) temperature
     ON hives.id = temperature.hive_id and temperature.row_number <= 2;

请注意，我将所有变量赋值放入一个表达式中。 MySQL 不保证 SELECT 中表达式的求值顺序。您的原始版本依赖于在 dummy 之前评估 row_number。