SQL优化（三）：子查询和IN，EXISTS用法和优化方法

Love The Way You Lie 2022-04-24 04:04 549阅读 0赞

### 用法 ###

#### 1. 与IN结合使用 ####

*  子查询与IN结合使用时，通常通过子查询查询出某个表单列的值，然后作为外层的SELECT的IN查询的数据源，如下，查询今天进行了购物的用户列表，首先通过子查询在订单表t\_order查出所有今天进行了购物的用户的user\_id，然后在外层SELECT中对于用户表t\_user的每个用户都通过IN来判断自己的user\_id是否在里面：
    
        mysql> select * from t_user where id in (select user_id from t_order where buy_date=curdate());
        +----+------+----------------------------------+------------+--------+
        | id | name | password                         | email      | phone  |
        +----+------+----------------------------------+------------+--------+
        |  2 | xyz2 | 5838eec5e44b83f35c2763382b45e469 | 456@qq.com | 456789 |
        +----+------+----------------------------------+------------+--------+
 *  执行计划如下：
    
        mysql> explain select * from t_user where id in (select user_id from t_order where buy_date=curdate());
        +----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+---------------------------------+
        | id | select_type | table   | partitions | type | possible_keys                                 | key          | key_len | ref   | rows | filtered | Extra                           |
        +----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+---------------------------------+
        |  1 | SIMPLE      | t_user  | NULL       | ALL  | PRIMARY                                       | NULL         | NULL    | NULL  |    1 |   100.00 | NULL                            |
        |  1 | SIMPLE      | t_order | NULL       | ref  | idx_user_id,idx_user_id_buy_date,idx_buy_date | idx_buy_date | 3       | const |    1 |   100.00 | Using where; FirstMatch(t_user) |
        +----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+---------------------------------+
        2 rows in set, 1 warning (0.00 sec)

##### 性能分析：数据集合+O(N)线性时间复杂度 #####

*  对于外层SELECT对应用户表t\_user的每一行数据都要执行一次这个子查询，而这个子查询是需要返回一个数据集合而不是单条数据，然后再判断外层SELECT的当前数据行的该列的值是否在这个集合中，类似于O(N)的线性时间复杂度，如Java的集合的contains方法，所以性能是很低的，即MySQL需要返回的数据量大同时查询的时间复杂度高。

#### 2. 与EXISTS结合使用 ####

*  在外层SELECT中，除了可以通过IN来使用子查询的结果外，还可以通过EXISTS，如下：与IN不同的是，在子查询中外层的t\_user表需要在子查询内使用，在子查询内部才是进行实际的查询，而EXISTS的作用是根据EXISTS返回的结果，即boolean类型的true或false来决定是否需要返回该外层SELECT当前遍历到的结果。
    
        mysql> select * from t_user where exists (select * from t_order where t_user.id=t_order.user_id and t_order. buy_date=curdate());
        +----+------+----------------------------------+------------+--------+
        | id | name | password                         | email      | phone  |
        +----+------+----------------------------------+------------+--------+
        |  2 | xyz2 | 5838eec5e44b83f35c2763382b45e469 | 456@qq.com | 456789 |
        +----+------+----------------------------------+------------+--------+
        1 row in set (0.00 sec)
 *  执行计划如下：
    
        mysql> explain select * from t_user where exists (select * from t_order where t_user.id=t_order.user_id and t_order. buy_date=curdate());
        +----+--------------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+-------------+
        | id | select_type        | table   | partitions | type | possible_keys                                 | key          | key_len | ref   | rows | filtered | Extra       |
        +----+--------------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+-------------+
        |  1 | PRIMARY            | t_user  | NULL       | ALL  | NULL                                          | NULL         | NULL    | NULL  |    1 |   100.00 | Using where |
        |  2 | DEPENDENT SUBQUERY | t_order | NULL       | ref  | idx_user_id,idx_user_id_buy_date,idx_buy_date | idx_buy_date | 3       | const |    1 |    25.00 | Using where |
        +----+--------------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+-------------+
        2 rows in set, 2 warnings (0.00 sec)

##### 性能分析：True或False的boolean值 + O(1)常量时间复杂度 #####

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

### 优化方法 ###

#### 1. 使用EXISTS替代IN ####

*  由以上的分析可知，在使用子查询时，将EXISTS结合使用比与IN结合使用效率更高，所以可以使用EXISTS来替代IN。

#### 2. 使用JOIN替代子查询 ####

