MySQL子查询(一)—— EXISTS与IN

EXISTS是一个非常强大的谓词,它允许数据库高效地检查指定查询是否产生某些行。通常EXISTS的输入是一个子查询,并关联到外部查询,但这并不一定是必须的。根据子查询是否返回行,该谓词返回TRUE或FALSE。例如下面的SQL语句:[sql]
SELECT customerid,companyname
FROM customers AS A
WHERE country = ‘Spain’
AND EXISTS
( SELECT * FROM orders AS B
WHERE A.customerid = B.customerid )[/sql]
通过下图的查询计划可以发现,SQL优化器首先根据WHERE条件先将country列为Spain的行数据取出,对于每个匹配的customerid,该执行计划对orders表上customerid索引进行一次查询,以检查orders表中是否有customerid的订单。子查询中的索引是非常必须的,因为这可以加速对于表orders的访问。

上述的SQL语句可以重写为IN子查询,并且两者的查询计划是相同的:[sql]
SELECT customerid,companyname
FROM customers AS A
WHERE country = ‘Spain’
AND customerid IN ( SELECT customerid FROM orders );[/sql]
实际在MySQL 5.6版本之前,其对于IN语句的优化是“LAZY”的。这意味着对于IN子句,如果不是显示的列表定义,如IN (‘a’,’b‘,’c‘),那么IN子句都会被转换为EXISTS的相关子查询。如下这句独立子查询:[shell]
SELECT … FROM t1 WHERE t1.a IN (SELECT b FROM t2);[/shell]
优化器会将该语句重写为如下的相关子查询:[shell]
SELECT … FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);[/shell]
这对性能会产生巨大的影响,因为转化为相关子查询后,就会对外部产生依赖(而通过我这么多年的观察,应用开发人员非常喜欢使用子查询,因为子查询相对更容易理解)。因此在MySQL 5.6版本之前子查询的优化方法之一就是将其重写为JOIN语句从而提升性能,如下面的IN子查询: [shell]
SELECT o_custkey FROM orders
WHERE o_custkey IN
( SELECT c_custkey FROM customer
WHERE c_acctbal < -500 );[/shell]
可以改写为:[shell]
SELECT o_custkey FROM orders,customer
WHERE o_custkey = c_custkey
AND c_acctbal < -500;[/shell]
此外,EXISTS与IN对三值逻辑的判断上还有一个小小的区别。对于EXISTS其总是返回TRUE或者FALSE。而对于IN,除了TRUE、FALSE值外,对于NULL值还有可能返回UNKNOWN。但是在过滤器中,UNKNOWN的处理方式与FALSE相同,因此使用IN与EXISTS一样,SQL优化器会选择相同的执行计划。

但是对于输入列表中包含NULL时,NOT EXISTS和NOT IN之间差异就表现的非常的明显了。因为对于输入列表中包含NULL值,IN总是返回TRUE和UNKNOWN,因此NOT IN总是返回NOT TRUE和NOT UNKNOWN,即FALSE和UNKNOWN。来看下面的例子:[shell]
mysql> SELECT NULL IN (‘a’,’b’, NULL)\G;
*************************** 1. row ***************************
NULL IN (‘a’,’b’, NULL): NULL
1 row in set (0.00 sec)

mysql> SELECT NULL NOT IN (‘a’,’b’, NULL)\G;
*************************** 1. row ***************************
NULL NOT IN (‘a’,’b’, NULL): NULL
1 row in set (0.00 sec)

mysql> SELECT ‘a’ NOT IN (‘a’,’b’, NULL)\G;
*************************** 1. row ***************************
‘a’ NOT IN (‘a’,’b’, NULL): 0
1 row in set (0.00 sec)

mysql> SELECT ‘c’ NOT IN (‘a’,’b’, NULL)\G;
*************************** 1. row ***************************
‘c’ NOT IN (‘a’,’b’, NULL): NULL
1 row in set (0.00 sec)[/shell]
’a’ IN和NOT IN的返回值都是显而易见的。NULL IN (‘a’,’b’,NULL)返回的是NULL,因为NULL值进行比较返回的是UNKNOWN状态。最后,对于‘c’ NOT IN(’a’,‘b’,NULL)的结果可能出乎一些人的意料之外,其返回的是NULL。之前已经说过,对于包含NULL值的NOT IN来说,其总是返回FALSE和UNKNOWN。而对于NOT EXISTS,其总是返回TRUE和FALSE。而这就是NOT EXISTS和NOT IN的最大区别。
这里来简单说明一下mysql中NULL值的定义和使用,其实这个其他程序语言,例如java,php,python,go等的定义都是一样的,这里举几个官方的小例子来说明其使用。
其实对于很多刚接触mysql的同学来说,NULL是个很容易混淆的概念,也许有人简单的或者模糊的理解为NULL大致等同于空字符串”,其实这是不对的,我们来简单测试一下:[shell]
CREATE TABLE `test` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`phone` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COMMENT=’test for NULL’
COLLATE=’utf8_bin’
ENGINE=InnoDB;[/shell]
然后插入数据:[shell]
INSERT INTO `test` (`phone`) VALUES (NULL);
INSERT INTO `test` (`phone`) VALUES (”);[/shell]
对与以上两行我们可以这样理解,第一行NULL的表示phone是not known,也就是不知道,但是是存在的。第二行”表示是没有phone的,不存在的。
在实际的查询中你可以使用IS NULL或者IS NOT NULL或者IFNULL()函数进行对NULL的相关查询和操作。
在SQL语句中,NULL和任何的值进行比较都会返回NULL,包括NULL。例如下面的SQL语句,返回NULL而不是返回查询到0行[shell]
SELECT NULL, 1+NULL, CONCAT(‘Invisible’,NULL);[/shell]
如果你要这样查询phone为NULL的Colum,实际上是不对的[shell]
SELECT * FROM my_table WHERE phone = NULL;[/shell]
你应该这样来查询:[shell]
SELECT * FROM my_table WHERE phone IS NULL;[/shell]
关于NULL的更多信息以后再讲。

Avatar photo

About Blackford

这是个最好的时代,这是个最坏的时代,这是个充满希望的春天,这是个令人绝望的冬天,我们前面什么都有,我们前面什么都没有。梦想,让我们一次次的走远,又一次次的回头,一个关于人生的梦想还在不断奔跑,带着喜悦和疼痛,不过一切才刚刚开始,并且直到今天也远远没有结束
This entry was posted in 数据库技术, 架构运维 and tagged , . Bookmark the permalink.

发表评论

电子邮件地址不会被公开。 必填项已用*标注