一篇文章了解hive与MySQL的常用优化

6 hive优化

6.1 资源调优

6.1.1 yarn资源调优

• hive进程是提交在yarn上运行的，因此提高yarn资源最为重要

<property>
    <!-- 每一个nodemanager可以用的最大内存-->
    <name>yarn.nodemanager.resource.memory-mb</name>
    <value>65536</value>
</property>
<property>
    <!-- 每一个nodemanager可以用的最大核数-->
    <name>yarn.nodemanager.resource.cpu-vcores</name>
    <value>16</value>
</property>
<property>
    <!-- 每一个container可以用的最大内存-->
    <name>yarn.scheduler.maximum-allocation-mb</name>
    <value>16384</value>
</property>
<property>
    <!-- 每一个nodemanager可以用的最小内存-->
    <name>yarn.scheduler.minimum-allocation-mb</name>
    <value>512</value>
</property>

6.2 分组聚合调优

6.2.1 优化说明

• hive执行sql语句时，需要经过map、shuffle、reduce，对于group by的调优，是通过减少shuffle的数据量出发的。

• 具体的做法是在map端开启map-side聚合：

  在maptask中维护一个hashtable，对每一maptask中的数据进行第一步聚合，使进入shuffle的数据量变少，以达到优化的作用。

6.2.2 相关参数设置

• hive.map.aggr=true:

  指定是否开启map-side聚合，默认情况下为开启。

• hive.map.aggr.hash.min.reduction=0.5：检验原表是否适合map-site的参数。

  开启map-site聚合的目的是减少数据量，但是有一些情况下进行map-site之后减少数据量并不多，因此有时候不适合开启map-site。该参数用于判断是否合适进行map-site，检验方法：抽取表前 n 条进行map-site，判断数据量的减少情况，若数据量少于原来的0.5（参数的意义），则进行聚合。

• hive.groupby.mapaggr.checkinterval=100000：指定进行检验的条数。

• hive.map.aggr.hash.force.flush.memory.threshold=0.9：

  hash-table能占用maptask内存的最大比例，但超过这个值，就会进行以此flush，就是写入磁盘的意思。

• 例子：

set hive.map.aggr=true;
set hive.map.aggr.hash.min.reduction=0.5;
set hive.groupby.mapaggr.checkinterval=100000;
set hive.map.aggr.hash.force.flush.memory.threshold=0.9;

select * from 表名
group by ...

注意：

• hive中检查是否进行map-side的机制受数据分布情况的影响，因此当我们知道这个sql语句一定适合进行map-side的话，我们可以直接指定hive.map.aggr.hash.min.reduction=1。使其强制进行map-side

6.3 join 调优

• hive有多种join算法，包括common join，map join， bucket map join， sort merge bucket map join，根据实际情况选择合适的算法就是join的调优思路。

6.3.1 common join

• common join是最hive中最稳定的算法，思路如下:

  common join算法用一个mapreduce job任务来完成，即将需要join的多个表，按照on的条件，shuffle到各个reduce中去，然后相同的key值在reduce中完成join操作。

• common join任务的个数，受字段筛选个数的影响，当有多个表进行join，若筛选字段为同一个，则只会有一个common join

• common join最稳定，但是效率低下，因为每一次join都需要进行shuffle，即经历的map阶段也经历了reduce阶段

6.3.2 map join

01) 原理

• map join用两个map任务来完成join操作，减少了shuffle和reduce操作。

• 适用场景：大表 join 小表

• 思路：

  若某join操作满足小表 join 大表的条件，则

  第一个job会扫描小表的数据，将其在本地建立成hash table，然后储存到hdfs缓存中，这是第一个map过程；

  第二个job会先将第一个job过程产生的hash table 读取到各个map task中去，然后每一个map task依次读取大表中的数据，进行join操作。

• 注意：

  1. 小表的hash table是在本地进行建立的，不需要提交到yarn，而且当本地建立完毕后直接提取到hdfs缓存中。
  2. 大表的每一map task都会读取hash table，这也就要求hash table的数据量不能过大，也就是得是小表

