SQL调优指南—SQL调优进阶—JOIN优化和执行

简介: 本文主要介绍如何使用JOIN。JOIN将多个表以某个或某些列为条件进行连接操作而检索出关联数据的过程,多个表之间以共同列而关联在一起。

基本概念

JOIN是SQL查询中常见的操作,逻辑上说,它的语义等价于将两张表做笛卡尔积,然后根据过滤条件保留满足条件的数据。JOIN多数情况下是依赖等值条件做的JOIN,即Equi-Join,用来根据某个特定列的值连接两张表的数据。

子查询是指嵌套在SQL内部的查询块,子查询的结果作为输入,填入到外层查询中,从而用于计算外层查询的结果。子查询可以出现在SQL语句的很多地方,比如在SELECT子句中作为输出的数据,在FROM子句中作为输入的一个视图,在WHERE子句中作为过滤条件等。

本文讨论的均为不下推的JOIN算子。如果JOIN被下推到LogicalView中,其执行方式由存储层MySQL自行选择。

JOIN类型

PolarDB-X支持Inner Join,Left Outer Join和Right Outer Join这3种常见的JOIN类型。2.5.png下面是几种不同类型JOIN示例:


/* Inner Join */
SELECT * FROM A, B WHERE A.key = B.key;
/* Left Outer Join */
SELECT * FROM A LEFT JOIN B ON A.key = B.key;
/* Right Outer Join */
SELECT * FROM A RIGHT OUTER JOIN B ON A.key = B.key;

还支持Semi-Join和Anti-Join。Semi Join和Anti Join无法直接用SQL语句来表示,通常由包含关联项的EXISTS或IN子查询转换得到。如下为Semi-Join和Anti-Join的示例。


/* Semi Join - 1 */

SELECT * FROM Emp WHERE Emp.DeptName IN (
SELECT DeptName FROM Dept
)
/ Semi Join - 2 /
SELECT * FROM Emp WHERE EXISTS (
SELECT * FROM Dept WHERE Emp.DeptName = Dept.DeptName
)
/ Anti Join - 1 /
SELECT * FROM Emp WHERE Emp.DeptName NOT IN (
SELECT DeptName FROM Dept
)
/ Anti Join - 2 /
SELECT * FROM Emp WHERE NOT EXISTS (
SELECT * FROM Dept WHERE Emp.DeptName = Dept.DeptName
)

JOIN算法

目前,PolarDB-X支持Nested-Loop Join、Hash Join、Sort-Merge Join和Lookup Join(BKAJoin)等JOIN算法。

Nested-Loop Join (NLJoin)

Nested-Loop Join通常用于非等值的JOIN。它的工作方式如下:

  1. 拉取内表(右表,通常是数据量较小的一边)的全部数据,缓存到内存中。
  2. 遍历外表数据,对于外表的每行:
    • 对于每一条缓存在内存中的内表数据。
    • 构造结果行,并检查是否满足JOIN条件,如果满足条件则输出。
  1. 如下为Nested-Loop Join示例:
> EXPLAIN SELECT * FROM partsupp, supplier WHERE ps_suppkey < s_suppkey;
NlJoin(condition="ps_suppkey < s_suppkey", type="inner")
Gather(concurrent=true)
LogicalView(tables="partsupp_[0-7]", shardCount=8, sql="SELECT * FROM `partsupp` AS `partsupp`")
Gather(concurrent=true)
LogicalView(tables="supplier_[0-7]", shardCount=8, sql="SELECT FROM `supplier` AS `supplier`")

通常来说,Nested-Loop Join是效率最低的JOIN操作,一般只有在JOIN条件不含等值(例如上面的例子)或者内表数据量极小的情况下才会使用。通过如下Hint可以强制PolarDB-X使用Nested-Loop Join以及确定JOIN顺序:


/+TDDL:NL_JOIN(outer_table, inner_table)/ SELECT ...

其中inner_table 和outer_table也可以是多张表的JOIN结果,例如:


/+TDDL:NL_JOIN((outer_table_a, outer_table_b), (inner_table_c, inner_table_d))/ SELECT ...

Hash Join

Hash Join是等值JOIN最常用的算法之一。它的原理如下所示:

  • 拉取内表(右表,通常是数据量较小的一边)的全部数据,写进内存中的哈希表。
  • 遍历外表数据,对于外表的每行:
    • 根据等值条件JOIN Key查询哈希表,取出0-N匹配的行(JOIN Key相同)。
    • 构造结果行,并检查是否满足JOIN条件,如果满足条件则输出。
  • Hash Join示例:
