OceanBase v4.2 特性解析：Lateral Derived Table 優化查詢

前言

從傳統規則來看，內聯視圖通常不允許引用在同一FROM子句中前面定義的表的列。但從OceanBase?4.2.2版本開始，這一限制得到了突破，允許內聯視圖作為Lateral Derived Table來定義，從而允許此類引用。Lateral Derived Table的語法與普通內聯視圖的語法相似，只是需要在內聯視圖之前之前添加關鍵字LATERAL。LATERAL關鍵字必須緊跟在需要作為Lateral Derived Table的每一個子查詢之前。

LATERAL關鍵字及其使用實例

允許在MySQL模式和Oracle模式下使用Lateral Derived Table，同時需要滿足如下要求：

  1. LATERAL關鍵字只能出現在FROM子句中，可以是用逗號分隔的表列表或者是JOIN（包含JOIN、INNER JOIN、CROSS JOIN、LEFT [OUTER] JOIN 或 RIGHT [OUTER] JOIN）中的一種。
  2. 如果LATERAL關鍵字在JOIN子句的右操作數中，并且包含對左操作數的引用，那么JOIN操作必須是INNER JOIN、CROSS JOIN或LEFT [OUTER] JOIN。如果表在左操作數中，并且包含對右操作數的引用，則JOIN操作必須是RIGHT [OUTER] JOIN。
  3. 如果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_scorefrom course c, lateral (select avg(score) avg_score, max(score) max_score from score s where s.course_id = c.course_id) v1where 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_countfrom 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) v2where 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=16conds(nil), nl_params_([c.course_id(:0)]), use_batch=true1 - output([c.course_id]), filter([c.course_name = 'math']), rowset=16access([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 true2 - output([v1.avg_score], [v1.max_score]), filter(nil), rowset=16access([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=16group(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=16access([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_scorefrom course c, (select course_id, avg(score) avg_score, max(score) max_score from score s group by course_id ) v1where 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=16equal_conds([v1.course_id = c.course_id]), other_conds(nil)merge_directions([ASC])1 - output([c.course_id]), filter([c.course_name = 'math']), rowset=16access([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 true2 - output([v1.avg_score], [v1.max_score], [v1.course_id]), filter(nil), rowset=16sort_keys([v1.course_id, ASC])3 - output([v1.course_id], [v1.avg_score], [v1.max_score]), filter(nil), rowset=16access([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=16group([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=16access([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=16conds(nil), nl_params_([v1.course_id(:0)]), use_batch=true1 - output([v1.course_id], [v1.avg_score], [v1.max_score]), filter(nil), rowset=16access([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=16group([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=16access([s.course_id], [s.score]), partitions(p0)is_index_back=false, is_global_index=false,range_key([s.__pk_increment]), range(MIN ; MAX)always true4 - output(nil), filter([c.course_name = 'math']), rowset=16access([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_scorefrom course c, lateral (select avg(score) avg_score, max(score) max_score from score s where s.course_id = c.course_id) v1where course_name = 'math';

總結

Lateral語法打開了之前同一From子句不能引用前面表的列的限制，在很多情況下都可以?來加速SQL執行，或者可以使SQL更容易理解。

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/web/23892.shtml
繁體地址，請注明出處：http://hk.pswp.cn/web/23892.shtml
英文地址，請注明出處：http://en.pswp.cn/web/23892.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

26-LINUX--I/O復用-select

26-LINUX--I/O復用-select

一.I/O復用概述 /O復用使得多個程序能夠同時監聽多個文件描述符，對提高程序的性能有很大幫助。以下情況適用于I/O復用技術： ? TCP 服務器同時要處理監聽套接字和連接套接字。 ? 服務器要同時處理 TCP 請求和 UDP 請求。 ? 程序要同時處理多個套接…
閱讀更多...
Facebook廣告素材如何測試？手把手教你！

Facebook廣告素材如何測試？手把手教你！

廣告素材對Facebook廣告效果的影響是很大的，用對了素材，Facebook廣告的價值就越高。廣告主們通常會先通過廣告測試，根據數據反饋來挑選素材。今天就手把手教你做Facebook素材測試的技巧，讓你更有靈感和思路！ 創意測試 …
閱讀更多...
Hudi CLI 安裝配置總結

Hudi CLI 安裝配置總結

前言 上篇文章 總結了Spark SQL Rollback, Hudi CLI 也能實現 Rollback，本文總結下 Hudi CLI 安裝配置以及遇到的問題。 官方文檔 https://hudi.apache.org/cn/docs/cli/ 版本 Hudi 0.13.0（發現有bug）、（然后升級）0.14.1Spark 3.2.3打包 mvn clean package -DskipTes…
閱讀更多...
使用 Django 構建動態網頁

使用 Django 構建動態網頁

文章目錄 創建 Django 項目和應用程序創建 HTML 模板創建視圖函數配置 URL 路由運行 Django 服務器使用 Django 模板語言 Django 是一個流行的 Python Web 框架，它能夠幫助開發人員快速構建強大的 Web 應用程序。在 Django 中，HTML 是用于呈現網頁內容的…
閱讀更多...
Spring Boot 復習

Spring Boot 復習

2 3 5（不考） 9 (1)RestController 注解是一個組合注解，等同于Controller 和ResponseBody 兩個注解結合使用的效果。主要作用是將當前類作為控制層的組件添加到 Spring 容器中，同時該類的方法無法返回 JSP 頁面，而且…
閱讀更多...
Flutter 中的 RenderObjectToWidgetAdapter 小部件：全面指南

Flutter 中的 RenderObjectToWidgetAdapter 小部件：全面指南

Flutter 中的 RenderObjectToWidgetAdapter 小部件：全面指南 Flutter 是一個功能強大的 UI 框架，由 Google 開發，允許開發者使用 Dart 語言構建跨平臺的移動、Web 和桌面應用。在 Flutter 的渲染體系中，RenderObjectToWidgetAdap…
閱讀更多...
MyBatis面試題系列三

MyBatis面試題系列三

1、#{}和${}的區別是什么？ #{}是預編譯處理，${}是字符串替換。 Mybatis 在處理#{}時，會將 sql 中的#{}替換為?號，調用 PreparedStatement 的 set 方法來賦值； Mybatis 在處理${}時，就是把${}替換成變量的值…
閱讀更多...
SpringBoot項目啟動時“jar中沒有主清單屬性”異常

SpringBoot項目啟動時“jar中沒有主清單屬性”異常

資料參考 Spring Boot 啟動時 “jar中沒有主清單屬性” 異常 - spring 中文網 (springdoc.cn) 實際解決 更詳細的參考以上&#xff0c;我這邊的話只需要在 pom文件 中加上 spring-boot-maven-plugin 插件就能解決該異常&#xff0c;具體如下&#xff1a; <build><p…
閱讀更多...
1. 計算機系統概述

1. 計算機系統概述

1. 計算機系統概述 文章目錄 1. 計算機系統概述1.1 計算機的發展硬件的發展軟件的發展 1.2.1 計算機硬件的基本組成早期馮諾依曼的結構現代計算機的結構 1.2.2 各個硬件的工作原理主存儲器運算器控制器計算機工作過程 1.2.3 計算機系統的多級層次結構1.3 計算機的性能指標存儲器…
閱讀更多...
GD32如何配置中斷優先級分組以及中斷優先級

GD32如何配置中斷優先級分組以及中斷優先級

使用GD32 MCU的過程中&#xff0c;大家可能會有以下疑問&#xff1a;中斷優先級如何配置和使用&#xff1f; 本文將會為大家解析中斷優先級分組以及中斷優先級的配置使用&#xff1a; 中斷優先級分組配置 一個GD32 MCU系統需要大家明確系統中使用的中斷優先級分組&#xff0…
閱讀更多...
代駕公司在市場競爭中如何保持優勢？

代駕公司在市場競爭中如何保持優勢？

在競爭激烈的市場中&#xff0c;代駕公司可以通過多種策略保持其競爭優勢&#xff0c;包括利用市場潛力、創新服務模式、提高服務效率以及加強品牌建設等。以下是具體的策略&#xff1a; 利用市場潛力 汽車產業空間巨大&#xff1a;隨著汽車保有量的增加&#xff0c;代駕行業…
閱讀更多...
掃地機器人：卷價格，不如卷技術

掃地機器人：卷價格，不如卷技術

掃地機器人內卷的終點是技術和價值&#xff0c;價格只是附屬品。 一路上漲的價格&#xff0c;一路下跌的銷量 從價格飆升&#xff0c;到重新卷回價格&#xff0c;尷尬的背后是掃地機器人在骨感現實下的無奈抉擇。 根據數據顯示&#xff0c;2020中國掃地機器人線上市場零售均價…
閱讀更多...
通過可識別性和深度學習重建大腦功能網絡

通過可識別性和深度學習重建大腦功能網絡

摘要 本研究提出了一種新的方法來重建代表大腦動力學的功能網絡&#xff0c;該方法基于兩個腦區在同一認知任務中的共同參與會導致其可識別性或其動力學特性降低的觀點。這種可識別性是通過深度學習模型在監督分類任務中獲得的分數來估計的&#xff0c;因此不需要對這種協同參…
閱讀更多...
零、測試開發前置知識

零、測試開發前置知識

文章目錄 1、什么是冒煙測試、回歸測試&#xff1f;2、設計測試用例的方法有哪些&#xff1f;3、對于404或500&#xff0c;你會如何分析定位&#xff1f;4、什么是敏捷開發&#xff1f;敏捷開發流程是怎么樣的&#xff1f;5、做接口測試過程中&#xff0c;下游接口需要上游數據…
閱讀更多...
Flink端到端的精確一次（Exactly-Once）

Flink端到端的精確一次（Exactly-Once）

目錄 狀態一致性 端到端的狀態一致性 端到端精確一次&#xff08;End-To-End Exactly-Once&#xff09; Flink內部的Exactly-Once 輸入端保證 輸出端保證 冪等寫入 事務寫入 Flink和Kafka連接時的精確一次保證 整體介紹 需要的配置 案例 狀態一致性 流式計算本身就…
閱讀更多...
Java工作學習筆記

Java工作學習筆記

1、ConfigurationProperties注解是什么意思&#xff1f; ConfigurationProperties 可以將屬性文件與一個Java類綁定&#xff0c;將屬性文件中的變量值注入到該Java類的成員變量中 示例代碼&#xff1a; /*** SSP配置** author mua*/ Component Data ConfigurationProperties…
閱讀更多...
如何提高接口響應速度

如何提高接口響應速度

在非大數據&#xff08;幾萬以上記錄&#xff09;的情況下&#xff0c;影響接口響應速度的因素中最大的是查詢數據庫的次數&#xff0c;其次才是數組遍歷和簡單數據處理&#xff08;如根據已有字段增加新的屬性&#xff0c;或計算值&#xff09;。 一般一次數據庫查詢需要50毫秒…
閱讀更多...
Java Web應用，IPv6問題解決

Java Web應用，IPv6問題解決

在Java Web程序中&#xff0c;如果使用Tomcat并遇到了IPv6相關的問題&#xff0c;可以通過以下幾種方式來解決&#xff1a; 1. 配置Tomcat以使用IPv4 默認情況下&#xff0c;Java可能會優先使用IPv6。如果你希望Tomcat使用IPv4&#xff0c;最簡單的方法是通過設置系統屬性來強…
閱讀更多...
無線麥克風哪個牌子性價比高？一文告訴你無線領夾麥克風怎么挑選

無線麥克風哪個牌子性價比高？一文告訴你無線領夾麥克風怎么挑選

?當我們談論到演講、表演或者錄制視頻時&#xff0c;一個高質量的無線麥克風能夠使得整個體驗提升至一個全新的水平。它不僅能夠保證聲音的清晰度和真實度&#xff0c;還能夠讓使用者在演講或者表演時更加自信和舒適。基于對市場的深入研究和用戶體驗的考量&#xff0c;我挑選…
閱讀更多...
TypeScript 中的 tsconfig.json

TypeScript 中的 tsconfig.json

什么是 tsconfig.json&#xff1f; tsconfig.json 是 TypeScript 編譯器的配置文件&#xff0c;用于指導編譯器如何編譯 TypeScript 代碼。在 TypeScript 項目中&#xff0c;如果存在這個文件&#xff0c;那么在執行 tsc 命令時&#xff0c;編譯器將會使用該文件中定義的配置選…
閱讀更多...
最新文章

Copyright @ 2022~2023