ToprowDB Dynamic Server 查询优化技术---子查询优化--02-1

2022-03-30 00:00:00 查询 语句 优化 嵌套 出现在

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

相关文章