> EXPLAIN SELECT  FROM partsupp, supplier WHERE ps_suppkey = s_suppkey;
HashJoin(condition="ps_suppkey = s_suppkey", type="inner")
Gather(concurrent=true)
LogicalView(tables="partsupp_[0-7]", shardCount=8, sql="SELECT * FROM `partsupp` AS `partsupp`")
Gather(concurrent=true)
LogicalView(tables="supplier_[0-7]", shardCount=8, sql="SELECT FROM `supplier` AS `supplier`")

Hash Join常出现在JOIN数据量较大的复杂查询、且无法通过索引Lookup来改善,这种情况下Hash Join是最优的选择。例如上面的例子中,partsupp表和supplier表均为全表扫描,数据量较大,适合使用HashJoin。由于Hash Join的内表需要用于构造内存中的哈希表,内表的数据量一般小于外表。通常优化器可以自动选择出最优的JOIN顺序。如果需要手动控制,也可以通过下面的Hint。

通过如下Hint可以强制PolarDB-X使用Hash Join以及确定JOIN顺序:


/+TDDL:HASH_JOIN(table_outer, table_inner)/ SELECT ...

Lookup Join (BKAJoin)

Lookup Join是另一种常用的等值JOIN算法,常用于数据量较小的情况。它的原理如下:

  1. 遍历外表(左表,通常是数据量较小的一边)数据,对于外表中的每批(例如1000行)数据。
  2. 将这一批数据的JOIN Key拼成一个IN (....)条件,加到内表的查询中。
  3. 执行内表查询,得到JOIN匹配的行。
  4. 借助哈希表,为外表的每行找到匹配的内表行,组合并输出。

Lookup Join (BKAJoin)示例:


> EXPLAIN SELECT  FROM partsupp, supplier WHERE ps_suppkey = s_suppkey AND ps_partkey = 123;
BKAJoin(condition="ps_suppkey = s_suppkey", type="inner")
LogicalView(tables="partsupp_3", sql="SELECT * FROM `partsupp` AS `partsupp` WHERE (`ps_partkey` = ?)")
Gather(concurrent=true)
LogicalView(tables="supplier_[0-7]", shardCount=8, sql="SELECT FROM `supplier` AS `supplier` WHERE (`s_suppkey` IN ('?'))")

Lookup Join通常用于外表数据量较小的情况,例如上面的例子中,左表partsupp由于存在ps_partkey = 123的过滤条件,仅有几行数据。此外,右表的s_suppkey IN ( ... )查询命中了主键索引,这也使得Lookup Join的查询代价进一步降低。

通过如下Hint可以强制PolarDB-X使用LookupJoin以及确定JOIN顺序:


/+TDDL:BKA_JOIN(table_outer, table_inner)/ SELECT ...


说明 Lookup Join的内表只能是单张表,不可以是多张表JOIN的结果。

Sort-Merge Join

Sort-Merge Join是另一种等值JOIN算法,它依赖左右两边输入的顺序,必须按JOIN Key排序。它的原理如下:

  1. 开始Sort-Merge Join之前,输入端必须排序(借助MergeSort或MemSort)。
  2. 比较当前左右表输入的行,并按以下方式操作,不断消费左右两边的输入:
    • 如果左表的JOIN Key较小,则消费左表的下一条数据。
    • 如果右表的JOIN Key较小,则消费右表的下一条数据。
    • 如果左右表JOIN Key相等,说明获得了1条或多条匹配,检查是否满足JOIN条件并输出。

Lookup Join (BKAJoin)示例:


> EXPLAIN SELECT  FROM partsupp, supplier WHERE ps_suppkey = s_suppkey ORDER BY s_suppkey;
SortMergeJoin(condition="ps_suppkey = s_suppkey", type="inner")
MergeSort(sort="ps_suppkey ASC")
LogicalView(tables="QIMU_0000_GROUP,QIMU_0001_GROUP.partsupp_[0-7]", shardCount=8, sql="SELECT * FROM `partsupp` AS `partsupp` ORDER BY `ps_suppkey`")
MergeSort(sort="s_suppkey ASC")
LogicalView(tables="QIMU_0000_GROUP,QIMU_0001_GROUP.supplier_[0-7]", shardCount=8, sql="SELECT FROM `supplier` AS `supplier` ORDER BY `s_suppkey`")

