提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
文章目录
in 和 exists
在涉及子查询的过程中,通常使用in或者exists来进行连接,但是在一些情况下二者的性能和查询结果又显著不同,在这里加以记录。
一、场景一: 大批量数据的关联子查询
数据库简单介绍:一个简单的订单表,数据有1500000条。
(root@localhost) [dbt3]>desc orders;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| o_orderkey | int(11) | NO | PRI | NULL | |
| o_custkey | int(11) | YES | MUL | NULL | |
| o_orderstatus | char(1) | YES | | NULL | |
| o_totalprice | double | YES | | NULL | |
| o_orderDATE | date | YES | MUL | NULL | |
| o_orderpriority | char(15) | YES | | NULL | |
| o_clerk | char(15) | YES | | NULL | |
| o_shippriority | int(11) | YES | | NULL | |
| o_comment | varchar(79) | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
需求说明:查询每月最后一天的详细销售情况。
in的实现方法
# 下述例子为不相关子查询,用时短,效率高,推荐使用
SELECT
*
FROM
orders o1
WHERE o_orderDATE IN
(SELECT
MAX(o_orderDATE) AS maxdate
FROM
orders
GROUP BY (DATE_FORMAT(o_orderDATE, '%Y%M')))
ORDER BY o_orderDATE ;
查询性能为:
执行耗时 : 1.719 sec
传送时间 : 0.296 sec
总耗时 : 2.016 sec
exists方法实现
SELECT
o_clerk,
o_orderdate
FROM
orders o1
WHERE EXISTS
(SELECT
MAX(o_orderDATE) AS maxdate
FROM
orders
GROUP BY (DATE_FORMAT(o_orderDATE, '%Y%M'))
HAVING o1.`o_orderDATE` = maxdate)
ORDER BY o_orderDATE ;
查询不到具体内容,耗时及其严重。
结果比较
在这种大规模的数据上,若利用exists进行相关子查询,每一条外部的查询都要和所有的1500000进行配对比较是非常不现实的,当遇到这种情况时,应当使用in进行非相关子查询。
二、场景二: not in 和 not exists的比较
用例数据库简介
代码如下(示例):
(root@localhost) [dbt3]>select * from a;
+--------+-------+---------------------+
| userid | price | date |
+--------+-------+---------------------+
| 1 | 100 | 2016-02-01 00:00:00 |
| 1 | 50 | 2016-02-01 00:00:00 |
| 2 | 50 | 2016-02-01 00:00:00 |
| 1 | 200 | 2016-03-01 00:00:00 |
| 2 | 200 | 2016-07-01 00:00:00 |
| 3 | 500 | 2016-09-01 00:00:00 |
+--------+-------+---------------------+
(root@localhost) [dbt3]>select * from b;
+------+
| y |
+------+
| 1 |
| 2 |
| NULL |
+------+
需求说明:查询a表中userid不在b表y列中的数据详情。
not in实现
(root@localhost) [dbt3]>select * from a where userid not in (select y from b);
Empty set (0.00 sec)
可以看到,这样的查询方式无法返回数据为空。
问题出在表b中,因为b.y数据包含null,当使用 not in 进行查询时,若所查询目标不在被查询表中时,不会返回1而是返回null。可以通过以下sql语句进行验证:
(root@localhost) [dbt3]>select 'd' not in ('a','b','c');
+--------------------------+
| 'd' not in ('a','b','c') |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)
(root@localhost) [dbt3]>select 'd' not in ('a','b','c',NULL);
+-------------------------------+
| 'd' not in ('a','b','c',NULL) |
+-------------------------------+
| NULL |
+-------------------------------+
1 row in set (0.00 sec)
很明显,在上述被查询表中,有null值和没有null值返回的结果完全相反,所以在进行not in查询时,为了防止null值得干扰,应当在子查询中进行提前处理,去掉所有null值数据,如下:
(root@localhost) [dbt3]>select * from a where userid not in (select y from b where y is not null);
+--------+-------+---------------------+
| userid | price | date |
+--------+-------+---------------------+
| 3 | 500 | 2016-09-01 00:00:00 |
+--------+-------+---------------------+
1 row in set (0.00 sec)
通过在子查询中引入限制条件,从而解决了上述问题。
not exists实现
not exists实现的方法并不会出现上述问题,按照正常步骤进行查询即可,不需要进行额外的判null处理。
(root@localhost) [dbt3]>select * from a where not exists (select y from b where a.userid = b.y);
+--------+-------+---------------------+
| userid | price | date |
+--------+-------+---------------------+
| 3 | 500 | 2016-09-01 00:00:00 |
+--------+-------+---------------------+
1 row in set (0.00 sec)
总结
通常情况下使用 in 能解决大部分问题且效率较高,但是在使用过程中要注意内查询中的null值问题。