为什么 EXISTS(NOT EXIST) 与 JOIN(LEFT JOIN) 的性能会比 IN(NOT IN) 好-阿里云开发者社区

开发者社区> xiaoheike> 正文

为什么 EXISTS(NOT EXIST) 与 JOIN(LEFT JOIN) 的性能会比 IN(NOT IN) 好

简介: 前言 网络上有大量的资料提及将 IN 改成 JOIN 或者 exist,然后修改完成之后确实变快了,可是为什么会变快呢?IN、EXIST、JOIN 在 MySQL 中的实现逻辑如何理解呢?本文也是比较粗浅的做一些介绍,知道了 MySQL 的大概执行逻辑,也方便理解。
+关注继续查看

前言

网络上有大量的资料提及将 IN 改成 JOIN 或者 exist,然后修改完成之后确实变快了,可是为什么会变快呢?IN、EXIST、JOIN 在 MySQL 中的实现逻辑如何理解呢?本文也是比较粗浅的做一些介绍,知道了 MySQL 的大概执行逻辑,也方便理解。本文绝大多数内容来自:高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M),还有一部分来自于网络,还有的来自于自己的理解,以下的内容有引用的都会做标准,如有雷同,纯属巧合。

IN 改为 JOIN/EXIST

例如有如下的 IN 查询:

SELECT
    * 
FROM
    tbl1 
WHERE
    col3 IN ( SELECT col3 FROM tbl2 )

如果子查询 select id from t2 数据量比较大的情况下,则会很慢,从网络找找答案,就知道往往是建议修改为:

SELECT
    * 
FROM
    tbl1 
WHERE
    EXISTS ( SELECT 1 FROM tbl2 WHERE tbl1.col3 = tbl2.col3 )

或者改成 INNER JOIN 形式:

SELECT
    * 
FROM
    tbl1
    INNER JOIN tbl2 ON tbl1.col3 = tbl2.col3

确实这两种优化是可行的。不过总体来说更推荐 INNER JOIN,下面章节也会提及。

MySQL JOIN 语法的执行逻辑

一下内容摘抄自 高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M),文章目录:Query Performance Optimization-->Query Execution Basics-->The Query optimizer Process-->MySQL's join execution strategy

INNER JOIN

简单的 JOIN 例子:

SELECT
    tbl1.col1,
    tbl2.col2 
FROM
    tbl1 INNER JOIN tbl2 USING ( col3 ) 
WHERE
    tbl1.col1 IN ( 5, 6 );

MySQL 执行的伪代码:

// WHERE tbl1.col1 IN ( 5, 6 ) 筛选出 tb11 符合条件的记录
outer_iter = iterator over tbl1 where col1 IN(5,6)
outer_row = outer_iter.next
while outer_row
    // 用 tb11 的 col3 去 tbl2 表中查询,有索引将会非常快
    inner_iter = iterator over tbl2 where col3 = outer_row.col3
    inner_row = inner_iter.next
    // 可能会命中多条数据
    while inner_row
        output [ outer_row.col1, inner_row.col2 ]
        inner_row = inner_iter.next
    end
    outer_row = outer_iter.next
end

实际上就是两个循环啦,从上面的代码可以大致了解到,为什么等连接加了索引会很快,主要是因为加了索引,这条语句将走索引:inner_iter = iterator over tbl2 where col3 = outer_row.col3

LEFT JOIN

简单的例子:

SELECT
    tbl1.col1,
    tbl2.col2 
FROM
    tbl1
    LEFT OUTER JOIN tbl2 USING ( col3 ) 
WHERE
    tbl1.col1 IN ( 5, 6 );

MySQL 执行的伪代码:

// WHERE tbl1.col1 IN ( 5, 6 ) 筛选出 tb11 符合条件的记录
outer_iter = iterator over tbl1 where col1 IN(5,6)
outer_row = outer_iter.next
while outer_row
    // 用 tb11 的 col3 去 tbl2 表中查询,有索引将会非常快
    inner_iter = iterator over tbl2 where col3 = outer_row.col3
    inner_row = inner_iter.next
    if inner_row
        // 可能会命中多条数据
        while inner_row
            output [ outer_row.col1, inner_row.col2 ]
            inner_row = inner_iter.next
        end
    else
        // 没有命中的则返回 NULL
        output [ outer_row.col1, NULL ]
    end
    outer_row = outer_iter.next
