Hive的优化大全,超过16种优化,详细篇,附带mysql数据库的优化

Properties
    执行计划好比一个寻宝路线图,展示数据库为了得到最终的查询结果,如何对数据进行操作和处理的;

案例:select * from 表名 where 字段='某某值' ;
执行计划命令explain,用于展示sql查询的执行计划;
他会显示查询的各个阶段.
   通过:表扫描(from表名),数据过滤(where条件),表连接(比如join),分组(比如groupby),聚合(五大聚合max,min,sum,count,avg),排序(orderby),限制(limitoffset)等;
   通过查看这些信息,可以了解hive查询的处理策略,从而发现可能存在的性能问题,并根据次进行优化和调整.

3.1.2 为啥要使用explain执行计划？

Properties
性能评估与预测:通过分析执行计划,可以大致判断,一个复杂查询是否可能出现长时间的运行或者消耗大量的内存和cpu资源;
问题诊断与优化:发现潜在的性能瓶颈问题.比如识别时候存在全表扫描,不必要的排序,,多个子查询的中的某个执行方式时候够高效;
理解查询逻辑:清晰的展示查询语句的执行逻辑和步骤;
调优决策依据:根据执行计划提供的信息,做出针对的优化决策,例如:是否需要创建索引,调整分区策略,修改连接方式等;
对比不同规定查询方案:相同的业务需求中,往往存在不同的查询写法,通过查看他们各自的执行计划,可以比较并选择最优的方案;

3.1.3 如何使用执行计划explain？

准备表

SQL
drop database if exists db_1 cascade;   -- 删除库
create database db_1;    -- 创建库
use db_1;   -- 进入库

CREATE TABLE products (   -- 创建产品表
    product_id INT,   -- 产品id 整数类型
    product_name STRING, -- 产品名称字符串类型
    category STRING   -- 产品类型字符串类型
)
ROW FORMAT DELIMITED   -- 选择行格式为分割格式   以行来分割
FIELDS TERMINATED BY ',' -- 选择分割符为逗号 ,
STORED AS TEXTFILE;   -- 指定存储类型为文本类型

CREATE TABLE sales (    -- 创建销售表
    sale_id INT,     -- 销售id,整数类型
    product_id INT, -- 产品id,整数类型
    amount FLOAT -- 金额,浮点数类型
)
PARTITIONED BY (city STRING) -- 以城市列分区城市字段,字符串类型
ROW FORMAT DELIMITED   -- 以行来分割
FIELDS TERMINATED BY ',' -- 指定分割符为,号
STORED AS TEXTFILE; -- 文件存储格式为文本类型

-- beijing分区
INSERT INTO sales PARTITION (city='beijing') VALUES -- 插入数据分区目录为北京
(101, 1, 12000), -- 各种值
(102, 2, 8000),
(103, 3, 4000);

-- shanghai分区
INSERT INTO sales PARTITION (city='shanghai') VALUES -- 插入数据分区目录为上海
(104, 1, 15000),
(105, 2, 5000),
(106, 4, 6000);

-- guangzhou分区
INSERT INTO sales PARTITION (city='guangzhou') VALUES
(107, 3, 7000),
(108, 1, 3000),
(109, 4, 8000);

-- shenzhen分区
INSERT INTO sales PARTITION (city='shenzhen') VALUES
(110, 1, 9000),
(111, 2, 6000),
(112, 3, 10000);

-- 郑州分区
INSERT INTO sales PARTITION (city='zhengzhou') VALUES
(113, 4, 9000),
(114, 2, 6000),
(115, 3, 10000);

按照传统数据库的执行逻辑猜想下列sql的执行顺序如下：

SQL
SELECT
    p.product_name, -- 执行普通字段 7
    s.city,   -- 执行普通个字段 8
    SUM(s.amount) AS total_sales -- 执行聚合字段 9
FROM
    sales s   -- 猜想的执行顺序 : 查询销售表 1
JOIN
    products p   -- 查询产品表   2
        ON s.product_id = p.product_id -- 过滤关联条件 3
GROUP BY
    p.product_name, s.city   -- 开始分组 4
HAVING
    s.city IN ('beijing', 'shanghai', 'guangzhou', 'shenzhen') AND -- 执行过滤条件 5
    SUM(s.amount) > 5000   -- 执行过滤条件 6
