本文介紹如何設計AnalyticDB for MySQL的表結構(包括選擇表類型、分布鍵、分區鍵、主鍵和叢集索引鍵等),從而實現表效能的最佳化。
選擇表類型
AnalyticDB for MySQL支援複製表和普通表兩種類型。在選擇表類型時,需要注意如下幾點:
複製表會在叢集的每個節點儲存一份資料,因此建議複製表中的資料量不宜太大,每張複製表格儲存體的資料不超過2萬行。
普通表(即分區表)能夠充分利用分布式系統的查詢優勢,提高查詢效率。普通表可儲存的資料量較大,通常可以儲存千萬條甚至千億條資料。
選擇分布鍵
如果業務明確有增量資料匯入需求,建立普通表時可以同時指定分布鍵和分區鍵,來實現資料的增量同步處理。您可以在建立表時,通過DISTRIBUTED BY HASH(column_name,...)
指定分布鍵,按照column_name
欄位的Hash值進行分區。更多詳情,請參見CREATE TABLE。
文法
DISTRIBUTED BY HASH(column_name,...)
注意事項
儘可能選擇值分布均勻的欄位作為分布鍵,例如交易ID、裝置ID、使用者ID或者自增列作為分布鍵。
說明盡量不要選擇日期、時間和時間戳記類型的欄位作為分布鍵,寫入時容易發生傾斜影響寫入效能,且多數查詢通常是限定了日期或者時間段,如:查詢最近一天或者一個月的資料,可能會導致要查詢的資料只存在於一個節點上,無法充分利用分散式資料庫中所有節點的處理能力。日期、時間類型的欄位建議作為二級分區來考慮,具體請參見選擇分區鍵。
儘可能將需要Join的欄位作為分布鍵,可以有效減少資料Shuffle。例如,需要按照顧客維度查看歷史訂單資訊,可以選擇
customer_id
作為分布鍵。儘可能選擇高頻率出現查詢條件的欄位作為分布鍵,從而實現按分布鍵做裁剪。
每張表只能選取一個分布鍵,一個分布鍵可以包含一個欄位或者多個欄位,儘可能選取少的欄位,使得分布鍵在各種複雜查詢中更加通用。
若在建立表時,未指定分布鍵,系統會根據MySQL表是否含有主鍵進行如下處理:
如果MySQL表含有主鍵,AnalyticDB for MySQL預設將主鍵作為分布鍵。
如果MySQL表不含有主鍵,AnalyticDB for MySQL將添加一個
__adb_auto_id__
欄位作為主鍵和分布鍵。
選擇分區鍵
如果設定了分布鍵後,單個分區的資料量較大,您可以通過分區鍵在分區內進一步設定分區,以提高資料訪問的效能。您可以在建立表時,通過PARTITION BY
來定義二級分區,資料會將按照指定方式進行切分。更多詳情,請參見CREATE TABLE。
文法
使用
column_name
的值做分區,文法如下:PARTITION BY VALUE(column_name)
將
column_name
的值轉換為%Y%m%d
的日期格式(類似20210101
)做分區,文法如下:PARTITION BY VALUE(DATE_FORMAT(column_name, '%Y%m%d'))
將
column_name
的值轉換為%Y%m
的日期格式(類似202101
)做分區,文法如下:PARTITION BY VALUE(DATE_FORMAT(column_name, '%Y%m'))
將
column_name
的值轉換為%Y
的日期格式(類似2021
)做分區,文法如下:PARTITION BY VALUE(DATE_FORMAT(column_name, '%Y'))
注意事項
當資料量較大時,二級分區的選擇至關重要,如果資料量大的表中沒有二級分區或者二級分區切分不合理,將嚴重影響AnalyticDB for MySQL叢集效能。如何進行分區欄位合理性診斷,請參見分布欄位合理性診斷。
目前切分粒度只支援年、月、日或原始值。切分粒度太大或太小都會影響查詢效能和寫入效能,甚至影響AnalyticDB for MySQL叢集的穩定性。
盡量使二級分區維持靜態狀態,不建議頻繁更新二級分區,例如,如果有每天頻繁更新多個歷史二級分區情境,應考慮使用的二級分區欄位是否合理。
您可以通過
LIFECYCLE N
關鍵字實現表生命週期管理,即對分區進行排序,超出N
的分區會被過濾。重要每張表中支援的最大分區數存在上限,因此分區表中的資料無法持續保留。關於分區數量限制的詳情,請參見使用限制。
選擇主鍵
主鍵可以作為每一條記錄的唯一標識。您可以在建立表時,通過PRIMARY KEY
來定義主鍵。更多詳情,請參見CREATE TABLE。
文法
PRIMARY KEY (column_name,...)
注意事項
只有定義過主鍵的表支援資料更新操作(包括DELETE和UPDATE)。
AnalyticDB for MySQL的主鍵可以是單個欄位或多個欄位的組合。推薦使用數實值型別欄位作為主鍵,並盡量減少欄位個數,以獲得較好的表效能。
主鍵中必須包含分布鍵和分區鍵,建議將分布鍵和分區鍵放在組合主鍵的前部。
選擇叢集索引鍵
叢集索引中索引值的邏輯順序決定了表中相應行的物理順序。選擇叢集索引鍵時需要注意如下幾點:
每個表僅支援建立一個叢集索引。建立方式,請參見CREATE TABLE。
建議將查詢一定會攜帶的欄位作為叢集索引鍵。例如,每個學生在學校教務系統中,只需查看自己的期末成績,那麼可以將學生的學號ID定義為叢集索引,來保證資料的局部性,提升資料查詢效能。
叢集索引會進行全表排序,需要消耗資源(例如:CPU資源),建議您合理使用叢集索引。
樣本
建立一張customer
表,需要滿足如下要求:
根據顧客的登入時間(即
login_time
列)進行資料分區,且需要將登入時間轉換為%Y%m%d
日期格式。僅保留最近30個分區(即生命週期為30)的資料。
根據顧客ID(即
customer_id
列)進行資料分布。將
login_time, customer_id, phone_num
設定為組合主鍵。
建表語句如下:
CREATE TABLE customer (
customer_id bigint NOT NULL COMMENT '顧客ID',
customer_name varchar NOT NULL COMMENT '顧客姓名',
phone_num bigint NOT NULL COMMENT '電話',
city_name varchar NOT NULL COMMENT '所屬城市',
sex int NOT NULL COMMENT '性別',
id_number varchar NOT NULL COMMENT '社會安全號碼碼',
home_address varchar NOT NULL COMMENT '家庭住址',
office_address varchar NOT NULL COMMENT '辦公地址',
age int NOT NULL COMMENT '年齡',
login_time timestamp NOT NULL COMMENT '登入時間',
PRIMARY KEY (login_time, customer_id, phone_num)
)
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT '客戶資訊表';
常見問題
Q:進行二級分區後,如何查看錶的所有二級分區及分區的統計資訊?
A:執行以下SQL,可以查看錶的所有二級分區及分區的統計資訊:
SELECT partition_id, -- 分區名 row_count, -- 分區總行數 local_data_size, -- 分區本機存放區所佔用空間大小 index_size, -- 分區的索引大小 pk_size, -- 分區的主鍵索引大小 remote_data_size -- 分區的遠端儲存所佔用空間大小 FROM information_schema.kepler_partitions WHERE schema_name = '$DB' AND table_name ='$TABLE' AND partition_id > 0;
重要針對增量資料中還未觸發compaction的分區不予展示。如果需要查看即時的所有二級分區列表,可以通過
select distinct $partition_column from $db.$table;
進行查詢。Q:分區數跟什麼因素有關係?使用者是否可以自己調整?
A:分區數是建立叢集時根據叢集初始規格自動計算得出的。不支援使用者調整分區數。
Q:在叢集變更配置時,是否會對分區數產生影響?
A:叢集變更配置時,不會影響分區數。
Q:AnalyticDB for MySQL是否支援修改分布鍵/分區鍵?
A:不支援。如果需要修改分布鍵/分區鍵,請參見ALTER TABLE。