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

对比一下执行计划

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'
;

印象中，规范写法的运行效率肯定会高一些，看一下执行计划会发现两种写法的执行计划是一样的，都在join之前做了过滤

总结

ODPS对谓词前置做了很好的优化，但是日常开发也尽量将过滤条件跟在读表之后，这样更加规范，代码也会具有更好的可读性。

总结

做好SQL开发、优化，得先学会阅读执行计划，多动手尝试可以快速帮助你掌握该技能。

（本篇讲到的执行计划，随着ODPS的优化，会发生改变）