ORDER BY
    total_sales DESC -- 执行排序 10
LIMIT 10; -- 执行限制 11

sql语句添加explain之后。

SQL
explain select 语句；

3.1.4 explain出来之后一大串都是什么意思？

执行计划一出来，吓死人了，这么长一串，都是啥意思呀

逐行分析看看

SQL
STAGE DEPENDENCIES:   --依赖关系
  Stage-6 is a root stage -- 阶段6 是最开始的阶段根阶段
  Stage-2 depends on stages: Stage-6 -- 阶段2 依赖阶段6
  Stage-3 depends on stages: Stage-2   -- 阶段3 依赖阶段2
  Stage-0 depends on stages: Stage-3 -- 阶段0 依赖阶段 3

  -- 以上的话意思就是我跟你说哈：你的这条sql的执行依赖关系是这么的
  -- 阶段6最先开始 ---> 然后阶段2 ---->在阶段3 ---> 最后阶段0

""
STAGE PLANS:    -- 开始阶段计划咯   STAGE 翻译是阶段 plans 翻译过来是平面图计划
  Stage: Stage-6   -- 最开始执行阶段6
    Map Reduce Local Work -- 开启本地的 mr 任务
      Alias -> Map Local Tables: -- 本地表的映射别名   Alias 翻译过来是别名
        p -- 产品表 product的别名
          Fetch Operator   --- from开始咯开始查找产品表
         ---------- form后面的产品表是第一个开始执行的---------------------
            limit: -1 -- -1表示无行数限制
      Alias -> Map Local Operator Tree: -- 本地操作数的映射别名   Operator 翻译过来是操作员
        p   -- 产品表 product的别名
          TableScan   -- 翻译过来就是表扫描
            alias: p   -- 别名 p
            filterExpr: product_id is not null (type: boolean) --过滤表达式 id不为空
            ------- 开始过滤关联执行条件咯所以这个是第二步顺序---------------
            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE /*统计信息   Statistics ：表示统计相关的内容
        num rows ：1 表示预估实际的行数为1行
        data size：0 预估数据大小为0 字节
        basic stats：paptial 统计的信息是局部的
        column starts:none 列的统计信息没有

        哈哈哈哈，它的分析是正确的因为我们还没给产品表插入数据！只是给销售表放入数据了
        所以产品表里面里面啥都没有

        */

            Filter Operator -- 操作员开启过滤
              predicate: product_id is not null (type: boolean) -- 产品id 不为null
              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
              -- 统计信息，行为1，大小为0 这是局部信息列里面空空如也~
              HashTable Sink Operator -- 哈希表进一步操作 sink 水槽下沉
                keys: -- 键
                  0 product_id (type: int) -- value 产品id 整数类型
                  1 product_id (type: int)
                   --   为啥会多出来这一行，那是因为你产品表里面没有数据可能是重复的查询导致
""
  Stage: Stage-2 -- 开始分析第二个阶段咯    最开始的时候   阶段2是依赖阶段6的
  --------------------- 我是第三步开始执行的---------------------------------------
    Map Reduce   --执行 my 任务
      Map Operator Tree: -- map 操作树
          TableScan -- 开始扫描表
            alias: s -- 销售表 sale 别名 s
"            filterExpr: (product_id is not null and (city) IN ('beijing', 'shanghai', 'guangzhou', 'shenzhen')) (type: boolean)"
  -- 过滤表达式   产品id 时候为空并且它是否在北京，上海广州，深圳里面
            Statistics: Num rows: 12 Data size: 2355 Basic stats: COMPLETE Column stats: PARTIAL   -- 统计信息，
            /*
            行数找到了 12条；
            数据大小 2355
            basic starts complete 找到了完整的   basic 翻译过来基本 complete 翻译过来完整
            行状态部分信息
            */
            Filter Operator   -- 过滤操作   开始执行过滤条件
              predicate: product_id is not null (type: boolean)
              -- 产品id 不是空
              Statistics: Num rows: 12 Data size: 2208 Basic stats: COMPLETE Column stats: PARTIAL -- 统计信息行数 12条大小2208 基本状态完整，行状态部分
              Map Join Operator   -- map 连接操作
                condition map: -- 条件映射
                     Inner Join 0 to 1 -- 内连接0 到1
                keys:
                  0 product_id (type: int) -- 产品id
                  1 product_id (type: int) -- 产品id
