AnalyticDB_分布式分析型数据库

大家好，又见面了，我是你们的朋友全栈

AnalyticDB分析型数据库

知识点结构图

本文初衷是为了学习归纳，若有错误，请指出。

修改记录

时间	内容
2020年9月13日	第一次发布

一、概述

1.1 定义

分析型数据库AnalyticDB（原名 ADS）是阿里巴巴针对海量数据分析自主研发的实时高并发在线分析系统，可以针对万亿级别的数据进行多维度分析透视和业务探索。采用分布式计算，具有强大的实时计算能力。

1.2 特点

主要特点就是实时和高并发，可以针对万亿级别的数据进行多纬度分析透视和业务探索。

兼容MySQL、BI工具和ETL工具，可以高效轻松地分析和集成数据。
采用分布式计算，具有强大的实时计算能力。
能够支撑较高并发查询量，同时通过动态的多副本数据存储计算技术也保证了较高的系统可用性

1.3 数据类型

AnalyticDB数据库支持多种列数据类型，如下：

二、架构原理

– 待补充

三、基本数据库对象及概念

ADS逻辑存储对象包括：数据库、表组、表，其中表分为维度表和事实表。

3.1 数据库

数据库，database 或 schema，在ADS中是最⾼层的对象，按数据库进⾏资源的分配、隔离和管理，实现了多租户的管理能⼒。

在分析型数据库中，数据库是⽤户和系统管理员的管理职权的分界点
分析型数据库是以数据库为粒度对⽤户的宏观资源进⾏配置，因此创建数据库时⽤户需要输⼊资源数⽬和资源类别来进⾏的资源分配。
分析型数据库的⽤户不能直接通过CREATE DATABASE的DDL语句创建数据库，只能通过DMS控制台界⾯来创建需要的业务数据库.
在分析型数据库中，⼀个数据库对应⼀个⽤于访问的域名URL和端⼝号，同时有且只有⼀个owner即数据库的创建者，如果数据库重建了，即便用回原来的数据库名，但URL和端口号还是要重新分配才能使用数据库。
由于AnalyticDB多租户的特点，即按数据库进⾏资源隔离、数据访问控制，不⽀持跨数据库的访问，也就是不能跨数据库查表，如果确实要用到不同库的两张表，可以考虑⼀个表多个库冗余设计和存储（即多建一张表存在另一个库中）

3.2 表组

表组（table group）是⼀系列数据表的集合，通常将同⼀业务下的表归属到⼀个表组，便于表的分类和管理。

就是统一业务类型的表集合。

在ADS的LM引擎下，还要求Join的两张表是同一表组，而MMP引擎则没有这个要求。

分析型数据库中表组分为两类：维度表组和事实表组。

一个数据库可以创建多个表组。

维度表组：维度表组是维度表（一种数据量较小，但能和任何表进行关联的表）的集合，由系统自动创建，在数据库中是唯一的，不可修改和删除，维度表组名称为：数据库_dimension_group，⽤户不可修改和删除。维度表特征上是⼀种数据量较⼩但是需要和任何表进⾏关联的表，创建维度表时不需要指定表组信息，⾃动归属到维度表组下。
事实表组：
- 事实表组是数据物理分配的最小单元，是事实表的集合，必须由用户自己来创建
- 一个事实表组最大支持创建256个事实表。
- 数据库中数据的副本数必须在表组上进行设定，同一个表组的所有表的副本数一致。
- 只有同一个表组的表才支持快速HASH JOIN。
- 同一个表组内的表可以共享一些配置项（例如：查询超时时间）。表组级别的配置会覆盖单表的个性化配置。
- 同一个表组的所有表的一级分区（即HASH分区）的分区数建议一致。
- ⽤户在创建事实表（实时表、批量表）前，必须先创建表组，创建表组的语法如下：

代码语言：javascript复制

CREATE TABLEGROUP [db_name.]tablegroup_name;

3.3 表

表按数据仓库模型分为：

事实表：⼜称普通表，存放数据量较⼤的表；事实表用于存储大量的事实数据（例如：销售数据）。一个数据库中通常有多个事实表，事实表通常会关联多个维度表。
- 事实表具有以下特点：
  - 事实表支持两级分区，一级分区为HASH分区，二级分区为LIST分区。事实表至少需要指定一级分区，如果后续每天（或固定一段时间）均有增量数据，则可以指定二级分区。
  - 事实表支持对若干列的数据进行聚集（聚集列），以实现高性能查询优化。
  - 事实表单表最大支持1024个列，可支持数千亿行甚至更多的数据。
  - 一个事实表通常关联多个维度表。
维度表：⼜称复制表，即表的数据将复制到每个计算节点上。维度表是分析事实表的数据的窗口，其数据用于描述事实表的数据。一个数据库中通常有多个维度表。通过维度表，您可以从不同角度来分析事实表数据，
- 维度表具有以下特点：
  - 维度表不需配置分区信息，但会消耗更多的存储资源，单表数据量大小受限。
  - 维度表最大可支持千万级的数据条数，可以和任意表组的任意表进行关联。
表按更新模式分为：
实时更新表（realtime）：⽀持insert和delete，⾯向实时更新场景，适合从业务系统直接写入数据；
- 注意：实时更新表至少设置一列为主键；
批量更新表（batch）：批量更新，适合将离线系统（如MaxCompute）产生的数据批量导入到分析型数据库，供在线系统使用。它不⽀持insert/delete，用类似于Insert OverWrite语法
- 注意：批量表没有主键，不支持设置；批量更新表，列的数据类型必须与数据源对应列的数据类型一致或可互相转化。

3.4 分区

