阿里adb資料庫原理?
ADB筆記:
目標:主要用於資料分析,後端支援BI報表和資料大屏。mysql協議,學習成本低。
特有名詞:
表組,對應RDS的schema。
維度表組(系統自帶):自帶維度概念的表(例如省份表等),可以放到維度表組下
普通表組:一般會把需要關聯的普通表放在相同普通表組中,建議這個表組中的所有普通表的一級分割槽數一致,join效能會有很大提升。
維度表:共享表。
普通表:分割槽表。預設一級分割槽,可建立二級分割槽。
分割槽:普通表才有,一級分割槽採用hash演算法,單表資料量在60億以內,推薦。
主鍵:表必須包含主鍵。由業務id、一級分割槽鍵組成,有些情況業務id與一級分割槽相同。對於記錄量特別大的表,從儲存空間和insert效能考慮,一定要減少主鍵的欄位數。
資料庫建立完畢後,系統會預設建立一個維度表組,所有維度相關的表,可以放到維度表組下。
特殊欄位:timestamp timestamp AnalyticDB精確到秒,MySQL支援自定義精度
常用sql連線:
https://help。aliyun。com/document_detail/94859。html?spm=a2c4g。11186623。2。38。22c965313Zwnsd
navicat連線後,無法顯示建表語句。
輸入匯入方式:1、DTS;2、資料整合。
insert插入顯示延遲5-10S,可單獨提工單修改。
更新資料:AnalyticDB不支援update操作,可以透過主鍵覆蓋的方式進行insert操作來實現和update同等的功能。
資料匯出功能較弱,dump方式到OSS/MaxCompute
推薦許可權定義方式:https://help。aliyun。com/document_detail/95546。html?spm=a2c4g。11186623。6。578。702d620fyspxAo
索引&掃描原理
AnalyticDB內部採用列存方式,透過單列高效過濾後,可直接透過內部記錄指標掃描其他列值,減少其他列的索引查詢開銷。
子查詢修改為表關聯
普通表join普通表,儘量包含分割槽列join條件,如果不包含則,儘量透過where條件過濾掉多餘的資料。
維度表join普通表,沒有限制。
預設是全索引,建表成功後,某列刪除索引操作,需提工單解決。
二級分割槽用於刪除資料,對於“回溯表”類場景,避免手動刪除。
一級分割槽鍵選擇:
1、分佈均勻,避免資料傾斜。park_record_id?
2、建議選擇一級分割槽列的資料型別為tinyint、smallint、int、bigint或者varchar。
3、如果是多個普通表(不包括維度表)JOIN,則選擇參與JOIN的列作為分割槽列。park_record_id?park_id?
4、選擇GROUP BY或DISTINCT包含的列作為分割槽列
5、如果常用的SQL包含某列的等值或IN查詢條件,則選擇該列作為分割槽列。以下列子則選擇id作為分割槽列。
select * from table where id=123 and …;
select * from table where user in(1, 2,3);
使用場景以管理員使用為主,範圍掃描較多,park_id分割槽優勢更大。
歷史單條資料,管理員查詢較少,可忽略。
使用者單條查詢,在RDS完成。
多參考設計樣例:https://help。aliyun。com/document_detail/97587。html?spm=a2c4g。11186623。6。655。207b43c1yl28Kx
https://help。aliyun。com/document_detail/97620。html?spm=a2c4g。11186623。6。656。5ebb12f55cr9Pf
為滿足高QPS,從設計上採用大寬表、冗餘欄位,並且避免表關聯。
場景描述:全量sql,查詢頻率低,以區域統計查詢為主。
最佳實踐:區域查詢、車場查詢讀擴大,資料分佈均勻+聚集列效果。缺點:
PRIMARY KEY (park_record_id,TS)
PARTITION BY HASH KEY (park_record_id) PARTITION NUM 128
SUBPARTITION BY LIST KEY (TS)
SUBPARTITION OPTIONS (available_partition_num = 300)
CLUSTERED BY (area_id,park_id)
單個AnalyticDB最多表數 256
單個表組總表數 256
最大一級分割槽數 255
不支援儲存過程
是否支援修改表的一級分割槽數:當前不支援動態修改,只能刪表重建。