"                outputColumnNames: _col2, _col3, _col8" -- 输出列名 select中又三个列名
                Statistics: Num rows: 13 Data size: 2428 Basic stats: COMPLETE Column stats: NONE /*
                统计信息：行数3 大小 2428 基本状态完整列状态空

                */
                Group By Operator -- 执行分组
         --------- 我是第四部分开始执行的---------------------
                  aggregations: sum(_col2)   --aggregations 翻译过来就是聚合对第二列求和
"                  keys: _col8 (type: string), _col3 (type: string)"
                    -- 分组的键名以这两列分组
                  mode: hash -- 模式哈希
"                  outputColumnNames: _col0, _col1, _col2"-- 输出这三列
                  Statistics: Num rows: 13 Data size: 2428 Basic stats: COMPLETE Column stats: NONE -- 统计信息：行数13 大小2428 状态完整，列状态空
                  Reduce Output Operator -- reduce 开始输出
"                    key expressions: _col0 (type: string), _col1 (type: string)"
-- 键：表达式：   以这两列分区
/* mr 八步：读取（read），切片（split），映射（map），分区（partition），排序（stor），规约（combiner），分组（group）*/

                    sort order: ++ -- 排序
"                    Map-reduce partition columns: _col0 (type: string), _col1 (type: string)" --分组的键
                    Statistics: Num rows: 13 Data size: 2428 Basic stats: COMPLETE Column stats: NONE -- 统计信息: 行13条,数据大小 2428 前部内容列信息为空
                    value expressions: _col2 (type: double) -- 值第二列
      Local Work: -- 本地工作
        Map Reduce Local Work -- mr本地任务
      Reduce Operator Tree: -- reduce任务操作树
        Group By Operator -- 分组操作
          aggregations: sum(VALUE._col0) -- 聚合 sum操作
          -----------------------------------------------
"          keys: KEY._col0 (type: string), KEY._col1 (type: string)"   -- 对这2组进行聚合
          mode: mergepartial -- 合并部分
"          outputColumnNames: _col0, _col1, _col2" -- 输出列名
          Statistics: Num rows: 6 Data size: 1120 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: (_col2 > 5000.0) (type: boolean)
            Statistics: Num rows: 2 Data size: 373 Basic stats: COMPLETE Column stats: NONE
            File Output Operator -- 文件输出操作
              compressed: true -- 开启压缩
              table: -- 压缩格式
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat -- 输入格式
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  --输出格式
                  serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
                  -- 序列化与反序列化
""
  Stage: Stage-3 -- 第三阶段
    Map Reduce -- 开启map reduce任务
      Map Operator Tree: -- map task 操作树
          TableScan -- 表扫描
            Reduce Output Operator -- reduce 输出操作
              key expressions: _col2 (type: double)
                   ----------------------执行排序------------------------------
              sort order: - -- 排序为降序   +为正序   -为倒叙
              Statistics: Num rows: 2 Data size: 373 Basic stats: COMPLETE Column stats: NONE
              TopN Hash Memory Usage: 0.1   -- 哈希内存操作
"              value expressions: _col0 (type: string), _col1 (type: string)"
      Reduce Operator Tree:
        Select Operator -- select 操作
        ----------------------select 为第五步开始执行----------------------------------------
"          expressions: VALUE._col0 (type: string), VALUE._col1 (type: string), KEY.reducesinkkey0 (type: double)" -- 聚合列为reduce的输出结果
"          outputColumnNames: _col0, _col1, _col2" -- 输出列名
          Statistics: Num rows: 2 Data size: 373 Basic stats: COMPLETE Column stats: NONE
          Limit   --限制
            Number of rows: 10
            Statistics: Num rows: 2 Data size: 373 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: true -- 压缩方式是
              Statistics: Num rows: 2 Data size: 373 Basic stats: COMPLETE Column stats: NONE
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
""
  Stage: Stage-0 -- 最后执行limit 操作
    Fetch Operator
      limit: 10
      Processor Tree:
        ListSink
""

我们经过执行计划的分析之后根据其执行计划的顺序被自动优化为:

SQL
SELECT
    p.product_name,
    s.city,
    SUM(s.amount) AS total_sales
FROM
    sales s   -- 一个 mr开始工作   6   最开始执行
JOIN
    products p   -- 一个mr开始工作    2   其次
        ON s.product_id = p.product_id --
