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的方式在存在索引和不存在,两者通过执行计划可以看出很大的差别,其中的一个瓶颈点就在于估算的行数。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-04-25

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏逸鹏说道

维护索引(4)——通过重组索引提高性能

前言: 如果碎片程度小于30%,建议使用重组而不是重建。因为重组不会锁住数据页或者数据表,并且降低CPU的资源。 总得来说,重组会清空当前的B-TREE,特别是...

2568
来自专栏数据和云

郑保卫 - 索引优化策略及实战

本文中将要介绍的索引战略方案是以尽可能少的索引来满足尽可能多的数据读取类型的索引构建方法。这个策略方案要求在构建索引时,尽可能多地搜集当前正在使用的未来将要出...

3185
来自专栏令仔很忙

手把手教你-----巧用Excel批量生成SQL语句,处理大量数据

在做系统或者做项目的时候,经常会遇到这样的要求:用户给我们发过来一些数据,要求我们把这些数据导入到数

3083
来自专栏Java后端技术栈

MySQL数据库开发常见问题及几点优化!

MySQL数据库是被广泛应用的关系型数据库,其体积小、支持多处理器、开源并免费的特性使其在Internet中小型网站中的使用率尤其高。在使用 MySQL的过程中...

781
来自专栏数据和云

实战演练:洞若观火--治堵之道在清源

堵塞往往是一件可怕的事情,交通堵塞让人心烦意乱,水道堵塞城市就会臭气冲天,言路堵塞则是非难辨。数据库出现会话堵塞,则很可能造成系统业务中断,这对于 DBA 来说...

1105
来自专栏一枝花算不算浪漫

[数据库设计]数据库设计三大范式

47812
来自专栏架构师之路

InnoDB,5项最佳实践,知其所以然?

第一篇,说说MySQL两个最常用的存储引擎,MyISAM和InnoDB。照自己的理解,把一些知识点总结出来,不只说知识点,多讲“为什么”。 一、关于count...

922
来自专栏杨建荣的学习笔记

用Oracle的眼光来学习MySQL 5.7的sys(上)(r11笔记第24天)

sys的初衷 MySQL 5.7的sys自从推出以来,整体的反响似乎没有预期的那么高,而我看到这个sys库的时候,第一感觉是越发和Oracle像了,不是...

3769
来自专栏杨建荣的学习笔记

一条delete语句的调优(r4笔记第86天)

今天刚上上班,就接到客户的邮件,说生产环境中执行某一条delete sql语句的时间超过了3个小时。最后客户无奈取消了这次数据清理,准备今天在申请时间重做。所以...

3635
来自专栏代码世界

数据库三范式详解

范式的概念   为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计...

3908

扫码关注云+社区

领取腾讯云代金券