MySQL インデックスの詳細な説明

1. インデックスの基本

1.1 はじめに

MySQL では、インデックスは「キー」とも呼ばれます。これは、ストレージ エンジンがレコードをすばやく検索するために使用するデータ構造です。

インデックスは良好なパフォーマンスに不可欠です。特に、テーブル内のデータ量が増加すると、インデックスがパフォーマンスに与える影響がますます重要になります。

インデックスの最適化は、クエリ パフォーマンスを最適化する最も効果的な手段です。本当に最適なインデックスを作成するには、多くの場合、SQL クエリ ステートメントを書き直す必要があります。

1.2 インデックスの仕組み

MySQL でインデックスがどのように機能するかを理解する最も簡単な方法は、本の索引を見ることです。たとえば、本の中でトピックを見つけたい場合、通常は最初に索引を見ます。対応する章とページ番号がわかれば、必要なものをすぐに見つけることができます。

MySQL では、ストレージ エンジンは同様の方法でインデックスを使用します。まず、インデックス内の対応する値を検索し、次に一致するインデックス レコードに基づいて対応するデータ行を見つけ、最後にデータ結果セットをクライアントに返します。

1.3 インデックスの種類

MySQL で通常参照されるインデックス タイプは次のとおりです。

• 総合索引

  共通インデックス (インデックスまたはキー) とも呼ばれる通常のインデックスは、通常、クエリの効率を向上させることができます。データ テーブルには複数の通常のインデックスを設定できます。通常のインデックスは、最も一般的に使用されるインデックス タイプです。インデックス タイプが明示的に指定されていない場合、ここで言及しているインデックスは通常のインデックスです。

• 主キーインデックス

  主キー インデックス (主キー)。主キーとも呼ばれます。クエリの効率を向上させ、一意の制約を提供できます。テーブルには主キーを 1 つだけ設定できます。自動増分としてマークされたフィールドは主キーである必要がありますが、主キーは必ずしも自動増分されるわけではありません。一般的に、主キーは意味のないフィールド (数値など) に定義され、主キーのデータ型は数値であることが望ましいです。

• ユニークインデックス

  一意のインデックスはクエリの効率を向上させ、一意の制約を提供できます。テーブルには複数の一意のインデックスを設定できます。

• 全文索引

  全文インデックス (Full Text) は全文検索のクエリ効率を向上させることができ、通常は Sphinx に置き換えられます。ただし、Sphinx は中国語の検索をサポートしていません。Coreseek は中国語をサポートする全文検索エンジンで、中国語の単語分割機能を備えた Sphinx としても知られています。実際のプロジェクトでは、Coreseek を使用しました。

• 外部キーインデックス

  外部キーインデックス (Foreign Key) は、外部キーとも呼ばれ、クエリの効率を向上させることができます。外部キーは、他の対応するテーブルの主キーと自動的に関連付けられます。外部キーの主な機能は、レコードの一貫性と整合性を確保することです。

  注: InnoDB ストレージ エンジンを使用するテーブルのみが外部キーをサポートします。外部キー フィールドにインデックス名が指定されていない場合は、自動的に生成されます。親テーブル (カテゴリ テーブルなど) のレコードを削除する場合は、まず子テーブル (記事テーブルなどの外部キーを持つテーブル) の対応するレコードを削除する必要があります。そうしないと、エラーが発生します。 テーブルを作成するときに、外部キー (cate_id) が cms_cate (id) を参照するなど、フィールドに外部キーを設定できます。外部キーの効率はあまり良くないため、外部キーを使用することはお勧めしませんが、データの一貫性と整合性を確保するために、外部キーの概念を使用する必要があります。

1.4 インデックス作成方法

MySQL では、インデックスはサーバー レベルではなく、ストレージ エンジン レベルで実装されます。 MySQL でサポートされているインデックス作成方法は、主に次のとおり、インデックス タイプ (広義) とも呼ばれます。

Bツリーインデックス

タイプが指定されていない場合は、B ツリー インデックスを参照している可能性があります。ストレージ エンジンによって B ツリー インデックスの使用方法が異なり、パフォーマンスも異なります。たとえば、MyISAM はプレフィックス圧縮テクノロジを使用してインデックスを小さくしますが、InnoDB はインデックスを元のデータ形式で保存します。たとえば、MyISAM はデータの物理的な場所によってインデックス行を参照しますが、InnoDB は主キーに基づいてインデックス行を参照します。

B-Tree はインデックス列を順番に格納するため、範囲データの検索に非常に適しています。ストレージ エンジンが必要なデータを取得するためにテーブル全体のスキャンを実行する必要がなくなるため、データへのアクセスを高速化できます。

