ToprowDB Dynamic Server 查询优化技术
华胜信泰架构师 李海翔
全面、深入地探讨子查询优化。首先展示更多的子查询优化的案例，然后揭开子查询的技术内幕，之后，回归我们的行文特色，对主流数据库的子查询优化技术做一个总结对比，扩展我们的视野。
1 还有什么样的子查询，可以被优化？
这一篇，我们将探讨更多的子查询类型。为了不至于混淆，我们先总结回顾一下。
在上一篇中，我们以IN子查询为例，进行了多个角度的探讨，探讨主要围绕在三个方面：
q 子查询是否是SPJ类型？
q 子查询是相关子查询还是不相关子查询？
q IN子查询的左操作符是否是主键、键或普通列？
如果对比视图优化（本系列的第二篇），细心的你还会发现，我们其实对比过了子查询为派生表的格式，所以如下我们集中在子查询为WHERE子句中的形式（但这不意味着子查询只能出现在WHERE子句中而不能出现在SQL语句的其他位置，举个例子，标量子查询是可以出现在目标列的位置处的。头有些大了吧，^_^, ^_^,一个子查询居然有这么多内容，烧脑不……）。
下面的内容，非常细致，需要极大的耐心才能通过比较掌握，你准备好了吗？
如果耐心不够，请选择直接退出……
1.1 EXISTS类型子查询
我们进行如下的子查询，分别用S1—S5表示：
S1: SELECT * FROM t3 WHERE b3 EXISTS (SELECT b1 FROM t1 WHERE a3=a1);
S2: SELECT * FROM t3 WHERE b3 EXISTS (SELECT a1 FROM t1 WHERE a3=a1);
S3: SELECT * FROM t3 WHERE b3 EXISTS (SELECT id1 FROM t1 WHERE a3=a1);
S4: SELECT * FROM t3 WHERE id3 EXISTS (SELECT b1 FROM t1 WHERE a1>2);
S5: SELECT * FROM t3 WHERE EXISTS (SELECT a1 FROM t1 WHERE b3=b1);
1.1.1 ToprowDB
1.1.1.1 S1语句
SELECT * FROM t3 WHERE EXISTS (SELECT b1 FROM t1 WHERE a3=a1)
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
2) informix.t1: INDEX PATH
(1) Index Name: informix. 102_7
Index Keys: a1 (Serial, fragments: ALL)
Lower Index Filter: informix.t3.a3 = informix.t1.a1
NESTED LOOP JOIN //使用了嵌套循环扫描，没有子查询存在，表明已经被优化
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
t2 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 3 1 3 00:00.01 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 3 1 00:00.01 4
1.1.1.2 S2语句
SELECT * FROM t3 WHERE EXISTS (SELECT a1 FROM t1 WHERE a3=a1)
Estimated Cost: 3
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
2) informix.t1: INDEX PATH
(1) Index Name: informix. 102_7
Index Keys: a1 (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.t3.a3 = informix.t1.a1
NESTED LOOP JOIN //使用了嵌套循环扫描，没有子查询存在，表明已经被优化
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
t2 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 3 1 3 00:00.00 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 3 1 00:00.01 3
1.1.1.3 S3语句
SELECT * FROM t3 WHERE EXISTS (SELECT id1 FROM t1 WHERE a3=a1)
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
2) informix.t1: INDEX PATH
(1) Index Name: informix. 102_7
Index Keys: a1 (Serial, fragments: ALL)
Lower Index Filter: informix.t3.a3 = informix.t1.a1
NESTED LOOP JOIN //使用了嵌套循环扫描，没有子查询存在，表明已经被优化
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
t2 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 3 1 3 00:00.00 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 3 1 00:00.00 4
1.1.1.4 S4语句
SELECT * FROM t3 WHERE EXISTS (SELECT b1 FROM t1 WHERE a1>2)
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
Filters: EXISTS <subquery> //子查询存在，没有被优化
Subquery:
---------
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) informix.t1: INDEX PATH
(1) Index Name: informix. 102_7
Index Keys: a1 (Serial, fragments: ALL)
Lower Index Filter: informix.t1.a1 > 2
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.01 2
Subquery statistics: //子查询存在，没有被优化
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1 1 1 00:00.00 2
1.1.1.5 S5语句
SELECT * FROM t3 WHERE EXISTS (SELECT a1 FROM t1 WHERE b3=b1)
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.t3: SEQUENTIAL SCAN
2) informix.t1: SEQUENTIAL SCAN (First Row)
Filters: informix.t3.b3 = informix.t1.b1
NESTED LOOP JOIN (Semi Join) //使用了嵌套循环扫描，没有子查询存在，表明已经被优化
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 t3
t2 t1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 6 1 6 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 1 1 3 00:00.00 2
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 6 1 00:00.00 4
————————————————
版权声明：本文为CSDN博主「那海蓝蓝」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/fly2nn/article/details/61924792
