ToprowDB Dynamic Server 查询优化技术---子查询优化--03-2

3 深入探讨，一类很少被人关注的子查询

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 行记录)

 

子查询被物化后上拉，执行了嵌套循环连接。