MySQLカバーインデックスの利点

一般的な提案は、WHERE 条件のインデックスを作成することですが、これは実際には一方的です。インデックスは、WHERE 条件だけでなく、すべてのクエリに対して設計する必要があります。インデックスはデータ行を効率的に見つけるのに役立ちますが、MySQL ではインデックスを使用して、データ行をまったく読み取ることなく列データを取得することもできます。結局のところ、インデックスのリーフ ノードには、インデックスに対応する値が含まれます。必要なデータを取得するにはインデックスを読み取るだけでよいのに、なぜ行を読み取る必要があるのでしょうか?インデックスにクエリのすべてのデータが含まれている場合、そのインデックスはカバーリング インデックスと呼ばれます。

カバーリングインデックスは非常に強力なツールとなり、パフォーマンスを大幅に向上させることができます。データを読み取る必要はなく、インデックスのみを読み取る必要がある場合を考えてみましょう。

• インデックス値は通常、行のストレージスペース全体よりもはるかに小さいため、MySQL はインデックス値のみを読み取る場合、少量のデータしか読み取ることができません。これはキャッシュ ワークロードにとって特に重要です。応答時間の大部分はデータのコピーに費やされます。ディスク I/O についても同じことが言えます。インデックス データは行データよりもストレージ スペースをはるかに少なく占めるため、I/O 負荷とメモリ使用量がさらに節約されます (MyISAM エンジンでは、インデックスをパッケージ化してストレージ スペースを小さくできるため、これはさらに重要です)。
• インデックスはインデックス値の順序で保存されるため、I/O アクセス スパンでは、ランダムなディスクの場所から行データを取得する場合よりも I/O 頻度が少なくなります。 MyISAM や Percona XtraDB などの一部のストレージ エンジンでは、OPTIMIZE を使用してテーブルを最適化し、完全に順序付けられたインデックスを取得することもできます。これにより、単純な範囲クエリで完全に順次アクセスが使用されるようになります。
• MyISAM などの一部のストレージ エンジンは、MySQL メモリ内のインデックスのみをキャッシュします。オペレーティング システムは MyISAM のデータをキャッシュするため、通常、アクセスにはシステム コールが必要です。これは、特にシステム コールがデータにアクセスするための最もコストのかかる方法であるキャッシュがロードされるシナリオでは、パフォーマンスに大きな影響を与える可能性があります。
• InnoDB のクラスター化インデックスにより、カバーリング インデックスは InnoDB にとって非常に役立ちます。 InnoDB のセカンダリ インデックスは、行の主キー値をリーフ ノードに格納します。したがって、セカンダリ インデックスはクエリをカバーし、プライマリ キー クエリを回避します。
  

いずれの場合も、最も典型的なのは、インデックス付き列のみを含むクエリのコストが、データ行を検索するコストよりもはるかに低くなることです。クラスター化インデックスは単なるインデックスの種類ではないことに注意することが重要です。クラスター化インデックスには、インデックスが付けられたデータ列に対応する値を格納する必要があります。ハッシュ、空間、およびフルテキスト インデックスにはこれらの値は保存されないため、MySQL ではクエリをカバーするためにバイナリ ツリーのみを使用できます。さらに、異なるストレージ エンジンは異なる方法でカバーリング インデックスを実装しており、すべてのストレージ エンジンがカバーリング インデックスをサポートしているわけではありません (たとえば、Memory ストレージ エンジンは現在これをサポートしていません)。

クエリ内のインデックスがカバーリング インデックスを使用していることを確認すると、Explain ステートメントの使用時に [Extra] 列に [Using index] が表示されます。たとえば、store_goods テーブルには複数列のインデックス (shop_id、goods_category_id1) があります。 MySQL は、クエリが次の 2 つの列のデータのみを返す場合にインデックスを使用できます。

EXPLAIN SELECT `goods_category_id1`,`shop_id` FROM `store_goods` WHERE 1

インデックス クエリをカバーすると、場合によってはこのような最適化が無効になることがあります。 MySQL クエリ オプティマイザーは、クエリを実行するときにインデックスがクエリをカバーするかどうかを判断します。インデックスが WHERE 条件をカバーしているが、クエリ全体をカバーしていないとします。評価の結果、カバーリング インデックスを使用しないことが決定された場合、MySQL 5.5 以前のバージョンでは、データが不要であってもデータ行を直接フェッチし、それらをフィルター処理します。

