Analytic DB使用优化

1、表结构优化

1.1 分区列选择

基本原理分析型数据库的表一级分区采用hash分区，可指定任意一列（不支持多列）作为分区列，然后采用以 下标准CRC算法，计算出CRC值，并将CRC值模分区数，得出每条记录的分区号。空值的hash值与字符串”- 1”相同

分析型数据库的调度模块会将同一个表组下所有表的相同分区分配在同一个计算节点上。好处在于，当出现多 表使用分区列join时，单计算节点内部直接计算，避免跨机计算。

分区列选择依据（按优先级高低排序）：

• 如果有多个事实表（不包括维度表）进行join，选择参与join的列作为分区列
• 选择group by 或distinct 包含的列作为分区列。
• 选择值分布均匀的列，不要选择分区倾斜的列作为分区列。
• 常用SQL包含某列的等值或in查询条件，选择该列作为分区列。

1.2 一级分区个数

基本原理：

分析型数据库的COMPUTENODE Local/Merge计算引擎（大部分查询主要的计算引擎），会在每个分区并行 计算，每个分区计算使用一个线程，分区计算结果汇总到FRONTNODE。因此分区数过小，会导致并发低，单 查询RT时间长。而如果分区数过多，会导致计算结果数过多，增加FRONTNODE压力；同时由于分区数过大 ，更容易产生长尾效应。因此需要根据资源配置和查询特点，选择合适的分区数。

一级分区个数选择依据：

• 参快速join的多个事实表分区数必须相同
• 单分区的数据记录数建议为300万条到2000万之间
• 分区数应该大于ECU数量 X 6，
• 单表一级分区数最大值为256
• 单计算节点的分区数（包括二级分区）不能超过1万

1.3 聚集列的选择和设置

基本原理：

分析型数据库数据存储支持按一列或多列排序（先按第一列排序，第一列相同情况下，使用第二列排序），保证该列值相同或相近的数据在磁盘同一位置。它的好处是，当以聚集列为查询条件时，查询结果保存在磁盘相同位置，可以减少IO次数。由于主聚集列只有一列，因此需要最合适的列作为主聚集列。

聚集列选择依据

• 主要或大多数查询条件包括这一列，且该条件具有较高的筛选率。
• Join 等值条件列（通常为一级分区列）作为聚集列。

1.4 列类型选择

基本原理

AnalyticDB处理数值类型的性能远好于处理字符串类型。原因在于： 值类型定长，占用内存少，存储空间小； 数值类型计算更快，尤其是join时；

因此，强烈建议用户尽可能使用数值类型，减少使用字符串类型。

2、查询SQL优化

2.1单表查询

索引和扫描选择

分析型数据库默认为全索引，对于查询的多个条件分别检索索引，得出多个结果集（行集合），然后采用流式 归并算法得出满足组合条件的最终结果集。索引的性能主要受key的分布影响，包括：cardinality(散列程度 )，范围查询的记录数/表记录数。

但是在以下四种情况下，索引性能较差。

• • 范围查询（或等值查询）筛选能力差，即满足条件的记录数/表总记录超过10%。
  • 不等于条件查询（不包括not null）。
  • 中缀或后缀查询，例如 like ‘%abc’ 或like ‘%abc%’。
  • AND 条件中某一条件具有高筛选能力，其他条件走索引性能比扫描性能差。

对于以上四种情况，扫描性能反而比索引的性能好。用户通过加hint的方法强制查询不走索引。

/*+ no-index=[table1.time]*/ 
select * from table1 where x= 3 and time between 0 and 10000

表示强制条件time between 0 and 10000走扫描。计算引擎首先检索列x的索引，得出满足条件x=3的行集合 ，然后读取每行所对应的time列数据，如果满足time between 0 and 10000，则将该行数据加入返回结果。

二级分区查询优化

一级分区包含多个二级分区；计算时，每个二级分区依次执行条件查询，并将所有二级分区的结果进行汇总。 由于每个二级分区都要参与所有条件筛选（索引查询），当二级分区较多时，查询性能较差。如果能够预知数 据的分布，确定二级分区的范围，可以在查询条件中增加二级分区列条件，这样可以快速过滤无效的二级分区 ，减少搜索范围。

select * from table where id = 3 and time between ‘2016-04-01 00:00:00’ and ‘2016-04-01 12:00:00’;
-- 优化后 pid为二级分区
select * from table where id = 3 and time between ‘2016-04-01 00:00:00’ and ‘2016-04-01 12:00:00’ and pid =20160401;

条件改写

当SQL中条件为函数时，无法走索引过滤，自动走扫描。在大多数情况下，性能会比较差，因此尽量改写条件 去除函数。例如以下SQL：

Select * from table where year（date_test） > 1990
-- 优化后
Select * from table where date_test > ‘1990-00-00’

2.2 多表查询

子查询使用

对于子查询，分析型数据库会首先执行子查询，并将子查询的结果保存在内存中，然后将该子查询作为一个逻 辑表，执行条件筛选。由于子查询没有索引，所有条件筛选走扫描。因此如果子查询结果较大时，性能比较差 ；反之当子查询结果集较小时，扫描性能反而超过索引查询。

对于join查询，由于分析型数据库默认采用hash join算法，如果其中一张表结果集（条件筛选后）较大时，扫 描性能会比索引差很多，因此尽量不要采用子查询。例如以下SQL：

Select A.id from table1 A join (select table2.id from table2 where table2.y = 6) B on A.id= B.id where A.x=5
-- 当满足条件x=5 和y=6的条数较多时，应改成：
Select A.id from table1 A join table2 B on A.id = B.id where B.y = 6 and A.x=5

当结果集较大的表是实时表，应尽量采用子查询。原因在于，实时表的最新数据（基线合并后写入的数据），索引能力很弱，查询性能非常差。子查询可以减少搜索范围，性能反而更好。

2.3 小表广播/Full MPP Mode查询优化

Full MPP Mode 与COMPUTENODE Local/Merge模式选择

COMPUTENODE Local/Merge模式是分析型数据库支持的一种极速查询模式，通过对用户SQL的有效甄别 ，快速判断是否和数据分布相匹配，从而达到优化数据shuffle逻辑，下沉大量计算操作的目的。

使用Full MPP Mode一般需用户使用Hint显示指定。

用户也可通过在查询hint中指定来强制查询使用MPP或COMPUTENODE模式。典型使用COMPUTENODE Local/Merge模式的场景有：

• • 不包含表连接或者子查询的任意查询。
  • 表连接条件为等值条件且连接列均为一级分区列的任意查询。
  • 子查询包含了分区列的的groupby操作的任意查询。

小表广播

小表广播是基于COMPUTENODE Local/Merge模式扩展出的一种支持子查询内部不包含分区列的join操作的 查询模式。典型的使用场景有：

• 非分区列的连接

select name from student1 a join student2 b on a.name = b.name
-- 改写成
select name from student1 a join (select /*+broadcast=true*/ name from student2) b on a.id = b.id

• • 子查询内不包含分区列

select name from student1 where name in (select name from student2 where id = 10)
-- 改写成
select name from student1 where name in (select /*+broadcast=true*/ name from student2 where id = 10)