上面执行计划中的 MergeSort算子以及下推的ORDER BY,这保证了Sort-Merge Join两边的输入按JOIN Key即s_suppkey (ps_suppkey)排序。

Sort-Merge Join由于需要额外的排序步骤,通常Sort-Merge Join并不是最优的。但是,某些情况下客户端查询恰好也需要按JOIN Key排序(上面的例子),这时候使用Sort-Merge Join是较优的选择。

通过如下Hint可以强制PolarDB-X使用Sort-Merge Join


/+TDDL:SORT_MERGE_JOIN(table_a, table_b)/ SELECT ...

JOIN顺序

在多表连接的场景中,优化器的一个很重要的任务是决定各个表之间的连接顺序,因为不同的连接顺序会影响中间结果集的大小,进而影响到计划整体的执行代价。

例如,对于4张表JOIN(暂不考虑下推的情形),JOIN Tree可以有如下3种形式,同时表的排列又有4! = 24种,一共有72种可能的JOIN顺序。3.2.png

下面是几种不同类型JOIN示例:


/ Inner Join */
SELECT * FROM A, B WHERE A.key = B.key;
/ Left Outer Join /
SELECT * FROM A LEFT JOIN B ON A.key = B.key;
/ Right Outer Join /
SELECT FROM A RIGHT OUTER JOIN B ON A.key = B.key;

还支持Semi-Join和Anti-Join。Semi Join和Anti Join无法直接用SQL语句来表示,通常由包含关联项的EXISTS或IN子查询转换得到。如下为Semi-Join和Anti-Join的示例。


/ Semi Join - 1 */
SELECT * FROM Emp WHERE Emp.DeptName IN (
SELECT DeptName FROM Dept
)
/ Semi Join - 2 /
SELECT * FROM Emp WHERE EXISTS (
SELECT * FROM Dept WHERE Emp.DeptName = Dept.DeptName
)
/ Anti Join - 1 /
SELECT * FROM Emp WHERE Emp.DeptName NOT IN (
SELECT DeptName FROM Dept
)
/ Anti Join - 2 /
SELECT * FROM Emp WHERE NOT EXISTS (
SELECT * FROM Dept WHERE Emp.DeptName = Dept.DeptName
)

JOIN算法

目前,PolarDB-X支持Nested-Loop Join、Hash Join、Sort-Merge Join和Lookup Join(BKAJoin)等JOIN算法。

Nested-Loop Join (NLJoin)

Nested-Loop Join通常用于非等值的JOIN。它的工作方式如下:

  1. 拉取内表(右表,通常是数据量较小的一边)的全部数据,缓存到内存中。
  2. 遍历外表数据,对于外表的每行:
    • 对于每一条缓存在内存中的内表数据。
    • 构造结果行,并检查是否满足JOIN条件,如果满足条件则输出。
  1. 如下为Nested-Loop Join示例:
> EXPLAIN SELECT * FROM partsupp, supplier WHERE ps_suppkey < s_suppkey;
NlJoin(condition="ps_suppkey < s_suppkey", type="inner")
Gather(concurrent=true)
LogicalView(tables="partsupp_[0-7]", shardCount=8, sql="SELECT * FROM `partsupp` AS `partsupp`")
Gather(concurrent=true)
LogicalView(tables="supplier_[0-7]", shardCount=8, sql="SELECT FROM `supplier` AS `supplier`")

通常来说,Nested-Loop Join是效率最低的JOIN操作,一般只有在JOIN条件不含等值(例如上面的例子)或者内表数据量极小的情况下才会使用。通过如下Hint可以强制PolarDB-X使用Nested-Loop Join以及确定JOIN顺序:


/+TDDL:NL_JOIN(outer_table, inner_table)/ SELECT ...

其中inner_table 和outer_table也可以是多张表的JOIN结果,例如:


/+TDDL:NL_JOIN((outer_table_a, outer_table_b), (inner_table_c, inner_table_d))/ SELECT ...

Hash Join

Hash Join是等值JOIN最常用的算法之一。它的原理如下所示:

  • 拉取内表(右表,通常是数据量较小的一边)的全部数据,写进内存中的哈希表。
  • 遍历外表数据,对于外表的每行:
    • 根据等值条件JOIN Key查询哈希表,取出0-N匹配的行(JOIN Key相同)。
    • 构造结果行,并检查是否满足JOIN条件,如果满足条件则输出。
  • Hash Join示例:
> EXPLAIN SELECT  FROM partsupp, supplier WHERE ps_suppkey = s_suppkey;
HashJoin(condition="ps_suppkey = s_suppkey", type="inner")
Gather(concurrent=true)
LogicalView(tables="partsupp_[0-7]", shardCount=8, sql="SELECT * FROM `partsupp` AS `partsupp`")
Gather(concurrent=true)
LogicalView(tables="supplier_[0-7]", shardCount=8, sql="SELECT FROM `supplier` AS `supplier`")

Hash Join常出现在JOIN数据量较大的复杂查询、且无法通过索引Lookup来改善,这种情况下Hash Join是最优的选择。例如上面的例子中,partsupp表和supplier表均为全表扫描,数据量较大,适合使用HashJoin。由于Hash Join的内表需要用于构造内存中的哈希表,内表的数据量一般小于外表。通常优化器可以自动选择出最优的JOIN顺序。如果需要手动控制,也可以通过下面的Hint。

通过如下Hint可以强制PolarDB-X使用Hash Join以及确定JOIN顺序:


/+TDDL:HASH_JOIN(table_outer, table_inner)/ SELECT ...

Lookup Join (BKAJoin)

Lookup Join是另一种常用的等值JOIN算法,常用于数据量较小的情况。它的原理如下:

  1. 遍历外表(左表,通常是数据量较小的一边)数据,对于外表中的每批(例如1000行)数据。
  2. 将这一批数据的JOIN Key拼成一个IN (....)条件,加到内表的查询中。
  3. 执行内表查询,得到JOIN匹配的行。
  4. 借助哈希表,为外表的每行找到匹配的内表行,组合并输出。

Lookup Join (BKAJoin)示例:


> EXPLAIN SELECT  FROM partsupp, supplier WHERE ps_suppkey = s_suppkey AND ps_partkey = 123;
BKAJoin(condition="ps_suppkey = s_suppkey", type="inner")
LogicalView(tables="partsupp_3", sql="SELECT * FROM `partsupp` AS `partsupp` WHERE (`ps_partkey` = ?)")
Gather(concurrent=true)
LogicalView(tables="supplier_[0-7]", shardCount=8, sql="SELECT FROM `supplier` AS `supplier` WHERE (`s_suppkey` IN ('?'))")

Lookup Join通常用于外表数据量较小的情况,例如上面的例子中,左表partsupp由于存在ps_partkey = 123的过滤条件,仅有几行数据。此外,右表的s_suppkey IN ( ... )查询命中了主键索引,这也使得Lookup Join的查询代价进一步降低。

通过如下Hint可以强制PolarDB-X使用LookupJoin以及确定JOIN顺序:


/+TDDL:BKA_JOIN(table_outer, table_inner)/ SELECT ...


说明 Lookup Join的内表只能是单张表,不可以是多张表JOIN的结果。

Sort-Merge Join

Sort-Merge Join是另一种等值JOIN算法,它依赖左右两边输入的顺序,必须按JOIN Key排序。它的原理如下:

  1. 开始Sort-Merge Join之前,输入端必须排序(借助MergeSort或MemSort)。
  2. 比较当前左右表输入的行,并按以下方式操作,不断消费左右两边的输入:
    • 如果左表的JOIN Key较小,则消费左表的下一条数据。
    • 如果右表的JOIN Key较小,则消费右表的下一条数据。
    • 如果左右表JOIN Key相等,说明获得了1条或多条匹配,检查是否满足JOIN条件并输出。

Lookup Join (BKAJoin)示例:


> EXPLAIN SELECT  FROM partsupp, supplier WHERE ps_suppkey = s_suppkey ORDER BY s_suppkey;
SortMergeJoin(condition="ps_suppkey = s_suppkey", type="inner")
MergeSort(sort="ps_suppkey ASC")
LogicalView(tables="QIMU_0000_GROUP,QIMU_0001_GROUP.partsupp_[0-7]", shardCount=8, sql="SELECT * FROM `partsupp` AS `partsupp` ORDER BY `ps_suppkey`")
MergeSort(sort="s_suppkey ASC")
LogicalView(tables="QIMU_0000_GROUP,QIMU_0001_GROUP.supplier_[0-7]", shardCount=8, sql="SELECT FROM `supplier` AS `supplier` ORDER BY `s_suppkey`")

上面执行计划中的 MergeSort算子以及下推的ORDER BY,这保证了Sort-Merge Join两边的输入按JOIN Key即s_suppkey (ps_suppkey)排序。

Sort-Merge Join由于需要额外的排序步骤,通常Sort-Merge Join并不是最优的。但是,某些情况下客户端查询恰好也需要按JOIN Key排序(上面的例子),这时候使用Sort-Merge Join是较优的选择。