インデックスに複数のフィールド (列) の値が含まれている場合、それは複合インデックスです。複合インデックスは、列が作成された順序に基づいて複数のフィールド値を順序付けます。次のように：

テーブルを作成する人（
 id int unsigned not null auto_increment primary key comment '主キーID',
 last_name varchar(20) NULLでない デフォルト '' コメント '姓',
 first_name varchar(20) NULLでない デフォルト '' コメント '名前',
 誕生日はNULLではありません。デフォルトは「1970-01-01」です。コメントは「生年月日」です。
 性別 tinyint unsigned not null デフォルト 3 コメント '性別: 1 男性、2 女性、3 不明'、
 キー(姓、名、誕生日)
) エンジン=innodb デフォルトの文字セット=utf8;

次のデータも people テーブルに挿入されています。

テーブルの各行の姓、名、誕生日の列の値を含む複合インデックス キー (last_name、first_name、birthday) を作成しました。インデックスもこの順序に従ってソートされ、保存されます。2 人の人物が同じ名と姓を持つ場合、インデックスは生年月日に従ってソートされ、保存されます。

B ツリー インデックスは、完全なキー値、キー値の範囲、またはキー プレフィックス検索に適用できます。キー プレフィックス検索は、左端のプレフィックスに基づく検索にのみ適用できます。

複合インデックスは、次のタイプのクエリに効果的です。

完全な価値一致

完全な値の一致とは、インデックス内のすべての列が一致することを指します。たとえば、姓が Allen、名が Cuba、生年月日が 1960-01-01 の人を検索します。

SQL ステートメントは次のとおりです。

last_name='Allen'、first_name='Cuba'、birthday='1960-01-01' の people から id、last_name、first_name、birthday を選択します。

。

左端の接頭辞に一致

たとえば、インデックスの最初の列のみを使用して、姓が Allen であるすべての人物を見つけることができます。 SQL ステートメントは次のとおりです。

last_name='Allen' の人から id、last_name、first_name、birthday を選択します。

一致する列プレフィックス

たとえば、インデックスの最初の列の値の先頭のみを一致させて、姓が A で始まるすべての人物を見つけることができます。 SQL ステートメントは次のとおりです。

last_name が 'A%' のような人から id、last_name、first_name、birthday を選択します。

一致する範囲の値

たとえば、範囲は Allen と Clinton の間の姓を持つ人々と一致します。 SQL ステートメントは次のとおりです。

last_name が 'Allen' と 'Clinton' の間である人から id、last_name、first_name、birthday を選択します。

ここでも、インデックスの最初の列のみが使用されます。

最初の列と完全に一致し、次の列と範囲が一致します

たとえば、姓が Allen で、名が文字 C で始まる人を検索します。つまり、複合インデックスの最初の列は完全に一致し、2 番目の列は範囲一致します。 SQL ステートメントは次のとおりです。

last_name = 'Allen' かつ first_name が 'C%' である people から id、last_name、first_name、birthday を選択します。

インデックスのみにアクセスするクエリ

B ツリーは通常、「インデックスのみのクエリ」をサポートします。つまり、クエリはデータ行にアクセスせずにインデックスのみにアクセスする必要があります。これは、後述する「カバーインデックス」の最適化に関連しています。

複合インデックスが失敗する状況をいくつか示します。

（１）複合インデックスの左端の列から検索を開始しない場合は、そのインデックスは使用できません。たとえば、上記の例では、このインデックスを使用して Cuba という名前の人物を検索することはできません。また、特定の誕生日を持つ人物を検索することもできません。これは、これら 2 つの列のどちらも複合インデックス キー (last_name、first_name、birthday) の左端の列ではないためです。同様に、姓が特定の文字で終わる人を検索することはできません。つまり、類似範囲クエリのあいまい一致演算子 % が最初に配置されていると、インデックスが無効になります。

（２）検索中にインデックス内の列がスキップされた場合、最初のインデックス列のみが使用され、後続のインデックス列は無効になります。たとえば、特定の日に生まれた Allen という姓を持つ人を検索します。ここで検索する場合、検索名 (first_name) が指定されていないため、MySQL は複合インデックスの最初の列 (つまり last_name) のみを使用できます。

（３）クエリに特定の列に対する範囲クエリが含まれている場合、この列の右側にあるすべての列はインデックス最適化を使用して検索することはできません。たとえば、クエリ条件が where last_name='Allen' and first_name like 'C%' and birthday='1992-10-25' の場合、like here は範囲条件であるため、このクエリではインデックスの最初の 2 つの列のみを使用できます。範囲クエリ列の値の数が制限されている場合は、範囲条件ではなく複数の等号条件を使用して、右側の列でもインデックスを使用できるように最適化できます。

