高同時実行性、低カーディナリティの複数フィールド任意組み合わせクエリの最適化

1. 質問

まず、このタイトルに出てくる「低カーディナリティの複数フィールド任意組み合わせクエリ」とは何を指すのか説明してください。これは、以下の条件を満たすクエリを指します:
1. 検索条件に複数のフィールド条件の組み合わせが含まれている
2. これらのフィールドの組み合わせが不確かである
3. 個々のフィールドの選択性が良くない

多数ある電子商取引の商品表示ページなど、このタイプのクエリの使用シナリオ。ユーザーは、カテゴリ、サプライヤー、ブランド、プロモーション、価格などのクエリ条件のさまざまな組み合わせを入力し、最終的には結果を並べ替えたり、ページネーションしたりする必要があることがよくあります。

この種の問題の問題点は次のとおりです:
1. レコードの数が多い場合、フルテーブルスキャンが実行されると、パフォーマンスが低下し、高い同時アクセスの要件を満たせなくなります。
2. クエリ条件に含まれる単一フィールドの選択性は非常に低く、クエリ効率の問題は単一フィールドインデックスでは解決できません。
3. 通常の Btree マルチフィールドインデックスを構築する場合、ユーザー入力条件の組み合わせが多すぎるため、数百または数千のインデックスが構築される可能性がありますが、これは非現実的であり、保守が困難です。

2. 解決策

この種の問題に対して私が考えることができる解決策は2つあります

2.1 ビットマップインデックス

ビットマップの特徴は、値が等しいすべての行セットのキーとビットマップを保存することです。複数のキーを含むクエリの場合、これらのキーに対応するビットマップに対して AND または演算を実行するだけで済みます。ビットマップのサイズは非常に小さく、ビット AND-OR 演算の効率も非常に高いため、ビットマップはこのタイプのクエリに非常に適しています。
ビットマップインデックスには欠点もあります。レコードを更新するとテーブル全体がロックされるため、同時書き込みが多いシナリオには適していません。もう 1 つの問題は、一般的なリレーショナルデータベースの中でビットマップインデックスをサポートしているのは Oracle だけのようであり、多くの場合オープンソースデータベースを使用したいことです。

2.2 逆インデックス

逆インデックスは、キーと、その値がこのキーと等しい行セットを格納します。行セットは、リスト、ツリー、またはその他の格納形式にすることができます。複数のキーを組み合わせたクエリの場合は、これらのキーの結果に対して集合演算を実行するだけです。
転置インデックスは一般的に全文検索に使用されますが、多くのシステムは Elasticsearch などの構造化データ検索をサポートするためにも使用します。 Elasticsearch は、JSON ドキュメントの高速検索、複合クエリ、並べ替え、集計、分散デプロイメント、その他多くの優れた機能をサポートしています。ただし、次の要素を考慮すると、リレーショナルデータベースでソリューションを見つけることを好みます。
- ファジーマッチングには検索エンジンが提供する高度な機能を使用する必要はありません。実際には、完全一致または単純なファジーマッチングが必要です。
- データの量が分散検索クラスターを必要とするほど大きくありません。
- 元のデータはすでにリレーショナルデータベースにあるため、データの同期について心配する必要はありません。
- 私はすでにリレーショナルデータベースのインターフェイスに基づいたアプリケーションを開発しているので、車輪の再発明はしたくありません。
- リレーショナルデータベースの運用と保守管理をマスターしましたが、新しいシステムでどれだけの落とし穴に直面するかわかりません。
- Java と C のパフォーマンスの違いを考慮すると、組み込みのリレーショナルデータベースソリューションのパフォーマンスは、プロの検索エンジンのパフォーマンスに劣らない可能性があります。

3. PostgreSQL のソリューション

ソリューションの範囲をオープンソースのリレーショナルデータベースに限定すると、答えは 1 つだけになる可能性があります。それは、PostgreSQL の gin インデックスです。
PostgreSQL の gin インデックスは転置インデックスであり、全文検索だけでなく、int や varchar などの通常のデータ型にも使用されます。
多次元クエリの場合は、次のようなインデックスを構築できます。
1. 等価条件に含まれるすべてのカーディナリティの低いフィールドに対して、一意の複数フィールドジンインデックスを作成します。
2. 適切な選択性を持つ等価クエリまたは範囲クエリに含まれるフィールドに対して。 , さらに、btree インデックス

