目录
3.7 hive优化五:Sort Merge Bucket Join 优化
3.13.1 数值列的索引优化 Row Group Index
3.13.2 非数值列的索引优化 Bloom Filter Index
1,hive优化概述
HIVE的优化从执行计划开始,
然后复习了分桶的知识点,
最后列举了对于Hive十六种优化方式和原理以及代码,
附带了关于mysql的优化;
2. MYSQL常用优化
以下是 MySQL 常见的优化方面及示例:
2.1 设计优化:
字段上面:选择合适的数据类型:例如,如果一个字段的取值范围在 0 到 255 之间,使用 TINYINT 比 INT 更节省空间。
范式建模,规范建表,设置主键和外键.因为他们会自动生成外键和主键索引;
适当建立索引:为经常用于查询、连接、排序的字段建立索引。例如,对于经常根据用户 ID 来查询用户信息的表,可以在 user_id 字段上建立索引。
2.2 查询优化:
避免全表扫描:尽量通过索引来查询数据。 少用*;
优化连接查询:确保连接条件的字段有适当的索引,也就是where里面的条件尽量用主键和外键;
减少不必要的子查询:有时可以通过连接来替代子查询以提高性能;
例如,假设有两个表 orders 和 customers,原本的查询是:
SQL |
可以优化为:
SQL |
2.3 索引优化:
避免过多索引:过多的索引会影响数据插入和更新的性能。
定期维护索引:删除不再使用的索引。
2.4 配置参数优化:
调整缓存大小:如查询缓存、缓冲池等。
优化并发连接设置:根据服务器的硬件资源和实际并发需求进行调整。
2.5 存储引擎选择:
根据业务需求选择合适的存储引擎,如 InnoDB 适合事务处理,MyISAM 适合读多写少的场景。
2.6 硬件优化:
根据业务需求,适当增加内存,cpu,硬盘等硬件信息;如果数据量过大,需要大规模计算,那就上hive来分析处理;
3. hive的优化
3.1 执行计划
3.1.1 什么是执行计划?
Properties |
3.1.2 为啥要使用explain执行计划?
Properties |
3.1.3 如何使用执行计划explain?
准备表
SQL |
按照传统数据库的执行逻辑 猜想下列sql的执行顺序如下:
SQL |
sql语句添加explain之后。
SQL |
3.1.4 explain出来之后一大串都是什么意思?
执行计划一出来,吓死人了,这么长一串,都是啥意思呀
逐行分析看看
SQL |
我们经过执行计划的分析之后 根据其执行计划 的顺序被自动优化为:
SQL |
名词解释
SQL |
3.1.5 MR的八步回顾 重点重点
3.1.6 执行计划的总结
SQL |
3.2 分桶的复习
3.2.1 为什么使用分桶
SQL |
3.2.2 准备工作
SQL |
SQL |
3.2.3 把表的数据上传到hdfs中去
在windows中准备小说表和人物表 然后通过hdfs 的web端 把2个文件上传到hdfs中
3.2.4 创建普通表
SQL |
3.2.5 创建分桶表
SQL |
3.2.6 抽样分桶表的数据
SQL |
SQL |
SQL |
3.2.7 分桶表的总结
SQL |
3.2.8 分桶表和分区表的共同点和不同点
SQL |
3.3 hive优化一:使用分区优化
SQL |
-
选择合适的分区键
-
分区键应该具有较高的区分度,并且在查询中经常被用作条件。
-
例如,对于订单表,使用订单日期作为分区键是一个常见的选择。
-
避免过度分区
-
过多的分区可能会导致元数据管理的复杂性增加,并且在某些情况下可能会影响性能。
-
定期清理过期分区
-
对于一些有时效性的数据,及时清理不再需要的旧分区,以节省存储空间和提高查询效率。
例如,假设有一个订单表 orders 按照 order_date 进行分区,当查询最近一个月的订单时,可以这样写查询语句:
SQL |
这样,Hive 就只会读取 2024 年 7 月 16 日到 8 月 16 日这个分区的数据,大大提高了查询效率。
3.4 hive优化二:使用分桶优化
见3.2中分桶的复习
3.5 hive优化三:mapjoin优化
理解: 通过把小表赋值到每一个map中 让其在map阶段进行连接规约,而不需要进度reduce阶段进行连接,从而优化了查询效率;
前提是需要开启参数和设置阈值: 这个参数是默认开启的
SQL |
阈值参数需要设置:
阈值参数的设置的前提下是内存足够大,默认是20MB,我们可以根据内存情况适当调高小表的在map阶段进行join的阈值
Hive还提供另外一个参数--表文件的大小作为开启和关闭MapJoin的阈值:
SQL |
3.6 hive优化四:Bucket-MapJoin优化
理解: mapjoin是大小表在map阶段优化,如何形成大小表?可以用分桶的方式,我们主动构建一批大小表,让其能够执行mapjoin.
比如我们上面案例 小说表是小表,人物表是大表,我们可以吧小说表分3桶,人物表分9桶或者3的其他倍数桶,让其形成一个小表.完成mapjoin;
桶优化的前提条件:
- 开启桶优化:
SQL |
- 一个表的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)
- 最终实现分桶排序优化(对2个表的分桶和字段都严格要求,并且排序)
如果两个表都比较大,在关联的时候为了加快计算效率,就可以先进行分桶,分桶字段和排序字段以及关联字段都相同,从而将关联转换成SMB Join.
3.8 hive优化六:表连接数据倾斜 运行优化
运行时候开启
SQL |
理解:
如果map分析分组的数据倾斜,就是一个map干活多,一个map干活少,设置skewjoin可以让干活多的map多开辟几个新的map任务干活; 从而解决数据倾斜导致数据运行缓慢;
为什么会存在 有的map干活多,有的map干活少: 以单词计数来说:
有的block块对应的切片中hello单词可能只有几百个,但是有的切片里面这个单词可能有几百万条,那么在mr走的map和分区的时候,有的map就干活多了;
那么究竟多大的数据才会决定map干活多了:默认是10万条; 可以手动修改:
SQL |
3.9 hive优化七:表连接数据倾斜 编译时优化
理解:我们还可以在编译的时候进行优化
因为hive是基于java编写的,java在运行的时候是需要进行编译的;
- 他在编译的时候会开启对执行计划的优化,提前对查询的逻辑和结构进行分析,生成更高效的执行计划;
- 数据倾斜的时候,他会通过更合理的任务分配和执行计划来调整与规避 最后减轻数据倾斜的影响;
- 如果某些表的数据经常被一起访问,那么可以把它们存储在相近的目录,减少磁盘扫描时间;
开启编译优化:
SQL |
3.10 hive优化八:表连接数据倾斜 Union优化
理解:
在多表拼接的时候,我们可以让其union all 两边的表计算之后直接输出合并,而非开辟多个mr执行重复的工作;
开启union优化之前 要开启运行优化和编译优化
SQL |
3.11 hive优化九:数据倾斜 分组统计优化
理解:让分组的时候,就执行mr聚合规约, 以单词计数为例,在分区排序之后,在map阶段就对现有的单词进行分组聚合,最后让reduce只用聚合其他map传递过来的总值即可,而非聚合全部map传递过来的全部未经聚合的数据;
开启的命令
SQL |
3.12 hive优化十:数据倾斜 MRJob随机数打散
理解:
开启hive reduce的负载均衡, 在reduce阶段,让每一个redece的任务处理的数据都相对均衡,避免有的reduce干活多,有的干活少;
以单词计数为例: 有的单词出现的频率过高,比如他们都被多个map都集中分到1区,就会导致1区超载;
我们开启job随机数打散,就是让其他reduce空闲期间帮助本该统计这个单词的reduce分担一下;打破原本可能存在的数据倾斜分布,实现负载均衡分配
SQL |
弊端:
开启这个,不支持对多列数据去重;
3.13 hive优化十一:索引优化 重点
原始想复制传统数据库的索引,不利于数据的维护,并且hive中没有主键和外键以及非空唯一等约定
所以hive中的索引优化分为,数值列的优化和非数值列的优化
3.13.1 数值列的索引优化 Row Group Index
前提是必须开启 orc 列式分区存储.
建立orc格式表之后,指定参数
SQL |
就会建立 row group index.
SQL |
会生成最大值和最小值索引,解释查找数据的时候,只需要对比最大值和最小值即可快速找到对应的orc分区然后获取对应的列;
如果要追加数据,也必须要使用索引的字段重新进行排序,不然会影响原来的min和max数值;
3.13.2 非数值列的索引优化 Bloom Filter Index
对于分数值的列 前提也是要开启orc格式分区列式存储数据
对于列中的相同种类,可以类似放入一个目录索引表中存储起来 即建立BloomFilter的数据结构,如果查询条件在我们这个索引结构中,就直接返回原表该字段对应的数据.如果不在则直接不跳过;
比如某个列是一个维度列,比如商品一级分类,有很多大的分类 然后对应的二级分类,三级分类,商品分类.
如果可以一级分类的列的数据 去重 然后放入 bloomfilter数据结构中. 这样查询的字段=过滤的列的字段;则直接返回该字段对应的数值;
开启布隆过滤索引的代码:
SQL |
语法格式
SQL |
3.14 Hive优化十二:并行执行
开启多线程 并行执行mr 前提是电脑能够支持多线程
SQL |
3.15 Hive优化十三:关联优化器
理解:
假设有一个查询是要关联两个表 A 和 B ,然后对关联结果按照某个字段进行 GROUP BY 。如果没有关联优化器,可能会先对表 A 和表 B 的关联进行一次 shuffle ,然后对 GROUP BY 再进行一次独立的 shuffle
开启关联优化器 : a和b关联,然后在对最后的结果进行洗牌操作
SQL |
3.16 Hive优化十四:小文件合并优化
此部分设置,要根据硬件内存来进行调整,个人电脑配置较低,不建议修改。
SQL |
是否开启合并Map端小文件,在Map-only的任务结束时合并小文件,true是打开。
SQL |
是否开启合并Reduce端小文件,在map-reduce作业结束时合并小文件。true是打开。
SQL |
合并后MR输出文件的大小,默认为256M。
SQL |
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;