目次
1,hive优化概述
2. MYSQL常用优化
2.1 计划优化：
2.2 查询优化：
2.3 索引优化：
2.4 配置参数优化：
2.5 存储引擎选择：
2.6 硬件优化：
3. hive的优化
3.1 实行计划
3.1.1 什么是实行计划?
3.1.2 为啥要使用explain实行计划？
3.1.3 如何使用实行计划explain？
3.1.4 explain出来之后一大串都是什么意思？
3.1.5 MR的八步回顾 重点重点
3.1.6 实行计划的总结
3.2 分桶的复习
3.2.1 为什么使用分桶
3.2.2 预备工作
3.2.3 把表的数据上传到hdfs中去
3.2.4 创建平凡表
3.2.5 创建分桶表
3.2.6 抽样分桶表的数据
3.2.7 分桶表的总结
3.2.8 分桶表和分区表的共同点和不同点
3.3 hive优化一:使用分区优化
3.4 hive优化二:使用分桶优化
3.5 hive优化三:mapjoin优化
3.6 hive优化四:Bucket-MapJoin优化
3.7 hive优化五:Sort Merge Bucket Join 优化
3.8 hive优化六:表毗连数据倾斜 运行优化
3.9 hive优化七:表毗连数据倾斜 编译时优化
3.10 hive优化八:表毗连数据倾斜 Union优化
3.11 hive优化九:数据倾斜 分组统计优化
3.12 hive优化十:数据倾斜 MRJob随机数打散
3.13 hive优化十一:索引优化 重点
3.13.1 数值列的索引优化 Row Group Index
3.13.2 非数值列的索引优化 Bloom Filter Index
3.14 Hive优化十二:并行实行
3.15 Hive优化十三:关联优化器
3.16 Hive优化十四:小文件归并优化
3.17 Hive优化十五:矢量优化
3.18 Hive优化十六:读取零拷贝
1,hive优化概述
HIVE的优化从实行计划开始,
然后复习了分桶的知识点,
最后枚举了对于Hive十六种优化方式和原理以及代码,
附带了关于mysql的优化;
2. MYSQL常用优化
以下是 MySQL 常见的优化方面及示例：
2.1 计划优化：
字段上面:选择符合的数据类型：例如，如果一个字段的取值范围在 0 到 255 之间，使用 TINYINT 比 INT 更节省空间。
范式建模,规范建表,设置主键和外键.因为他们会自动生成外键和主键索引;
得当建立索引：为经常用于查询、毗连、排序的字段建立索引。例如，对于经常根据用户 ID 来查询用户信息的表，可以在 user_id 字段上建立索引。
2.2 查询优化：
避免全表扫描：只管通过索引来查询数据。 少用*;
优化毗连查询：确保毗连条件的字段有得当的索引,也就是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';
|
2.3 索引优化：
避免过多索引：过多的索引会影响数据插入和更新的性能。
定期维护索引：删除不再使用的索引。
2.4 配置参数优化：
调解缓存大小：如查询缓存、缓冲池等。
优化并发毗连设置：根据服务器的硬件资源和实际并发需求举行调解。
2.5 存储引擎选择：
根据业务需求选择符合的存储引擎，如 InnoDB 适合事务处置惩罚，MyISAM 适合读多写少的场景。
2.6 硬件优化：
根据业务需求，得当增加内存，cpu，硬盘等硬件信息；如果数据量过大，必要大规模盘算，那就上hive来分析处置惩罚；
3. hive的优化
3.1 实行计划
3.1.1 什么是实行计划?
| 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之后。
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)
- 终极实现分桶排序优化(对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优化十四:小文件归并优化
此部门设置，要根据硬件内存来举行调解，个人电脑配置较低，不建议修改。
是否开启归并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;
3.18 Hive优化十六:读取零拷贝
开启之后,orc可以使用hsfs最新的api避免将额外的数据赋值到内存中
- set hive.exec.orc.zerocopy=true;
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
