第一章 适用范围

当SQL文本中包含GROUP BY视图时，不同数据库通常都会采用一定的优化处理时段，来提升整条SQL的执行效率。因此本篇主要讨论ORACLE、MySQL库在相应场景下的性能表现。

本案例中的问题现象主要测试于当前主流的ORACLE：11G、MySQL：5.7环境。不排除随着后续版本的升级可能会有一定细微的差异表现。但问题的现象及差异表现，不会有太大出入。

第二章 ORACLE库案例分析

众所周知，ORACLE的优化器是相对智能的。对含有group by的视图在处理时，是会尝试做相应查询转换手段的。如：视图合并、谓词推入等。

下面创建案例脚本：

CREATE TABLE TEST_T1 AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE TEST_T2 AS SELECT * FROM DBA_OBJECTS;

create index idx_TEST_T1_type on TEST_T1(object_type);
create index idx_t2_objectid on TEST_T2(object_id);
create index idx_t2_type on TEST_T2(object_type);

下面分别测试在连接条件过滤性很好、不好的场景下数据库的差异表现。

2.1 连接条件过滤性很好

测试语句如下：

select t1.object_Id,t1.object_name,t2.object_id,t2.cnt
  from test_t1 t1
  left join (select object_id, count(*) CNT from test_t2 group by object_id) t2
    on t1.object_id = t2.object_id
 where t1.object_type = 'CLUSTER'
   and t2.cnt < 3;

查询表现如下：

表现分析：

数据库自动对视图t2做了合并，与外部表直接关联，并在最后完成聚合动作。这样做的好处是，优先应用连接条件索引，将数据量降低到最小（10行）。并只对这10行做聚合，查询转换后，执行效率非常好。

2.2 连接条件过滤性不好

测试语句如下：

select t1.object_Id,t1.object_name,t2.object_type,t2.cnt
  from test_t1 t1
  left join (select object_type, count(*) CNT
               from test_t2
              group by object_type) t2
    on t1.object_type = t2.object_type
 where t1.OBJECT_ID < 1000
   and t2.cnt < 3;

查询表现如下：

表现分析：

数据库优先查询视图T2，在将较大的结果集(8W多行)聚合后，仅返回46行记录。之后与T1表连接，仅返回1行需要的记录。
这部分数据库并没有做查询转换，单独查询了视图并与外表连接。这里分析视图合并是要考虑成本的。本条SQL未做视图合并的COST值是696。

验证一下，添加HINT测试做视图合并的成本：

select  t1.object_Id, t1.object_name, t2.object_type, t2.cnt
  from test_t1 t1
  left join (select /*+ MERGE */
              object_type, count(*) CNT
               from test_t2
              group by object_type) t2
    on t1.object_type = t2.object_type
 where t1.OBJECT_ID < 1000
   and t2.cnt < 3;

查询表现如下：

表现分析：

这里看到，数据库之所以未做视图合并，是因为完成视图合并后，中间结果集需要增大到395W的数据量，再对这395W数据聚合后，需要消耗大量的资源开销及执行时间。且优化器预估成本值达到了10433。正在基于此种情况，优化器放弃了视图合并的手段。采用了前述的单独查询视图的方式。

2.3 案例总结：

这里ORACLE优化器对查询写法是没有太多要求的，是可以根据成本选择相应查询转换手段，来用相对高效的方式去查询数据的。

即：当连接条件过滤性很好时，可以通过视图合并，有效降低中间结果集；
当连接条件过滤性很不好时，由于成本上升，不会做视图合并。单独对视图聚合处理。

第三章 MySQL库案例分析

相比较与ORACLE库，MySQL的优化器是相对差一些的。并没有相应的查询转换技术。尤其是8.0之前，表连接的方式只有nested loop方式。更容易加剧一些性能问题的出现。但MySQL优化器也考虑到了嵌套循环方式的很多弊端，设计了很多独特的查询方式以提升查询效率（后面案例中会提及）。

下面创建案例脚本：

CREATE TABLE TEST_T1 as select * from information_schema.COLUMNS;
ALTER TABLE TEST_T1 ADD id INT PRIMARY KEY AUTO_INCREMENT;

CREATE TABLE TEST_T2 as select * from information_schema.COLUMNS;
ALTER TABLE TEST_T2 ADD id INT PRIMARY KEY AUTO_INCREMENT;

下面分别测试在连接条件过滤性很好、不好的场景下数据库的差异表现。

3.1 连接条件过滤性很好

测试语句如下：