なぜこのようなことが起こるのか、そしてこの問題を解決するためにクエリを書き直す方法を見てみましょう。最初のクエリは次のようになります。

EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY' AND title like '%APOLLO%'

この時点での結果は、カバー インデックスではなく通常のインデックスが使用されることです。その理由は次のとおりです。

• テーブルからすべての列を読み取り、すべての列をカバーするインデックスがないため、クエリ列をカバーするインデックスはありません。理論的には、MySQL には使用できる別のショートカットがあります。つまり、インデックスでカバーされている列が WHERE 条件で使用されるため、MySQL は最初にこのインデックスを使用して対応する俳優を見つけ、次にその役職が一致するかどうかを確認し、条件を満たすすべてのデータ行を読み取ることができます。
• ストレージ エンジン API の以前のバージョン (MySQL 5.5 より前のバージョン) では、MySQL はインデックスで LIKE 操作を使用できず、単純な比較操作 (=、IN、>=) のみをサポートします。 MySQL では、プレフィックス一致の LIKE クエリを比較演算に変換できるため、インデックスで使用できます。ただし、先頭のワイルドカード文字 (つまり、LIKE の先頭の %) により、ストレージ エンジンは一致条件を評価できなくなります。したがって、MySQL はインデックス値ではなく行データを比較のために取得します。
  

インデックスを巧みに組み合わせてクエリを書き換えることで、これを実現する方法があります。インデックスを (artist、title、prod_id) に拡張し、クエリを次のように書き換えることができます。

説明選択 * 
製品から
	参加する （
    prod_idを選択
    製品から
    俳優が「ショーン・キャリー」でタイトルが「%アポロ%」の場合
 ) AS t1 ON (t1.prod_id=products.prod_id)

列へのアクセスを遅らせるため、これを「遅延結合」と呼びます。クエリの最初のフェーズでは、サブクエリ内で一致する行が見つかると、MySQL はカバー インデックスを使用します。クエリ全体でカバーされているわけではありませんが、何もないよりはましです。

この最適化の有効性は、WHERE 条件で見つかったデータの行数によって異なります。製品テーブルに数百万行のデータが含まれていると仮定します。合計 100 万行のデータを使用して、これら 2 つのクエリのパフォーマンスを比較できます。

• ケース 1: 俳優「SEAN CARREY」の商品は 30,000 個あり、そのうち 20,000 個にタイトル「APOLLO」があります。
• ケース 2: 俳優「SEAN CARREY」の商品は 30,000 個あり、そのうち 40 個に「APOLLO」というタイトルが付いています。
• ケース3: 俳優「SEAN CARREY」の商品は50件あり、そのうち10件のタイトルが「APOLLO」です。
  

比較結果を次の表に示します。

結果は次のように説明されます。

• 最初のケースでは、クエリは非常に大きな結果セットを返すため、最適化の効果は見られません。ほとんどの時間はデータの読み取りと送信に費やされます。
• 2 番目のケースでは、カバー インデックスを使用したサブクエリ フィルタリングによって結果セットが小さくなり、パフォーマンスが 5 倍向上します。この効果の理由は、30,000 行のデータセットと比較して、読み取る必要がある行が 40 行だけであるためです。
• 3 番目のケースでは、サブクエリが失敗していることがわかります。カバー インデックス フィルタリングによって返される結果セットが小さすぎるため、データ テーブルから直接データを読み取る場合よりもサブクエリのコストが高くなります。

ほとんどのストレージ エンジンでは、インデックスはインデックスの一部である列へのアクセスのみをカバーできます。ただし、InnoDB は実際にはさらに最適化を行います。 InnoDB のセカンダリ インデックスがリーフ ノードに主キーの値を格納すると考えてください。つまり、InnoDB のセカンダリ インデックスには、InnoDB がカバー インデックスを使用するのに役立つ追加の列が実際に存在します。 たとえば、sakila.actor テーブルは InnoDB を使用し、last_name にインデックスがあるため、この列がインデックスの一部ではない場合でも、このインデックスは主キー actor_id に基づくクエリをカバーできます。

EXPLAIN SELECT 俳優ID、姓
sakila.actor から last_name = 'HOPPER'

上記はMySQLカバーリングインデックスの利点の詳細な内容です。MySQLカバーリングインデックスの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。