SQLMAXおよびGROUPBYとWHERE

NeverFall

次の表があるとします。

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;

私は上のGroupWise最大を実行するtimestamp_detectedことにより、グループ化されたdevice_id以下に：

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
                 );

そして、これらの2つのインデックス：test(distincted)とtest(device_id, timestamp_detected, distincted)。

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]