複合インデックス内の列の順序がいかに重要であるかがわかったので、これらの制限はすべてインデックス列の順序に関連しています。パフォーマンスを最適化する場合、さまざまな種類のクエリ要件を満たすために、同じ列を持つインデックスを異なる順序で使用する必要がある場合があります。たとえば、テーブルでは、key(last_name, first_name, birthday) と key(first_name, last_name, birthday) という 2 つの複合インデックスが必要になる場合があります。

B-Tree インデックスは最も一般的に使用されるインデックス タイプです。以下では、特に指定がない限り、B-Tree インデックスを指します。

1. ハッシュインデックス

ハッシュ インデックスはハッシュ テーブルに基づいて実装されます。インデックスのすべての列と完全に一致するクエリのみが有効です。 MySQL では、メモリ エンジンのみがハッシュ インデックスを明示的にサポートします。

2. 空間データインデックス（Rツリー）

MyISAM エンジンは空間インデックスをサポートしており、地理データ ストレージとして使用できます。 B-Tree インデックスとは異なり、このインデックスではプレフィックス クエリは必要ありません。

3. 全文インデックス

フルテキスト インデックスは、インデックス内の値を直接比較するのではなく、テキスト内のキーワードを検索する特殊なタイプのインデックスです。全文インデックスのマッチング方法は、他のインデックスとはまったく異なります。単純な where 条件のマッチングではなく、検索エンジンの動作に似ています。フルテキスト インデックスと B ツリー インデックスを同じ列に同時に作成できます。フルテキスト インデックスは、通常の where 条件操作ではなく、Match Against 操作に適しています。

インデックスには、1 つの列 (つまり、フィールド) または複数の列の値を含めることができます。インデックスに複数の列が含まれている場合、一般的に複合インデックスと呼ばれます。このとき、MySQL はインデックスの左端のプレフィックス列のみを効率的に使用できるため、列の順序は非常に重要です。 2 つの列を含む 1 つのインデックスを作成することは、1 つの列のみを含む 2 つのインデックスを作成することとは大きく異なります。

1.5 インデックスの利点

インデックスを使用すると、MySQL は必要なデータをすばやく見つけることができますが、これはインデックスの唯一の機能ではありません。

最も一般的な B ツリー インデックスはデータを順番に格納するため、MySQL は Order By および Group By 操作に使用できます。データは順番に格納されるため、B-Tree は関連する列の値を一緒に格納します。最後に、実際の列の値もインデックスに格納されるため、クエリによっては、クエリのためにテーブルに戻ることなく、インデックスのみを使用してすべてのデータを取得できる場合もあります。この機能に基づいて、インデックスには次の 3 つの利点があると結論付けることができます。

• インデックスにより、MySQL サーバーがスキャンする必要があるデータの量が大幅に削減されます。
• インデックスは、サーバーがソートや一時テーブルを回避するのに役立ちます。
• インデックスはランダム I/O をシーケンシャル I/O に変換できます。

さらに、インデックスがクエリ ステートメントに適しているかどうかを評価するために、「3 つ星システム」を使用する人もいます。 3 つ星システムは主に、インデックスが関連レコードをまとめることができる場合は 1 つ星、インデックス内のデータの順序が検索の配置順序と一致している場合は 2 つ星、インデックス内の列にクエリに必要なすべての列が含まれている場合は 3 つ星を意味します。

インデックスは常に最善のツールであるとは限りませんし、インデックスが多ければ多いほど良いというわけでもありません。一般に、インデックスは、ストレージ エンジンがレコードをすばやく見つけられるようにする利点が、インデックスによって発生する余分な作業を上回る場合にのみ役立ちます。

非常に小さいテーブルの場合、ほとんどの場合、単純なフルテーブルスキャンの方が効率的であり、インデックスを作成する必要はありません。中規模から大規模のテーブルの場合、インデックスの利点は非常に明白です。

2. 高性能インデックス戦略

インデックスを正しく作成して使用することが、高パフォーマンスのクエリの基礎となります。これまで、さまざまな種類のインデックスとその長所と短所を紹介してきました。次に、これらのインデックスの利点を実際に活用する方法を見てみましょう。次のセクションでは、インデックスを効率的に使用する方法を説明します。

2.1 独立した列