select t1.id,t1.table_name,t2.id,t2.cnt
  from test_t1 t1 
  left join (select id,count(*) CNT from test_t2 group by id) t2
    on t1.id = t2.id
 where t1.table_name='TEST_T1'
	and t2.cnt<3;

查询表现如下：

表现分析：

优先通过T2表的主键索引全扫描，获得视图中的数据后完成聚合动作。之后将T2聚合后的数据生成物化临时表。由T1表驱动关联条件与T2的物化临时表做连接。

这部分相当于ORACLE库的未做视图合并的效果。ORACLE是可以通过HASH连接去关联，而MySQL当前版本还只支持嵌套循环，故此优化器通过一种特殊的方式，将T2生成物化临时表的过程中，提前创建好了待关联列的索引。即：t2.id。然后通过T1与T2临时表自动创建的索引完成过滤。见执行计划中的<auto_key1>部分。这种技术也是MySQL优化器所独有的。

这里分析，ORACLE优化器在连接条件过滤性很好的时候，是可以通过视图合并的手段去提升效率的。而MySQL暂不支持上述查询转换。因此考虑能否人为的对SQL进行改写，以应用查询转换技术。

改写SQL如下：

select t1.id,t1.table_name,t2.id,count(t2.id) 
  from test_t1 t1
  left join test_t2 t2
    on t1.id = t2.id
 where t1.table_name='TEST_T1'
    group by t1.id,t1.table_name,t2.id
	having count(t2.id) <3;

直接关联两个表，好处是可以获得T2表的关联条件以应用关联索引。并对关联结果做聚合，在最后通过having语句判断count条件。

查询表现如下：

表现分析：

改写后，执行效率相较改写前有了较大程度的性能提升。数据库直接通过T2的关联索引访问T2表，减少了T2表的索引全扫描动作。且并没有创建T2的物化临时表。这些都是提升查询效率的方面。

总结分析：因此这里分析，在关联列过滤性很好的时候，建议通过表关联后聚合的方式，手动的实现视图合并。

3.2 连接条件过滤性不好

测试语句如下：

select t1.id, t1.table_name, t2.table_name, t2.cnt
  from test_t1 t1
  LEFT JOIN (select table_name, count(*) CNT
               from test_t2
              group by table_name) t2
    on t1.table_name = t2.table_name
 where t1.table_name like 'T%'
   and t2.cnt < 3;

查询表现如下：

表现分析：

默认对T2查询后生成物化临时表，在与T1做关联。查询效率可以得到保障。
这里分析，如果是改写成上一小节提及的表连接方式，会是什么效果呢？

改写SQL如下：

select t1.id,t1.table_name,t2.table_name,count(t2.table_name) 
  from test_t1 t1
  left join test_t2 t2
    on t1.table_name = t2.table_name
 where t1.table_name like 'T%'
    group by t1.id,t1.table_name,t2.table_name
	having count(t2.table_name) <3;  

直接关联两个表，好处是可以获得T2表的关联条件以应用关联索引。但T2表不是一个视图了，可能无法应用之前的物化临时表技术。

查询表现如下：

表现分析：

由于T2表缺少关联列的索引，T1表通过循环嵌套的方式多次全表访问T2表，造成查询效率较低。

创建T2连接列索引测试：

create index idx_t2_name on TEST_T2(table_name);

即使创建了T2的关联列索引，执行时间仍然要达到0.1秒以上。相比较于直接关联T2子查询的方式，直接关联表的写法其执行效率还是比较低的。这里分析之所以效率变差了，主要还是来自于关联条件关联后的中间结果集较大，并对大量的结果集做聚合所致。这种问题与ORACLE场景中的测试保持一致。

3.3 案例总结：

这里MySQL优化器没有足够多的查询转换手段，来提升SQL的执行效率。因此，就需要通过相应的写法来保障SQL执行效率。

经过上述章节的分析：
当连接条件过滤性很好时，可以通过表连接后聚合的方式，手动实现ORACLE的视图合并技术，有效降低中间结果集；
当连接条件过滤性很不好时，需要提前聚合相应的中间表，通过聚合后，还能使用MySQL的独有技术物化临时表，来保障关联group by视图的执行效率。

第四章 问题总结

经过上述章节的分析，ORACLE和MySQL优化器在处理group by的子查询其查询表现是存在差异的。ORACLE是有足够多的查询转换技术来保障执行效率的。MySQL优化器相对ORACLE略差一些，不同场景对SQL写法的依赖性比较高。

因此就需要明确不同场景下的优化器处理原理。在书写代码的过程中，有效的降低SQL处理的中间结果集。来根据不同写法保障MySQL数据库的特定查询表现。