简谈子查询的优化---与达梦公司@joehan100先生探讨

 

起因:

微博上因《数据库查询优化器的艺术》一书与友人讨论内容如下:

@joehan100:回复@那海蓝蓝: 试读了几章，佩服之至！你看看在书中有没有达梦7这样的子查询优化方式：http://t.cn/8sG5BHT //@joehan100:虽说MySQL/PostgreSQL的优化器比较一般，不过寸也有所长，看看应该也能学到不少东西。

正文：

子查询的优化，常规技术是“扁平化”，英文常用“flatten”表示，也有“拉平”或“上拉”的叫法，其本质是一样的，即：去掉子查询，把子查询中的表对象合并到父查询中，以便：

1）消除子查询带来的消耗

2）便于多表连接时可以进一步优化表连接的顺序

子查询优化的常规步骤就是：

1）子查询中的子表对象合并到父查询的FROM子句中

2）子查询的WHERE条件和 子查询的语义表示的和父查询的匹配条件都合并到父查询的WHERE子句中

常见的子查询优化的句型，包括：

1）IN子查询的优化

2）EXISTS子查询的优化

3）ALL/ANY/SOME子查询的优化

4）标量子查询的优化

5）其他非如上类型的子查询的优化

1）到4）可以看作是子查询优化的特例，因其中用到的谓词是用户常用的，所以多数数据库系统对他们进行了优化。5）的优化方式，也被一些系统支持，但支持程度不同。比如，PostgreSQL和MySQL对子查询优化的支持程度，可以参见《数据库查询优化器的艺术》“第17章PostgreSQL和MySQL的逻辑查询优化技术”431页的“表17-2 常见的可优化的子查询优化技术对照表”。例举如下：

 

对达梦执行计划的分析：

1 创建表，没有索引

create table t1(c1 int, c2 int);

create table t2(d1 int, d2 int);

2 插入数据，注意观察数据的特点，没有重复值，假设对单表等值比较或小数据量范围比较，则增加索引会更有效果。

declare

i int;

begin

for i in 1..10000 loop

insert into t1 values(i, i);

insert into t2 values(i, i * 10);

end loop;

commit;

end;

3 查询语句如下

select *

from t1

where c1 + ( select min(d1)

from t2

where c1 + 10 = d1 ) = 1000;

分析：

1 这个查询，是相关子查询，所以内表的获取数据的逻辑要受到外表数据的影响。

2 从语义看，是要找出表t1中满足比表t2的d1小10的c1且c1和d1加和等于1000的表t1的所有元组。

4 Oracle的查询执行计划的分析

 

 

 

如下内容，因中肯，直接引自DM对Oracle执行计划的分析（可参看：http://t.cn/8sG5BHT ）：

这个计划很简单，猜想Oracle处理的方式是，全表扫描T1, 对每一行记录，取出T1.C1放入变量:B中，然后扫描T2,计算filter 1, 而fliter1是一个复杂的子查询表达式。因为T1有10000行，因此这个fliter 1需要反复计算10000次, 或者说这个子查询会被反复求值10000次，T2也被反复扫描10000次，效率之低也就不奇怪了。

5 达梦的查询执行计划的分析

达梦自己的分析：

这个计划要复杂、精细得多。达梦7的优化器把子查询展开成了一个hash join，形象地说，就是先做一遍(select min(d1) from t2, t1 where t1.c1 + 10 = t2.d1 group by t1.c1), 把所有t1.c1对应的min(d1)全部计算出来，放在一个中间结果（spool）中，这一过程因为是hash连接，只需对T1, T2各扫描一遍即可，速度非常快。一旦获得这个结果，再次扫描T1, 对每一行用T1.C1在spool中定位获取相应的min(d1), 再进行过滤(T1.C1 + exp48 = 1000)，因此整个计算过程只需要执行三次扫描。

我们可以看到，在处理这类子查询时，达梦的平坦化技术获得了巨大的性能优势，而Oracle则中规中矩，模拟手工操作，将变量代入子查询反复求值，一旦没有辅助索引等手段，就会产生严重的性能问题。

6 那海蓝蓝的分析

6.1)  DM的执行计划确实在这条SQL语句上更为进步，效率非常高。分析如达梦所言。

6.2)  从图上的“2和3红色方框”来看，DM对表的存储，是存在索引的。而且扫描表时，使用的是“CSCN，聚集索引扫描”，这和MySQL的InnoDB的存储技术，是类似或一样的。^_^

6.3)  特别注意达梦的说明中，“(select min(d1) from t2, t1 where t1.c1 + 10 = t2.d1 group by t1.c1) ”中的 GROUP BY操作，这个应该是达梦对本条SQL优化的关键之一。

6.4)  但是单从这一条SQL看，只是个例，是否算作普遍的优化方式，还期待DM能有更多的资料公开，方便大家学习研究共同进步。