您现在的位置是:网站首页> 内容页

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

  • uedbet赫塔菲贝蒂斯
  • 2019-04-23
  • 366人已阅读
简介前言网络上有大量的资料提及将IN改成JOIN或者exist,然后修改完成之后确实变快了,可是为什么会变快呢?IN、EXIST、JOIN在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.nextwhile 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.nextend

实际上就是两个循环啦,从上面的代码可以大致了解到,为什么等连接加了索引会很快,主要是因为加了索引,这条语句将走索引: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.nextwhile 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.nextend

和 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, becausethe two are logically equivalent. Not so in MySQL, which sorts the values in theIN() list and uses a fast binary search to see whether a value is in the list. This isO(log n) in the size of the list, whereas an equivalent series of OR clauses is O(n) inthe 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 ALLSELECT * 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,980SELECT * FROM sakila.filmWHERE film_idIN(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: 1230 | Chapter 6: Query Performance Optimizationselect_type: PRIMARYtable: filmtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 951Extra: Using where*************************** 2. row ***************************id: 2select_type: DEPENDENT SUBQUERYtable: film_actortype: refpossible_keys: idx_fk_film_idkey: idx_fk_film_idkey_len: 2ref: film.film_idrows: 2Extra: 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: 1select_type: SIMPLEtable: filmtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 951Extra:*************************** 2. row ***************************id: 1select_type: SIMPLEtable: film_actortype: refpossible_keys: idx_fk_film_idkey: idx_fk_film_idkey_len: 2ref: sakila.film.film_idrows: 2Extra: Using where; Using index; Not exists

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

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

2018.9.16 19:50

, 1, 0, 9);

文章评论

Top