部分インデックス - PolarDB - Alibaba Cloud ドキュメントセンター

部分インデックスは、テーブルのサブセット上に構築されたインデックスであり、サブセットは、条件式 (部分インデックスの述語と呼ばれる) によって定義される。インデックスには、述語を満たすテーブル行のエントリのみが含まれます。部分インデックスは特殊な機能ですが、役立つ状況がいくつかあります。

部分インデックスを使用する主な理由の1つは、共通値のインデックスを避けるためです。共通の値 (すべてのテーブル行の数パーセント以上を占める値) を検索するクエリはインデックスを使用しないため、インデックスにそれらの行を保持する意味はまったくありません。これにより、インデックスのサイズが小さくなり、インデックスを使用するクエリが高速化されます。また、すべての場合にインデックスを更新する必要がないため、多くのテーブル更新操作が高速化されます。

共通値を除外する部分インデックスの設定

webサーバーのアクセスログをデータベースに保存するとします。ほとんどのアクセスは組織のIPアドレス範囲から発生しますが、一部は他の場所 (ダイヤルアップ接続の従業員など) から発生します。 IPによる検索が主に外部アクセスを対象としている場合、組織のサブネットに対応するIP範囲にインデックスを付ける必要はないでしょう。

次のようなテーブルを想定します。

CREATE TABLE access_log (
        url varchar、
        client_ip inet,
        ...
    );

この例に合った部分インデックスを作成するには、次のようなコマンドを使用します。

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
    WHERE NOT (client_ip > inet '192.168.100.0 'AND)
               client_ip < inet '192.168.100.255 ');

このインデックスを使用できる一般的なクエリは次のとおりです。

SELECT *
    FROM access_log
    WHERE url = '/index.html' AND client_ip = inet '212.78.10.32 ';

ここで、クエリのIPアドレスは部分インデックスでカバーされます。次のクエリは、インデックスから除外されたIPアドレスを使用するため、部分インデックスを使用できません。

SELECT *
    FROM access_log
    WHERE url = '/index.html' AND client_ip = inet '192.168.100.23 ';

この種の部分インデックスは、共通の値が予め定められていることを必要とするので、そのような部分インデックスは、変化しないデータ分布に対して最もよく使用される。このようなインデックスは、新しいデータ配布を調整するために時々再作成できますが、これにより保守作業が追加されます。

部分インデックスの別の可能な使用法は、典型的なクエリワークロードが関心を持たないインデックスから値を除外することである。これは、上記と同じ利点をもたらすが、インデックススキャンがその場合に有益である場合であっても、「関心のない」値がそのインデックスを介してアクセスされることを防止する。明らかに、この種のシナリオの部分インデックスを設定するには、多くの注意と実験が必要になります。

興味のない値を除外する部分インデックスの設定

請求された注文と請求されていない注文の両方を含むテーブルがあり、請求されていない注文がテーブル全体のごく一部を占めるが、それらが最もアクセスされる行である場合、請求されていない行だけにインデックスを作成することでパフォーマンスを向上させることができます。インデックスを作成するコマンドは次のようになります。

CREATE INDEX orders_unbilled_indexオン注文 (order_nr)
        請求される場所は真実ではありません。

このインデックスを使用するための可能なクエリは次のとおりです。

SELECT * FROM注文WHERE billed is not true AND order_nr < 10000;

ただし、インデックスは、order_nrをまったく含まないクエリでも使用できます。例:

SELECT * FROM注文請求された場所はtrueではありませんAND金額> 5000.00;

これは、システムがインデックス全体をスキャンしなければならないため、金額列の部分的なインデックスほど効率的ではありません。しかし、未請求注文が比較的少ない場合、未請求注文を見つけるためだけにこの部分インデックスを使用することが勝利になる可能性があります。

このクエリはこのインデックスを使用できません。

SELECT * FROM注文WHERE order_nr = 3501;

注文3501は、課金注文または未課金注文の中にあり得る。

