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

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」です。

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

データセット元のクエリ最適化されたクエリ
最初のケース5qps 5qps
2番目のケース7qps 35qps
3番目のケース2400qps 2000qps

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

  • 最初のケースでは、クエリは非常に大きな結果セットを返すため、最適化の効果は見られません。ほとんどの時間はデータの読み取りと送信に費やされます。
  • 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の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQLはカバーインデックスを使用してテーブルリターンを回避し、クエリを最適化します。
  • MySQLカバーインデックスの使用例
  • MySQLのカバーインデックスに関する知識ポイントのまとめ
  • インデックスとテーブルリターンをカバーするMySQLの使い方
  • MySQLカバーインデックスの詳しい説明
  • MySQL パフォーマンス最適化の事例 - インデックス共有のカバー
  • MySQL パフォーマンス最適化のケーススタディ - インデックスと SQL_NO_CACHE をカバー

<<:  JavaScriptはXiaomi Mall公式サイトの完全なページ実装プロセスを模倣します

>>:  border-image を使用してテキストバブルの境界線を実装する方法のサンプルコード

推薦する

IE6のmin-widthとmin-heightと互換性を持たせる簡単な方法

ウェブサイトがワイドスクリーンの場合、ブラウザ ウィンドウを左右にドラッグすると、ウェブサイトの幅が...

html.cssオーバーフローの包括的な理解

html.cssオーバーフローの包括的な理解XML/HTML コードコンテンツをクリップボードにコピ...

Vue.js での $emit の使用に関する詳細な説明

1. 親コンポーネントは props を使用して子コンポーネントにデータを渡すことができます。 2....

Vueはマルチタブコンポーネントを実装します

効果を直接確認するために、リロード、左を閉じる、右を閉じる、その他の機能を閉じるなどの右クリック メ...

テキストエリアの使用に関する注意事項

なぜテキストエリアについて具体的に言及するのでしょうか?なぜなら、textarea ノードは実際には...

Server-U 14バージョンのインストールと使用方法

Server-Uソフトウェアの紹介Server-U は非常に強力なファイル マネージャーです。FTP...

Vueプロジェクトでvuexを使用する方法

目次Vuex とは何ですか? Vuex 使用サイクル図私のストアディレクトリvuexの例の実装要約す...

MySQL で削除されたレコードが有効にならない理由のトラブルシューティング

オンライン MySQL トランザクションの問題の記録先週の金曜日、大きなテーブルを削除する操作を実行...

Echarts バー水平棒グラフのサンプルコード

目次横棒グラフデータとスタイルを動的に更新するeChartsの幅と高さの適応の問題を解決する縦棒グラ...

Alibaba Cloud Centos7.X で外部にポートを開く方法

一言で言えば、大手メーカーからクラウド サーバーを購入する場合は、セキュリティ グループに移動して、...

MySQL ダーティ ページ フラッシュとテーブル スペースの縮小の原理の分析

mysql ダーティページWAL メカニズムにより、InnoDB はステートメントを更新するときに、...

リモート接続を許可するようにMySQLを変更する方法

MySQLリモート接続の問題に関しては、会社で働いているときに誰かのコンピュータに保存されているMy...

Zabbix は DingTalk のアラーム機能を画像付きで設定します

実装のアイデア:まず、アラーム情報にはitemidが必要です。これは前提条件です。情報に渡されるパラ...

MySQL 圧縮パッケージ版 zip インストール設定方法

圧縮版の記事ではデータの初期化がされていないなどいくつか問題があったため、Windows にインスト...

MySQL バックアップ スクリプトの書き方

序文:データベースのバックアップの重要性は、特にデータの損失が深刻な結果を招く可能性がある実稼働環境...