DEV05 GBase 8a MPP Cluster 数据库性能优化

一、数据库为何要优化

（一）从 DBA 角度谈数据库为何要优化

1、系统上线一段时间后，数据的快速膨胀会导致查询性能下降。
      数据最终是持久存放在磁盘上的，所以 IO 读取速度直接影响数据的吞吐量。应用系统上线后，随着操作用户和业务量增多，存入数据库的业务数据也会越来越多，数据处理时间会相应变长，甚至经常引起数据库线程挂起。
2、数据库集群服务器硬件规划：
      通过当前业务数据总量、每日数据平均增长量预估数据节点数、每个节点的物理内存、磁盘容量、RAID方案等。通过估算用户访问页面的请求数量，预估管理节点的数量。
3、数据库配置问题：
      数据库引擎参数未最佳设置。8a 集群安装完毕后，能够自动评估缓存数据最大占用内存大小和SWAP空间，所以，一般不需要·特别干涉。
4、数据库冗余开放的日志可能大量占用服务器磁盘：
      比如审计日志的开放和审计策略的选择是需要非常慎重的。合理的审计事件和粒度的审计策略对于数据安全管理、监测 SQL 执行性能有着重要的作用。脱离客户审计需求的审计策略将会严重影响数据库性能。
5、大数据项目要求整合外部门数据，要求数据库具备高度灵活的扩展性。
      比如客户关系系统，随着需求的增长需要引入人事系统和财务系统的数据，如果使用 8a 虚拟集群的功能，人事系统和财务系统是两个虚拟子集群，可以直接挂载到客户关系系统主集群上。未来，财务数据不需要了，也可以随时卸载。

（二）数据库产品的选型：

        关系型数据库最流行、最先进的架构是联邦架构。从以上简略架构图可以看出，Coordinators（管理节点）集群和 Data Nodes（数据节点）集群是解耦的。Coordinators 最多支持 64 个节点，能承受更高的并发。Data Nodes 可以分隔为多个虚拟集群，历史项目中单个虚机集群的 Data Nodes 最多支持 370 个节点，则多虚拟集群的扩展性完全满足海量数据爆炸式增长。

（三）从 DE（开发工程师）角度谈数据库为何要优化

1、业务模型表结构耦合，导致多表关联查询性能下降：
业务需求的迭代增加超出了原始库表设计的初衷，导致扩展后的主题表体系结构越来越不合理，导致表关联增加、明显降低查询效率。
2、SQL 逻辑结构不合理：子查询层次过多；表关联时不恰当的关联条件；忘记关联条件导致笛卡尔乘积。
3、数据库连接池不合理：连接池过小会导致连接池满后，新的客户无法连接上系统；但连接池过大，也会造成资源无效损耗，出现新的性能问题。
4、资源泄露：应用程序中没有及时关闭数据库连接、没有关闭查询结果集对象等。
5、对需求理解的偏差导致一次查询读取不合理的数据量。
6、项目经理对于开发语言和数据库接口驱动不恰当的选择。比如项目经理接手一个购物网站，最适合的开发语言是 Java，最适合的数据库接口驱动是 JDBC。可能经理最擅长 c# 语言，所以决定团队使用这种语言开发，数据库接口驱动选择 ado.net，这就带来系统上线后高并发带来的风险。

（四）DE 优化方案

1、减少交互次数（减少网络传输）
从数据库中批量一次性取得所要求的数据，减少反复回调次数。
2、减少数据访问（减少磁盘访问）
增加必要的缓存，减少对数据库数据的不必要实时读写。
3、返回更少数据（减少网络传输或磁盘访问）
4、优化查询 SQL（减少服务器CPU、磁盘及内存开销）
建立合适的索引，SQL语句结构调整，大SQL语句的拆分等方式优化查询语句的执行性能。

二、库表设计优化

（一）需求开发遵循自顶而下的优化原则。

