专栏首页xiaoheike为什么 EXISTS(NOT EXIST) 与 JOIN(LEFT JOIN) 的性能会比 IN(NOT IN) 好

为什么 EXISTS(NOT EXIST) 与 JOIN(LEFT JOIN) 的性能会比 IN(NOT IN) 好

前言

网络上有大量的资料提及将 IN 改成 JOIN 或者 exist,然后修改完成之后确实变快了,可是为什么会变快呢?IN、EXIST、JOIN 在 MySQL 中的实现逻辑如何理解呢?本文也是比较粗浅的做一些介绍,知道了 MySQL 的大概执行逻辑,也方便理解。本书绝大多数内容来自:高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M),还有一部分来自于网络,还有的来自于自己的理解,以下的内容有引用的都会做标准,如有雷同,纯属巧合。

IN 改为 JOIN/EXIST

例如有如下的 IN 查询:

SELECT
    * 
FROM
    tbl1 
WHERE
    col3 IN ( SELECT col3 FROM tbl2 )

如果子查询 select id from t2 数据量比较大的情况下,则会很慢,从网络找找答案,就知道往往是建议修改为:

SELECT
    * 
FROM
    tbl1 
WHERE
    EXISTS ( SELECT 1 FROM tbl2 WHERE tbl1.col3 = tbl2.col3 )

或者改成 INNER JOIN 形式:

SELECT
    * 
FROM
    tbl1
    INNER JOIN tbl2 ON tbl1.col3 = tbl2.col3

确实这两种优化是可行的。不过总体来说更推荐 INNER JOIN,下面章节也会提及。

MySQL JOIN 语法的执行逻辑

一下内容摘抄自 高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M),文章目录:Query Performance Optimization-->Query Execution Basics-->The Query optimizer Process-->MySQL's join execution strategy

INNER JOIN

简单的 JOIN 例子:

SELECT
    tbl1.col1,
    tbl2.col2 
FROM
    tbl1 INNER JOIN tbl2 USING ( col3 ) 
WHERE
    tbl1.col1 IN ( 5, 6 );

MySQL 执行的伪代码:

// WHERE tbl1.col1 IN ( 5, 6 ) 筛选出 tb11 符合条件的记录
outer_iter = iterator over tbl1 where col1 IN(5,6)
outer_row = outer_iter.next
while outer_row
    // 用 tb11 的 col3 去 tbl2 表中查询,有索引将会非常快
    inner_iter = iterator over tbl2 where col3 = outer_row.col3
    inner_row = inner_iter.next
    // 可能会命中多条数据
    while inner_row
        output [ outer_row.col1, inner_row.col2 ]
        inner_row = inner_iter.next
    end
    outer_row = outer_iter.next
end

实际上就是两个循环啦,从上面的代码可以大致了解到,为什么等连接加了索引会很快,主要是因为加了索引,这条语句将走索引:inner_iter = iterator over tbl2 where col3 = outer_row.col3

LEFT JOIN

简单的例子:

SELECT
    tbl1.col1,
    tbl2.col2 
FROM
    tbl1
    LEFT OUTER JOIN tbl2 USING ( col3 ) 
WHERE
    tbl1.col1 IN ( 5, 6 );

MySQL 执行的伪代码:

// WHERE tbl1.col1 IN ( 5, 6 ) 筛选出 tb11 符合条件的记录
outer_iter = iterator over tbl1 where col1 IN(5,6)
outer_row = outer_iter.next
while outer_row
    // 用 tb11 的 col3 去 tbl2 表中查询,有索引将会非常快
    inner_iter = iterator over tbl2 where col3 = outer_row.col3
    inner_row = inner_iter.next
    if inner_row
        // 可能会命中多条数据
        while inner_row
            output [ outer_row.col1, inner_row.col2 ]
            inner_row = inner_iter.next
        end
    else
        // 没有命中的则返回 NULL
        output [ outer_row.col1, NULL ]
    end
    outer_row = outer_iter.next
end

和 INNER JOIN 差不多。

MySQL Exist 语法执行逻辑

没能够找到伪代码,个人觉得应该执行逻辑和JOIN是相似的。从 高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M) 找到了 Exist 与 INNER JOIN 的使用场景,文章路径:Chapter 6. Query Performance Optimization-->Limitations of the MySQL Query Optimizer-->Correlated Subqueries-->When a correlated subquery is good。

