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

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

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を簡略化したものです。

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

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

1. postgres=# select ATTRACT_TP,count(*) from T_MPS_INFO group by ATTRACT_TP;
   
2. attract_tp | count
   
3. --- --- ------+--------
   
4. 6 | 251
   
5. 1 | 143
   
6. 10 | 314
   
5 | 194333
7. 
   
7 | 1029
8. 
   
9. 
   (11行)
10. 
    
11. 
    postgres=#選択COLUMN_ID、COLUMN_ID による T_MPS_INFO グループのカウント (*);
12. 
    column_id カウント
13. 
    -----------+--------
14. 
    | 285 | 20
15. 351 | 26
    
347 | 2
16. 
    
228 | 21
17. 
    
| 1
18. 
    
54 | 10
19. 
    
2147483647 | 4 | 1
20. 
    
131 | 10
21. 
    243 |
22. 
    61 | 40
23. 
    350 |
24. 377 | 2
    
25. 260 | 184 | 181
    
341 | 199271
26. 
    235 |
294
27. 
    
352 | 3
28. 
    
368 1
29. 
    
199 | 8
30. 
    
84 |
31. 
    361 | 33197 9
32. 319 |
    
33. 244 65
    
125
34. 253 | 49
    
121 | 3
35. 
    
365 | 1
36. 
    
0 | 1
37. 
    
217 | 1
38. 
    122 | 49
39. 161 |
    222 | 9
40. 
    261 2 | 3816
41. 57 | 1 9
    
42. 97 | 20
    
43. 3
    
44. 85 | 1
    
45. 375 | 641
    
1 | 6479
46. 
    185 | 10
47. 
    | 17
48. 
    (80 行)
49. 
    
50. 
    postgres=# T_MPS_INFO グループから FIELD2,count(*) を選択します;
51. 
    フィールド 2 | 2297
52. |
    6 | 469
53. 2 | 11452
    
54. 4 |
    5 | 200497
55. 
    9 | 331979
56. 
    0 | 2
57. 
    7 | 78
58. 
    (11 行)
59. 
    
60. postgres=# T_MPS_INFO グループから STATUS;
    
61. ステータスを選択します。
    --- ---+--------
62. 
    | 2297
63. ありがとうございます。これらのフィールドの値の分布は非常に不均一です。はい、次の Lua スクリプトを構築して、負荷をシミュレートするためのさまざまな選択ステートメントを生成します。
    qx.lua:
