MySQL 独立インデックスと共同インデックスの選択

複数列のインデックスについては、理解が不足していることがよくあります。よくある間違いは、多数の列に独立したインデックスを設定したり、インデックス列の順序を間違えたりすることです。インデックス列の順序の問題については次の記事で説明します。まず、次のテーブル構造を例に、複数の独立したインデックスの場合を見てみましょう。

CREATE TABLEテスト（
  c1 INT、
  c2 INT、
  c3 INT、
  キー(c1)、
  キー(c2)、
  キー(c3)、
);

このインデックス戦略を使用するのは、通常、何らかの権威あるアドバイス (WHERE 条件で使用される条件列にインデックスを追加するなど) の結果です。実際、これは完全に間違っており、せいぜい星 1 つしか付けられません。この方法によるインデックス作成は、実際に最適化されたインデックス作成よりも数桁遅くなります。 3 つ以上の星を持つインデックスを設計できない場合は、WHERE 条件を無視するよりも、行順序の最適化やカバーリング インデックスの作成に重点を置く方がよい場合があります。

カバーリング インデックスとは、クエリ ステートメントの実行をデータ テーブルから読み取ることなく、インデックスからのみ取得できることを意味します。インデックスカバレッジが達成されているとも言えます。 クエリ ステートメントがカバー インデックスの条件を満たす場合、MySQL はインデックスを使用してクエリに必要なデータを返すだけでよいため、インデックスを見つけた後にテーブルに戻る必要がなくなり、I/O が削減され、効率が向上します。 たとえば、covering_index_sample テーブルには共通インデックス idx_key1_key2(key1, key2) があります。 SQL ステートメント「select key2 from coverage_index_sample where key1 = 'keytest';」を使用すると、データ テーブルからデータ行を検索しなくても、カバー インデックスを介してクエリを実行できます。

多くの場合、多くの列に個別のインデックスを作成しても、MySQL のパフォーマンスは向上しません。 MySQL 5.0 以降では、インデックス マージ戦略を使用してこの設計をわずかに最適化できます。このアプローチにより、複数列のインデックスを持つテーブルに対するクエリで、必要な行を見つけるためのインデックスの使用を制限できます。

インデックスのマージは、複数のインデックスに対して条件付きスキャンを個別に実行し、それぞれの結果をマージします（交差/結合）。

以前のバージョンの MySQL では 1 つのインデックスしか使用できなかったため、インデックスのサポートがない場合には MySQL はテーブル全体のスキャンを実行することがよくありました。たとえば、film_actor テーブルには film_id と actor_id のインデックスがありますが、WHERE 条件で両方のインデックスを使用することは適切ではありません。

film_id、actor_id を film_actor から選択します。ここで actor_id = 1 または film_id = 1;

以前のバージョンの MySQL では、次に示すように 2 つのクエリを結合しない限り、このクエリによってテーブル全体がスキャンされます。

film_id、actor_id を film_actor から選択し、 actor_id = 1 を UNION ALL に格納します。 
film_id、actor_id を film_actor から選択します。ここで film_id = 1 かつ actor_id <> 1;

MySQL 5.0 以降では、クエリは両方のインデックスを使用し、最終結果をマージします。このプロセスを実装するには、次の 3 つのアルゴリズムのバリエーションが必要です。

1. OR条件を使用して結合データを取得する
2. AND条件を使用して交差データを取得します
3. 上記の 2 つのステップでデータの共通部分の結合を取ります。
   

上記は少しわかりにくいですが、実際には、インデックスを使用するために、単一の条件を使用して分散し、すべてのデータを検索してから、データを結合する必要があります。 EXPLAIN を使って確認してみましょう。

EXPLAIN SELECT `film_id`,`actor_id` FROM `film_actor` WHERE `actor_id`=1 OR `film_id`=1

クエリ方法はフルテーブルスキャンですが、最適化のために Extra が使用されていることがわかります。 MySQL は複雑なクエリを処理するときにこの手法を使用するため、Extra にネストされた操作が表示されることがあります。このインデックス マージ戦略は、うまく機能する場合もありますが、多くの場合、インデックスの使用が不適切であることを示していると見なす必要があります。

1. サーバーが交差インデックス (通常は AND 条件を使用) を使用する場合、通常は、列を結合する個別のインデックスではなく、関連するすべての列を含むインデックスが必要であることを意味します。
2. サーバーがユニオン インデックス (通常は OR 条件付き) を使用する場合、キャッシュ、並べ替え、およびマージ操作で大量の CPU およびメモリ リソースが消費されることがあります。特に、インデックスにフィルターが常に含まれていない場合は、スキャンによってマージ操作に対して大量の行が返される可能性があります。
3. オプティマイザーはこれらのコストを負担せず、ランダム ページ読み取りの数を最適化するだけであることに注意してください。これにより、クエリが「安価」になり、テーブル全体のスキャンが実際に遅くなる可能性があります。 CPU とメモリの使用率が高いと同時実行クエリに影響しますが、クエリ ステートメントを個別に実行するとこれらの影響は発生しません。したがって、MySQL 4.1 で行われたように、UNION を使用するようにクエリを書き直す方が最適な場合があります。
   

EXPLAIN 分析の使用時にインデックスのマージが見られる場合、クエリ ステートメントとテーブル構造をチェックして、それが最適なアプローチであるかどうかを確認する必要があります。 optimizer_switch を使用してインデックスのマージを無効にすることでこれを確認できます。

film_actor のインデックスを結合インデックスに変更し (元の 2 つの独立したインデックス film_id と actor_id を削除)、その効果を確認します。完全なテーブル クエリが回避されていることがわかります。

ALTER TABLE film_actor ADD INDEX `sindex` (`film_id`,`actor_id`);

以上がMySQL独立インデックスとジョイントインデックスの選択の詳細です。MySQL独立インデックスとジョイントインデックスの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。