美文网首页mysql@IT·互联网程序员
MySQL(七)|MySQL中In与Exists的区别(1)

MySQL(七)|MySQL中In与Exists的区别(1)

作者: 小怪聊职场 | 来源:发表于2018-05-22 20:01 被阅读572次

    最近被一条SQL语句弄的有点兴奋,具体情况是这样的...
    我这边有两个表需要关联查询,表的情况如下:

    # 2759174行数据
    SELECT COUNT(*) FROM tb_data t1;
    
    # 7262行数据
    SELECT COUNT(*) FROM tb_task t2;
    
    # 执行时间为44.88s
    SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
    
    # 执行时间为28.93s
    SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);
    

    这里涉及到IN和EXISTS的区别。
    如果你试图在网上找出答案,你会发现所有的博客都是写着:
    如果两个表中一个表大,另一个是表小,那么IN适合于外表大而子查询表小的情况;EXISTS适合于外表小而子查询表大的情况。
    但是,这个说法正确吗?继续往下看!!!

    按照我上面测试的情况。
    t1表有两百多万行数据,t2表只有7千行数据。它们关联关系为t1.task_id = t2.id,我在使用IN时,t2表是子查询表,并且是小表,按理来说在这种情况下使用IN应该是更加合理的方式。
    然后实际情况是使用IN需要44.88s,使用EXISTS需要28.93s,这个是怎么回事?

    一、EXISTS和IN的介绍

    我们先对EXISTS和IN做一个简单的介绍。

    1.1、exists

    exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop到的这条记录;反之,如果exists里的条件语句不能返回记录行,则当前loop到的这条记录被丢弃,exists的条件就像一个bool条件,当能返回结果集则为true,不能返回结果集则为false

    如下:

    select * from user where exists (select 1);
    

    对user表的记录逐条取出,由于子条件中的select 1永远能返回记录行,那么user表的所有记录都将被加入结果集,所以与select * from user;是一样的。

    又如下:

    select * from user where exists (select * from user where user_id = 0);
    

    可以知道对user表进行loop时,检查条件语句(select * from user where user_id = 0),由于user_id永远不为0,所以条件语句永远返回空集,条件永远为false,那么user表的所有记录都将被丢弃。

    总结:如果A表有n条记录,那么exists查询就是将这n条记录逐条取出,然后判断n遍exists条件。

    1.2、in

    in查询相当于多个or条件的叠加,这个比较好理解,比如下面的查询:

    select * from user where user_id in (1, 2, 3);
    

    等效于

    select * from user where user_id = 1 or user_id = 2 or user_id = 3;
    

    总结:in查询就是先将子查询条件的记录全都查出来,假设结果集为B,共有m条记录,然后再将子查询条件的结果集分解成m个,再进行m次查询。

    1.3、使用上的区别

    in查询的子条件返回结果必须只有一个字段,例如

    select * from user where user_id in (select id from B);
    

    不能是

    select * from user where user_id in (select id, age from B);
    

    而exists就没有这个限制。

    二、EXISTS和IN的性能分析

    为了便于分析,我把实际上的例子简化一下。
    实际:
    SELECT t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
    SELECT t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);
    简化后:
    查询1、SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
    查询2、SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

    2.1、in

    假设B表的所有id为(1,2,3),查询1可以转换为:
    SELECT * FROM A WHERE A.id = 1 OR A.id = 2 OR A.id = 3;
    这里主要是用到了A的索引,B表如何对查询影响不大。

    2.2、exists

    查询2可以转化以下伪代码:

    for (i = 0; i < count(A); i++) {
        a = get_record(A, i); #从A表逐条获取记录
        if (B.id = a[id]) { #如果子条件成立
            result[] = a;
        }
    }
    return result;
    

    这里主要用到了B表的索引,A表如何对查询的效率影响不大。

    2.3、实际情况

    1)SELECT t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
    它使用的索引情况如下:

    使用了t1(A)表索引

    2)SELECT t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);

    使用了t2(B)表索引

    三、结论

    MySQL中的in语句是把外表和内表作join连接,而exists语句是对外表作nest loop循环,每次loop循环再对内表进行查询。

    通过以上分析,很容易得出下面的结论:
    1、如果查询的两个表大小相当,那么用in和exists差别不大。
    2、如果两个表中一个表大,另一个是表小,那么IN适合于外表大而子查询表小的情况。
    3、如果两个表中一个表大,另一个是表小,EXISTS适合于外表小而子查询表大的情况。

    在一般情况下确实如此,这里需要考虑到索引原因。但特殊情况是什么情况呢?我目前也不知道。

    最后,我们来看看《高性能MySQL》这边经典的MySQL书籍对in和exists有什么见解。

    《高性能MySQL》书籍上对于in和exists的描述

    书上说,MySQL会把in的查询语句改成exists再去执行(实际上我们在没有索引情况下,他们的执行过程确实是一致的)

    在《MySQL技术内幕:SQL编程》这本书中说:确实有很多DBA认为EXISTS比IN的执行效率更高,可能是当时优化器还不是很稳定和足够优秀,但是目前绝大数的情况下,IN和EXISTS都具有相同的执行计划。

    四、处理

    说实话,我想尽了我所知道的办法,都没有找出这到底是怎么回事。我想我是应该要去看看《MySQL技术内幕:SQL编程》啦。
    不过对于下次处理类似的问题时,我已经有对应的处理方案:
    根据具体的业务情况,分析出它对应可用的经验法则,再进行不断的测试和调优,选出最优的SQL。
    而不仅仅是选择相信经验法则。

    当然,也有一些文章对这个进行了一个深入的讲解,但是我判断不出对错,在此提供给大家参考吧。
    深入理解MySql子查询IN的执行和优化

    这个是第1篇,第2篇等我看一会书先~

    相关文章

      网友评论

      本文标题:MySQL(七)|MySQL中In与Exists的区别(1)

      本文链接:https://www.haomeiwen.com/subject/fzjhjftx.html