mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录

2024-10-03 13:14:38

NOT IN、JOIN、IS NULL、NOT EXISTS效率对比

语句一：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 = B.a)

知道以上三条语句的实际效果是相同的已经很久了，但是一直没有深究其间的效率对比。一直感觉上语句二是最快的。
今天工作上因为要对一个数千万行数据的库进行数据清除，需要删掉两千多万行数据。大量的用到了以上三条语句所要实现的功能。本来用的是语句一，但是结果是执行速度1个小时32分，日志文件占用21GB。时间上虽然可以接受，但是对硬盘空间的占用确是个问题。因此将所有的语句一都换成语句二。本以为会更快。没想到执行40多分钟后，第一批50000行都没有删掉，反而让SQL SERVER崩溃掉了，结果令人诧异。试了试单独执行这条语句，查询近一千万行的表，语句一用了4秒，语句二却用了18秒，差距很大。语句三的效率与语句一接近。

第二种写法是大忌，应该尽量避免。第一种和第三种写法本质上几乎一样。

假设buffer pool足够大，写法二相对于写法一来说存在以下几点不足：
（1）left join本身更耗资源（需要更多资源来处理产生的中间结果集）
（2）left join的中间结果集的规模不会比表A小
（3）写法二还需要对left join产生的中间结果做is null的条件筛选，而写法一则在两个集合join的同时完成了筛选，这部分开销是额外的

这三点综合起来，在处理海量数据时就会产生比较明显的区别（主要是内存和CPU上的开销）。我怀疑楼主在测试时buffer pool可能已经处于饱和状态，这样的话，写法二的那些额外开销不得不借助磁盘上的虚拟内存，在SQL Server做换页时，由于涉及到较慢的I/O操作因此这种差距会更加明显。

关于日志文件过大，这也是正常的，因为删除的记录多嘛。可以根据数据库的用途考虑将恢复模型设为simple，或者在删除结束后将日志truncate掉并把文件shrink下来。

因为以前曾经作过一个对这个库进行无条件删除的脚本，就是要删除数据量较大的表中的所有数据，但是因为客户要求，不能使用truncate table，怕破坏已有的库结构。所以只能用delete删，当时也遇到了日志文件过大的问题，当时采用的方法是分批删除，在SQL2K中用set rowcount @chunk，在SQL2K5中用delete top @chunk。这样的操作不仅使删除时间大大减少，而且让日志量大大减少，只增长了1G左右。
但是这次清除数据的工作需要加上条件，就是delete A from A where ....后面有条件的。再次使用分批删除的方法，却已经没效果了。
不知您知不知道这是为什么。

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

首先说明该条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 结果
+----+--------------------+------------+----------------+---------------------------+------------+---------+------+------+--

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

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 结果
+----+-------------+------------+-------+----------------------+------------+---------+------+------+-----------------------

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

|
| 3 | DERIVED | UserMsg | ref | SubjectID,CreateTime | SubjectID | 5 | | 6667 | 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 结果
+----+-------------+-------+-------+---------------------------+------------+---------+--------------+------+---------------

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

--------------------+
| 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 结果
+----+--------------------+---------+-------+---------------------------+------------+---------+--------------+------+------

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

------------------------+
| 1 | PRIMARY | a | range | SubjectID,CreateTime | CreateTime | 9 | NULL | 1839 | 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
+----+-------------+------------+-------+-----------------+------------+---------+--------+------+--------------------------

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

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

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
+----+--------------------+------------+----------------+-----------------+------------+---------+------+------+------------

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

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
+----+-------------+------------+-------+---------------+------------+---------+------+-------+-----------------------------

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

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

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

提高MySQL中InnoDB表BLOB列的存储效率的教程

首先,介绍下关于InnoDB引擎存储格式的几个要点: 1.InnoDB可以选择使用共享表空间或者是独立表空间方式,建议使用独立表空间,便于管理.维护.启用 innodb_file_per_table 选项,5.5以后可以在线动态修改生效,并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间,早于5.5的版本,修改完这个选项后,需要重启才能生效: 2.InnoDB的data page默认16KB,5.6版本以后,新增选项 innodb_page_size
MySQL利用profile分析慢sql详解（group left join效率高于子查询）

使用profile来分析慢sql mysql 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项资源的使用情况.分析器可以更好的展示出不良 SQL 的性能问题所在. 最近遇到一个查询比较慢的sql语句,用了子查询,大概需要0.8秒左右,这个消耗时间比较长,严重影响了性能,所以需要进行优化.单独查询单表或者子查询记录都很快,下面来看看详细的介绍. 开启profile mysql> show profiles; -- 查看是否开启 Empty set, 1 warning (0.00
MySQL中使用or、in与union all在查询命令下的效率对比

OR.in和union all 查询效率到底哪个快? 网上很多的声音都是说union all 快于 or.in,因为or.in会导致全表扫描,他们给出了很多的实例. 但真的union all真的快于or.in? EXPLAIN SELECT * from employees where employees.first_NAME ='Georgi' UNION ALL SELECT * from employees where employees.first_NAME ='Bezalel' 这条语
浅谈mysql的子查询联合与in的效率

最近的产品测试发现一个问题,当并发数量小于10时,响应时间可以维持在100毫秒以内.但是当并发数到达30个时,响应时间就超过1秒.这太不能接受了,要求是通过1秒中并发100个. 经过检测发现,时间主要是耗在其中的一个存储过程中.把存储过程的语句一条一条的过一遍也没有发现明显的不合理.因为mysql本身不能提供毫秒级别的时间,google了一个mysql的能提供毫秒的时间函数,再做测试,做了一个定位.发现是其中一条语句,语句是这个样子: select .... from A, B where ..
mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录

NOT IN.JOIN.IS NULL.NOT EXISTS效率对比语句一: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 = B.a) 知道以上三
mysql使用from与join两表查询的区别总结

