数据仓库【SQL优化】

一、SQl优化背景

        每个sqlboy工作一段时间后都会面临这一个能力提升问题--Sql优化。本文通过更通俗易懂的话术讲解sql优化的策略内容。提供一个从业务需求探查到最终上线全流程的sql优化方案，如有缺失，希望大家多多交流补充。

二、SQL优化策略

1. 数据探查阶段
   1. 业务探查：了解业务热点数据
   2. 数据建表：合理分区、分桶
2. 数据开发阶段：较少输入，避免倾斜
   1. 分区裁剪和列裁剪：减少数据范围
   2. 大小表关联：使用mapJoin，在map端聚合，减少reduce压力
   3. 两大表关联：设计分桶策略，避免数据倾斜
   4. 关联条件类型统一：关联字段类型不一致，会导致数据倾斜
   5. group by 替代distinct：避免一个reduce处理所有的数据
   6. sort by 替代order by：避免全量排序，禁止不必要的Order by排序
   7. 尽量避免使用子查询语句，用in替换or，
   8. 合理使用union all（不去重）替代union（去重需要遍历、排序比较）
3. 数据治理阶段：到达数据治理阶段一般都是由于数据倾斜导致任务执行时间长或者占用计算资源多。
   1. 缓解策略：加大资源调用，包括调节Map个数、Reduce个数以及每个reduce处理数据量的大小，或者直接增加计算资源（CPU和内存资源），这种策略只是暂时环节了数据倾斜现象，没有从根本上解决问题，可以做个临时策略；
   2. 根除策略：首先跟业务沟通确认，如果是无效数据，可以直接过滤删除；如果是少量固定key倾斜，可以单独做数据处理后合并；最后将倾斜key拼接随机数，人为打散做二次聚合处理，避免数据倾斜单个renduce处理大量数据。
4. 参数优化：参数优化在日常开发过程中建议积累一个专门的文档，做好标注，遇到问题做针对性条数优化即可
   1. 大小表关联：Map Join，小表太大会造成内存压力

      1.  --启动Map Join自动转换 
          set hive.auto.convert.join=true;
          --开启无条件转Map Join 
          set hive.auto.convert.join.noconditionaltask=true;
          --无条件转Map Join小表阈值，默认值10M，推荐设置为Map Task总内存的三分之一到二分之一 
          set hive.auto.convert.join.noconditionaltask.size=10000000;
         

   2. 大表与大表关联：设计分桶表关联
      1. 适合条件：量表均为分桶表，且关联字段为分桶字段；量表分桶呈倍数关系；分桶内字段是有序的

      2. set hive.optimize.bucketmapjoin.sortedmerge = true;  
         --使用排序合并算法来执行Bucket Map Join操作。排序合并算法可以在两个已经按照分桶列排序的表之间进行连接操作，而无需进行全局排序。这样可以减少排序的开销，提高查询效率
         set hive.optimize.bucketmapjoin = true;
         --它通过将两个或多个表的数据按照相同的桶列进行分桶，并将桶中的数据放在同一个Map任务中进行处理，从而减少了数据的传输和处理开销。
         set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; 
         

   3. 调节reduce并行度：增加reduce的个数

      1.  - -指定Reduce端并行度，默认值为 1，表示用户未指定 
           set mapreduce.job.reduces; 
          - -Reduce端并行度最大值 
           set hive.exec.reducers.max; 
          - -单个Reduce Task计算的数据量，用于估算Reduce并行度
           set hive.exec.reducers.bytes.per.reducer;  --默认1G
         

   4. 小文件合并：小文件过多会造成启动很多reduce但是处理的数据量很少，浪费计算资源

      1. --开启合并map only任务输出的小文件
         set hive.merge.mapfiles=true;
         
         --开启合并map reduce任务输出的小文件
         set hive.merge.mapredfiles=true;
         
         --合并后的文件大小
         set hive.merge.size.per.task=256000000;
         
         --触发小文件合并任务的阈值，若某计算任务输出的文件平均大小低于该值，则触发合并
         set hive.merge.smallfiles.avgsize=16000000;
         
         

   5. 并行计算：没有依赖的Stage可以并行执行，提高计算效率，优点类似Spark的DAG内部窄依赖

      1. --启用并行执行优化，默认是关闭的
         set hive.exec.parallel=true;       
             
         --同一个sql允许最大并行度，默认为8
         set hive.exec.parallel.thread.number=8; 
         
         

   6. CBO优化：类似有向无环图DAG，计算最优执行路径；

      1. --是否启用cbo优化 
         set hive.cbo.enable=true;
         

   7. 建表：列式存储+压缩格式

      1. CREATE TABLE A_T1 ( 
         user_id int, 
         user_name string,
         user_age int, 
         address string 
         ) STORED AS ORC 
         tblproperties (“orc.compress" = “SNAPPY”)
         

   8. 更换计算引擎：Hive、Spark、Tez不同的计算引擎有一定的场景区别
      1. Hive_MR：单纯数据读取、不涉及逻辑加工；数据量大，计算步骤少逻辑简单；运算时间超过90分钟；多次读取同一张大表的任务；
      2. Spark：生成结果集较小；运行时间在40分钟以内的；切分job较多的；需要复杂算子的。
      3. Tez：使用DAG描述认为，减少不必要的MR中间点，减少磁盘IO；在运行过程中可以动态调整任务并行度。

小结

        在编写Hive SQL时，优化是至关重要的。首先要合理设计表结构和分区、分桶，以提高查询效率；其次，使用何时的数据类型和索引，减少数据存储和查询的开销；此外，避免使用不必要的JOIN操作和子查询，尽量使用内置函数和聚合函数来简化查询；最后，通过设置合适的并行度和资源配置，提高作业的执行速度。

        记住，持续优化是一个迭代的过程，不断地调整和改进SQL语句，以获得更好地性能。内化于心，不断成长~