子查问是指在一个 SELECT 语句中嵌套另一个 SELECT 语句。
通常来讲,应用子查问的益处如下:
- 子查问容许结构化的查问,这样就能够把一个查问语句的每个局部隔开。
- 子查问提供了另一种办法来执行有些须要简单的 JOIN 和 UNION 来实现的操作。
- 在许多人看来,子查问可读性较高。
一个子查问会返回一个标量(繁多值)、一个行、一个列或一个表(一行或多行及一列或多列),这些子查问被称为标量、列、行和表子查问。可返回一个特定品种后果的子查问常常只用于特定的语境中。子查问能够包含一般 SELECT 能够包含的任何关键词或子句,如 DISTINCT、GROUP BY、ORDERBY、LIMIT、JOIN、UNION 等。
子查问的限度是其内部语句必须是以下语句之一:SELECT、INSERT、UPDATE、DELETE、SET 或 DO。还有一个限度是,目前用户不能既在一个子查问中批改一个表,又在同一个表中进行抉择,尽管这样的操作可用于一般的 DELETE、INSERT、REPLACE 和 UPDATE 语句中,然而对子查问不能够同时进行这样的操作。
罕用关键词(ANY/IN/SOME/ALL)
应用 ANY、IN、SOME 和 ALL 进行子查问的语法如下:
operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
ANY 关键词必须与一个比拟操作符一起应用。ANY 关键词的意思是“对于子查问返回的列中的任一数值,如果比拟后果为 TRUE,则返回 TRUE”。例如:
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
关键词 IN 是“= ANY”的别名,关键词 SOME 是 ANY 的别名。
ALL 关键词也必须与比拟操作符一起应用。ALL 的意思是“对于子查问返回的列中的所有值,如果比拟后果为 TRUE,则返回 TRUE”。
独立子查问
独立子查问是不依赖内部查问而运行的子查问。
举个例子,当初有一张订单表,咱们须要查问出每月最初理论订单日期产生的订单。在这里,每月最初理论订单的日期可能并不是每月的最初一天。因而,每月最初订单日期须要通过子查问来判断。查问语句如下:
SELECT orderid,customerid,employeeid,orderdate
FROM orders
WHERE orderdate IN
(SELECT MAX(orderdate)
FROM orders
GROUP BY (DATE_FORMAT(orderdate,'%Y%m'))
)
子查问通过把订单按月分组并返回每个分组中最大的日期后果,这就是每个月最初订单生成的日期。而后内部查问通过子查问取得的日期来获得所有的订单信息。
这个问题看似失去了解决,实际上并没有这么简略。因为在这个数据量并不大的数据库中执行上述 SQL 语句居然须要 6.08 秒,如果咱们用 EXPLAIN 来对语句进行剖析,就能够找到问题的所在——独立子查问被转化成了相干子查问。
这个是 MySQL 优化器对 IN 子查问优化时存在的一个问题,MySQL 优化器对于 IN 语句的优化是“LAZY”的。对于 IN 子句,如果不是显式的列表定义,如 IN(‘a’,’b’,’c’),那么 IN 子句都会被转换为 EXISTS 的相干子查问。
如果子查问和内部查问别离返回 M 和 N 行,那么该子查问被扫描为 O(N+M*N)而不是 O(M+N)。
因而,对于上述问题的 SQL 语句,MySQL 数据库的优化器将其对应地转换为如下的相干子查问:
SELECT orderid,customerid,employeeid,orderdate
FROM orders AS A
WHERE EXISTS
( SELECT *
FROM orders
GROUP BY(DATE_FORMAT(orderdate,'%Y%M'))
HAVING MAX(orderdate)= A.OrderDate
);
须要留神的是,在大多数状况下,MySQL 数据库都将独立子查问转换为相干子查问。
不过对于上述语句,还是有肯定的办法能够优化的。留神到 慢的起因是独立子查问被转换成相干子查问,而这个相干子查问须要进行屡次的分组操作。能够采取另一个办法,再嵌套一层子查问,防止屡次的分组操作,语句如下:
SELECT orderid,customerid,employeeid,orderdate
FROM orders A
WHERE EXISTS
(SELECT * FROM (SELECT MAX(orderdate) AS orderdate
FROM orders
GROUP BY (DATE_FORMAT(orderdate,'%Y%M')) ) B
WHERE A.orderdate = B.orderdate
);
相干子查问
相干子查问是指援用了内部查问列的子查问,即子查问会对外部查问的每行进行一次计算。
举个例子,要查问每个员工最大订单日期的订单,因为一个员工可能有多个订单具备雷同的订单日期,所以可能会为每个员工返回多行数据。
在这个例子中,子查问必须关联内部查问,将外部查问的 employeeid 与内部的 employeeid 进行匹配:
SELECT orderid,customerid,employeeid,orderdate,requireddate
FROM orders AS A
WHERE orderdate =
(SELECT MAX(orderdate) FROM orders AS B
WHERE A.employeeid=B.employeeid);
不过因为执行了大量逻辑 IO,该语句运行速度过慢,有什么形式能够优化呢?
首先,咱们能够思考增加一个惟一索引来放慢处理速度:
CREATE UNIQUE INDEX idx_empid_od_rd_oid
ON orders(employeeid,orderdate,requireddate,orderid)
速度失去了肯定的晋升,然而问题依然是相干子查问须要与内部子查问的列进行屡次比拟。
为了防止子查问与内部子查问的屡次比拟操作,对于相干子查问,有时能够通过派生表来进行重写,对于以上 SQL 咱们能够重写为:
SELECT
A.orderid,A.customerid,A.employeeid,
B.orderdate,requireddate
FROM orders AS A,
(SELECT employeeid,MAX(orderdate) AS orderdate FROM orders
GROUP BY employeeid) AS B
WHERE A.employeeid=B.EmployeeID AND A.OrderDate=B.orderdate;
这时没有了相干子查问的执行过程,逻辑 IO 大大减少了,同时也能用到索引,执行速度失去了很大晋升。
EXISTS 谓词
EXISTS 是一个十分弱小的谓词,它容许数据库高效地查看指定查问是否产生某些行。通常 EXISTS 的输出是一个子查问,并关联到内部查问,但这不是必须的。依据子查问是否返回行,该谓词返回 TRUE 或 FALSE。与其余谓词和逻辑表达式不同的是,无论输出子查问是否返回行,EXISTS 都不会返回 UNKNOWN。如果子查问的过滤器为某行返回 UNKNOWN,则示意该行不返回,因而,这个 UNKNOWN 被认为是 FALSE。
目前在绝大多数的状况下,IN 和 EXISTS 都具备雷同的执行打算。然而要留神的是,NOT IN 和 NOT EXISTS 具备十分不同的执行打算。
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。而对于 NOT EXISTS,其总是返回 TRUE 和 FALSE。
集体心得:对于“非存在型”问题,应用 NOT EXISTS 谓词,其余应用关联子查问的场景应该尽可能改写成关联派生表的形式,并增加适合的索引。