首页 > 数据库 >企业实战总结:SQL Join执行的常见问题及解决方案

企业实战总结:SQL Join执行的常见问题及解决方案

时间:2023-12-25 20:32:30浏览次数:48  
标签:常见问题 join outer k2 k1 SQL Join select left


1.背景

SQL 的join 对于数据开发同学是最经常遇到的操作,通过表与表之间的关联来得到想要的数据。

但是在开发中我们会遇到一些莫名奇妙的问题,本文就选择最常见的两类问题跟大家分享。

2.结果不符合预期

2.1 string和bigint做join,出现重复数据

这个问题源于底层的隐式转换规则,当string和bigint做比较的时候,会将两侧分别转换为double类型,而这个转换可能存在一定的精度损失,导致出现本来不相等的两个值能够join上,例如:

create table tbl_str as select "111111111111111111" k;
create table tbl_int as select  111111111111111110 k;
select a.k ak, b.k bk
from tbl_str a
join tbl_int b
on a.k = b.k;
-- 并不相同的两个值可以得到结果
+----+------------+
| ak | bk         |
+----+------------+
| 111111111111111111 | 111111111111111110 |
+----+------------+
-- 原因在于上述sql被转化为了
select a.k ak, b.k bk
from tbl_str a
join tbl_int b
on cast(a.k as double) = cast(b.k as double);

其实大家在写join语句的时候,一般对join key的类型不会太在意,而且对于比较小的数字,转化成double类型也并不会有精度损失,但是随着业务的变化,有可能会出现较长的数字id,这里就有存在结果不正确的风险。针对这种情况,一般的解决方法是在join on条件的一侧做一次显式的转换,让两侧的类型一致即可。

select a.k ak, b.k bk
from tbl_str a
join tbl_int b
on a.k = cast(b.k as string);

2.2 string和bigint做join,出现重复数据

这个情况和上述情况的原因是一样的,但是出现的现象不同。这里是string列中出现"nan"时,会转换为double的nan值,目前MaxCompute还不支持nan的double值,此时会出现排序异常无法退出的情况。解决方法同上。

2.3 过滤条件位置不对导致outer join退化为inner join

例如本来想做一个outer join,但是由于分区过滤条件写到外面导致实际执行的是inner join,本质是使用JOIN时分区剪裁失效导致 ,例如:

select a.k ak, b.k bk
from tbl1 a
left outer join tbl2 b
on a.k = b.k
where b.ds='20210101';

需要把限制条件写到where子句中;

select a.k ak, b.k bk
from tbl1 a
left outer join
(select k from tbl2 where ds = '20210101') b
on a.k = b.k ;

分区裁剪失效的情况

自定义函数导致分区剪裁失效:需要使用关键字explain + sql来查看

使用JOIN时分区剪裁失效

在SQL语句中使用JOIN进行关联时:

1.如果分区剪裁条件放在WHERE子句中,则分区剪裁会生效。

2.如果分区剪裁条件放在ON子句中,从表的分区剪裁会生效,主表则不会生效。

LEFT OUTER JOIN

1.分区剪裁条件均放在ON子句中:左表进行了全表扫描,只有右表的分区裁剪有效果;

2.分区剪裁条件均放在WHERE子句中:两张表的分区裁剪都有效果。

RIGHT OUTER JOIN与LEFT OUTER JOIN类似,如果分区剪裁条件放在ON子句中则只有RIGHT OUTER JOIN的左表生效。如果分区剪裁条件放在WHERE中,则两张表都会生效。、

FULL OUTER JOIN

分区剪裁条件只有都放在WHERE子句中才会生效,放在ON子句中都不会生效。

3.性能问题

3.1 join一侧大表,一侧小表(几十MB以内)

这种可以使用mapjoin的方式,目前MaxCompute会自动对一些小表做mapjoin,但是阈值比较保守,用户也可以用hint自己打开。下面例子就是对b和c都是小表的场景做的优化。

select /*+mapjoin(b,c)*/ a.k ak, b.v1, c.v2
from tbl1 a
left outer join tbl2 b
on a.k = b.k
left outer join tbl3 c
on a.k = c.k;

需要注意的是mapjoin括号中的表是小表侧,而且是不能当做可能会补null的一边,也就是left join的左表,right join的右表,full join的两端,否则就算是写了hint也也不起作用。有一个配置可以调整mapjoin的小表侧的大小,超出会在运行过程中报错,目前默认值是640(单位MB),最大可以增加到8192。

3.2 多路join,join on 多列,有相同的前缀列

多路的join的on条件,如果使用的都是merge join且有部分相同的列,可以把较短的放前面:

select a.*, b.v1, c.v2
from tbl1 a
left outer join tbl2 b
on a.k1 = b.k1 and a.k2 = b.k2
left outer join tbl3 c
on a.k1 = c.k1;

这里tbl3的join key为k1,tbl2的join key是k1, k2,tbl3的join key更短,可以放在前面。

select a.*, b.v1, c.v2
from tbl1 a
left outer join tbl3 c
on a.k1 = c.k1;
left outer join tbl2 b
on a.k1 = b.k1 and a.k2 = b.k2

这样可以尽可能使用前一个join已经shuffle好的结果,通过一个局部的排序就避免第二步产生额外的shuffle。

此外还有一种情况,join多列,列都相同,但是顺序不一致,也可能导致执行计划较差。

select a.*, b.v1, c.v2
from tbl1 a
left outer join tbl2 b
on a.k1 = b.k1 and a.k2 = b.k2
left outer join tbl3 c
on a.k2 = c.k2 and a.k1 = c.k1;
-- 需要调整join的k1 k2的顺序为
-- on a.k1 = c.k1 and a.k2 = c.k2;

