浅谈MPP数据库优化

目录

SQL优化

语法改造

执行计划

下推优化

统计信息采样

表设计 

行列存选择

字段类型选择

索引选择

Roaring Bitmap

物化视图

系统性优化

脏叶清理

参数优化

query_dop

work_mem

---

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子查询处理中都要用到。