ADS⽀持2级分区策略，将表数据分布到不同的节点，⼀级分区采⽤hash算法，⼆级分区采⽤list 算法。

一级分区方式大致和Hive的HashPartitioner一样。

如下图所示，事实表按ID进⾏⼀级分区，通过CRC32算法将不同ID值分布到不同的节点。⼆级分区采⽤按⽇期（bigint类型）进⾏分区–每天⼀个⼆级分区。

3.4.1 Hash分区

HASH分区是事实表的一级分区，说明如下：

根据导入操作时已有的一列内容进行散列求模后进行分区。
一个普通表至少有一级HASH分区，默认为100个，分区数最小支持8个，最大支持256个。
多张普通表进行快速 HASH JOIN ，JOIN KEY必须包含分区列，并且这些表的HASH分区数必一致。
数据装载时，仅包含HASH分区的数据表会全量覆盖历史数据。
每个分区的数据建议不超过1500万条，您可通过划分二级分区来扩大表的数据存储量。

3.4.2 List分区

LIST分区是事实表的二级分区，说明如下：

根据导入操作时所填写的分区列值来进行分区。同一次导入的数据会进入同一个LIST分区，因此LIST分区支持增量的数据导入。比如日分区
一个普通表默认最大支持365*3个二级分区，默认为100个。

3.5 索引

AnalyticDB为解决⼤数据索引的问题，采⽤默认模式预先为所有列创建索引，可以在明确表的某⼀列不需要索引情况，可以显式的disable index。（牛逼。。。）

AnalyticDB为每个分区⾃动创建了下列索引：

倒排索引：分区表的所有列（适⽤Bitmap索引的列除外）都建了倒排索引，key为排序的列值，value为对应的RowID list，所以对于任何列进⾏FILTER(WHERE key=value)或者JOIN查询都⾮常⾼效。同时索引采⽤pForDelta压缩，拥有⾼压缩⽐(1:4~1:32)和解压速度(1GB/s)。
Bitmap索引：对于值重复率⾼的列，建⽴Bitmap索引，如上图中的gender列。
区间树索引：为了加速范围查询，对于类型为数字的列同时建⽴了区间树索引。

对于倒排索引的解释参考：https://blog.csdn.net/starzhou/article/details/87519973

3.6 主键

AnalyticDB的realtime类型的表必须包含主键字段，AnalyticDB⽀持realtime表insert／delete，通过主键进⾏相同记录的判断，确定唯⼀记录。

像前面介绍表，实时表可以通过delete、insert的方式更新数据，而在插入数据时就要根据主键来判断唯一值。

主键组成：(业务id ⼀级分区键⼆级分区键)，有些情况，业务id与⼀级分区相同。对于记录量特别⼤的表，从存储空间和insert性能考虑，⼀定要减少主键的字段数。在之前的公司，有用多个列的MD5值来作为主键的。

注意：

AnalyticDB中的主键只是⽤来做记录唯⼀性判断，主键的简单性有利于insert/delete的性能。
和其他数据库的主键特性不同，ADS⽆需考虑主键对查询SQL的性能，它一般只为了判断唯一值。
主键不⽀持修改，如需要修改主键，必须重建表。

3.7 聚集列

ADS支持将一列或多列进行排序，保证该列值相同或相近的数据存储在磁盘同一位置，这样的列叫做聚集列。

它的好处是，当以聚集列为查询条件时，查询结果保存在磁盘相同位置，可以减少IO次数，提高查询性能。

由于主聚集列只有⼀列，因此需要最合适的列作为主聚集列，聚集列的选择如下：

该列是大多数查询条件会用到了，具有较高的筛选率
Join 等值条件列（通常为⼀级分区列）作为聚集列。

3.8 数据块大小

基本原理: AnalyticDB数据按列存储，对每列按固定记录数切块，作为IO的基本单位。如果数据块太⼤，容易导致单块有效数据量⽐例较⼩，增加单次IO latency；反之如果数据块太⼩，会增加IO次数，影响查询性能。

配置建议: 需要根据业务本身查询特点，⽤户选择合适的块⼤⼩。对于包含聚集列（单块中有多条有效数据）或者内存资源较为充⾜情况下，适合采⽤较⼤的块⼤⼩（超过或等于32760）；反之如果没有聚集列，同时查询结果的列个数⼜特别多时，建议设置较⼩的块⼤⼩。⽬前AnalyticDB默认块⼤⼩为32760。

注意：修改块⼤⼩只对新导⼊（或基线合并）的数据有效，对历史数据⽆效。

3.9 多值列

– 待补充

多值列是AnalyticDB特有的数据类型

暂时参考《阿里云专有云企业版 V3.7.1 分析型数据库用户指南 20190124》 – 6.2.2

四、SQL操作

4.1 数据库操作

AnalyticDB 不支持通过 SQL 语句来创建数据库实例，建议通过 Apsara Stack 控制台来创建数据库实例。
通过 AnalyticDB 控制台，可动态增加或减少 AnalyticDB 数据库实例的 ECU 数量，以达到扩容和缩容的目的。扩容和缩容均不会影响当前业务。
AnalyticDB 不支持通过 SQL 语句来删除数据库实例，建议您通过 Apsara Stack 控制台来删除数据库实例。

4.2 表组操作

AnalyticDB支持通过界面操作或SQL操作来创建事实表组。

在AnalyticDB数据库中，您只可以创建事实表组，维度表组是唯一的，在创建数据库时自动生成，命名为数据库名*_dimension_group*。

4.2.1 创建事实表组

代码语言：javascript复制

