mysql not in 和 left join 效率问题记录

最新推荐文章于 2023-12-25 11:33:45 发布
最新推荐文章于 2023-12-25 11:33:45 发布
阅读量1.4w 收藏 3
点赞数
分类专栏： 数据库相关 文章标签： join mysql null subquery table 数据结构
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ethanzhao/article/details/4467022
版权

声明：感谢 laserhe, denniswwh ， ACMAIN_CHM ， vinsonshen 的热心帮助

首先说明该条sql的功能是查询集合a不在集合b的数据。
not in的写法
select add_tb.RUID
from (select distinct RUID
      from UserMsg
      where SubjectID =12
      and CreateTime>'2009-8-14 15:30:00'
      and CreateTime<='2009-8-17 16:00:00'
) add_tb
  where add_tb.RUID
  not in (select distinct RUID
          from UserMsg
          where SubjectID =12
      and CreateTime<'2009-8-14 15:30:00'
)
返回444行记录用时 0.07sec
explain 结果
+----+--------------------+------------+----------------+---------------------------+------------+---------+------+------+--

----------------------------+
| id | select_type        | table      | type           | possible_keys             | key        | key_len | ref  | rows |

Extra                        |
+----+--------------------+------------+----------------+---------------------------+------------+---------+------+------+--

----------------------------+
|  1 | PRIMARY            | <derived2> | ALL            | NULL                      | NULL       |    NULL | NULL |  452 |

Using where                  |
|  3 | DEPENDENT SUBQUERY | UserMsg    | index_subquery | RUID,SubjectID,CreateTime | RUID       |      96 | func |    2 |

Using index; Using where     |
|  2 | DERIVED            | UserMsg    | range          | SubjectID,CreateTime      | CreateTime |       9 | NULL | 1857 |

Using where; Using temporary |
+----+--------------------+------------+----------------+---------------------------+------------+---------+------+------+--

----------------------------+
分析:该条查询速度快原因为id=2的sql查询出来的结果比较少，所以id=1sql所以运行速度比较快，id=2的使用了临时表，不知道这个时候是否使用索引？
其中一种left join
select a.ruid,b.ruid
from(select distinct RUID
     from UserMsg
     where SubjectID =12
     and CreateTime >= '2009-8-14 15:30:00'
     and CreateTime<='2009-8-17 16:00:00'
) a  left join  (
    select distinct RUID
    from UserMsg
    where SubjectID =12 and CreateTime< '2009-8-14 15:30:00' 
) b on a.ruid = b.ruid
where b.ruid is null
返回444行记录用时 0.39sec
explain 结果
+----+-------------+------------+-------+----------------------+------------+---------+------+------+-----------------------

-------+
| id | select_type | table      | type  | possible_keys        | key        | key_len | ref  | rows | Extra                  

     |
+----+-------------+------------+-------+----------------------+------------+---------+------+------+-----------------------

-------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL                 | NULL       |    NULL | NULL |  452 |                        

     |
|  1 | PRIMARY     | <derived3> | ALL   | NULL                 | NULL       |    NULL | NULL | 1112 | Using where; Not exists

     |
|  3 | DERIVED     | UserMsg    | ref   | SubjectID,CreateTime | SubjectID  |       5 |      | 6667 | Using where; Using

temporary |
|  2 | DERIVED     | UserMsg    | range | SubjectID,CreateTime | CreateTime |       9 | NULL | 1838 | Using where; Using

temporary |
+----+-------------+------------+-------+----------------------+------------+---------+------+------+-----------------------

-------+
分析:使用了两个临时表，并且两个临时表做了笛卡尔积，导致不能使用索引并且数据量很大
另外一种left join
select distinct a.RUID
from UserMsg a
left join UserMsg b
    on a.ruid = b.ruid
    and b.subjectID =12 and b.createTime < '2009-8-14 15:30:00'
where a.subjectID =12
and a.createTime >= '2009-8-14 15:30:00'
and a.createtime <='2009-8-17 16:00:00'
and b.ruid is null;
返回444行记录用时 0.07sec
explain 结果
+----+-------------+-------+-------+---------------------------+------------+---------+--------------+------+---------------

--------------------+
| id | select_type | table | type  | possible_keys             | key        | key_len | ref          | rows | Extra          

                  |
+----+-------------+-------+-------+---------------------------+------------+---------+--------------+------+---------------

--------------------+
|  1 | SIMPLE      | a     | range | SubjectID,CreateTime      | CreateTime |       9 | NULL         | 1839 | Using where;

Using temporary      |
|  1 | SIMPLE      | b     | ref   | RUID,SubjectID,CreateTime | RUID       |      96 | dream.a.RUID |    2 | Using where;