• 问题：map join的适合场景是大表join 小表，即小表缓存，大表遍历。那存在问题：

  1. 什么时候进行map join，什么时候进行common join，优化器内部是怎么走的。
  2. 什么能当大表，当大表的条件是什么？
  3. 什么能当小表，当小表的条件是什么？
  4. 当需要join的表情况事先无法进行确定时，怎么判断其进行join呢？

  答案见下节

02) 优化器里的步骤

1. 物理计划生成器将join语句转化为普通的common join语句，进而物理优化器接收到最初未进行优化的 common join计划，优化器从common join语句开始判断是否将其转化为map join。（注意：优化器接收到的是common join计划）

2. 判断用户是否开启map join转化， 即hive.auto.convert.join=true。若不是则直接走common join，若开启则进行下一步

3. 选择大表候选人：大表用于遍历，小表用于缓存。在inner join时，所以表都是大表候选者，但是在left join中，只有左表时候选表，因为左表数据需要全部输出，因此只能作为大表。

4. 判断是否进行无条件转化 hive.auto.convert.join.noconditionaltask=true。

   • 若不进行无条件转化，则会MySQL会将common join作为备用计划，但后续无法进行map join时，进行common join
   • 若不进行无条件转化，则不讲common join作为备用计划

   在实际情况中，这个参数都为true，因为到最后实在进行不了map join，还是会进行common join。

5. 判断是否能进行map join：即通过判断是否有表可以成为小表，判断依据是文件的大小，通过参数hive.auto.convert.join.noconditionaltask.size指定。

   • 优化器会选出参与join表中最小的表，若小于上述参数，则将该表作为小表进行map join
   • 反之则进行common join。

6. 生成最优map join方案：注意一点，common join传入的表可能不止两个,因为当筛选字段一样时，只需要一个common join；因此当有多个表时，要怎么进行map join呢？哪两个表先，或者直接三个表一起呢。

   • 判断两个小表的之和是否小于hive.auto.convert.join.noconditionaltask.size，若是，则同时将两个表作为小表，产生两个hash table。
   • 若只有最小的表满足条件，则小表与大表进行map join，将结果与另一个表进行common join。

7. 如图：

03) 参数设置

# 是否开启map join转化，默认开启
set hive.auto.convert.join=true;
# 是否无条件转化，一般为true
set hive.auto.convert.join.noconditionaltask=true;
# 能作为小表的文件大小
set hive.auto.convert.join.noconditionaltask.size=10000000;

04) 参数设置注意：

1. 优化器针对的是common join的优化，当一开始sql语句被分为了多个common join则会进行多次map join的优化判断。
2. 无条件转化其实没有什么用，因为实在进行不下去都会进行common join。
3. 判断小表的依据为文件在hdfs上的文件大小，而磁盘上的占用远比加载到内存占用小的多，因为会有压缩，类啥啥的，若磁盘上大小为10M，则到缓存可以为100M，因此设置时要注意！！可以通过yarn的监控页面提前看看文件上传到内存中的大小。
4. 当我们知道各个文件的大小时，就能直接通过调整参数来完成多种map join方案。

6.3.3 bucket map join

01) 原理

• bucket map join是对map join的改进，通过对表进行分桶，将各个桶分为转化为hash table进行缓存，maptask只需要调用对应的hash table进行join即可，解决了mapjoin只能大表join小表的问题。
• 适用条件：两个join的表都是分桶表，且关联字段都为join，分桶数量呈整数倍，目的是为了保证每一个桶之间有明确的关联信息。

02) 参数设置

# 关闭 cbo 优化，cbo 会导致 hint 信息被忽略，需将如下参数修改为 false
set hive.cbo.enable=false;
# map join hint 默认会被忽略(因为已经过时)，需将如下参数修改为 false
set hive.ignore.mapjoin.hint=false;
# 启用 bucket map join 优化功能,默认不启用，需将如下参数修改为 true
set hive.optimize.bucketmapjoin = true;

