AUTO INCREMENT とグローバル辞書を使用した COUNT(DISTINCT) とジョインの高速化
このトピックでは、AUTO INCREMENT 列とグローバル辞書を使用して COUNT(DISTINCT) 計算とジョインを高速化する方法について説明します。
ユースケース
-
シナリオ 1: 大量のデータ（小売や配送の注文など）で正確な重複排除を行う必要があるとします。しかし、重複排除のための列が STRING 型であるため、カウント時に最適なパフォーマンスを発揮しない可能性があります。例えば、
ordersテーブルのorder_uuid列は、注文 ID を表す STRING 型で、通常 32 から 36 バイトのサイズであり、UUID()や類似の関数で生成されます。この場合、SELECT count(DISTINCT order_uuid) FROM orders WHERE create_date >= CURDATE();のようなクエリで STRING 列order_uuidに対して COUNT(DISTINCT) を行うと、満足のいくパフォーマンスが得られないかもしれません。正確な重複排除のために INTEGER 列を使用することで、パフォーマンスが大幅に向上します。 -
シナリオ 2: ビットマップ関数を使用して多次元分析での正確な重複排除を高速化したい とします。
bitmap_count()関数は INTEGER 入力を必要としますが、重複排除する列が STRING 型の場合、bitmap_hash()関数を使用する必要があります。これにより、重複排除のカウントが近似的でやや低くなる可能性があり、クエリパフォーマンスが低下し、ストレージ要件が増加する可能性があります。これは、bitmap_hash()によって生成された INTEGER 値が、順次割り当てられた INTEGER 値と比較してより分散されているためです。 -
シナリオ 3: 注文の発注と支払いの間の時間が短い注文数をクエリする必要があるとします。この場合、注文の発注時間と支払い時間が異なるビジネスチームによって管理されている異なるテーブルに保存されている可能性があります。これらのテーブルを注文 ID に基づいてジョインし、注文を重複排除する必要があるかもしれません。例えば:
SELECT count(distinct order_uuid)
FROM orders_t1 as t1 JOIN orders_t2 as t2
ON t1.order_uuid = t2.order_uuid
WHERE t2.payment_time - t1.create_time <= 3600
AND create_date >= CURDATE();しかし、ジョインに STRING 型の
order_uuid列を使用するのは、INTEGER 列を使用するよりも効率が悪いです。
最適化アプローチ
上記のシナリオの問題に対処するために、最適化アプローチは、注文データをターゲットテーブルにロードし、STRING と INTEGER 値の間にマッピングを確立することを含みます。以降のクエリ分析は INTEGER 列に基づいて行われます。このアプローチは以下の段階に分けられます。
-
ステージ 1: グローバル辞書を作成し、STRING と INTEGER 値の間にマッピングを確立します。この辞書では、キー列は STRING 型で、値列は AUTO INCREMENT INTEGER 型です。データがロードされると、システムは各 STRING 値に対して自動的に一意の ID を生成し、STRING と INTEGER 値の間にマッピングを作成します。
-
ステージ 2: 注文データとグローバル辞書の間のマッピング関係をターゲットテーブルにロードします。
-
ステージ 3: 以降のクエリ分析で正確な重複排除やジョインのためにターゲットテーブルの INTEGER 列を使用し、パフォーマンスを大幅に向上させます。
-
ステージ 4: さらなるパフォーマンス最適化のために、INTEGER 列にビットマップ関数を使用して正確な重複排除を高速化できます。
ソリューション
v3.2.5 以前では、ステージ 2 は次の 2 つの方法で実装できました。
- 外部テーブルまたは内部テーブルを中間テーブルとして使用し、ロード前に辞書テーブルとジョインして対応する辞書 ID を取得します。
- 主キーテーブルを使用してデータロードを行い、その後、ジョイン操作を伴う UPDATE ステートメントを使用して辞書 ID を更新します。しかし、このデータロードプロセスは不便で、多くの制約があります。
v3.2.5 以降、StarRocks は dict_mapping() 関数を導入し、ターゲットテーブルの辞書 ID 列を dict_mapping() 式を使用して生成列として定義できるようになりました。以降のデータロードタスクは通常のデータロードのように処理され、ジョイン操作を伴う UPDATE ステートメントを使用して辞書 ID を書き込む必要はありません。データロード中に、システムは自動的に元のテーブルと辞書テーブルを関連付け、対応する辞書 ID を挿入します。これにより、テーブルタイプに関係なく、さまざまなロード方法をサポートし、グローバル辞書テーブルを使用したデータロードプロセスが大幅に簡素化されます。
ビジネスシナリオ
以下の例では、id と order_uuid の 2 列を含む 2 つの CSV ファイル batch1.csv と batch2.csv を使用します。
-
batch1.csv1, a1
2, a2
3, a3
11, a1
11, a2
12, a1 -
batch2.csv1, a2
2, a2
3, a2
11, a2
12, a101
12, a102
13, a102
プロセス
ステージ 1
グローバル辞書テーブルを作成し、CSV ファイルから注文 ID 列の値をロードして STRING と INTEGER 値の間にマッピングを確立します。
-
グローバル辞書として機能する主キーテーブルを作成します。主キー
order_uuid（STRING 型）と値列order_id_int（AUTO INCREMENT INTEGER 型）を定義します。備考dict_mapping関数は、グローバル辞書テーブルが主キーテーブルであることを要求します。CREATE TABLE dict (
order_uuid STRING,
order_id_int BIGINT AUTO_INCREMENT -- 各 order_uuid 値に自動的に ID を割り当てます。
)
PRIMARY KEY (order_uuid)
DISTRIBUTED BY HASH (order_uuid)
PROPERTIES("replicated_storage" = "true"); -
Stream Load を使用して、2 つの CSV ファイルから
order_uuid列を辞書テーブルdictのorder_uuid列にバッチロードします。列モードで部分更新を使用したことを確認してください。curl --location-trusted -u root: \
-H "partial_update: true" \
-H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid" \
-T batch1.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dict/_stream_load
curl --location-trusted -u root: \
-H "partial_update: true" \
-H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid" \
-T batch2.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dict/_stream_load
注意
次のステージに進む前にデータソースに新しいデータが追加された場合、すべての新しいデータを辞書テーブルにロードして、マッピングが存在することを確認する必要があります。
ステージ 2
辞書 ID 列を dict_mapping 属性で含むターゲットテーブルを作成します。注文データがターゲットテーブルにロードされると、システムは自動的に辞書テーブルと関連付け、対応する辞書 ID を挿入します。
-
CSV ファイルのすべての列を含むテーブル
dest_tableを作成します。また、STRING 型のorder_uuid列とマッピングする INTEGER 列order_id_int（通常は BIGINT）を定義し、dict_mapping列属性を持たせる必要があります。将来のクエリ分析はこのorder_id_int列に基づいて行われます。-- ターゲットテーブルで、STRING 型の列 `order_uuid` とマッピングする BIGINT 型の dict_mapping 列 `order_id_int` を定義します。
CREATE TABLE dest_table (
id BIGINT,
order_uuid STRING, -- この列は STRING 型の注文 ID を記録します。
batch INT comment '異なるバッチロードを区別するために使用されます',
order_id_int BIGINT AS dict_mapping('dict', order_uuid) -- 辞書 ID dict_mapping 列は `order_uuid` に対応します。
)
DUPLICATE KEY (id, order_uuid)
DISTRIBUTED BY HASH(id); -
Stream Load または他の利用可能な方法でターゲットテーブルにデータをロードします。
order_id_int列がdict_mapping属性を持っているため、システムはロード中にdictから辞書 ID を自動的に取得します。curl --location-trusted -u root: \
-H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid, batch=1" \
-T batch1.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dest_table/_stream_load
curl --location-trusted -u root: \
-H "format: CSV" -H "column_separator:," -H "columns: id, order_uuid, batch=2" \
-T batch2.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dest_table/_stream_load
ステージ 3
クエリ分析中に、INTEGER 列 order_id_int に対して正確な重複排除やジョインを行うことで、STRING 列 order_uuid を使用するよりもパフォーマンスが大幅に向上します。
-- BIGINT 型の order_id_int に基づく正確な重複排除。
SELECT id, COUNT(DISTINCT order_id_int) FROM dest_table GROUP BY id ORDER BY id;
-- STRING 型の order_uuid に基づく正確な重複排除。
SELECT id, COUNT(DISTINCT order_uuid) FROM dest_table GROUP BY id ORDER BY id;
ビットマップ関数を使用して正確な重複排除を高速化する こともできます。
ビットマップ関数を使用して正確な重複排除を高速化する
計算をさらに高速化するために、グローバル辞書を作成した後、辞書テーブルの INTEGER 列の値をビットマップ列に直接挿入できます。その後、このビットマップ列にビットマップ関数を使用して正確な重複排除を行うことができます。
方法 1
グローバル辞書を構築し、すでに dest_table に注文データをインポートしている場合は、次の手順に従います。
-
2 つの列を持つ集計テーブル
dest_table_bitmapを作成します。1 つはbitmap_union()関数を使用して集計するための BITMAP 型のorder_id_bitmap列で、もう 1 つは INTEGER 型のid列です。このテーブルには元の STRING 列は含まれていません。そうでないと、各ビットマップには 1 つの値しか含まれず、加速の利点が失われます。CREATE TABLE dest_table_bitmap (
id BIGINT,
order_id_bitmap BITMAP BITMAP_UNION
)
AGGREGATE KEY (id)
DISTRIBUTED BY HASH(id) BUCKETS 6; -
dest_table_bitmapにデータを挿入します。dest_tableのid列のデータをid列に挿入し、辞書テーブルdictの INTEGER 列order_id_intのデータを（to_bitmap()関数で処理して）order_id_bitmap列に挿入します。INSERT INTO dest_table_bitmap (id, order_id_bitmap)
SELECT id, to_bitmap(dict_mapping('dict', order_uuid))
FROM dest_table
WHERE dest_table.batch = 1; -- 異なるバッチを示します。
INSERT INTO dest_table_bitmap (id, order_id_bitmap)
SELECT id, to_bitmap(dict_mapping('dict', order_uuid))
FROM dest_table
WHERE dest_table.batch = 2; -
正確な重複排除のために BITMAP 列に
BITMAP_UNION_COUNT()関数を使用します。SELECT id, BITMAP_UNION_COUNT(order_id_bitmap) FROM dest_table_bitmap
GROUP BY id ORDER BY id;
方法 2
グローバル辞書を作成した後、特定の注文データを保持する必要がない場合、データを直接 dest_table_bitmap テーブルにロードしたい場合は、次の手順に従います。
-
2 つの列を持つ集計テーブル
dest_table_bitmapを作成します。1 つはbitmap_union()関数を使用して集計するための BITMAP 型のorder_id_bitmap列で、もう 1 つは INTEGER 型のid列です。このテーブルには元の STRING 列は含まれていません。そうでないと、各ビットマップには 1 つの値しか含まれず、加速の利点が失われます。CREATE TABLE dest_table_bitmap (
id BIGINT,
order_id_bitmap BITMAP BITMAP_UNION
)
AGGREGATE KEY (id)
DISTRIBUTED BY HASH(id) BUCKETS 6; -
集計テーブルにデータを挿入します。CSV ファイルの
id列のデータをid列に挿入し、辞書テーブルdictの INTEGER 列order_id_intのデータを（to_bitmap()関数で処理して）order_id_bitmap列に挿入します。curl --location-trusted -u root: \
-H "format: CSV" -H "column_separator:," \
-H "columns: id, order_uuid, order_id_bitmap=to_bitmap(dict_mapping('dict', order_uuid))" \
-T batch1.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dest_table_bitmap/_stream_load
curl --location-trusted -u root: \
-H "format: CSV" -H "column_separator:," \
-H "columns: id, order_uuid, order_id_bitmap=to_bitmap(dict_mapping('dict', order_uuid))" \
-T batch2.csv \
-XPUT http://<fe_host>:<fe_http_port>/api/example_db/dest_table_bitmap/_stream_load -
正確な重複排除のために BITMAP 列に
BITMAP_UNION_COUNT()関数を使用します。SELECT id, BITMAP_UNION_COUNT(order_id_bitmap) FROM dest_table_bitmap
GROUP BY id ORDER BY id;