テーブルのデータ配布スキーム
次の図は、シャード、パーティション、クラスター化インデックスなど、テーブルを作成する前に理解する必要がある概念を示しています。
構文
CREATE TABLE [IF NOT EXISTS] table_name
({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'column_comment']
| table_constraints}
[, ... ])
[table_attribute]
[partition_options]
[storage_policy]
[block_size]
[engine]
[rt_engine]
[table_properties]
[AS query_expr]
[COMMENT 'table_comment']
column_attributes:
[DEFAULT {constant | CURRENT_TIMESTAMP}]
[AUTO_INCREMENT]
column_constraints:
[{NOT NULL|NULL} ]
[PRIMARY KEY]
table_constraints:
[{INDEX|KEY} [index_name] (column_name,...)]
[{INDEX|KEY} [index_name] (column_name->'$[*]')]
[FULLTEXT [INDEX|KEY] [index_name] (column_name) [index_option]] [,...]
[PRIMARY KEY [index_name] (column_name,...)]
[CLUSTERED KEY [index_name] (column_name,...)]
[[CONSTRAINT [symbol]] FOREIGN KEY (fk_column_name) REFERENCES pk_table_name (pk_column_name)][,...]
[ANN INDEX [index_name] (column_name,...) [index_option]] [,...]
table_attribute:
DISTRIBUTE BY HASH(column_name,...) | DISTRIBUTE BY BROADCAST
partition_options:
PARTITION BY
{VALUE(column_name) | VALUE(date_format(column_name, 'format'))}
LIFECYCLE N
storage_policy:
STORAGE_POLICY= {'HOT'|'COLD'|'MIXED' {hot_partition_count=N}}
block_size:
BLOCK_SIZE= VALUE
engine:
ENGINE= 'XUANWU|XUANWU_V2'
パラメーター
table_name、column_name、column_type、およびCOMMENT
パラメーター | 説明 |
table_name | テーブルの名前。 テーブル名は1 ~ 127文字で、英数字、アンダースコア (_) を使用できます。 テーブル名は、英字またはアンダースコア (_) で始まる必要があります。 db_name.table_name 形式を使用して、データベースに作成するテーブルを指定できます。
|
column_name | テーブルに追加する列の名前。 列名は1 ~ 127文字で、英数字、アンダースコア (_) を使用できます。 列名は、文字またはアンダースコア (_) で始まる必要があります。 |
column_type | 列のデータ型。 AnalyticDB for MySQLでサポートされているデータ型については、「基本データ型」および「複合データ型」をご参照ください。 |
COMMENT | 列またはテーブルのコメント。 |
column_attributes (DEFAULT | AUTO_INCREMENT)
DEFAULT {定数 | CURRENT_TIMESTAMP}
列のデフォルト値を指定します。 デフォルト値は、constantまたはCURRENT_TIMESTAMP関数です。 他の関数またはバリアント式はサポートされていません。
値を指定しない場合、列のデフォルト値はNULL
です。
AUTO_INCREMENT
自動インクリメント列を指定します。 自動インクリメント列のデータ型はBIGINT
である必要があります。
AnalyticDB for MySQLは、自動インクリメント列に一意の値を割り当てます。 ただし、これらの値は順次インクリメントされず、常に1から始まるとは限りません。
column_constraints (NOT NULL | PRIMARY KEY)
NOT NULL
NOT NULL
列を指定します。NULL
値を含めることはできません。 NULL
またはnot NULL
として指定されていない列には、NULL
値を含めることができます。
主要なキー
主キーを指定します。 列制約を使用して、主キーとして指定できる列は1つだけです。 たとえば、id BIGINT NOT NULL primary key
を使用して、id列をプライマリキーとして指定できます。 複数の列を主キーとして指定するには、table_constraintsで複合主キーを使用します。
table_constraints (インデックス)
AnalyticDB for MySQLは、INDEX、PRIMARY KEY、CLUSTERED KEY、FOREIGN KEY、FULLTEXT INDEX、ANN INDEXなどのさまざまなインデックスをサポートしています。 テーブルには、1つ以上の種類のインデックスを含めることができます。
インデックス | キー
通常のインデックスを指定します。 INDEXとKEYは同じ意味で使用できます。
デフォルトでは、AnalyticDB for MySQLはテーブルのすべての列にインデックスを自動的に作成します。 ただし、index (id)
を使用してid列のインデックスなど、テーブルを作成するときに1つ以上の列にインデックスを作成した場合、AnalyticDB for MySQLはテーブルの他の列にインデックスを自動的に生成しません。
主要なキー
主キーインデックスを指定します。
概要
各テーブルには主キーを1つだけ持つことができます。
主キーは、primary key (id)
などの単一の列、またはPRIMARY KEY (id,name)
などの複数の列で構成できます。
複合主キーには、配布キーとパーティションキーが必要です。 複合主キーのフロントセクションに配布キーとパーティションキーを配置することを推奨します。
使用上の注意
主キーがないテーブルでは、DELETEまたはUPDATE操作を実行できません。
プライマリキーを指定しない場合、次のルールが適用されます。
テーブルの作成後、主キー列の追加、削除、または変更はできません。
高いパフォーマンスを確保するために、1つまたは少数の数値列を主キーとして選択することをお勧めします。
説明 テーブルに過剰な主キー列がある場合、次の問題が発生する可能性があります。
CPUおよびI/Oリソースの消費量が多い。 これは、AnalyticDB for MySQLが、データの書き込み時に重複するプライマリキー値が存在するかどうかをチェックするためです。
主キーインデックスのディスク使用率が高い。 プライマリキーインデックスのディスク使用状況を表示するには、スペースの分析機能を使用します。
BUILDジョブのパーティション再構築速度が遅くなります。
クラスターキー
クラスター化インデックスを指定します。 クラスタ化インデックスは、パーティションレベルで設定されます。 データが保存される物理的な順序を決定します。 パーティション内のデータは、クラスタ化インデックスの値に基づいて順次ソートされ、格納されます。 クラスタ化インデックスの同じまたは類似のキー値を有するデータレコードは、同じまたは隣接するデータブロックに格納される。 範囲クエリまたは等価フィルタリングでは、クラスタ化インデックスを使用すると、ディスクI/Oを減らし、データ読み取りを高速化できます。 これは、クエリ条件がクラスタ化インデックス列と同じである場合、ストレージエンジンが連続したデータブロックを読み取ることができるためです。
該当するシナリオ
クラスタ化インデックスは、範囲クエリと等価フィルタリングでうまく機能します。 範囲クエリまたは等価フィルタリングの条件で頻繁に使用される列は、理想的なクラスタ化インデックス列です。
クエリ条件がクラスタ化インデックス列と一致または部分的に一致すると、読み取り効率が大幅に向上します。 たとえば、software-as-a-service (SaaS) アプリケーションのクラスター化インデックスとしてユーザーIDを使用できます。 これにより、特定のユーザーIDのレコードが同じまたは連続したデータブロックに格納され、データクエリのパフォーマンスが向上します。
使用上の注意
各テーブルには、クラスター化インデックスを1つだけ設定できます。
クラスター化されたインデックスは、clustered KEYインデックス (id)
などの単一の列、またはCLUSTERED KEYインデックス (id,name)
などの複数の列に作成できます。 クラスタ化インデックスに2つの列が含まれる場合、データは最初に最初のクラスタ化インデックス列の値に基づいてソートされます。 最初のクラスタ化インデックス列の値が同じ場合、データは2番目のクラスタ化インデックス列の値に基づいてソートされます。 したがって、CLUSTERED KEY index(id,name)
とCLUSTERED KEY index(name,id)
は異なるクラスタ化インデックスです。
並べ替えのパフォーマンスが低下しないように、クラスタ化インデックスでは、長い値 (10 KBを超える文字列など) を持つ列を使用しないことをお勧めします。
FULLTEXTインデックス | FULLTEXTキー
フルテキストインデックスを指定します。 FULLTEXT INDEXとFULLTEXT KEYは交換可能に使用できます。 フルテキストインデックスの詳細については、「フルテキストインデックスの作成」をご参照ください。
構文とパラメータ
構文: [FULLTEXT [INDEX | KEY] [index_name] (column_name) [index_option]] [,...]
パラメータ:
index_name: フルテキストインデックスの名前。
column_name: フルテキストインデックスを作成する列の名前。 列のタイプはVARCHARでなければなりません。
index_option: フルテキストインデックスに使用されるアナライザーとカスタム辞書を指定します。 このパラメーターはオプションです。
WITH ANALYZER analyzer_name
: フルテキストインデックスに使用されるアナライザーを指定します。 アナライザーとその使用方法については、「フルテキストインデックスのアナライザー」をご参照ください。
WITH DICT tbl_dict_name
: フルテキストインデックスに使用されるカスタム辞書を指定します。 カスタム辞書の詳細については、「フルテキストインデックスのカスタム辞書」をご参照ください。
外国キー
外部キーインデックスを指定します。 外部キーインデックスは、不要な結合を排除するために使用されます。 詳細については、「プライマリキーと外部キーの制約を使用して不要な結合を排除する」をご参照ください。
構文とパラメータ
サポートされるバージョン:
V3.1.10以降のAnalyticDB for MySQLクラスターのみがFOREIGN KEY句をサポートしています。
説明 AnalyticDB For MySQLのマイナーバージョンを表示する方法の詳細について Data Lakehouse Editionクラスターについては、「クラスターのマイナーバージョンを表示する方法」をご参照ください。 クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。
構文: [[CONSTRAINT [シンボル]] FOREIGN KEY (fk_column_name) 参照pk_table_name (pk_column_name)][,...]
パラメータ:
symbol: 外部キー制約の名前。 名前はテーブル内で一意である必要があります。 このパラメーターはオプションです。 このパラメーターを指定しない場合、パーサーは外部キー制約の名前として_fkのサフィックスが付いた外部キー列の名前を自動的に使用します。
fk_column_name: 外部キー列の名前。 列はすでに存在している必要があります。
pk_table_name: プライマリテーブルの名前。 プライマリテーブルは既に存在している必要があります。
pk_column_name: プライマリテーブルのプライマリキー列である外部キー制約列の名前。 列はすでに存在している必要があります。
使用上の注意
各テーブルは複数の外部キーインデックスを持つことができます。
外部キーインデックスは、foreign key (sr_item_sk, sr_ticket_number) REFERENCES store_sales(ss_item_sk,d_date_sk)
などの複数の列で構成できません。
AnalyticDB for MySQLはデータ制約をチェックしません。 プライマリテーブルのプライマリキーと関連するテーブルの外部キーの間のデータ制約関係を確認する必要があります。
外部テーブルに外部キー制約を追加することはできません。
ANNインデックス
ベクトルインデックスを指定します。 ベクトル検索の詳細については、「ベクトル検索」をご参照ください。
構文とパラメータ
構文: [ANN INDEX [index_name] (column_name,...) [index_option]] [,...]
パラメータ:
index_name: ベクトルインデックスの名前。
column_name: ベクトル列の名前。 ベクトル列は、ARRAY<FLOAT> 、ARRAY<SMALLINT> 、またはARRAY<BYTE> のいずれかのタイプでなければなりません。 ベクトル次元を指定する必要があります。 たとえば、次の構文を使用して、featureという名前のARRAY<FLOAT> 型の4次元ベクトル列を作成できます。feature array<float>(4)
index_option: ベクトルインデックスの属性。
algorithm
: ベクトル間の距離を計算するための式で使用されるアルゴリズム。 この値をHNSW_PQに設定します。これは、ベクトル次元に敏感で、テーブルごとに100万から10万のレコードが含まれる中規模のデータセットに適しています。
dis_function
: ベクトル間の距離を計算するために使用される式。 値をSquaredL2に設定します。 計算式:(x1 - y1)^ 2 (x2 - y2)^ 2...
.
JSONインデックス
JSONインデックスまたはJSON配列インデックスを指定します。 詳細は、「JSONインデックス」をご参照ください。
構文とパラメータ
JSONインデックス
サポートされるバージョン:
説明 AnalyticDB For MySQLのマイナーバージョンを表示する方法の詳細について Data Lakehouse Editionクラスターについては、「クラスターのマイナーバージョンを表示する方法」をご参照ください。 クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。
構文: [INDEX [index_name] (column_name | column_name->'$.json_path'.)]
パラメータ:
JSON配列インデックス
サポートされるバージョン:
V3.1.10.6以降のAnalyticDB for MySQLクラスターのみがJSON配列インデックスをサポートしています。
説明 AnalyticDB For MySQLのマイナーバージョンを表示する方法の詳細について Data Lakehouse Editionクラスターについては、「クラスターのマイナーバージョンを表示する方法」をご参照ください。 クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。
構文: [INDEX [index_name] (column_name->'$[*]')]
パラメータ:
table_attribute (配布キー)
テーブルが標準テーブルかレプリケートテーブルかを決定します。
DISTRIBUTED BY HASH: テーブルを標準テーブルとして指定します。 標準テーブルは、分散システムのクエリ機能をフルに活用して、クエリ効率を向上させることができます。 各標準テーブルは、最大数千億のデータレコードを格納できます。
DISTRIBUTED BY BROADCAST: レプリケートされたテーブルとしてテーブルを指定します。 レプリケートされたテーブルは、テーブルが属するAnalyticDB for MySQLクラスターの各シャードにデータレプリカを格納します。 レプリケートされた各テーブルに最大20,000行を格納することをお勧めします。
HASHによる分布 (column_name,...)
テーブルの配布キーを指定します。 配布キーを持つテーブルは標準テーブルです。 AnalyticDB for MySQLは、配布キー値のハッシュ値を計算し、ハッシュ値に基づいてテーブルをシャードに分割します。 シャーディングは、スケーラビリティとクエリのパフォーマンスを向上させます。
概要
各テーブルには配布キーを1つだけ持つことができます。
各配布キーには1つ以上の列を含めることができます。
配布キー列は主キー列に含める必要があります。 たとえば、配布キーがcustomer_id列の場合、主キーにはcustomer_id列が含まれている必要があります。
使用上の注意
おすすめ
分散キーをさまざまな複雑なクエリに適したものにするには、選択する列を少なくします。
トランザクションID、デバイスID、ユーザーID、自動インクリメント列など、値が均等に分散されている列を配布キーとして選択します。 ただし、クエリ条件が少数の列値に制限されている場合、これらの列は理想的な配布キーではありません。 たとえば、列Aの値が均等に分散されているが、クエリ条件が常にA=3の場合、列Aを分散キーとして設定すると、データホットスポットが発生します。
テーブルの結合に使用できる列を配布キーとして選択します。 このようにして、結合された2つのテーブルで同じ配布キー値を持つデータが同じシャードに配布されます。 結合操作は、シャード間のデータ伝送の必要なしに同じシャード上で実行される。 これにより、データの再配布が最小限に抑えられ、クエリのパフォーマンスが向上します。 たとえば、ユーザーの注文履歴を照会する場合、customer_id
列を配布キーとして指定できます。
日付、時刻、およびタイムスタンプタイプの列を配布キーとして選択しないでください。 前の列は、データ書き込み中にデータスキューを起こしやすく、書き込みパフォーマンスを低下させる可能性があります。 ほとんどのクエリは、日や月などの期間に制限されています。 この場合、クエリするデータは1つのノードにしか存在しない可能性があり、クエリは分散データベースシステムのすべてのノードの処理機能を利用することはできません。 パーティションキーとして日付と時刻の種類の列を選択することを推奨します。 詳細については、このトピックの「partition_options (Partition key and lifecycle) 」セクションをご参照ください。
データモデリング診断機能を使用して、列が適切な配布キーであるかどうか、およびデータスキューが発生するかどうかを確認できます。 詳細については、「ストレージ診断」をご参照ください。
ブロードキャストで分配
レプリケートされたテーブルを指定します。 レプリケートされたテーブルは、テーブルが属するAnalyticDB for MySQLクラスターの各シャードにデータレプリカを格納します。 レプリケートされた各テーブルに大量のデータを格納しないことをお勧めします。
利点: JOINクエリを実行するときに、レプリケートされたテーブルのデータを異なるノード間で送信する必要はありません。 これにより、ネットワーク送信のオーバーヘッドが大幅に削減され、並行性の高いシナリオでのクラスターの安定性が向上します。
デメリット: レプリケートされたテーブルでINSERT、UPDATE、およびDELETE操作を実行した後にデータが変更されると、これらの変更はクラスターのすべてのノードにブロードキャストされ、データの一貫性が確保されます。 しかしながら、これは全体的な書き込み性能に影響を及ぼす。 したがって、レプリケートされたテーブルを頻繁に作成、削除、または変更しないことをお勧めします。
partition_options (パーティションキーとライフサイクル)
テーブルの配布キーを指定した後、単一のシャードに大量のデータが含まれている場合、シャードをパーティションに分割して、データのフィルタリングを高速化し、クエリのパフォーマンスを向上させることができます。
メリット
パーティショニングは、次の理由により、データのフィルタリングを高速化し、クエリのパフォーマンスを向上させます。
パーティションプルーニング機能を使用すると、クエリ速度が向上します。 パーティションプルーニング機能により、システムはクエリに関連するデータを含むパーティションのみをスキャンできます。 これにより、クエリ速度が向上します。
インデックススキャンのパフォーマンスを向上させます。 50百万などの過剰な数の行を含むテーブルがパーティション分割されていない場合、インデックススキャンの効率は低くなります。 テーブルがパーティション分割されている場合、パーティションごとにインデックスが作成されます。 これは、より効率的なインデックススキャンにつながる。
BUILDジョブの効率を向上させます。 BUILDジョブを使用して、リアルタイムで書き込まれたデータを履歴データに変換できます。 プロセス中に、システムはパーティションとインデックスを作成し、冗長データをクリアします。 インデックスは、BUILDジョブが完了した後にのみ有効になります。 テーブルがパーティション分割されていない場合、テーブル全体がビルドジョブごとにスキャンされます。 テーブルに含まれるレコードが多いほど、プロセスにかかる時間が長くなり、新しいインデックスが有効になります。 これはクエリのパフォーマンスに影響します。 テーブルがパーティション分割されている場合、データが変更されているパーティションのみがビルドジョブごとにスキャンされます。 これにより、BUILDジョブの効率が向上します。
パーティショニングは、データライフサイクル管理を容易にする。
パーティショニングは、異なるストレージポリシーに基づいてホットデータとコールドデータの階層ストレージを実装するのに役立ちます。
PARTITION BY
パーティションキーを指定します。
構文: PARTITION BY VALUE {(column_name) | (DATE_FORMAT(column_name, 'format'))} LIFECYCLE n
パラメータ:
column_name: パーティションキーの名前。 PARTITION BY VALUE(column_name)
構文では、パーティション分割はcolumn_name
列の値に基づいて行われます。 パーティションキーには、numeric、datetime、a string that specifies a numberのいずれかのタイプを指定できます。
DATE_FORMAT(column_name, 'format')
: datetime列を指定された日付形式に変換し、データを分割します。 指定された日付形式には、年、月、または日のみを含めることができます。 以下の日付形式がサポートされています: % Y、% y、% Y % m、% y % m、% Y % m % d、および % y % m % d。 テーブルの作成後に形式を変更できます。 詳細については、「ALTER TABLE」をご参照ください。
使用上の注意
V3.2.1.0より前のAnalyticDB For MySQLクラスターの場合、PARTITION BY
句を使用してパーティションキーを指定する場合、LIFECYCLE n
パラメーターを使用してパーティションのライフサイクルを指定する必要があります。 それ以外の場合は、エラーが返されます。
V3.2.1.0以降のAnalyticDB For MySQLクラスターの場合、PARTITION BY
句を使用してパーティションキーを指定すると、LIFECYCLE n
パラメーターはオプションになります。 LIFECYCLE nパラメーターを指定しない場合、パーティションデータは削除されません。
テーブルの作成後、パーティションキーの追加、パーティションキー列の追加、削除、変更はできません。 パーティションキーを追加または変更するには、目的のパーティションキーを持つテーブルを作成し、新しいテーブルにデータを移行します。 詳細については、「ALTER TABLE」をご参照ください。
おすすめ
パーティションキーとしてdatetime列を使用することを推奨します。
パーティションが大きすぎたり小さすぎたりすると、読み書きのパフォーマンスに影響を与える可能性があり、クラスターの安定性にも影響を与える可能性があります。 推奨されるパーティションサイズとパーティションフィールドの合理性の基準については、「ストレージ診断」トピックの「パーティションテーブル診断」セクションをご参照ください。
履歴パーティションのデータを頻繁に更新しないことを推奨します。 履歴パーティションのデータを頻繁に更新する場合は、パーティションキーを変更する必要があります。
LIFECYCLE n
LIFECYCLE nパラメーターは、PARTITION BY
句とともに使用する必要があります。 このパラメーターを使用して、パーティションのライフサイクルを管理できます。 AnalyticDB for MySQLは、パーティションキー値に基づいてパーティションを降順にソートします。 最初のn個のパーティションは保持され、他のパーティションは削除されます。
V3.2.1.1より前のAnalyticDB For MySQLクラスターの場合、LIFECYCLE n
パラメーターは、最大n個のパーティションを各シャードに保持できることを指定します。 パーティションのライフサイクルは、シャードレベルで管理されます。 ただし、データが不均等に分散している場合やデータ量が少なすぎる場合は、特定のシャードにn個を超えるパーティションが保持される可能性があります。
V3.2.1.1以降のAnalyticDB For MySQLクラスターの場合、パーティションライフサイクルはテーブルレベルで管理されます。 LIFECYCLE n
パラメーターは、最大n個のパーティションを各テーブルに保持できることを指定します。 ただし、AnalyticDB for MySQLクラスターがV3.2.1.1以降に更新される前に作成されたテーブルの場合、パーティションライフサイクルはシャードレベルで管理されます。 LIFECYCLE n
パラメーターは、最大n個のパーティションを各シャードに保持できることを指定します。
例
PARTITION BY VALUE (DATE_FORMAT(date, '% Y % m % d')) LIFECYCLE 30
は、パーティション分割中に、日付列のデータがyyyyMMdd形式に変換され、最大30のパーティションが保持されることを指定します。 1日目から30日目までのデータが、パーティション20231201からパーティション20231230までの対応するパーティションに書き込まれると仮定する。 データがパーティション20231231に書き込まれる31日目に、最大30個のパーティションしか保持できないため、パーティションキー値が最小のパーティション (この場合はパーティション20231201) が自動的に削除されます。
STORAGE_POLICY (ストレージポリシー)
Cluster EditionのData Lakehouse EditionおよびData Warehouse Edition in elastic modeでは、データストレージポリシーを指定できます。 ストレージポリシーは、読み書きのパフォーマンスとストレージコストが異なります。
有効値:
hot (デフォルト): ホットストレージ テーブル全体のすべてのパーティションのデータはSSDに保存されます。 ホットストレージは最高の読み取り /書き込みパフォーマンスを備えていますが、ストレージコストが最も高くなります。
コールド: コールドストレージ。 テーブル全体のすべてのパーティションのデータは、Object Storage Service (OSS) に保存されます。 ホットストレージと比較して、コールドストレージは読み取り /書き込みパフォーマンスが低くなりますが、最も安価なオプションです。
混合: ホットストレージとコールドストレージの組み合わせ、階層ストレージとも呼ばれます。 このポリシーは、頻繁にアクセスされるデータ (ホットデータ) をSSDに保存し、頻繁にアクセスされないデータ (コールドデータ) をOSSに保存することで、ストレージコストを削減し、クエリパフォーマンスを保証します。 STORAGE_POLICYパラメーターをmixedに設定した場合、PARTITION BY
句を使用してパーティションキーを指定し、hot_partition_count
パラメーターを使用してホットパーティションの数を指定する必要があります。 パーティションキーを指定しないと、階層ストレージは有効にならず、データはSSDに保存されます。
hot_partition_count (ホットパーティション)
STORAGE_POLICY
パラメーターをmixedに設定した場合、hot_partition_count=n
(nは正の整数) を使用してホットパーティションの数を指定する必要があります。 AnalyticDB for MySQLは、パーティションキーの値に基づいて、レコードを降順に並べ替えます。 最初のn個のパーティションはホットパーティションであり、他のパーティションはコールドパーティションです。
説明 STORAGE_POLICY
パラメーターをmixed以外の値に設定し、hot_partition_count=n
を使用すると、エラーが発生します。
block_size (データブロック)
データブロックは、データを読み書きするための最小のI/O単位である。 BLOCK_SIZEパラメーターは、列指向ストレージの各データブロックに格納される行数を指定します。 このパラメーターは、各I/O操作で読み取られる行数を決定し、クエリの特性に基づいてクエリのパフォーマンスに影響します。 たとえば、ポイントクエリに対してBLOCK_SIZEが大きな値に設定されている場合、ブロックはストレージシステムによって非効率的に読み取られます。 この場合、BLOCK_SIZEの値を適切に減らすことができます。
使用法のノート:
レプリケートされたテーブルのデフォルト値: 4096。
32コア未満のスタンドアロンエディションのエラスティックモードのAnalyticDB for MySQL Data Warehouse Editionクラスターのデフォルト値: 8192。
それ以外の場合のデフォルト値: 32760。 BLOCK_SIZEのデフォルト値が32760の場合、SHOW CREATE TABLE
ステートメントの実行時にBLOCK_SIZEは表示されません。
重要 列指向ストレージに慣れていない場合は、BLOCK_SIZEの値を変更しないことをお勧めします。
エンジン (ストレージエンジン)
AnalyticDB for MySQL内部テーブルのストレージエンジンタイプを指定します。 履歴データの分析には、ストレージエンジンを使用できます。
有効値:
XUANWU (デフォルト): XUANWUストレージエンジン。 テーブルの作成時にENGINEパラメーターを指定しない場合、この値が使用されます。
XUANWU_V2: XUANWU_V2ストレージエンジン。 XUANWUをベースに開発された次世代ストレージエンジンです。 XUANWU_V2ストレージエンジンをサポートするのは、V3.2.0以降のAnalyticDB for MySQLクラスターのみです。 XUANWU_V2ストレージエンジンを使用するには、engineパラメーターをXUANWU_V2に設定する必要があります。 詳細については、「XUANWU_V2 engine」をご参照ください。
説明 V3.1.9.5より前のAnalyticDB For MySQLクラスターの場合、内部テーブルの作成時にENGINE
パラメーターをXUANWUに設定した場合、table_properties
パラメーターを {"format":"columnstore"} に設定する必要があります。 それ以外の場合、テーブルの作成に失敗します。
XUANWUストレージエンジンの詳細については、「XUANWU分析ストレージエンジン」をご参照ください。
例
パーティションテーブルの作成とパーティションライフサイクルの設定
customerという名前の標準テーブルを作成します。 複合主キーとしてlogin_time
、customer_id
、phone_num
、配布キーとしてcustomer_id
、パーティションキーとしてlogin_time
を指定します。 パーティションのライフサイクルを30に設定します。
すべてのパーティションは、login_time
パーティションキーの値に基づいて降順にソートされます。 最初の30のパーティションのみが保持されます。 データが31番目のパーティションに書き込まれると、パーティションキー値が最小のパーティションが自動的に削除されます。
1日目 (login_time値が20231201) から30日目 (login_time値が20231230) までのデータが、パーティション20231201からパーティション20231230までの対応するパーティションに書き込まれると仮定する。 login_time値が20231231のデータが31日目にデータベースに書き込まれると、login_time値が最も小さいパーティション (パーティション20231201) が自動的に削除されます。 このようにして、過去30日以内のデータのみが保持されます。
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';
配布キーなしのテーブルの作成
プライマリキーを持つテーブルの作成
プライマリキーを持ち、配布キーを持たないテーブルを作成した場合、AnalyticDB for MySQLは自動的にプライマリキーを配布キーとして使用します。
CREATE TABLE orders (
order_id BIGINT NOT NULL COMMENT 'Order ID',
customer_id INT NOT NULL COMMENT 'Customer ID',
order_status VARCHAR(1) NOT NULL COMMENT 'Order status',
total_price DECIMAL (15, 2) NOT NULL COMMENT 'Price',
order_date DATE NOT NULL COMMENT 'Order date',
PRIMARY KEY(order_id,order_date)
);
テーブルの作成に使用されるステートメントを照会し、プライマリキー列order_idとorder_dateが配布キーとして使用されていることを確認します。
SHOW CREATE TABLE orders;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` ( |
| | 'order_id' bigint NOT NULL COMMENT 'Order ID', |
| | 'customer_id' int NOT NULL COMMENT 'Customer ID', |
| | 'order_status' varchar(1) NOT NULL COMMENT 'Order status', |
| | 'total_price' decimal(15, 2) NOT NULL COMMENT 'Price', |
| | 'order_date' date NOT NULL COMMENT 'Order date', |
| | PRIMARY KEY (`order_id`,`order_date`) |
| | ) DISTRIBUTE BY HASH(`order_id`,`order_date`) INDEX_ALL='Y' STORAGE_POLICY='HOT' ENGINE='XUANWU' TABLE_PROPERTIES='{"format":"columnstore"}' |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
プライマリキーなしでテーブルを作成する
プライマリキーまたは配布キーを持たないテーブルを作成する場合。 AnalyticDB for MySQLは、__adb_auto_id__
列をテーブルに追加し、その列をプライマリキーおよび配布キーとして使用します。
CREATE TABLE orders_new (
order_id BIGINT NOT NULL COMMENT 'Order ID',
customer_id INT NOT NULL COMMENT 'Customer ID',
order_status VARCHAR(1) NOT NULL COMMENT 'Order status',
total_price DECIMAL (15, 2) NOT NULL COMMENT 'Price',
order_date DATE NOT NULL COMMENT 'Order date'
);
テーブルの作成に使用されるステートメントを照会し、__adb_auto_id__という名前の自動インクリメント列がテーブルに自動的に追加され、プライマリキーおよび配布キーとして使用されることを確認します。
SHOW CREATE TABLE orders_new;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| orders_new | CREATE TABLE `orders_new` ( |
| | `__adb_auto_id__` bigint AUTO_INCREMENT, |
| | 'order_id' bigint NOT NULL COMMENT 'Order ID', |
| | 'customer_id' int NOT NULL COMMENT 'Customer ID', |
| | 'order_status' varchar(1) NOT NULL COMMENT 'Order status', |
| | 'total_price' decimal(15, 2) NOT NULL COMMENT 'Price', |
| | 'order_date' date NOT NULL COMMENT 'Order date', |
| | PRIMARY KEY (`__adb_auto_id__`) |
| | ) DISTRIBUTE BY HASH(`__adb_auto_id__`) INDEX_ALL='Y' STORAGE_POLICY='HOT' ENGINE='XUANWU' TABLE_PROPERTIES='{"format":"columnstore"}' |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
パーティションキーなしでテーブルを作成する
supplier_id
自動インクリメント列を配布キーとして使用し、supplier_id
値のハッシュ値に基づいてシャードされる、supplierという名前のテーブルを作成します。
CREATE TABLE supplier (
supplier_id BIGINT AUTO_INCREMENT PRIMARY KEY,
supplier_name VARCHAR,
address INT,
phone VARCHAR
)
DISTRIBUTED BY HASH(supplier_id);
ストレージポリシーの指定
コールドストレージポリシーの指定
CREATE TABLE item (
order_id BIGINT NOT NULL,
item_id INT NOT NULL,
quantity DECIMAL(15, 2) NOT NULL,
discount DECIMAL(15, 2) NOT NULL,
shipdate DATE NOT NULL,
PRIMARY KEY (order_id,item_id,shipdate)
)
DISTRIBUTE BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='COLD';
ホットストレージポリシーの指定
CREATE TABLE item (
order_id BIGINT NOT NULL,
item_id INT NOT NULL,
quantity DECIMAL(15, 2) NOT NULL,
discount DECIMAL(15, 2) NOT NULL,
shipdate DECIMAL NOT NULL,
PRIMARY KEY (order_id,item_id,shipdate)
)
DISTRIBUTE BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='HOT';
階層ストレージポリシーを指定し、ホットパーティションの数を16に設定します
CREATE TABLE item (
order_id BIGINT NOT NULL,
item_id INT NOT NULL,
quantity DECIMAL(15, 2) NOT NULL,
discount DECIMAL(15, 2) NOT NULL,
shipdate DATE NOT NULL,
PRIMARY KEY (order_id,item_id,shipdate)
)
DISTRIBUTE BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=16;
フルテキストインデックスの指定
コンテンツ列にfidx_cという名前のフルテキストインデックスを作成します。
CREATE TABLE fulltext_tb (
id INT,
content VARCHAR,
keyword VARCHAR,
FULLTEXT INDEX fidx_c(content),
PRIMARY KEY (id)
)
DISTRIBUTE BY HASH(id);
フルテキストインデックスの作成および変更方法については、「フルテキストインデックスの作成」をご参照ください。
フルテキスト検索の詳細については、「フルテキスト検索」をご参照ください。
ベクトルインデックスの指定
short_featureという名前のARRAY<SMALLINT> 型の4次元ベクトル列とfloat_featureという名前のARRAY<FLOAT> 型の4次元ベクトル列を持つテーブルを作成します。
テーブルのベクトル列に基づいて、short_feature_indexおよびfloat_feature_indexという名前のベクトルインデックスを作成します。
CREATE TABLE fact_tb (
xid BIGINT NOT NULL,
cid BIGINT NOT NULL,
uid VARCHAR NOT NULL,
vid VARCHAR NOT NULL,
wid VARCHAR NOT NULL,
short_feature array<smallint>(4),
float_feature array<float>(4),
ann index short_feature_index(short_feature),
ann index float_feature_index(float_feature),
PRIMARY KEY (xid, cid, vid)
)
DISTRIBUTE BY HASH(xid) PARTITION BY VALUE(cid) LIFECYCLE 4;
ベクトルインデックスとベクトル検索の詳細については、「ベクトル検索」をご参照ください。
外部キーインデックスの指定
store_returns
という名前のテーブルを作成します。 FOREIGN KEY
句を使用して、store_returnsテーブルのsr_item_sk
列をcustomer
テーブルの主キー列customer_id
に関連付けます。
CREATE TABLE store_returns (
sr_sale_id BIGINT NOT NULL PRIMARY KEY,
sr_store_sk BIGINT,
sr_item_sk BIGINT NOT NULL,
FOREIGN KEY (sr_item_sk) REFERENCES customer (customer_id)
);
JSON配列インデックスの指定
テーブルを作成し、vj列にidx_vjという名前のJSON配列インデックスを作成します。
CREATE TABLE json(
id INT,
vj JSON,
INDEX idx_vj(vj->'$[*]')
)
DISTRIBUTED BY HASH(id);
JSON配列インデックスを作成および変更する方法の詳細については、JSONインデックスの「JSON配列インデックスの作成」セクションおよび「ALTER TABLE」の「JSON配列インデックス」セクションをご参照ください。
よくある質問
列の属性と制約
自動インクリメント列は常に1から始まりますか? すべての値は一意ですか?
自動インクリメント列の値は順番にインクリメントされず、常に1から始まるとは限りません。 ただし、自動インクリメント列のすべての値は一意です。
配布キー、パーティションキー、およびライフサイクル
配布キーとパーティションキーの違いは何ですか?
分配キーはシャーディングで使用されます。 テーブル内のデータは、配布キー値のハッシュ値に基づいて異なるシャードに分散されます。 パーティションキーは、パーティション分割で使用されます。 シャード内では、パーティションキーの値に基づいて、データがさまざまなパーティションに分散されます。 次の図は、シャーディングとパーティションの仕組みを示しています。
テーブルを作成するときに配布キーを指定する必要がありますか?
パーティションテーブルを作成するときに、配布キーを指定する必要はありません。 プライマリキーを指定し、配布キーを指定しない場合、AnalyticDB for MySQLは自動的にプライマリキーを配布キーとして使用します。 プライマリキーまたは配布キーを指定しない場合、AnalyticDB for MySQLは自動的に __adb_auto_id__
列をテーブルに追加し、その列を配布キーおよびプライマリキーとして使用します。
レプリケートされたテーブルを作成するときに、配布キーを指定する必要はありません。 ただし、DISTRIBUTED BY BROADCASTを使用して、AnalyticDB for MySQLクラスターの各ストレージノードにデータの完全なコピーを格納するように指定する必要があります。
クラスターの仕様を変更すると、シャードの数が影響を受けますか。
いいえ、シャードの数はクラスター仕様の変更の影響を受けません。
テーブルのパーティション情報を照会するにはどうすればよいですか。
次のステートメントを実行して、テーブルのパーティション情報を照会できます。
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 size of the partition index.
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;
パーティションテーブルを作成した後にパーティション情報を表示できないのはなぜですか?
次の理由により、パーティションテーブルの作成後にパーティション情報を表示できません。
テーブルにはデータが書き込まれません。 テーブルを作成するときは、パーティションキーを指定してパーティション分割ルールを設定するだけです。 パーティショニングは、パーティションキーの値に基づいて実行されます。 テーブルにデータが書き込まれていない場合、パーティションキー値は空であり、パーティションは作成されません。
パーティションのビルドジョブは完了していません。 パーティションはリアルタイムで作成されません。 テーブルのBUILDジョブが完了した後にのみ、パーティション情報を表示できます。
解決策:
テーブルにデータを書き込み、BUILDジョブが完了するのを待ちます。
説明 BUILDジョブの詳細およびBUILDジョブのステータスの照会方法については、「BUILD」をご参照ください。
パーティションキーでサポートされているデータ型は何ですか。
パーティションキーには、数値、日時、または数値を指定する文字列のデータ型を指定できます。 他のデータタイプは、データ書き込みエラーを引き起こす可能性がある。
次のエラーメッセージは、書き込まれたパーティションキー値がデータ型の要件を満たしていないことを示します: パーティション形式関数エラー
。
PARTITION BY VALUE(FROM_UNIXTIME(col, 'format')) などの関数を使用してパーティションキーを指定できますか?
いいえ、他の関数を使用してパーティションキーを指定することはできません。 パーティションキーは、partition by VALUE(column_name) およびPARTITION BY VALUE(date_format(column_name, 'format')) のいずれかの関数を使用してのみ指定できます。 他の関数を使用すると、エラーが発生します。
パーティションのライフサイクルを照会するにはどうすればよいですか。
SHOW CREATE TABLE <table_name>
文を実行して、パーティションのライフサイクルを表示できます。 返された結果にパーティションのライフサイクルが表示されます。
LIFECYCLEの値を30に設定することで、データを30日間だけ保持するように設定してから30日以上保存されたデータを照会できるのはなぜですか。
次の理由により、30日以上保存されたデータをクエリできます。
特定のパーティションは期限切れになり、削除されていません。 期限切れのパーティションは、テーブルのBUILDジョブが完了するまで削除されません。
シャードレベルのパーティションライフサイクル管理を使用するV3.2.1.1より前のAnalyticDB For MySQLクラスターの場合、テーブル内のシャードに含まれるパーティションは、lifecycle nパラメーターで指定された数よりも少なくなります。 この問題は、V3.2.1.1以降のAnalyticDB for MySQLクラスターで作成されたテーブルでは発生しません。
根本原因:
データは一貫して同じシャードに書き込まれません。 データが日付によって分割されると仮定する。 シャード1はパーティション20231201〜20231230を含み、シャード2はパーティション20231202〜20231231を含む。 両方のシャードに30個のパーティションがあるため、両方のシャードのパーティションが保持されます。このパーティションは、LIFECYCLE nパラメーターで指定された値30を超えません。 したがって、パーティション20231201のデータを20231231に照会できます。
新しいデータが長時間テーブルに書き込まれることはありません。 データが日付によって分割されると仮定する。 Shard 1には、パーティション20231201、20231202、20231203、および20231204が含まれます。 新しいデータはパーティションに書き込まれません。 この場合、Shard1には4つのパーティションしかありません。これは、LIFECYCLE nパラメーターで指定された値30を超えません。 したがって、パーティションは削除されず、パーティション20231201でデータを照会できます。
期限切れのパーティションのデータはすぐに削除されますか?
いいえ、パーティションはリアルタイムで作成または削除されません。 期限切れのパーティションは、テーブルのBUILDジョブが完了するまで削除されません。
インデックス
テーブルのクラスタ化インデックスをクエリするにはどうすればよいですか。
SHOW CREATE TABLE
ステートメントを実行して、テーブルで指定されたクラスター化インデックスを照会できます。
AnalyticDB for MySQLは一意のインデックスをサポートしていますか?
AnalyticDB for MySQLは一意のインデックスをサポートしていません。 ただし、AnalyticDB for MySQLのテーブルのプライマリキーインデックスは一意のインデックスであり、プライマリキーの値が一意であることを保証します。
列指向ストレージ
TABLE_PROPERTIES='{"format":"columnstore"}' の構文は、テーブル作成ステートメントで何を意味しますか?
TABLE_PROPERTIES='{"format":"columnstore"}'
は、ストレージエンジンが列指向ストレージを使用することを指定します。 テーブルの作成時に構文を変更しないでください。
その他
テーブルを作成した後、ALTER TABLEステートメントを使用して何を変更できますか?
ALTER TABLEステートメントを実行して、次の変更を加えることができます。
テーブルの作成後は、その他の変更はできません。 詳細については、「ALTER TABLE」をご参照ください。
各クラスターにはいくつのテーブルを作成できますか。
AnalyticDB for MySQLクラスターごとに作成できるテーブルの最大数には、次の制限が適用されます。
1 ~ 20個のノードグループを持つ、予約モードの各Data Warehouse Editionクラスターに対して作成できるテーブルの最大数: 80000/(シャード数 /ノードグループ数)
。 数式では、[シャード数 /ノードグループ数]
の結果を切り上げる必要があります。
ノードグループを追加して、作成できる内部テーブルの最大数を増やすことができます。 詳細については、「Data Warehouse Editionクラスターのスケール」をご参照ください。
エラスティックモードで各Data Warehouse Editionクラスターに作成できる内部テーブルの最大数: [80000/(シャード数 /EIU数)] × 2
数式では、[シャード数 /EIU数]
の結果を切り上げる必要があります。
エラスティックI/Oユニット (EIU) をスケールアウトして、作成できる内部テーブルの最大数を増やすことができます。 詳細については、「エラスティックI/Oリソースのスケールアウト」をご参照ください。
Data Lakehouse Editionクラスターごとに作成できる内部テーブルの最大数: [80000/(シャード数 /予約済みストレージリソースを24 ACUで割った量)] × 2
予約済みストレージリソースをスケールアップして、作成できる内部テーブルの最大数を増やすことができます。 詳細については、「Data Lakehouse Editionクラスターのスケール」をご参照ください。
作成できる外部テーブルの最大数 エラスティックモードのData Lakehouse EditionクラスターまたはData Warehouse Editionクラスター: 500,000。
説明 シャードの数を照会するには、SELECT COUNT(1) FROM information_schema.kepler_meta_shards;
ステートメントを実行します。 シャードの数を増減することはできません。 シャーディングの詳細については、「Terms」トピックの「shard」セクションを参照してください。
AnalyticDB for MySQLのデフォルトの文字セットは何ですか?
AnalyticDB for MySQLはデフォルトの文字セットとしてUTF-8を使用します。これはMySQLのutf8mb4文字セットに相当します。 他の文字セットはサポートされていません。
一般的なエラーと解決策
パーティション番号は0より大きい必要があります
原因: パーティションキーは指定しましたが、パーティションライフサイクルは指定しませんでした。
例:
CREATE TABLE test (
id INT COMMENT '',
name VARCHAR(10) COMMENT '',
PRIMARY KEY (id, name)
)
DISTRIBUTED BY HASH(id) PARTITION BY VALUE(name);
解決策: テーブル作成ステートメントでパーティションのライフサイクルを指定します。 例:
CREATE TABLE test (
id INT COMMENT '',
name VARCHAR(10) COMMENT '',
PRIMARY KEY (id, name)
)
DISTRIBUTED BY HASH(id) PARTITION BY VALUE(name) LIFECYCLE 30;
説明 このエラーは、V3.2.1.0より前のAnalyticDB for MySQLクラスターでのみ発生します。
許可された204800パーティションのみ、既存のパーティションの数=>196462
原因: クラスター内のパーティション数がAnalyticDB for MySQLの102,400の上限を超えています。
次のステートメントを実行して、クラスター内のパーティション数を照会します。
SELECT count(partition_id)
FROM information_schema.kepler_partitions
WHERE partition_id > 0;
解決策: パーティションの粒度を増やします。たとえば、粒度を日から月に変更します。 パーティションの粒度を変更する方法については、「ALTER table」トピックの「テーブルのパーティション関数の形式の変更」をご参照ください。
パーティション列 'XXX' がプライマリインデックスに見つかりません=> [YYY]
原因: プライマリキーに配布キーまたはパーティションキーが含まれていません。
例:
CREATE TABLE test (
id INT COMMENT '',
name VARCHAR(10) COMMENT '',
PRIMARY KEY (id)
)
DISTRIBUTED BY HASH(id) PARTITION BY VALUE(name) LIFECYCLE 30;
このエラーは、主キーまたは配布キーを指定しない場合にも発生します。 テーブルの作成時にプライマリキーまたは配布キーを指定しない場合、AnalyticDB for MySQLは自動的に __adb_auto_id__
列をテーブルに追加し、その列をプライマリキーおよび配布キーとして使用します。 この場合、主キーには __adb_auto_id__
列のみが含まれ、パーティションキーは含まれません。 したがって、このエラーが発生します。
例:
CREATE TABLE test (
id INT COMMENT '',
name VARCHAR(10) COMMENT ''
)
PARTITION BY VALUE(name) LIFECYCLE 30;
解決策: 主キーにパーティションキーを含めます。
SemanticException: 5000テーブルのみ許可
原因: クラスター内のテーブル数がAnalyticDB for MySQLの上限を超えています。 各クラスターに作成できるテーブルの最大数は、クラスターのエディションと仕様によって異なります。 さまざまな種類のクラスターに対して作成できるテーブルの最大数の制限については、「制限」をご参照ください。
解決策:
不要なテーブルを削除します。
複数のテーブルを1つにマージします。
unsigned expr not supported
原因: AnalyticDB for MySQLは、符号なし番号をサポートしていないため、UNSIGNED属性をサポートしていません。
解決策: テーブル作成ステートメントの列にUNSIGNED属性を指定しないでください。 代わりに、ビジネスコードに負でない値の制約を実装する必要があります。