前言在mysql中,多表连接查询是很常见的需求,在使用多表查询时,可以from多个表,也可以使用join连接连个表这两种查询有什么区别?哪种查询的效率更高呢? 带着这些疑问,决定动手试试 1.先在本地的mysql上先建两个表one和two one表 CREATE TABLE `one` ( `id` int(0) NOT NULL AUTO_INCREMENT, `one` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB
mysql中各种常见join连表查询实例总结

本文实例讲述了mysql中各种常见join连表查询.分享给大家供大家参考,具体如下: 通常我们需要连接多个表查询数据,以获取想要的结果. 一.连接可以分为三类: (1) 内连接:join,inner join (2) 外连接:left join,left outer join,right join,right outer join,union,union all (3) 交叉连接:cross join 二.准备需要演示的表: CREATE TABLE `a` ( `id` int(11) uns
mysql多个left join连接查询用法分析

本文实例讲述了mysql多个left join连接查询用法.分享给大家供大家参考,具体如下: mysql查询时需要连接多个表时,比如查询订单的商品表,需要查询商品的其他信息,其他信息不在订单的商品表,需要连接其他库的表,但是连接的条件基本都是商品ID就可以了,先给一个错误语句(查询之间的嵌套,效率很低): SELECT A.order_id, A.wid, A.work_name, A.supply_price, A.sell_price, A.total_num, A.sell_profit,
MySQL Innodb 存储结构和存储Null值用法详解

背景: 表空间:INNODB 所有数据都存在表空间当中(共享表空间),要是开启innodb_file_per_table,则每张表的数据会存到单独的一个表空间内(独享表空间). 独享表空间包括:数据,索引,插入缓存,数据字典.共享表空间包括:Undo信息(不会回收<物理空间上>),双写缓存信息,事务信息等. 段(segment):组成表空间,有区组成. 区(extent):有64个连续的页组成.每个页16K,总共1M.对于大的数据段,每次最后可申请4个区. 页(page):是INNODB 磁盘
mysql高效查询left join和group by(加索引)

mysql高效查询 mysql牺牲了group by来增加left join的速度(前提是加了索引). user表:10万数据实例1: 200秒左右 SELECT U.id, A.favorite_count FROM (SELECT id from user) U LEFT JOIN ( -- 点赞数 SELECT favorite_by AS user_id, SUM(favorite_count) AS favorite_count FROM favorite GROUP BY favo
MYSQL数据库基础之Join操作原理

Join使用的是Nested-Loop Join算法,Nested-Loop Join有三种 select * from t1 join t2 on t1.a = t2.a; -- a 100条数据, b 1000条数据 Simple Nested-Loop Join 会遍历t1全表,t1作为驱动表,t1中的每一条数据都会到t2中做一次全表查询,该过程会比较100*1000次. 每次在t2中做全表查询时,全表扫描可就不保证在内存里了,Buffer Pool会淘汰,有可能在磁盘. Block Ne
MySQL中CONCAT()函数拼接出现NULL的问题解决

项目中查询用到了concat()拼接函数,在此查询中出现了拼接的字段为null的情况,拼接结果为null在应用层报了空指针异常. SELECT CONCAT('1,',NULL,'2') result; SELECT CONCAT('1,','','2') result; 通过实践证明CONCAT()函数拼接时如果拼接的参数中有NULL时,结果为NULL. 使用以下方式来解决方法一:使用IFNULL函数如果是NULL将其置为''空字符串. SELECT CONCAT('1,',IFNULL(N
Mysql体系化探讨令人头疼的JOIN运算

目录前言一图总览 SQL中的JOIN SQL对JOIN的定义 JOIN定义 JOIN分类等值JOIN 空值处理规则下分类 JOIN的实现笨办法数据库对于JOIN优化分布式系统下JOIN 等值JOIN的剖析三种等值JOIN: 外键关联同维表主子表 JOIN的语法简化外键属性化同维表等同化子表集合化维度对齐语法解决关联查询多表JOIN问题简化JOIN运算好处: 关联查询外键预关联全内存下外键关联情况进一步的外键关联外键序号化借助集群的力量解决大维表问题. 有
MySQL实战教程之Join语句执行流程

目录 Join语句执行流程一.Index Nested-Loop Join 二.Simple Nested-Loop Join 三.Block Nested-Loop Join 四.总结 Join语句执行流程 Hi,我是阿昌,今天学习记录的是关于Join语句执行流程的内容. 在实际生产中,关于 join 语句使用的问题,一般会集中在以下两类: 不让使用 join,使用 join 有什么问题呢? 如果有两个大小不同的表做 join,应该用哪个表做驱动表呢? 创建两个表 t1 和 t2 来说明.

mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录

相关推荐

随机推荐