mysql not in、left join、IS NULL、NOT EXISTS 效率问题记
网络编程 2021-07-05 15:27www.168986.cn编程入门
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)
知道以上三条语句的实际效果是相同的已经很久了,一直没有深究其间的效率对比。一直感觉上语句二是最快的。
今天工作上因为要对一个数千万行数据的库进行数据清除,需要删掉两千多万行数据。大量的用到了以上三条语句所要实现的功能。本来用的是语句一,结果是执行速度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 @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 结果
+----+--------------------+------------+----------------+---------------------------+------------+---------+------+------+--
----------------------------+
| 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那个字段各个行的数值的乘积,也就是笛卡尔积)
呢狼蚁网站SEO优化这个
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
放在外面就会
这个本身就是排列组合的一个可能
语句一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 @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 结果
+----+--------------------+------------+----------------+---------------------------+------------+---------+------+------+--
----------------------------+
| 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那个字段各个行的数值的乘积,也就是笛卡尔积)
呢狼蚁网站SEO优化这个
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
放在外面就会
这个本身就是排列组合的一个可能
编程语言
- 如何快速学会编程 如何快速学会ug编程
- 免费学编程的app 推荐12个免费学编程的好网站
- 电脑怎么编程:电脑怎么编程网咯游戏菜单图标
- 如何写代码新手教学 如何写代码新手教学手机
- 基础编程入门教程视频 基础编程入门教程视频华
- 编程演示:编程演示浦丰投针过程
- 乐高编程加盟 乐高积木编程加盟
- 跟我学plc编程 plc编程自学入门视频教程
- ug编程成航林总 ug编程实战视频
- 孩子学编程的好处和坏处
- 初学者学编程该从哪里开始 新手学编程从哪里入
- 慢走丝编程 慢走丝编程难学吗
- 国内十强少儿编程机构 中国少儿编程机构十强有
- 成人计算机速成培训班 成人计算机速成培训班办
- 孩子学编程网上课程哪家好 儿童学编程比较好的
- 代码编程教学入门软件 代码编程教程