大家好,又見面了,我是你們的朋友全棧

AnalyticDB分析型數據庫

• 知識點結構圖

• 本文初衷是為了學習歸納,若有錯誤,請指出。

修改記錄

一、概述

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分區)的分區數建議一致。
  • ?戶在創建事實表(實時表、批量表)前,必須先創建表組,創建表組的語法如下:

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 創建事實表組

CREATE TABLEGROUP ads_demo--表組名options(minRedundancy=2 executeTimeout=30001;--設置選項:副本數、超時時間

4.2.2 修改事實表組

? 事實表組創建完成后,后續可根據實際需要修改事實表組的最小副本數和超時時間,但不支持修改表組名稱。

? 同樣支持界面修改和SQL修改。

ALTER tablegroup ads_demo minRedundancy = 4;

4.2.3 刪除事實表組

? 當想刪除某個表組時,必須要先刪除該表組下的所有表,然后才能刪除表組。

? 只支持SQL刪除。

drop TABLEGROUP ads_demo;

4.3 表操作

4.3.1 創建表

-- 示例-- 創建事實表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 joinOPTIONS (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 編輯表

? 創建表成功后,表的列、一級分區、表名、表組、更新方式均不可更改(除非重建),但您可以修改查詢超時時間、聚集列、注釋,并且可以新增列。

-- 增加列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,則會取消該列的索引;創建表后,不支持修改索引。

-- 取消索引示例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 數據類型,語法示例如下:

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

? 創建表時,您可通過 jsonIndexAttrs ‘’ 語法指定要為JSON 中的哪些屬性構建索引。如果不帶 jsonIndexAttrs ‘<attributes to be indexed>’ ,則表示對 JSON 的所有屬性都構建索引。注意,這里是說的對Json字段里面細分的哪些屬性。

• 如果通過jsonIndexAttrs ”只為部分屬性構建了索引,則其他未構建索引的屬性也可以查詢,但查詢性能相對較低。
• 如果確定某些屬性不會進行WHERE檢索,則不必為這些屬性構建索引,以節省索引所占的磁盤空間。
• 總結:不構建Json索引,則默認對Json內所有屬性都構建索引;如果只對Json某些屬性構建了索引,那么只有這些屬性是有索引的,其他也不會再默認構建索引。 對Json字段構建部分索引:舉例需要對 name, company.company_address 屬性構建索引,則創建表時指定子句

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 16TABLEGROUP ads_demoOPTIONS (UPDATETYPE = 'realtime')COMMENT '';

? 插入數據示例:

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

? 查詢數據:

• 查詢JSON數據是通過 json_extract(<列名>, ‘’) 語法進行的

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

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

• 單個CacheTable的行數限制:
  • 創建Cache Table時,如果 select 查詢結果超過300000行,則返回錯誤碼為18066的消息
  • 創建 Cache Table 時,select 查詢不可通過 LIMIT 子句來限制查詢結果集的大小。需要改用where條件來限制行數
• 查詢CacheTable:查詢 Cache Table 數據時,需要帶上 cache_id Hint 信息,在用上hint形式

/* +cache_id = 1683065103.38806.6.0.082539 */select * FROM cache.test_cache_table_1;

• 刪除CacheTable,也是必須帶上cache_id

/* +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數據時,數據庫性能處于最佳狀態?，F場實際使用時,建議根據表行長來確定一次提交的記錄數 N ,N = 16KB/rowsize。

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_nameSELECT col1, ... FROM db_name.source_table_nameWHERE ...;

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 均可

/*+engine=COMPUTENODE*/insert INTO db_name.target_table_name (col1, col2, col3)select col1, col2, col3 FROM db_name.source_table_nameWHERE col4 = 'xxx';

• **MPP 引擎模式:**select 部分的查詢走 MPP 模式,數據批量返回到 FRONTNODE 節點,并以批量發起實時數據 insert,默認每批的記錄數為100條。MPP 引擎模式需要指定/+engine=MPP/ Hint。

/*+engine=MPP*/insert INTO db_name.target_table_name (col1, col2, col3)select col1, col2, col3 FROM db_name.source_table_nameWHERE 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。

/*+engine=MPP, mppNativeInsertFromSelect=true*/INSERTINTO db_name.target_table_name (col1, col2, col3)select col1, col2, col3 FROM db_name.source_table_nameWHERE col4 ='xxx';

• 三者之間的區別:暫時還不知道,后面再補充。

4.4.4 insert FROM select 之異步化執行

? 當通過 insert FROM select 語句插入大量數據(1000萬條以上的記錄)時,您需要進行長時間的等待,此時您可通過 run_async=true Hint 來進行異步化執行。

? 進入異步化執行的語句后,可以通過查詢元數據表 information_schema.async_task 來查看三天內異步化任務的執行狀態,STATUS字段為SUCCESS執行成功,如下:

-- 加run_async=true hint來進入異步化執行-- sql執行后會返回異步化執行的ID/*+run_async=true, engine=mpp, mppNativeInsertFromSelect=true*/insert INTO tpch_junlan.insert_from_select_testSELECT * FROM lineitem;--返回:+-----------------------------------+| ASYNC_TASK_ID |+-----------------------------------+| xxxxx_19010_1501141772740 |+-----------------------------------+select * FROM information_schema.async_taskWHERE 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 | InsertFrom 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的計算性能和并發能力都比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 任務等

-- 查詢用戶的數據庫列表-- 指定 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():

odps@ garudadc>select __aid, count(*) as countfrom dmj_ex_1.allcase_actiongroup 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,否則要進行第二步

select count(distinct 一級分區列) from tab

對一級分區鍵group by統計分區的數據總數來檢查數據是否分布均勻

select 一級分區, count(*) from tab group by 一級分區列 order by count(*) desc

檢查是否有空值(’’),并查詢空值的數據量

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

七、常見SQL優化細節

– 待補充

版權聲明:本文內容由互聯網用戶自發貢獻,該文觀點僅代表作者本人。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。如發現本站有涉嫌侵權/違法違規的內容, 請發送郵件至 舉報,一經查實,本站將立刻刪除。

192438.html