GROUP BY
    p.product_name, s.city   -- reduce开始输出
HAVING
    s.city IN ('beijing', 'shanghai', 'guangzhou', 'shenzhen') AND -- 在第一阶段开始执行
    SUM(s.amount) > 5000   --   在第二阶段开始执行聚合的时候执行
ORDER BY
    total_sales DESC -- 第三个阶段 mr开始
LIMIT 10; --最后阶段限制

名词解释

SQL
stage dependencies描绘了作业之间的依赖关系 -- 阶段6最先开始 ---> 然后阶段2 ---->在阶段3 ---> 最后阶段0 的执行过程。

Stage-6分为Map和Reduce两个阶段，对应的执行计划关键词解读如下：
·MapReduce：表示当前任务执行所用的计算引擎是MapReduce。
·Map Opertaor Tree：表示当前描述的Map阶段执行的操作信息。
·Reduce Opertaor Tree：表示当前秒时的是Reduce阶段的操作信息。接下来解读上面两个操作数，会尽量保持原有执行计划打印的缩进来解读。Map操作树（Map Operator Tree）信息解读如下：

·TableScan：表示对关键字alias声明的结果集，这里指代
student_tb_orc，进行表扫描操作。
·Statistics：表示对当前阶段的统计信息。例如，当前处理的数据行和数据量，这两个都是预估值。
·Filter Operator：表示在之前操作（TableScan）的结果集上进行数据的
过滤。
·predicate：表示filter Operator进行过滤时，所用的谓词，

·Select Operator：表示在之前的结果集上对列进行投影，即筛选列。
·expressions：表示需要投影的列，即筛选的列。
·outputColNames：表示输出的列名。

·Group By Operator：表示在之前的结果集上分组聚合。
·aggreations：表示分组聚合使用的算法，这里是sum。
·keys：表示分组的列，在该例子表示的是s_age。
·Reduce output Operator：表示当前描述的是对之前结果聚会后的输出信息，这里表示Map端聚合后的输出息。
·key expressions/value expressions：MapReduce计算引擎，在Map阶段和
Reduce阶段输出的都是键-值对的形式，这里key expression和value
expressions分别描述的就是Map阶段输出的键（key）和值（
value）所用的
数据列。这里的例子key expressions指代的就是s_age列，value exporess指代
的就是count（1）列。
·sort order：表示输出是否进行排序，+表示正序，-表示倒序。
·Map-reduce partition columns：表示Map阶段输出到Reduce阶段的分区列，在Hive-SQL中，distributeby指代分区的列。 Reduce阶段所涉及的关键词与Map阶段的关键词是一样的，字段表示含义也相同，因此这里不罗列。下面是Reduce中出现但是在Map阶段没有出现的关键词。
·compressed：在File Output Operator中这个关键词表示文件输出的结果是否进行压缩，false表示不进行输出压缩。
·table：表示当前操作表的信息。
·input format/out putformat：分别表示文件输入和输出的文件类型。
·serde：表示读取表数据的序列化和反序列化的方式。

3.1.5 MR的八步回顾重点重点

3.1.6 执行计划的总结

SQL
对于具体的HQL查询顺序,可以通过hive的explain执行计划来探查原因,找到慢查询的原因,提高查询效率;

也可以说执行计划是开启hive优化的源头.如果发现某个阶段的数据量过大,或者执行时间过长,可以针对性的进行优化,如添加索引和调整分区策略等

3.2 分桶的复习

3.2.1 为什么使用分桶

SQL
便于抽样:可以使用分桶对然后获取指定的桶数据,对整个数据集的一部分进行抽样测试,使开发变得更有效率;

提高查询效率:分桶可以显著减少全表扫描的时间,单查询特定的字段或者范围的时候,数据库只需要搜索相关的桶,而不是整个表,减少了IO流和cpu使用率;

优化写入性能:分桶可以帮助减少小文件的数量,使数据更均匀的分布在每个block块上,大大提高并行写入和处理的效率;

便于数据管理:需要定期归档和删除旧数据的情况下,可以针对特定的桶进行操作,而非整个数据集;

3.2.2 准备工作

SQL
9,天龙八部
10,射雕英雄传
11,神雕侠侣
12,笑傲江湖
13,鹿鼎记
14,倚天屠龙记
15,亮剑
16,潜伏
17,天道
18,士兵突击
19,三国演义
20,西游记
21,水浒传

