MySQL で高性能なインデックスを作成するための完全な手順

1. インデックスの基本

1. インデックスの種類

1.1 Bツリーインデックス

ほとんどの MySQL ストレージ エンジンは、デフォルトで B+ ツリー インデックスを使用します。ストレージ エンジンによって、B+ ツリー インデックスの使用方法は異なります。MyISAM はプレフィックス圧縮テクノロジを使用してインデックスを小さくしますが、InnoDB はメタデータ形式でインデックスを保存します。MyISAM インデックスはデータの物理的な場所によってインデックス行を参照しますが、InnoDB は主キーに基づいてインデックス行を参照します。

B ツリーと B+ ツリー

Bツリー:

B+ツリー:

違い：

• B ツリーのキーワードとレコードは一緒に配置されます。リーフ ノードは外部ノードとみなされ、情報は含まれません。B+ ツリーの非リーフ ノードには、次のノードを指すキーワードとインデックスのみが含まれます。レコードはリーフ ノードにのみ配置されます。
• Bツリーでは、レコードがルートノードに近いほど、レコードの検索が速くなります。キーワードが見つかれば、レコードの存在を判断できます。ただし、B+ツリーでは、各レコードの検索時間は基本的に同じです。ルートノードからリーフノードまで移動する必要があり、リーフノードでキーワードを再度比較する必要があります。この観点から見ると、B ツリーのパフォーマンスは B+ ツリーよりも優れているように見えますが、実際のアプリケーションでは B+ ツリーのパフォーマンスの方が優れています。 B+ ツリーの非リーフ ノードには実際のデータは格納されないため、各ノードは B ツリーよりも多くの要素を収容でき、ツリーの高さは B ツリーよりも低くなります。この利点は、ディスク アクセスの回数が減ることです。 B+ ツリーではレコードを見つけるために B ツリーよりも多くの比較が必要ですが、ディスク アクセスの時間は数百または数千のメモリ比較に相当するため、実際には B+ ツリーのパフォーマンスの方が優れている可能性があります。さらに、B+ ツリーのリーフ ノードはポインターを使用して相互に接続され、順次トラバーサル (たとえば、ディレクトリ内のすべてのファイル、テーブル内のすべてのレコードの表示など) を容易にします。そのため、多くのデータベースとファイル システムで B+ ツリーが使用されています。
  

実際のアプリケーションでは、オペレーティング システムのファイル インデックス作成やデータベース インデックス作成に B ツリーよりも B+ ツリーの方が適しているのはなぜですか?

• B+ツリーはディスクの読み取りと書き込みのコストが低い
  • B+ ツリーの内部ノードには、キーワードの特定の情報へのポインターがありません。したがって、その内部ノードは B ツリーの内部ノードよりも小さくなります。同じ内部ノードのすべてのキーワードが同じディスク ブロックに格納されている場合、ディスク ブロックはより多くのキーワードを収容できます。検索する必要があるキーワードの数が多いほど、一度にメモリに読み込まれます。相対的に言えば、IO 読み取りと書き込みの数は削減されます。
• B+ツリーのクエリ効率はより安定している
  • 非終端点は、最終的にファイルの内容を指すノードではないため、リーフ ノード内のキーワードのインデックスにすぎません。したがって、キーワード検索では、ルート ノードからリーフ ノードへのパスをたどる必要があります。すべてのキーワード クエリのパスの長さは同じであるため、各データ ポイントのクエリ効率は同じになります。
    

赤黒木を使わないのはなぜですか?

• B+ツリーは検索時間が短い
  • バランスのとれた木の検索操作の時間計算量は木の高さ h に関係し、O(h)=O(logdN) となります。ここで、d は各ノードの出力次数です。
  • 赤黒木の出次数は 2 ですが、B+ 木の出次数は一般に非常に大きいため、赤黒木のツリーの高さ h は明らかに B+ 木よりもはるかに大きく、検索回数も多くなります。
