AnalyticDB:一括更新

簡単なアップデート

データのマージは、データの変更、つまり更新、削除、挿入、またはコピー操作の実行に関するものです。 更新操作を例にとると、column-store テーブルの単一の行で記録を更新することです。 次の図は、AnalyticDB for PostgreSQL のデータ更新プロセスを示しています。

手順は次のとおりです。

1. ユーザーは、Update SQL リクエストをマスターノードに送信します。

2. マスターノードは、分散トランザクションを開始し、更新するテーブルをロックします (AnalyticDB for PostgreSQL は、同じテーブルへの同時更新を許可しません）。一致するスレーブノードに更新リクエストを配信します。

3. スレーブノードは、インデックスをスキャンして更新するデータを探し出し、データを更新します。 column-store テーブルの場合、古いデータ行を削除し、新しいデータ行をテーブルの末尾に書き込むことが更新のロジックです。 column-store テーブル内の更新されたデータページがメモリキャッシュに書き込まれ、対応するテーブルファイルの長さの変更 (データがテーブルの終わりに書き込まれるため、対応するテーブルファイルの長さが増えます) がログ (xlog ファイル) に記載されます。

4. 更新プロセスが終了する前に、メモリ内の更新されたデータページと xlog ファイルは、両方ともミラーノードに同期されます。 同期が完了すると、マスターノードは分散トランザクションを終了し、成功した実行に関するメッセージをユーザーに返します。

プロセス全体は長く、SQL 文の解析、トランザクションの配布、ロック、マスターノードとスレーブノード間の接続、スレーブノードとミラーノード間のデータとログの同期など、多くの操作が含まれています。 これらの操作はすべて CPU または I/O リソースを消費し、リクエストの応答が遅くなります。

したがって、 AnalyticDB for PostgreSQL の場合は、単一のデータ行への更新を避け、可能な限り一括操作を使用してデータを更新する以下のことを推奨します。

• 文の解析、ノード通信、およびデータ同期のオーバーヘッドを軽減するために、1 つの SQL 文を更新します。
• 不要なオーバーヘッドを避けるために、1 つのトランザクションを更新します。

一括更新

1 つの SQL 文を使用して複数の独立したデータ行を更新するには、次の手順に従います。

1. 対象テーブルの準備

更新するテーブルが target_table であるとします。 target_table は以下のように定義されます。

create table target_table(c1 int, c2 int, primary key (c1));
insert into target_table select gen-USerate_series(1, 10000000);

対象テーブルは、通常非常に大きくなります。 target_table に 1,000万行のデータを挿入するとします。 target_table は、更新を容易にするために索引付けされています。 A プライマリーキーが定義され、その結果、ユニークなインデックスが含まれます。

2. ステージテーブルの準備

一括更新にはステージテーブル (この例ではsource_table) が必要です。 これは、データを更新するために作成された一時テーブルです。 target_table のデータを更新するには、最初に新しいデータを source_table に挿入し、[COPY コマンド]、[OSS 外部テーブル]、または target_table への別の手段で、新しいデータをインポートします。

次の例では、データがいくつか source_table に直接生成されています。

create table source_table(c1 int, c2 int);
insert into source_table select gen-USerate_series(1, 100), gen-USerate_series(1,100);

3. 一括更新

source_table データの準備が完了したら、 update set … from … where ..文を実行します。

set optimizer = on;
target_tableをsource_table.c1からsource_table.c2に設定します。

更新操作のクエリプランは次のとおりです。

postgres=> explain update target_table set c2 = source_table.c2 from source_table where target_table.c1= source_table.c1;
                                                         QUERY PLAN
-------------------------------------------------- -------------------------------------------------- -------------------------
 Update  (cost=0.00..586.10 rows=25 width=1)
   ->  Result  (cost=0.00..581.02 rows=50 width=26)
         ->  Redistribute Motion 4:4  (slice1; segments: 4)  (cost=0.00..581.02 rows=50 width=22)
               Hash Key: public.target_table.c1
               ->  Assert  (cost=0.00..581.01 rows=50 width=22)
                     Assert Cond: NOT public.target_table.c1 IS NULL
                     ->  Split  (cost=0.00..581.01 rows=50 width=22)
                           ->  Nested Loop  (cost=0.00..581.01 rows=25 width=18)
                                 Join Filter: true
                                 ->  Table Scan on source_table  (cost=0.00..431.00 rows=25 width=8)
                                 ->  Index Scan using target_table_pkey on target_table  (cost=0.00..150.01 rows=1 width=14)
                                       Index Cond：public.target_table.c1 = source_table.c1

このプランから、 AnalyticDB for PostgreSQL はインデックスを使用します。 しかし、source_table にさらにデータを追加すると、オプティマイザは、Nest Loop 関連メソッドとインデックススキャンを使用する方がインデックスを削除するよりは効率的ではないと判断する可能性があります。 結果として、ハッシュ関連方法とテーブルスキャンを使用して実行するかもしれません。 たとえば、

postgres=> insert into source_table select gen-USerate_series(1, 1000), gen-USerate_series(1,1000);
INSERT 0 1000
postgres => analyze source_table;
ANALYZE
postgres=> explain update target_table set c2 = source_table.c2 from source_table where target_table.c1= source_table.c1;
                                              QUERY PLAN
-------------------------------------------------- ---------------------------------------------------
 Update  (cost=0.00..1485.82 rows=275 width=1)
   ->  Result  (cost=0.00..1429.96 rows=550 width=26)
         ->  Assert  (cost=0.00..1429.94 rows=550 width=22)
               Assert Cond: NOT public.target_table.c1 IS NULL
               ->  Split  (cost=0.00..1429.93 rows=550 width=22)
                     ->  Hash Join  (cost=0.00..1429.92 rows=275 width=18)
                           Hash Cond: public.target_table.c1 = source_table.c1
                           ->  Table Scan on target_table  (cost=0.00..477.76 rows=2500659 width=14)
                           ->  Hash  (cost=431.01..431.01 rows=275 width=8)
                                 ->  Table Scan on source_table  (cost=0.00..431.01 rows=275 width=8)

ここで説明している一括更新アプローチでは、SQLのコンパイル、ノード間通信、トランザクション、およびその他のオーバーヘッドが削減され、データ更新パフォーマンスが大幅に向上し、リソース消費が削減されます。

一括削除

削除操作に関して、一括更新に使用されるのと同様のステージテーブルを使用し、次の削除コマンドと "Using" 構文でデータを一括削除できます。

delete from target_table using source_table where target_table.c1 = source_table.c1;

一括削除操作でもインデックスが使用されます。

explain delete from target_table using source_table where target_table.c1 = source_table.c1;
                                             QUERY PLAN
-------------------------------------------------- ---------------------------------------------------
 Delete (slice0; segmen-USts: 4) (rows=50 width=10)
   ->  Nested Loop  (cost=0.00..41124.40 rows=50 width=10)
         ->  Seq Scan on source_table  (cost=0.00..6.00 rows=50 width=4)
         ->  Index Scan using target_table_pkey on target_table  (cost=0.00..205.58 rows=1 width=14)
               Index Cond：target_table.c1 = source_table.c1

削除と挿入を使ってデータをマージする

データをマージするには、まずデータをステージテーブルにマージする必要があります。 マージするデータが既にターゲットテーブルに存在することが事前に分かっている場合は、更新文を使用してデータをマージします。 しかし、ほとんどの場合、マージするデータのある部分は既にターゲットテーブルに存在し、一部が新しくなっており、ターゲットテーブルに一致するレコードが存在していません。 この場合、一括削除と一括挿入の組み合わせが使用できます。 サンプルコードは次のとおりです。

set optimizer = on;
delete from target_table using source_table where target_table.c1 = source_table.c1;
insert into target_table select * from source_table;

Values( ) を使用してリアルタイムでデータを更新する

ステージテーブルを使用するには、そのライフサイクルを維持する必要があります。 一部のユーザーは、リアルタイムに一括で PostgreSQL に関して HybridDB にデータを更新したいと考えています、つまり、継続的にデータを同期したり PostgreSQL 用に HybridDB にデータをマージしたいと考えています。

前述の方法を使用する場合は、ステージテーブルを繰り返し作成および削除 (または切り捨て) する必要があります。 実際、テーブルを維持するために苦労することなく、Values 式を使用してステージテーブルと同様の効果を得ることができます。 まず、データをスプライスして Values 式に更新し、次の方法を使用して、更新コマンドまたは削除コマンドを実行します。

update target_table set c2 = t.c2 from (values(1,1),(2,2),(3,3),…(2000,2000)) as t(c1,c2) where target_table.c1=t.c1
delete from target_table using (values(1,1),(2,2),(3,3),…(2000,2000)) as t(c1,c2) where target_table.c1 = t.c1