を構築するときに、複数フィールドのインデックスでもあるのに、なぜ gin には 1 つの複数フィールドのインデックスを構築する必要があるのに、btree には複数の複数フィールドのインデックスが必要なのかという疑問を持つ人もいるかもしれません。さまざまなクエリの組み合わせを考慮して構築されます。これは、gin 複数フィールドインデックスの各フィールドが同等であり、先頭フィールドがないためです。したがって、一意の gin 複数フィールドインデックスが構築されている限り、btree 複数フィールドインデックスはすべてのクエリの組み合わせをカバーできます。クエリ条件に suoyi 先頭フィールドが含まれていない場合、インデックスは使用できません。

マルチフィールド gin インデックスに内部的に保存される各キーは (列番号、キーデータ) の形式であるため、混乱することなく異なるフィールドを区別できます。保存された値は、キーに一致するすべてのレコードの ctid のセットです。レコード数が比較的多い場合、このセットは btree 形式で格納され、圧縮されているため、gin インデックスが占める記憶領域は非常に小さく、同等の btree インデックスの約 20 分の 1 のみです。別のパフォーマンスの向上から派生します。

多次元クエリに含まれる複数のフィールド、複数フィールドの gin インデックスに含まれるフィールドの場合、ctid セットは gin インデックスによってマージされ (和集合または交差を取る)、その後、取得された ctid セットが ctid と結合されます。他のインデックスから取得したセットを BitmapAnd または BitmapOr マージします。 gin インデックス内の ctid セットをマージする効率は、インデックス間で ctid セットをマージする効率よりもはるかに高く、gin インデックスはカーディナリティの低いフィールドをより適切に最適化するため、別のインデックスを構築するよりも gin インデックスの特性を最大限に活用する方が優れています。各フィールドの btree インデックスを作成し、BitmapAnd Or BitmapOr を使用すると、結果セットがより効率的にマージされます。

4. 実際のケース

4.1 元のクエリ

以下のSQLは、あるシステムにおける実際のSQLを簡略化したものです。

gpppur.GB_BEGINDATE <= '2016-02-29 14:36:00' かつ gpppur.GB_ENDDATE > '2016-02-29 14:36:00' THEN 1
WHEN の場合を選択gpppur.PREVIEW_BEGINDT <= '2016-02-29 14:36:00' AND gpppur.PREVIEW_ENDDT > '2016-02-29 14:36:00' THEN 2
ELSE 3 END AS フラグ、
gpppur.*
FROM T_MPS_INFO gpppur
WHERE gpppur.ATTRACT_TP = 0
AND gpppur.COLUMN_ID = 1
AND gpppur.FIELD2 = 1
AND g pppur.STAT US = 1
注文BY flag ASC,gpppur.PC_SORT_NUM ASC,gpppur.GB_BEGINDATE DESC
LIMIT 0,45

MySQL データベースが使用され、総データ量は 60w、FIELD2+STATUS のマルチフィールドインデックスがあります。
クエリ条件に含まれる 4 つのフィールドの値の分布は次のとおりです:

postgres=# select ATTRACT_TP,count(*) from T_MPS_INFO group by ATTRACT_TP;
attract_tp | count
--- --- ------+--------
6 | 251
1 | 143
10 | 314
(11行)
postgres=#選択COLUMN_ID、COLUMN_ID による T_MPS_INFO グループのカウント (*);
column_id カウント
-----------+--------
| 285 | 20
351 | 26
243 |
61 | 40
350 |
377 | 2
260 | 184 | 181
235 |
361 | 33197 9
319 |
244 65
253 | 49
122 | 49
161 |
222 | 9
261 2 | 3816
57 | 1 9
97 | 20
3
85 | 1
375 | 641
185 | 10
| 17
(80 行)
postgres=# T_MPS_INFO グループから FIELD2,count(*) を選択します;
フィールド 2 | 2297
|
6 | 469
2 | 11452
4 |
5 | 200497
9 | 331979
0 | 2
7 | 78
(11 行)
postgres=# T_MPS_INFO グループから STATUS;
ステータスを選択します。
--- ---+--------
| 2297
ありがとうございます。これらのフィールドの値の分布は非常に不均一です。はい、次の Lua スクリプトを構築して、負荷をシミュレートするためのさまざまな選択ステートメントを生成します。
qx.lua:
1. pathtest = string.match(test, "(.*/)") または ""
3. dofile(pathtest .. "common.lua")
5. function thread_init(thread_id)
6. set_vars()
7. end

