如何設計AnalyticDBMySQL表結構,分布鍵,分區鍵 - AnalyticDB

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