⼀对多关联导致数据发散的SQL优化经验
⼯作中会经常写SQL，⽽且⼯作中查询的数据量，⼀般都是⼏百万，甚⾄⼏亿的数据量。如果⾃⼰的SQL写的很烂，就可能导致查询了很久也不会跑出结果来。⽽我在⼯作中写SQL经常遇见的⼀个问题就是⼀对多关联导致数据发散。因此，这篇⽂章，主要记录⼀对多关联导致数据发散的应对⽅案。
多表关联优化的例⼦
我之前写了⼀个SQL，运⾏了⼀个多⼩时，也没有运⾏出来。
下⾯我列出这个SQL的逻辑：
SELECT t1.active_date,
t1.platform_name,
COUNT(DISTINCT t1.uid)AS user_cnt1,
count(DISTINCT case when t2.uid is not null then t2.uid end)as user_cnt2,
count(DISTINCT case when t3.uid is not null then t3.uid end)as user_cnt3
FROM  table1 as t1
LEFT JOIN(SELECT  uid,
active_date,
platform_name
FROM  table2
WHERE active_date ='2020-07-23') t2
ON t1.fuid = t2.fuid
AND  t1.active_date = t2.active_date
AND t1.platform_name = t2.platform_name
LEFT JOIN(SELECT
uid,
active_date,
platform_name
FROM  table3
WHERE active_date ='2020-07-23') t3
ON t1.fuid = t3.fuid
AND t1.f_p_date = t3.f_p_date
AND t1.platform_name = t3.platform_name
GROUP BY t1.active_date, t1.platform_pe;
其中，table1有100w的数据量；table2有200w的数据量，⽽且是分区表；table3有500w的数据量，也是分区表。
从SQL来看上⾯的逻辑，其实很清晰：就是⼀个主表t1left join两个表，主表的数据量为100w左右，left join的两个表分别为两个分区表，数据量分别是200w和500w。咋⼀看，这三个表join怎么也不会跑这么长的时间，那怎么会跑不出来结果呢？
根据经验来看，⼀个SQL有问题，⽆⾮就是两个情况：⼀个是SQL写的太烂，⾃⼰把⾃⼰搞死了；另⼀个是表的关联有问题。上⾯的SQL 逻辑不难，⽽且SQL写法也没有什么可以优化的地⽅。对left join的两个表继续观察，才发现table2的数据是有重复的，这就导致table1和table2关联的条件也是有重复的。这也就是说，table1和table2他们之间的关联是⼀对多的关系，这样就会导致查询的数据量膨胀和发散。但如果是⼀对⼀的关系，join的数据量是不会膨胀的。
此时，我对table2的数据进⾏去重之后，再和table1关联，结果运⾏了⼏分钟，数据就出来了，性能提⾼了很多。
优化后的SQL：
SELECT t1.active_date,
t1.platform_name,sql优化的几种方式
COUNT(DISTINCT t1.uid)AS user_cnt1,
count(DISTINCT case when t2.uid is not null then t2.uid end)as user_cnt2,
count(DISTINCT case when t3.uid is not null then t3.uid end)as user_cnt3
FROM  table1 as t1
LEFT JOIN(SELECT  uid,
active_date,
platform_name
FROM  table2
WHERE active_date ='2020-07-23'
group by uid,
active_date,
platform_name  --table2数据去重
) t2
ON t1.fuid = t2.fuid
AND  t1.active_date = t2.active_date
AND t1.platform_name = t2.platform_name
LEFT JOIN(SELECT
uid,
active_date,
platform_name
FROM  table3
WHERE active_date ='2020-07-23') t3
ON t1.fuid = t3.fuid
AND t1.f_p_date = t3.f_p_date
AND t1.platform_name = t3.platform_name
GROUP BY t1.active_date, t1.platform_pe;
SQL优化的经验
截⽌到⽬前，我⾃⼰在写SQL踩坑最多的地⽅，始终是多表关联导致数据发散这个问题。如果出现了该问题，我⼀般的思路是从以下⼏点来思考的：
1、表关联的条件对不对：⼯作中的SQL查询会⽤到很多表，很多表甚⾄⼀开始是陌⽣的，本来你以为两个表的关联条件是⽤户UID，实际上是订单ID，or⽤户UID+⽇期 or⽤户UID+其他条件。如果⼀开始表之间的关联条件不完整或者是错误的，这就会导致数据发散的现象。因此，我们⼀开始要确定好表之间关联的正确逻辑。
2、表的数据是不是唯⼀的：如果确定了表关联的条件⽆误，但某个关联表中的数据有重复，这也会出现⼀对多关联的情况。这个时候，可以把有重复数据的表先group by，再去关联。
除此之外，在多表关联查询的时候，还有两个经验想分享给⼤家：
1、多表关联的时候，尽量遵循“⼩表在前，⼤表在后”的原则，⼀般来说，这样查询效率会更⾼⼀点；
2、SELECT⼦句中避免使⽤ ‘ * ‘，SQL在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个⼯作是通过查询数据字典完成的, 这意味着将耗费更多的时间。反⽽，在select查询时 ，枚举出⾃⼰需要的表中的那些字段，会⽐使⽤*查询效率⾼。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。