SQL优化常用方法总结

作者:jcmp      发布时间:2021-05-07      浏览量:0
SQL优化方法有很多,作者在此将个人工作

SQL优化方法有很多,作者在此将个人工作中常用的几个优化方法总结出供参考。

一、列裁剪和分区裁剪

原理:列式存储是将每一列数据存在一个地方,分区数据的 每个分区对应一个文件夹,因此通过列裁剪和分区裁剪能够有效减少表的扫描和数据量计算来提高查询速度。

有一种应用场景是大小表join时,小表放前作为左表来过滤大表数据,缩小数据范围从而减少计算。

二、谓词下推

两/多表join的时候过滤条件写在子查询中,避免全表扫描增加的计算。虽然大部分场景都能被识别出做自动优化,但是写SQL需要有这个优化意识。

三、数据倾斜问题

数据量大情况下, 对于join, group by, count distinct和row number等窗口函数这样的算子在shuffle过程中会产生数据倾斜的问题。

定位是否存在数据倾斜问题有以下两种常用方法:

发现任务有数据倾斜问题后,常用的优化方法有以下几种:

四、业务优化

相同常用的查询,落一个中间表来复用减少计算,更多在建模过程当中使用。

五、合并小文件

1.往动态分区表插入数据时,会插入大量小文件;

2.reduce的数量设置的较多,到reduce处理时,会分配到不同的reduce中,会产生大量的小文件;

3.源数据文件就存在大量的小文件。

解决方法:

1. 在Map读数据时将小文件合并,Spark参数设置为:

set spark.sql.parquet.adaptiveFileSplit=true;

set spark.sql.files.maxPartitionBytes=536870912;

2. 在Map输出端将文件合并(比较少用):

set spark.merge.files.byBytes.enabled=true;

set spark.merge.files.byBytes.repartitionNumber=100;

set spark.merge.files.byBytes.fileBytes=134217728;

set spark.merge.files.byBytes.compressionRatio=3;

整体优化思路是在有限的资源中选取合适的优化策略,最大化利用计算资源来提高查询速度。

任务运行问题排查思路:在Web UI查看任务运行情况,通过执行计划定位有问题的逻辑,进而定位SQL具体处理逻辑,结合Web UI分析问题出现的原因,比如:数据倾斜,小文件过多等等,通过一些SQL优化和配置参数手段解决任务运行问题。