MySQL中的反连接有什么用

152次阅读

共计 3243 个字符，预计需要花费 9 分钟才能阅读完成。

这篇文章给大家分享的是有关 MySQL 中的反连接有什么用的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。

在表的连接上，半连接，反连接本身很平常，但是统计信息的不够丰富导致执行计划的评估中可能会出现较大差别，会很可能把半连接，反连接的实现方式和执行路径的差异放大，导致 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

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

感谢各位的阅读！关于“MySQL 中的反连接有什么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

正文完