AnalyticDB for MySQL のテーブルスキーマの設計方法、分散キー、パーティションキー - AnalyticDB

このトピックでは、パフォーマンスを最適化するために AnalyticDB for MySQL テーブルのスキーマを設計する方法について説明します。スキーマには、テーブルタイプ、分散キー、パーティションキー、プライマリキー、クラスター化インデックスキーが含まれます。

テーブルタイプの選択

AnalyticDB for MySQL は、レプリケートテーブルと標準テーブルをサポートしています。テーブルタイプを選択する際は、次の点を考慮してください：

レプリケートテーブルは、クラスターの各ノードにデータのレプリカを格納します。各レプリケートテーブルのデータ量は、最大 20,000 行に制限することを推奨します。
標準テーブルはパーティションテーブルとも呼ばれ、分散システムのクエリ能力を利用してクエリパフォーマンスを向上させることができます。標準テーブルには、数千万行から数千億行までの大量のデータを格納できます。

分散キーの選択

増分データをインポートするには、標準テーブルを作成する際に分散キーとパーティションキーを指定します。これにより、増分データ同期が可能になります。テーブルを作成する際は、DISTRIBUTED BY HASH(column_name,...) 句を使用して分散キーを指定します。テーブルは、column_name フィールドのハッシュ値に基づいてシャーディングされます。詳細については、「CREATE TABLE」をご参照ください。

構文
```
DISTRIBUTED BY HASH(column_name,...)
```
注意事項
- トランザクション ID、デバイス ID、ユーザー ID、自動インクリメント列など、値が均等に分散されているフィールドを分散キーとして選択します。
  説明
  DATE、TIME、または TIMESTAMP 型のフィールドを分散キーとして選択しないでください。これらのフィールドは、データ書き込み時にデータスキューを引き起こし、書き込みパフォーマンスを低下させる可能性があります。ほとんどのクエリは、過去 1 日や 1 か月など、時間範囲が限定されています。この場合、クエリ対象のデータが単一のノードにしか存在しない可能性があり、分散データベースのすべてのノードの処理能力を活用できなくなります。DATE 型または TIME 型のフィールドは、サブパーティションキーとして使用することを推奨します。詳細については、「パーティションキーの選択」をご参照ください。
- データシャッフルを減らすには、テーブルの結合に使用されるフィールドを分散キーとして選択します。たとえば、顧客別に過去の注文をクエリする場合は、customer_id フィールドを分散キーとして選択できます。
- クエリ条件に頻繁に現れるフィールドを分散キーとして選択します。これにより、分散キーに基づいたパーティションプルーニングが可能になります。
- 各テーブルには分散キーを 1 つだけ設定できます。分散キーには 1 つ以上のフィールドを含めることができます。さまざまな複雑なクエリに対して分散キーの汎用性を高めるために、できるだけ少ないフィールドを選択してください。
- テーブル作成時に分散キーを指定しない場合、システムは次のように処理します：
  - テーブルにプライマリキーがある場合、AnalyticDB for 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'))|(FROM_UNIXTIME(column_name, '%Y%m%d'))}
```
- column_name フィールドの値を %Y%m の日付形式 (例：202101) に変換してテーブルをパーティショニングします。構文は次のとおりです：
```
PARTITION BY VALUE{(DATE_FORMAT(column_name, '%Y%m'))|(FROM_UNIXTIME(column_name, '%Y%m'))}
```
- column_name フィールドの値を %Y の日付形式 (例：2021) に変換してテーブルをパーティショニングします。構文は次のとおりです：
```
PARTITION BY VALUE{(DATE_FORMAT(column_name, '%Y'))|(FROM_UNIXTIME(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 テーブルのプライマリキーは、単一のフィールドまたは複数のフィールドの組み合わせにすることができます。テーブルのパフォーマンスを向上させるには、数値フィールドをプライマリキーとして使用し、できるだけ少ないフィールドを使用することを推奨します。
- プライマリキーには、分散キーとパーティションキーを含める必要があります。複合プライマリキーの先頭に分散キーとパーティションキーを配置することを推奨します。

クラスター化インデックスキーの選択

クラスター化インデックス内のキー値の論理的な順序は、テーブル内の対応する行の物理的な順序を決定します。クラスター化インデックスキーを選択する際は、次の点を考慮してください：

各テーブルは 1 つのクラスター化インデックスのみをサポートします。作成方法については、「CREATE TABLE」をご参照ください。
常にクエリに含まれるフィールドをクラスター化インデックスキーとして使用します。たとえば、学校の学生情報システムで、各学生が自分の最終成績のみを閲覧する必要がある場合、学生 ID をクラスター化インデックスとして定義することで、データ局所性を確保し、クエリパフォーマンスを向上させることができます。
クラスター化インデックスはテーブル全体をソートするため、CPU リソースなどのリソースを消費します。クラスター化インデックスは慎重に使用してください。

例

次の要件を満たす customer という名前のテーブルを作成します：

顧客のログイン時間 (login_time 列) に基づいてテーブルデータをパーティショニングし、ログイン時間を %Y%m%d の日付形式に変換します。
最後の 30 パーティションのデータのみを保持します (ライフサイクルは 30)。
顧客 ID (customer_id 列) に基づいてデータを分散します。
login_time, customer_id, phone_num を複合プライマリキーとして設定します。

CREATE TABLE ステートメントは次のとおりです：

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 'ID カード番号',
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;
```
重要
コンパクションがトリガーされていない増分データのパーティションは表示されません。すべてのサブパーティションのリアルタイムリストを表示するには、select distinct $partition_column from $db.$table; ステートメントを実行します。
Q：シャード数に影響を与える要因は何ですか？シャード数を自分で変更できますか？
A：シャード数は、クラスター作成時の初期スペックに基づいて自動的に計算されます。シャード数を変更することはできません。
Q：クラスターのスペックを変更すると、シャード数に影響しますか？
A：クラスターのスペックアップまたはスペックダウンは、シャード数に影響しません。
Q：AnalyticDB for MySQL は、分散キーまたはパーティションキーの変更をサポートしていますか？
A：いいえ、サポートしていません。分散キーまたはパーティションキーを変更するには、「ALTER TABLE」をご参照ください。