6.4 数据倾斜

6.4.1 概述

• 即某个key值的数据量明显多于其他数据量，导致在shuffle阶段进行hashmap的时候，大量相同key的数据进入同一个reduce，进而导致了该reduce的执行 速度慢于其他reduce，这就是数据倾斜。
• 数据倾斜出现的原因是某个key的值太多，所以它一般出现在join或分组之后。

6.4.2 分组造成的数据倾斜

• group by时，shuffle是根据key分配到各个reduce中去的，因此会产生数据倾斜。
• 一般group by主要的目的是为了聚合，即求个分组的信息，因此有两种优化思路：map-side聚合、skew-groupby优化

01) map-side聚合

• 在map端，先对相同的key进行预聚合，可以相同的key的数据就会明显下降，进行shuffle就不会出现数据倾斜，极端情况下，会完全屏蔽数据倾斜。

• 相关参数

  set hive.map.aggr=true;
  set hive.map.aggr.hash.min.reduction=0.5;
  set hive.groupby.mapaggr.checkinterval=100000;
  set hive.map.aggr.hash.force.flush.memory.threshold=0.9;
  

02) skew-groupby优化

• skew-groupby优化是通过两个mr过程来完成的：

  第一个mr过程将数据随机分配到多个reduce中进行部分聚合

  第二个mr过程将部分聚合的数据进行聚合

• 其实map-side和skew-groupby的解决思路都一样，先完成部分聚合来减少同一个key值的数据量

• 相关参数

  # 启用 skew-groupby
  set hive.groupby.skewindata=true;
  # 关闭 map-side 聚合
  set hive.map.aggr=false;
  

6.4.3 join导致的数据倾斜

• join的默认算法为common join，也就是通过一个mr来完成，map阶段将相同的key传到shuffle，然后再进行reduce，因此会出现数据倾斜。
• 解决思路如下：
  • map join：有一个表为小表
  • skew-join：产生数据倾斜的key值，在某一个表中的数据量很小
  • 修改sql语句：任何情况

01) map join优化

• 在map端完成join，由于map端的数据是平均的，因此可以避免数据倾斜。但是只适用于小表join大表的情况。

02) skew-join优化

• 为key值较多的数据单独开一个map join任务

• 适应条件：要求产生数据倾斜的key在某一个表中是小的，方便走map-join

• 原理：

  1. map端结束之后数据进入shuffle，shuffle结束之后会自动检查各个key值的数据。
  2. 当某个key值大于阈值时，就会将其传入hdfs，并开启另一个job为其进行map join。
  3. 如下图

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g8cgRnNH-1685330194543)(…/img/hive/image-20230417201758454.png)]

• 参数设置

  --启用 skew join 优化
  set hive.optimize.skewjoin=true;
  --触发 skew join 的阈值，若某个 key 的行数超过该参数值，则触发
  set hive.skewjoin.key=100000;
  

03) 调整优化语句

• 思路：将相同key值转化为多个不同的key值，再进行join，适用于任何情况

• 原理：

  1. 对产生数据倾斜中的key值进行打散，即在key值后面添加上一个随机字符
  2. 对未产生数据倾斜的数据进行扩容。

• 例子：表A中字段id存在有数据倾斜

  优化前：

  select * from
  A join B 
  on A.id = B.id
  

  优化后：

  select * from 
  	(select 	# 打散
   		concat(id, '_', cast(rand()*2 as int)) id,		# 通过赋予随机数，将其转化为两个key
   		value
   	from A
  	) ta
  	join
  	(select		# 扩容
           concat(id, '_', 0) id,		# 用于与第一个key join
           value
       from B
       union all
       select
           concat(id, '_', 1) id,		# 用于与第二个key join
           value
  	 from
  	) tb
  on ta=tb
  

7 MySQL优化（索引优化与查询优化）

7.1 索引失效的情况

• 从B+tree角度和索引效率两个角度来判断索引是否失效：
  • 当筛选条件在B+tree中走不下去时，索引失效，如 <>
  • 当避免不了出现全图扫描的情况，索引失效，如 or