インデックスを不適切に使用したり、MySQL が既存のインデックスを使用できないようにするクエリがよく見られます。 SQL クエリ内の列が独立していない場合、MySQL はインデックスを使用しません。 「独立した列」とは、インデックス列が式または関数パラメータの一部になることができないことを意味します。

たとえば、次の SQL クエリ ステートメントでは、主キー インデックス ID を使用できません。

id+1=3 の people から id、last_name、first_name、birthday を選択します。

上記の where 式は実際には where id=2 に短縮できることは簡単にわかりますが、MySQL はこの式を自動的に解析できません。 where 条件を簡素化し、比較演算子の片側に常にインデックス列だけを配置する習慣を身につける必要があります。したがって、主キー インデックスを使用する場合、正しい記述方法は次のとおりです。

id=2 の people から id、last_name、first_name、birthday を選択します。

もう一つよくある間違いは次のとおりです。

to_days(current_date()) - to_days(date_col) <= 10 となる ... から ... を選択します。

2.2 プレフィックスインデックスとインデックスの選択性

場合によっては、非常に長い文字列にインデックスを付ける必要があり、インデックスが大きくなって遅くなります。通常の解決策は、列の最初の数文字のみをインデックス化することです。これにより、インデックス スペースが大幅に節約され、インデックスの効率が向上します。ただし、インデックスの選択性も低下します。インデックスの選択性は、データ テーブル内のレコードの総数に対する一意のインデックス値の数 (カーディナリティとも呼ばれます) の比率を指し、範囲は 0 から 1 です。

ユニーク インデックスの選択性は 1 であり、これは最高のインデックス選択性であり、最高のパフォーマンスを実現します。

一般的に、列プレフィックスの選択性は、クエリのパフォーマンス要件を満たすのに十分に高いです。 Blob、Text、または非常に長い Varchar 型の列の場合、MySQL ではこれらの列の全長をインデックス化できないため、プレフィックス インデックスを使用する必要があります。つまり、列の最初の数文字のみをインデックス化します。

プレフィックス インデックスを追加するには:

alter table user add key(address(8)); // アドレスフィールドの最初の8文字のみをインデックスします

プレフィックス インデックスは、インデックスを小さくして高速化するための効果的な方法ですが、MySQL ではプレフィックス インデックスを Order By および Group By 操作に使用できず、また、プレフィックス インデックスをカバリング スキャンに使用できないという欠点があります。

たとえば、ドメインのすべての電子メール アドレスを検索する場合など、サフィックス インデックスが役立つ場合があります。ただし、MySQL はサフィックス インデックスをネイティブにサポートしていません。文字列を逆順に保存し、それに基づいてプレフィックス インデックスを作成し、トリガーを通じてこのインデックスを維持することができます。

2.3 複数列インデックス

マルチカラムインデックスとは、複数の列を含むインデックスです。列の順序に注意する必要があります。複数列インデックスは複合インデックスとも呼ばれます。たとえば、前のキー (last_name、first_name、birthday) は複合インデックスです。

よくある間違いは、列ごとに個別のインデックスを作成したり、複数の列に間違った順序でインデックスを作成したりすることです。

まず最初の問題を見てみましょう。列ごとに個別のインデックスを作成します。show create table からこの状況を簡単に確認できます。

テーブルtを作成（
 c1 整数、
 c2 整数、
 c3 整数、
 キー(c1)、
 キー(c2)、
 キー(c3)
);

この誤ったインデックス作成戦略は、通常、「where 条件ですべての列にインデックスを作成する」などの専門家からの漠然としたアドバイスを聞くことによって発生します。

ほとんどの場合、複数の列に独立した単一列インデックスを作成しても、MySQL クエリのパフォーマンスは向上しません。 MySQL 5.0 以降のバージョンでは、インデックス マージと呼ばれる戦略が導入されており、テーブル上の複数の単一列インデックスを使用して、指定された行をある程度特定できます。しかし、効率は依然として複合指数よりもはるかに悪いです。

たとえば、テーブル film_actor には、フィールド film_id と actor_id に単一列のインデックスがあります。SQL クエリ ステートメントは次のようになります。

actor_id=1 または film_id=1 の場合、film_actor から film_id、actor_id を選択します。

MySQL 5.0 以降のバージョンでは、クエリはこれらの 2 つの単一列インデックスを使用して同時にスキャンし、結果をマージできます。このアルゴリズムには、OR 条件による結合、AND 条件による積集合、最初の 2 つの条件を組み合わせた結合と積集合の 3 つのバリエーションがあります。

上記のクエリは、2 つのインデックス スキャンの結合を使用しており、これは explain の Extra 列から確認できます (結合文字は Extra 値に表示されます)。