*  子查询不管是使用IN还是EXISTS，对外层SELECT对应的数据表均需要进行全表扫描，并且对于每行数据都需要执行一次子查询，所以如果该数据行表很大，则需要执行大量的子查询，，即可能出现“大表驱动小表”，从而产生性能问题。
 *  对于JOIN而言，由于可以通过“小表驱动大表”，并且进行JOIN的列都加了索引，所以可以一定程度上优化子查询，如下：还是查询今天进行了购物的用户：
    
        mysql> select * from t_user inner join t_order where t_user.id=t_order.user_id and t_order.buy_date=curdate();
        +----+------+----------------------------------+------------+--------+----------+---------+------+------------+
        | id | name | password                         | email      | phone  | order_id | user_id | cost | buy_date   |
        +----+------+----------------------------------+------------+--------+----------+---------+------+------------+
        |  2 | xyz2 | 5838eec5e44b83f35c2763382b45e469 | 456@qq.com | 456789 |        5 |       2 | 1000 | 2019-04-14 |
        +----+------+----------------------------------+------------+--------+----------+---------+------+------------+
        1 row in set (0.00 sec)
        
        mysql> explain select * from t_user inner join t_order where t_user.id=t_order.user_id and t_order.buy_date=curdate();
        +----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+-------------+
        | id | select_type | table   | partitions | type | possible_keys                                 | key          | key_len | ref   | rows | filtered | Extra       |
        +----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+-------------+
        |  1 | SIMPLE      | t_user  | NULL       | ALL  | PRIMARY                                       | NULL         | NULL    | NULL  |    1 |   100.00 | NULL        |
        |  1 | SIMPLE      | t_order | NULL       | ref  | idx_user_id,idx_user_id_buy_date,idx_buy_date | idx_buy_date | 3       | const |    1 |   100.00 | Using where |
        +----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+-------------+
        2 rows in set, 1 warning (0.00 sec)

#### 3. 子查询和JOIN均不用，程序控制 ####

*  如果数据表较大时，使用子查询和JOIN效率均不高，并且对于JOIN而言，会影响分库分表的使用，故可以对需要关联的表分别查询出来，然后在程序中进行数据拼接组装操作，从而减轻数据库的压力，因为数据库通常是应用的性能瓶颈，而应用程序通常可以进行分布式和集群部署来拓展。
 *  除此之外，对于拼接的结果可以缓存到分布式缓存，如Redis中，从而可以重复使用。

发表评论取消回复

表情：

评论列表（有 0 条评论，549人围观）

还没有评论，来说两句吧...

相关阅读

相关 SQL查询优化

目录一、获取有性能问题SQL的三种方法二、慢查询日志介绍 1、使用慢查询日志获取有性能问题的SQL 三、慢查询日志实例四、实时获取性能问题五、SQL

水深无声/ 2023年03月14日 08:19/ 0 赞/ 8 阅读

相关 MySQL | 使用 limit 优化查询和防止SQL被优化

-------------------- 文章目录查询优化 1.1 最大值和最小值的优化 1.2 优化 limit 分页

桃扇骨/ 2022年10月30日 09:23/ 0 赞/ 2 阅读

相关 SQL查询优化

SQL查询优化主要针对SQL查询的优化介绍： 1. 慢查询 2. 索引 3. 拆分表全文索引主要是针对文件、文本的检索，比如文章。全文索引针对MyISA

拼搏现实的明天。/ 2022年10月02日 14:59/ 0 赞/ 261 阅读

相关性能优化和SQL优化

一、性能调优手段 1、配置参数调优 2、应用算法优化 3、GC内存调优二、集群调优核心：以数据位中心，均衡并发，高效计算三、调优工具 Web UI、nMon

旧城等待，/ 2022年06月03日 01:36/ 0 赞/ 518 阅读

相关 MySQL学习（九）：SQL查询语句的用法和优化

一、概述 MySQL的性能优化可以从机器硬件，如磁盘，内存等；MySQL服务器配置，如线程数，查询缓存等；MySQL的主从分离和分库分表等；SQL语句优化等。其中S

阳光穿透心脏的1/2处/ 2022年04月24日 04:10/ 0 赞/ 194 阅读

相关 SQL优化（三）：子查询和IN，EXISTS用法和优化方法

用法 1. 与IN结合使用子查询与IN结合使用时，通常通过子查询查询出某个表单列的值，然后作为外层的SELECT的IN查询的数据源，如下，查询今天进行了购物

Love The Way You Lie/ 2022年04月24日 04:04/ 0 赞/ 550 阅读

相关 SQL Server查询优化方法

SQL Server查询优化方法(查询速度慢的原因很多，常见如下几种) 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成

左手的ㄟ右手/ 2022年04月10日 13:44/ 0 赞/ 321 阅读

相关常用sql优化方法

1.尽可能建立索引，包括条件列，连接列，外键列； 2.尽可能让where中的列顺序和复合索引中的列顺序一样； 3.尽可能不要使用select \ ，而只列出自己需要的字段列

女爷i/ 2022年03月19日 03:18/ 0 赞/ 319 阅读

相关 SQL优化（一）：MySQL多表查询FROM和JOIN的用法与性能优化

一、概述 from和join均是用于指定需要从哪些表查询数据，from可以是一个表或多个表，如果是多个表则是生成一个笛卡尔集，会涉及到大量数据。所以通常在涉及到多个

喜欢ヅ旅行/ 2022年02月21日 13:20/ 0 赞/ 1291 阅读

相关 MySQL查询优化和配置优化

查询优化 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行

太过爱你忘了你带给我的痛/ 2021年12月05日 02:15/ 0 赞/ 343 阅读