一.SQL调优方法
1.查询时，避免使用select*，使用select列，只查需要用到的列，多余的列根本无需查出来。
2.用union all代替union
union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高
3.多用limit，提高效率。
4.增量查询
select*,获取所有的数据进行同步=>改为增量查询，只同步一批数据，比如100条，提升单词查询的效率。
5.分页查询。
对查询接口做分页处理，避免一次性返回过多的数据影响接口性能。
6.用连接查询代替子查询.
子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。
7.join表的数量不宜过多
join太多，mysql在选择索引的时候会非常复杂，很容易选错索引。
8.join时要注意
在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。
9.控制索引的数量
众所周知，索引能够显著的提升查询sql的性能，但索引数量并非越多越好。
因为表中新增数据时，需要同时为它创建索引，而索引是需要额外的存储空间的，而且还会有一定的性能消耗。
10.选择合理的字段类型
-能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
-尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
-长度固定的字符串字段，用char类型。
-长度可变的字符串字段，用varchar类型。
-金额字段用decimal，避免精度丢失问题。
11.提升group by效率
where条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些。也就是SQL语句在执行一些耗时操作之前，尽可能缩小数据范围，这样能提升sql整体的性能。
12.小表驱动大表-- in和exists关键字
in：会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。
select * from order where user_id in (select id from user where status=1)
exists：优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。
select * from order where exists (select 1 from user where order.user_id = user.id and status=1)
in 适用于左边大表，右边小表。
exists 适用于左边小表，右边大表。
13.索引优化
1.检查sql语句有没有走索引。
可以使用explain命令，查看mysql执行计划。
执行计划查看：通过type、key、key_len这几列可以判断索引使用情况。
type：连接类型 key：实际用到的索引 key_len: 实际索引长度
2.防止索引失效
全值索引
对索引中所有的列都指明值，是做好用的一种避免索引失效的方法。
创建复合索引
create index deptno_sal_index on emp(deptno,sal);
二、Hive调优方法
1.Hive的参数配置问题
Hive的参数配置有3种方式,分别是:
1.修改配置文件. 直接去/export/server/hive/conf文件中,修改hive-env.sh,hive-site.xml文件即可
2.命令行配置. nohup hive --service hiveserver2 --hiveconf 参数名=参数值 &
3.参数配置 set mapreduce.job.reduce=3
优先级,从高到低
参数配置>命令行配置>修改配置文件
作用范围,从高到低
配置文件>命令行>参数配置
2.Hive调优详解
1.数据压缩
解释:就是使用压缩协议,对Hive表数据进行压缩
区分压缩协议快与慢,可以参考3个维度:压缩后文件占比,压缩速度(写),解压速度(读)
我们以后常用的压缩协议:
Snappy 压缩后文件不大(压缩比相对较高),解压,压缩速度快
Zlip 压缩比高,解压和压缩速度稍慢
MR程序中们也可以对Map端的数据和Reduce端的数据分别做压缩
Map端压缩: 降低Reduce端拉取数据的数量,减少传输,提高查询效率
Reduce端压缩:降低结果文件的大小,提高磁盘(资源)利用率
2.存储方式
行存储:TextFile,SequenceFile
优点:select *方式,效率高
缺点:select列方式,效率相对较低, 数据密集度比较低,占用大量存储空间
列存储:Orc,parquet 如果表是列存储的,你去看它的HDFS文件内容,看不到(乱码),因为底层是二进制方式存的,所以才会节省存储空间
优点:select列方式,效率高, 数据密集高,占用少量存储空间
缺点:select*方式,效率相对较低
问题:会不会出现1个Block块中,全部是某个列的数据
答案:如果是行存储绝无可能,如果是列存储,有可能
3.Fetch抓取
概述/解释:
HiveSQL能直接执行,就不要转MR程序,然后再执行,它有几种模式可以选择,具体如下:
设置格式:
set hive.fetch.task.conversion=模式名
常用格式:
more: 默认模式,全表查询,查询指定列,简单查询,limit分页查询不是MR,其它转MR程序
minimal: 全表查询,查询指定列,limit分页查询不走MR,其他转MR执行
none: 全部都转MR程序
4.本地模式
概述/解释:
如果HiveSQL非要转MR程序,能在本地直接执行,既不要交由Yarn来调度
5.join优化
概述/解释:
主要有大表 join 大表,小表 join 大表,桶表 join 桶表三种情况.
思路为:
1.如果是 小表 join 大表,则开启Map端join,能在Map端做合并,就不要到Reduce端做合并
2.如果是 大表 join 大表,则有空值过滤和空值转换两种思路
select * from a join b on a.id=b.id; --假设 B表(100W条,30W空)
select * from a join (select * from b where id is not null) b on a.id=b.id; --空值过滤
select * from a join (select 列1,列2...,if(id is null,10,id)id from b) b on a.id=b.id; 空值转换,固定值,可能存在数据倾斜的情况
select * from a join (select 列1,列2...,if(id is null,concat(10,rand()),id)id from b)b on a.id=b.id; 空值转换,随机填充
3.桶表 join 桶表,且表A的桶的数量,是表B的桶的数量的整数值,则可以用分桶字段 替代 关联字段,即: on a.id=b.id=>on a.分桶字段=b.分桶字段;
6.SQL常规优化
1.列裁剪. 能写select 列1,列2...不要写selcet *...
2.分区裁剪. 分区表查询时,尽量写分区字段,即:select..from表名 where分区字段=...;
3.group by数据倾斜
解释:
由于数据源的问题,某个分区(标记)的数据,可能远远大于另外一些分区(标记的数据0,就会出现数据倾斜 例如:0分区:1w 1分区:97w 2分区:2w
解决方案:
手动开启负载均衡, set hive.groupby.skewindata=true;程序会自动开启2个MR任务处理该数据
第1个MR负责将数据随机打散(避免倾斜),进行处理,获取到结果.第1个MR程序的Reduce结果 作为第2个MR程序的MapTask任务数据源
由第2个MR程序对数据做最终合并即可
4.count()计数的时候,如果数据量比较大,尽量用group by 结合count一起用
select count(distinct id) from表名; --转1个MR,数据量大的时候,可能失败
select count(id) from (select id from表名 group by id)a; --转2两个MR,效率相对较低,但是大数据量情况下,也能计算
5.避免出现笛卡尔积的情况
就是你在join查询的时候,尽量写关联条件,例如:select * from a,b on关联条件;
7.动态分区
关闭严格模式,因为严格模式要求,动态分区时,需要指定1个静态分区.
且我们可以设置分区的数量,避免因为分区数量较小,但是文件分区较多的情况下,HiveSQL出错的问题
8.调整MapReduce任务数
MapTask任务数:
默认情况下,1个切片=128MB=1个MapTask任务,我们可以通过设置切片大小,从而调整MapTask任务数
例如:小文件较多的时候,我们就用归档包,降低MapTask任务数...
ReduceTask任务数:
默认情况下,1个分区=1个ReduceTask任务=1个最终结果文件,如果要调整ReduceTask数量,就手动调整分区数量
9.并行执行
如果1个HiveSQL转换成了多个阶段,且各个阶段之间的依赖度不高的情况下,就可以考虑让这些阶段并行执行.可以提高自行效率.
10.严格模式
这个严格模式不是动态分区的严格模式,这个严格模式是:禁用低效的SQL
低效SQL如下:
1.分区表,查询的时候 没有写分区字段
2.order by语句 必须要结合limit一起用
3.禁用笛卡尔积的操作
11.JVM重用
默认情况下,Container资源容器用一次就释放了,频繁的创建和销毁非常消耗资源,实现JVM重用,就是实现Container资源容器重用
12.推测执行
类似于:木桶效应,当1个MR程序的多个Task任务并行执行时,如果某个Task任务执行速度较慢,会严重拖慢整个程序
Hive会找出那个执行最慢的Task任务,然后创建1个新的任务,和它执行一模一样的计算,然后采用先计算完 就用谁的结果.
实际开发中,一般禁用
13.explain执行计划
格式:
explain HiveSQL语句,可以查看HiveSQL划分了多少个阶段,阶段越少越少
调优总结：
1. 调硬件.
包括但不限于: CPU, 内存, 磁盘, 因为Hive依赖: Hadoop, MySQL, Java, Linux, 所有这些优化都会影响HiveSQL执行效率.
2. 开启或者增大某些配置.
例如: Fetch抓取, 并行度机制, 动态分区数, 负载均衡(解决: Group by数据倾斜), 严格模式(禁用低效SQL)......
3. 关闭或者减小某些配置.
例如: 推测执行, 动态分区的严格模式, MapTask任务数(当小文件过多, 1个文件 至少 = 1个MapTask, 可以通过 归档包 解决)
4. 减少IO传输.
例如: 存储方式(行存储, 列存储), 压缩方式(zlib, snappy), join优化.
三、Spark调优方法
1- 常规性能调优
最优资源配置
并行度调节
广播变量
2- 算子调优
调节mapPartitions
foreachPartition优化数据库操作
filter与coalesce的配合使用
repartition解决SparkSQL低并行度问题
reduceByKey本地聚合
3-Shuffle调优
调节map端缓冲区大小
调节reduce端拉取数据缓冲区大小
调节reduce端拉取数据重试次数
调节reduce端拉取数据等待间隔
调节SortShuffle排序操作阈值
4- JVM调优
调优一：降低cache操作的内存占比
调优二：调节Executor堆外内存
调优三：调节连接等待时长
5-Spark数据倾斜
数据倾斜的表现
Spark作业的大部分task都执行迅速，但是有的task在运行过程中会突然报出OOM，
反复执行几次都在某一个task报出OOM错误，此时可能出现了数据倾斜，作业无法正常运行。
定位数据倾斜问题
① 查阅代码中的shuffle算子，例如reduceByKey、countByKey、groupByKey、join等算子，根据代码逻辑判断此处是否会出现数据倾斜；
② 查看Spark作业的log文件，log文件对于错误的记录会精确到代码的某一行，可以根据异常定位到的代码位置来明确错误发生在第几个stage，对应的shuffle算子是哪一个；
解决方案：
解决方案一: 避免数据源的数据倾斜
通过对Hive表中对倾斜的数据进行预处理，以及在进行kafka数据分发时尽量进行平均分配 , 从根源上解决数据倾斜问题,
解决方案二: 避免shuffle过程
为避免数据倾斜，考虑避免shuffle过程，如果避免了shuffle过程，从根本上就消除了发生数据倾斜问题的可能;
解决方案三：过滤导致倾斜的key
在Spark作业中考虑将可能导致数据倾斜的key对应的数据进行过滤，这样，在Spark作业中就不会发生数据倾斜了;
解决方案四：提高shuffle操作中的reduce并行度
8922
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