64. 
    
    1. pathtest = string.match(test, "(.*/)") または ""
       
    2. 
       
    3. dofile(pathtest .. "common.lua")
       
    4. 
       
    5. function thread_init(thread_id)
       
    6. set_vars()
       
    7. end
       
    8. 
       
    関数イベント(thread_id)
    9. 
       
    ローカルATTRACT_TP,COLUMN_ID,FIELD2,STATUS
    10. 
        
    ATTRACT_TP = 0, 10)
    11. 
        
    COLUMN_ID = sb_rand_uniform(1 , 100)
    12. 
        
    FIELD2 = sb_rand_uniform(0, 10)
    13. 
        
    STATUS = sb_rand_uniform(0, 4)
    14. 
        
    15. 
        
    rs = db_query("gpppur.GB_BEGINDATE の場合を選択
    
WHEN gpppur.PREVIEW_BEGINDT
ELSE 3 END AS flag,
65. 
    
gpppur.*
66. 
    
FROM T_MPS_INFO gpppur
67. 
    
WHERE gpppur.ATT RACT_TP = "。 .ATTRACT_TP.."
68. 
    
AND gpppur.COLUMN_ID = "..COLUMN_ID.."
69. 
    
AND gpppur.FIELD2 = "..FIELD2.."
70. 
    
AND gpppur.STATUS = "..STATUS.."
71. 
    
ORDER BY flag ASC,gpppur.PC_SORT_NUM ASC,gpppur.GB_BEGINDATE DESC
72. 
    
LIMIT 45")
73. 
    
end

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

[root@rh6375Gt20150507 ~]# sysbench --db-driver=mysql --test=/opt/sysbench-0.5/sysbench/tests/db/qx.lua --mysql-db=test -- mysql-user=mysql --mysql-password=mysql --mysql-host=srdsdevapp69 --num-threads=32 --max-time=5 run
1. sysbench 0.5: マルチスレッドシステム評価ベンチマーク
3. Running次のオプションを使用したテスト:
4. スレッド数: 32
5. 乱数ジェネレーターのシードは 0 なので無視されます
8. スレッドが開始されました!
10. OLTP テスト統計:
11. 実行されたクエリ:
12. 読み取り: 825
13. 書き込み: 0
14. その他: 0
15. 合計: 825
16. トランザクション: 0 (0.00/秒)
17. 読み取り/書き込みリクエスト: 825 (64.20/秒)
18. その他の操作:​​ 0 (0.00
19. 無視されたエラー: 0 (1 秒あたり 0.00)
20. 再接続: 0 (1 秒あたり 0.00)
22. 一般統計:
23. 合計時間: 12.8496 秒
24. イベント総数: 825
イベント実行にかかった合計時間: 399.6003秒
26. 応答時間:
27. 最小: 1.01ミリ秒
28. 平均: 484.36ミリ秒
29. 最大: 12602.74ミリ秒
30. およそ 95 パーセンタイル: 222.79 ミリ秒
32. スレッドの公平性:
33. イベント (平均/stddev): 25.7812/24.12
34. 実行時間 (平均/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) を達成しました。

1. [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
   
2. sysbench 0.5: マルチスレッド システム評価ベンチマーク
   
3. 
   
4. 次のオプションを使用してテストします:
   
5. スレッド数: 32
   
6. 乱数生成器のシードは 0 で無視されます
   
7. 
   
8. 
   
9. スレッド
   
10. 
    
11. OLTP テスト統計:
    
12. クエリ実行:
    
13. 読み取り: 1948
    
14. 書き込み: 0
    
15. その他: 0
    
16. 合計: 1948
    
17. トランザクション: 0 (1秒あたり0.00)
    
18. 読み取り/書き込みリクエスト: 1948 (335.52 あたり秒)
    
19. その他の操作:​​ 0 (1 秒あたり 0.00)
    
20. 無視されたエラー: 0 (1 秒あたり 0.00)
    
21. 再接続: 0 (1 秒あたり 0.00)
    
22. 
    
23. 一般統計:
    
24. 合計時間: 5.8059秒
    
25. イベント総数: 1948
    
26. イベント実行にかかった合計時間: 172.0538秒
    
27. 応答時間:
    
28. 分:​​
    平均: 88.32 ミリ秒
29. 
    最大: 2885.69ms
30. 
    約95 パーセンタイル: 80.01 ミリ秒
31. 
    
32. 
    スレッドの公平性:
33. 
    イベント (平均/stddev): 60.8750/27.85
34. 
    実行時間 (平均/stddev): 5.3767/0.29

次步、追加ジン

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

1. CREATE EXTENSION
   
2. postgres=# gin(attract_tp, column_id, field2, status) を使用して t_mps_info にインデックス idx3 を作成します;
   
3. CREATE INDEX
   
再度圧縮を実行します。出力された qps は 5412、MySQL の 85 倍 (5412/64=85) です。

[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: マルチスレッド システム評価ベンチマーク
1. 
   
2. 
   
次のオプションでテストを実行します:
3. 
   
スレッド数: 32
4. 
   
乱数ジェネレータ シードは0 は無視されます
5. 
   
6. 
   
7. 
   
スレッド
8. 
   
9. 
   
OLTP テスト統計:
10. 
    
実行されたクエリ:
11. 
    
読み取り:書く: 0
12. 
    
その他: 0
13. 
    
合計: 10000
14. 
    
トランザクション: 0 (1秒あたり0.00)
15. 
    
読み取り/書き込みリクエスト: 10000 (1秒あたり5412.80)
16. 
    
その他の操作:​​ 0 (1秒あたり0.00)
17. 
    
無視編集エラー: 0 (1 秒あたり 0.00)
18. 
    
再接続: 0 (1 秒あたり 0.00)
19. 
    
20. 
    
一般統計:
21. 
    
合計時間: 1.8475 秒
22. 
    
イベント総数: 10000
23. 
    
合計イベント実行にかかった時間: 58.2706 秒
24. 
    
応答時間:
25. 
    
最小: 0.95 ミリ秒
26. 
    
平均: 5.83 ミリ秒
27. 
    
最大: 68.36 ミリ秒
28. 
    
約95 パーセンタイル: 9.42 ミリ秒
29. 
    
30. 
    
スレッドの公平性:
31. 
    
イベント (平均/stddev): 312.5000/47.80
32. 
    
実行時間 (平均/stddev): 1.8210/0.02
33. 
    
34. 
    4.3补充
作としてそれに比べて、私たちは MySQL に attract_tp、column_id、field2、status の 4 つのフィールドを含むマルチフィールド インデックスを追加しました。出力される qps は 4000 で、PostgreSQL とは異なります。 (PostgreSQL の計算は不可能です。PostgreSQL のパフォーマンスは MySQL よりもはるかに優れています。これは大家の共用です。私の例では、SQL は計算不可能ですか?)

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

http://www.bkjia.com/PHPjc/1108026.htmlwww.bkjia.com本当http://www.bkjia.com/PHPjc/1108026.html技術記事高同時実行性の低カーディナリティの複数フィールド任意組み合わせクエリの最適化 1. 質問: まず、このタイトルでいう「低カーディナリティの複数フィールド任意組み合わせクエリ」とは何を指すのか説明してください。これは満足するという意味です...