ToprowDB Dynamic Server 查询优化技术---子查询优化--03-2
3.1 派生表的优化
在子查询中，有一类常见的子查询形式，是子查询出现在FROM子句中，这样的子查询，有个学名，叫做“派生表”。这样的且符合SPJ格式要求派生表，可以被优化。
这一节，我们来对比四种数据库，看看各个数据库对于派生表的支持情况。大家可以看到，我们将加入Oracle的对比。
3.1.1 Q1
select * from (select idx as id2 from test2) as temp,test1;
3.1.1.1 ToprowDB/Informix
QUERY: (OPTIMIZATION TIMESTAMP: 08-15-2016 18:22:01)
------
select * from (select idx as id2 from test2),test1
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.test1: SEQUENTIAL SCAN
2) informix.test2: SEQUENTIAL SCAN
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 test1
t2 test2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 5 1 5 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 20 1 20 00:00.00 2
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 20 1 00:00.00 4
子查询被上拉，执行了嵌套循环连接。
3.1.1.2 Oracle
Execution Plan
----------------------------------------------------------
Plan hash value: 1251974749
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1220 | 5 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 20 | 1220 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TEST2 | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 5 | 240 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST1 | 5 | 240 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
子查询被上拉，执行了sort merge连接。
3.1.1.3 Mysql
mysql> explain select * from (select idx as id2 from test2) as temp,test1;
+----+-------------+------------+-------+---------------+--------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+--------+---------+------+------+---------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | NULL |
| 1 | PRIMARY | test1 | ALL | NULL | NULL | NULL | NULL | 5 | Using join buffer (Block Nested Loop) |
| 2 | DERIVED | test2 | index | NULL | index1 | 5 | NULL | 4 | Using index |
+----+-------------+------------+-------+---------------+--------+---------+------+------+---------------------------------------+
3 rows in set (0.00 sec)
子查询被上拉，执行了嵌套循环连接。
3.1.1.4 PostgreSQL
postgres=# explain select * from (select idx as id2 from test2) as temp,test1;
QUERY PLAN
---------------------------------------------------------------------
Nested Loop (cost=0.00..5815.30 rows=462400 width=96)
-> Seq Scan on test2 (cost=0.00..16.80 rows=680 width=4)
-> Materialize (cost=0.00..20.20 rows=680 width=92)
-> Seq Scan on test1 (cost=0.00..16.80 rows=680 width=92)
(4 行记录)
子查询被物化后上拉，执行了嵌套循环连接。
3.1.2 Q2
select * from (select idpk as id2 from test3) as temp,test1;
3.1.2.1 ToprowDB
QUERY: (OPTIMIZATION TIMESTAMP: 08-15-2016 18:22:09)
------
select * from (select idpk as id2 from test3),test1
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.test1: SEQUENTIAL SCAN
2) informix.test3: SEQUENTIAL SCAN
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 test1
t2 test3
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 5 1 5 00:00.00 2
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 10 1 10 00:00.00 2
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 10 1 00:00.00 4
子查询被上拉，执行了嵌套循环连接。
3.1.2.2 Oracle
Execution Plan
----------------------------------------------------------
Plan hash value: 2676247877
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 610 | 4 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 10 | 610 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TEST3 | 2 | 26 | 2 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 5 | 240 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST1 | 5 | 240 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
子查询被上拉，执行了sort merge循环连接。
3.1.2.3 Mysql
mysql> explain select * from (select idpk as id2 from test3) as temp,test1;
+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
| 1 | PRIMARY | test1 | ALL | NULL | NULL | NULL | NULL | 5 | Using join buffer (Block Nested Loop) |
| 2 | DERIVED | test3 | index | NULL | PRIMARY | 4 | NULL | 2 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+------+---------------------------------------+
3 rows in set (0.00 sec)
子查询被上拉，执行了嵌套循环连接。
3.1.2.4 PostgreSQL
postgres=# explain select * from (select idpk as id2 from test3) as temp,test1;
QUERY PLAN
---------------------------------------------------------------------
Nested Loop (cost=0.00..5815.30 rows=462400 width=96)
-> Seq Scan on test3 (cost=0.00..16.80 rows=680 width=4)
-> Materialize (cost=0.00..20.20 rows=680 width=92)
-> Seq Scan on test1 (cost=0.00..16.80 rows=680 width=92)
(4 行记录)
子查询被物化后上拉，执行了嵌套循环连接。
————————————————
版权声明：本文为CSDN博主「那海蓝蓝」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/fly2nn/article/details/61924799
相关文章