mysql - 使用 MIN/MAX 的查询作为子查询比单独查询慢得多

Question

考虑对大约 1M 行的表进行以下查询。(结构在最后。TL;DR:它有UNIQUE KEY `max_filter` (`id_chat`, `id_device`, `id_message`)。)

SELECT MAX(`id_message`)
FROM `message_keys`
WHERE `id_chat` = 94609
    AND `id_device` = 26664
    AND `id_message` <= 238798

它几乎按预期立即运行(大约 1 毫秒)。当我这样修改它时:

SELECT (
    SELECT MAX(id_message)
    FROM message_keys
    WHERE message_keys.id_chat = 94609
        AND message_keys.id_device = devices.id
        AND message_keys.id_message <= 238798
) AS max
FROM devices
WHERE devices.id_user = 1

假设用户 1有10个设备，我预计最多运行10-20毫秒，但需要500到1000毫秒，这是 Not Acceptable 。

问题出在哪里？

我使用的是 MariaDB 10.1.23。

解释:

+----+--------------------+--------------+------+------------------------------+------------+---------+------------------------+------+-------------+-------------+
| id |    select_type     |    table     | type |        possible_keys         |    key     | key_len |          ref           | rows |    Extra    |             |
+----+--------------------+--------------+------+------------------------------+------------+---------+------------------------+------+-------------+-------------+
|  1 | PRIMARY            | devices      | ref  | id_user                      | id_user    |       4 | const                  |   10 | Using index |             |
|  2 | DEPENDENT SUBQUERY | message_keys | ref  | PRIMARY,max_filter,id_device | max_filter |       8 | const,devices.id       |  520 | Using where | Using index |
+----+--------------------+--------------+------+------------------------------+------------+---------+------------------------+------+-------------+-------------+

显示警告；在EXPLAIN EXTENDED之后:

+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1276 | Field or reference 'tukan.devices.id' of SELECT #2 was resolved in SELECT #1                                                                                                                                                                                                                                                                                                |
| Note  | 1003 | select <expr_cache><`tukan`.`devices`.`id`>((select max(`tukan`.`message_keys`.`id_message`) from `tukan`.`message_keys` where ((`tukan`.`message_keys`.`id_chat` = 94609) and (`tukan`.`message_keys`.`id_device` = `tukan`.`devices`.`id`) and (`tukan`.`message_keys`.`id_message` <= 238798)))) AS `max` from `tukan`.`devices` where (`tukan`.`devices`.`id_user` = 1) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

结构:

CREATE TABLE `message_keys` (
 `id_message` int(10) unsigned NOT NULL,
 `id_chat` int(10) unsigned NOT NULL,
 `id_from` int(10) NOT NULL,
 `id_device` int(10) unsigned NOT NULL,
 `key` blob NOT NULL,
 `status` enum('sent','delivered','read') CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
 PRIMARY KEY (`id_message`,`id_device`),
 UNIQUE KEY `max_filter` (`id_chat`,`id_device`,`id_message`),
 KEY `id_device` (`id_device`),
 CONSTRAINT `message_keys_ibfk_1` FOREIGN KEY (`id_message`) REFERENCES `messages` (`id`) ON DELETE CASCADE,
 CONSTRAINT `message_keys_ibfk_2` FOREIGN KEY (`id_device`) REFERENCES `devices` (`id`) ON DELETE CASCADE,
 CONSTRAINT `message_keys_ibfk_3` FOREIGN KEY (`id_chat`) REFERENCES `chats` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `devices` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `id_user` int(10) unsigned NOT NULL,
 `guid` binary(32) NOT NULL,
 `public_key` text CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
 `session_key` binary(32) DEFAULT NULL,
 `id_session_home_key` int(10) unsigned DEFAULT NULL,
 `name` varchar(255) NOT NULL,
 `description` text,
 `ip` int(10) unsigned NOT NULL,
 `is_locked` tinyint(1) NOT NULL DEFAULT '0',
 `default_home_key` binary(32) DEFAULT NULL,
 `time_created` int(10) unsigned NOT NULL,
 `time_last_authorized` int(10) unsigned NOT NULL,
 `client_message_id` int(10) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `id_user` (`id_user`),
 KEY `id_session_home_key` (`id_session_home_key`),
 CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `users` (`id`) ON DELETE CASCADE,
 CONSTRAINT `devices_ibfk_2` FOREIGN KEY (`id_session_home_key`) REFERENCES `home_keys` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

这个:

SELECT MAX(`id_message`) AS `max`
FROM `devices`
LEFT JOIN `message_keys`
ON `message_keys`.`id_chat` = 94609
    AND `message_keys`.`id_device` = `devices`.`id`
    AND `message_keys`.`id_message` <= 238798
WHERE `devices`.`id_user` = 1
GROUP BY `devices`.`id`

花费相同的时间(500–1000 毫秒)。

+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                         |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select max(`tukan`.`message_keys`.`id_message`) AS `max` from `tukan`.`devices` left join `tukan`.`message_keys` on(((`tukan`.`message_keys`.`id_chat` = 94609) and (`tukan`.`message_keys`.`id_device` = `tukan`.`devices`.`id`) and (`tukan`.`message_keys`.`id_message` <= 238798))) where (`tukan`.`devices`.`id_user` = 1) group by `tukan`.`devices`.`id` |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Answer 1

子查询可能导致 MySQL 创建临时表。请改用对设备表的联接:

SELECT MAX(a.id_message)
FROM message_keys a
JOIN devices b
    ON a.device_id = b.id
WHERE a.id_chat = 94609
    AND a.id_device = 26664
    AND a.id_message <= 238798
    AND b.id_user = 1;