mysql - SQL MAX和GROUP BY与WHERE

下表所示：

CREATE TABLE `test` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id` INT(11) UNSIGNED NOT NULL,
    `distincted` BIT(1) NOT NULL DEFAULT b'0',
    `timestamp_detected` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx1` (`device_id`),
    INDEX `idx2` (`device_id`, `timestamp_detected`),
    CONSTRAINT `test_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;

我想对按timestamp_detected分组的device_id执行groupwise max，方法如下：

SELECT lh1.id, lh1.timestamp_detected, lh1.device_id FROM test as lh1,
    (SELECT MAX(timestamp_detected) as max_timestamp_detected, device_id FROM test GROUP BY device_id) as lh2
    WHERE lh1.timestamp_detected = lh2.max_timestamp_detected
        AND lh1.device_id = lh2.device_id;

这在运行explain时会产生以下结果：

+----+-------------+------------+-------+---------------------------------------------------------+------------------------------+---------+------------------------------------------+------+--------------------------+
| id | select_type | table      | type  | possible_keys                                           | key                          | key_len | ref                                      | rows | Extra                    |
+----+-------------+------------+-------+---------------------------------------------------------+------------------------------+---------+------------------------------------------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL                                                    | NULL                         | NULL    | NULL                                     |   15 | Using where              |
|  1 | PRIMARY     | lh1        | ref   | FK_location_history_device,device_id_timestamp_detected | device_id_timestamp_detected | 9       | lh2.device_id,lh2.max_timestamp_detected |    1 | Using index              |
|  2 | DERIVED     | test       | range | FK_location_history_device,device_id_timestamp_detected | device_id_timestamp_detected | 4       | NULL                                     |   15 | Using index for group-by |
+----+-------------+------------+-------+---------------------------------------------------------+------------------------------+---------+------------------------------------------+------+--------------------------+

现在要求结果中只包含distincted=1的行。我将查询修改为：

SELECT lh1.id, lh1.timestamp_detected, lh1.device_id FROM test as lh1,
        (SELECT MAX(timestamp_detected) as max_timestamp_detected, device_id FROM test WHERE distincted = 1 GROUP BY device_id) as lh2
        WHERE lh1.timestamp_detected = lh2.max_timestamp_detected
            AND lh1.device_id = lh2.device_id;

它返回正确的结果，但似乎需要更长的时间。运行explain会产生以下结果：

+----+-------------+------------+-------+---------------------------------------------------------+------------------------------+---------+------------------------------------------+------+-------------+
| id | select_type | table      | type  | possible_keys                                           | key                          | key_len | ref                                      | rows | Extra       |
+----+-------------+------------+-------+---------------------------------------------------------+------------------------------+---------+------------------------------------------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL                                                    | NULL                         | NULL    | NULL                                     |  860 | Using where |
|  1 | PRIMARY     | lh1        | ref   | FK_location_history_device,device_id_timestamp_detected | device_id_timestamp_detected | 9       | lh2.device_id,lh2.max_timestamp_detected |    1 | Using index |
|  2 | DERIVED     | test       | index | FK_location_history_device,device_id_timestamp_detected | FK_location_history_device   | 4       | NULL                                     |  860 | Using where |
+----+-------------+------------+-------+---------------------------------------------------------+------------------------------+---------+------------------------------------------+------+-------------+

我试图将distincted列添加到索引idx2中，但没有成功。如何优化此查询？

最佳答案

问题是：

SELECT lh1.id, lh1.timestamp_detected, lh1.device_id
FROM test lh1 JOIN
     (SELECT MAX(timestamp_detected) as max_timestamp_detected, device_id
      FROM test
      WHERE distincted = 1
      GROUP BY device_id
     ) as lh2
     on lh1.timestamp_detected = lh2.max_timestamp_detected AND
        lh1.device_id = lh2.device_id;

对于这个查询，我建议使用test(distincted, device_id, time_stamp_detected)和test(device_id, timestamp_detected)上的索引。
我还想知道，使用此等效查询是否可以获得更好的性能：

SELECT lh1.id, lh1.timestamp_detected, lh1.device_id
FROM test lh1
WHERE distincted = 1 AND
      NOT EXISTS (SELECT 1
                  FROM test t
                  WHERE t.distincted = 1 AND
                        t.device_id = lh1.device_id AND
                        t.timestamp_detected > lh1.timestamp_detected
                 );

这两个指标：test(distincted)和test(device_id, timestamp_detected, distincted)。