SQL
1,乔峰,9
2,段誉,9
3,虚竹,9
4,阿朱,9
5,王语嫣,9
6,梦姑,9
7,郭靖,10
8,黄蓉,10
9,黄药师,10
10,欧阳锋,10
11,一灯大师,10
12,洪七公,10
13,杨过,11
14,小龙女,11
15,李莫愁,11
16,老顽童,11
17,金轮法王,11
18,梅姑,11
19,令狐冲,12
20,任盈盈,12
21,岳不群,12
22,任我行,12
23,风清扬,12
24,东方不败,12
25,韦小宝,13
26,建宁公主,13
27,双儿,13
28,沐剑屏,13
29,曾柔,13
30,阿珂,13
31,张无忌,14
32,光明左使,14
33,紫衫龙王,14
34,白眉鹰王,14
35,金毛狮王,14
36,青翼蝠王,14
37,李云龙,15
38,赵刚,15
39,楚云飞,15
40,丁伟,15
41,孔捷,15
42,魏和尚,15
43,余则成,16
44,王翠平,16
45,李涯,16
46,吴敬中,16
47,陆桥山,16
48,马奎,16
49,丁元英,17
50,芮小丹,17
51,韩楚风,17
52,欧阳雪,17
53,肖亚文,17
54,林雨峰,17
55,许三多,18
56,成才,18
57,高成,18
58,伍六一,18
59,袁朗,18
60,史今,18
61,刘备,19
62,关羽,19
63,张飞,19
64,诸葛亮,19
65,曹操,19
66,周瑜,19
67,唐僧,20
68,孙悟空,20
69,猪八戒,20
70,沙和尚,20
71,牛魔王,20
72,红孩儿,20
73,宋江,21
74,卢俊义,21
75,鲁智深,21
76,林冲,21
77,武松,21
78,吴用,21

3.2.3 把表的数据上传到hdfs中去

在windows中准备小说表和人物表然后通过hdfs 的web端把2个文件上传到hdfs中

3.2.4 创建普通表

SQL

drop database if exists db_1 cascade;
create database db_1;

use db_1;

-- # 1 创建普通表
create table tb_story(
id int,
name string
)
row format delimited fields terminated by ','
;

create table tb_person(
id int,
name string,
story_id int
)
row format delimited fields terminated by ','
;

-- 2 加载数据:
-- 2.1 先通过浏览器将原始文件上传到hdfs的 input 目录
-- 2.2 实现加载数据
load data inpath '/test/aa/stork.txt' into table tb_story;
load data inpath '/test/aa/person.txt' into table tb_person;

-- 3 测试
select * from tb_story;
select * from tb_person;

3.2.5 创建分桶表

SQL

-- 创建小说表
create table tb_story_bucket(
    id int,
    name string
)
clustered by (id) into 3 buckets
stored as textfile
;

insert into tb_story_bucket select * from tb_story;

select * from tb_story_bucket;

-- 创建小说分桶排序表
create table tb_story_bucke_sort(
    id int,
    name string
)
clustered by (id) sorted by (id) into 3 buckets
stored as textfile

;

insert into tb_story_bucke_sort select * from tb_story;

select * from tb_story_bucke_sort;

-- 创建分桶表
create table tb_person_bucket_sort(
    id int,
    name string,
    story_id int
)
clustered by (story_id) sorted by (story_id) into 3 buckets
row format delimited fields terminated by '\t'
;

-- 向分桶表插入数据
insert into tb_person_bucket_sort
select * from tb_person;

select * from tb_story_bucke_sort;
select * from db_1.tb_person_bucket_sort;

3.2.6 抽样分桶表的数据

SQL
select * from 表名
tablesample (bucket x out of y on 列)

y 是要几桶
x 是第几桶

表的桶数/y 等于要几桶

y必须和桶数相等也可以是桶的倍数和因数

SQL
-- 抽样
select * from tb_person_bucket_sort
tablesample ( bucket 1 out of 3 on story_id) -- 分3桶那第1桶

SQL
结果为 story_id %3 为0的数据

3.2.7 分桶表的总结