例如下面的 JOIN 语句:

SELECT DISTINCT
    film.film_id 
FROM
    sakila.film INNER JOIN sakila.film_actor USING ( film_id );

需要对数据去重,这时候使用 EXISTS 会更合适,因为它的含义是 有一个匹配,所以平时使用的时候也得要小心,使用不当数据就被直接丢失了。改成如下的 EXISTS 语句,执行效率会更高:

SELECT
    film_id 
FROM
    sakila.film 
WHERE
    EXISTS ( SELECT * FROM sakila.film_actor WHERE film.film_id = film_actor.film_id );

所以大多数时候可以使用 INNER JOIN,特别的场景使用 EXISTS。

MySQL IN 语法的执行逻辑

从官网与知名书籍中找到了如下的信息。从官网文档:C.4 Restrictions on Subqueries,有如下的文字:

The reason for supporting row comparisons for IN but not for the others is that IN is implemented by rewriting it as a sequence of =comparisons and AND operations. This approach cannot be used for ALL, ANY, or SOME.

以及高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M),文章目录:Chapter 6. Query Performance Optimization-->The Query Optimization Process-->The Query optimizer-->IN() list comparisons 下有如下描述:

In many database servers, IN() is just a synonym for multiple OR clauses, because the two are logically equivalent. Not so in MySQL, which sorts the values in the IN() list and uses a fast binary search to see whether a value is in the list. This is O(log n) in the size of the list, whereas an equivalent series of OR clauses is O(n) in the size of the list (i.e., much slower for large lists).

所以呢,IN 查询会被转变为 OR 查询,列子如下。

举个栗子

有如下简单的的 SQL:

SELECT
    * 
FROM
    tbl1 
WHERE
    col3 IN (SELECT col3 FROM tbl2)

那么经过 MySQL 会先执行 SELECT col3 FROM tbl2,假设得到三个值 '1', '2',则会被会被处理为 OR 语句:

SELECT
    * 
FROM
    tbl1 
WHERE
    col3 = '1' 
    OR col3 = '2'

OR 语句实际上又会被处理成 UNION ALL ,所以最后执行的语句类似于:

SELECT
    * 
FROM
    tbl1 
WHERE
    col3 = '1' 
UNION ALL
SELECT
    * 
FROM
    tbl1 
WHERE
    col3 = '2'

因此,如果子查询 SELECT col3 FROM tbl2 的数据量很大的话,MySQL 的解析可能比执行更耗费时间。(PS:多少数据量算多呢?这个我一直没有找到答案,应该也是和MySQL的配置相关,所以才不会有一个定值,因此建议尽量使用 EXISTS 或者 JOIN)

MySQL 可能对IN查询做的优化

书籍 高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M) 有描述了 IN 查询有可能会被MySQL内部优化为 EXISTS 查询,文章路径:Chapter 6. Query Performance Optimization-->Limitations of the MySQL Query Optimizer-->Correlated Subqueries。

语句一:比如一个IN查询:

SELECT
    * 
FROM
    sakila.film 
WHERE
    film_id IN ( SELECT film_id FROM sakila.film_actor WHERE actor_id = 1 );

语句二:有可能在实现的时候被分成了两次查询,先通过查询得到 film_id 在通过 IN 查询,如下所示:

SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980

SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

实际上呢,语句一MySQL会尝试优化为 EXISTS 查询,如下的语句,而语句二则没办法做更多的优化。应该是简单的查询可以直接优化,复杂的查询是不能够的,要不然平常直接写IN语句,而不用专门改成 EXISTS 或者 INNER JOIN 语句。

SELECT
    * 
FROM
    sakila.film 
WHERE
    EXISTS ( SELECT * FROM sakila.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id );

NOT IN 改成 NOT EXIST/LEFT JOIN

例如有如下的 NOT IN 查询:

SELECT
    * 
FROM
    tbl1 
WHERE
    col3 NOT IN ( SELECT col3 FROM tbl2 )

改成 NOT EXISTS 语法:

SELECT
    * 
FROM
    tbl1 
WHERE
    NOT EXISTS ( SELECT 1 FROM tbl2 WHERE tbl1.col3 = tbl2.col3 )

改成 LEFT JOIN 语法:

SELECT
    * 
FROM
    tbl1
    LEFT JOIN tbl2 ON tbl1.col3 = tbl2.col3 