• 优化项目管理全生命周期，不能一味执行瀑布模型，注意各个阶段必要的迭代。
• 充分理解业务需求的前提下，从系统架构设计->业务模型设计->建表->模拟数据汇入和调优。架构设计是整个系统的“上层建筑”，决定了业务模型的规划。业务模型设计一般是在数仓设计阶段。该阶段根据业务场景定义若干主题，设计出每个主题的维度、指标、派生指标，制定或整理数据字典的标准。事实表（维度和指标的组合）和维表的设计结构必须是星型或者雪花型，这是最适合关系型数据库的。各组主题表之间尽量解耦，以避免未来客户端查询表关联数量的增长。建表阶段是实现业务模型的过程。根据维度最长字节、指标最大值设计列的数据类型。彻底摒弃 One-Size-Fits-ALL 的做法。

（二）业务模型的设计

       在多维分析的商业智能解决方案中，根据事实表和维度表的关系，又可将常见的模型分为星型模型和雪花型模型。在设计数据仓库逻辑型数据的模型的时候，就应考虑数据是按照星型模型还是雪花型模型进行组织。

1、星型模型：
所有维表都直接连接到事实表上，整个图解就像星星一样。

我们可以看到事实表通过地域键和地域维表关联，通过部门键和部门维表关联，以此类推。地域、部门、产品、时间四张维表和事实表分别关联，维表是一个层次的。

2、雪花模型：
存在一个或多个维表通过其他维表连接到事实表上时，其图解就像多个雪花连接在一起。

上面雪花模型图，地域表有被细分为国家、省份、城市三个维度。和星型模型不同的是：维表层次大于一。

3、模型的选择：
       星型架构是一种非正规化的结构，多维数据集的每一个维度都直接与事实表相连，不存在渐变维度，所以数据有一定冗余。因为有冗余，所以很多统计不需要做外部的关联查询，因此一般情况下效率比雪花模型高。
       相比星型模型，雪花模型的特点是贴近业务，数据冗余较少。设计是需要同时考虑业务的贴近性和 SQL 执行性能。比如，以上雪花模型中时间维表就没有必要拆分为年月日，处理日期的 SQL 内置函数中有很多。

（三）库表字段类型的选择

1、选择最合适的字段类型：
(1) 数值型：能用整型不用浮点；数值尽量使用精度低的类型
(2) 字符型：能用可变长度就不用固定长度
(3) 主键长度不要过长
(4) 尽量为字段设置 NOT NULL
(5) 尽量不使用自增（ auto_increment ）列，可以用 GUID 替代

2、选择最合适的哈希分布列：
(1) 单表查询使用最频繁的条件列；
(2) 多表等值关联经常用到的列。
(3) 聚合计算中经常操作的列。

三、原始数据优化

（一）充分利用索引
      8a 集群两种重要的索引是智能索引和哈希索引。正确运用索引能带来 SQL 执行性能的提速。
1、利用粗粒度智能索引，可以快速定位到结果所在的DC（数据存储的最小单元）。
【注意】在条件表达式的字段上使用函数会导致智能索引失效。

2、哈希索引通常可以用来解决等值查询的定位效率。但会带来维护成本，会影响数据加载及DML操作的性能，实际使用时需根据具体需求而定。
【应用场景】内存基本充足、对以单表精确查询为主。如电信业务中的并发话单查询等。
不适合创建哈希索引的列：(1) 二进制；(2) 数据量大但去重值少。
例如，创建全局哈希索引的 SQL 文如下——

CREATE TABLE testIndex (IDNo varchar(18));
CREATE INDEX idx_tIndex ON testIndex(IDNo) key_block_size = 4096 USING HASH GLOBAL; // 数据块最小 4096 最大 32767，而且必须是 4096 的倍数

（二）数据排序
      数据在按某查询列进行排序后，则相同数据取值会集中存放在有限的数据包中，因此在以该列进行过滤时，利用智能索引命中的数据包会很少，不仅能降低IO量而且会提高压缩比。这样做最大的好处是可以将智能索引的过滤效果发挥到最优，从而使整体查询性能大幅提升。
      建议在实际应用场景许可的前提下，将数据按照查询常用条件列进行排序。如在金融行业中，通常按照银行账号或保单号进行查询，因此可按一定的时间间隔对数据按照银行账号或保单号进行排序，则在此时间范围内的银行账号或保单号有序，在进行查询时，便可通过智能索引特性提高查询性能。