end

和 INNER JOIN 差不多。

MySQL Exist 语法执行逻辑

没能够找到伪代码,个人觉得应该执行逻辑和JOIN是相似的。从 高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M) 找到了 Exist 与 INNER JOIN 的使用场景,文章路径:Chapter 6. Query Performance Optimization-->Limitations of the MySQL Query Optimizer-->Correlated Subqueries-->When a correlated subquery is good。

例如下面的 JOIN 语句:

SELECT DISTINCT
    film.film_id 
FROM
    sakila.film INNER JOIN sakila.film_actor USING ( film_id );

需要对数据去重,这时候使用 EXISTS 会更合适,因为它的含义是 有一个匹配,所以平时使用的时候也得要小心,使用不当数据就被直接丢失了。改成如下的 EXISTS 语句,执行效率会更高:

SELECT
    film_id 
FROM
    sakila.film 
WHERE
    EXISTS ( SELECT * FROM sakila.film_actor WHERE film.film_id = film_actor.film_id );

所以大多数时候可以使用 INNER JOIN,特别的场景使用 EXISTS。

MySQL IN 语法的执行逻辑

从官网与知名书籍中找到了如下的信息。从官网文档:C.4 Restrictions on Subqueries,有如下的文字:

The reason for supporting row comparisons for IN but not for the others is that IN is implemented by rewriting it as a sequence of =comparisons and AND operations. This approach cannot be used for ALL, ANY, or SOME.

以及高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M),文章目录:Chapter 6. Query Performance Optimization-->The Query Optimization Process-->The Query optimizer-->IN() list comparisons 下有如下描述:

In many database servers, IN() is just a synonym for multiple OR clauses, because
the two are logically equivalent. Not so in MySQL, which sorts the values in the
IN() list and uses a fast binary search to see whether a value is in the list. This is
O(log n) in the size of the list, whereas an equivalent series of OR clauses is O(n) in
the size of the list (i.e., much slower for large lists).

所以呢,IN 查询会被转变为 OR 查询,列子如下。

举个栗子

有如下简单的的 SQL:

SELECT
    * 
FROM
    tbl1 
WHERE
    col3 IN (SELECT col3 FROM tbl2)

那么经过 MySQL 会先执行 SELECT col3 FROM tbl2,假设得到三个值 '1', '2',则会被会被处理为 OR 语句:

SELECT
    * 
FROM
    tbl1 
WHERE
    col3 = '1' 
    OR col3 = '2'

OR 语句实际上又会被处理成 UNION ALL ,所以最后执行的语句类似于:

SELECT
    * 
FROM
    tbl1 
WHERE
    col3 = '1' 
UNION ALL
SELECT
    * 
FROM
    tbl1 
WHERE
    col3 = '2'

因此,如果子查询 SELECT col3 FROM tbl2 的数据量很大的话,MySQL 的解析可能比执行更耗费时间。(PS:多少数据量算多呢?这个我一直没有找到答案,应该也是和MySQL的配置相关,所以才不会有一个定值,因此建议尽量使用 EXISTS 或者 JOIN)

MySQL 可能对IN查询做的优化

书籍 高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M) 有描述了 IN 查询有可能会被MySQL内部优化为 EXISTS 查询,文章路径:Chapter 6. Query Performance Optimization-->Limitations of the MySQL Query Optimizer-->Correlated Subqueries。

语句一:比如一个IN查询:

SELECT
    * 
FROM
    sakila.film 
WHERE
    film_id IN ( SELECT film_id FROM sakila.film_actor WHERE actor_id = 1 );

语句二:有可能在实现的时候被分成了两次查询,先通过查询得到 film_id 在通过 IN 查询,如下所示:

SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980

SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

