使用min/max的查询作为子查询比单独查询慢得多

6rvt4ljy 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(282)

考虑对一个有~1m行的表执行以下查询(结构在最后。热释光；医生：是的 UNIQUE KEYmax_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

假设 user 1有10个 device s、我预计这最多运行10-20毫秒，但需要500-1000毫秒。
有什么问题吗？
我正在使用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 |
+----+--------------------+--------------+------+------------------------------+------------+---------+------------------------+------+-------------+-------------+
``` `SHOW WARNINGS;` 之后 `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 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

sql mysql mariadb

来源：https://stackoverflow.com/questions/50944161/queries-with-min-max-are-much-slower-as-subqueries-than-by-themselves

3条答案

按热度按时间

vmjh9lq91#

如果你在钥匙上分组怎么办？

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 GROUP BY 
message_keys.id_chat,message_keys.id_device ,message_keys.id_message
) AS max
FROM devices
WHERE devices.id_user = 1

赞(0）回复(0）举报 2021-06-20

yptwkmov2#

对于此查询：

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

最佳指标为 devices(id_user, id) 以及 message_keys(id_device, id_chat, id_message) .
特别是，第一个查询可以为查询选择三个单列索引中的任意一个。第二，它必须使用 (device_id) ，这可能不是查询的最佳索引。

赞(0）回复(0）举报 2021-06-20

jljoyd4f3#

子查询可以导致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;

赞(0）回复(0）举报 2021-06-20

我来回答

使用min/max的查询作为子查询比单独查询慢得多

3条答案

相关问题

热门标签

最新问答