3.3 多路join,每个join的key不一致,表的大小差异较大

多路join,每个join的key不一致会产生多次的shuffle,每次的shuffle的数据是上一次join产生的结果,为了减少shuffle的数据量,应该在不改变语义的情况下,遵从以下原则,优先级由高到低:

1.能过滤数据的join放前面,产生数据膨胀的放后面;

2.结果行数区别不大的情况下,每行数据小的放前面,每行数据大的放后面;3.mapjoin放在最后面;

下面的例子中,如果b表可以过滤a表的数据,c和d均不会产生膨胀,且c表join过来的字段较小,e表会产生膨胀,则一个比较理想的join顺序如下:

select a.k ak, b.v1, c.v2, d.v3
from tbl1 a
join tbl2 b --b表可以过滤a表的部分数据
on a.k1 = b.k1
left outer join tbl3 c --c不会导致数据膨胀,且c.v2较小
on a.k2 = c.k2
left outer join tbl4 d --d不会导致数据膨胀,d.v3比c.v2较大
on a.k3 = d.k3
left outer join tbl5 e --e会产生数据膨胀
on a.k4 = e.k4

3.4 join中有一侧表存在数据倾斜

数据倾斜也是join中的一类常见问题,例如要对在购物车的商品以商品id做关联,总是免不了出现部分热门商品在购物车的次数远远超出平均值的情况。

select a.id, b.name
from shopping_cart a
left outer join dim b
on a.id = b.id;

这时会出现严重的长尾。以前为了避免这种情况,往往需要用户自己拆表,长尾的数据做mapjoin,非长尾的数据做merge join。

标签:常见问题,join,outer,k2,k1,SQL,Join,select,left
From: https://blog.51cto.com/u_15346267/8971761

相关文章

  • mysql执行计划之Extra列-Using where
    顾名思义,Extra列是用来说明一些额外信息的,我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息很多。这里单说Usingwhere。Usingwhere只是表示MySQL使用where子句中的条件对记录进行了过滤。与是否全表扫描或读取了索......
  • 无涯教程-PostgreSQL - 连接C/C++
    本教程将使用libpqxx库,该库是PostgreSQL的官方C++客户端API。libpqxx的源代码在BSD许可下可用,因此您可以免费下载,将其传递给他人,进行更改,出售,将其包含在自己的代码中,并与选择的任何人共享您的更改。安装Libpqxx可以从以下链接下载最新版本的libpqxx:下载Libpqxx。因此,下载......
  • Mysql基础增删改查语句
    一,基础语句1.增加Insert(特殊的如果id自动递增的话,就不需要插入id)基本语法 insertinto表名(列1,列2,列3,列4,...) values(值,值,值)例子 insertintostudent(name,sex,age)values('张三',18,'男')插入的另外一种形式:insertinto表名set列=值,列=值,列=值,....例子 insertinto......
  • 无涯教程-PostgreSQL - 常用函数
    PostgreSQL内置函数(也称为聚合函数)用于对字符串或数字数据执行处理。以下是所有通用PostgreSQL内置函数的列表-PostgreSQLCOUNT函数   - 获取的行数。PostgreSQLMAX函数      - 获取最高(最大值)值。PostgreSQLMIN函数      -......
  • java 17 原生操作 mysql 5.7
    环境:JDK:17mysql:5.7和数据库打交道,在项目开发中是在所难免的。今天简单学习下在java中原生操作MySQL,demo通过maven做依赖管理。依赖在新建maven项目后,加入依赖:<dependencies><dependency><groupId>com.mysql</groupId><artifactId>mysql......
  • mysql 定时 数据库备份并上传到另一台服务器上,上传结束并删除源文件
    首先总共有两个脚本:#!/bin/bash:主要用于进行数据库备份、压缩、删除,单独运行命令是:bash XXX.sh#!/usr/bin/expect:主要用于进行数据备份文件的上传,单独运行命令是:expectXXX.sh这两个脚本都需要具有777权限:chmod777XXX.sh分成两个脚本写的原因是:expect主要是用于ssh远程......
  • MSSQL执行查询报错“使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其
     MSSQL执行查询报错“使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。”报错截图: 根本原因如提示,列不一致,列的个数和列名,顺序都需要一致。 ......
  • 无涯教程-PostgreSQL - Functions(函数)
    PostgreSQL函数,也称为存储过程,使您能够执行通常会在数据库中的单个函数中进行多个查询和往返的操作,函数允许数据库重用,因为其他应用程序可以直接与您的存储过程进行交互,而无需中间层或重复代码。Functions-语法创建函数的基本语法如下-CREATE[ORREPLACE]FUNCTIONfuncti......
  • sql语句一览表
    登录到MySQL:mysql-h主机名-u用户名-p-h:该命令用于指定客户端所要登录的MySQL主机名,登录当前机器该参数可以省略;-u:所要登录的用户名;-p:告诉服务器将会使用一个密码来登录,如果所要登录的用户名密码为空,可以忽略此选项。创建一个数据库:createdatabase数据库......
  • 无涯教程-PostgreSQL - SubQueries(子查询)
    子查询或内部查询或嵌套查询是另一个PostgreSQL查询中的查询,并嵌入在WHERE子句中。子查询可与SELECT,INSERT,UPDATE和DELETE语句以及=,<,>,>=,<=,IN等运算符一起使用。SELECT子查询子查询最常与SELECT语句一起使用。基本语法如下-SELECTcolumn_name[,column_name]FROMtable......