• 下面列举的索引失效也是针对性而言的，在一些情况下索引会失效，但是一些情况下索引不会失效，如覆盖索引。

1. 全值匹配效果高

   • 所谓全值匹配是指：存在一个联合索引刚好是所有查询字段，此时查询效果最好
   • 例子：

   # 创建复合索引，顺序为 id, name, age
   create index idx_id_name_age on  sutdent(id, name, age)	
   
   # 全脂匹配情况
   select * from student 
   where id = 1 and name = 'y' and age = 13	# 筛选的列都在索引中
   

2. 最佳左前缀法则

   • 查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效！
   • 主要是针对于复合索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无 法被使用

   # 假设存在索引
   create index idx_id_name_age on  sutdent(id, name, age)	
   
   # 无法使用该索引的情况有
   select * from student where
   name = 10 and age = 1			
   # 虽然name，age字段在索引中，但索引的第一字段为id，而筛选条件没有出现id，因此无法使用。
   

3. 计算，函数，类型转化导致索引失效

   • 在索引字段在进行计算、函数和类型转化，会导致该无法使用该索引，因此要尽量避免
   • 不能理解，若字段进行了操作，其余原先建立索引时的情况不一致，因此不能使用
   • 特别注意类型转化问题

   # 计算
   select * from student where
    age + 1 = 11	# 经过计算，因此此sql无法使用关于age的索引	
    
   # 函数
   select * from student where
   left(name, 3) like "abc%"
   
   # 类型转化
   假设id的类型为字符串类型
   select * from student where
   id = 11		# 出现了类型转化
   

4. 范围筛选的右侧会失效

   • 范围筛选：>,<,between and …
   • 范围筛选对应列之后的索引失效，要看索引的创建顺序，而不是where 中的顺序
   • 启示：创建联合索引时，最好把范围筛选涉及的列放在最后面

   # 假设有索引顺序为 name, id , age
   create index idx_id_name_age on  sutdent(name, id, age)
   
   # 则下列筛选中，age索引将没有用上
   select * from student  where
   name = 'u' and id > 3 and age = 3	# id为范围筛选，而在索引中age排在id后面，索引此函数将没法充分应用到索引。
   
   # 改进方法，改变创建索引的顺序
   create index idx_id_name_age on  sutdent(name, age, id)	# id放在了age后面，这也给予了一个启示，建立索引时，定值的列应该放在前面
   

5. 否定时，索引失效

   • 否定包括：<>(!=), is not null, not like。
   • 否定无法使用索引，从b+tree也可以看出，因为否定则不知道往哪个子树进行下去，只能遍历全部。

6. like以通配符%开头时失效

   • 如：like “%abc”

7. or左右存在非索引的列，索引失效

   • 因为判断没有索引的列只能全表扫描，所以还不如直接全表扫描。
   • 若or两边都是有索引的，那就可以通过两次索引获得

8. 练习：

   假设有索引 index(a,b,c)，判断下方where用到的索引情况

where语句	索引情况
a=3	Y,用到a
a=3 and b=5	Y,用到ab
a=3 and b=5 and c=4	Y,abc
where b=3 或者 where b=3 and c=4 或者 where c=4	N,最左前缀法则，没有a索引用不了
a=3 and c=5	Y,a (没有b，c用不了)
a=3 and b>4 and c=5	Y,ab (筛选范围)
a is null and b is not null	N, 出现否定
a<>3	N，出现否定
abs(a)=3	N，函数
a=3 and b like ‘kk%’ and c=4	Y,abc
a=3 and b like ‘%kk’ and c=4	Y, a (出现%开头)
a=3 and b like ‘%kk%’ and c=4	Y, a (出现%开头)
a=3 and b like ‘k%k%’ and c=4	Y,abc

7.2 关联查询优化（多表）

• 多表查询的时间主要花费在表与表之间的join上，因此可以从join原理角来探究 减少磁盘io、减少记录扫描次数的方法来提高查询效率。

