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

2022-03-30 00:00:00 查询 执行 优化 嵌套 计划


探讨子查询优化,我们也许会有一些 “故地重游似曾相识”的感觉,感觉子查询和视图优化怎么那么相似?其实,这种感觉,是一种正确的良好的感觉,或者叫做直觉吧。视图和子查询确实相似,从长像看,放在SQL语句中,两者都导致了query产生了嵌套;从优化技术看,都是要消除嵌套、把子部分去除合并到父亲节点;所以,本质上,视图和子查询的优化方式是一样的,称之为“上拉/扁平化”优化。(提高效率,扁平化管理,一些歪想,神思千里…….托着下巴,想想扁平化后的好处……)

1 什么样的子查询,可以被优化?
什么样的子查询,可以被优化?

标题有点大,不过不是标题党,前面我们说视图和子查询的优化相似,那么视图如果可以被优化,似乎子查询的优化就是有路可寻的,或者聪明的你一定会想到,SPJ类型的子查询,是可以被优化的,非SPJ类型的子查询,是不可以被优化的,是吧?

1.1 SPJ类型子查询
沿用上一篇视图优化中提供的示例,我们进行如下的子查询:

SELECT * FROM (SELECT * FROM t1,t2) AS v_SPJ, t3;

注意视图优化篇中提及的是:SELECT * FROM v_SPJ, t3;


然后在ToprowDB中得到的查询执行计划可能如下(取决于统计数据导致的代价估算模型的计算值):-


这里的子查询,是SPJ类型的子查询,与SPJ类型的视图相似,被优化,即子查询被消除。

1.2 非SPJ类型的子查询
非SPJ子查询,类似非SPJ类型的视图,如果子查询的语句中包含有如下子句,这样的子查询,称为非SPJ类型的子查询:

1 GROUP BY子句

2 DISTNICT子句

3 聚集函数

4 带有集合操作: UNION / UNION DISTINCT / UNION ALL

5 带有分析函数、connect by、OUTER JOIN、PL/SQL function等

同样用一个例子来说明:

执行:SELECT * FROM t3, (SELECT DISTINCT t1.b1, t2.b2 FROM t1, t2 GROUP BY t1.b1, t2.b2) AS v_NON_SPJ;

左侧执行计划中的t1和t2,是原始SQL中的t2和t1,这2个表作了连接操作,得到一个临时的结果集((Temp Table For View)),这说明子查询v_NON_SPJ没有解体,即子查询v_NON_SPJ的query被单独执行了。换句话说,非SPJ类型的子查询不能被优化。

前面2个例子,说明了子查询的优化,和视图优化,确实是相似的,遵循着一样的规律,都是要区分SPJ类型还是非SPJ类型,前者可以被优化而后者不能被优化。

2 所有的SPJ类型子查询/视图都可以被优化吗?
在节,我们提到SPJ类型的子查询/视图是可以被优化的,那么,一个问题是:所有的SPJ类型的子查询/视图都可以被优化吗?

如下将执行的SQL语句为:

S1:SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1);

S2:SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1);

S3:SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1);

S4: SELECT * FROM t3 WHERE b3 IN (SELECT b1 FROM t1 WHERE a1=a3);

2.1 S1语句
SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1) //非相关的子查询

可以得到如下的执行计划:

Estimated Cost: 4

Estimated # of Rows Returned: 1

1) informix.t3: SEQUENTIAL SCAN

Filters: informix.t3.b3 = ANY <subquery> //此处表明IN子查询被转换为ANY,但依旧是子查询

Subquery:

---------

Estimated Cost: 2

Estimated # of Rows Returned: 1

1) informix.t1: SEQUENTIAL SCAN

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.13 4

从上面的执行计划可以看出,非相关子查询没有被优化,嵌套层次依然存在。

2.2 S2语句
SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1) //非相关的子查询,子查询目标列为索引列id1

执行计划如下:

Estimated Cost: 4

Estimated # of Rows Returned: 1

1) informix.t3: SEQUENTIAL SCAN

Filters: informix.t3.b3 = ANY <subquery> //此处表明IN子查询被转换为ANY,但依旧是子查询

Subquery:

---------

Estimated Cost: 2

Estimated # of Rows Returned: 1

1) informix.t1: SEQUENTIAL SCAN

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.06 4

Subquery statistics: //存在子查询

--------------------

Table map :

----------------------------

Internal name Table name

----------------------------

t1 t1

type table rows_prod est_rows rows_scan time est_cost

-------------------------------------------------------------------

scan t1 3 1 3 00:00.00 2

从上面的执行计划可以看出,ToprowDB对于非相关子查询不支持优化,嵌套层次依然存在。

2.3 S3语句
SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1) //非相关的子查询,子查询左操作符为索引列id1

执行计划如下:
Estimated Cost: 3

Estimated # of Rows Returned: 1

1) informix.t3: INDEX PATH //此处变为索引扫描

(1) Index Name: informix. 104_12

Index Keys: id3 (Serial, fragments: ALL) //id3上存在索引,而子查询值固定,所以可以对t3表执行索引扫描

Lower Index Filter: informix.t3.id3 = ANY <subquery> //此处表明IN子查询被转换为ANY,但依旧是子查询

Subquery:

---------

Estimated Cost: 2

Estimated # of Rows Returned: 1

Query statistics:

-----------------

Table map :

----------------------------

Internal name Table name

----------------------------

t1 t3

