☆* o(≧▽≦)o *☆嗨~我是小奥🍹
📄📄📄个人博客:小奥的博客
📄📄📄CSDN:个人CSDN
📙📙📙Github:传送门
📅📅📅面经分享(牛客主页):传送门
🍹文章作者技术和水平有限,如果文中出现错误,希望大家多多指正!
📜 如果觉得内容还不错,欢迎点赞收藏关注哟! ??
MySQL 4.1引入了对子查询的支持,即嵌套在其他查询中的查询。
比如给定一个订单信息查询的场景:
订单存储在两个表中:
orders表不存储客户信息,它只存储客户的ID。实际的客户信息存储在customers表中。
假如需要列出订购物品TNT2的所有客户,应该怎样检索?
上述每个步骤都可以单独作为一个查询来执行。可以把一条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结合使用时,通常通过子查询查询出某个表单列的值,然后作为外层的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|
+--+------------+-------------+----------+------+-------------+-------+-------+-------------------+----+--------+-----------+
分析:
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|
+--+------------+-------------+----------+------+-------------+-------+-------+-------------------+----+--------+-----------+
分析:
user.id = user_realname.user_id
,故如果子查询的结果不为空,即存在数据,则外层SELECT对应的user表的当前数据行肯定是符合要求的,故该子查询实际上并不返回任何数据,而是返回值True或False,不需要与IN一样返回一个数据集合。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+子查询的SQL方式: