OceanBase v4.2 特性解析：Lateral Derived Table 优化查询

46 阅读 0 评论 0 点赞

前言

从传统规则来看，内联视图通常不允许引用在同一FROM子句中前面定义的表的列。但从OceanBase 4.2.2版本开始，这一限制得到了突破，允许内联视图作为Lateral Derived Table来定义，从而允许此类引用。Lateral Derived Table的语法与普通内联视图的语法相似，只是需要在内联视图之前之前添加关键字LATERAL。LATERAL关键字必须紧跟在需要作为Lateral Derived Table的每一个子查询之前。

LATERAL关键字及其使用实例

允许在MySQL模式和Oracle模式下使用Lateral Derived Table，同时需要满足如下要求：

LATERAL关键字只能出现在FROM子句中，可以是用逗号分隔的表列表或者是JOIN（包含JOIN、INNER JOIN、CROSS JOIN、LEFT [OUTER] JOIN 或 RIGHT [OUTER] JOIN）中的一种。
如果LATERAL关键字在JOIN子句的右操作数中，并且包含对左操作数的引用，那么JOIN操作必须是INNER JOIN、CROSS JOIN或LEFT [OUTER] JOIN。如果表在左操作数中，并且包含对右操作数的引用，则JOIN操作必须是RIGHT [OUTER] JOIN。
如果Lateral Derived Table引用聚合函数，则该函数的聚合查询不能是包含当前Lateral Derived Table所属的查询。

-- 满足要求1,2,3
select * from t1, lateral (select * from t2 where t1.c1 = t2.c1);
select * from t1 cross join lateral (select * from t2 where t1.c1 = t2.c1) on 1=1;
select * from t1 left join lateral (select * from t2 where t1.c1 = t2.c1) on 1=1;
select * from lateral (select * from t2 where t1.c1 = t2.c1) right join t1 on 1=1;

-- 不满足要求3
select sum(t1.c1) as s from t1, lateral (select * from t2 where s = t2.c1);
ERROR 1054 (42S22): Unknown column 's' in 'where clause'

典型使用场景

场景1

Lateral关键字可以解决一些select list中的子查询需要返回多列的场景，对于原先的查询需要写两次子查询，但使用Lateral就可以很好地解决这个问题。

-- Q1
select 
(select avg(score) from score s where s.course_id = c.course_id) avg_score,
(select max(score) from score s where s.course_id = c.course_id) max_score
from course c where course_name = 'math';

Q1 会产生 2 次 score 表的扫描任务，使用 Lateral 改写为 Q2 后，score 表可减少 1 次扫描。

-- Q2
select v1.avg_score, v1.max_score
  from course c, 
  lateral (select avg(score) avg_score, max(score) max_score from score s 
                  where s.course_id = c.course_id) v1
  where course_name = 'math';

场景2

-- 当前存在两张表，一张是课程表，还有一张是成绩表
create table Course (
  course_id int primary key,
  course_name varchar(20),
  teacher_id varchar(20)
);

create table Score (
  student_id int,
  course_id int,
  score int,
  key i_course (course_id)
);

现在需要查出数学这门课的平均分和最高分，并且统计出超过平均分人数，基于现有的语法我们可以先使用子查询分别查出平均分和最高分，然后在另外的子查询中筛选出超过平均分的人数。

-- Q3
select 
(select avg(score) from score s where s.course_id = c.course_id) avg_score,
(select max(score) from score s where s.course_id = c.course_id) max_score,
(select count(1) from score s where s.course_id = c.course_id 
   and s.score >  
       (select avg(score) from score s where s.course_id = c.course_id)) gt_avg_count
from course c where course_name = 'math';

===========================================================================
|ID|OPERATOR                            |NAME       |EST.ROWS|EST.TIME(us)|
---------------------------------------------------------------------------
|0 |SUBPLAN FILTER                      |           |1       |95          |
|1 |├─TABLE FULL SCAN                   |c          |1       |4           |
|2 |├─SCALAR GROUP BY                   |           |1       |23          |
|3 |│ └─DISTRIBUTED TABLE RANGE SCAN    |s(i_course)|1       |23          |
|4 |├─SCALAR GROUP BY                   |           |1       |23          |
|5 |│ └─DISTRIBUTED TABLE RANGE SCAN    |s(i_course)|1       |23          |
|6 |└─SCALAR GROUP BY                   |           |1       |46          |
|7 |  └─SUBPLAN FILTER                  |           |1       |46          |
|8 |    ├─DISTRIBUTED TABLE RANGE SCAN  |s(i_course)|1       |23          |
|9 |    └─SCALAR GROUP BY               |           |1       |23          |
|10|      └─DISTRIBUTED TABLE RANGE SCAN|s(i_course)|1       |23          |
===========================================================================

从上面的计划可以看出，查询Q3效率比较差，取平均分的子查询调用了两次，且平均分和最高分的子查询可以合并到一起计算，现在分别使用两个子查询需要对score表扫描两次相同的数据集。

对于查询Q3，可以使用Lateral子句改写一下，将查询最大值和最小值的两个子查询合并，然后查询超过平均分人数的子查询引用外面已经计算好的平均分，改写后的查询语句Q4 如下。

-- Q3
select v1.avg_score, v1.max_score, v2.gv_avg_count
  from course c, 
  lateral (select avg(score) avg_score, max(score) max_score from score s 
                  where s.course_id = c.course_id) v1,
  lateral (select count(1) gv_avg_count from score s 
                  where s.course_id = c.course_id and s.score > v1.avg_score) v2
  where course_name = 'math';

===========================================================================
|ID|OPERATOR                            |NAME       |EST.ROWS|EST.TIME(us)|
---------------------------------------------------------------------------
|0 |NESTED-LOOP JOIN                    |           |1       |51          |
|1 |├─NESTED-LOOP JOIN                  |           |1       |28          |
|2 |│ ├─TABLE FULL SCAN                 |c          |1       |4           |
|3 |│ └─SUBPLAN SCAN                    |v1         |1       |23          |
|4 |│   └─SCALAR GROUP BY               |           |1       |23          |
|5 |│     └─DISTRIBUTED TABLE RANGE SCAN|s(i_course)|1       |23          |
|6 |└─SUBPLAN SCAN                      |v2         |1       |23          |
|7 |  └─SCALAR GROUP BY                 |           |1       |23          |
|8 |    └─DISTRIBUTED TABLE RANGE SCAN  |s(i_course)|1       |23          |
===========================================================================

从改写之后的计划可以看出，效率明显是高于Q3，现在对于score表只需要扫描两次就能得到结果。

优化器改写优化

查询Q2改写为LATERAL子句后，生成的执行计划如下：

=========================================================================
|ID|OPERATOR                          |NAME       |EST.ROWS|EST.TIME(us)|
-------------------------------------------------------------------------
|0 |NESTED-LOOP JOIN                  |           |1       |28          |
|1 |├─TABLE FULL SCAN                 |c          |1       |4           |
|2 |└─SUBPLAN SCAN                    |v1         |1       |23          |
|3 |  └─SCALAR GROUP BY               |           |1       |23          |
|4 |    └─DISTRIBUTED TABLE RANGE SCAN|s(i_course)|1       |23          |
=========================================================================
Outputs & filters:
-------------------------------------
  0 - output([v1.avg_score], [v1.max_score]), filter(nil), rowset=16
      conds(nil), nl_params_([c.course_id(:0)]), use_batch=true
  1 - output([c.course_id]), filter([c.course_name = 'math']), rowset=16
      access([c.course_id], [c.course_name]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false],
      range_key([c.course_id]), range(MIN ; MAX)always true
  2 - output([v1.avg_score], [v1.max_score]), filter(nil), rowset=16
      access([v1.avg_score], [v1.max_score])
  3 - output([T_FUN_SUM(s.score) / cast(T_FUN_COUNT(s.score), DECIMAL(20, 0))], [T_FUN_MAX(s.score)]), filter(nil), rowset=16
      group(nil), agg_func([T_FUN_MAX(s.score)], [T_FUN_SUM(s.score)], [T_FUN_COUNT(s.score)])
  4 - output([s.score]), filter(nil), rowset=16
      access([GROUP_ID], [s.__pk_increment], [s.score]), partitions(p0)
      is_index_back=true, is_global_index=false,
      range_key([s.course_id], [s.__pk_increment]), range(MIN,MIN ; MAX,MAX)always true,
      range_cond([s.course_id = :0])

从执行计划可以看出，需要通过c表驱动v1执行，只能走Nested Loop Join，在c表数据量很小的情况下执行的效率很高。但当c表数据量很大，score表中的数据很少时，执行的效率就会很差。显然Lateral关键字限制了Join Order的枚举顺序，因此需要优化器通过预设的改写规则去除LATERAL关键字，提升计划的枚举空间。

-- Q5
select v1.avg_score, v1.max_score
  from course c, 
  (select course_id, avg(score) avg_score, max(score) max_score from score s 
                  group by course_id ) v1
  where course_name = 'math' and v1.course_id = c.course_id;

=======================================================
|ID|OPERATOR               |NAME|EST.ROWS|EST.TIME(us)|
-------------------------------------------------------
|0 |MERGE JOIN             |    |1       |9           |
|1 |├─TABLE FULL SCAN      |c   |1       |4           |
|2 |└─SORT                 |    |1       |5           |
|3 |  └─SUBPLAN SCAN       |v1  |1       |5           |
|4 |    └─HASH GROUP BY    |    |1       |5           |
|5 |      └─TABLE FULL SCAN|s   |1       |4           |
=======================================================
Outputs & filters:
-------------------------------------
  0 - output([v1.avg_score], [v1.max_score]), filter(nil), rowset=16
      equal_conds([v1.course_id = c.course_id]), other_conds(nil)
      merge_directions([ASC])
  1 - output([c.course_id]), filter([c.course_name = 'math']), rowset=16
      access([c.course_id], [c.course_name]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false],
      range_key([c.course_id]), range(MIN ; MAX)always true
  2 - output([v1.avg_score], [v1.max_score], [v1.course_id]), filter(nil), rowset=16
      sort_keys([v1.course_id, ASC])
  3 - output([v1.course_id], [v1.avg_score], [v1.max_score]), filter(nil), rowset=16
      access([v1.course_id], [v1.avg_score], [v1.max_score])
  4 - output([s.course_id], [T_FUN_SUM(s.score) / cast(T_FUN_COUNT(s.score), DECIMAL(20, 0))], [T_FUN_MAX(s.score)]), filter(nil), rowset=16
      group([s.course_id]), agg_func([T_FUN_MAX(s.score)], [T_FUN_SUM(s.score)], [T_FUN_COUNT(s.score)])
  5 - output([s.course_id], [s.score]), filter(nil), rowset=16
      access([s.course_id], [s.score]), partitions(p0)
      is_index_back=false, is_global_index=false,
      range_key([s.__pk_increment]), range(MIN ; MAX)always true

优化器改写后的SQL去除掉了Lateral关键字，增加了c表和v1的计划枚举空间，在score表数据量很小时，优化器还是会根据代价生成下面的计划，通过v1来驱动c表执行，减少计划执行时间。

=======================================================
|ID|OPERATOR               |NAME|EST.ROWS|EST.TIME(us)|
-------------------------------------------------------
|0 |NESTED-LOOP JOIN       |    |1       |23          |
|1 |├─SUBPLAN SCAN         |v1  |1       |5           |
|2 |│ └─HASH GROUP BY      |    |1       |5           |
|3 |│   └─TABLE FULL SCAN  |s   |1       |4           |
|4 |└─DISTRIBUTED TABLE GET|c   |1       |18          |
=======================================================
Outputs & filters:
-------------------------------------
  0 - output([v1.avg_score], [v1.max_score]), filter(nil), rowset=16
      conds(nil), nl_params_([v1.course_id(:0)]), use_batch=true
  1 - output([v1.course_id], [v1.avg_score], [v1.max_score]), filter(nil), rowset=16
      access([v1.course_id], [v1.avg_score], [v1.max_score])
  2 - output([s.course_id], [T_FUN_SUM(s.score) / cast(T_FUN_COUNT(s.score), DECIMAL(20, 0))], [T_FUN_MAX(s.score)]), filter(nil), rowset=16
      group([s.course_id]), agg_func([T_FUN_MAX(s.score)], [T_FUN_SUM(s.score)], [T_FUN_COUNT(s.score)])
  3 - output([s.course_id], [s.score]), filter(nil), rowset=16
      access([s.course_id], [s.score]), partitions(p0)
      is_index_back=false, is_global_index=false,
      range_key([s.__pk_increment]), range(MIN ; MAX)always true
  4 - output(nil), filter([c.course_name = 'math']), rowset=16
      access([GROUP_ID], [c.course_name]), partitions(p0)
      is_index_back=false, is_global_index=false, filter_before_indexback[false],
      range_key([c.course_id]), range(MIN ; MAX),
      range_cond([:0 = c.course_id])

通过改写可以提高SQL在不同场景下的适应性，减少差计划产生的可能。但是如果业务不需要做这个改写，可以通过NO_DECORRELATE这个hint来禁用对Lateral Derived Table的改写。

-- Q4'
select /*+ NO_DECORRELATE */ v1.avg_score, v1.max_score
  from course c, 
  lateral (select avg(score) avg_score, max(score) max_score from score s 
                  where s.course_id = c.course_id) v1
  where course_name = 'math';

总结

Lateral语法打开了之前同一From子句不能引用前面表的列的限制，在很多情况下都可以⽤来加速SQL执行，或者可以使SQL更容易理解。

本站资源均来自互联网，仅供研究学习，禁止违法使用和商用，产生法律纠纷本站概不负责！如果侵犯了您的权益请与我们联系！

转载请注明出处：免费源码网-免费的源码资源网站 » OceanBase v4.2 特性解析：Lateral Derived Table 优化查询

点赞(0) 打赏

本文分类：文章资讯
本文标签：OceanBase v4.2 特性解析：Lateral Derived Table 优化查询
浏览次数：46 次浏览
本文链接：https://freeymw.com/article/10855.html

上一篇 > 苹果智能：iOS 18 AI增强功能
下一篇 > 【设计模式】创建型设计模式之工厂模式

评论列表共有 0 条评论

暂无评论

OceanBase v4.2 特性解析：Lateral Derived Table 优化查询

前言

LATERAL关键字及其使用实例

典型使用场景

场景1

场景2

优化器改写优化

总结

评论列表 共有 0 条评论

发表评论 取消回复

评论列表共有 0 条评论

发表评论取消回复