# 调优前：学习表结构设计
在本实践中，您将学习如何优化表的设计。您首先不指定存储方式，分布键、分布方式和压缩方式创建表，然后为这些表加载测试数据并测试系统性能。接下来，您将应用调优表实践以使用新的存储方式、分布键、分布方式和压缩方式重新创建这些表，并再次为这些表加载测试数据和测试系统性能，以便比较不同的设计对表的加载性能、存储空间和查询性能的影响。
在进行调优表实践之前，需要先了解表结构设计相关的内容。因为进行数据库设计时，表设计上的一些关键项将严重影响后续整库的查询性能。表设计对数据存储也有影响：好的表设计能够减少I/O操作及最小化内存使用，进而提升查询性能。
本小节介绍如何设计DWS表结构，包括：选择表模型、选择存储方式、压缩级别、分布方式、分布列以及使用分区表和局部聚簇等，从而实现表性能的优化。
#### 选择表模型
在设计数据仓库模型的时候，最常见的有两种：星型模型与雪花模型。选择哪一种模型需要根据业务需求以及性能的多重考量来定。
- **星型模型** 由包含数据库核心数据的中央事实数据表和为事实数据表提供描述性属性信息的多个维度表组成。维度表通过主键关联事实表中的外键，如[图1]所示。
  - 所有的事实都必须保持同一个粒度。
  
  - 不同的维度之间没有任何关联。
  
  
  图1星型模型   
  ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002041575536.png "点击放大") 
- **雪花模型** 是在星型模型基础上拓展来的，每一个维度可以再扩散出更多的维度，根据维度的层级拆分成颗粒度不同的多张表，如[图2]所示。
  - 优点是减少维度表的数据量，各个维度表之间按需关联。
  
  - 缺点是需要额外维护维度表的数量。
  
  
  图2雪花模型   
  ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002077574929.png "点击放大") 
本实践基于TPC-DS的SS（Store Sales）模型做验证。该模型为雪花模型，[图3]显示了该数据模型的结构。
图3TPC-DS Store Sales ER-Diagram   
![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002077456329.png "点击放大")
有关该模型中事实表Store_Sales及各维度表的信息，请查阅TPC-DS官方文档：<https://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp>。
#### 选择存储方式
表的存储模型选择是表定义的第一步。业务属性是表的存储模型的决定性因素，根据下表选择适合当前业务的存储模型。
一般情况下，如果表的字段比较多（大宽表），查询中涉及到的列不多的情况下，适合列存储。如果表的字段个数比较少，查询大部分字段，那么选择行存储比较好。
| **存储模型** | **适用场景**                                                                                           |
|:---|:---|
| 行存       | 点查询（返回记录少，基于索引的简单查询）。 增删改比较多的场景。 |
| 列存       | 统计分析类查询。 group，join多的场景。                              |
   
表的行/列存储通过表定义的orientation属性定义。当指定orientation属性为row时，表为行存储；当指定orientation属性为column时，表为列存储；如果不指定，默认为行存储。
#### 使用表压缩
表压缩可以在创建表时开启，压缩表能够使表中的数据以压缩格式存储，意味着占用相对少的存储空间。
对于I/O读写量大，CPU富足（计算相对小）的场景，选择高压缩比；反之选择低压缩比。建议依据此原则进行不同压缩下的测试和对比，以选择符合自身业务情况的最优压缩比。
**DWS仅支持列存表压缩，不支持行存表压缩功能。**压缩比通过COMPRESSION参数指定，其支持的取值如下：
列存表为：LOW/MIDDLE/HIGH，默认值为LOW。
各压缩级别所适用的业务场景说明如下：
| 压缩级别  | 所适用的业务场景                  |
|:---|:---|
| 低级别压缩 | 系统CPU使用率高，存储磁盘空间充足。       |
| 中度压缩  | 系统CPU使用率适中，但存储磁盘空间不是特别充足。 |
| 高级别压缩 | 系统CPU使用率低，磁盘空间不充足。        |
   
#### 选择分布方式
DWS支持的分布方式有复制表（Replication）、哈希表（Hash）和轮询表（Roundrobin）。
![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/note_3.0-zh-cn.png)
轮询表（Roundrobin）8.1.2及以上集群版本支持。
| **策略**           | **描述**                            | **适用场景**                       | 优势与劣势                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
|:---|:---|:---|:---|
| 复制表（Replication） | 集群中每一个DN实例上都有一份全量表数据。             | 小表、维度表。                        | - Replication优点是每个DN上都有此表的全量数据，在join操作中可以避免数据重分布操作，从而减小网络开销，同时减少了plan segment（每个plan segment都会起对应的线程）。  - Replication缺点是每个DN都保留了表的完整数据，造成数据的冗余。一般情况下只有较小的维度表才会定义为Replication表。                                                                            |
| 哈希表（Hash）        | 表数据通过hash方式散列到集群中的所有DN实例上。        | 数据量较大的事实表。                     | - 在读/写数据时可以利用各个节点的IO资源，大大提升表的读/写速度。  - 一般情况下大表（1000000条记录以上）定义为Hash表。                                                                                                                                                                                    |
| 轮询表（Roundrobin）  | 表的每一行被轮番地发送给各个DN，数据会被均匀地分布在各个DN中。 | 数据量较大的事实表，且使用Hash分布时找不到合适的分布列。 | - Roundrobin优点是保证了数据不会发生倾斜，从而提高了集群的空间利用率。  - Roundrobin缺点是无法像Hash表一样进行DN本地化优化，查询性能通常不如Hash表。  - 一般在大表无法找到合适的分布列时，定义为Roundrobin表，若大表能够找到合适的分布列，优先选择性能更好的Hash分布。   |
   
 #### 选择分布列