film_actor から film_id、actor_id を選択します (actor_id=1 または film_id=1\G)。

インデックス マージ戦略は最適化の結果である場合もありますが、多くの場合、テーブル上のインデックスが適切に構築されていないことを示しています。

• 複数のインデックスに対して交差演算 (通常は複数の and 条件を使用) が行われる場合、通常は、複数の独立した単一列インデックスではなく、関連するすべての列を含む複合インデックスが必要であることを意味します。

• 複数のインデックスが結合されると (通常は複数の or 条件を使用)、アルゴリズムのキャッシュ、ソート、およびマージ操作で大量の CPU およびメモリ リソースが消費されるのが一般的です。この時点で、クエリは 2 つのクエリ結合として書き換えることができます。

film_actor から film_id、actor_id を選択します。actor_id が 1 の場合
すべて結合
film_id=1 かつ actor_id<>1 の場合、film_actor から film_id、actor_id を選択します。

説明結果にインデックス結合が見つかった場合は、SQL クエリ ステートメントとテーブル構造を慎重に確認し、それが最適かどうか、複数のクエリ結合メソッドに分割できるかどうかなどを確認する必要があります。

2.4 適切なインデックス列の順序を選択する

最も混乱を招くのは、複合インデックス内の列の順序です。複合インデックスでは、正しい列の順序はインデックスを使用するクエリによって異なり、並べ替えやグループ化のニーズを最適に満たすためにも考慮する必要があります。

インデックス列の順序とは、インデックスが最初に左端の列でソートされ、次に 2 番目の列、3 番目の列というようにソートされることを意味します。したがって、列の順序と正確に一致する order by、group by、distinct などの句のクエリ要件を満たすために、インデックスを昇順または降順でスキャンできます。

並べ替えやグループ化が重要でない場合は、最も選択性の高い列を複合インデックスの左端 (最初) に配置するのがよい場合がよくあります。現時点では、インデックスは where 条件の検索を最適化するためにのみ使用されます。ただし、この場合、インデックスを最も選択的にするために、最も頻繁に実行されるクエリに基づいてインデックス列の順序を調整する必要もあるかもしれません。

次のクエリを例に挙げます。

staff_id=2かつcustomer_id=500の場合、支払いから*を選択します。

key(staff_id, customer_id) または key(customer_id, staff_id) のどちらでインデックスを作成する必要がありますか?いくつかのクエリを実行して、テーブル内の値の分布を確認し、どの列がより選択的であるかを判断できます。たとえば、次のクエリを使用して予測できます。

payment_G から sum(staff_id=2)、sum(customer_id=500) を選択します

結果では、sum(staff_id=2) の値が 7000、sum(customer_id=500) の値が 60 であると示されています。このことから、上記のクエリでは、customer_id がより選択的であり、インデックスの先頭に配置する必要があることがわかります。つまり、key(customer_id, staff_id) を使用します。

ただし、これを行う際に注意すべき点が 1 つあります。クエリの結果は、選択した特定の値に大きく依存するということです。上記の方法で最適化すると、条件値が異なるクエリに対して不公平になる可能性があり、サーバー全体のパフォーマンスが悪化する可能性もあります。

pt-query-digest などのツールのレポートから「最悪のクエリ」が抽出される場合、上記の方法に従って選択されたインデックス順序は非常に効率的であることがよくあります。実行する類似の特定のクエリがない場合は、経験則に従うのが最適です。経験則では、特定の条件値のクエリではなく、グローバルなカーディナリティと選択性が考慮されるためです。経験則によれば、選択性は次のように決定できます。

count(distinct staff_id)/count(*) を staff_id_selectivity として選択します。
count(distinct customer_id)/count(*) as customer_id_selectivity、
支払いから\G

結果では、staff_id_selectivity の値は 0.001 で、customer_id_selectivity の値は 0.086 であると示されています。値が大きいほど選択性が高くなることが分かっています。したがって、customer_id の選択性は高くなります。したがって、これをインデックスの最初の列として配置します。

テーブルpaymentを変更し、キー(customer_id、staff_id)を追加します。

選択性とグローバル カーディナリティに関する経験則を研究して分析することは価値がありますが、クエリのパフォーマンスに大きな影響を与える可能性がある order by や group by などの要素の影響を必ず覚えておいてください。

2.5 クラスター化インデックス

クラスター化インデックスは、個別のインデックス タイプではなく、データを保存する方法です。正確な詳細は実装方法によって異なりますが、InnoDB のクラスター化インデックスは実際には B ツリー インデックスとデータ行を同じ構造で格納します。