SQL
不可以使用load的方式加载数据到分桶表;
使用insert into 的方式加载数据到分桶表;
可以按桶查询不是不分桶表的数据;
可以对分桶表采取抽样的方式提取数据; 其中y最好等于原表的分桶数量或者是它的倍数或者因数;
datax不支持直接插入数据到分桶表,所以需要临时表转存一下;
分桶是为了提高和优化查询效率,以及便于抽样,和数据管理

3.2.8 分桶表和分区表的共同点和不同点

SQL
共同点:
   都是用于优化数据存储和查询处理的机制;
   都可以提高查询性能,通过减少数据扫描的范围来加速查询

不同点:
    分区是分目录,分桶是分文件;
    分区表中列的值进行划分的,分桶是基于哈希函数吧表的数据分配到桶中;
    分区主要用于where子句中基于分区进行过滤,从而只读取分区表相关的数据;
    分桶常用语抽样,提高连接操作效率等;

3.3 hive优化一:使用分区优化

SQL
在 Hive 中，使用分区进行优化主要体现在以下几个方面：

减少数据扫描量
    通过在查询中指定分区条件，Hive 可以只读取相关分区的数据，而不是扫描整个表。
    例如，如果表按照日期进行分区，查询特定日期范围内的数据时，只会读取对应日期分区的数据。
提高查询性能
    由于只处理相关分区的数据，减少了输入的数据量，从而缩短了查询的执行时间。
    便于数据管理和维护
    可以方便地对不同分区的数据进行单独的管理操作，如删除、备份特定分区的数据。
优化存储
    可以根据分区数据的特点选择不同的存储格式和压缩方式，进一步提高存储和读取效率。

选择合适的分区键
分区键应该具有较高的区分度，并且在查询中经常被用作条件。
例如，对于订单表，使用订单日期作为分区键是一个常见的选择。
避免过度分区
过多的分区可能会导致元数据管理的复杂性增加，并且在某些情况下可能会影响性能。
定期清理过期分区
对于一些有时效性的数据，及时清理不再需要的旧分区，以节省存储空间和提高查询效率。

例如，假设有一个订单表 orders 按照 order_date 进行分区，当查询最近一个月的订单时，可以这样写查询语句：

SQL
SELECT * FROM orders WHERE order_date >= '2024-07-16' AND order_date <= '2024-08-16';

这样，Hive 就只会读取 2024 年 7 月 16 日到 8 月 16 日这个分区的数据，大大提高了查询效率。

3.4 hive优化二:使用分桶优化

见3.2中分桶的复习

3.5 hive优化三:mapjoin优化

理解: 通过把小表赋值到每一个map中让其在map阶段进行连接规约,而不需要进度reduce阶段进行连接,从而优化了查询效率;

前提是需要开启参数和设置阈值: 这个参数是默认开启的

SQL
set hive.auto.convert.join=true;

阈值参数需要设置:

阈值参数的设置的前提下是内存足够大,默认是20MB,我们可以根据内存情况适当调高小表的在map阶段进行join的阈值

Hive还提供另外一个参数--表文件的大小作为开启和关闭MapJoin的阈值：

SQL
--旧版本为hive.mapjoin.smalltable.filesize
set hive.auto.convert.join.noconditionaltask.size=512000000

3.6 hive优化四:Bucket-MapJoin优化

理解: mapjoin是大小表在map阶段优化,如何形成大小表?可以用分桶的方式,我们主动构建一批大小表,让其能够执行mapjoin.

比如我们上面案例小说表是小表,人物表是大表,我们可以吧小说表分3桶,人物表分9桶或者3的其他倍数桶,让其形成一个小表.完成mapjoin;

桶优化的前提条件:

开启桶优化:

SQL
-- 前提开启了mapjoin
set hive.optimize.bucketmapjoin = true;

一个表的bucket数是另一个表bucket数的整数倍.比如我们的例子人物表和小说表

bucket列 == join列(数值类型) 分桶的列=连接的列

必须是应用在map join的场景中必须开启mapjoin2个前提条件; 即开启mapjoin和设置阈值;

注意：如果表不是bucket的，则只是做普通join。 2个表必须都是分桶表;

3.7 hive优化五:Sort Merge Bucket Join 优化

理解: 这个比分桶表连接优化,多了一个排序; 他和分桶join的区别在于

分桶连接优化	SMB连接优化
前提必须开启mapjoin	前提必须开启mapjoin
分桶的列=连接的条件列	分桶的列=连接的条件列
小表分桶数是=大表分桶数的分桶数或者倍数,	小表分桶数=大表分桶数

