テーブルのデータ配布戦略
次の図では、シャーディング、パーティション分割、クラスター化インデックスなど、テーブルを作成する前に理解する必要がある概念について説明します。
構文
CREATE TABLE [存在しない場合] table_name
({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'colum_comment']
| table_constraints}
[, ... ])
[table_attribute]
[partition_options]
[storage_policy]
[block_size]
[エンジン]
[rt_engine]
[table_properties]
[AS query_expr]
[コメント 'テーブル_コメント']
column_attributes:
[DEFAULT {定数 | CURRENT_TIMESTAMP}]
[AUTO_INCREMENT]
column_constraints:
[{NOT NULL | NULL} ]
[主要なキー]
table_constraints:
[{INDEX | KEY} [index_name] (column_name,...)]
[FULLTEXT [インデックス | キー] [index_name] (column_name) [index_option]] [,...]
[PRIMARY KEY [index_name] (column_name,...)]
[CLUSTERED KEY [index_name] (column_name,...)]
[[シンボル]] 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=値
エンジン:
エンジン='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
AUTO_INCREMENT列を定義します。 データ型はBIGINT
である必要があります。
AnalyticDB for MySQLは、AUTO_INCREMENT列に一意の値を割り当てます。 ただし、これらの値は連続的に増加しず、1から開始することもできません。
column_constraints (NOT NULL | PRIMARY KEY)
NOT NULL
NULL
値を保持できないNOT NULL
列を定義します。 NULL
またはnot NULL
として定義された列は、NULL
値を保持できます。
主要なキー
主キーを定義します。 column_constraintsでは、プライマリキーとして定義できる列は1つだけです。 たとえば、id BIGINT NOT NULL primary key
で主キーとしてid列を定義します。 主キーに複数の列を含めるには、table_constraintsで複合主キーを定義します。
table_constraints (インデックス)
AnalyticDB for MySQLは、INDEX、PRIMARY KEY、CLUSTERED KEY、FOREIGN KEY、FULLTEX 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操作を実行することはできません。
主キーが定義されていない場合、次のことが起こります。
テーブルの作成後は、主キー列の追加、削減、または変更はできません。
ヒント: 数値型の列を主キーとして使用し、パフォーマンスを向上させるために主キー列の数を最小限に抑えることをお勧めします。
説明 列が多すぎる主キーは、次の悪影響を与える可能性があります。
クラスターキー
クラスター化インデックスを定義します。 パーティションレベルで定義されるクラスタ化インデックスは、データが格納される物理的な順序を決定します。 パーティション内のデータは、クラスタ化インデックスのキー値に基づいてソートされた順序で格納されます。 クラスタ化インデックスの同じまたは類似のキー値を有するデータレコードは、同じまたは隣接するデータブロックに格納される。 範囲クエリまたは等価クエリでは、クラスタ化インデックスを使用すると、ディスクI/Oを削減し、データ読み取りを高速化できます。 これは、クエリ条件がクラスタ化インデックス列と同じである場合、ストレージエンジンが連続したデータブロックを読み取ることができるためです。
該当するシナリオ:
クラスター化されたインデックスは、範囲クエリと等価クエリでうまく機能します。 範囲または等価クエリの条件で一般的に使用される列は、理想的なクラスタ化インデックス列です。
クエリ条件がクラスタ化インデックス列と一致または部分的に一致すると、読み取り効率が大幅に向上します。 たとえば、SaaSユーザーは通常、自分のデータのみをクエリするため、ユーザーIDをクラスター化インデックスとして設定すると、同じユーザーIDを持つレコードが同じまたは連続したデータブロックに継続的に保存され、データの読み取りが容易になります。
使用法ノート:
クラスター化されたインデックスはテーブルごとに1つだけです。
クラスター化インデックスは、clustered KEYインデックス (id)
のように単一の列に作成することも、CLUSTERED KEYインデックス (id,name)
のように複数の列に作成することもできます。 クラスタ化インデックスキーに複数の列が含まれる場合、データレコードはまずクラスタ化インデックス定義の最初の列でソートされます。 最初の列の値が同じ場合、データは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: (オプション) フルテキストインデックスのアナライザーとカスタム辞書を指定します。
外国キー
外部キーインデックスを定義します。 外部キーインデックスは、不要な結合を排除するために使用されます。 詳細については、「プライマリキーと外部キーの制約を使用して不要な結合を排除する」をご参照ください。
構文とパラメータ
サポートされているバージョン
AnalyticDB for MySQLクラスターのバージョンは3.1.10以降である必要があります。
構文: [[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> のいずれかのタイプである必要があります。 その次元も指定する必要があります。 たとえば、配列 <float> 型の4次元ベクトル列を作成するには、feature array<float>(4)
という構文を使用します。
index_option: ベクトルインデックスの属性。
algorithm
: ベクトル間の距離を計算するための式に基づくアルゴリズム。 有効な値はHNSW_PQのみです。これは、テーブルごとに100万から1,000万のレコードを持つ中規模のデータセットに適しています。 このアルゴリズムは、ベクトル次元に敏感である。
dis_function
: ベクトル間の距離を計算するために使用される式。 唯一の有効な値はSquaredL2です。 計算式:(x1-y1)^ 2 +(x2-y2)^ 2 +...
.
JSONインデックス
JSONインデックスを定義します。 詳細は、「JSONインデックス」をご参照ください。
構文とパラメータ
サポートされているバージョン:
構文: [INDEX [index_name] (column_name | column_name->'$.json_path'.)]
パラメータ:
table_attribute (配布キー)
table_attributeは、テーブルが標準テーブルかディメンションテーブルかを決定します。
DSTRIBUTED BY HASH: テーブルを標準テーブルとして定義します。 標準テーブルは、クエリを実行する際に分散データベースの利点を最大限に活用することによって、クエリ効率を向上させることができる。 標準テーブルには、通常、1,000万から100兆の範囲のかなりの量のデータを保存できます。
DISTRIBUTED BY BROADCAST: テーブルをディメンションテーブルとして定義します。 ディメンションテーブルは、クラスターのすべてのシャードにデータのコピーを格納します。 したがって、ディメンションテーブルは、大量のデータには適していない可能性があります。 ディメンションテーブルに20,000個以下のレコードを格納することをお勧めします。
HASHによる分布 (column_name,...)
テーブルの配布キーを定義します。 配布キーが定義されたテーブルは標準テーブルです。 AnalyticDB for MySQLはハッシュ値を計算し、それに基づいてデータレコードをシャードに分割します。 シャーディングは、スケーラビリティとクエリのパフォーマンスを向上させます。
概要
使用上の注意
テーブルの作成時に配布キーが定義されていない場合、AnalyticDB for MySQLは、テーブルにプライマリキーがあるかどうかに応じて、配布キーを追加する場合があります。
テーブルを作成した後は、その配布キー列を追加、削減、または変更することはできません。 配布キーを変更するには、目的の配布キーを使用してテーブルを作成し、データを新しいテーブルに移行する必要があります。 詳細については、「ALTER TABLE」をご参照ください。
ヒント:
分散キーの列数を最小化して、複雑なクエリでの使用を最大化します。
トランザクションID、デバイスID、ユーザーID列、自動インクリメント列など、クエリ条件に一般的に表示され、均等に分散された値を分散キーとして持つ列を選択します。 ただし、クエリ条件が少数の列値に制限されている場合、このような列は理想的な配布キーではありません。 例えば、列Aが均等に分散された値を有するが、クエリ条件が常にA=3であると仮定する。 この場合、列Aを配布キーとして設定すると、データベースにホットスポットが発生するため、回避する必要があります。
結合に頻繁に使用される列を配布キーとして指定します。 このようにして、2つの結合されたテーブルの同じ配布キー値を持つデータは、結合操作が実行される同じシャードに配布されます。 シャード間でデータを転送する必要がないため、データの再配布が最小限に抑えられ、クエリのパフォーマンスが向上します。 たとえば、ユーザーの注文履歴のクエリを使用する場合は、customer_id
を配布キーとして設定します。
日付、時刻、またはtimestamp列を配布キーとして指定しないでください。 このようなキーは、書き込み動作中にデータスキューを引き起こし、書き込みパフォーマンスに影響を与えます。 さらに、クエリは、クエリ条件の一部として、最近の日または月などの特定の日付または時間範囲を使用することが多いので、同じノードに同じ日付、時間、またはタイムスタンプを有するレコードを格納することは、クエリが分散アーキテクチャの利点を十分に利用することを妨げ得る。 ただし、このような列は理想的なパーティションキーです。 詳細については、「partition_options (Partition key and lifecycle) 」をご参照ください。
データモデリング診断機能を使用して、列が適切な配布キーであるかどうか、およびデータスキューが存在するかどうかを確認できます。 詳細については、「データモデリング診断」をご参照ください。
ブロードキャストで分配
ディメンションテーブルを定義します。 ディメンションテーブルの場合、そのデータの完全なコピーがクラスタの各ノードに格納されます。 そのため、ディメンションテーブルに大量のデータを格納しないようにすることをお勧めします。
利点: テーブル結合を伴うクエリを実行する場合、ディメンションテーブル内のデータをノード間で転送する必要はありません。 その結果、同時実行性の高いシナリオでネットワーク送信オーバーヘッドを大幅に削減でき、クラスターの安定性が向上します。
デメリット: ディメンションテーブルで (INSERT、UPDATE、またはDELETE操作を通じて) データ変更が発生すると、これらの変更はクラスタのすべてのノードにブロードキャストされ、データの一貫性が確保されます。 したがって、ディメンションテーブルでのデータの追加、削除、および変更を最小限に抑えることをお勧めします。
partition_options (パーティションキーとライフサイクル)
大量のデータを含むシャードのパーティションキーを定義して、データのフィルタリングを高速化し、クエリのパフォーマンスを向上させます。
なぜパーティション化?
パーティショニングは、次の理由により、データのフィルタリングを高速化し、クエリのパフォーマンスを向上させます。
パーティションプルーニングによりクエリ速度が向上します。 パーティションプルーニングを有効にすると、システムは実行中のクエリに関連するレコードのみをスキャンします。
インデックススキャンのパフォーマンスを向上させます。 非パーティション化テーブルに格納されるデータレコードの数が50百万のような過剰な場合、インデックススキャンは効率が低いという問題がある。 このようなテーブルがパーティション分割されると、パーティションごとにインデックスが作成されるため、インデックススキャンがはるかに効率的になります。
データ固化効率を向上させます。 データの固化中、パーティションとインデックスが作成され、冗長データが削除されます。 新しいインデックスは、凝固プロセスが完了した後にのみ機能し始めます。 パーティションなしでは、テーブル全体がスキャンされてリアルタイムデータが固まります。 テーブルに含まれるレコードが多いほど、このプロセスに時間がかかり、新しいインデックスが有効になるまでの時間が遅延します。 この遅延はクエリのパフォーマンスに悪影響を及ぼします。 しかし、パーティションでは、変更されたデータを持つパーティションのみがデータ固化の目的でスキャンされ、データ固化効率が向上します。
パーティショニングを使用すると、データライフサイクルを管理できます。
パーティションを使用すると、ホットデータとコールドデータに異なるストレージポリシーを実装できます。
パーティション分割とパーティションのライフサイクルの仕組み
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 representation of a numberのデータ型を使用できます。
DATE_FORMAT(column_name, 'format')
: パーティション分割前に日付列を指定の日付形式に変換するために使用されます。 指定された日付形式には、年、月、日のみを含めることができます: % Y、% y、% Y % m、% y % m、% Y % m % d、または % y % m % d。 テーブルの作成後に形式を変更できます。 詳細については、「ALTER TABLE」をご参照ください。
使用上の注意
LIFECYCLE N
はPARTITION BY
と共に定義する必要があります。 そうしないと、エラーが発生します。 パーティションを定義しない場合、データは自動的に削除されません。
テーブルの作成後、パーティションキーを追加することはできません。また、パーティションキー列を追加、削減、または変更することもできません。 パーティションキーを追加または変更するには、目的のパーティションキーを使用してテーブルを作成し、新しいテーブルにデータを移行します。 詳細については、「ALTER TABLE」をご参照ください。
ヒント:
パーティションキーとしてdatetime列を使用することを推奨します。
パーティションが大きすぎたり小さすぎたりすると、読み書きのパフォーマンスに影響を与える可能性があり、クラスターの安定性にも影響を与える可能性があります。 推奨されるパーティションサイズと、パーティションサイズが適切かどうかについては、「データモデリング診断」をご参照ください。
履歴パーティションのデータを頻繁に更新しないでください。 パーティションキーが適切でない場合は、変更を検討してください。
LIFECYCLE N
LIFECYCLEは、PARTITION BY
とともに設定する必要があります。 N個のパーティションを各シャードに保持することで、パーティションのライフサイクルを管理します。 AnalyticDB for MySQLは、パーティションキー値に基づいてパーティションを降順に配置します。 最初のN個のパーティションを保持し、残りを削除します。
たとえば、PARTITION BY VALUE (DATE_FORMAT(date, '% Y % m % d')) LIFECYCLE 30
は、パーティション分割中に、日付列のデータがyyyyMMdd形式に変換され、最大30個のパーティションが保持されることを示します。 1日目から30日目までのデータが、パーティション20231201からパーティション20231230までの対応するパーティションに書き込まれると仮定する。 データがパーティション20231231に書き込まれる31日目に、最大30個のパーティションを保持できるため、パーティションキー値が最小のパーティション (この場合はパーティション20231201) が自動的に削除されます。
STORAGE_POLICY (ストレージポリシー)
Data Lakehouse EditionとData Warehouse EditionのクラスターエディションのElastic Modeの両方で、データストレージポリシーを指定できます。 ストレージポリシーは、読み書きのパフォーマンスとストレージコストが異なります。
有効値:
hot (デフォルト): ホットストレージ テーブル全体のすべてのパーティションのデータはSSDに保存されます。 ホットストレージは最高の読み取り /書き込みパフォーマンスを備えていますが、ストレージコストが最も高くなります。
コールド: コールドストレージ。 データはOSS (Object Storage Service) に保存されます。 ホットストレージと比較して、コールドストレージは読み取り /書き込みパフォーマンスが低くなりますが、最も安価なオプションです。
混合: ホットとコールドのストレージ戦略の組み合わせ、階層ストレージとも呼ばれます。 頻繁にアクセスされるデータ (ホットデータ) をSSDに保存し、ほとんどアクセスされないデータ (またはコールドデータ) をOSSに保存することで、ストレージコストを削減し、クエリのパフォーマンスを保証します。 ストレージポリシーを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で定義されます。 block_sizeはI/O操作の数に影響し、さまざまなクエリタイプに合わせて調整できます。 たとえば、block_sizeを小さくすると、ポイントクエリの読み取り効率を高めることができます。
デフォルト値:
次元のテーブル: 4,096
AnalyticDB for MySQL Basic Editionおよびクラスター版のElastic Mode (32コア未満のコンピューティングリソースを使用): 8,192
その他: 32,760。 block_sizeのデフォルト値が32,760の場合、SHOW CREATE TABLE
ステートメントの実行時にblock_sizeは表示されません。
重要 列状ストレージに慣れていない場合は、block_sizeのデフォルト値を使用できます。
エンジン (ストレージエンジン)
AnalyticDB for MySQLの内部テーブルのストレージエンジンのタイプを指定します。 履歴データ分析に使用されます。
有効値:
XUANWU: テーブル作成時にENGINEが明示的に設定されていない場合のデフォルト値。
XUANWU_V2: XUANWU上に構築された次世代ストレージエンジン。 AnalyticDB for MySQLクラスターのマイナーバージョンは3.2.0以降である必要があります。 事前にXUANWU_V2エンジンを有効にする必要があります。 詳細については、「XUANWU_V2 engine」をご参照ください。
説明 マイナーバージョンが3.1.9.5より前のクラスターで内部テーブルを作成するときにENGINE='XUANWU'
を明示的に定義する場合は、table_properties='{"format":"columnstore"}'
も明示的に設定する必要があります。 それ以外の場合、テーブルは作成できません。
XUANWUストレージエンジンの詳細については、「XUANWU分析ストレージエンジン」をご参照ください。
例
パーティションとパーティションライフサイクルの定義
customerという名前の標準テーブルを作成し、複合主キーとしてlogin_time
、customer_id
、phone_num
、配布キーとしてcustomer_id
、パーティションキーとしてlogin_time
を定義します。 パーティションのライフサイクルを30に設定します。
すべてのパーティションは、パーティションキーlogin_time
の値によって降順にソートされます。 最初の30のパーティションのみが保持されます。 データが31番目のパーティションに書き込まれると、最も古いパーティションが自動的に削除されます。
login_timeが初日の20231201から始まり、2日目の20231202から30日目の20231230まで毎日増分すると仮定します。 login_timeの値が20231231のデータがデータベースに書き込まれる31日目に、login_timeの値が最も小さいパーティション (パーティション20231201) が自動的に削除されます。 このようにして、過去30日間のデータのみが保持されます。
テーブルの顧客を作成する (
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 'citizen ID' 、
home_address VARCHAR NOT NULL COMMENT 'home address' 、
office_address VARCHAR NOT NULL COMMENT 'オフィスアドレス' 、
年齢INT NOT NULL COMMENT 'age' 、
login_time TIMESTAMP NOT NULL COMMENT 'ログオン時間' 、
PRIMARYキー (login_time,customer_id,phone_num)
)
ハッシュによる分布 (customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '% Y % m % d')) LIFECYCLE 30
コメント '顧客情報テーブル';
配布キーが定義されていないテーブルの作成
主キーが定義されている
次のステートメントでは、主キーは定義されていますが、配布キーは定義されていません。 この場合、AnalyticDB for MySQLは自動的にプライマリキーを配布キーとして使用します。
CREATE TABLE orders (
order_id BIGINT NOT NULL COMMENT 'order ID' 、
customer_id BIGINT 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日付NOT NULL COMMENT 'date' 、
PRIMARYキー (order_id,order_date)
);
テーブル構造を照会すると、主キー列order_idとorder_dateが配布キーとして使用されていることがわかります。
SHOW CREATE TABLEオーダー;
--------- + ----------------------------------------------------------------------------------------------------------------------------------------------- +
| テーブル | テーブルの作成 |
+ --------- + ----------------------------------------------------------------------------------------------------------------------------------------------- +
| orders | CREATE TABLE 'orders' ( |
| | 'order_id' bigint NOT NULL COMMENT 'order ID' 、|
| | 'customer_id' int NOT NULL COMMENT'customerID' 、|
| | 'order_status' varchar (1) NOT NULL COMMENT 'order status', |
| | 'total_price' decimal (15, 2) NOT NULL COMMENT 'price', |
| | 'order_date' date NOT NULL COMMENT'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行セット (0.04秒)
主キーが定義されていません
次のステートメントでは、主キーも配布キーも定義されていません。 AnalyticDB for MySQLは、__adb_auto_id__
列をプライマリおよび配布キーとして追加します。
テーブルのorders_newを作成 (
order_id BIGINT NOT NULL COMMENT 'order ID' 、
customer_id BIGINT 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日付NOT NULL COMMENT 'date' 、);
テーブル構造を照会すると、__adb_auto_id__という名前の自動インクリメント列が自動的にテーブルに追加されることがわかります。 この列は、テーブルの主キーおよび配布キーとして機能します。
SHOW CREATE TABLE orders_new;
------------ + ----------------------------------------------------------------------------------------------------------------------------------------------- +
| テーブル | テーブルの作成 |
+ ------------- + ----------------------------------------------------------------------------------------------------------------------------------------------- +
| 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'customerID' 、|
| | 'order_status' varchar (1) NOT NULL COMMENT 'order status', |
| | 'total_price' decimal (15, 2) NOT NULL COMMENT 'price', |
| | 'order_date' date NOT NULL COMMENT'date', |
| | PRIMARY KEY ('__adb_auto_id__') |
| |) ハッシュによる分布 ('__adb_auto_id__') INDEX_ALL='Y' STORAGE_POLICY='HOT' ENGINE='XUANWU' TABLE_PROPERTIES='{"format":"columnstore"}' |
+ ------------- + ----------------------------------------------------------------------------------------------------------------------------------------------- +
1行セット (0.04秒)
パーティションキーが定義されていないテーブルの作成
サプライヤテーブルを作成し、supplier_id
を自動インクリメント列と配布キーとして定義します。 テーブルは、supplier_id
のハッシュ値に基づいてシャードされます。
CREATE TABLEサプライヤー (
supplier_id BIGINT AUTO_INCREMENT PRIMARYキー、
supplier_name VARCHAR,
アドレスINT、
電話VARCHAR
)
HASHによる分布 (supplier_id);
ストレージポリシーの定義
コールドストレージポリシーの定義
テーブルアイテムを作成 (
order_id BIGINT NOT NULL、
item_id INT NOT NULL,
数量DECIMAL(15、2) NOT NULL、
ディスカウントDECIMAL(15、2) NOT NULL、
shipdate日付NOT NULL,
PRIMARYキー (order_id,item_id,shipdate)
)
ハッシュによる分布 (item_id)
PARTITION BY VALUE(date_format(shipdate, '% Y % m')) LIFECYCLE 200
STORAGE_POLICY='COLD';
ホットストレージポリシーの定義
テーブルアイテムを作成 (
order_id BIGINT NOT NULL、
item_id INT NOT NULL,
数量DECIMAL(15、2) NOT NULL、
ディスカウントDECIMAL(15、2) NOT NULL、
shipdate DECIMAL NOT NULL、
PRIMARYキー (order_id,item_id,shipdate)
)
ハッシュによる分布 (item_id)
PARTITION BY VALUE(date_format(shipdate, '% Y % m')) LIFECYCLE 200
STORAGE_POLICY='HOT';
階層ストレージポリシーを定義し、ホットパーティションの数を16に設定します
テーブルアイテムを作成 (
order_id BIGINT NOT NULL、
item_id INT NOT NULL,
数量DECIMAL(15、2) NOT NULL、
ディスカウントDECIMAL(15、2) NOT NULL、
shipdate日付NOT NULL,
PRIMARYキー (order_id,item_id,shipdate)
)
ハッシュによる分布 (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 (コンテンツ) 、
主要なキー (id)
)
ハッシュによる分配 (id);
フルテキストインデックスの作成および変更方法の詳細については、「フルテキストインデックスの作成」をご参照ください。
フルテキスト検索の詳細については、「フルテキスト検索」をご参照ください。
ベクトルインデックスの定義
配列 <smallint> 型のshort_featureと配列 <float> 型のfloat_featureであるベクトル列を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配列 <smallint>(4) 、
float_feature配列 <float>(4),
ann index short_feature_index(short_feature) 、
ann index float_feature_index(float_feature) 、
主要なキー (xid、cid、vid)
)
ハッシュによる分配 (xid) 値による分配 (cid) ライフサイクル4;
ベクトルインデックス作成とベクトル検索の詳細については、「」をご参照ください。
外部キーインデックスの定義
store_returns
という名前のテーブルを作成します。 FOREIGN KEY
構文を使用して、store_returnsテーブルのsr_item_sk
列をcustomer
テーブルの主キー列customer_id
に関連付けます。
CREATE TABLE store_returns (
sr_sale_id BIGINT NOT NULL PRIMARYキー、
sr_store_sk BIGINT、
sr_item_sk BIGINT NOT NULL、
FOREIGN KEY (sr_item_sk) REFERENCES customer (customer_id)
);
よくある質問
列の属性と制約
自動インクリメント列は常に1から始まりますか? すべての値は一意ですか?
自動インクリメント列の値は連続的に増加しません。また、1から開始できません。 ただし、自動インクリメント列のすべての値は一意です。
配布キー、パーティションキー、およびライフサイクル
配布キーとパーティションキーの違いは何ですか?
配布キーは、データベースシャードを決定するために使用されます。 配布キーのハッシュ値に基づいて、データはシャードに分割されます。 パーティションキーは、シャード内のデータベースパーティションを決定するために使用されます。 次の図は、シャーディングとパーティションがどのように機能するかを示しています。
テーブルを作成するときに配布キーを定義する必要がありますか?
パーティションテーブルを作成するときに、配布キーを指定する必要はありません。 プライマリキーを定義し、配布キーを定義しない場合、AnalyticDB for MySQLはプライマリキーを配布キーとして使用します。 プライマリキーも配布キーも定義されていない場合、__adb_auto_id__
列が配布キーおよびプライマリキーとして自動的に生成されます。
ディメンションテーブルを作成するときに、配布キーを指定する必要はありません。 ただし、データの完全なコピーが各ノードに格納されることを示すDISTRIBUTED BY BROADCASTを設定する必要があります。
クラスター構成の変更はシャードの数に影響しますか?
いいえ。クラスター構成を変更しても、クラスター内のシャード数には影響しません。
パーティションに関する情報をクエリするにはどうすればよいですか?
パーティション情報を照会するには、次のステートメントを実行します。
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;
パーティションテーブルを作成した後、パーティション情報を表示できないのはなぜですか?
主な理由は2つあります。
データはテーブルに書き込まれていません。 テーブルの作成時にパーティションキーを定義すると、パーティション分割のルールのみが構成されます。 テーブルにデータがない場合、パーティションキーの値は空です。 したがって、パーティションは作成されません。
パーティションのビルドジョブが完了していません。 パーティションはリアルタイムで構築されません。 テーブルのBUILDジョブが完了した後にのみ、パーティションの情報を表示できます。
解決策:
テーブルにデータを書き込み、BUILDジョブが完了するのを待ち、パーティション情報を表示できます。
説明 ビルドジョブとその進行状況を照会する方法の詳細については、「ビルド」をご参照ください。
パーティションキーの有効なデータ型は何ですか?
パーティションキーは、数値、日時、または数値の文字列表現のデータ型を受け入れます。 他のデータタイプは、データ書き込みエラーを引き起こす可能性がある。
次のエラーメッセージは、入力データがパーティションキー列の必要なデータ型と一致しないことを示しています: パーティション形式関数エラー
。
DATE_FORMAT以外の関数を使用して、partition BY VALUE(FROM_UNIXTIME(col,'format')) などのパーティションキーを定義できますか?
いいえ、他の関数を使用してパーティションキーを指定することはできません。 パーティションキーは、partition by VALUE (列) とPARTITION BY VALUE(DATE_FORMAT (列、'format')) の2つのステートメントのいずれかを使用してのみ定義できます。 他の関数を使用するとエラーが発生します。
パーティションのライフサイクルを照会するにはどうすればよいですか。
SHOW CREATE TABLE <table_name>
コマンドを使用して、パーティションのライフサイクルを表示できます。 返される結果には、パーティションのライフサイクルが表示されます。
、LIFECYCLEの値を30に設定して、データを30日間だけ保持するように設定しました。 30日前に保存されたデータにアクセスできるのはなぜですか?
期限切れのデータはすぐに削除されますか?
いいえ。 パーティションはリアルタイムで作成または削除されません。 期限切れのパーティションは、テーブルのBUILDジョブが完了するまで削除されません。
インデックス
テーブルのクラスタ化インデックスをクエリするにはどうすればよいですか。
テーブル作成ステートメントSHOW CREATE table
を使用して、テーブルに定義されているクラスタ化インデックスを照会できます。
AnalyticDB for MySQLはUNIQUE INDEXをサポートしていますか?
いいえ、UNIQUE INDEXはAnalyticDB for MySQLでサポートされていません。 ただし、主キーインデックスは一意であり、主キー列に重複する値がないことを保証します。
柱状ストレージ
TABLE_PROPERTIES='{"format":"columnstore"}' の構文は、テーブル作成ステートメントで何を意味しますか?
TABLE_PROPERTIES='{"format":"columnstore"}'
は、ストレージエンジンがカラムストレージを使用することを示します。 テーブルの作成時にこの構文を変更しないでください。
偏見
テーブルの作成後、ALTER tableステートメントを使用して何を変更できますか?
ALTER TABLEでは、次の変更を行うことができます。
テーブルの作成後は、その他の変更はできません。 詳細については、「ALTER TABLE」をご参照ください。
クラスターにはいくつのテーブルを作成できますか。
AnalyticDB for MySQLクラスターのテーブルの最大数は次のとおりです。
Data Warehouse Edition in reserved modeクラスター (1 ~ 20ノードグループ): [80,000/(シャード数 /ノードグループ数)]
[シャード数 /ノードグループ数]
の結果は切り上げられます。
ノードグループを追加すると、内部テーブルの最大数を増やすことができます。 詳細については、「クラスターの設定の変更」をご参照ください。
Data Warehouse Edition in elastic modeクラスター: [80000/(シャード数 /EIU数)] × 2
[シャード数 /EIU数]
の結果は切り上げられます。
EIUまたはエラスティックI/Oリソースを追加すると、内部テーブルの最大数を増やすことができます。 詳細については、「elastic I/Oリソースのスケールアップまたはアウト」をご参照ください。
Data Lakehouse Editionクラスター: [80000/(シャード数 /予約済みストレージグループ数)] × 2
予約済みストレージリソースのグループは24 ACU (AnalyticDB compute unit) です。 48のACUを持つクラスターには、2つの予約済みストレージグループがあります。
ACUを追加すると、内部テーブルの最大数を増やすことができます。 詳細については、「Data Lakehouse Editionクラスターのスケール」をご参照ください。
Data Warehouse EditionクラスターおよびData Lakehouse Editionクラスターの外部テーブルの最大数: 500,000。
説明 シャードの数を照会するには、SELECT COUNT (1) FROM information_schema.kepler_meta_shards;
を使用します。 シャードの数を増減することはできません。 シャーディングの詳細については、「シャード」をご参照ください。
AnalyticDB for MySQLのデフォルトの文字セットは何ですか?
AnalyticDB for MySQLはデフォルトの文字セットとしてUTF-8を使用します。これはMySQLのutf8mb4文字セットに相当します。 他の文字セットはサポートされていません。
一般的なエラーメッセージ
パーティション番号は0より大きい必要があります
原因: パーティションは定義しましたが、パーティションのライフサイクルは定義しませんでした。
間違ったテーブル作成ステートメントは次のようになります。
CREATE TABLEテスト (
id INT COMMENT '',
名前VARCHAR(10) COMMENT ''、
主要なキー (id, name)
)
ハッシュによる分布 (id) 値による部分 (名前);
解決策: テーブル作成ステートメントでパーティションのライフサイクルを定義します。 以下に例を示します。
CREATE TABLEテスト (
id INT COMMENT '',
名前VARCHAR(10) COMMENT ''、
主要なキー (id, name)
)
ハッシュによって分配されます (id) PARTITION BY VALUE(name) LIFECYCLE 30;
許可された204800パーティションのみ、既存のパーティションの数=>196462
原因: クラスター内のパーティション数がAnalyticDB for MySQLの102,400の上限を超えています。
次の文を使用して、クラスター内のパーティション数を照会します。
SELECTカウント (partition_id)
FROM information_schema.kepler_partitions
WHERE partition_id > 0;
解決策: 日によるパーティショニングから月によるパーティショニングへの変更など、パーティショニングの粒度を調整します。 パーティション分割の粒度を変更する方法の詳細については、「ALTER TABLE: パーティション分割関数の形式を変更する」をご参照ください。
パーティション列 'XXX' がプライマリインデックスに見つかりません=> [YYY]
原因: プライマリキーに配布キーまたはパーティションキーが含まれていません。
間違ったクエリは次のようになります。
CREATE TABLEテスト (
id INT COMMENT '',
名前VARCHAR(10) COMMENT ''、
主要なキー (id)
)
ハッシュによって分配されます (id) PARTITION BY VALUE(name) LIFECYCLE 30;
このエラーは、主キーまたは配布キーを指定しない場合にも発生します。 プライマリキーと配布キーを指定せずにテーブルを作成すると、AnalyticDB for MySQLはプライマリキーと配布キーとして __adb_auto_id__
列を自動的に生成します。 この場合、主キーには __adb_auto_id__
のみが含まれ、配布キーは含まれないため、このエラーです。
ここに別の間違った声明があります:
CREATE TABLEテスト (
id INT COMMENT '',
名前VARCHAR(10) COMMENT''
)
値 (名前) LIFECYCLE 30;
解決策: 主キーにパーティションキーを含めます。
SemanticException: 5000テーブルのみ許可
原因: クラスター内のパーティション数がAnalyticDB for MySQLの102,400の上限を超えています。 テーブルの最大数は、製品のシリーズと仕様によって異なります。 詳細については、「クラスターごとに作成できるテーブルの最大数」をご参照ください。
解決策:
不要なテーブルを削除します。
複数のテーブルを1つにまとめます。
unsigned expr not supported
原因: AnalyticDB for MySQLはUNSIGNED属性をサポートしていません。つまり、符号なし番号はサポートしていません。
解決策: テーブル作成ステートメントのcolumn属性にUNSIGNEDを定義しないでください。 代わりに、ビジネスコードに負でない値の制約を課す必要があります。