内连接外连接由于关联字段重复值导致局部笛卡尔积过程解析

  其他常见问题
内容纲要

概要描述


Inceptor基本优化思路中,我们在做join关联操作的时候,尽量避免关联字段重复值、关联过程中出现重复值,会导致笛卡尔积,然后task执行过慢。

关于内连接和外连接,我们的第一反应,肯定不会产生笛卡尔积,左连接是以左表为准,左表有m条记录,则结果集是m条记录,而实际情况,比想象中要复杂。

首先说下结论:连接查询,如果on条件是非唯一字段,会出现笛卡尔积(局部笛卡尔积);如果on条件是表的唯一字段,则不会出现笛卡尔积。

详细说明


1. 测试数据准备

--表1
CREATE TABLE USER01 (id INT ,name STRING ,job_Id INT ,valid INT ) CLUSTERED BY (id) INTO 1 BUCKETS STORED AS ORC_TRANSACTION;
INSERT INTO USER01 SELECT 1,'诸葛亮',1,1 FROM system.dual;
INSERT INTO USER01 SELECT 2,'关羽',2,2 FROM system.dual;
INSERT INTO USER01 SELECT 3,'张飞',2,2 FROM system.dual;
INSERT INTO USER01 SELECT 4,'张飞',6,6 FROM system.dual;
INSERT INTO USER01 SELECT 5,'威严',7,7 FROM system.dual;
INSERT INTO USER01 SELECT null,null,null,null FROM system.dual;
--表2
CREATE TABLE job (id INT ,name STRING ,valid INT ) CLUSTERED BY (id) INTO 1 BUCKETS STORED AS ORC_TRANSACTION;
INSERT INTO job SELECT 1,'产品部',1 FROM system.dual;
INSERT INTO job SELECT 2,'技术部',1 FROM system.dual;
INSERT INTO job SELECT 7,'财务部',7 FROM system.dual;
INSERT INTO job SELECT 8,'人事部',8 FROM system.dual;
INSERT INTO job SELECT NULL,NULL,NULL  FROM system.dual;

file

file

2. 内连接产生的局部笛卡尔积

  • 如果A表有m(5)条记录,m1(4)条符合on条件,B表有n(4)条记录,有n1(3)条符合on条件,内连接唯一字段结果为:Max(m1,n1)=4

  • 1,2,2,6,7 和 1,2,7,8对比,以user01表为主表,因为主表中有4条符合条件的记录(1,2,2,7),而job表有3条符合条件的记录(1,2,7),取两者中的最大的,所以为4条

SELECT * FROM USER01 u JOIN job j ON u.JOB_ID=j.ID;

file

结论:假如,on条件是表中非唯一字段,则结果集是两表匹配到的结果集的笛卡尔积(局部笛卡尔积) 。

3. 外连接产生的局部笛卡尔积

  • 1,2,2,6,7 和 1,1,7,8对比,以user01表为主表,因为主表中有2条符合条件的记录(1,7),而job表有3条符合条件的记录(1,1,7),取两者中的最大的,所以取3条,然后在加上user表在job表中没有匹配的记录(6-2=4),所以最终结果为3+4=7条
SELECT * FROM USER01 u LEFT JOIN job j ON u.VALID=j.VALID;

file

结论:左连接非唯一字段,是局部笛卡尔积

排查思路


SELECT * FROM USER01 u LEFT JOIN job j ON u.VALID=j.VALID;
  • 以该sql为例,两张表是根据VALID字段关联的,倾斜的话肯定就是VALID倾斜
  • 根据join key做聚合查询并降序排序,统计
set mapred.reduce.tasks = 5;
select valid,count(*) as num from USER01 group by valid distribute by valid sort by num desc limit 10;
select valid,count(*) as num from job group by valid distribute by valid sort by num desc limit 10;

同一个join key的数据量相乘,如果数据量过大,计算量全部分配给一个节点肯定会造成task执行时间过长。

解决方案


  • 建议在内连接、左连接的on条件中加上能唯一标识表中的唯一记录

这篇文章对您有帮助吗?

平均评分 0 / 5. 次数: 0

尚无评价,您可以第一个评哦!

非常抱歉,这篇文章对您没有帮助.

烦请您告诉我们您的建议与意见,以便我们改进,谢谢您。