WHERE
    tbl2.col3 IS NULL

书籍 高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M) 有描述了 NOT EXISTS 与 LEFT JOIN 的对比,文章路径:Chapter 6. Query Performance Optimization-->Limitations of the MySQL Query Optimizer-->Correlated Subqueries-->When a correlated subquery is good。该部分对比了二者的执行计划,实际上是相差无几的。

NOT EXISTS 的执行计划

NOT EXISTS 查询:

EXPLAIN SELECT
    film_id,
    language_id 
FROM
    sakila.film 
WHERE
    NOT EXISTS ( SELECT * FROM sakila.film_actor WHERE film_actor.film_id = film.film_id ) \G

执行计划输出内容:

*************************** 1. row ***************************
id: 1
230 | Chapter 6: Query Performance Optimization
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index

LEFT JOIN 执行计划

LEFT JOIN 查询:

EXPLAIN SELECT
    film.film_id,
    film.language_id 
FROM
    sakila.film
    LEFT OUTER JOIN sakila.film_actor USING ( film_id ) 
WHERE
    film_actor.film_id IS NULL \G

执行计划输出结果:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists

二者相差无几,LEFT JOIN 性能会略好一些,所以建议使用 LEFT JOIN。

欢迎转载,但请注明本文链接,谢谢你。

2018.9.16 19:50

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 1万步21天钉钉运动大神赛

      目前所在的企业,从入职开始到现在,内部沟通协作的主要软件也就是钉钉。三月初的时候,偶然在钉钉运动一栏中发现了这个活动,当时也没怎么犹豫,果断支付了2元契约金...

    happyJared
  • 此处芯安是吾乡 ——Seal ring

    版图生涯是一条工程师修炼技术与心境凝练的修行路。俗话说“工欲善其事必先利其器”,练就一套得心应手的手上功夫是必须要刷满的技能点,如同老木匠视如珍宝的工具箱。出征...

    用户2304635
  • 或许,9月12日的苹果新品发布会依然会让人失望!

    正如每一次的苹果邀请函都带给我们不一样的感受一样,今年9月12日的苹果发布会的邀请函依然让我们浮想联翩。这或许是苹果以往营销套路的延续——通过给人一个极具想象力...

    孟永辉
  • 无法将类型“System.Collections.Generic.IEnumerable<EmailSystem.Model.TemplateInfo>”隐式转换为“System.Collection

    跟着阿笨一起玩NET
  • 恒大入主法拉第,FF91真的要来了吗?

    自恒大入主法拉第之后,FF91落地的步伐似乎开始逐步加快。在许家印到法拉第公司考察之后,FF91在落地到中国的步伐更加快速。就在上周,恒大法拉第未来智能汽车集团...

    孟永辉
  • 如何vs升级后10和12都能同时兼容

    升级办法:先复制vs2008版本的解决方案文件。升级2012后,再将文件复制到目录里面即可。注意升级过程中产生的升级文件(UpgradeLog文件)不能删除!

    跟着阿笨一起玩NET
  • 锤子手机推“无限屏”新机:让我们为老罗的创新点赞

    人们对于锤子手机的发布会的期待其实更多的是在看罗永浩的“单口相声”。无论锤子手机如何,观众依然能够在锤子手机发布会上过一回嘴瘾。罗永浩对于自己,对于友商,对于行...

    孟永辉
  • 饿了么并入口碑,一场“非典型”新零售大战的开始

    如果你了解互联网巨头的套路,你就会发现他们会不断用资本的力量去拓展自己的势力范围,并且建构一个能够将人们所有的生活场景全部都囊括其中的生态体系。饿了么并入口碑仅...

    孟永辉
  • 共享单车落幕:上海凤凰诉ofo或在情理之中

    共享单车浩浩荡荡的发展历程伴随着资本的退却而最终落幕。尽管共享单车市场上摩拜投身美团总算是有了一个好归宿,哈罗单车对于三四线的深度布局实现了逆袭,ofo依然在仗...

    孟永辉
  • 申请高级项目经理

    要评计算机系统集成项目经理必须先考软考的系统集成项管理工程师,如果想评计算机系统集成高级项目经理必须考软考的信息系统项目管理师。考试一年2次,每年的5月下旬和1...

    庞小明

扫码关注云+社区

领取腾讯云代金券