MYSQL IN EXISTS LEFT JOIN 结果不同的问题？ - 服务器托管|北京服务器租用|机房托管租用|IDC托管租用|机房机柜带宽租用-价格及费用咨询

这个问题的从下面的这个SQL 来开始，这是一个典型的说复杂不复杂，说写的好，写的不怎么好的一个SQL。

select fi.title,fi.film_id  from film as fi
left join film_category as fc on fi.film_id = fc.film_id
and exists (select 1 from film_actor as fa where fa.film_id = fi.film_id and fa.film_id = 200);
执行计划
——————————————————————————————
| -> Nested loop left join  (cost=453.00 rows=1000) (actual time=0.045..1.954 rows=1000 loops=1)
    -> Covering index scan on fi using idx_title  (cost=103.00 rows=1000) (actual time=0.028..0.236 rows=1000 loops=1)
    -> Nested loop inner join  (cost=100.35 rows=1) (actual time=0.002..0.002 rows=0 loops=1000)
        -> Filter: (fi.film_id = 200)  (cost=0.25 rows=1) (actual time=0.001..0.001 rows=0 loops=1000)
            -> Covering index lookup on fc using PRIMARY (film_id=200)  (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=1000)
        -> Filter: (``.film_id = 200)  (cost=18.27..18.27 rows=1) (actual time=0.018..0.018 rows=1 loops=1)
            -> Single-row index lookup on  using  (film_id=fi.film_id)  (actual time=0.001..0.001 rows=1 loops=1)
                -> Materialize with deduplication  (cost=1.35..1.35 rows=5) (actual time=0.018..0.018 rows=1 loops=1)
                    -> Filter: (fa.film_id is not null)  (cost=0.80 rows=5) (actual time=0.008..0.010 rows=3 loops=1)
                        -> Covering index lookup on fa using idx_fk_film_id (film_id=200)  (cost=0.80 rows=5) (actual time=0.007..0.009 rows=3 loops=1)
这里执行计划出现了 Materialize with deduplication ，这个到底是一个什么意思，今天的问题就从这里开始了。

这里Materialize with deduplication 的意思是，当第一次MYSQL需要这个子查询的结果的情况下，会将临时结果产生为一个临时表，当再次需要这个结果的时候会再次调用。通过给临时表用散列表对表进行索引，索引为唯一索引去除重复值。

这样的好处有两个

1 可以尽量不进行语句的改写

2 可以重复的调用

这个功能本身 materializatinotallow=on 设置为ON 才能在查询中使用这个功能

mysql> SELECT @@optimizer_switchG

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_uninotallow=on,index_merge_sort_uninotallow=on,index_merge_intersectinotallow=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materializatinotallow=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensinotallow=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

下面我们分析一下，什么时候会出现 materialize with deduplication

语句1 select film_id,title,last_update from film as fi where fi.film_id in (select fa.film_id from film_actor as fa where fa.film_id = 200);

第一个语句并没有出现materialize with deduplication，而是通过索引连接的方式将子查询进行了处理，原因是因为两个表的主键都包含了 film_id 并且子查询中的条件也是主键包含，所以语句优化的过程中并没有出现 materialize with deduplication.

那么我们将条件更换，将子查询中的条件更换为lat_update

select film_id,title,last_update from film as fi where fi.film_id in (select fa.film_id from film_actor as fa where fa.last_update > ‘2006-12-12’);

在语句变化后，条件变为了非主键的条件后，就产生了 Materialize with deduplication 同时产生了子查询的结果后，并且结果为一行，将主表和产生的新的临时表进行了 nested loop inner join的操作。

此时我们优化这个查询，因为cost 太高了，我们针对这个查询添加了film_actor 中的字段 last_update的索引。然后在次查看执行计划后，发现整体的cost 大幅度降低。

通过这个问题，可以看出虽然有 masterialize with deduplication 但是对于子查询中的数据的过滤还是一个必选项，也可以有效的提高查询的效率。

上面查询中使用了IN 和 EXISTS ，如果我们通过 not in 和 not exists 来看看执行计划是否有变化。通过下面两个语句，可以看到整体的执行计划的改变

explain format=tree select film_id,title,last_update from film as fi where not exists ( select 1 from film_actor as fa where fa.film_id = fi.film_id and fa.last_update > '2005-12-12');
explain format=tree select film_id,title,last_update from film as fi where fi.film_id not in (select fa.film_id from film_actor as fa where fa.last_update > '2005-12-12');

整体的执行计划变更中，虽然使用的 last_update 的索引，但并没有提高查询效率，同时因为是排除在查询中还添加 film_id is not null ，然后使用了MYSQL 8.021 后提供的 antijoin 的方式来进行两个表的反向的数据连接。

但是整体的数据查询的效率 cost 很高，这也从侧面说明语句在撰写中，尽量还是要避免 NOT IN , NOT EXISTS 。

explain format=tree select count(fi.film_id)
from film as fi
left join film_category as fc on fi.film_id = fc.film_id
and fi.film_id in  (select film_id from film_actor as fa where fa.film_id = fi.film_id and fa.film_id = 2);
explain format=tree select count(fi.film_id)
from film as fi
left join film_category as fc on fi.film_id = fc.film_id
and exists (select * from film_actor as fa where fa.film_id = fi.film_id and fa.film_id = 2);
explain analyze select count(fi.film_id)  
 from film as fi  
 left join film_category as fc on fi.film_id = fc.film_id   
 left join film_actor as fa on fa.film_id = fi.film_id and fa.film_id = 2;

上面的三个SQL 看上去要表达一个目的，实际上从结果上看，1 2 SQL 的结果是一致的，第三个用 LEFT JOIN 表达的SQL 的结果和前两个不一样。

这里结果的不同主要有几个问题

1 IN EXIST 在数据结果查询中，是有去重的功能的。

2 LEFT JOIN 是是存在一对多的关系

见下图这个就是，通过left JOIN 查询后的数据，明显与上个 EXIST ,IN 的结果中，多个 3个 2 原因是在于

实际上在film_actor 中就存在 4条 film_id =2 的记录，所以LEFT JOIN 如实的包括了4 个2 的记录，而 EXIST IN 则带有去重的功能，所以在结果中只有一个 2 的记录。

如果要LEFT JOIN 中查询的结果与 EXIST IN 一致则可以在查询语句中加入group by 来去重。

select count(*) from (
select count(fi.film_id)
 from film as fi  
 left join film_category as fc on fi.film_id = fc.film_id   
 left join film_actor as fa on fa.film_id = fi.film_id and fa.film_id = 2
 group by fi.film_id) as t;

所以在撰写语句的时候，要明白 IN EXIST 和 LEFT JOIN 之间的区别，避免结果不是自己要的。

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net

相关推荐: 慢 SQL 优化之索引的作用是什么？ | 京东云技术团队

前言本文针对 MySQL 数据库的 InnoDB 存储引擎，介绍其中索引的实现以及索引在慢 SQL 优化中的作用。本文主要讨论不同场景下索引生效与失效的原因。慢SQL与索引的关系慢SQL优化原则数据库也是应用，MySQL 作为一种磁盘数据库，属于典型…

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用