• B+ツリーはディスクの事前読み取り機能を使用する
  • ディスク I/O 操作を減らすために、ディスクは厳密にオンデマンドで読み取られるのではなく、毎回事前に読み取られることがよくあります。事前読み取り処理中、ディスクは順次読み取りを実行します。順次読み取りではディスクのシークが不要で、ディスクの回転時間が短いため、速度が非常に速くなります。
  • オペレーティング システムは通常、メモリとディスクを固定サイズのブロックに分割します。各ブロックはページと呼ばれ、メモリとディスクはページ単位でデータを交換します。データベース システムは、ノードを 1 回の I/O で完全にロードできるように、インデックス ノードのサイズをページのサイズに設定します。また、事前読み込み機能も使用でき、隣接ノードも事前に読み込むことができます
    

1.2 ハッシュインデックス

ハッシュ インデックスはハッシュ テーブルに基づいて実装されます。データの各行について、ストレージ エンジンはすべてのインデックス列のハッシュ コードを計算します。ハッシュ コードは O(1) 時間で検索に使用できますが、並べ替えやグループ化には使用できません。完全検索のみがサポートされており、部分検索や範囲検索には使用できません。

MySQL では、メモリ エンジンのみがハッシュ インデックスを明示的にサポートします。

InnoDB ストレージ エンジンには、「アダプティブ ハッシュ インデックス」と呼ばれる特別な機能があります。インデックス値が頻繁に使用される場合、B+Tree インデックスの上にハッシュ インデックスが作成されます。これにより、B+Tree インデックスは、高速ハッシュ検索など、ハッシュ インデックスの利点の一部を得ることができます。

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

MyISAM ストレージ エンジンは空間データ インデックス (R ツリー) をサポートしており、地理データのストレージに使用できます。空間データはすべての次元からデータをインデックス化し、複合クエリに任意の次元を効果的に使用できます。

データを保守するには、GIS 関連の機能を使用する必要があります。

1.4 全文索引

MyISAM ストレージ エンジンは、テキスト内のキーワードを直接比較するのではなく、テキスト内のキーワードを検索するために使用されるフルテキスト インデックスをサポートしています。

検索条件では、通常の WHERE ではなく MATCH AGAINST を使用します。フルテキスト インデックスは、キーワードとそれが配置されているドキュメントのマッピングを記録する逆インデックスを使用して実装されます。

InnoDB ストレージ エンジンも、MySQL バージョン 5.6.4 でフルテキスト インデックスのサポートを開始しました。

2. インデックスの利点と欠点

アドバンテージ

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

欠点

• インデックスによりクエリ速度は大幅に向上しますが、INSERT、UPDATE、DELETE などのテーブル更新の速度も低下します。テーブルを更新すると、MySQL はデータを保存するだけでなく、インデックス ファイルも保存します。インデックス列を持つフィールドを更新するたびに、更新によってキー値が変更された後にインデックス情報が調整されます。
• 実際、インデックスは主キーとインデックス フィールドを格納し、エンティティ テーブルのレコードを指すテーブルでもあるため、インデックス列もスペースを占有します。
  

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

1. 独立した列

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

例えば

mysql> SELECT id, name FROM t_user WHERE id + 1 = 5;

MySQL はこの id + 1 方程式を解析できません。WHERE 条件を簡素化する習慣を身につける必要があります。

2. プレフィックスインデックス

非常に長い文字列にインデックスを付ける必要がある場合があり、その場合、インデックスが大きくなり、速度が遅くなります。

たとえば、BLOB、TEXT、および VARCHAR タイプの列の場合、プレフィックス インデックスを使用して先頭の文字のみをインデックスする必要があります。

プレフィックス長の選択はインデックスの選択性に基づいて決定する必要がある

3. 複数列インデックス

多くの人は、複数列インデックスを完全に理解していません。よくある間違いは、各列に個別のインデックスを作成したり、複数列インデックスを間違った順序で作成したりすることです。

ほとんどの場合、複数の列に独立した単一列インデックスを作成しても、MySQL のクエリ パフォーマンスは向上しません。そのため、「インデックス マージ」戦略が導入され、テーブル上の複数の単一列インデックスを使用して、指定された行をある程度特定できるようになりました。

たとえば、次のステートメントでは、ユーザー名とパスワードを複数列のインデックスとして設定するのが最適です。

t_user からユーザー名、パスワードを選択します。ユーザー名 = 'Aiguodala'、パスワード = 'Aiguodala';

4. 適切なインデックス列の順序

最も選択性の高いインデックス列を先頭に配置します。