その後sysbenchを使用して実行、結果在32発行時に得られたqpsは64

sysbench 0.5: マルチスレッドシステム評価ベンチマーク
Running次のオプションを使用したテスト:
スレッド数: 32
乱数ジェネレーターのシードは 0 なので無視されます
スレッドが開始されました!
OLTP テスト統計:
実行されたクエリ:
読み取り: 825
書き込み: 0
その他: 0
合計: 825
トランザクション: 0 (0.00/秒)
読み取り/書き込みリクエスト: 825 (64.20/秒)
その他の操作: 0 (0.00
無視されたエラー: 0 (1 秒あたり 0.00)
再接続: 0 (1 秒あたり 0.00)
一般統計:
合計時間: 12.8496 秒
イベント総数: 825
応答時間:
最小: 1.01ミリ秒
平均: 484.36ミリ秒
最大: 12602.74ミリ秒
およそ 95 パーセンタイル: 222.79 ミリ秒
スレッドの公平性:
イベント (平均/stddev): 25.7812/24.12
実行時間 (平均/stddev): 12.4875/0.23

4 .2 強化後の意見

上のページ特定の SQL は、すべての等価評価条件を含む 4 つのフィールド (ATTRACT_TP、COLUMN_ID、FIELD2、STATUS) の組み合わせインデックスを作成することによって変換できますが、この SQL だけがさまざまな評価の組み合わせで生成されることは説明が必要です。 1000 もの異なる SQL の 1 つであり、各 SQL に関連するクエリフィールドの組み合わせは異なっており、各組み合わせに対して個別に複数のフィールドインデックスを作成することは不可能です。 PostgreSQL の gin インデックスを使用して、MySQL のテーブル定義、インデックス、およびデータベースを非正規に PostgreSQL に移行します。 gin インデックスを追加する前に、事前にテストを行っています。測定されたパフォーマンスは、MySQL の 5 倍 (335/64=5) を達成しました。

[root@rh6375Gt20150507 ~]# sysbench --db-driver=pgsql --test=/opt/sysbench-0.5/sysbench/tests/db/qx.lua --pgsql-db=postgres --pgsql-user= postgres --pgsql-password=postgres --pgsql-host=srdsdevapp69 --num-threads=32 --max-time=5 run

sysbench 0.5: マルチスレッドシステム評価ベンチマーク

次のオプションを使用してテストします:

スレッド数: 32

乱数生成器のシードは 0 で無視されます

スレッド

OLTP テスト統計:

クエリ実行:

読み取り: 1948

書き込み: 0

その他: 0

合計: 1948

トランザクション: 0 (1秒あたり0.00)

読み取り/書き込みリクエスト: 1948 (335.52 あたり秒)

その他の操作: 0 (1 秒あたり 0.00)

無視されたエラー: 0 (1 秒あたり 0.00)

再接続: 0 (1 秒あたり 0.00)

一般統計:

合計時間: 5.8059秒

イベント総数: 1948

イベント実行にかかった合計時間: 172.0538秒

応答時間:

分:
平均: 88.32 ミリ秒

最大: 2885.69ms

約95 パーセンタイル: 80.01 ミリ秒

スレッドの公平性:

イベント (平均/stddev): 60.8750/27.85

実行時間 (平均/stddev): 5.3767/0.29

次步、追加ジン

postgres=# 拡張子 btree_gin を作成します;

CREATE EXTENSION
postgres=# gin(attract_tp, column_id, field2, status) を使用して t_mps_info にインデックス idx3 を作成します;
CREATE INDEX

[root@rh6375Gt20150507 ~]# sysbench --db-driver=pgsql --test= /opt/sysbench-0.5/sysbench/tests/db/qx.lua --pgsql-db=postgres --pgsql-user=postgres --pgsql-password=postgres --pgsql-host=srdsdevapp69 --num-threads= 32 --max-time=5 run

4.3补充

5. 全体
gin インデックス (同様の gist、spgist インデックスも含む) は PostgreSQL の大きな特徴であり、これは非常に興味深い使用方法に基づいており、Oracle と同じようにビットマップインデックスを作成できます。また、インデックス検索 (Elasticsearch、Solr など) の方式に基づいて対比を行います。さらに、本人が知っている限り、より良い方式がある場合は、私が知ることができれば幸いです。