テーブルにクラスター化インデックスがある場合、そのデータ行は実際にはインデックスのリーフ ページに格納されます。つまり、リーフ ページには行のすべてのデータが含まれ、ノード ページにはインデックス列のデータのみが含まれます。

ストレージ エンジンはインデックスの実装を担当するため、すべてのストレージ エンジンがクラスター化インデックスをサポートしているわけではありません。このセクションでは InnoDB に焦点を当てていますが、ここで説明する内容は、クラスター化インデックスをサポートするすべてのストレージ エンジンに適用できます。

InnoDB は主キーによってデータをクラスタ化します。主キーが定義されていない場合、InnoDB は代わりに一意の空でないインデックスを選択します。そのようなインデックスがない場合、InnoDB はクラスター化インデックスとして機能する主キーを暗黙的に定義します。

クラスター化インデックスの利点:

• 関連データをまとめて保存できます。
• より高速なデータアクセス。クラスター化インデックスは、インデックスとデータを同じ B ツリーに格納します。そのため、クラスター化インデックスからデータを取得する方が、非クラスター化インデックスからデータを取得するよりも通常は高速です。
• カバーリングインデックススキャンを使用するクエリでは、ノードページから主キー値を直接使用できます。

テーブルやクエリを設計する際に上記の利点を最大限に活用できれば、パフォーマンスを大幅に向上させることができます。

クラスター化インデックスの欠点:

• クラスター化インデックスは、I/O 集約型アプリケーションのパフォーマンスを最大化しますが、すべてのデータがメモリに格納されている場合、アクセスの順序はそれほど重要ではなく、クラスター化インデックスの利点はほとんどありません。
• 挿入速度は挿入順序に大きく依存します。主キーの順序で挿入するのが、InnoDB テーブルにデータを挿入する最も速い方法です。ただし、データが主キーの順序で挿入されていない場合は、操作が完了した後に OPTIMIZE TABLE コマンドを使用してテーブルを再編成するのが最適です。
• クラスター化インデックス列を更新すると、InnoDB は更新された各行を新しい場所に移動する必要があるため、コストがかかります。
• クラスター化インデックスに基づくテーブルでは、新しい行が挿入されたり、主キーが更新されたりしたときに「ページ分割」の問題が発生し、行を移動する必要が生じることがあります。ページ分割により、テーブルがより多くのディスク領域を占有することになります。

InnoDB では、クラスター化インデックスがテーブルそのものなので、MyISAM のように個別の行ストレージは必要ありません。クラスター化インデックスの各リーフ ノードには、主キー値、トランザクション ID、トランザクションのロールバック ポインター、MVCC (マルチバージョン コントロール)、および残りのすべての列が含まれます。

InnoDB のセカンダリ インデックス (非クラスター化インデックス) は、クラスター化インデックスとは大きく異なります。セカンダリ インデックスのリーフ ノードには、「行ポインタ」ではなく、主キー値が格納されます。したがって、セカンダリ インデックスを介してデータを検索する場合、2 つのインデックス検索が実行されます。ストレージ エンジンは、まずセカンダリ インデックスのリーフ ノードを検索して対応する主キー値を取得し、次にこの主キー値に基づいてクラスター化インデックス内の対応するデータ行を検索する必要があります。

データ行が順番に挿入されるようにするには、主キーを auto_increment として定義するのが最も簡単な方法です。 InnoDB を使用する場合は、可能な限り主キーの順序でデータを挿入し、可能な限り単調に増加する主キー値を使用して新しい行を挿入するようにしてください。

同時実行性の高いワークロードの場合、InnoDB に主キーの順序で挿入すると、重大な主キー値の競合問題が発生する可能性があります。この問題は非常に深刻であり、Baidu で検索することで自分で解決できます。

2.6 カバーインデックス

通常、クエリの where 条件に基づいて適切なインデックスが作成されますが、これはインデックス最適化の 1 つの側面にすぎません。優れたインデックスを設計するには、where 条件だけでなく、クエリ全体を考慮する必要があります。

インデックスは確かにデータを見つける効率的な方法ですが、MySQL ではインデックスを使用して、データ行を読み取らなくても列データを直接取得することもできます。インデックスのリーフ ノードにクエリ対象のすべてのデータがすでに含まれている場合、クエリのためにテーブルに戻る必要があるのはなぜですか。

インデックスにクエリする必要があるすべてのフィールド (列) の値が含まれている (またはカバーしている) 場合、そのインデックスを「カバー インデックス」と呼びます。