• 驱动表：底层原理中不断提取数据进行比较的表

  被驱动表：被比较的表

  可以通过explain来查看，位于下方的是被驱动表（如下如jobs为驱动表），不可以通过sql语句进行判断，因为MySQL优化器会对自行进行优化！

7.2.1 循环遍历算法

• 此为join使用的最简单的算法：不断从驱动表中提取一条数据，与被驱动表中的所有数据进行比较，找出匹配的结果。
• 该算法存在问题：磁盘io次数过多，每再驱动表中抽取一条数据，被驱动整个表就会进行io一次，被驱动表的io次数受驱动表记录条数的影响，效率不高。
• 对于此算法的改进，应该使记录条数较小的表作为驱动表，也就是小表驱动大表。

7.2.2 块循环遍历算法

• 为了解决上述算法中被驱动表io次数过多的问题，出现了块循环遍历算法：为驱动表建立的buffer缓存区，使得可以一次性再驱动表中提取多条数据，并一次性于被驱动表中的所有数据进行比较，很明显，被驱动表进行io的次数明显减少，受到了buffer大小的影响。

• 对于此算法的改进可以从两个角度出发：

  1. 增加buffer的大小，使得可以从驱动表中一次性提取更多的数据。
  2. “小表驱动大表”，这里的小表大表与前面的不同，前面是记录条数少的表为小表，此处是 记录条数x记录大小 小的表作为小表，其目的也是为了buffer中可以放入更多的条数。
  3. 减少驱动表中不必要的查询，即减少不必要的查询字段

• 可以通过SHOW VARIABLES LIKE ‘%optimizer_switch%’，查看是否开始了buffer

  指定buffer大小的参数自己上网查一下

7.2.3 索引遍历算法

• 为被驱动表添加索引，极大提高效率：从驱动表中提取数据，进入利用被驱动表的索引进行B+tree查找，不需要进行磁盘io
• 对于此算法的改进：为被驱动表添加索引

7.2.4 优化器

• 在MySQL中，优化器会自动根据传入的sql语句进行优化，其结果可能会导致驱动表和被驱动表位置的转化

  # 所谓驱动表位置的转化为：
  select * from 表1 join 表2.	# 从语句中可以看出似乎我们指定了表1 为驱动表，但在实际情况中优化器可能将表2作为驱动表
  

• 内连接时，优化器会将有索引的表作为被驱动表(索引遍历算法)，当两个表都有索引时，小的表作为被驱动表（小表驱动大表）

• 一次性将被驱动表中的数据提取到缓存中去，这样就只有一个io了，速度更快，为什么不这么进行？

  因为又时候被驱动表数据极大，内存不够

7.2.5 小结

• 小表驱动大表
• 增加buffer的大小
• 为被驱动表添加索引，或者有索引的表作为被驱动表
• 减少驱动表中不必要的查询

7.3 子查询优化

• 没什么好优化的，建议直接变为join语法，然后对join进行优化
• 因为子查询产生的虚拟表，虚拟表没法建立索引，因此无法优化

7.4 order by优化

7.4.1 优化思路

• MySQL中order by排序只要有两种方式，filesorted和index顺序，filesort是将记录提取到内存中进行排序，index是利用索引进行排序。
• filesort相关的算法有两种：单路排序和多路排序
  1. 多路排序，即至少会进行两次io，第一次从磁盘中提取需要排序的字段到内存中进行排序，排序完毕后，又用字段返回内存中提取对应数据。对内存要求小，但是io次数多，速度慢
  2. 单路排序：将所有数据一次性提取指内存中进行排序，速度快，但是对内存要求大。
• order by的优化思路：
  1. 扩大sort_buffer的容量，提高单路排序的速度
  2. 如果能用索引就用索引，因为索引已经是按顺序排好的了

7.4.2 索引优化

注意：

