分析比较多表查询中的IN与JOIN

痛定思痛。 2022-09-26 00:26 195阅读 0赞

IN 是子查询的关键字，JOIN 是连接的关键字，项目开发中经常会使用到多表查询，而子查询与连接正是实现多表查询的重要途径。那两者是怎么运行的？IN与JOIN哪个更好？下面就来分析与比较。

--------------------

现在有test1与test2两张表，都没有任何像主键，外键那样的约束，且只有一个字段。两张表是非相关的。

![这里写图片描述][20160717101908773]

--------------------

现在使用IN关键字实现子查询，test2作为子查询表（外部表）：

![这里写图片描述][20160717102140620]

查看执行计划：

![这里写图片描述][20160717102250977]

--------------------

使用JOIN关键字实现连接，同样test2作为外部表：

![这里写图片描述][20160717102513416]

查看执行计划：

![这里写图片描述][20160717102600766]

--------------------

分析：

1.  使用IN子查询实现多表查询时，从执行计划可以看出，整个查询分成3个部分，id = 1的查询有两个，id = 2的查询有一个。id大的级别高，优先进行查询。id = 2的查询对应的是test2（子查询表）的FTS。然后进行id = 1的查询，同级别的查询从上往下顺序执行。计划中显示这个查询是个子查询（subquery），同时查询test1的时候，使用到join buffer(Blocked Nested Loop)，即连接缓冲(阻塞的嵌套循环)。
2.  使用JOIN连接实现多表查询时，先查询test2表（外部表），几乎与IN的方式一样（FTS），再查询test1表，也与IN的方式一样，都用到了join buffer(Blocked Nested Loop)
3.  那join buffer(Blocked Nested Loop)究竟是什么意思，我想这篇博客已经解释得很清楚了。[http://blog.itpub.net/22664653/viewspace-1692317/][http_blog.itpub.net_22664653_viewspace-1692317]
4.  总结一下，非相关（无索引）的多表查询中，使用IN与JOIN的查询都是先将外部表的查询结果加入到连接缓冲区，再从内部表拿取数据进入缓冲区进行比较（嵌套循环）。查询计划几乎没有区别。但是，IN存在优先级的关系，比JOIN多了一次subquery的查询，在这种情况下，JOIN更优。

--------------------

现在在test1表中添加主键（索引），在test2表中添加外键约束（索引），两张表是相关的。

![这里写图片描述][20160717110200681]

进行同样的查询，返回结果是一样的：

![这里写图片描述][20160717110347678]

--------------------

查看IN方式的执行计划：

![这里写图片描述][20160717110630120]

--------------------

查看JOIN方式的执行计划：

![这里写图片描述][20160717110730543]

--------------------

分析：

1.  现在使用IN方式进行查询时，不再像非相关那样显示子查询subquery了（若是子查询会有不同的优先级），而是有个参照的过程！先借助索引对外部表test2进行扫描；再借助索引对test1进行扫描，其中参照了test2的id列。
2.  使用JOIN方式也是一样有一个参照的过程！
3.  这时两种方式的查询也没有用到上面所说的连接缓冲区与阻塞嵌套循环。
4.  总结一下，当两张表相关（外键相连）时，无论是IN还是JOIN，联合查找都是一个参照的过程。

--------------------

写到这里，似乎IN与JOIN在表相关（逻辑外键）的时候，并不知道哪个更优，下面就来实践一下。

--------------------

实际应用：

--------------------

下面使用MySQL的示例数据库sakila（customer表中有599个顾客信息，主键为customer\_id。rental表中有16044行数据，其中的主键为rental\_id，外键列customer\_id参考customer表中的主键）分别执行IN与JOIN实现多表查询：

IN查询语句：SELECT CONCAT(first\_name,last\_name) FROM customer WHERE customer\_id IN (SELECT customer\_id FROM rental WHERE rental\_id <=16000);

结果（返回了599条客户名字信息）：

![这里写图片描述][20160717213418115]

慢查询日志：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"># Time: 160717 21:17:58
 # User@Host: root[root] @ localhost [127.0.0.1] Id: 17
 # Query_time: 0.000000 Lock_time: 0.000000 Rows_sent: 599 Rows_examined: 1198
 use sakila;
 SET timestamp=1468761478;
 SELECT CONCAT(first_name,last_name) FROM customer WHERE customer_id IN (SELECT customer_id FROM rental WHERE rental_id <=16000);
 </code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li></ul>

JOIN查询语句：SELECT CONCAT(first\_name,last\_name) FROM customer AS a INNER JOIN rental AS b ON a.customer\_id = b.customer\_id WHERE rental\_id<=16000;

结果（返回了15995行数据，发现里面有很多重复的名字）：

![这里写图片描述][20160717213820635]

慢查询日志：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"># Time: 160717 21:19:17
 # User@Host: root[root] @ localhost [127.0.0.1] Id: 18
 # Query_time: 0.030000 Lock_time: 0.000000 Rows_sent: 15995 Rows_examined: 16643
 SET timestamp=1468761557;
 SELECT CONCAT(first_name,last_name) FROM customer AS a INNER JOIN rental AS b ON a.customer_id = b.customer_id WHERE rental_id<=16000;
 </code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li></ul>

使用DISTINCT关键字去重的JOIN查询语句：SELECT DISTINCT CONCAT(first\_name,last\_name) FROM customer AS a INNER JOIN rental AS b ON a.customer\_id = b.customer\_id WHERE rental\_id <=16000;

![这里写图片描述][20160717214045889]

慢查询日志：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"># Time: 160717 21:20:31
 # User@Host: root[root] @ localhost [127.0.0.1] Id: 19
 # Query_time: 0.010000 Lock_time: 0.000000 Rows_sent: 599 Rows_examined: 1797
 SET timestamp=1468761631;
 SELECT DISTINCT CONCAT(first_name,last_name) FROM customer AS a INNER JOIN rental AS b ON a.customer_id = b.customer_id WHERE rental_id <=16000;
 </code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li></ul>

分析：

1.  由于rental表的customer\_id列作为外键列，参照的是customer表的主键customer\_id。因此在该查询上两张表是相关表。上面已经分析了这样的IN与JOIN实现多表查询就不存在连接缓冲与阻塞的嵌套循环。但都是通过参照的关系进行查找。
2.  通过比较查找时间（SQL效率）与检索行数（磁盘IO），在这种情况下我会选择IN进行查询。

[20160717101908773]: /images/20220717/502f62e9cdd74e0991a661efedb60cd5.png
[20160717102140620]: /images/20220717/4192efdc3e584e218b70f31651eb6fd8.png
[20160717102250977]: /images/20220717/5e1893574d674f61aac12e0f90a69438.png
[20160717102513416]: /images/20220717/b6d390816b7940bcb5da4e6b863e02b8.png
[20160717102600766]: /images/20220717/5dc155c8db704295a28b587f5bf2dda4.png
[http_blog.itpub.net_22664653_viewspace-1692317]: http://blog.itpub.net/22664653/viewspace-1692317/
[20160717110200681]: /images/20220717/2dd274aa19e24348beff7f8b5fda48c1.png
[20160717110347678]: /images/20220717/532b3b3b8e514401aa58cb1d6311148b.png
[20160717110630120]: /images/20220717/0e534aed19004e0fa1dbdf1b0f005190.png
[20160717110730543]: /images/20220717/adc19056ec584371b290e60bfeb722bb.png
[20160717213418115]: /images/20220717/1095facc8006445e9013853212546b99.png
[20160717213820635]: /images/20220717/0cf19cfda2514e3192a6784e9643f32f.png
[20160717214045889]: /images/20220717/f5a0e8bfb12a462c8b9050cd01b23017.png