CREATE TABLEGROUP ads_demo		--表组名
options(minRedundancy=2 executeTimeout=30001;		--设置选项：副本数、超时时间

4.2.2 修改事实表组

事实表组创建完成后，后续可根据实际需要修改事实表组的最小副本数和超时时间，但不支持修改表组名称。

同样支持界面修改和SQL修改。

代码语言：javascript复制

ALTER tablegroup ads_demo minRedundancy = 4;

4.2.3 删除事实表组

当想删除某个表组时，必须要先删除该表组下的所有表，然后才能删除表组。

只支持SQL删除。

代码语言：javascript复制

DROP TABLEGROUP ads_demo;

4.3 表操作

4.3.1 创建表

代码语言：javascript复制

-- 示例
-- 创建事实表
CREATE TABLE t_fact_orders
(
order_id varchar COMMENT '',
customer_id varchar COMMENT '',
goods_id bigint COMMENT '',
numbers bigint COMMENT '',
total_price double COMMENT '', 
order_time timestamp COMMENT '',
order_date bigint COMMENT '',
PRIMARY KEY (order_id, customer_id, order_date) )
PARTITION BY HASH KEY (customer_id) PARTITION NUM 128		--一级分区   分区数
SUBPARTITION BY LIST KEY (order_date)		--二级分区   二级分区最大分区数
SUBPARTITION OPTIONS (available_partition_num = 90)
--[CLUSTERED BY (col3,col4)] --CLUSTERED BY ⼦句⽤于指定聚集列
TABLEGROUP ads_demo		--指定表组，同一表组的表才能hash join
OPTIONS (UPDATETYPE = 'realtime')		--创建一张实时更新表，带主键，如果 updateType选项不填则默 认为批量更新表
COMMENT '';
-- 创建维度表：
CREATE DIMENSION TABLE t_dim_goods (
goods_id bigint comment '',
price double comment '',
class bigint comment '',
name varchar comment '',
update_time timestamp comment '',
primary key (goods_id)
) 
OPTIONS (UPDATETYPE = 'realtime');		--维度表比较简单，用dimension指定就可以到维度表组

4.3.2 编辑表

创建表成功后，表的列、一级分区、表名、表组、更新方式均不可更改（除非重建），但您可以修改查询超时时间、聚集列、注释，并且可以新增列。

代码语言：javascript复制

-- 增加列
ALTER TABLE t_fact_orders ADD new_col varchar;
-- 二级分区数是可以修改的，最⼤⼆级分区数⽬前可以在建表后进⾏在线修改
ALTER TABLE [db_name.[table_name subpartition_available_partition_num = N;
-- 删除表
DROP TABLE tab_01;
-- 查看表中字段的顺序
SHOW CREATETABLE db_name.table_name;

4.3.3 disableIndex true取消索引

ADS默认为所有列创建index，同时可以⽀持选择性取消列的索引。

什么时候该选择取消索引，参考原则：

1. 只会出现在select⼦句中，不会在where⼦句中使⽤情况
1. 列的值重复⽐较⾼，如值基本都是相同的值
创建表时指定某列为 disableIndex true，则会取消该列的索引；创建表后，不支持修改索引。

代码语言：javascript复制

-- 取消索引示例
CREATE TABLE t_fact_orders
(
order_id varchar COMMENT '',
customer_id varchar COMMENT '',
goods_id bigint COMMENT '',
numbers bigint disableIndex true COMMENT '',
total_price double disableIndex true COMMENT '',
...

4.3.4 Json支持

AnalyticDB 支持 JSON 数据类型和 JSON 索引。

创建表时，您可以指定列为 JSON 数据类型，语法示例如下：

代码语言：javascript复制

CREATE TABLE t_fact_json (
id int COMMENT '',
data json, 
PRIMARY KEY (id) )
PARTITION BY HASH KEY (id) PARTITION NUM 16
TABLEGROUP ads_demo
OPTIONS (UPDATETYPE='realtime')
COMMENT '';

创建表时，您可通过 jsonIndexAttrs ‘’ 语法指定要为JSON 中的哪些属性构建索引。如果不带 jsonIndexAttrs ‘<attributes to be indexed>’ ，则表示对 JSON 的所有属性都构建索引。注意，这里是说的对Json字段里面细分的哪些属性。

如果通过jsonIndexAttrs ”只为部分属性构建了索引，则其他未构建索引的属性也可以查询，但查询性能相对较低。
如果确定某些属性不会进行WHERE检索，则不必为这些属性构建索引，以节省索引所占的磁盘空间。
总结：不构建Json索引，则默认对Json内所有属性都构建索引；如果只对Json某些属性构建了索引，那么只有这些属性是有索引的，其他也不会再默认构建索引。对Json字段构建部分索引：举例需要对 name, company.company_address 属性构建索引，则创建表时指定子句

代码语言：javascript复制

CREATE TABLE t_fact_json (
id int COMMENT '',
data json jsonIndexAttrs '$.name, $.company.company_address' comment '',	--这里对json构建索引
PRIMARY KEY (id) )
PARTITION BY HASH KEY (id) PARTITION NUM 16
TABLEGROUP ads_demo
OPTIONS (UPDATETYPE = 'realtime')
COMMENT '';

插入数据示例：

代码语言：javascript复制

insert into t_fact_json (id, data) values(0, '{"id":0,"name":"tjy", "age":0}');

查询数据：

查询JSON数据是通过 json_extract(<列名>, ‘’) 语法进行的

代码语言：javascript复制

select * from t_fact_json where json_extract(data, '$.company') = 'alibaba';

4.3.5 Cache Table

AnalyticDB 会在 FRONTNODE 节点构建本地 local 的内置数据库引擎，内置数据库引擎存储一定量的本地数据缓存表（Cache Table），以便您快速对本地单表进行查询。

基于Cache table，您可进行一定范围内的高效的分页数据查询。但 Cache Table 只能作为临时存储，不能作为永久性存储。

要创建Cache Table，后面必须跟一个select，并且创建成功后会给一个cache_id

代码语言：javascript复制

CREATE TABLE cache.table_name OPTIONS(cache=true)
AS
SELECT * FROM table_name;

单个CacheTable的行数限制：
- 创建Cache Table时，如果 SELECT 查询结果超过300000行，则返回错误码为18066的消息
- 创建 Cache Table 时，SELECT 查询不可通过 LIMIT 子句来限制查询结果集的大小。需要改用where条件来限制行数
查询CacheTable：查询 Cache Table 数据时，需要带上 cache_id Hint 信息，在用上hint形式

代码语言：javascript复制

/*  cache_id = 1683065103.38806.6.0.082539 */
SELECT * FROM cache.test_cache_table_1;

删除CacheTable，也是必须带上cache_id

代码语言：javascript复制

/*  cache_id = 1683065103.38806.6.0.082539 */
DROP TABLE cache.test_cache_table_1;

4.4 数据操作

在 AnalyticDB 中，只有实时更新表（realtime）支持 DML 语言，批量更新表（batch）不支持。实时更新表支持的 DML 语句包括：INSERT 和 DELETE。

4.4.1 插入数据

可以用Insert插入实时更新表，插入后有延迟，约一分钟后能查到数据。

一次提交16KB数据时，数据库性能处于最佳状态。现场实际使用时，建议根据表行长来确定一次提交的记录数 N ，N = 16KB/rowsize。

代码语言：javascript复制

INSERT INTO table_name [ ( column [, ... ] ) ] VALUES [(),()]
INSERT INTO db_name.target_table_name [ ( column [, ... ] ) ]
SELECT col1, ... FROM db_name.source_table_name where ...;
-- 或者能保证字段顺序下：
INSERT INTO table_name(co1,col2,col3,...) VALUES(?,?,?,...)
INSERT INTO db_name.target_table_name
SELECT col1, ... FROM db_name.source_table_name
WHERE ...;

4.4.2 INSERT IGNORE

INSERT和INSERT IGNORE的区别如下：

INSERT：主键覆盖，即如果当前插入的记录与数据库中已有的记录主键相同，则覆盖已有记录。
INSERT IGNORE：如果当前插入的记录与数据库中已有的记录主键相同，则丢弃正在插入的新记录，保留已有记录。

在实际应用中，您可根据业务应用的需求来选择 INSERT 或 INSERT IGNORE 语句。

4.4.3 INSERT FROM SELECT 之多引擎模式执行

INSERT FROM SELECT 语句支持在LM（Local-Merge）、 MPP 和Native MPP三种引擎模式执行。

**LM 引擎模式：**SELECT 部分的查询走 FRONTNODE COMPUTENODE 的 LM 模式。LM 引擎模式具有最好的执行性能，但 SELECT 的查询部分不会做最终的数据聚合，所以您需要考虑查询是否满足 LM 计算引擎的要求，如果不否满足，则写入的数据不保证整体语义的正确性、完整性。LM是默认执行引擎，指定或不指定/ engine=COMPUTENODE/ Hint 均可

代码语言：javascript复制

/* engine=COMPUTENODE*/
INSERT INTO db_name.target_table_name (col1, col2, col3)
SELECT col1, col2, col3 FROM db_name.source_table_name
WHERE col4 = 'xxx';

**MPP 引擎模式：**SELECT 部分的查询走 MPP 模式，数据批量返回到 FRONTNODE 节点，并以批量发起实时数据 INSERT，默认每批的记录数为100条。MPP 引擎模式需要指定/ engine=MPP/ Hint。

代码语言：javascript复制

/* engine=MPP*/
INSERT INTO db_name.target_table_name (col1, col2, col3)
SELECT col1, col2, col3 FROM db_name.source_table_name
WHERE col4 = 'xxx';

**Native MPP 引擎模式：**在 LM 和 MPP 模式中，所有数据均由 FRONTNODE 节点单点写入，并发度受限。在 Native MPP 引擎模式中，INSERT FROM SELECT 语句的数据写入节点直接由多个worker节点并发完成，每批的记录数为100条。相对 LM 和 MPP 引擎模式，Native MPP提高了数据写入的并发度。Native MPP引擎模式需要指定 / engine=MPP, mppNativeInsertFromSelect=true/ Hint。

代码语言：javascript复制

/* engine=MPP, mppNativeInsertFromSelect=true*/
INSERTINTO db_name.target_table_name (col1, col2, col3)
SELECT col1, col2, col3 FROM db_name.source_table_name
WHERE col4 ='xxx';

三者之间的区别：暂时还不知道，后面再补充。

4.4.4 INSERT FROM SELECT 之异步化执行

当通过 INSERT FROM SELECT 语句插入大量数据（1000万条以上的记录）时，您需要进行长时间的等待，此时您可通过 run_async=true Hint 来进行异步化执行。

进入异步化执行的语句后，可以通过查询元数据表 information_schema.async_task 来查看三天内异步化任务的执行状态，STATUS字段为SUCCESS执行成功，如下：

代码语言：javascript复制

-- 加run_async=true hint来进入异步化执行
-- sql执行后会返回异步化执行的ID
/* run_async=true, engine=mpp, mppNativeInsertFromSelect=true*/
INSERT INTO tpch_junlan.insert_from_select_test
SELECT * FROM lineitem;
--返回：
 ----------------------------------- 
| ASYNC_TASK_ID |
 ----------------------------------- 
| xxxxx_19010_1501141772740 |
 ----------------------------------- 
SELECT * FROM information_schema.async_task
WHERE id = 'xxxxx_19010_1501141772740';
--返回：
 ------------ ----------------------- ----------------------- 
| CLUSTER_NAME | TABLE_SCHEMA | ID | 
TASK_NAME | STATUS | MESSAGE 
| PROCESS_ID | COMMAND 
| CREATOR_ID | CREATE_TIME
| UPDATE_TIME |
 -------------- -------------- -----------------------------------
| dailybuild | tpch_junlan | xxxxx_19010_1501141772740 | Insert
From Select | SUCCESS | task has been processed successfully. | 
2017072715493210008113606009999000098 | insert into tpch_junlan.
insert_from_select_test select * from lineitem | $ | 2017-07-
27 15:49:33.0 | 2017-07-27 15:50:02.0 |
 -------------- -------------- -----------------------------------

4.4.5 删除表数据

实时更新表可以delete表中的部分数据，但批量更新表是不可以删除数据的，只能整表drop掉。

注意：

如果实时表包含二级分区，则 WHERE 子句必须包含二级分区条件
删除全表数据（不包含二级分区，请谨慎使用）时，如果表数据量非常大（记录数超过10万条），则可能导致性能严重下降。
如果要删除表的全部数据，且表的数据量非常大（记录数超过10万条），则强烈建议通过删表重建方式来完成，即先删除表，然后再重建一张新表。
如果需要删除表的历史数据，则可以合理使用二级分区机制来快速自动删除单个二级分区的数据，以达到删除最老历史数据的目的。
- 这里主要是说建表时，可以指定二级分区的最大分区数，在系统中会对二级分区的所有分区排序，并且如果分区数超过了设置的最大分区数，那么系统会自动删除分区数最小的数据，从而达到删除历史数据的目的。

4.5 Select操作

4.5.1 计算引擎

AnalyticDB目前拥有 COMPUTENODE Local-Merge（简称LM)）和 Full MPP Mode（简称MPP）两套计算引擎，两种计算引擎在 SELECT 查询时各有优缺点。同时，您还可通过Hint强制指定计算引擎。

LM是ADS默认的引擎，MMP是新增的引擎，两者区别如下：

对比项	LM	MPP
优缺点	计算性能很好、并发能力强，但对部分跨一级分区列的计算支持差。	计算功能全面、支持跨一级分区列的计算，但查询响应时间和并发能力不如 LM 。
Hint写法	/* engine = COMPUTENODE */	/* engine = MPP */

个人理解两者的区别就是LM的计算性能和并发能力都比MMP强，但是在对一些复杂的查询或者跨一级分区列的查询不支持，比如数学函数、窗口函数、Group by仅非分区列等这种LM无法做到；

而MPP虽然计算性能和并发没有LM那么好，但具备LM所不支持的计算功能，这两者应该是互补关系。

LM 计算引擎下，表关联的充要条件（四原则）如下：
- 两个表均为事实表且在同一个表组，或两个表中有一个是维度表。
- 两个表均为事实表且拥有相同的一级分区列，或两个表中有一个是维度表。
- 两个表均为事实表且关联条件（ON）中至少含有一个条件是两个表各自的分区列的等值关联条件，或两个表中有一个是维度表。关联条件（ON）中的条件两端包含有效的HashMap索引。
MPP计算引擎下，表关联加速运行的条件如下：
- 两个表均为事实表且在同一个表组，或两个表中有一个是维度表。
- 两个表均为事实表且拥有相同的一级分区列，或两个表中有一个是维度表。
- 两个表均为事实表且关联条件（ON）中至少含有一个条件是两个表各自的分区列的等值关联条件，或两个表中有一个是维度表。
MPP计算引擎注意事项如下：
- MPP 的查询响应时间和并发能力不如 LM 模式，通常适用于交互式 BI 场景、实时 ETL 场景。建议只在进行低频调用、性能敏感度低、必须使用 MPP 等查询时使用 MPP 模式。
- MPP 拥有较丰富的数学函数、字符串处理函数、窗口函数等支持。
- AnalyticDB 支持自动对查询进行路由，当自动路由功能开启（默认关闭）且 LM 不支持某个查询时，则会自动路由到 MPP，以兼顾性能和通用性。比如以下几种情况开启后会自动改为MMP模式：
  - 特定函数，LM 无法识别，捕获异常，例如：row_number over等。
  - 事实表 JOIN 事实表，JOIN KEY 全部在非分区列上。
  - 不同表组的事实表 JOIN 事实表。
  - 维度表在前，LEFT JOIN 事实表。
  - 事实表 RIGHT JOIN 维度表（同上）。
  - 事实表 JOIN 事实表，一级分区数不同。
  - GROUP BY 仅含非分区列，外层套子查询
  - GROUP BY 仅含非分区列，带 ORDER BY。
  - GROUP BY 仅含非分区列，带 HAVING。
  - UNION、INTERSECT、MINUS不含分区列。
  - SELECT 表达式复杂，例如：SUM/SUM，以及任何带聚合函数的计算表达式等
  - COUNT DISTINCT 或 DISTINCT 非分区列。

4.5.2 select不支持的操作

不支持：
- SELECT a, b, a …：重复列。
MPP也不支持：
- SELECT a COUNT(*) FROM A：普通表达式（）不能同时套普通表达式（a）和聚合表达式（COUNT(…)）
  - SELECT SUM(COUNT(*))：聚合表达式不能套聚合表达式做为其子表达式。
不支持的JOIN：
- SELECT … FROM A RIGHT JOIN B：不支持右连接，需要转换为左连接。（这点和上面的right join自动转为MMP模式相矛盾，应该说的只是LM不支持。）
- SELECT … FROM A SEMI JON B：不支持半连接。
- SELECT … FROM A, B：单表或多表连接，但没有ON条件。
- SELECT … FROM A, B WHERE A.a = B.b：单表或多表连接在WHERE子句中有隐含ON条件，但是没有ON子句的，暂不支持

4.5.3 Intersect、Union、Minus

交集：Intersect & Intersect distinct（交集后去重）：返回两个查询结果的交集

并集：Union All & Union

差集：Minus ：（返回仅存在于左查询结果集而不在右查询结果集的数据行）

MPP模式下的差集是用Except

4.5.4 MPP引擎下支持的selelct注意点

MPP引擎下可以用with as语法，使用 WITH 子句定义一个子查询后，SELECT 只需要执行一遍这个子查询即可，这样会明显提高查询性能。
支持各种outer join。
MPP 支持在复杂的聚合函数中使用 GROUPING SETS、CUBE 和 ROLLUP 语法
MPP的INTERSECT 优先级高于 EXCEPT 和 UNION
支持使用TABLESAMPLE，用于从现有的表中随机抽取一些样本数据，抽样方法有 BERNOULLI 和 SYSTEM 两种。
支持UNNEST 子句用于展开数组类型或 map 类型的子查询

4.6 Show操作

SHOW 语句，您可以查询用户的数据库、表组、表信息，查询表的列信息，查询表的 DDL 建表语句，以及查询正在运行的 MPP 任务等

代码语言：javascript复制

-- 查询用户的数据库列表
-- 指定 EXTRA 参数，输出关于数据库的更多信息
SHOW DATABASES [LIKE 'name_pattern'] [EXTRA];
-- 查询用户当前数据库下的表组列表
SHOW TABLEGROUPS;
SHOW TABLEGROUPS IN ads_demo;
-- 查询用户当前数据库（或表组）下的表的列表
SHOW TABLES [IN db_name[.tablegroup_name]]
-- 查询表的列信息
SHOW COLUMNS IN table_name;
-- 查询表的 DDL 建表语句。
SHOW CREATE TABLE [db_name.]table_name;
-- 查询当前正在运行的 MPP 任务
-- 如果指定 /* cross-frontnode=true*/ Hint，则查询当前数据库实例所有正在运行的 MPP 任 务，否则只查询当前连接的 FRONTNODE 节点实例运行的 MPP 任务。
[/* cross-frontnode=true*/] 
SHOW PROCESSLIST MPP;

4.7 SQL-Hint使用

– 待补充

五、数据同步

5.1 数据导入方式及注意事项

数据入库方式：AnalyticDB 中表的数据更新方式包括批量更新和实时更新两种，批量更新方式对应的 SQL 命令为LOAD DATA 批量导入，实时更新方式对应的 SQL 命令为 INSERT。

AnalyticDB 支持多种数据入库方式，包括但不限于以下方式：

内置支持将 MaxCompute 中的海量数据快速批量导入到 AnalyticDB。
支持通过阿里云数据集成（DataWorks）将各类数据源导入 AnalyticDB 的批量更新表或实时更新表。
支持通过阿里云数据传输（DTS）从阿里云 RDS 实时同步数据变更到 AnalyticDB。
支持标准的INSERT、DELETE 语法，可通过用户程序、Kettle等第三方工具写入 AnalyticDB 实时更新表

注意事项：

实时插入和删除数据时，AnalyticDB 不支持事务，并且仅遵循最终一致性，所以AnalyticDB 并不能作为 OLTP 系统使用。
AnalyticDB 不支持 update 语句。对于实时更新表，如果需要更新某行记录中的某些字段，您可通过 insert into 语句，并以主键覆盖的方法来实现。

5.2 数据导入-从MaxCompute导入到ADS库

在 DMS For AnalyticDB 控制台，选择菜单栏中的导入导出 > 导入。

如果 MaxCompute 的数据类型是以下类型，则必须手动改写成 AnalyticDB 支持的类型。

AnalyticDB 目标表的列名要与源表中的列名一致。MaxCompute 源表的列类型与 AnalyticDB 目标表的对应的列类型可以不一致，但二者必须能够成功转换

如果发生长尾，需要检查分区键是否合理，数据分布是否均匀，可以检查MaxCompute源表，按分区列group by并计算count（）：

代码语言：javascript复制

odps@ garudadc>select __aid, count(*) as count
from dmj_ex_1.allcase_action
group by __aid order by count desc limit 5
--返回结果：
 ------------ ------------ 
| __aid | count |
 ------------ ------------ 
| 0 | 2124978 |
| 9 | 5197 |
| 6 | 5185 |
| 1 | 5172 |
| 5 | 5097 |
 ------------ ------------

5.3 数据导入-通过DateWords建立数据同步任务到ADS库

通过大数据开发套件（DataWorks）的数据集成任务里的数据同步进行操作。

创建目标表，确保来源表有数据。
配置数据源
创建数据同步任务
配置数据同步任务
运行数据同步任务。

5.4 数据导入-通过DTS从RDS实时同步到ADS库以及通过第三方工具同步数据到ADS库

这两部分用到时参考官方文档，这里不做说明。

5.5 数据导出-从AnalyticDB导出数据到OSS

– 待补充

5.6 数据导出-从AnalyticDB导出数据到MaxCompute

– 待补充

六、表规划设计

6.1 数据存储分布策略

6.1.1 分布式逻辑存储

ADS的事实表支持二级分区策略，一级分区采用Hash算法，二级分区采用List算法，通过二级分区策略，ADS可将表数据分布到不同节点。

在ADS中，事实表的逻辑存储如下图：

如上图，事实表一级分区按id进行求hash值，然后在对分区总数m求模运算，以此来将不同id值的数据分布到不同节点。

事实表的二级分区则按日期进行分区。单个二级分区的记录数不宜太小，比如：如果每天有2000万新增数据（每个一级分区每天新增记录数：2000万/32 = 62万），则建议按周划分二级分区（每个二级分区的总记录数：62万*7天 = 434万）。如果每天有300万新增数据，则建议按月划分二级分区。

在ADS中，维度表的逻辑存储则比较简单，采用复制的方式存储在每个节点上。如下：

6.1.2 分区存储物理示意图

在 AnalyticDB 中，数据表的分区存储示意图如下：

可以把下图当做6.1.1 的补充。

图例中的数据库有 m 个 LocalNode ，LocalNode 即 AnalyticDB 的本地计算节点，又名ComupteNode（简称 CN）。
在 AnalyticDB 中，设置一级分区的目的是将数据均匀分散到多个 CN 节点当中，以便充分发挥AnalyticDB 分布式计算的能力。设置二级分区的目的是将数据分散到不同的存储文件上，与传统数据库的分区相似

6.2 表结构设计

6.2.1 一级分区的规划和设计

基本原理：AnalyticDB 的表一级分区采用 HASH 分区，可指定任意一列（不支持多列）作为分区列。HASH 分区通过标准 CRC 算法计算出 CRC 值，并将 CRC 值与分区数作模计算，得出每条记录的分区号。

在 AnalyticDB 中，调度模块会将同一个表组下所有表的相同分区分配在同一个计算节点上。因此，当多表使用分区列进行 JOIN 时，单计算节点内部直接计算，避免了跨机计算。

在ADS中，一级分区的选择依据如下（按优先级从高到低排）：

（1）如果是多个事实表（不包括维度表） JOIN，则选择参与 JOIN 的列作为分区列。如果是多列 JOIN ，则根据查询重要程度或查询性能要求（例如：某 SQL 的查询频率特别高）来选择分区列，以保证基于分区列的 JOIN 具有较好的查询性能。
（2）选择 GROUP BY 或 DISTINCT 包含的列作为分区列。
（3）选择值分布均匀的列作为分区列，请勿选择分区倾斜的列作为分区列。这一点对表数据group by一下就知道
（4）如果常用的 SQL 包含某列的经常用于=或 IN 查询条件，则选择该列作为分区列。 一级分区键数据倾斜规避：
数据倾斜会给 AnalyticDB 带来诸多问题，例如：SQL查询长尾、后台数据上线超时、单节点资源不足等
理想情况下，一般选择既符合业务访问 SQL 的要求，又能将数据均匀分布的列作为一级分区键。但实际业务中的数据很难符合理想平均分布。
评估实际数据均匀程度的方式如下：
- 可通过 select 分区列, count(*) from tabname group by 分区列; 来查询是否存在明显的数据分布倾斜。
- 无数据可统计情况下，需要与业务相关人员进行讨论并获得相关信息。如果评估倾斜的指标同时满足如下条件，则需要考虑更换分区键：
  - 一级分区列不同值的个数相比一级分区数（一般最大256）是一倍到十倍关系
  - 一级分区列中单个值的记录数超过10万。
  - 一级分区列的最多记录数是平均值的两倍以上。（呈正态分布）

一级分区个数选择：

基本原理：AnalyticDB 的 LM 计算引擎是大部分查询所使用的计算引擎，它会在每个分区并行计算，每个分区计算使用一个线程，分区计算结果汇总到 FRONTNODE 。因此，如果分区数过小，则会导致并发低、单查询 RT时间长；如果分区数过多，则会导致计算结果数过多、增加FRONTNODE压力，并且容易产生长尾效应。
注意，一级分区数不可修改。如需修改，必须删表重建，所以分区数一般是在建表前就设计好。
选择依据如下：
- 参与快速 JOIN 的所有事实表的分区数必须相同（尽量设置同一表组的分区数一致）
- 每个一级分区的数据记录数建议为300万条到2000万之间。如果有二级分区，则保证一级分区下的每个二级分区的记录数在300万条到2000万条之间。
- 一级分区数应该大于ECU数量 * 6，同时需要考虑到后续的扩容需求。例如：某数据库资源是8个 C8，则分区数需要大于8 * 6 = 48。但分区数也要小于一定的值，实际分区时，也容易出现划分一级分区过多的情况，例如：两个 ECU 的情况下，设置了128个一级分区。（具体参考实际购买的ECU数量）
- 单表一级分区数最大值为256。如果需要设置成更大，请联系技术支持人员。
- 单计算节点的分区数（包括二级分区）不能超过10000。

6.2.2 二级分区的规划和设计

一般情况下，每个一级分区下会包含多个二级分区。二级分区主要用于解决数据表需要按固定时间周期（例如：天、周、月、年）增加数据的问题，一般也是选择为按天、周、月、年这样时间特征的字段，同时二级分区还考虑了保留一定时间范围的历史数据。

二级分区采用 LIST 分区，不同值的个数即为二级分区数。

二级分区列是数据表中的一个 bigint 类型的列，通常为bigint类型的日期，如2020090310

二级分区适用场景：一般情况下，当一级分区数据量随时间增大到超过单个一级分区记录数最佳推荐值（2000万~3000万）时，需要考虑设计二级分区。二级分区可以理解为按队列方式管理分区个数，当超过最大定义数，最小值分区自动删除，循环使用空间，所以二级分区支持自动清除历史数据。

但是如果二级分区数过多，则会导致多次索引查询、性能下降，并且二级分区有自身的元数据信息，过多也会导致占用更多的内存。如果过少，则导致用户导入数据频率降低，从而影响数据实时性。

一般情况下，如果单个分区每日增量数据超过300万，则推荐按天进行二级分区；如需要存储的时间范围更长，则可按周、月进行规划。如果有二级分区，则保证一级分区下的每个二级分区的记录数在300万条到2000万条之间。另外虽然单表的最大二级分区数支持365*3个，但单表二级分区数推荐小于等于90，同时每个计算节点上总的二级分区个数不超过10 000个。

6.2.3 聚集列选择

基本原理：
- ADS支持将一列或多列进行排序，保证该列值相同或相近的数据存储在磁盘同一位置，这样的列叫做聚集列。
- 当以聚集列为查询条件时，由于查询结果保存在磁盘同一位置，所以可以减少输入/输出I/O次数，提高查询性能。
聚集列的选择：
- 该列是大多数查询条件会用到了，具有较高的筛选率
- Join 等值条件列（通常为⼀级分区列）作为聚集列。

可以通过 DMS 管理工具修改表的聚集列。实时更新表修改后，新插入（INSERT）的数据在optimize 后才会生效。

6.2.4 主键选择

在ADS中，实时更新表必须包含主键，同时数据的insert和delete操作都要根据主键来判断唯一记录。但ADS的主键构成和其他数据库有所不同，如下，可以是其他键的组合。

主键组成：业务 ID 一级分区键二级分区键。

如果表记录数特别大，从存储空间和 INSERT 性能考虑，一定要减少主键的字段数。

最主要的还是要从业务角度确保生成的主键在该表能代表唯一值。

6.2.5 列类型选择

基本原理：
- 在 AnalyticDB 中，数值类型的处理性能远高于字符串类型，其原因如下：
  - 数值类型的值定长、占用内存少、存储空间小。
  - 数值类型计算更快，尤其是 JOIN 查询时。
因此，建议在选择列的数据类型时尽可能使用数值类型，减少使用字符串类型。
在以下场景中，可以将字符串转换为数值类型：
- 包含字符前缀或后缀的字符串值（例如：E12345、E12346等），则可直接去掉前缀或将前缀映射为数字
- 某列只有少数几个字符串值（例如：国家名），则可对每个国家进行编码以使每个国家对应一个唯一数字。（可以考虑额外建一张国家名的代码表）
- 对于时间/日期类型的数据，建议尽量使用 date、timestamp 或 int 类型进行存储，避免使用varchar 类型。
- 对于地理经度、纬度，建议采用 double 数据类型
ADS支持的数据类型如下表格：

6.2.7 总结表结构设计原则

建表指导原则：

同一表组下所有事实表均采用相同的一级分区数。
选择一级分区键时，需要考虑表的关联及数据均衡分布。
需要进行关联的表均采用相同的一级分区和二级分区，分区键和分区数均一致。
根据数据存储时间范围来规划二级分区的时间间隔，需要创建一个 bigint 类型的列。
每个二级分区的数据量控制在2000万左右。
可以考虑将有较高筛选率的或者join等值连接的一级分区列作为聚集列
主键一定要是从业务角度能保证在该表唯一的，可以是业务ID 一级分区键二级分区键或求他们的MD5值。
列类型尽量符合规范，多用数值类型，少用字符类型。

6.3 数据倾斜

6.3.1 数据倾斜原因

数据倾斜即数据在数据库中的存储分布不均衡，引起数据倾斜常见原因如下：

一级分区列存在数据不均衡。例如：按省份代码进行分区，但不同省份的业务数据差异性非常大。
沿用以前系统（例如：Oracle）的分区策略。例如：按月份进行分区，数据呈正态分布集中在某一分区上。
空值过多。如果一级分区列值包含大量空值（’’），则容易导致 ‘’ 分区倾斜。
- ‘’ 和 NULL 是有区别的，对于 NULL 值 AnalyticDB 会自动根据主键的第一个非分区列进行 HASH，比如：primary key(c1,c2,c3)，c1是一级分区键，当c1为null时，会自动取c2来作为二次hashkey进行数据分布。
未经过数据仓库建模（例如：雪花、星型模型），而只是照搬交易型数据库建模。

6.3.2 数据倾斜影响

数据倾斜会给 AnalyticDB 带来存储溢出、计算长尾问题，从而导致数据库业务中断、查询超时。

具体来说：

存储溢出：在ADS中，每个计算节点均分配了相同的存储空间，数据倾斜会导致某些节点的磁盘爆满而无法再写入数据，而其他节点则有很多空间。
计算长尾：当出现数据倾斜时，每个节点的数据量相差较大，对数据量多的节点计算所需的IO次数、内存大小、CPU、网络开销都大于平均值，这将导致数据查询缓慢，查询超时等问题。

6.3.3 数据倾斜规避

在创建表前，您必须进行充分的业务数据调研和数据倾斜验证，以规避数据倾斜。

为规避数据倾斜，按一级分区列选择原则选择一级分区后，还需要注意以下事项：

调研一级分区不同值个数，一般要求不同值个数是设置的一级分区数的N倍，N要大于10，否则要进行第二步

代码语言：javascript复制

select count(distinct 一级分区列) from tab

对一级分区键group by统计分区的数据总数来检查数据是否分布均匀

代码语言：javascript复制

select 一级分区, count(*) from tab group by 一级分区列 order by count(*) desc

检查是否有空值（’’），并查询空值的数据量

代码语言：javascript复制

select count(*) from t_fact_mail_status where org_code='';

七、常见SQL优化细节

– 待补充

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/192438.html原文链接：https://javaforall.cn

数据库数据集成 sql

0 人点赞