また、この例では、インデックス付き列と述語で使用される列が一致する必要がないことを示しています。PostgreSQLsupports partial indexes with arbitrary predicates. ただし、述語は、インデックスの恩恵を受けるはずのクエリで使用される条件と一致する必要があることに注意してください。正確には、部分的なインデックスは、クエリのWHERE条件が数学的にインデックスの述語を意味することをシステムが認識できる場合にのみ、クエリで使用できます。 (そのような一般定理証明者を作成するのが非常に難しいだけでなく、実際に使用するにはおそらく遅すぎるでしょう。) システムは、単純な不等式含意を認識することができ、例えば、「x < 1」は「x < 2」を意味する。そうでなければ、述語条件は、クエリのWHERE条件の一部と正確に一致しなければならない。マッチングは、実行時ではなく、クエリ計画時に行われる。その結果、パラメータ化されたクエリ句は部分インデックスでは機能しません。例えば、パラメータを有する準備されたクエリは、“x < ? これは、パラメータのすべての可能な値に対して「x < 2」を意味することはありません。

部分インデックスの第3の可能な使用法は、インデックスがクエリで使用されることを全く必要としない。ここでのアイデアは、テーブルのサブセットに対して一意のインデックスを作成することです。これにより、インデックス述語を満たす行間で一意性が強制され、そうでない行は制約されません。

部分一意インデックスの設定

テスト結果を説明する表があるとします。本発明者らは、所与の主題およびターゲットの組み合わせに対して1つの「成功」エントリのみが存在することを保証したいが、任意の数の「不成功」エントリが存在し得る。これを行う1つの方法があります:

CREATE TABLEテスト (
        件名テキスト、
        ターゲットテキスト、
        boolean成功,
        ...
    );

    CREATE UNIQUE INDEX tests_success_constraint ON tests (subject、target)
        どこで成功;

これは、成功したテストが少なく、失敗したテストが多い場合に特に効率的なアプローチです。 is null制限を持つ一意の部分インデックスを作成することにより、列に1つのNULLのみを許可することもできます。

最後に、部分インデックスを使用して、システムのクエリ計画の選択をオーバーライドすることもできます。また、固有の分布を持つデータセットは、実際にはインデックスを使用すべきでないときにシステムにインデックスを使用させる可能性があります。その場合、インデックスは、問題のあるクエリに利用できないように設定することができる。通常、PostgreSQLはインデックスの使用について合理的な選択を行います (たとえば、共通の値を取得するときにそれらを回避するため、前の例ではインデックスサイズを保存するだけで、インデックスの使用を回避する必要はありません) 。

部分的なインデックスを設定すると、少なくともクエリプランナーが知っているのと同じくらい、特にインデックスが有益である可能性がある時期を知っていることがわかります。この知識を形成するには、PostgreSQLworkのインデックスの経験と理解が必要です。ほとんどの場合、通常のインデックスに対する部分インデックスの利点は最小限になります。それらが非常に逆効果である場合があります。

パーティションの代わりに部分インデックスを使用しないでください

重複しない部分インデックスの大きなセットを作成したいと思うかもしれません。

CREATE INDEX mytable_cat_1 ON mytable (データ) WHERE category = 1;
    CREATE INDEX mytable_cat_2 ON mytable (データ) WHERE category = 2;
    CREATE INDEX mytable_cat_3 ON mytable (データ) WHERE category = 3;
    ...
    CREATE INDEX mytable_cat_N ON mytable (データ) WHERE category = N;

これは悪い考えです! ほぼ確実に、次のように宣言された単一の非部分的なインデックスを使用する方が良いでしょう。

CREATE INDEX mytable_cat_data ON mytable (カテゴリ、データ);

この大きなインデックスでの検索は、小さなインデックスでの検索よりも2、3のツリーレベルを下る必要があるかもしれませんが、部分インデックスの適切な1つを選択するために必要なプランナーの労力よりもほぼ確実に安価になります。問題の核心は、システムが部分インデックス間の関係を理解していないことであり、現在のクエリに適用できるかどうかを確認するために、それぞれのインデックスを手間をかけてテストします。

テーブルが十分に大きく、単一のインデックスが本当に悪い考えである場合は、代わりにパーティション分割を使用することを検討する必要があります。そのメカニズムでは、システムは、テーブルとインデックスが重複していないことを理解しているので、より良いパフォーマンスが可能です。