type table rows_prod est_rows rows_scan time est_cost

-------------------------------------------------------------------

scan t1 3 1 3 00:00.10 4

Subquery statistics:

--------------------

Table map :

----------------------------

Internal name Table name

----------------------------

t1 t1

type table rows_prod est_rows rows_scan time est_cost

-------------------------------------------------------------------

scan t1 3 1 3 00:00.00 2

type rows_sort est_rows rows_cons time

-------------------------------------------------

sort 3 0 3 00:00.02 //在子查询上执行了一个排序操作

从上面的执行计划可以看出,非相关子查询左操作符带有主键索引列没有被优化,嵌套层次依然存在。但是, ToprowDB在子查询上执行了一个排序操作。

从前面的3个例子,我们可以看出,ToprowDB不支持对部分SPJ类型的子查询进行优化。

3 深入探讨
3.1 主流数据库对于例1至例3的优化情况
我们以MySQL 5.6.27和PostgreSQL 9.5为例,看看第二节所述的子查询的在MySQL和PostgreSQL的支持情况。

3.1.1 MySQL
3.1.1.1 S1语句
SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1) //非相关的子查询

可以得到如下的执行计划:

mysql> explain SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1);

+----+-------------+-------+-------+------+----------------------------------------------------+

| id | select_type | table | type | key | Extra |

+----+-------------+-------+-------+------+----------------------------------------------------+

| 1 | SIMPLE | t1 | index | a1 | Using index |

| 1 | SIMPLE | t3 | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |

+----+-------------+-------+-------+------+----------------------------------------------------+

2 rows in set (0.00 sec)

从查询执行计划看,嵌套不存在(id列的值相同表明不存在嵌套层次),子查询被消除,即被优化。

3.1.1.2 S2语句
SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1) //非相关的子查询,子查询目标列为索引列id1

执行计划如下:

mysql> EXPLAIN SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1);

+----+-------------+-------+-------+------+----------------------------------------------------+

| id | select_type | table | type | key | Extra |

+----+-------------+-------+-------+------+----------------------------------------------------+

| 1 | SIMPLE | t1 | index | a1 | Using index |

| 1 | SIMPLE | t3 | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |

+----+-------------+-------+-------+------+----------------------------------------------------+

从查询执行计划看,嵌套不存在,子查询被消除,即被优化。

3.1.1.3 S3语句
SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1) //非相关的子查询,子查询左操作符为索引列id1

执行计划如下:

mysql> explain SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1);

+----+-------------+-------+--------+---------+--------------------------+

| id | select_type | table | type | key | Extra |

+----+-------------+-------+--------+---------+--------------------------+

| 1 | SIMPLE | t1 | index | a1 | Using where; Using index |

| 1 | SIMPLE | t3 | eq_ref | PRIMARY | NULL |

+----+-------------+-------+--------+---------+--------------------------+

从查询执行计划看,嵌套不存在,子查询被消除,即被优化。

从上面的执行计划可以看出,MySQL对于ToprowDB不支持的三种子查询,都进行了优化。

3.1.2 PostgreSQL
3.1.2.1 S1语句
SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1) //非相关的子查询

可以得到如下的执行计划:

postgres=# explain SELECT * FROM t3 WHERE b3 IN (SELECT a1 FROM t1);

QUERY PLAN

------------------------------------------------------------------

Hash Semi Join (cost=55.90..103.00 rows=1020 width=12)

Hash Cond: (t3.b3 = t1.a1)

-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)

-> Hash (cost=30.40..30.40 rows=2040 width=4)

-> Seq Scan on t1 (cost=0.00..30.40 rows=2040 width=4)

从查询执行计划看,嵌套不存在,子查询被消除变为“Semi Join”,即被优化。



3.1.2.2 S2语句
SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1) //非相关的子查询,子查询目标列为索引列id1

执行计划如下:

postgres=# EXPLAIN SELECT * FROM t3 WHERE b3 IN (SELECT id1 FROM t1);

QUERY PLAN

------------------------------------------------------------------

Hash Semi Join (cost=55.90..103.00 rows=1020 width=12)

Hash Cond: (t3.b3 = t1.id1)

-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)

-> Hash (cost=30.40..30.40 rows=2040 width=4)

-> Seq Scan on t1 (cost=0.00..30.40 rows=2040 width=4)

从查询执行计划看,嵌套不存在,子查询被消除变为“Semi Join”,即被优化。





3.1.2.3 S3语句
SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1) //非相关的子查询,子查询左操作符为索引列id1

执行计划如下:

postgres=# explain SELECT * FROM t3 WHERE id3 IN (SELECT a1 FROM t1);

QUERY PLAN

------------------------------------------------------------------

Hash Semi Join (cost=55.90..114.35 rows=2040 width=12)

Hash Cond: (t3.id3 = t1.a1)

-> Seq Scan on t3 (cost=0.00..30.40 rows=2040 width=12)

-> Hash (cost=30.40..30.40 rows=2040 width=4)

-> Seq Scan on t1 (cost=0.00..30.40 rows=2040 width=4)

从查询执行计划看,嵌套不存在,子查询被消除变为“Semi Join”,即被优化。

从上面的执行计划可以看出,MySQL和PostgreSQL对于ToprowDB不支持的三种子查询,都进行了优化。
————————————————
版权声明:本文为CSDN博主「那海蓝蓝」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/fly2nn/article/details/61924786

相关文章