目录
SQL优化
(1)分区裁剪。
(2)列裁剪
(3)常量折叠
(4)谓词下推
这四种比较简单的优化手段,虽然现在的数据库内部的优化器已经做到了自动优化这几点,但是不同产品之间是有差异的,我们并不知道一个产品的内部优化器是否完善,一定要有上面优化的意识。
语法改造
A union all B
这种语法是取A和B结果集的并集,不做去重。但是这种是俩结果集并行处理,如果A和B的逻辑复杂和数据量过大的话,严重影响效率,正确方式就是将其改造成下面内容
insert into 目标表 select * from A;
insert into 目标表 select * from B;
--两个语句拆开执行,对集群IO压力和CPU压力小很多,上面的例子逻辑简单,只浪费IO不浪费CPU
A not in B
这种语法是A表做驱动表,B做被驱动表。当两个表数据量差距大,A和B进行关联就会导致优化器组合太多,默认设置为nest loop 也就是嵌套查询,或者是双层for循环,会出现效率低,甚至跑不动,之前开发Spark-sql的时候遇到过这个问题,换种思路就能解决,如下:
--直观写法
SELECT *
FROM SYS_ORG A
WHERE A.ORG_ID
NOT IN (select B.ORG_ID
from SYS_ORG_TEST AS B ) ;
--改造后
SELECT *
FROM SYS_ORG A LEFT JOIN SYS_ORG_TEST B
ON B.ORG_ID = A.ORG_ID
WHERE B.ORG_ID IS NULL;
执行计划
为什么要懂执行计划?
(1)因为执行计划是最能反应数据库,或者数据框架内部真实的执行流程。也就是数据库内部优化器经过语法解析,语义解析,逻辑执行计划的代价估算后的真实物理执行计划。
(2)真实的物理计划,能直观的显示出处理这部分sql逻辑都用了哪些算子,算子就是数据库内核处理数据的C++方法接口。
对执行计划有个大概的了解后。梳理下算子,分五种算子:
其中瓶颈点往往都是stream算子,因为它是分布式数据库计算模块中的核心中的核心shuffle功能,Streaming共有三种形态,分别对应了分布式结构下不同的数据shuffle功能:
GATHER:作用是CN从DN收集数据。
REDISTRIBUTE:作用是DN根据选定的分布列把数据重分布到所有的DN。
BROADCAST:作用是把当前DN的数据广播给其他所有的DN
下推优化
简单来说,因为share nothing数据库,各个节点相互独立,将执行计划直接交给数据节点去处理,
中间不会有DN和DN之接的交互,所以用好了执行速度非常快。但弊端就是,需要各个DN直接交互的语句返回结果是错误的,例如带Join的语句,返回随机数的语句等,没有把握慎用。但这个知识点十分适合面试吹牛。
统计信息采样
数据库中,统计信息是规划器生成计划的源数据。没有收集统计信息或者统计信息陈旧往往会造成执行计划严重劣化,从而导致性能问题。 也就是说,数据库代价估算十分依赖基于统计信息的逻辑执行计划。
所以需要定期去对表收集统计信息,极端场景下,可能会调大统计信息采样的样本量!
表设计
行列存选择
行存
适合TP场景 (textfile) 不好压缩,磁盘占用高,对CPU压力小,对IO不友好。
列存
适合AP场景 (parquet,orc,column) 易压缩,磁盘占用低,读取的时候,会解压,浪费一定的CPU,但对IO非常友好。
字段类型选择
比较常见的问题,就是定长和变长的选择,定长如char(),varchar()这种,变长如text和string这种。
总结下来各有优缺点,拿变长来说:
(1)如果变长灵活,不会出现上游varchar(200),下游varchar(20)因为数据类型不够导致的报错。
(2)变长类型比较少,而且统一,比如都设置成String,关联的时候,触发隐式转换的概率就越低。
缺点就是毕竟是变长类型,作为CPU需要统计每个字段的长度,这期间是有代价的,但定长就不会,因为长度固定,这部分开销不会有。
总结下来,变长和定长恰好优缺点互补。想要性能就定长,想要容错就变长。
分布键选择
在Hadoop里面没有分布键这一说,他是128M一个数据块,能够保证均匀分布。但是MPP架构不一样,没有块存的概念。行存tuple,列存CU(compress unit),能做到的尽可能均匀的方案就是以分布键为Key,其他的值为Value。因此分布键的选择至关重要,选择不合理,会导致分布不均匀,造成存储倾斜,存储倾斜一定会导致计算倾斜。这里推荐分布键选择遵循着两个原则:
(1)优先主键,因为主键往往不为空,散列度高。
(2)经常被关联的键,经常被关联如果是分布键,就不会有重分布的过程了,省略不少计算代价。
索引选择
B+ 适合范围和比较查询。
B树 适合点查询。
B*树 B+变种,在非根节点和非叶子节点内部多了一个指向兄弟节点的指针,这样做的目的是可以有效的减少树高,从而提高索引效率。
倒排索引 适合模糊查询,和在长字段里面。
Psort索引 列存专用,基于稀疏索引之上做了优化,将列存表存储单元内部的CU中的min\max值内部做了排序,减少了读取冗余数据碎片。
Roaring Bitmap
大规模数据去重,快速聚合分析,大规模数据的快速查询,布尔类型数据存储可以省略大量的磁盘空间。
物化视图
传统的视图在调用的过程中是现调用现计算。物化视图反之,相当于把计算结果提前计算好,查询的时后,会立刻返回结果。但也会付出代价。比如说会定期去计算和更新结果,这期间的计算过程都不是免费的。如果非要用物化视图不可,在这个视图内部逻辑应该做好优化,防止物化视图自动更新的时候,占用集群字段过多。
临时表与CTE
优化查询性能是数据库管理的核心任务之一。使用公用表表达式(CTE)和临时表是两种重要的技术手段。
这种临时表,要活用,可以在一个会话里随便折腾,会话结束后,临时表消失,十分省心。
CREATE TEMPORARY TABLE temp_table (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL
);
CTE中SELECT的基本价值是将复杂的查询分解称为简单的部分,该子句定义了两个辅助语句regional_sales和top_regions,其中regional_sales的输出用在top_regions中而top_regions的输出用在主SELECT查询。这个例子可以不用WITH来书写,但是就必须要用两层嵌套的子SELECT,使得查询更长更难以维护。另外CTE用后就自动销毁
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
系统性优化
脏叶清理
如果导入过程中,进行了大量的更新或删除行时,应运行VACUUM FULL命令,然后运行ANALYZE命令。大量的更新和删除操作,会产生大量的磁盘页面碎片,从而逐渐降低查询的效率。VACUUM FULL可以将磁盘页面碎片恢复并交还操作系统。但注意,这种会对表上八级锁,其他事务无法影响这个表。
参数优化
query_dop
相当于指定一个线程的核数,默认是1,即一个线程一个核去处理,可以会话级设置。不建议调高。因为分布式数据库的场景下,节点越多,链接越多,使用数据库的整体代价就越高。
work_mem
设置内部排序操作和Hash表在开始写入临时磁盘文件之前使用的内存大小。ORDER BY,DISTINCT和merge joins都要用到排序操作。Hash表在散列连接、散列为基础的聚集、散列为基础的IN子查询处理中都要用到。