了解那些“奇葩”SQL写法，快速写出高效率SQL（4）-阿里云开发者社区

了解那些“奇葩”SQL写法，快速写出高效率SQL（4）

2023-11-06 40

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

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

对比一下执行计划

mapjoin生效执行计划：

mapjoin未生效执行计划：

MapJoin简单说就是在Map阶段将小表读入内存，顺序扫描大表完成Join。

对比两种执行计划，mapjoin生效之后，只有两个MAP任务，没有了JOIN任务，相当于省了一次JOIN。

mapjoin是否生效，可以看是HashJoin还是MergeJoin来判断。

总结

mapjoin开启之后，运行效率提高明显，但会因为写法、小表过大不生效，要从执行计划中去判断并做参数调整保障mapjoin生效。

小表大小调整参数：set odps.sql.mapjoin.memory.max=2048（单位M）

distmapjoin：加强版mapjoin

场景介绍

对于大小表join的场景，小表经常会超出mapjoin的最大内存，那么mapjoin就不会生效了。ODPS提供了将中型表放入内存的方案，即distmapjoin，用法和mapjoin相似，即在select语句中使用Hint提示/*+distmapjoin((shard_count=,replica_count=))*/才会执行distmapjoin。shard_count（分片数，默认[200M,500M]）和replica_count（副本数，默认1）共同决定任务运行的并发度，即并发度=shard_count * replica_count。

写法&执行计划探查

常规写法：

SELECT
    base.*
    ,cst_info.*
FROM    @base base
LEFT JOIN @cst_info cst_info
ON (base.cst_id = cst_info.cst_id
    AND base.origin_inst_code = cst_info.inst_id)
;

优化写法：

SELECT  /*+distmapjoin(cst_info(shard_count=20))*/
    base.*
    ,cst_info.*
FROM    @base base
LEFT JOIN @cst_info cst_info
ON (base.cst_id = cst_info.cst_id
    AND base.origin_inst_code = cst_info.inst_id)
;

对比执行计划

常规写法：

优化写法：

对比两种执行计划和mapjoin执行计划可以发现，优化写法都省去了JOIN任务，这个在很大程度上加快了运行速度和降低资源消耗，distmapjoin写法比mapjoin写法多了一个REDUCE任务，即对小表的分片。

distmapjoin是否生效，可以看是DistributedMapJoin1还是MergeJoin来判断。

总结

同mapjoin总结

where限制条件写在外层会很慢吗？

场景介绍

日常开发中，大家都习惯性将过滤条件紧跟在读表之后，这样可以减少数据量以减少任务运行时间。

写法&执行计划探查

过滤条件在读表之后的规范写法和多表join之后再过滤的非规范写法。

-- 规范写法
SELECT
        base.*
        ,fee_year_rate.*
FROM (
    SELECT  *
    FROM    @base
    where terms = '12'
)base
INNER JOIN @fee_year_rate fee_year_rate
ON (base.terms = fee_year_rate.terms)
;
-- 非规范写法
SELECT
        base.*
        ,fee_year_rate.*
FROM @base base
INNER JOIN @fee_year_rate fee_year_rate
ON (base.terms = fee_year_rate.terms)
WHERE   base.terms = '12'
;