采用Hash分布方式，需要为用户表指定一个分布列（distribute key）。当插入一条记录时，系统会根据分布列的值进行hash运算后，将数据存储在对应的DN中。
所以Hash分布列选取至关重要，需要满足以下原则：
1. **列值应比较离散，以便数据能够均匀分布到各个DN**。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列。
2. **在满足第一条原则的情况下尽量不要选取存在常量filter的列**。例如，表dwcjk相关的部分查询中出现dwcjk的列zqdh存在常量的约束(例如zqdh='000001')，那么就应当尽量不用zqdh做分布列。
3. **在满足前两条原则的情况，考虑选择查询中的连接条件为分布列** ，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量。
   对于Hash分布策略，如果分布列选择不当，可能导致数据倾斜，查询时出现部分DN的I/O短板，从而影响整体查询性能。因此在采用Hash分布策略之后需对表的数据进行数据倾斜性检查，以确保数据在各个DN上是均匀分布的。可以使用以下SQL检查数据倾斜性：
   ```
   SELECT 
   xc_node_id, count(1) 
   FROM tablename 
   GROUP BY xc_node_id 
   ORDER BY xc_node_id desc;
   ```
   其中xc_node_id对应DN，一般来说，**不同DN的数据量相差5%以上即可视为倾斜，如果相差10%以上就必须要调整分布列**。
   
4. 一般不建议用户新增一列专门用作分布列，尤其不建议用户新增一列，然后用SEQUENCE的值来填充作为分布列。因为SEQUENCE可能会带来性能瓶颈和不必要的维护成本。
 
#### 使用分区表
分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区表和普通表相比具有以下优点：
1. 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
2. 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
3. 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。
DWS支持的分区表为范围分区表和列表分区（列表分区8.1.3集群版本支持）。
#### 使用局部聚簇
局部聚簇（Partial Cluster Key）是列存下的一种技术。这种技术可以通过min/max稀疏索引较快的实现基表扫描的filter过滤。Partial Cluster Key可以指定多列，但是一般不建议超过2列。Partial Cluster Key的选取原则：
1. 受基表中的简单表达式约束。这种约束一般形如col op const，其中col为列名，op为操作符 =、\>、\>=、\<=、\<，const为常量值。
2. 尽量采用选择度比较高（过滤掉更多数据）的简单表达式中的列。
3. 尽量把选择度比较低的约束col放在Partial Cluster Key中的前面。
4. 尽量把枚举类型的列放在Partial Cluster Key中的前面。
 
#### 选择数据类型
高效数据类型，主要包括以下三方面：
1. **尽量使用执行效率比较高的数据类型**
   一般来说整型数据运算（包括=、＞、＜、≧、≦、≠等常规的比较运算，以及group by）的效率比字符串、浮点数要高。比如某客户场景中对列存表进行点查询，filter条件在一个numeric列上，执行时间为10+s；修改numeric为int类型之后，执行时间缩短为1.8s左右。
   
2. **尽量使用短字段的数据类型**
   长度较短的数据类型不仅可以减小数据文件的大小，提升IO性能；同时也可以减小相关计算时的内存消耗，提升计算性能。比如对于整型数据，如果可以用smallint就尽量不用int，如果可以用int就尽量不用bigint。
   
3. **使用一致的数据类型**
   表关联列尽量使用相同的数据类型。如果表关联列数据类型不同，数据库必须动态地转化为相同的数据类型进行比较，这种转换会带来一定的性能开销。
   
 
#### 使用索引
- 建立索引的目的是为了加速查询，所以请确保索引能在一些查询中被使用。如果一个索引不会被任何查询语句用到，那这个索引是没有意义的，请删除这个索引。
- 避免创建不需要的二级索引，有用的二级索引能加速查询，但是要注意索引占用空间也会随着索引数量的增加而增加。每增加一个索引，在插入一条数据的时候，就要额外新增一个Key-Value，所以索引越多，写入越慢，并且空间占用越大。另外过多的索引也会影响优化器运行时间，并且不合适的索引会误导优化器。所以索引并不是越多越好。
- 根据具体的业务特点创建合适的索引。原则上需要对查询中需要用到的列创建索引，目的是提高性能。下面几种情况适合创建索引：
  - 区分度比较大的列，通过索引能显著地减少过滤后的行数。例如推荐在人的身份证号码这一列上创建索引，但不推荐在人的性别这一列上创建索引。
  
  - 有多个查询条件时，可以选择组合索引，注意需要把等值条件的列放在组合索引的前面。这里举一个例子，假设常用的查询是**SELECT \* FROM t where c1 = 10 and c2 = 100 and c3 \> 10;** ，那么可以考虑建立组合索引**Index cidx (c1, c2, c3)**，这样可以用查询条件构造出一个索引前缀进行Scan。
   
- 在查询条件中使用索引列作为条件时，不要在索引列上做计算、函数或者类型转换的操作，会导致优化器无法使用该索引。
- 尽量使索引列包含查询列，避免总是SELECT \* 查询所有列的语句。
- 查询条件使用 !=，NOT IN时，无法使用索引。
- 使用LIKE时如果条件是以通配符 % 开头，也无法使用索引。
- 当查询条件有多个索引可供使用，但用户知道用哪一个索引是最优的时，推荐使用优化器Hint来强制优化器使用这个索引，这样可以避免优化器因为统计信息不准或其他问题时，选错索引。
- 查询条件使用IN表达式时，后面匹配的条件数量不宜过多，否则执行效率会较差。