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查询优化的道路,长路漫漫,技术多多,期待在日后有机会继续探索。