インデックスの選択性は、一意のインデックス値とレコードの総数の比率を指します。最大値は 1 です。この場合、各レコードにはそれに対応する一意のインデックスが存在します。選択性が高くなるほど、各レコードの区別が容易になり、クエリの効率が高くなります。

5. クラスター化インデックス

クラスター化インデックスは、個別のインデックス タイプではなく、データを格納する方法です。「クラスター化」という用語は、データ行と隣接するキー値がコンパクトにまとめて格納されることを意味します。

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

集約データの利点と欠点

アドバンテージ：

• 関連データをまとめて保存できる
  • たとえば、電子メール メールボックスを実装する場合、データはユーザー ID に従ってクラスター化されるため、特定のユーザーのすべての電子メールを取得するには、ディスクから読み取る必要があるデータの量はわずかです。クラスター化インデックスがない場合、各電子メールを取得するとディスク IO が発生します。
• データ アクセスが高速化します。クラスター化インデックスはインデックスとデータを同じ B+ ツリーに格納するため、データの検索が容易になります。
• カバーリングインデックススキャンを使用するクエリでは、ページノード内の主キー値を直接使用できます。
  

欠点:

• データをクラスタ化すると、IO 集約型アプリケーションのパフォーマンスが最大化されますが、すべてのデータがメモリ内に配置されている場合、アクセスの順序は重要ではなく、クラスタ化インデックスの利点はありません。
• 挿入速度は挿入順序に大きく依存します。データが主キーの順序でロードされていない場合は、ロード後に OPTIMIZE TABLE コマンドを使用してテーブルを再編成するのが最適です。したがって、自動増分主キーを選択することをお勧めします。
• クラスター化インデックス列を更新すると、InnoDB は更新された各行を新しい場所に移動する必要があるため、コストがかかります。
• クラスター化インデックスに基づくテーブルでは、新しい行が挿入されたとき、または主キーが更新されて行を移動する必要がある場合に、「ページ分割」の問題が発生する可能性があります。行の主キー値により、行を 1 ページ全体に挿入する必要がある場合、ストレージ エンジンは行を収容するためにページを 2 つのページに分割します。これが分割操作です。ページ分割により、テーブルがより多くのディスク領域を占有することになります。
• クラスター化インデックスは、特に行がまばらな場合や、ページ分割によりデータが連続して保存されていない場合に、テーブル全体のスキャンの速度を低下させる可能性があります。
  

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

データはインデックスで区切られた構造で保存されます。インデックス構造のリーフノードは、データの対応する行を指します。MyISAM は、key_buffer を介してインデックスをメモリにキャッシュします。データにアクセスする必要がある場合 (インデックスを介してアクセスする場合)、メモリ内でインデックスを直接検索し、次にインデックスを介してディスク上の対応するデータを見つけます。これが、インデックスがキーバッファにヒットしない場合に速度が遅くなる理由です。

6. カバーインデックス

インデックスは、クエリする必要があるすべてのフィールドの値をカバーします

利点：

• インデックス エントリはデータ行よりもはるかに小さいため、データ アクセスの量を大幅に削減し、すべてのエントリをメモリに格納しやすくなります。
• インデックスは列の値の順序で保存されるため、IO 集約型の範囲クエリでは、ディスクから各データ行をランダムに読み取る場合よりも IO が大幅に少なくなります。
• 一部のストレージ エンジン (MyISAM など) は、メモリ内のインデックスのみをキャッシュし、データのキャッシュはオペレーティング システムに依存します。したがって、システム コール (通常は時間がかかります) を使用せずに、インデックスのみにアクセスできます。
• InnoDB のセカンダリ インデックス (非クラスター化インデックス) は、行のプライマリ キー値をリーフ ノードに格納します。セカンダリ プライマリ キーがクエリをカバーできる場合、プライマリ キー インデックスのセカンダリ クエリを回避できます。

3. クエリパフォーマンスの最適化

1. パフォーマンス分析を説明する

EXPLAIN キーワードを使用して、オプティマイザーをシミュレートし、SQL クエリ ステートメントを実行して、MySQL が SQL ステートメントをどのように処理するかを確認します。クエリステートメントまたはテーブル構造のパフォーマンスボトルネックを分析する

例：

1.1 id: 表の読み取り順序

