AnalyticDB for MySQLでのテーブルスキーマ設計 (配布キーとパーティションキーの選択を含む) の実行 - AnalyticDB for MySQL

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

テーブルタイプの選択

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、自動インクリメント列など、値が分散キーとして均等に分散されているフィールドを選択します。
  説明
  日付、時刻、およびタイムスタンプタイプのフィールドを配布キーとして選択しないでください。先行するフィールドは、データ書き込み中にデータスキューを起こしやすく、書き込み性能を低下させる可能性がある。ほとんどのクエリは、日や月などの期間に制限されています。この場合、クエリするデータは単一のノードにのみ存在する可能性があり、クエリは分散データベースシステムのすべてのノードの処理機能を利用することはできません。パーティションキーとして日付と時刻の種類のフィールドを選択することを推奨します。詳細については、このトピックの「パーティションキーの選択」をご参照ください。
- データのシャッフルを減らすには、テーブルの結合に使用できるフィールドを配布キーとして選択します。たとえば、顧客に基づいて過去の注文をクエリする場合は、customer_idフィールドを配布キーとして選択できます。
- 配布キーに基づいてデータをフィルタリングするには、頻繁に使用されるクエリ条件フィールドを配布キーとして選択します。
- 各テーブルには、単一の配布キーのみを含めることができます。各配布キーは、1つ以上のフィールドを含むことができる。選択するフィールドを少なくすることで、さまざまな複雑なクエリに適した配布キーを作成できます。
- テーブルの作成時に配布キーを指定しない場合は、次のルールが適用されます。
  - テーブルにプライマリキーがある場合、AnalyticDB for MySQLはプライマリキーを配布キーとして使用します。
  - テーブルにプライマリキーがない場合、AnalyticDB for MySQLは __adb_auto_id__ フィールドをテーブルに追加し、そのフィールドをプライマリキーおよび配布キーとして使用します。

パーティションキーの選択

テーブルの配布キーを指定した後、単一のシャードに大量のデータが含まれている場合は、データアクセスのパフォーマンスを向上させるために、シャードをパーティションに分割できます。テーブルを作成するときは、PARTITION BY句を使用して、テーブルをパーティション化するパーティションキーを指定します。詳細については、「CREATE TABLE」をご参照ください。

構文
- column_nameフィールドの値を使用して、テーブルを分割します。
```
PARTITION BY VALUE(column_name)
```
- column_nameフィールドの値を使用してテーブルを分割します。この値は、20210101などの % Y % m % d形式に変換されます。
```
PARTITION BY VALUE(column_name)
```
- 202101などの % Y % m形式に変換されたcolumn_nameフィールドの値を使用して、テーブルを分割します。
```
PARTITION BY VALUE(DATE_FORMAT(column_name, '%Y%m'))
```
- 2021などの % Y形式に変換されたcolumn_nameフィールドの値を使用して、テーブルを分割します。
```
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テーブルのプライマリキーは、単一のフィールドまたは複数のフィールドの組み合わせです。高いパフォーマンスを確保するために、1つまたは少数の数値フィールドを主キーとして選択することをお勧めします。
- 主キーには、配布キーとパーティションキーを含める必要があります。複合主キーのフロントセクションに配布キーとパーティションキーを配置することを推奨します。

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

クラスター化インデックスでは、キー値の論理的な順序によって、テーブル内の対応する行の物理的な順序が決まります。クラスター化インデックスを選択するときは、次の項目に注意してください。

各テーブルは、単一のクラスタ化インデックスをサポートします。クラスター化インデックスの作成方法については、「create TABLE」をご参照ください。
データクエリに常に含まれるフィールドをクラスター化インデックスとして選択することをお勧めします。たとえば、学生が教育管理システムで自分の最終試験成績のみを照会できるようにしたいとします。この場合、学生IDをクラスタ化インデックスとして指定して、データの局所性を確保し、データクエリのパフォーマンスを向上させることができます。
クラスター化インデックスはテーブル全体をソートするため、データ書き込みパフォーマンスが低下し、CPU使用率が高くなります。クラスター化インデックスは使用しないことを推奨します。

例：

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

テーブルデータは、% Y % m % d形式に変換されたlogin_timeフィールドの値を使用して分割されます。
最後の30個のパーティションのデータのみが保持されます。これは、LIFECYCLE NパラメータがLIFECYCLE 30に設定されていることを示します。
テーブルデータは、customer_idフィールドを使用して配布されます。
主キーはlogin_time, customer_id, phone_numに設定されます。

次のステートメントを実行して、顧客テーブルを作成します。

CREATE TABLE customer (
customer_id bigint NOT NULL COMMENT 'Customer ID',
customer_name varchar NOT NULL COMMENT 'Customer name',
phone_num bigint NOT NULL COMMENT 'Phone number',
city_name varchar NOT NULL COMMENT 'City',
sex int NOT NULL COMMENT 'Gender',
id_number varchar NOT NULL COMMENT 'ID card number',
home_address varchar NOT NULL COMMENT 'Home address',
office_address varchar NOT NULL COMMENT 'Office address',
age int NOT NULL COMMENT 'Age',
login_time timestamp NOT NULL COMMENT 'Logon time',
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 'Customer information table';

よくある質問

Q: テーブルをパーティションに分割した後、テーブルのすべてのパーティションとパーティションに関する統計をクエリするにはどうすればよいですか?
A: 次のステートメントを実行して、テーブルのすべてのパーティションと、パーティションに関する統計を照会します。
```
SELECT partition_id, -- The name of the partition.
          row_count, -- The total number of rows in the partition.
          local_data_size, -- The local data storage of the partition.
          index_size, -- The index size of the partition.
          pk_size, -- The size of the primary key index of the partition.
          remote_data_size -- The remote data storage of the partition.
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: シャードの数は、クラスターの作成時に指定したAnalyticDB for MySQLクラスターの仕様に基づいて自動的に計算されます。手動でシャードの数を変更することはできません。
Q: AnalyticDB for MySQLクラスターの設定を変更すると、シャードの数が影響を受けますか。
A: クラスターの設定を変更しても、シャードの数は影響を受けません。
Q: AnalyticDB for MySQLテーブルの配布キーまたはパーティションキーを変更できますか?
A: いいえ。AnalyticDB for MySQLテーブルの配布キーまたはパーティションキーは変更できません。別の配布キーまたはパーティションキーの使用方法については、「ALTER table」トピックの「テーブルの配布キーまたはパーティションキーを変更できますか」を参照してください。