了解那些“奇葩”SQL写法,快速写出高效率SQL(3)

简介: 了解那些“奇葩”SQL写法,快速写出高效率SQL

join写法:

image.png

优化写法:

image.png

从执行计划可以看出,join写法的执行步骤要更多,多次shuffle也会消耗更多的资源,串行运行的时间也会更长。优化写法只需要在读取所有数据之后,做一次reduce就可以完成。最后对比一下运行时间和资源消耗,优化写法运行时间快20%,资源使用减少30%。(场景越复杂,效果越好)

总结

由于JOIN是离线数据开发中最常出现低效的环节,那么直接干掉JOIN其实是更好的选择。

当多张表的关联键相同取int类型、聚合的值的场景下,union all + group by写法运行更快、更节省资源、代码开发运维更加简单,并且在表行数越多、关联表越多、关联键越多的场景下,优势会更加突出。

关于两种优化写法,优化写法二更加灵活、更好维护、资源占用更少,但是对于需要使用占位数据的场景(比如聚合map),方法一更加适合。

多张大表join提速(字符串类型)

场景介绍

日常开发中,经常遇到从一个主体多张表取属性的情况,比如客户信息相关的数据,A表取地址、B表取电话号、C表取uv、D表取身份信息、E表取偏好。

写法&执行计划探查

假设有3份数据需要关联得到属性,常规的写法为使用2次full outer join + coalesce来关联取值;或者先将3份数据主体合并在一起,再使用3次left join。

-- 本案例和上边案例类似,使用先将主体合并在一起,再使用三次left join
SELECT
    base.cst_id          AS cst_id
    ,t1.bal_init_prin    AS bal_init_prin
    ,t2.amt_retail_prin  AS amt_retail_prin
    ,t3.amt_buy_prin     AS amt_buy_prin
FROM (
    SELECT
        cst_id
    FROM @bal_init             -- 日初资产
    UNION
    SELECT
        cst_id
    FROM @amt_retail           -- 当天放款资产
    UNION
    SELECT
        cst_id
    FROM @amt_buy              -- 当天买入资产
)base
LEFT JOIN @bal_init t1         -- 日初资产
ON base.cst_id = t1.cst_id
LEFT JOIN @amt_retail t2       -- 当天放款资产
ON base.cst_id = t2.cst_id
LEFT JOIN @amt_buy t3          -- 当天买入资产
ON base.cst_id = t3.cst_id
;

接下来看优化写法:

-- STRING数据类型利用json来实现
SELECT
    cst_id
    ,GET_JSON_OBJECT(all_val, '$.bal_init_prin')   AS bal_init_prin
    ,GET_JSON_OBJECT(all_val, '$.amt_retail_prin') AS amt_retail_prin
    ,GET_JSON_OBJECT(all_val, '$.amt_buy_prin')    AS amt_buy_prin
FROM    (
    SELECT
        cst_id
        ,CONCAT('{',CONCAT_WS(',', COLLECT_SET(all_val)) , '}') AS all_val
    FROM (
        SELECT
            cst_id
            ,CONCAT('\"bal_init_prin\":\"', bal_init_prin, '\"') AS all_val
        FROM @bal_init        -- 日初资产
        UNION ALL
        SELECT
            cst_id
            ,CONCAT('\"amt_retail_prin\":\"', amt_retail_prin, '\"') AS all_val
        FROM @amt_retail      -- 当天放款资产
        UNION ALL
        SELECT
            cst_id
            ,CONCAT('\"amt_buy_prin\":\"', amt_buy_prin, '\"') AS all_val
        FROM @amt_buy         -- 当天买入资产
    )t1
    GROUP BY
        cst_id
)tt1
;

对比join写法和优化写法的执行计划

join写法的执行计划:

image.png

image.png

优化写法:

image.png

image.png

对比两个执行计划,join写法对于每一张表的数据使用了两次,分别为构建主体和取值,所以每一个MAP、JOIN任务的复杂度还是比较高的,但是优化写法MAP、REDUCE任务简洁明了。并且随着表的增多,JOIN写法的JOIN任务负责度会更高。对比运行时间和资源消耗,优化写法运行快了20%,资源消耗减少20%。(场景越复杂,效果越好)

由于使用到collect_set,所以需要考虑该节点是否存在超内存的问题并进行内存调整,该场景一般情况下不会出现。

总结

同大表join(聚合类型),区别在于此方法适用于STRING类型。注意collect_set函数的内存占用。


mapjoin为什么快?是否生效了?

场景介绍

日常开发中,经常会遇到大表join小表的情况,mapjoin是老生常谈的处理方式,但是也要注意写法、小表内存参数调整以保障mapjoin生效。

写法&执行计划探查

目前ODPS对mapjoin做了优化可以自动开启,不用手动写/* +mapjoin(a,b)*/来开启了。inner join,大表left join小表都可以直接使mapjoin生效。

mapjoin生效写法:

-- base为大表,fee_year_rate为小表
-- 方式一,inner join
SELECT
        base.*
        ,fee_year_rate.*
FROM @base base
INNER JOIN @fee_year_rate fee_year_rate
ON (base.terms = fee_year_rate.terms)
;
-- 方式一,LEFT join
SELECT
        base.*
        ,fee_year_rate.*
FROM @base base
LEFT JOIN @fee_year_rate fee_year_rate
ON (base.terms = fee_year_rate.terms)
;

mapjoin未生效写法:

-- 方式三,right join
SELECT
        base.*
        ,fee_year_rate.*
FROM @base base
RIGHT JOIN @fee_year_rate fee_year_rate
ON (base.terms = fee_year_rate.terms)
;
-- 方式四, full outer join
SELECT
        base.*
        ,fee_year_rate.*
FROM @base base
FULL OUTER JOIN @fee_year_rate fee_year_rate
ON (base.terms = fee_year_rate.terms)
;
目录
相关文章
|
6月前
|
SQL 分布式计算 运维
了解那些“奇葩”SQL写法,快速写出高效率SQL
本文主要讲解常见的SQL开发场景、‘奇葩’SQL写法并深入执行计划,带你了解如何快速写出高效率SQL。
|
12月前
|
SQL 分布式计算 MaxCompute
了解那些“奇葩”SQL写法,快速写出高效率SQL(4)
了解那些“奇葩”SQL写法,快速写出高效率SQL
68 0
了解那些“奇葩”SQL写法,快速写出高效率SQL(4)
|
12月前
|
SQL 分布式计算 MaxCompute
了解那些“奇葩”SQL写法,快速写出高效率SQL(2)
了解那些“奇葩”SQL写法,快速写出高效率SQL
57 0
了解那些“奇葩”SQL写法,快速写出高效率SQL(2)
|
12月前
|
SQL 分布式计算 MaxCompute
了解那些“奇葩”SQL写法,快速写出高效率SQL(1)
了解那些“奇葩”SQL写法,快速写出高效率SQL
97 0
了解那些“奇葩”SQL写法,快速写出高效率SQL(1)
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
4月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
92 13
|
4月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
4月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
58 6
|
4月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
278 1