起因:
微博上因《数据库查询优化器的艺术》一书与友人讨论内容如下:
@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 常见的可优化的子查询优化技术对照表”。例举如下:
![简谈子查询的优化---与达梦公司@joehan100先生探讨 - 那海蓝蓝 - 那海蓝蓝的博客 简谈子查询的优化---与达梦公司@joehan100先生探讨 - 那海蓝蓝 - 那海蓝蓝的博客](http://img1.ph.126.net/-OJ1qqncUaUHN6l8LINbrg==/6608409030073601074.jpg)
对达梦执行计划的分析:
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的查询执行计划的分析
![简谈子查询的优化---与达梦公司@joehan100先生探讨 - 那海蓝蓝 - 那海蓝蓝的博客 简谈子查询的优化---与达梦公司@joehan100先生探讨 - 那海蓝蓝 - 那海蓝蓝的博客](http://img1.ph.126.net/m5KjXjA_IXIike8yzDlQiA==/1159676904148209538.png)
如下内容,因中肯,直接引自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能有更多的资料公开,方便大家学习研究共同进步。