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

3.2 LATERAL连接

3.2.1 什么是Lateral连接

    我们知道，派生表是非相关的，即不能在派生表里引用其外部的同层次的其他对象。即如下SQL是错误的：

select * from t1, (select idx as id2 from t2 where t2.id2=t1.id1) as temp;

temp对象作为一个派生表，其WHERE子句当中出现了“t2.id2=t1.id1”，即引用了同FROM层的t1表对象，这在SQL标准中是不允许的。

但是，有些操作，需要“引用其外部的同层次的其他对象”，这该怎么办呢？SQL标准定义了“LATERAL JOIN”来满足这样的需求，称之为“侧连接”，意思是从同层对象中的旁侧开一个口子，借以探视其中的列对象。这就是“LATERAL JOIN”。详情及适用场景，可自行上网查阅。

本文则侧重对此类派生表式的子查询的优化进行探索。

3.2.2 LATERAL连接的优化

 

select * from test1 a, LATERAL (select * from test2 b where b.age=a.age);

3.2.2.1 ToprowDB

QUERY: (OPTIMIZATION TIMESTAMP: 08-18-2016 13:45:31)

------

select * from test1 a, LATERAL (select * from test2 b where b.age=a.age)

 

Estimated Cost: 4

Estimated # of Rows Returned: 1

 

  1) informix.a: SEQUENTIAL SCAN

  2) informix.b: SEQUENTIAL SCAN

 

DYNAMIC HASH JOIN

    Dynamic Hash Filters: informix.b.age = informix.a.age

 

Query statistics:

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

 

  Table map :

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

  Internal name     Table name

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

  t1                a

  t2                b

 

  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     4          1         4          00:00.00   2      

 

  type     rows_prod  est_rows  rows_bld  rows_prb  novrflo  time       est_cost

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

  hjoin    2          1         4         5         0        00:00.00   4      

 

子查询被上拉，执行了HASH连接。

 

3.2.2.2 Oracle

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

| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |       |     4 |   384 |     6   (0)| 00:00:01 |

|*  1 |  HASH JOIN         |       |     4 |   384 |     6   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| TEST2 |     4 |   192 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| TEST1 |     5 |   240 |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - access("B"."AGE"="A"."AGE")

 

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

 

子查询被上拉，执行了HASH连接。

3.2.2.3 Mysql

不支持LATERAL语法

3.2.2.4 PostgreSQL

postgres=# explain select * from test1 a, LATERAL (select * from test2 b where b.age=a.age) tmp;

                              QUERY PLAN

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

 Hash Join  (cost=25.30..124.72 rows=2312 width=184)

   Hash Cond: (a.age = b.age)

   ->  Seq Scan on test1 a  (cost=0.00..16.80 rows=680 width=92)

   ->  Hash  (cost=16.80..16.80 rows=680 width=92)

         ->  Seq Scan on test2 b  (cost=0.00..16.80 rows=680 width=92)

(5 行记录)

 

子查询被上拉，执行了HASH连接。

 

3.2.2.5 小结

SQL语句：select * from test1 a, LATERAL (select * from test2 b where b.age=a.age) tmp;

SQL语句	ToprowDB	Oracle	MySQL	PostgreSQL
S1	上拉优化（HASH）	上拉优化（HASH）	不支持LATERAL功能	上拉优化（HASH）

分析：

q  从整体上看，LATERAL的优化，除MySQL功能不支持外，ToprowDB、Oracle、PostgreSQL三个数据库都支持优化，三者能力相似

 

4 其他的一些子查询

    还有一些子查询，我们没有例举，如标量子查询。在SQL查询优化的道路，长路漫漫，技术多多，期待在日后有机会继续探索。