sql的优化语句（数据倾斜的情况）

sql的优化

**

分为业务和架构的两个方面

**
**
reduce阶段的每个分区一个reduce、对key做hash分区的时候，如果key的数量很多，那么把热点数据分发到一个reduce中进行处理。
比如用Hive算数据的时候reduce阶段卡在99.99%用SparkStreaming做实时算法时候，一直会有executor出现OOM的错误，但是其余的executor内存使用率却很低。
**

架构上

• MapJoin把小表全部加载到内存在map端进行join，避免reducer处理默认25兆hive.mapjoin.smalltable.filesize=25
• group by 是否在Map端进行聚合，默认为True当选项设定为 true，生成的查询计划会有两个 MR Job。set hive.groupby.skewindata=true;
  思想：就是先随机分发并处理，再按照key group by来分发处理。 第一个 MR Job 中， Map 的输出结果会随机分布到 Reduce中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce中（这个过程可以 保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作
• 使用skewjoin默认是关闭，与mapjoin有冲突，hive.optimize.skewjoin
  =true配合hive.skewjoin.jey(100000个)，数量大于改值的key会另起mr

业务上

• 过滤掉不关心的热点key值、null值、空值和0值

把user_id >0 和等于0的分开来处理：
select* from test a join user b on a.user_id = b.user_id and a.user_id > 0 union all select * fron test c where c.user_id = 0;`

• 预先统计热点的key值，对其处理，打散

如0值是热点数据
select * from test  a left join user b on case when a.user_id = 0 then bigint(-1000*rand()) else a.user_id end =b.user_id

• distribute by rand()指定特定的分区方式，避免key的hash值进行分区

场景1
当天s活跃首次活跃的用户的位置
使用min(struct)代替row_number()

select
  device_id,
  city_name,
  street_name
from
(select
   device_id,
   city_name,
   street_name,
   row_number() over(partition by device_id order by client_timestamp) as rk
from
    kscdm.dwd_ks_tfc_laun_hi
where
    p_date='20210531_done'
)t
where
rk = 1;

对语句进行改写

select 
device_id,
fist_info.col1 as city_name,
fist_info.col2 as street_name
from 
(SELECT
  device_id,
  min(struct(client_timestamp,city_name,street_name)) as fist_info
FROM
  kscdm.dwd_ks_tfc_laun_hi
WHERE
  p_date = '20210601_done'
  group by device_id )t;

其他
容易导致数据倾斜的场景

Join/GroupBy/CountDistinct，在存在热点key（例如某个字段存在大量空值）的时候，都会导致一个或少数reduce task处理的数据量远超其他task

如何查找造成数据倾斜的热点key

当SQL中包含Join/GroupBy/PartitionBy/DistributedBy等操作时，通过对上述操作所用到的列进行统计，通常就能够找到造成数据倾斜的热点key
A表JoinB表

select * from t1 left join t2 on t1.id = t2.id;
分别查找t1/t2热点id的热点值：
select id, count(1) as c from t1 group by id order by c desc;
select id, count(1) as c from t2 group by id order by c desc;

GroupBy

select c1,c2,c3 from t1 group by c1,c2,c3;
各个列中热点key的查找：
select c1, count(1) as c from t1 group by c1 order by c desc;
select c2, count(1) as c from t1 group by c2 order by c desc;
select c3, count(1) as c from t1 group by c3 order by c desc;

使用map join解决小表关联大表造成的数据倾斜问题

• map join是指将做连接的小表全量数据分发到作业的map端进行join，从而避免reduce task产生数据倾斜；map
  join优化默认已打开（hive.auto.convert.join=true），
  这个配置跟hive.optimize.skewjoin有冲突，请保证二者只开一个即可；
• map
  join需要在内存中加载全部小表数据，容易导致map端OOM，hive.mapjoin.smalltable.filesize这个参数用于设置小表的大小，默认25000000（25M），当小表数据量超过这个大小时，不会走map
  join优化逻辑，不建议用户把这个参数设置过大
• hive.optimize.skewjoin可以处理热点key join 倾斜的问题，但是只支持inner join场景，不支持outerjoin场景

问题2 Map Task长尾

基本原理
根据mapreduce原理，map task按照数据分片大小读取固定数据量进行处理，各个map task处理的数据量基本一致，正常情况下map task不应该出现数据长尾的现象。然而实际使用过程中，用户常常发现有作业长时间卡在少数map task上，有时候长达几小时。

出现这种现象的原因通常是：hive查询分多轮stage进行，某些作业在运行过程中产生了大量临时小文件（临时文件数 = task个数 * 动态分区数），在下一轮stage中，由于map task是按照固定数据量大小（默认256M）进行分片，如果生成的文件只有KB级别，则单个task需要处理成千上万个文件，这期间涉及到大量hdfs操作，很容易受到hdfs波动影响导致执行时间拉长。
常见优化思路

• 1.查看上一轮stage作业是否存在reduce，如果有reduce task，则小文件是reduce生成的，如果单个reduce task执行时间不是特别大，可以适当控制reduce最大并发(hive.exec.reducers.max，默认5120，建议设置为2560/1280/640等)；如果上一轮stage没有reduce，则小文件是map
  生成的，需要加大split size减少map task
  （mapreduce.input.fileinputformat.split.maxsize，默认256000000，建议可以设置到1024000000）;

• 2.在优化手段1的基础上，还可以使用数据架构组定制开发的根据文件数分片的功能（hadoop默认是按照文件大小分片），限制单个task处理的文件数大小（set
  mapreduce.split.by.block.num.enable = true; set
  mapreduce.split.block.number.threshold = 500;）

问题3 Map/Reduce gc严重

Map/Reduce gc严重可能导致任务执行时间拉长、task 超时或者内存超限。
类似的，gc问题可以通过jobid在bethune上查找对应的作业诊断信息进行确认

---

• 1.加大内存：mapTask gc告警设置mapreduce.map.memory.mb（默认3072），reduceTask gc告警可以设置mapreduce.reduce.memory.mb（默认4096），建议按照512的幅度增加，合理使用避免浪费；
• 2.如果sql中有join和group by操作，可以调整参数缩小内存buffer检查间隔：

set hive.mapjoin.check.memory.rows=10000;

set hive.groupby.mapaggr.checkinterval=5000;

set hive.map.aggr.hash.percentmemory=0.3;

set hive.mapjoin.followby.map.aggr.hash.percentmemory=0.1;

set hive.map.aggr.hash.force.flush.memory.threshold=0.7;

set hive.map.aggr.hash.min.reduction=0.3;

• 3.可以选择关闭GBY的map端优化来争取节约内存hive.map.aggr=false;

问题4 MapTask过多，调度开销大

当sql的输入数据量太大，导致map task个数特别多，虽然每个task执行时间都不长，但是由于计算资源有限，在资源紧张的时候，一个作业内的多个task只能分批串行执行，导致资源调度开销成为任务执行时间过长的主要因素，花在资源等待上的时间长达几十分钟乃至几小时。另外map task过多也会导致reduce阶段 shuffle时间变长。

资源紧张问题可以通过jobid在bethune上查找对应的作业诊断信息进行确认，如下图所示：
常见优化思路

• 1.加大单个map处理的数据量（mapreduce.input.fileinputformat.split.maxsize，默认256000000，建议可以设置到1024000000），减少map
  task个数；
• 2.合理设置sql查询的分区范围，尽量避免全表扫描，考虑生成一些增量的中间表来替代

问题5 map/reduce平均运行时间过长

问题5和问题4正好相反，通常是在输入数据量不大，但是由于计算逻辑复杂导致作业执行时间特别长，作业的map/reduce个数通常在个位数或者十位数，这种情况下需要反过来通过增加task并发度，减少单个task处理的数据量来加快任务运行速度。

常见优化思路

• 1.map运行时间过长：加大map并发需要减小split size（mapreduce.input.fileinputformat.split.maxsize，默认256000000，建议可以设置到32000000）；
• 2.reduce运行时间过长：加大reduce并发需要减小reduce 的split size（hive.exec.reducers.bytes.per.reducer，默认1024000000，建议可以设置到128000000）;