通过如下Hint可以强制PolarDB-X使用Sort-Merge Join


/+TDDL:SORT_MERGE_JOIN(table_a, table_b)*/ SELECT ...

JOIN顺序

在多表连接的场景中,优化器的一个很重要的任务是决定各个表之间的连接顺序,因为不同的连接顺序会影响中间结果集的大小,进而影响到计划整体的执行代价。

例如,对于4张表JOIN(暂不考虑下推的情形),JOIN Tree可以有如下3种形式,同时表的排列又有4! = 24种,一共有72种可能的JOIN顺序。

相关文章
|
存储
【C盘瘦身】如何清理Wechat Files,经常使用电脑微信用户必知的常识!
【C盘瘦身】如何清理Wechat Files,经常使用电脑微信用户必知的常识!
3415 0
【C盘瘦身】如何清理Wechat Files,经常使用电脑微信用户必知的常识!
ly~
|
存储 算法 编译器
游戏开发中,C 语言的性能优势体现在哪些方面?
在游戏开发中,C 语言凭借其对硬件的直接访问和内存操作的精准控制,能够显著提升性能。它允许开发者手动管理内存,优化数据存储和读取,充分利用显卡等硬件资源,实现流畅的图形渲染和音效处理。作为一种接近底层的语言,C 语言具有高效的执行速度,适用于物理引擎和碰撞检测等高性能需求模块,并且提供了丰富的运算符和数据类型,便于实现高效的算法。此外,C 语言代码具有良好的可移植性和跨平台性,支持多种操作系统和硬件平台,减少了多平台发布的开发成本。编译器提供的优化选项和手动代码优化的灵活性进一步提升了游戏的整体性能。
ly~
479 5
|
负载均衡 网络协议 算法
|
人工智能 Serverless API
尽享红利,Serverless构建企业AI应用方案与实践
本次课程由阿里云云原生架构师计缘分享,主题为“尽享红利,Serverless构建企业AI应用方案与实践”。课程分为四个部分:1) Serverless技术价值,介绍其发展趋势及优势;2) Serverless函数计算与AI的结合,探讨两者融合的应用场景;3) Serverless函数计算AIGC应用方案,展示具体的技术实现和客户案例;4) 业务初期如何降低使用门槛,提供新用户权益和免费资源。通过这些内容,帮助企业和开发者快速构建高效、低成本的AI应用。
456 12
|
12月前
|
人工智能 Cloud Native API
Forrester Wave:阿里云公有云产品及战略能力全球第二
阿里云被评为Forrester 公有云平台Wave评估领导者,是唯一一家入选领导者象限的中国厂商,其中阿里云在共30个打分项中有17项为最高分。
|
搜索推荐 数据挖掘 UED
中小企业CRM解决方案排名:性价比之选
中小企业在选择CRM系统时面临的主要痛点包括预算有限、需求多样、IT资源匮乏等。因此,他们需要寻找高性价比、功能全面、操作简便且具备灵活性和可扩展性的解决方案。本文推荐了纷享销客、Zoho CRM、Apptivo和简道云CRM等几款适合中小企业的CRM系统,这些系统不仅能满足企业的多样化需求,还能助力企业高效管理客户关系,提升销售业绩。
|
弹性计算 供应链 数据库
阿里云服务器付费类型包年包月、按量付费、抢占式实例区别及适用场景参考
阿里云服务器有包年包月、按量付费、抢占式实例三种付费类型可选,不同类型的模式收费标准与适用场景是不一样的,有的用户由于初次购买阿里云服务器,并不清楚他们之间的区别,以及什么样的情况下应该选择哪种付费类型,本文为大家介绍下阿里云服务器付费类型包年包月、按量付费、抢占式实例区别及适用场景参考。
1502 0
阿里云服务器付费类型包年包月、按量付费、抢占式实例区别及适用场景参考
|
自然语言处理 数据挖掘 Unix
R 语言是什么、R 的优势、资源|学习笔记
快速学习 R 语言是什么、R 的优势、资源。
430 1
R 语言是什么、R 的优势、资源|学习笔记
|
存储 安全 前端开发
基于Python guI的学生信息管理系统的设计与实现
基于Python guI的学生信息管理系统的设计与实现
1648 0
|
算法 数据安全/隐私保护 Python
凯撒加密Caesar cipher
凯撒加密Caesar cipher
753 0
凯撒加密Caesar cipher