カバーリングインデックスは非常に便利で、パフォーマンスを大幅に向上させることができます。クエリがテーブルに戻って行を取得するのではなく、インデックスをスキャンするだけで済むとしたら、どれほどの利点が得られるか考えてみましょう。

• 通常、インデックス エントリはデータ行のサイズよりもはるかに小さいため、インデックスのみを読み取る必要がある場合、MySQL はデータ アクセスの量を大幅に削減できます。カバーリング インデックスはデータよりも小さく、メモリに簡単に配置できるため、I/O を集中的に使用するアプリケーションにも役立ちます。
• インデックスは列の値によって順番に格納されるため (少なくとも 1 ページ内)、I/O 集約型の範囲クエリでは、ディスクからすべての行をランダムに読み取る場合よりもデータ I/O が大幅に少なくなります。
• カバーリング インデックスは、InnoDB のクラスター化インデックスのため、InnoDB テーブルに特に役立ちます。 InnoDB のセカンダリ インデックス (非クラスター化インデックス) は、行のプライマリ キー値をリーフ ノードに格納するため、セカンダリ プライマリ キーでクエリをカバーできる場合は、プライマリ キー インデックスのセカンダリ クエリを回避できます。

これらすべてのシナリオでは、インデックス内でクエリ全体を実行するコストは、通常、テーブルに戻るよりもはるかに低くなります。

B ツリー インデックスはカバー インデックスにすることができますが、ハッシュ インデックス、空間インデックス、およびフルテキスト インデックスはカバー インデックスをサポートしていません。

インデックスによってカバーされるクエリ (インデックス カバー クエリとも呼ばれます) を開始すると、explain の Extra 列に「インデックスの使用」情報が表示されます。のように：

人からIDを選択する方法を説明します。
people から last_name を選択する方法を説明します。
peopleからid、first_nameを選択する方法を説明します。
people から last_name、first_name、birthday を選択する方法を説明します。
last_name='Allen' である people から last_name、first_name、birthday を選択することを説明します。

people テーブルは、上のセクションで作成したものです。このテーブルには、主キー (id) インデックスと複数列の複合インデックス キー (last_name、first_name、birthday) が含まれています。この 2 つのインデックスは、4 つのフィールドの値をカバーします。 SQL クエリ ステートメントに、これら 4 つのフィールド内でクエリされるすべてのフィールドが含まれている場合、このクエリはインデックス カバレッジ クエリと呼ばれます。インデックスに SQL クエリ ステートメントでクエリされるすべてのフィールドの値が含まれている場合、このインデックスはクエリ ステートメントのカバー インデックスになります。たとえば、key(last_name, first_name, birthday) は、select last_name,first_name from people のカバー インデックスです。

2.7 インデックススキャンを使用したソート

MySQL には、並べ替え操作 (order by) とインデックス順序スキャンによる自動並べ替え (つまり、インデックスによる並べ替え) という 2 つの方法で、順序付けられた結果セットを生成できます。実際、これら 2 つのソート操作は競合しません。つまり、order by はソートにインデックスを使用できます。

正確に言うと、MySQL は結果セットを次の 2 つの方法でソートします。

1. インデックスソート

インデックスソートとは、インデックス内のフィールド値を使用して結果セットをソートすることを意味します。 explain の type パラメータの値が index の場合、MySQL はインデックス ソートを使用する必要があることを意味します。のように：

人からIDを選択する方法を説明します。
select id,last_name from people order by id desc; を説明します。
people から last_name を選択する方法を説明します。
select last_name from people order by last_name; を説明します。
select last_name from people order by last_name desc; を説明します。

注意: explain の type の値がインデックスでない場合でも、インデックスでソートされる場合があります。のように：

ID >3 の people から ID を選択する方法について説明します。
select id,last_name from people where id >3 order by id desc; を説明します。

2. ファイルの並べ替え

ファイルのソートとは、追加の操作を通じてクエリ結果セットをソートし、それをクライアントに返すことを指します。このソート方法ではインデックスソートは使用されないため、効率は低くなります。 MySQL では filesort と呼ばれるファイル ソートでは、必ずしもディスク ファイルを使用するわけではありません。

explain の Extra パラメータの値に「Using filesort」という文字列が含まれている場合、ファイルのソートが進行中であることを意味します。この時点で、インデックスまたは SQL クエリ ステートメントを最適化する必要があります。のように：

select id,last_name,first_name from people where id > 3 order by last_name; を説明します。

MySQL では、ルックアップとクエリの両方を満たすために同じインデックスを使用できます。可能であれば、インデックスを設計するときに、両方の操作を可能な限り満たすようにしてください。