1. 根据索引排序并一定会提高排序的效果，也就是说MySQL优化器并不一定会使用上索引

   # 为salary创建索引
   CREATE INDEX idx_salary ON employees(salary);
   # 根据age进行排序并返回*
   explain select * from employees order by salary;
   # 见下文结果，此查询语句并没有利用索引进行排序，而是利用简单的filesort。为什么呢？我们进行分析
   若用idx_salart排序的话，我们可以通过二级索引获得主键的排序情况，然后再回表取得全部数据，注意！！我们需要回表获得全部数据，那还不ru一次性全部导入进行排序，因此最优MySQL优化器使用了 sortfile
   

   

2. 加上limit，where后，可能会进行index排序

   # 获得前10条数据
   EXPLAIN SELECT * FROM employees ORDER BY salary LIMIT 10;
   # 由结果可以看出应用了index，为什么呢？
   因为我们只查询前10条，需要回表的数据不多，因此利用上了index 
   

   

3. 不难看出添加索引并不一定能提高效率，重点还要看回表的情况，如果需要全部回表，那还不如直接filesort

索引也会失效：

1. 上述索引失效的情况都包含再order by中

2. 联合索引时，默认每一字段都是升序的，因此若排序要求乱序了，则索引失效

   create index idx_dep_sal on employees(department_id, salary);
   
   EXPLAIN SELECT department_id, salary FROM employees ORDER BY department_id, salary;
   EXPLAIN SELECT department_id, salary FROM employees ORDER BY department_id DESC, salary;
   # 由结果可以看出两个查询都使用了索引，但是呢，因为排序要求的不同多少回有点不同。
   

3. order by失效的情况其实也可以从B+tree从查找，当B+tree无法对排序要求起作用的话，索引失效。

7.5 覆盖索引（回表）

• 覆盖索引：利用二级索引查询的方式来避免进行回表的操作。

  在二级索引的B+tree中，也存在有数据，即建立索引的字段以及主键，因此，若我们需要查询的字段全都在二级索引中，那我们是不是可以直接从二级索引的B+tree中获得数据，而不需要回表到聚簇索引中进行查询，这样就减少了回表的操作！

• 回表：所谓回表就是在二级索引中获得需要记录的主键，然后返回聚簇索引中进行查询。

  为什么我们要避免回表，因为可能会出现随机IO的情况！当我们在二级索引中获得了一系列需要的主键，很明显主键是乱序的，因此进行回表时，也是乱序的，而在聚簇索引中主键排序是顺序的，也就是说，每查询一个主键，就会调用不同的数据页，这就出现了随机io，严重影响效率。

• 例子：不等于号中的覆盖索引

  CREATE INDEX idx_depart ON employees(department_id);			# 创建department_id 的索引
  EXPLAIN SELECT * FROM employees WHERE department_id <> 10;		
  EXPLAIN SELECT employee_id FROM employees WHERE department_id <> 10;
  

  由结果可以看出，查询1没有使用索引，为什么呢？因为他查询的*需要进行回表操作，消耗太高了，没必要，因此直接循环查找。

  索引2使用的索引，为什么呢？因为他查询的是employee_id，这个是表的主键，在idx_depart索引也存在，所以可以直接通过这个二级索引获得，不需要进行回表操作，这就是所谓的覆盖索引。

覆盖索引的优缺：

• 优点：

  覆盖索引避免了回表操作，将磁盘的随机io次数变为了顺序io，加快了查询效率。避免了回表操作必然很容易理解，那将随机io变为顺序io怎么理解呢，当我们需要进行回表时，进行的io时随机io，假设我们从二级索引中获得了一个范围，主键为非顺序的，因此进行回表时也是非顺序的，那么就会出现随机io的情况。

  简单来说，回表操作时会导致随机io的，这也是为什么有时候回表不如直接遍历的原因。

• 缺点：

  覆盖索引的本质是将我们需要查询的字段建立新的B+tree，所以其最大的弊端就是内存会明显增加，比如总的字段有4个，目前我们需要的字段有3个，如果我们利用这3个字段重新建立索引的话，很容易发现等于重新复制了3/4的数据量！！

  所以到底用不用索引还得衡量一些利弊，要空间还是要时间。