全部產品
Search
文件中心

AnalyticDB:表結構設計

更新時間:Oct 24, 2024

本文介紹如何設計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