インデックスの並べ替えは、インデックス列に where 条件のフィールドと order by のフィールドが含まれ、インデックス内の列の順序が where + order by に含まれるすべてのフィールドの順序と一致している場合にのみ使用できます (注意: order by は where の後に来ます)。

ここで、上記の SQL ステートメントを最適化して、インデックス ソートを活用してみましょう。

まず、複数列のインデックスを追加します。

テーブル people を変更し、キー (id、last_name) を追加します。

key(id,last_name) のみを追加した場合、インデックス ソートは使用できないことがわかります。これは、where + order by ステートメントがインデックスの左端のプレフィックス要件も満たす必要があり、where id > 3 が範囲条件であるため、後続の order by last_name でインデックス key(id,last_name) を使用できなくなるためです。

次に、SQL ステートメントの order by last_name を order by id,last_name に変更します。

注: SQL クエリ ステートメントが複数のテーブルを結合する結合クエリである場合、インデックス ソートは、順序付けのすべてのフィールドが最初のテーブルのものである場合にのみ使用できます。

インデックス ソートを使用できない状況をいくつか示します。

1. order by が複数のフィールドに従ってソートされているが、複数のフィールドのソート方向が一貫していない場合、つまり、一部のフィールドが asc (昇順、デフォルトでは昇順) で、一部のフィールドが desc (降順) の場合。のように：

説明: select * from people where last_name='Allen' order by first_name asc, birthday desc;

2. order by にインデックス列にないフィールドが含まれている場合。のように：

説明 select * from people where last_name='Allen' order by first_name, gender;

3. インデックス列の最初の列が範囲検索条件の場合。のように：

select * from people where last_name like 'A%' order by first_name; を説明します。

4. この状況では、SQL ステートメントを次のように最適化できます。

select * from people where last_name like 'A%' order by last_name,first_name; を説明します。

2.8 冗長インデックスと重複インデックス

MySQL では、同じ列に複数のインデックスを作成できます (ただし、インデックスの名前は異なります)。MySQL では重複したインデックスを個別に管理する必要があり、また、クエリを最適化する際にオプティマイザもそれらを 1 つずつ分析して考慮する必要があるため、重複したインデックスはパフォーマンスに影響します。

重複インデックスとは、同じ列に同じ順序で作成された同じタイプのインデックスです。重複したインデックスの作成は避け、発見次第すぐに削除する必要があります。

冗長なインデックスは、重複インデックスとは異なります。インデックスキー（a、b）を作成し、インデックスキー（a）を作成すると、冗長インデックスになります。インデックス（a）は前のインデックスのプレフィックスインデックスにすぎないためです。インデックス（a、b）は、インデックス（a）としても使用できます。ただし、別のインデックス（b、a）を作成すると、冗長インデックスではなくなります。

冗長なインデックスは通常、テーブルに新しいインデックスを追加するときに発生します。たとえば、既存のインデックス（a）を拡張するのではなく、新しいインデックス（a、b）を追加する場合があります。もう1つのケースは、二次インデックス（a）から（a、id）を拡張することです。ここで、IDはinnodbの場合、デフォルトではプライマリキー列が既に既に含まれているため、これも冗長です。

ほとんどの場合、冗長なインデックスは必要ありません。新しいインデックスを作成する代わりに、既存のインデックスを拡張するようにしてください。ただし、既存のインデックスを拡張すると大きくなり、インデックスを使用する他のクエリステートメントのパフォーマンスに影響するため、パフォーマンス上の理由で冗長なインデックスが必要になる場合があります。

インデックスを拡張するときは注意してください。セカンダリインデックスの葉のノードには一次キー値が含まれているため、列（a）のインデックスは（a、id）のインデックスに相当します。誰かがIDでa = 5注文のようなクエリを使用した場合、インデックス（a）は非常に便利です。ただし、インデックス（a）をインデックス（a、b）に変更すると、実際にはインデックス（a、b、id）になります。

PerconaツールボックスからPTアップグレードツールを使用して、計画されたインデックスの変更を再確認することをお勧めします。

したがって、インデックスに関連するすべてのクエリについて明確にしている場合にのみ、既存のインデックスを拡張します。それ以外の場合、新しいインデックスの作成（新しいインデックスで元のインデックスを冗長にする）が最も安全な方法です。

2.9未使用のインデックス

MySQLサーバーには、そのようなインデックスが完全に冗長になることはありません。ただし、一意のインデックスの一意の制約関数は、クエリで一意のインデックスが使用されていないことである可能性があることに注意する必要がありますが、複製データを回避するために使用できます。