MySQL中的in+子查询应该如何优化

MySQL 4.1引入了对子查询的支持，即嵌套在其他查询中的查询。

一、为什么需要子查询

比如给定一个订单信息查询的场景：

订单存储在两个表中：

• 对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。
• 各订单的物品存储在相关的orderitems表中。

orders表不存储客户信息，它只存储客户的ID。实际的客户信息存储在customers表中。

假如需要列出订购物品TNT2的所有客户，应该怎样检索？

• 检索包含物品TNT2的所有订单的编号。
• 检索具有前一步骤列出的订单编号的所有客户的ID。
• 检索前一步骤返回的所有客户ID的客户信息。

上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。

两条SQL如下：

# 检索订单编号，假设检索结果是20005，20007
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
# 检索对应订单编号的客户的id，假设检索结果是10004，10005
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);

如果使用子查询，那么就可以组合如下：

SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

其实，这两种SQL的结果是一样的，在WHERE子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。

二、IN + 子查询优化

下面我们就来分析一下 IN + 子查询 的执行计划，以及如何去优化它。

2.1 IN + 子查询

子查询与IN结合使用时，通常通过子查询查询出某个表单列的值，然后作为外层的SELECT的IN查询的数据源，如下：

mysql> select id, name, phone from user 
mysql> where id in (select user_id from user_realname where name = 'zhangsan');
+--+--------+-----------+
|id|name    |phone      |
+--+--------+-----------+
|1 |zhangsan|13511223453|
+--+--------+-----------+

下面我们通过执行计划Explain来分析一下该SQL：

+--+------------+-------------+----------+------+-------------+-------+-------+-------------------+----+--------+-----------+
|id|select_type |table        |partitions|type  |possible_keys|key    |key_len|ref                |rows|filtered|Extra      |
+--+------------+-------------+----------+------+-------------+-------+-------+-------------------+----+--------+-----------+
|1 |SIMPLE      |<subquery2>  |null      |ALL   |null         |null   |null   |null               |null|100     |Using where|
|1 |SIMPLE      |user         |null      |eq_ref|PRIMARY      |PRIMARY|4      |<subquery2>.user_id|1   |100     |null       |
|2 |MATERIALIZED|user_realname|null      |ALL   |null         |null   |null   |null               |4   |25      |Using where|
+--+------------+-------------+----------+------+-------------+-------+-------+-------------------+----+--------+-----------+

分析：

• 对于外层SELECT对应用户表user的每一行数据都要执行一次这个子查询，而这个子查询是需要返回一个数据集合而不是单条数据，然后再判断外层SELECT的当前数据行的该列的值是否在这个集合中，类似于O(N)的线性时间复杂度。

2.2 EXISTS

mysql> select id, name, phone from user
mysql> where exists 
mysql> (select * from user_realname where user.id = user_realname.user_id and name = 'zhangsan');

+--+--------+-----------+
|id|name    |phone      |
+--+--------+-----------+
|1 |zhangsan|13511223453|
+--+--------+-----------+

下面我们通过执行计划Explain来分析一下该SQL：

+--+------------+-------------+----------+------+-------------+-------+-------+-------------------+----+--------+-----------+
|id|select_type |table        |partitions|type  |possible_keys|key    |key_len|ref                |rows|filtered|Extra      |
+--+------------+-------------+----------+------+-------------+-------+-------+-------------------+----+--------+-----------+
|1 |SIMPLE      |<subquery2>  |null      |ALL   |null         |null   |null   |null               |null|100     |Using where|
|1 |SIMPLE      |user         |null      |eq_ref|PRIMARY      |PRIMARY|4      |<subquery2>.user_id|1   |100     |null       |
|2 |MATERIALIZED|user_realname|null      |ALL   |null         |null   |null   |null               |4   |25      |Using where|
+--+------------+-------------+----------+------+-------------+-------+-------+-------------------+----+--------+-----------+

分析：

• 执行计划与IN差不多，外层SELECT的type都是ALL，即全表扫描，但是EXISTS的执行过程与IN不一致
• 对于EXISTS而言，外层SELECT对应的用户表user也参与到了子查询的SQL中，即user.id = user_realname.user_id，故如果子查询的结果不为空，即存在数据，则外层SELECT对应的user表的当前数据行肯定是符合要求的，故该子查询实际上并不返回任何数据，而是返回值True或False，不需要与IN一样返回一个数据集合。
• 而对外层SELECT来说，通过EXISTS判断子查询返回的boolean值True或者False来判断当前数据行是否符合要求，故**时间复杂度为常量级别O(1)**。

2.1 JOIN

mysql> select user.id, user.name, user.phone from user
mysql> join user_realname
mysql> on user.id = user_realname.user_id
mysql> where user_realname.name = 'zhangsan';

+--+--------+-----------+
|id|name    |phone      |
+--+--------+-----------+
|1 |zhangsan|13511223453|
+--+--------+-----------+

下面我们通过执行计划Explain来分析一下该SQL：

+--+-----------+-------------+----------+------+-------------+-------+-------+-----------------------------------+----+--------+-----------+
|id|select_type|table        |partitions|type  |possible_keys|key    |key_len|ref                                |rows|filtered|Extra      |
+--+-----------+-------------+----------+------+-------------+-------+-------+-----------------------------------+----+--------+-----------+
|1 |SIMPLE     |user_realname|null      |ALL   |null         |null   |null   |null                               |4   |25      |Using where|
|1 |SIMPLE     |user         |null      |eq_ref|PRIMARY      |PRIMARY|4      |leadnews_user.user_realname.user_id|1   |100     |null       |
+--+-----------+-------------+----------+------+-------------+-------+-------+-----------------------------------+----+--------+-----------+

分析：

• 子查询不管是使用IN还是EXISTS，对外层SELECT对应的数据表均需要进行全表扫描，并且对于每行数据都需要执行一次子查询，所以如果该数据行表很大，则需要执行大量的子查询，即可能出现“大表驱动小表”，从而产生性能问题。
• 对于JOIN而言，由于可以通过“小表驱动大表”，所以可以一定程度上优化子查询。

三、总结

对于IN+子查询的SQL方式：

• 使用EXIST结合子查询效率会更高；
• 建议使用JOIN来优化。