Not exists; Distinct |
+----+-------------+-------+-------+---------------------------+------------+---------+--------------+------+---------------

--------------------+
分析：两次查询都是用上了索引，并且查询时同时进行的，所以查询效率应该很高
使用not exists的sql
select distinct a.ruid
from UserMsg a
where a.subjectID =12
and a.createTime >= '2009-8-14 15:30:00'
and a.createTime <='2009-8-17 16:00:00'
and not exists (
    select distinct RUID
    from UserMsg
    where subjectID =12 and createTime  < '2009-8-14 15:30:00'
    and ruid=a.ruid
)
返回444行记录用时 0.08sec
explain 结果
+----+--------------------+---------+-------+---------------------------+------------+---------+--------------+------+------

------------------------+
| id | select_type        | table   | type  | possible_keys             | key        | key_len | ref          | rows | Extra 

                      |
+----+--------------------+---------+-------+---------------------------+------------+---------+--------------+------+------

------------------------+
|  1 | PRIMARY            | a       | range | SubjectID,CreateTime      | CreateTime |       9 | NULL         | 1839 | Using

where; Using temporary |
|  2 | DEPENDENT SUBQUERY | UserMsg | ref   | RUID,SubjectID,CreateTime | RUID       |      96 | dream.a.RUID |    2 | Using

where                  |
+----+--------------------+---------+-------+---------------------------+------------+---------+--------------+------+------

------------------------+
分析：同上基本上是一样的，只是分解了2个查询顺序执行，查询效率低于第3个

为了验证数据查询效率，将上述查询中的subjectID =12的限制条件去掉，结果统计查询时间如下
0.20s
21.31s
0.25s
0.43s

laserhe帮忙分析问题总结
select a.ruid,b.ruid 
from(    select distinct RUID     
    from UserMsg      
    where CreateTime >= '2009-8-14 15:30:00'      
    and CreateTime<='2009-8-17 16:00:00'
) a      left join  UserMsg  b
    on a.ruid = b.ruid 
    and b.createTime < '2009-8-14 15:30:00'
where b.ruid is null;
执行时间0.13s
+----+-------------+------------+-------+-----------------+------------+---------+--------+------+--------------------------

----+
| id | select_type | table      | type  | possible_keys   | key        | key_len | ref    | rows | Extra                     

  |
+----+-------------+------------+-------+-----------------+------------+---------+--------+------+--------------------------

----+
|  1 | PRIMARY     | <derived2> | ALL   | NULL            | NULL       |    NULL | NULL   | 1248 |                           

  |
|  1 | PRIMARY     | b          | ref   | RUID,CreateTime | RUID       |      96 | a.RUID |    2 | Using where; Not exists   

  |
|  2 | DERIVED     | UserMsg    | range | CreateTime      | CreateTime |       9 | NULL   | 3553 | Using where; Using

temporary |
+----+-------------+------------+-------+-----------------+------------+---------+--------+------+--------------------------

----+
执行效率类似与not in的效率

数据库优化的基本原则：让笛卡尔积发生在尽可能小的集合之间，mysql在join的时候可以直接通过索引来扫描，而嵌入到子查询里头，查询规

划器就不晓得用合适的索引了。
一个SQL在数据库里是这么优化的：首先SQL会分析成一堆分析树，一个树状数据结构，然后在这个数据结构里，查询规划器会查找有没有合适

的索引，然后根据具体情况做一个排列组合，然后计算这个排列组合中的每一种的开销（类似explain的输出的计算机可读版本），然后比较里

面开销最小的，选取并执行之。那么：
explain select a.ruid,b.ruid  from(select distinct RUID      from UserMsg       where CreateTime >= '2009-8-14 15:30:00'     

 and CreateTime<='2009-8-17 16:00:00' ) a  left join  UserMsg  b on a.ruid = b.ruid  and b.createTime < '2009-8-14 15:30:00'

where b.ruid is null;
和
explain select add_tb.RUID
    -> from (select distinct RUID
    ->       from UserMsg
    ->       where  CreateTime>'2009-8-14 15:30:00'
    ->       and CreateTime<='2009-8-17 16:00:00'
    -> ) add_tb
    ->   where add_tb.RUID
    ->   not in (select distinct RUID
    ->           from UserMsg
    ->           where CreateTime<'2009-8-14 15:30:00'
    -> );
explain
+----+--------------------+------------+----------------+-----------------+------------+---------+------+------+------------

------------------+
| id | select_type        | table      | type           | possible_keys   | key        | key_len | ref  | rows | Extra       

                |
+----+--------------------+------------+----------------+-----------------+------------+---------+------+------+------------

