为什么子查询连接比直接连接快得多？

Question

我有两个表(页面和注释)，大约每个13万行.

我想列出没有任何注释的页面(外键是comments.page_id)

如果我执行正常的左侧外部联接，那么运行需要超过750秒的。(130 k^2= 17B)然而，如果我执行相同的联接，但使用表的子查询，则只需1秒即可。

服务器版本:5.6.44-日志- MySQL社区服务器(GPL)：

查询1.正常连接，750+秒

SELECT p.id
FROM `pages` AS p
LEFT JOIN  `comments` AS c
    ON p.id = c.page_id
WHERE c.page_id IS NULL
GROUP BY 1

查询2.连接第一个表作为子查询，时间太长了

SELECT p.id
FROM (
    SELECT id FROM `pages`
) AS p
LEFT JOIN  `comments` AS c
    ON p.id = c.page_id
WHERE c.page_id IS NULL
GROUP BY 1

查询3.连接第二个表作为子查询，1.6秒

SELECT p.id
FROM `pages` AS p
LEFT JOIN (
   SELECT * FROM `comments`
) AS c
    ON p.id = c.page_id
WHERE c.page_id IS NULL
GROUP BY 1

查询4.连接2个子查询，1秒

SELECT p.id
FROM (
    SELECT id FROM `pages`
) AS p
LEFT JOIN (
   SELECT * FROM `comments`
) AS c
    ON p.id = c.page_id
WHERE c.page_id IS NULL
GROUP BY 1

查询5.连接两个子查询，只选择1列，0.2秒

SELECT p.id
FROM (
    SELECT id FROM `pages`
) AS p
LEFT JOIN (
   SELECT page_id FROM `comments`
) AS c
    ON p.id = c.page_id
WHERE c.page_id IS NULL
GROUP BY 1

查询6.时间过长

SELECT p.id
    FROM `pages` AS p
    WHERE NOT EXISTS( SELECT page_id FROM `comments`
                        WHERE page_id = p.id );;

现在，在MySql版本5.7中，上述查询的all需要“太多时间”执行。

在MySql 5.7中，查询1和4有相同的解释：

id  select_type  table    partitions     type    possible_keys  key         key_len  ref    rows        filtered    Extra  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1    SIMPLE         p       NULL        index       PRIMARY    PRIMARY      4       NULL    147626      100.00      Using index; Using temporary; Using filesort  
1    SIMPLE         c       NULL        ALL         NULL        NULL        NULL    NULL    147790      10.00       Using where; Not exists; Using join buffer (Block Nested Loop)

遗憾的是，在MySql 5.6中，我现在无法得到查询1的解释(花费了太多时间)，但查询4的解释如下：

id  select_type table       type    possible_keys   key     key_len     ref     rows        Extra   
---------------------------------------------------------------------------------------------------------------------------
1   PRIMARY     <derived2>  ALL     NULL            NULL        NULL    NULL    147626      Using temporary; Using filesort 
1   PRIMARY     <derived3>  ref     <auto_key0>     <auto_key0>  4      p.id    10          Using where; Not exists    
3   DERIVED     comments    ALL     NULL            NULL        NULL    NULL    147790      NULL   
2   DERIVED     pages       index   NULL            PRIMARY     4       NULL    147626      Using index

表：

CREATE TABLE `pages` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `identifier` varchar(250) NOT NULL DEFAULT '',
 `reference` varchar(250) NOT NULL DEFAULT '',
 `url` varchar(1000) NOT NULL DEFAULT '',
 `moderate` varchar(250) NOT NULL DEFAULT 'default',
 `is_form_enabled` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `date_modified` datetime NOT NULL,
 `date_added` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=147627 DEFAULT CHARSET=utf8


CREATE TABLE `comments` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(10) unsigned NOT NULL DEFAULT '0',
 `page_id` int(10) unsigned NOT NULL DEFAULT '0',
 `website` varchar(250) NOT NULL DEFAULT '',
 `town` varchar(250) NOT NULL DEFAULT '',
 `state_id` int(10) NOT NULL DEFAULT '0',
 `country_id` int(10) NOT NULL DEFAULT '0',
 `rating` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `reply_to` int(10) unsigned NOT NULL DEFAULT '0',
 `comment` text NOT NULL,
 `reply` text NOT NULL,
 `ip_address` varchar(250) NOT NULL DEFAULT '',
 `is_approved` tinyint(1) unsigned NOT NULL DEFAULT '1',
 `notes` text NOT NULL,
 `is_admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `is_sent` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `sent_to` int(10) unsigned NOT NULL DEFAULT '0',
 `likes` int(10) unsigned NOT NULL DEFAULT '0',
 `dislikes` int(10) unsigned NOT NULL DEFAULT '0',
 `reports` int(10) unsigned NOT NULL DEFAULT '0',
 `is_sticky` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `is_locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `is_verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `date_modified` datetime NOT NULL,
 `date_added` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=147879 DEFAULT CHARSET=utf8

问题

1. 为什么会发生这种情况？MySql在引擎盖下做什么？
2. 这种情况是否只发生在MySql或任何其他Sql中？
3. 如何编写快速查询以获得所需的信息？(第5.6、5.7节)

Answer 1

长期运行的查询的问题是，在comments表的page_id列上缺少索引。因此，对于pages表中的每一行，您需要检查注释表的所有行。由于您使用的是左联接，这是唯一可能的连接顺序。在5.6中，当您在FROM子句(也称为派生表)中使用子查询时，MySQL将在用于派生表的结果的临时表上创建一个索引(解释输出中的auto_key0)。当您只选择一列时，它会更快，原因是临时表将更小。

在MySQL 5.7中，如果可能的话，这些派生表将自动合并到主查询中。这样做是为了避免额外的临时表。但是，这意味着您不再有一个索引可用于联接。(详见这篇博客文章。)

您有两个选项可以改进5.7中的查询时间：

1. 可以在注释上创建索引(Page_id)
2. 通过将子查询重写为不能合并的查询，可以防止子查询合并。具有聚合、限制或联合的子查询将不会合并(有关详细信息，请参阅博客文章 )。这样做的一种方法是向子查询中添加限制子句。为了不从结果中删除任何行，限制必须大于表中的行数。

在MySQL 8.0中，您还可以使用优化器提示来避免合并。在你的情况下，你会觉得

SELECT /*+ NO_MERGE(c) */ ... FROM

有关如何使用此类提示的示例，请参阅这份报告的幻灯片34-37。

Answer 2

查询1有“爆炸-内爆”综合症。首先，它执行一个JOIN；这会爆炸行数。然后做一个GROUP BY来收缩。

也是

每页注释的数量等将对您的查询产生影响。

当SELECT *只需要知道LEFT JOIN是否成功时，它就会获取所有的列。(你注意到了。)此外，您没有保留任何列，因为您正在查找缺少的行。

查询2不应该像您发现的那样快--它需要构建两个临时表(“派生”表)，索引其中一个，然后执行外部查询。(新版本的MySQL可能会短路，旧版本因工作效率低下而声名狼藉。)

查询3：

试一试

SELECT p.id
    FROM `pages` AS p
    WHERE NOT EXISTS( SELECT 1 FROM `comments`
                        WHERE page_id = p.id );

此外：

• 使用InnoDB，而不是MyISAM。
• comments需要INDEX(page_id)