实际上呢,语句一MySQL会尝试优化为 EXISTS 查询,如下的语句,而语句二则没办法做更多的优化。应该是简单的查询可以直接优化,复杂的查询是不能够的,要不然平常直接写IN语句,而不用专门改成 EXISTS 或者 INNER JOIN 语句。

SELECT
    * 
FROM
    sakila.film 
WHERE
    EXISTS ( SELECT * FROM sakila.film_actor WHERE actor_id = 1 AND film_actor.film_id = film.film_id );

NOT IN 改成 NOT EXIST/LEFT JOIN

例如有如下的 NOT IN 查询:

SELECT
    * 
FROM
    tbl1 
WHERE
    col3 NOT IN ( SELECT col3 FROM tbl2 )

改成 NOT EXISTS 语法:

SELECT
    * 
FROM
    tbl1 
WHERE
    NOT EXISTS ( SELECT 1 FROM tbl2 WHERE tbl1.col3 = tbl2.col3 )

改成 LEFT JOIN 语法:

SELECT
    * 
FROM
    tbl1
    LEFT JOIN tbl2 ON tbl1.col3 = tbl2.col3 
WHERE
    tbl2.col3 IS NULL

书籍 高性能MySQL第三版(O'Reilly.High.Performance.MySQL.3rd.Edition.M) 有描述了 NOT EXISTS 与 LEFT JOIN 的对比,文章路径:Chapter 6. Query Performance Optimization-->Limitations of the MySQL Query Optimizer-->Correlated Subqueries-->When a correlated subquery is good。该部分对比了二者的执行计划,实际上是相差无几的。

NOT EXISTS 的执行计划

NOT EXISTS 查询:

EXPLAIN SELECT
    film_id,
    language_id 
FROM
    sakila.film 
WHERE
    NOT EXISTS ( SELECT * FROM sakila.film_actor WHERE film_actor.film_id = film.film_id ) \G

执行计划输出内容:

*************************** 1. row ***************************
id: 1
230 | Chapter 6: Query Performance Optimization
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index

LEFT JOIN 执行计划

LEFT JOIN 查询:

EXPLAIN SELECT
    film.film_id,
    film.language_id 
FROM
    sakila.film
    LEFT OUTER JOIN sakila.film_actor USING ( film_id ) 
WHERE
    film_actor.film_id IS NULL \G

执行计划输出结果:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists

二者相差无几,LEFT JOIN 性能会略好一些,所以建议使用 LEFT JOIN。

我的博客即将入驻“云栖社区”,诚邀技术同仁一同入驻。

欢迎转载,但请注明本文链接,谢谢你。

2018.9.16 19:50

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server中INNER JOIN与子查询IN的性能测试
这个月碰到几个人问我关于“SQL SERVER中INNER JOIN 与 IN两种写法的性能孰优孰劣?”这个问题。其实这个概括起来就是SQL Server中INNER JOIN与子查询孰优孰劣(IN是子查询的实现方式之一,本篇还是只对比INNER JOIN与子查询IN的性能,如果展开INNER JOIN与子查询性能对比,范围太大了,没法一一详述)。
1834 0
PostgreSQL PostGIS point join polygon (by ST_xxxx) - pglz_decompress 性能优化
标签 PostgreSQL , postgis , pglz_decompress , perf , tuning 背景 在空间数据中,通常会有轨迹、点、面的数据,假设有两张表,一张为面的表,一张为点的表,使用包含 ST_xxxx(c.geom, p.geom) 来进行JOIN(例如以面为单位,聚合统计点的数量)。
897 0
浅析Mysql Join语法以及性能优化
一.Join语法概述 join 用于多表中字段之间的联系,语法如下:   复制代码代码如下: ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona   table1:左表;table2:右表。
822 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
4519 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
10778 0
阿里云服务器远程登录用户名和密码的查询方法
阿里云服务器远程连接登录用户名和密码在哪查看?阿里云服务器默认密码是什么?云服务器系统不同默认用户名不同
483 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
3278 0
+关注
xiaoheike
原创,不重复造轮子
21
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载