MySQL中的反连接(r12笔记第45天）

  关于Oracle的半连接，反连接，我一直认为这是一个能讲很长时间的话题，所以在我的新书《Oracle DBA工作笔记》中讲性能优化的时候，我花了不少的笔墨做了阐述，结果在做MySQL性能优化的时候，优化思路切换到MySQL层面，我发现要说的东西要更多。总体来看，这部分的优化细节MySQL还在路上，不同的版本中都能够一窥其中的变化，可以看到在不断改进。

   在表的连接上，半连接，反连接本身很平常，但是统计信息的不够丰富导致执行计划的评估中可能会出现较大差别，会很可能把半连接，反连接的实现方式和执行路径的差异放大，导致SQL性能变差，同时MySQL里面in和exists的差距也在减小。

   我就简化一下我的描述，拿MySQL 5.6版本的一些差别来说明。算是对5.5和5.7的承上启下。

我们创建一个表t_fund_info，数据量在两百万，创建另外一个表t_user_login_record数据量和t_fund_info一样。 t_fund_info有主键字段account,t_user_login_record没有索引。

SQL语句如下：

select account
  from t_fund_info
 where money >= 300
   and account not in (select distinct (account)
                         from t_user_login_record
                        where add_time >= '2016-06-01');执行计划如下：

里面的列select_type PRIMARY代表子查询中的最外层查询，此处不是主键查询。而SUBQUERY代表是子查询内层查询的第一个SELECT,结果不会依赖于外部查询的结果集。

从type为ALL代表是全表扫描，所以这样一个查询两个表都是全表扫描，在MySQL内部解析的时候是怎么分解的呢。我们通过explain extended的方式来得到更详细的信息。

/* select#1 */
select test . t_fund_info . account AS account
  from test . t_fund_info
 where ((test . t_fund_info . money >= 300) and
       (not (< in_optimizer >
         (test . t_fund_info . account, test . t_fund_info .
          account in
          (< materialize >
           ( /* select#2 */
                 select test . t_user_login_record . account
                   from test . t_user_login_record
                  where (test . t_user_login_record . add_time >= '2016-06-01')), <
           primary_index_lookup >
           (test . t_fund_info . account in < temporary
            table > on < auto_key >
            where((test . t_fund_info . account = materialized - subquery .
                        account))))))))可以看到启用了临时表，查取了子查询的数据作为后续的缓存处理数据.

  这样的处理，究竟对性能提升有多大呢，其实不大，而且性能改进也很有限。

 我们换一个思路，那就是使用not exists

explain extended select t1.account from t_fund_info t1 where t1.money >=300 and  not exists (select distinct(t2.account) from t_user_login_record t2 where t1.account=t2.account and t2.add_time >='2016-06-01');这种方式在MySQL是如何分解的呢。

select test . t1 . account AS account
  from test . t_fund_info t1
 where ((test . t1 . money >= 300) and
       (not
        (exists ( /* select#2 */
                   select test . t2 . account
                     from test . t_user_login_record t2
                    where ((test . t1 . account = test . t2 . account) and
                          (test . t2 . add_time >= '2016-06-01'))))))  可以看到几乎没有做什么特别的改动。

这一点在5.5,5.6,5.7中都是很相似的处理思路。

  当然这种方式相对来说性能提升都不大。一个局限就在于统计信息不够丰富，所以自动评估就会出现很大的差距。

  这个地方我们稍放一放，我们添加一个索引之后再来看看。

create index ind_account_id2 on t_user_login_record(account); 
然后使用not in的方式查看解析的详情。

select test . t_fund_info . account AS account
  from test . t_fund_info
 where ((test . t_fund_info . money >= 300) and
       (not (< in_optimizer >
         (test . t_fund_info .
          account, < exists >
          (< index_lookup >
           (< cache > (test . t_fund_info . account) in t_user_login_record on
            ind_account_id2
            where((test . t_user_login_record . add_time >= '2016-06-01') and
                       (< cache > (test . t_fund_info . account) = test .
                        t_user_login_record . account))))))))

可以看到这个方式有了索引，not in和not exits的解析方式很相似。有一个差别就是在子查询外有了<cache>的处理方式。

  我们来看看两者的差别，同样的步骤，有了索引之后，估算的key_len(使用索引的长度)为182，估算行数为1

-----------------+---------+------+---------
 key             | key_len | ref  | rows    
-----------------+---------+------+---------
 NULL            | NULL    | NULL | 1875524
 ind_account_id2 | 182     | func |       1而之前没有索引的时候，这个结果差别就很大了，是190多万。

------+---------+------+---------
 key  | key_len | ref  | rows    
------+---------+------+---------
 NULL | NULL    | NULL | 1875524
 NULL | NULL    | NULL | 1945902而顺带看看有了索引之后，not exists的方式是否会有改变。

/* select#1 */
select test . t1 . account AS account
  from test . t_fund_info t1
 where ((test . t1 . money >= 300) and
       (not
        (exists ( /* select#2 */
                   select test . t2 . account
                     from test . t_user_login_record t2
                    where ((test . t1 . account = test . t2 . account) and
                          (test . t2 . add_time >= '2016-06-01'))))))

   以上可以看出，和没有添加索引的解析方式没有差别。哪里会差别呢，就是执行的估算行数上，有天壤之别。
   所以通过这样一个反连接的小例子，可以看出来存在索引的时候，not in会内部转换为not exists的处理方式，而not exists的方式在存在索引和不存在，两者通过执行计划可以看出很大的差别，其中的一个瓶颈点就在于估算的行数。