开启 smb优化的代码

SQL
-- 前提开启了mapjoin
-- 前提开启了桶 mapjoin
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

总结:

最快的是分桶排序优化,当然前提是

满足mapjoin优化(大小表,小表在缓存中);

满足分桶优化(手动通过分桶造就大小表实现mapjoin)

最终实现分桶排序优化(对2个表的分桶和字段都严格要求,并且排序)

如果两个表都比较大，在关联的时候为了加快计算效率，就可以先进行分桶，分桶字段和排序字段以及关联字段都相同，从而将关联转换成SMB Join.

3.8 hive优化六:表连接数据倾斜运行优化

运行时候开启

SQL
set hive.optimize.skewjoin=true;

理解:

如果map分析分组的数据倾斜,就是一个map干活多,一个map干活少,设置skewjoin可以让干活多的map多开辟几个新的map任务干活; 从而解决数据倾斜导致数据运行缓慢;

为什么会存在有的map干活多,有的map干活少: 以单词计数来说:

有的block块对应的切片中hello单词可能只有几百个,但是有的切片里面这个单词可能有几百万条,那么在mr走的map和分区的时候,有的map就干活多了;

那么究竟多大的数据才会决定map干活多了:默认是10万条; 可以手动修改:

SQL
set hive.skewjoin.key=100000;

3.9 hive优化七:表连接数据倾斜编译时优化

理解:我们还可以在编译的时候进行优化

因为hive是基于java编写的,java在运行的时候是需要进行编译的;

他在编译的时候会开启对执行计划的优化,提前对查询的逻辑和结构进行分析,生成更高效的执行计划;

数据倾斜的时候,他会通过更合理的任务分配和执行计划来调整与规避最后减轻数据倾斜的影响;

如果某些表的数据经常被一起访问,那么可以把它们存储在相近的目录,减少磁盘扫描时间;

开启编译优化:

SQL
set hive.optimize.skewjoin.compiletime=true;

3.10 hive优化八:表连接数据倾斜 Union优化

理解:

在多表拼接的时候,我们可以让其union all 两边的表计算之后直接输出合并,而非开辟多个mr执行重复的工作;

开启union优化之前要开启运行优化和编译优化

SQL
-- 开启运行优化
set hive.optimize.skewjoin=true;
-- 开启编译优化
set hive.optimize.skewjoin.compiletime=true;
-- 开启union优化
set hive.optimize.union.remove=true;

3.11 hive优化九:数据倾斜分组统计优化

理解:让分组的时候,就执行mr聚合规约, 以单词计数为例,在分区排序之后,在map阶段就对现有的单词进行分组聚合,最后让reduce只用聚合其他map传递过来的总值即可,而非聚合全部map传递过来的全部未经聚合的数据;

开启的命令

SQL
set hive.map.aggr=true;

3.12 hive优化十:数据倾斜 MRJob随机数打散

理解:

开启hive reduce的负载均衡, 在reduce阶段,让每一个redece的任务处理的数据都相对均衡,避免有的reduce干活多,有的干活少;

以单词计数为例: 有的单词出现的频率过高,比如他们都被多个map都集中分到1区,就会导致1区超载;

我们开启job随机数打散,就是让其他reduce空闲期间帮助本该统计这个单词的reduce分担一下;打破原本可能存在的数据倾斜分布,实现负载均衡分配

SQL
set hive.groupby.skewindata=true;

弊端:

开启这个,不支持对多列数据去重;

3.13 hive优化十一:索引优化重点

原始想复制传统数据库的索引,不利于数据的维护,并且hive中没有主键和外键以及非空唯一等约定

所以hive中的索引优化分为,数值列的优化和非数值列的优化

3.13.1 数值列的索引优化 Row Group Index

前提是必须开启 orc 列式分区存储.

建立orc格式表之后,指定参数

SQL
orc.create.index=true

就会建立 row group index.

SQL
CREATE TABLE 表名
stored AS ORC
TBLPROPERTIES
(
    'orc.compress'='SNAPPY',
--     开启行组索引
    'orc.create.index'='true'
)
AS
    SELECT CAST(siteid AS INT) AS id,
    pcid
    FROM 表名
--     插入的数据保持排序
    DISTRIBUTE BY id sort BY id;

