spark-sql cli模式下的优化经验

在spark-sql cli模式下提供对纯sql语句的支持，可以让懂sql语句的人直接使用，简单方便。

但带来方便的同时也增加了优化的难度；因为执行过程不可控导致优化很难入手；因此当出现执行速度很慢时优化难以入手，难度比scala代码要难很多。

但是优化还是有经验可循，总结如下

 

1.适当调大autoBroadcast阈值--小表在后

如果使用代码scala或java，对广播变量很熟悉，但是对于sql开发中就广播变量的使用并不能自己设置，广播将数据发送每一个executor节点，避免shuffle，用map join 代替hash join,从而提升效率。在spark-sql cli  模式下可以通过参数实现自动广播，spark.sql.autoBroadcastJoinThreshold=xxx(单位k) ，默认是10M ,使用时小表放在join 的右边 ，自动广播才生效；这也就是之前很多时候为什么发现小表在后执行速度很快，小表在前，速度很慢的原因；（为什么小表要放在后面才会生效？因为源码中方法调用方式  df1.join(broadcast(df2) ）

在2.2 版本之后可以通过添加hint 方式强制采用broadcastjoin 但是注意外连接中基表不能被广播，因此对于左关联和右关联，小表不能在前  用法案例 如下 会强制将 t1进行广播

select /* + broadcast(t1) +*/    *  from  t1,t2 where t1.key=t2.key 

select /* + broadcast(t1) +*/    *  from  t2 left join t1 on  t1.key=t2.key

 2.使用缓存 cache

     使用方式 ：cache table tableName； cache table 不能使用库名，使用前一定要将database 切换到当前库，cache table底层调用的rdd的cache，作用是把数据存储在内存中一份，后期使用时不用重新计算，节省时间，对于已经落地的表数据，节省的是数据加载的时间；但是需要注意的是，使用cache table 不一定能增加运行速度。因为节省的是加载数据的时间，对于表空间不大，且使用频繁的，可以使用cache，可以和view 搭配使用 

3 .多张大表inner join 

• 两表关联的结果数据量比较少的在前面

这个很好理解 ，例如 有 A表（5千万条数据） B 表（1亿条数据） C表（8千万数据） 单张表

关联关系 ： A关联B结果数据5000万条，

                   A inner join c 4千万，

                   B inner join C 2千万

那么顺序应该是 B inner join C 然后关联 A  ，这样B join C 后只有2千万条数据在内存中，数据量少了，那么处理起来也就更快了；

• 粒度越细的表的放在前面 

这里粒度指关联的字段，该关联字段能作为表的唯一主键，即只有一条数据。

例如 表test1 (name,id,code,class,xxx) 逻辑唯一主键是name,id,code,class,

        表test2 (name,id,code ,xxx) 逻辑唯一主键是name,id,code

        t表test3 (name,id,xxx) 逻辑唯一主键是name,id

 test1粒度比test2细，test2比test3细，那正确的关联顺序是 test1 在前 其次是 test2，最后是test3,

• 关联关系避免出现笛卡尔积（一旦出现后果很严重）

这种问题一是因为没有明确表的粒度和逻辑主键关系，两表关联是出现的情况；假设存在 A inner join B on  A.name = b.name  对于A和B都不存在关联建name 不唯一的情况，执行效率也是很慢，这种情况应尽量避免，可以从业务逻辑入手或借助中间表进行优化；即现抽取需要的字段，进行group by 去重；看笛卡尔积情况是否还存在；

4.避免过多的小文件

 如果遇到表的小文件很多，执行效率也很慢；如果一张表有18万个小文件，那么相当于需要18万个task，假设你给的core总量100,那么需要执行1800遍才能执行完，另外每一个文件存储都会有元数据记录，加载这些元数据也会占用很大内存。因此这种情况应该尽量避免，对于已经存在的可以通过单表查询 group by 落地到临时表，group by 后文件的数量和设置的并行度相同；因此生产中设置合理的并行度也很重要；

5.数据倾斜

数据倾斜这个是经典问题，也会经常遇到，现象为其他大部分task执行完，只有最后一个或几个task一直等待；如果能使用spark ui界面可以看的更明确，数据量分配明显不公平；

处理时，从两方面入手--》业务层面和数据层面入手 

  业务层

1.在设计表的时候明确表的逻辑主键，尽可能的确保逻辑主键唯一性，还要明确表与表自建的逻辑关系和粒度关系，明细表和汇总表分开，这样可以尽可能的避免出现笛卡尔积问题

2.借助中间表，通过对逻辑梳理，在不影响结果的情况下，借助中间尽可能的保证关联条件唯一（两表关联至少对于其中一张表是唯一的）

   数据层：

     1.数据过滤 --> 因为很多时候是因为 存在null或空字符串导致等无意义的数据导致，如果是这些数据那么可以直接在条件添加过滤条件；

     2.数据分离-->倾斜的数据进行单独计算,如果存在小表，对小表进行广播，

    4.  数据加盐（打散）--> 对于聚合的操作可以通过先拼接字符串的方式，如id_01 、 id_02，聚合处理后才去除拼接的字符串，再做一次聚合，加入A join B on A.id=B.id ，A表数据经过打散处理后 ，对B表也要进行复制操作以保证数据结果的正确性

 

2.left join 和right join

原则上是能用inner join 就尽量使用inner join但是有时候业务场景不能使用inner 

一般情况下 使用left 和right 优化原则和inner相同，遵循粒度最细的表尽可能在前 例如场景需要

 A left join B 但是 b表的粒度细  那么应该改为B right join A ，执行效率可能提升很多；

多表关联时尽可能采用基表，应避免 A left join B on a.x=b.x  inner join C on c.x=b.x 这种情况写法；

 

3.其他优化和注意事项

 

• 使用or很快 比union效率高

例如在常见的关系型数据库中 用union 替换 or效率比较高，但是在在spark-sql中恰恰相反。首先因为union有两个sql执行 数据可能会扫描加载两次，第二次即便是同一张表也可能会从磁盘中去加载；另外 使用union 还会对结果进行一次去重操作；

还有in 操作，传统关系数据库中使用in 会使查询放弃索引，效率变慢，但是对于spark-sql不同，因为本身就没有索引；

• 使用or带上非空转换

一般关联条件和等值过滤条件都默认做一次非空判单，但是一旦用上or就不再进行非空判单

例如 A a inner join B b on a.name = b.name where a.count=10 and (a.code='0' or b.code='2')

在经过解析后最后的运行是 isnotnull(a.name) and isnotnull(b.name ) and a.name = a.name

对于a.count=10 会转换成 isnotnulll(a.count )and a.count=10

对于(a.code='0' or b.code='2')解析为 a.code='0' ||b.code='2' 并没有非空判断，因此当出现a.code 为nulll 而b.code=2时该数据返回null 而不是 true 或false，相当于程序先解析a.code='0' 程序返回null ，而null判断非false，并没有执行b.code='2' 的判断。正确写法是 （nvl(a.code,'')='0' or nvl(b.code,'')='2')或 (a.code='0' or b.code='2')=true

所以当出现结果值与期望值有出入时可以试着做非空转换

• 并行度并非越大越好

spark-sql中调整并行度的方式是 设置参数 spark.sql.shuffle.partitions=200，该参数起表示发生shuffe时可以并行执行的数量，对应生成结果文件的数量，如果数量越大，单词task需要处理的数据量就越少，当然结果文件就越多。如果启动资源有100个核，设置启动参数 spark.sql.shuffle.partitions=500 那么最总会生成500个文件，小文件越多，单个文件数据量越小，在读取资源时开启资源来读文件的次数就越多，因此设置太大并不划算，官方建议是cpu的2到3倍，实际使用不会超过5倍。

另外 spark.sql.shuffle.partitions=500 仅仅对使用group by 的sql 语句有用，因为如果没有使用 group by 就不会发生shuffle。