（三）选择合适的压缩策略
       大部分应用中性能的瓶颈是磁盘IO，所以新型数据库的设计都以降低磁盘IO为主要设计目标，数据压缩可减少I/O的时间，提升性能，压缩也是提高 8a 集群性能的主要技术之一，8a 集群并行执行器能够从上层并行调度解压，因此使解压的适用性得到了很大的提升，很多场景下（尤其是针对超大数据量的场景），使用合适的数据压缩方式可获得比不压缩更好的性能。

       8a 集群基于列存储，采用高效透明压缩技术，内置数十种不同等级的压缩算法，并设置了库级，表级，列级压缩选项，灵活平衡性能与压缩比的关系，而且压缩与解压缩过程对用户是透明的。压缩方式分为三类：
默认自适应压缩；
高压缩(压缩速度快， 解压速度慢)；
轻量级压缩(压缩和解压缩速度都快）。

       8a 集群对于数值型和字符内部采用不同的压缩算法。
√ 列级 int 型压缩方式选项：0，1，5
√ 列级 varchar 型压缩方式选项：0，3，5
√ 表级组合压缩方式为：（0，0）、（3，1）、（5，5）
       选取原则：
（3，1）压缩优势是压缩比高，比（5，5）压缩比高一倍，但是执行效率一般。如果对存储空间要求高，对性能不太要求时，建议使用（3，1）压缩；如果对存储空间要求不高，对性能要求高时，建议使用（5，5）压缩。
       在实际数据压缩算法选择时，首先根据表中数据的业务特性进行表级压缩选项设定，对于频繁读取，性能要求高的，例如模型数据、指标数据等，采用轻量级压缩。对于读取频度不高，性能要求一般的，如未清洗的历史数据等，采用高压缩算法。然后，根据表中每个列的数据特性结合业务操作特性选择合适的列级压缩选项，选取原则与表级压缩类似。

举个例子：业务表 factTable 数据量增长迅速、查询频繁，我们可以对 factTable 做表级轻量级压缩，以改善性能并节省存储空间，可执行以下 SQL。

ALTER TABLE factTable ALTER COMPRESS(5,5);

四、分布式执行计划原理

（一）什么是分布式执行计划：

1、执行计划：
       执行计划是一条 SQL 语句执行过程或者访问路径的描述。
       在数据库系统设计中，执行计划是对SQL执行流程的形式化描述，包括了SQL执行需要的所有算子以及其执行次序。通过“EXPLAIN + SQL”指令可以详细地查看其执行计划，找到性能瓶颈，为我们优化SQL性能提供方向和依据。
2、分布式执行计划：
       相比于集中式数据库，分布式数据库拥有多个数据节点，分别负责各自分片的数据计算与存储，那么其执行计划就需要特殊的实现方式。像 GBase 8a 这样联邦架构的大规模分布式并行处理数据库集群，通过引入分布式算子实现数据分片存储功能，管理节点发起者负责执行计划解析优化，协调数据节点并发执行，各个数据节点的执行结果由管理节点进行进一步整合进行分组、排序等操作，最终管理节点的发起者将结果集回馈给客户端。
       包含复杂表关联查询的 DQL 语句，通过执行计划可以清晰分析出性能的瓶颈，还可以再次验证库表设计是否还残留缺陷。这是本章节课程的重点。

3、分布式执行计划的基本原理

3.1 分析器
1、词法分析：从查询语句中识别出系统支持的关键字、标识符、操作符、终结符等。
2、语法分析：根据SQL语言的标准定义语法规则，使用词法分析中产生的词去匹配语法规则，生成对应的抽象语法树。
3、语义分析：对语法树进行有效性检查，检查语法树中对应的表、列、函数、表达式是否有对应的元数据，将抽象语法树转换为逻辑执行计划。

3.2 优化器
基于代价的查询优化（Cost Based Optimization，CBO）：对SQL语句所待选执行路径进行代价估算，从中选择代价最低的执行路径作为最终的执行计划。

1. 调整join语句中表的连接顺序；
2. 去除无效的条件；
3. 当表中有多个索引时决定选择哪一个。

3.3 生成分布式执行计划
管理节点下发已经调优的SQL给各个数据节点，并且确定执行步骤对应的数据迁移方案（BroadCast 广播、Redistribute 重分布）。

（二）先导知识：

1、数据分布方式：
       8a 集群按照数据分布策略分成复制表和分布表。
(1) 复制表：每个数据节点都有完整的数据。
(2) 分布表：

• 随机分布表：数据均匀分布在各个数据节点。
• 哈希分布表：按照数据的哈希值分布在各个数据节点。
  

2、散列数据分布原理

       8a 集群加载时对原始数据中的每条数据中指定的哈希列进行处理，处理后的数据按照哈希值装入特定的哈希桶中，每个哈希桶对应一个集群数据节点。这样每个节点所得到的数据就都具有了某种共同特征（指定列都具有相同的哈希值），在查询时优化引擎可以根据这些共同特征对查询计划进行优化，以达到缩短查询时间的目的。
       假设当前表哈希分布列是 pname，字符类型，存储的是三国人名的全拼。当前表的数据将会按照 pname 的哈希值存储到三个数据节点中。paname 值先经过 hash function 转换为 hash key，通过哈希映射表决定 pname 值对应的数据行存储到哪个数据节点上。哈希映射的系统表 nodedatamap 是哈希桶概念的核心。按照哈希分布策略，数据分布一般不是均匀的，很可能产生数据倾斜，SQL 性能优化时我们必须处理数据倾斜问题。

（三）表关联查询执行计划详解：

       JOIN 和 GROUP BY 是最常用的算子。以下分几种情况描述，让大家循序渐进地理解执行计划的原理。

1、分布表静态join执行计划
典型场景：两张哈希分布表关联查询，关联条件都是哈希分布列，执行计划最优！
以下讲解 JOIN 相关执行计划的时候，我都会使用下面这个 SQL：

Select * From T1 inner join T2 on T1.gID = T2.gID // T1.gID 和 T2.gID 都是哈希分布列

       客户端发出 SQL 给某管理（Coordinator）节点，管理节点的优化器生成分布式执行计划再下发到数据节点上并行执行。图中 T1 或 T2 的后缀表示分片表；假设每张表在每个数据节点只有一个分片。比如 T1_n1 表示 T1 表的 n1 分片。每个节点 T1 分片数据和 T2 分片数据的哈希值相同，则直接 JOIN 即可得到关联数据。然后将结果汇总给发起的管理节点，最后回馈给客户端。整个过程，参与运算的数据没有做任何迁移处理，所以这种情况叫做静态 JOIN 执行计划。

2、分布表join复制表执行计划
典型场景：一张分布表和一张复制表关联查询，关联条件都是哈希分布列，执行计划最优！
SQL 示例：

Select * From T1 inner join T2 on T1.gID = T2.gID // T1 是分布表、T2 是复制表

我们看到，无论是哈希分布表还随机分布表，T1 在各个 Data Node（数据节点）上分片数据的 gID 列哈希值仅在本地 T2 表匹配即可，因为 T2 是复制表，在所有 Data Node 都有一份数据，执行 JOIN 执行计划时无需数据迁移。

3、小表拉复制表join执行计划
典型场景：大表和小表关联查询，关联条件中大表字段是哈希列，小表字段非哈希列。
SQL 示例：

Select * From T1 inner join T2 on T1.gID = T2.gID;
// ① T1 是分布表、T2.gID 非哈希列； ② T2 满足小表的条件。

由于T1和T2关联列一个是分布列，一个不是，所以计划分行前分布策略决定将小表T2完整数据复制到各个数据节点，生成临时表 tmp2，则各节点 T1 分片表分别关联这些本地的临时表，然后将结果集汇总到发起的管理节点合并、排序即可回馈给客户端。那么，分布策略如何判断T2是数据量小的表呢？本章结束前将使用一张流程图解释完整的逻辑。

4、动态重分布join执行计划一（等值关联列只有一个哈希）
典型场景一：两张表关联查询，关联条件中一个字段是哈希列，一个字段非哈希列。
SQL 示例

Select * From T1 inner join T2 on T1.gID = T2.gID;
// ① T1.gID 是哈希列、T2.gID 非哈希列 ② 两张表都不满足小表条件

T1和T2都不满足小表的条件，则分布策略决定将T2数据按照 gID 做哈希重分布，T2 在各个节点生成的临时表数据 gID 列的哈希值和 T1 分片表就能完全对应了。T1表的数据没有动，这就是第一种哈希重分布的情况。

5、动态重分布join执行计划二（等值关联列都是非哈希）
典型场景一：两张表关联查询，关联条件中两个字段皆非哈希列。
SQL 示例

Select * From T1 inner join T2 on T1.gID = T2.gID;
// ① T1.gID 和 T2.gID 都不是哈希列 ② 两张表都不满足小表条件

由于 T1 和 T2 分片表在各个节点的数据 gID 列的哈希值都匹配布行，则分布策略决定将两表都按照 gID 列做哈希重分布，T1 和 T2 在各个节点都生成临时表。这种执行计划代价是最大的。

6、哈希分布表静态group by执行计划
典型场景：单表聚合计算，GROUP BY列表中包含哈希列。
SQL 示例：

Select c1, c2, sum(c3) From T1 Group By 1,2 Order By 1; // c1 是哈希分布列

T1 的三个分片表的源数据都是按照 c1 列做哈希分布的，各个节点分别做聚合运算，然后将统计值传递到管理节点，做合并和排序操作，即可回馈给客户端。
这种聚合运算的执行计划，执行前没有数据迁移的过程，所以是静态 GROUP BY。

7、动态重分布group by执行计划
典型场景：单表聚合计算，GROUP BY 列表中不包含哈希列；该表在数据节点动态重分布后做聚合运算。
SQL 示例：

Select c1, c2, sum(c3) From T1 Group By 1,2 Order By 1; // Group By 中不存在哈希列

Group By 列表存在多列，执行计划选择第一列作为哈希重分布列

由于 GROUP BY 列表中不存在哈希分布列，则策略选择第一列 c1 将数据重分布，形成 tmp1_n1、tmp1_n2、tmp1_n3 三张临时表，然后各个节点对这些临时表最聚合运算，然后将结果合并到管理节点。这就是系统数据做动态重分布后再做聚合运算的执行计划。针对 GROUP BY 列表中不存在哈希分布列，系统默认使用这种计划。

8、两阶段group by执行计划
典型场景：单表聚合计算，GROUP BY 列表中不包含哈希列；各个数据节点数据分别聚合（不做动态哈希重分布），最后管理节点再次做聚合运算。
SQL 示例：

Select c1, c2, sum(c3) From T1 Group By 1,2 Order By 1; // Group By 中不存在哈希列

如果开启 gcluster_hash_redistribute_groupby_optimize 参数，则系统针对 GROUP BY 列表中不存在哈希列的情况，执行两阶段 GROUP BY 计划，不做数据重分布处理。这种计划首先各个节点执行 T1 分片数据聚合运算，结果集传递给管理节点，必须做第二次聚合运算才能保证结果是正确的，最后排序、回馈给客户端。

9、影响 JOIN 执行计划的参数

• gcluster_hash_redistribute_join_optimize 参数:
  等值关联运算的分布式执行计划策略的控制开关。
  取值释义：0 - 小表拉复制表。1 - 动态重分布。2（默认值） - 自动评估
• gcluster_hash_redist_threshold_row 参数：分布式执行计划策略选择的数据行数阈值
  取值释义：0（默认值） - 不限制。正整数 – 行数阀值，若小表数据行数大于阀值则动态重分布，否则小表拉复制表。

       这两个参数都是 Session 级变量，在 SQL 语句执行之前赋值立即生效，Session 关闭之后立即失效。
       两张表等值关联查询，当关联条件字段都不是哈希分布列时，执行计划选择小表拉复制表还是动态重分布策略，需要这两个参数的配合使用。完整逻辑如下——

假设条件中的 ΔT 表示 T2 比 T1 少百分之多少的数据量。

10、影响 GROUP BY 执行计划的参数

• gcluster_hash_redistribute_groupby_optimize：
  控制聚合运算时使用动态重分布计划的开关
  参数取值释义：
  0 - 执行两阶段 GORUP BY
  1（默认值） – 动态重分布
  也是 Session 级变量。

五、EXPLAIN 使用指南

1、基本语法
      EXPLAIN / DESC [extanded/partitions] select …

• explain只能显示 SQL 文中 select 部分的执行计划
  标准输出为explain，加extanded/partitions时可以以扩展方式和树形方式输出执行计划信息
• 支持 CTE（Common Table Expression），需要开启集群层参数 _t_gcluster_support_cte

2、执行计划标准输出
      首先，我们对执行计划的输出界面，做一个感性的认识——

ID	MOTION	OPERATION	TABLE	CONDITION	ROW、WIDTH、COST
执行步骤标识	该步骤处理方式，拉复制表、重分布，返回结果…	具体执行的算子	具体步骤涉及的表名、视图名、子查询别名、产生的临时结果集的步骤标识	算子的过滤条件、聚合字段	步骤结果评估的条数、行宽、该步骤执行的代价

      步骤的显示顺序是自底而顶的。表格的底部是 00 步骤，表格的第一行是最后一步。
      比如步骤<05>，MOTION 中 BROADCAST 字串表示广播。OPERATION 的 SubQuery1 表示子查询；TABLE 列的 p2 就是子查询的别名。即 子查询 p2 的完整数据每个节点都复制一份儿。

3、执行计划信息字段详解

表头	描述
ID	从00开始，自底向顶递增显示。
MOTION	RESULT：结果发送到客户端，一般为执行计划的最后一步； GATHER：结果发送到汇总节点，一般在sort或聚集函数操作前； REDIST({colHash})：结果按照 colHash 列做哈希重分布； NO REDIST：结果直接保存到对应的数据分片，不进行重分布； BROADCAST：结果拉复制表（全量数据广播）； RAND REDIST：结果随机分布到所有节点； SCALAR {N}：结果为标量，N为标量子查询的编号。
OPERATION	SCAN：单表扫描，并使用条件过滤数据； Table：单表，没有过滤条件； SubQuery{N}：子查询，N为自动编号； Step：使用前一个Step的结果； INNER/LEFT/FULL JOIN：连接操作； WHERE：子查询的WHERE条件； GROUP：分组操作；ORDER：排序操作； LIMIT：计算LIMIT，OFFSET； AGG：distinct，聚集操作； UNION/UNION ALL/MINUS/INTERSECT：UNION操作。
TABLE	哈希分布表：TABLE[{哈希列}]； 复制表：显示[REP]； 随机分布表：显示[DIS]； 子查询别名（OPERATION 列显示SubQuery{N}）； 某个步骤的结果集：OPERATION 列显示为 Step，本列显示为<{ID}>
CONDITION	SCAN 操作的单表过滤条件； JOIN 操作的连接条件； GROUP BY 操作涉及列或表达式； ORDER BY 操作涉及列或表达式； LIMIT OFFSET 内容。

        例如——

        这个执行计划表格 MOTION 栏位中的 REDIST(comid) 表示当前步骤的结果集要针对 comid 列做一次哈希重分布。00 步骤的 OPERTATION 栏位是 Table，表示实表，TABLE 栏位是 p2[groupcode] 表示 p2 表的哈希列是 groupcode。再看 02 步骤 OPERATION 栏位的 INNER JOIN 到 GROUP 之间包含两个 Step，TABLE 栏位的 <00><01>，表示这两步的结果集关联起来在做一次聚合运算。OPERATION 栏位的缩进清晰地表达了算子的执行顺序。

六、总结

      数据库性能的优化涉及硬件和软件的方方面面，作为开发类课程，本文重点讲解灵活运用8a 集群分布式执行计划解决 SQL 执行性能低下的问题，适合数据分析师和开发工程师。
      集群服务器的性能是规划出来的，属于 DBA 的职责。DBA 需要预估数据和用户数量的增长，在甲方预算之内做硬件整体规划。
      SQL 的执行性能是设计出来的。业务模型的设计完全依赖需求开发阶段，设计者对于需求的深层次理解和业务掌握的程度。比如，业务报表的整理不到位、业务流程逻辑有盲点，数据库实表的设计就会有漏洞，可能带来报表生成时额外的表关联，性能当然不能保障。
      在库表设计比较完美的前提下，数据分析师或开发工程师的编码能力虽然有高有低，但是都必须掌握 SQL 调优的方法。看懂分布式执行计划，熟练运用执行计划有助于及时发现问题，在系统累积的数据尚未达到一定量级之前，将发现的结构不合理的 SQL 结构调整为最优，以降低研发团队的维护成本。