id は選択クエリのシーケンス番号であり、クエリ内で選択句または操作テーブルが実行される順序を示す一連の数字が含まれます。

IDは同じです: 実行順序は上から下です

EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;

異なるID: 実行順序はIDが大きい方が先に実行されます

EXPLAIN SELECT t2.id FROM t2 WHERE t2.id = 
(t1.idをt1から選択する。t1.id = 
(t3からt3.idを選択)
);

1.2 select_type: クエリ操作タイプ

select_typeはクエリの種類を表し、主に共通クエリ、結合クエリ、サブクエリ、その他の複雑なクエリを区別するために使用されます。

1.3 表: 表のソース

表は、このデータがどの表に基づいているかを示します

1.4 タイプ: アクセスタイプ

type はクエリのアクセス タイプです。これはより重要な指標です。結果は最良から最悪まで次のとおりです。

システム > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

--一般的な順序は、system > const > eq_ref > ref > range > index > all です。

一般的に言えば、クエリが少なくとも範囲レベル、できれば参照レベルに到達するようにする必要があります。

1.5 possible_key: 可能なインデックス

このテーブルに適用される可能性のある 1 つ以上のインデックスを表示します。クエリに関係するフィールドにインデックスがある場合、そのインデックスはリストされますが、クエリによって実際に使用されない可能性があります。

1.6 キー: 実際に使用されるインデックス

実際に使用されるインデックス。 NULL の場合、インデックスは使用されません。

1.7 key_len: インデックスで使用されるバイト数

インデックスで使用されるバイト数を示します。この列は、クエリで使用されるインデックスの長さを計算するために使用できます。 key_len フィールドは、インデックスが完全に使用されているかどうかを確認するのに役立ちます。

ken_len が長いほど、インデックスがより完全に使用されます。

1.8 ref: 使用されているインデックスの詳細情報を表示します

ref は、インデックスのどの列が使用されているかを示し、可能な場合は定数にすることができます。インデックス列の値を検索するために使用される列または定数

1.9 行: クエリされた行数

行列には、クエリを実行するために MySQL が調べる必要があると想定している行数が表示されます。少ないほど良いです!

1.10 追加: 追加の重要な情報

その他の重要な追加情報

• filesort の使用: 外部インデックス ソートを使用する (ユーザー作成インデックスは使用されません)
  • これは、MySQL がテーブル内のインデックスの順序でデータを読み取るのではなく、外部インデックスを使用してデータをソートすることを意味します。 MySQL ではインデックスを使用して完了できないソート操作は「ファイルソート」と呼ばれます。
  • 「Using filesort」メッセージは、SQL ステートメントが適切に設計されておらず、作成されたインデックスに従って、またはインデックスによって指定された順序でソートされていないことを示します。
• 一時的な使用
  • 一時テーブルは中間結果を保存するために使用されます。MySQL はクエリ結果をソートするときに一時テーブルを使用します。並べ替え順序やグループ化クエリのグループ化でよく使用されます
  • 「一時の使用」​​が発生する場合、複合インデックスが順番に使用されていないことが原因で、SQL ステートメントの設計が不適切である可能性があります。
• インデックスの使用
  • インデックスを使用するということは、対応する選択操作でカバーリング インデックスが使用されることを意味し、これによりテーブルのデータ行へのアクセスが回避され、効率が向上します。
  • 同時に where が使用されている場合は、インデックスを使用してインデックス キー値の検索を実行することを意味します。
  • where を使用しない場合、インデックスは検索を実行するのではなく、データの読み取りにのみ使用されます。
• where の使用
  • フィルタリングが使用されている場所を示します
• 結合バッファの使用
  • 接続キャッシュが使用される
• 不可能な場所
  • where 句の値は常に false であり、タプルを取得するために使用することはできません。
• 最適化されたテーブルを選択する
  • GROUP BY 句がない場合、インデックスに基づく MIN/MAX 操作や MyISAM ストレージ エンジンの COUNT(*) 操作の最適化は、計算を実行するために実行フェーズまで待つ必要はありません。最適化は、クエリ実行プラン生成フェーズ中に完了します。

要約する

これで、MySQL での高性能インデックスの作成に関するこの記事は終了です。MySQL の高性能インデックスに関する関連コンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。