会生成最大值和最小值索引,解释查找数据的时候,只需要对比最大值和最小值即可快速找到对应的orc分区然后获取对应的列;

如果要追加数据,也必须要使用索引的字段重新进行排序,不然会影响原来的min和max数值;

3.13.2 非数值列的索引优化 Bloom Filter Index

对于分数值的列前提也是要开启orc格式分区列式存储数据

对于列中的相同种类,可以类似放入一个目录索引表中存储起来即建立BloomFilter的数据结构,如果查询条件在我们这个索引结构中,就直接返回原表该字段对应的数据.如果不在则直接不跳过;

比如某个列是一个维度列,比如商品一级分类,有很多大的分类然后对应的二级分类,三级分类,商品分类.

如果可以一级分类的列的数据去重然后放入 bloomfilter数据结构中. 这样查询的字段=过滤的列的字段;则直接返回该字段对应的数值;

开启布隆过滤索引的代码:

SQL
CREATE TABLE 表名
stored AS ORC
TBLPROPERTIES
(
    'orc.compress'='SNAPPY',
--     开启行组索引
    'orc.create.index'='true',
    -- 开启布隆索引
     "orc.bloom.filter.columns"="pcid"   -- 指定pcid为布隆索引列
)
AS
    SELECT CAST(siteid AS INT) AS id,
    pcid
    FROM 表名
--     插入的数据保持排序
    DISTRIBUTE BY id sort BY id;

语法格式

SQL
create tblae 表(
  字段1 类型,
  字段2 类型   --定义了表的结构，包括字段1 和字段2 以及它们的数据类型。
)
stored as orc -- 表示以 ORC 格式存储表数据
tblproperties (
'orc.compress'='SNAPPY',    --指定使用 SNAPPY 压缩算法对 ORC 数据进行压缩。
'orc.create.index'='true',   --创建行组索引。
'orc.bloom.filter.columns'='字段2' --为字段2 列创建布隆过滤器索引。
);

3.14 Hive优化十二:并行执行

开启多线程并行执行mr 前提是电脑能够支持多线程

SQL
set hive.exec.parallel=true; -- 开启并行
set hive.exec.parallel.thread.number=16; -- 线程数为16

3.15 Hive优化十三:关联优化器

理解:

假设有一个查询是要关联两个表 A 和 B ，然后对关联结果按照某个字段进行 GROUP BY 。如果没有关联优化器，可能会先对表 A 和表 B 的关联进行一次 shuffle ，然后对 GROUP BY 再进行一次独立的 shuffle

开启关联优化器 : a和b关联,然后在对最后的结果进行洗牌操作

SQL
set hive.optimize.correlation=true;

3.16 Hive优化十四:小文件合并优化

此部分设置，要根据硬件内存来进行调整，个人电脑配置较低，不建议修改。

SQL
hive.merge.mapfiles

是否开启合并Map端小文件，在Map-only的任务结束时合并小文件，true是打开。

SQL
hive.merge.mapredfiles

是否开启合并Reduce端小文件，在map-reduce作业结束时合并小文件。true是打开。

SQL
hive.merge.size.per.task

合并后MR输出文件的大小，默认为256M。

SQL
hive.merge.smallfiles.avgsize

3.17 Hive优化十五:矢量优化

矢量:数学属于-- 包含大小和方向的数字

前提必须是orc格式存储; 一次让其读取1024行的数据


set hive.vectorized.execution.enabled=true; 
set hive.vectorized.execution.reduce.enabled = true;

3.18 Hive优化十六:读取零拷贝

开启之后,orc可以使用hsfs最新的api避免将额外的数据赋值到内存中

set hive.exec.orc.zerocopy=true;

凡梦_leo

关注

7
点赞
踩
20

收藏

觉得还不错? 一键收藏
0
评论
Hive的优化大全,超过16种优化,详细篇,附带mysql数据库的优化

hive的十六种优化的原理和代码; mysql的优化方式介绍
复制链接

扫一扫

专栏目录

Hive的优化大全,超过16种优化,详细篇,附带mysql数据库的优化

1,hive优化概述

2. MYSQL常用优化

2.1 设计优化：

2.2 查询优化：

2.3 索引优化：

2.4 配置参数优化：

2.5 存储引擎选择：

2.6 硬件优化：

3. hive的优化

3.1 执行计划

3.1.1 什么是执行计划?