MySQL インデックス カバレッジの例の分析

この記事では、MySQL インデックス カバレッジについて例を挙げて説明します。ご参考までに、詳細は以下の通りです。

インデックスカバー

クエリ対象の列がインデックスの一部である場合、クエリはインデックス ファイルに対してのみ実行すればよく、データを見つけるためにディスクに戻る必要はありません。このタイプのクエリは非常に高速であり、「インデックス カバーリング」と呼ばれます。

t15テーブルがあり、そこに結合インデックスが作成されているとします: cp (cat_id, price)

次の SQL ステートメントを使用すると、インデックス カバレッジが発生します。信じられないなら、調べてみましょう。ここでの Extra には、Using index と表示されています。これは、この SQL ステートメントがインデックス カバレッジのみを使用していることを意味します。

cat_id = 1 の場合、t15 から価格を選択します。

質問を見てみましょう。電子メール列にインデックスが付いた t11 テーブルを作成します。

次のクエリを使用するとします。

t11 から eamil を選択します。right(email,4)='.com'

クエリ分析:

まずはExtraを見てみましょう。ここにUsing indexとありますが、これはインデックスカバレッジが使われていることを意味します。possible_keysがNULLになっているのは、MySQLの関数を使用しているため、クエリ中にメールインデックスが使われていないのですが、キーがメールなので、ソートにインデックスが使われていることを意味します。信じられない方は、データを印刷して見てください。

ここのデータはソートされています。元データはこんな感じです。

インデックスカバーの問題

テーブルAを作成（
id varchar(64) 主キー、
ver int、
…
）

テーブルには、idとverの結合インデックスを持つ、非常に長いフィールドvarbinary(3000)がいくつかあり、合計10,000レコードになります。

select id from A order by idなぜこんなに遅いのでしょうか?
そしてselect id from A order by id,ver非常に速いです

質問: id と (id,ver) はどちらもインデックスを持っており、select id は「インデックス カバレッジ」の効果を生み出すはずです。前者は遅く、後者は速いのはなぜですか?

アイデア: InnoDB クラスター化インデックスと MyISAM インデックスの違い、これら 2 つの観点からインデックス カバレッジを考慮する

(1) このテーブルが MyISAM インデックスを使用していると仮定すると、これら 2 つの SQL 文はどちらもデータを検索するために行を戻る必要がないため、速度は同様になるはずです。

（2）このテーブルがInnoDBインデックスを使用していると仮定すると、SQL文select id from A order by idは主キーインデックスを使用します。これは、InnoDBの各主キーが各行のデータを保持し、この問題には特に大きなフィールドがいくつかあるため、idの検索が比較的遅くなるためです。SQL文select id from A order by id,verはid、ver結合インデックスを使用します。InnoDBストレージエンジンでは、セカンダリインデックスに主キーインデックスのアプリケーションが格納されるため、セカンダリインデックスには行のデータは保持されません。この場合、（id、ver）インデックスでidを検索する方が高速になります。対応するノードツリーが見つかったら、主キーインデックスの場所を再度検索するだけで、行のデータを取得できます。これは高速です。

推測する:

（1）テーブルがMyISAMエンジンを使用している場合、2つのステートメントの速度に大きな違いはありません。
（2）クラスター化インデックスのため、InnoDBテーブルのIDインデックスはディスク上の複数のブロックにまたがる必要があり、速度が低下します。
（３）InnoDBエンジンであっても、長いvarbinay列がない場合、２つの文の速度に大きな差はありません。

テーブル t12 (ストレージ エンジン MyISAM) には、主キー インデックスと (id,ver) インデックス、およびいくつかの大きな可変長フィールドがあります。テスト推論 1

InnoDB ストレージ エンジンを備えたテーブル t13 には、主キー インデックスと (id,ver) インデックス、およびいくつかの大きな可変長フィールドがあります。

テーブル t14、ストレージ エンジンは InnoDB、主キー インデックスと (id,ver) インデックスがあり、大きな可変長フィールドはありません。 補論 3

t12、t13、t14 の各テーブルには 10,000 個のデータが含まれており、テストが実行されます。テスト結果は次のようになり、推論は正しいです。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL インデックス操作スキルの概要」、「MySQL 共通関数の概要」、「MySQL ログ操作スキルの概要」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアド プロシージャ スキルの概要」、および「MySQL データベース ロック関連スキルの概要」。

この記事が皆様のMySQLデータベース設計に役立つことを願っています。