Hive的优化大全,超过16种优化,详细篇,附带mysql数据库的优化 - ToB企服应用市场:ToB评测及商务社交产业平台

以下是 MySQL 常见的优化方面及示例：

字段上面:选择符合的数据类型：例如，如果一个字段的取值范围在 0 到 255 之间，使用 TINYINT 比 INT 更节省空间。

范式建模,规范建表,设置主键和外键.因为他们会自动生成外键和主键索引;

得当建立索引：为经常用于查询、毗连、排序的字段建立索引。例如，对于经常根据用户 ID 来查询用户信息的表，可以在 user_id 字段上建立索引。

避免全表扫描：只管通过索引来查询数据。少用*;

优化毗连查询：确保毗连条件的字段有得当的索引,也就是where内里的条件只管用主键和外键;

减少不必要的子查询：偶然可以通过毗连来替代子查询以提高性能;

例如，假设有两个表 orders 和 customers，原本的查询是：

SQL
SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE name = 'John');

SQL
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.name = 'John';

避免过多索引：过多的索引会影响数据插入和更新的性能。

定期维护索引：删除不再使用的索引。

调解缓存大小：如查询缓存、缓冲池等。

优化并发毗连设置：根据服务器的硬件资源和实际并发需求举行调解。

根据业务需求选择符合的存储引擎，如 InnoDB 适合事务处置惩罚，MyISAM 适合读多写少的场景。

根据业务需求，得当增加内存，cpu，硬盘等硬件信息；如果数据量过大，必要大规模盘算，那就上hive来分析处置惩罚；

Properties
    实行计划好比一个寻宝路线图,展示数据库为了得到终极的查询效果,如何对数据举行操作和处置惩罚的;

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

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

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 语句；

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

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：表示读取表数据的序列化和反序列化的方式。

SQL
对于详细的HQL查询序次,可以通过hive的explain实行计划来探查原因,找到慢查询的原因,提高查询效率;

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

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

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

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

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

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

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

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;

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;

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的数据

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

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

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

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.2中分桶的复习

理解: 通过把小表赋值到每一个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

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

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

桶优化的前提条件:

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

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

前提必须开启mapjoin

分桶的列=毗连的条件列

小表分桶数是=大表分桶数的分桶数或者倍数,

小表分桶数=大表分桶数

开启 smb优化的代码

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

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

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

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;

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

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

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

在多表拼接的时候,我们可以让其union all 双方的表盘算之后直接输出归并,而非开辟多个mr实行重复的工作;

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

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

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

SQL
set hive.map.aggr=true;

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

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

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

SQL
set hive.groupby.skewindata=true;

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

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

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

前提是必须开启 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数值;

对于分数值的列前提也是要开启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 列创建布隆过滤器索引。
);

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

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

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

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

SQL
set hive.optimize.correlation=true;

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

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

[/code] [size=4]3.17 Hive优化十五:矢量优化[/size]
矢量:数学属于-- 包罗大小和方向的数字
前提必须是orc格式存储; 一次让其读取1024行的数据
[code]
set hive.vectorized.execution.enabled=true;
set hive.vectorized.execution.reduce.enabled = true;

set hive.exec.orc.zerocopy=true;

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