mysql - 为什么子查询比联接更快？

Question

慢:

SELECT `m`.`id`,
       `h`.`number`,
       `h`.`message`,
       `h`.`code`,
  FROM `members` AS `m`
    INNER JOIN `history` AS `h` ON `h`.`memb_id` = `m`.`id`
    LEFT JOIN  `reads`   AS `r` ON `r`.`memb_id` = `m`.`id`
    LEFT JOIN  `writes`  AS `w` ON `w`.`memb_id` = `m`.`id`
  WHERE `m`.`status` = 1
    AND (`r`.`reads` > 0 OR `w`.`writes` > 0)
  GROUP BY `m`.`id`
;
-- members  ~ 40k
-- history  ~ 400k
-- reads    ~ 40k
-- writes   ~ 4k
-- Query duration: 0.764 sec.

-- members  ~ 40k
-- history  ~ 400k
-- reads    ~ 400k
-- writes   ~ 4k
-- Query duration: 5.819 sec.

快

SELECT `m`.`id`,
       (SELECT `number`  FROM `history` WHERE `memb_id` = `m`.`id` ORDER BY `id` DESC LIMIT 1) AS `number`,
       (SELECT `message` FROM `history` WHERE `memb_id` = `m`.`id` ORDER BY `id` DESC LIMIT 1) AS `message`,
       (SELECT `code`    FROM `history` WHERE `memb_id` = `m`.`id` ORDER BY `id` DESC LIMIT 1) AS `code`
  FROM `members` AS `m`
    LEFT JOIN `reads`  AS `r` ON `r`.`email` = `m`.`id`
    LEFT JOIN `writes` AS `w` ON `w`.`email` = `m`.`id`
  WHERE `m`.`status` = 1
    AND (`r`.`reads` > 0 OR `w`.`writes` > 0)
  GROUP BY `m`.`id`
;

-- members  ~ 40k
-- history  ~ 400k
-- reads    ~ 40k
-- writes   ~ 4k
-- Query duration: 0.328 sec.

-- members  ~ 40k
-- history  ~ 400k
-- reads    ~ 400k
-- writes   ~ 4k
-- Query duration: 0.343 sec.

架构:

CREATE TABLE `members` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(254) NOT NULL,
    `email` VARCHAR(254) NOT NULL,
    `password` VARCHAR(254) NOT NULL,
    `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `status` (`status`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

CREATE TABLE `recipients_history` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `memb_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `number` INT(3) UNSIGNED NOT NULL DEFAULT '0',
    `message` TEXT NOT NULL,
    `code` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0',
    `datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `memb_id` (`memb_id`),
    INDEX `number` (`number`),
    INDEX `code` (`code`),
    INDEX `datetime` (`datetime`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

-- writes uses the same schema
CREATE TABLE `reads` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `memb_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `reads` INT(11) UNSIGNED NOT NULL DEFAULT '1',
    PRIMARY KEY (`id`),
    INDEX `memb_id` (`memb_id`),
    INDEX `reads` (`reads`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Answer 1

如上所述，您的查询并不等同。您的“慢速”查询获取成员的每条历史记录，然后使用 GROUP BY 缩小结果范围。这意味着短时间内可能存在大量数据，而且数字、消息和代码的值很可能不是最新的历史记录。

添加针对子查询的联接以获取每个成员的最新历史记录 ID，请尝试以下操作:-

SELECT m.id,
       h.number,
       h.message,
       h.code
FROM members AS m
LEFT OUTER JOIN
(
    SELECT memb_id, MAX(id) as max_id
    FROM history
    GROUP BY memb_id
) sub1 ON m.id = sub1.memb_id
LEFT OUTER JOIN history h ON sub1.memb_id = h.memb_id AND sub1.max_id = h.id
LEFT JOIN reads  AS r ON r.email = m.id
LEFT JOIN writes AS w ON w.email = m.id
WHERE m.status = 1
AND (r.reads > 0 OR w.writes > 0)