------------------+
|  1 | PRIMARY            | <derived2> | ALL            | NULL            | NULL       |    NULL | NULL | 1248 | Using where 

                |
|  3 | DEPENDENT SUBQUERY | UserMsg    | index_subquery | RUID,CreateTime | RUID       |      96 | func |    2 | Using index;

Using where     |
|  2 | DERIVED            | UserMsg    | range          | CreateTime      | CreateTime |       9 | NULL | 3509 | Using where;

Using temporary |
+----+--------------------+------------+----------------+-----------------+------------+---------+------+------+------------

------------------+
开销是完全一样的，开销可以从 rows 那个字段得出（基本上是rows那个字段各个行的数值的乘积，也就是笛卡尔积）
但是呢：下面这个：
explain select a.ruid,b.ruid  from(select distinct RUID      from UserMsg       where CreateTime >= '2009-8-14 15:30:00'     

 and CreateTime<='2009-8-17 16:00:00' ) a  left join  (  select distinct RUID  from UserMsg where createTime < '2009-8-14

15:30:00' ) b on a.ruid = b.ruid  where b.ruid is null;
执行时间21.31s
+----+-------------+------------+-------+---------------+------------+---------+------+-------+-----------------------------

-+
| id | select_type | table      | type  | possible_keys | key        | key_len | ref  | rows  | Extra                       

|
+----+-------------+------------+-------+---------------+------------+---------+------+-------+-----------------------------

-+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL       |    NULL | NULL |  1248 |                             

|
|  1 | PRIMARY     | <derived3> | ALL   | NULL          | NULL       |    NULL | NULL | 30308 | Using where; Not exists     

|
|  3 | DERIVED     | UserMsg    | ALL   | CreateTime    | NULL       |    NULL | NULL | 69366 | Using where; Using temporary

|
|  2 | DERIVED     | UserMsg    | range | CreateTime    | CreateTime |       9 | NULL |  3510 | Using where; Using temporary

|
+----+-------------+------------+-------+---------------+------------+---------+------+-------+-----------------------------

-+
我就有些不明白
为何是四行
并且中间两行巨大无比
按理说
查询规划器应该能把这个查询优化得跟前面的两个一样的
（至少在我熟悉的pgsql数据库里我有信心是一样的）
但mysql里头不是
 所以我感觉查询规划器里头可能还是糙了点
 我前面说过优化的基本原则就是，让笛卡尔积发生在尽可能小的集合之间
那么上面最后一种写法至少没有违反这个原则
虽然b 表因为符合条件的非常多，基本上不会用索引
但是并不应该妨碍查询优化器看到外面的join on条件，从而和前面两个SQL一样，选取主键进行join
 不过我前面说过查询规划器的作用
 理论上来讲
 遍历一遍所有可能，计算一下开销
 是合理的
 我感觉这里最后一种写法没有遍历完整所有可能
 可能的原因是子查询的实现还是比较简单？
 子查询对数据库的确是个挑战
 因为基本都是递归的东西
 所以在这个环节有点毛病并不奇怪
 其实你仔细想想，最后一种写法无非是我们第一种写法的一个变种，关键在表b的where 条件放在哪里
 放在里面，就不会用索引去join
 放在外面就会
 这个本身就是排列组合的一个可能

ethanzhao
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
记一次mysql优化 not in, not exits, left join 子查询
qq_39504351的博客
08-24 1097
在某次业务逻辑中写到如下逻辑：查询学校级别的未拦截弹窗记录。而弹窗设备表（t_block_blacklist_device）有四种类型（1:学校级黑名单 2:设备级别黑名单 3:略 4:学校级白名单 注：从来没有被加入黑名单或白名单不存在这张表中）。弹窗弹出次数表为t_block_count。于是写下以下sql：查询弹窗弹出次数表的次数，排除掉出现在弹窗设备表中学校级的弹窗id（c_block_...
Mysql left和in性能_exits， in， left join性能比较
weixin_28965077的博客
01-27 3459
exits in left join性能比较 ，笔者使用的是MYSQL数据库，这三个关键字方法在其他的关系数据库里也是大同小异，如果各种有兴趣，自行比较。我这里有一个249_account 表，总数为8538507 249001_account_temp表 总数为 8242734其中有部分数据，249_account里有，249_account_temp表里没有，要求找出249_accoun...
mysql/sqlserver中exists与left join效率对比
最新发布
微差的博客
12-25 1526
在exists中，利用a表的索引与b表的主键匹配迅速找出数据并判断条件是否符合，索引匹配完成，数据结果也就有了。根据下面两张图中的执行计划的结果来看，很明显，left join 比exists多执行一步，使用exists时，从头到尾扫描完索引进行条件检索，数据结果也就有了，而left join中，从头到尾扫描完索引检索后只是刚拿到left join 的结果，还没拿到最终结果，紧跟着还要执行联合查询，因为两个结果集inner join时没有索引，走的全表扫描，因此慢，比exists的方式多执行了12秒。
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
热门推荐
赵延东的一亩三分地
02-08 9万+
从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，还是那就话，别死记网上结论、在使用内外关联时，特别是简写方式时记住关联条件不要乱放！【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
【sql】not in和left join，left join替换not in，数据库优化
mudarn的博客
01-24 2407
（1）not in 理解 select * from student where classID in (1,2,3) 找出班级ID为1，2， 3的学生 not in (1,2,3) 就是不在这个范围内的。 （2）巧用left join代替not in select * from a where a.id not in (      select id from b ) 找出不和b表id相等...
python调用rest接口_【Python】使用Python调用Rest接口
weixin_39789206的博客
12-03 459
用SpringBoot写了一个几个简单的Rest接口，对一个计数器进行查询，设置和增加，具体可以看：废话不多说，直接上Python代码：1 # -*- coding: utf-8 -*- 2 import json 3 4 import requests 5 6 REQUEST_URL = "http://localhost:8080/me/count" 7 HEADER = {'...
mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录
09-11
mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录，需要的朋友可以参考下。
MySQL的LEFT JOIN表连接的进阶学习教程
12-15
比如说在LEFT JOIN查询里，一般来说左表就是主表，但这只是经验之谈，很多时候经验主义是靠不住的，为了说明问题，先来个例子，建两个演示用的表categories和posts： CREATE TABLE IF NOT EXISTS `categories` ( `...
MySql如何使用not in实现优化
12-14
最近项目上用select查询时使用到了not in来排除用不到的主键id一开始使用的sql如下： select 　s.SORT_ID， ...表中的数据较多时这个sql的执行时间较长、执行效率低，在网上找资料说可以用 left join进
MySQL联表查询基本操作之left-join常见的坑
01-19
本篇文章就 mysql 常用联表查询复现常见的坑。 基础环境 建表语句 DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_name` VARCHAR(50) DEFAULT NULL COMMENT '...
mysql join not in_mysql not in 和 left join比较
weixin_35554273的博客
01-18 125
mysql> select User.userName from User where userId not in (select userId from Article );+----------+| userName |+----------+| xiaolin |+----------+1 row in set (0.03 sec)mysql> explain select U...
mysql not exists 效率高_mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录
weixin_42419782的博客
01-25 770
语句一：select count(*) from A where A.a not in (select a from B)语句二：select count(*) from A left join B on A.a = B.a where B.a is null语句三：select count(*) from A where not exists (select a from B where A.a...
mysql的exists与inner join 和 not exists与 left join 性能差别惊人
ESR 研发中心
07-21 1125
由于客户数据量越来越大，在实践中让我发现mysql的exists与inner join 和 not exists与 left join 性能差别惊人。 我们一般在做数据插入时，想插入不重复的数据，或者盘点数据在一个表，另一个表否有存在相同的数据会用not exists和exists，例如： insert into t1(a1) select b1 from t2 where not exi...
Mysql join效率_MYSQl left join 联合查询效率分析
weixin_28831341的博客
01-27 573
user表:id | name---------1 | libk2 | zyfon3 | daodaouser_action表:user_id | action---------------1 | jump1 | kick1 | jump2 | run4 | swimsql:select id, name, action from user as uleft join user_action a ...
SQL性能优化
weixin_30408165的博客
08-10 147
SQL性能优化 Oracle 优化器RBO， CBORBO 基于规则的优化器 oracle 10g开始，已经丢弃RBOCBO 基于成本的优化器 oracle 8中开始引入的 Oracle 解析器按照从右到左的顺序处理FROM 字句中的表名，FROM 中写在最后的表（基础表， drving t...
mysql exists 效率_Mysql之exists和inner join效率问题（1）
weixin_35990581的博客
01-19 3939
使用两张大小差距比较大的表来进行测试前言：在使用laravel的过程中发现whereHas实现的方式是exists子查询，又听说exists尽量少使用，容易导致慢查询，于是就有了接下来两篇文章。两张表结构如下：app_userapp_feedback(user_id列建立索引)很明显app_user表要比app_feedback表要大--------------------------------...
inner join 和 exists 效率_掌握 MySQL 这 19 个骚操作，效率至少提高3倍
weixin_39686192的博客
12-01 690
1、EXPLAIN做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。下面来个简单的示例，标注(1、2、3、4、5)我们要重点关注的数据：type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。key_len列，索引长度。rows列，扫描行数。该值是个预估值。extra列，详细说...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值