初心者向けMySQLインデックス

序文

MySQL インデックスで最も重要なデータ構造は B+ ツリーなので、まずは B+ ツリーの原理について簡単に説明しましょう。

B+ ツリー原理

1. データ構造

B ツリーはバランス ツリー、つまりバランスの取れたツリーを指します。バランスツリーは、すべてのリーフノードが同じレベルに配置されている検索ツリーです。

B+ ツリーは、B ツリーとリーフ ノードのシーケンシャル アクセス ポインタに基づいて実装されています。B ツリーのバランスを備え、シーケンシャル アクセス ポインタを通じて区間クエリのパフォーマンスが向上します。

B+ ツリーでは、ノード内のキーは左から右へ非減少的に配置されます。ポインターの左と右の隣接キーが keyi と keyi+1 であり、それらが null でない場合、ポインターが指すノードのすべてのキーは keyi 以上で、keyi+1 以下になります。

2. 操作

検索操作を実行するときは、まずルート ノードに対してバイナリ検索を実行してキーへのポインターを見つけ、次にポインターが指すノードを再帰的に検索します。リーフ ノードが見つかるまで、リーフ ノードに対してバイナリ検索が実行され、キーに対応するデータが検索されます。

挿入および削除操作は、バランスの取れたツリーのバランスを崩します。したがって、挿入および削除操作の後は、バランスを維持するためにツリーを分割、結合、回転などする必要があります。

3. 赤黒木との比較

赤黒木などのバランス木もインデックスの実装に使用できますが、ファイル システムとデータベース システムでは、主に次の 2 つの理由から、インデックス構造として B+ 木が一般的に使用されます。

1. 検索回数が減る

バランスのとれた木の検索操作の時間計算量は木の高さ h に等しく、おおよそ O(h)=O(logdN) です。ここで、d は各ノードの出力次数です。

赤黒木の出次数は 2 ですが、B+ 木の出次数は一般に非常に大きいため、赤黒木の木の高さ h は明らかに B+ 木よりもはるかに大きく、検索回数も多くなります。

(II) ディスクの先読み機能の使用

ディスク I/O を削減するために、ディスクは厳密にオンデマンドで読み取られるのではなく、毎回事前に読み取られることがよくあります。事前読み取り処理中、ディスクは順次読み取りを実行します。順次読み取りではディスクのシークが不要で、回転時間が短いため、速度が非常に速くなります。

オペレーティング システムは通常、メモリとディスクをページと呼ばれる一定のサイズのブロックに分割し、メモリとディスクはページ単位でデータを交換します。データベース システムは、ノードを 1 回の I/O で完全にロードできるように、インデックス ノードのサイズをページのサイズに設定します。また、事前読み取り機能を利用することで、隣接するノードも事前にロードすることができます。

MySQL インデックス

インデックスはサーバー レベルではなくストレージ エンジン レベルで実装されるため、ストレージ エンジンごとにインデックスの種類と実装が異なります。

1. B+ツリーインデックス

ほとんどの MySQL ストレージ エンジンのデフォルトのインデックス タイプです。

テーブル全体をスキャンする必要がなく、ツリーのみを検索すればよいため、検索速度が大幅に速くなります。

検索だけでなく、並べ替えやグループ化にも使用できます。

複数の列をインデックス列として指定することができ、複数のインデックス列が一緒にキーを形成します。

完全なキー値、キー値の範囲、およびキー プレフィックス検索に適用できます。このうち、キー プレフィックス検索は左端のプレフィックス検索にのみ適用されます。検索がインデックス列の順序どおりでない場合、インデックスは使用できません。

InnoDB の B+Tree インデックスは、プライマリ インデックスと補助インデックスに分かれています。プライマリ インデックスのリーフ ノードのデータ フィールドには、完全なデータ レコードが記録されます。このインデックス作成方法は、クラスター化インデックスと呼ばれます。データ行を 2 つの異なる場所に保存することは不可能であるため、テーブルにはクラスター化インデックスを 1 つだけ含めることができます。

補助インデックスのリーフ ノードのデータ フィールドには、主キーの値が記録されます。そのため、補助インデックスを使用して検索する場合は、まず主キーの値を見つけてから、主インデックスで検索する必要があります。

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

ハッシュインデックスは O(1) 時間で検索できますが、秩序性が失われます。つまり、ソートやグループ化には使用できません。また、完全検索のみをサポートしており、部分検索や範囲検索には使用できません。 InnoDB ストレージ エンジンには、「アダプティブ ハッシュ インデックス」と呼ばれる特別な機能があります。インデックス値が頻繁に使用される場合、B+Tree インデックスの上にハッシュ インデックスが作成されます。これにより、B+Tree インデックスは、高速ハッシュ検索など、ハッシュ インデックスの利点の一部を得ることができます。

3. 全文インデックス

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

検索条件では、通常の WHERE ではなく MATCH AGAINST を使用します。

フルテキスト インデックスは、キーワードとそれが配置されているドキュメントのマッピングを記録する逆インデックスを使用して実装されます。

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

4. 空間データインデックス

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

インデックスの最適化

1. 独立した列

クエリを実行する場合、インデックス列を式または関数パラメータの一部にすることはできません。そうしないと、インデックスを使用できません。たとえば、次のクエリでは actor_id 列のインデックスを使用できません。

sakila.actor から actor_id を選択します。ここで actor_id + 1 = 5;

2. 複数列インデックス

クエリの条件として複数の列を使用する必要がある場合、複数の単一列インデックスを使用するよりも、複数列インデックスを使用するとパフォーマンスが向上します。たとえば、次のステートメントでは、actor_id と film_id を複数列のインデックスとして設定するのが最適です。

SELECT film_id, actor_ id FROM sakila.film_actor WHERE actor_id = 1 AND film_id = 1;

3. インデックス列の順序

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

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

たとえば、以下に示す結果では、customer_id は staff_id よりも選択性が高く、customer_id 列を複数列インデックスの先頭に配置するのが最適です。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity、
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity、
カウント（*）
支払いから;

スタッフID選択度: 0.0001
顧客ID選択度: 0.0373
 カウント(*): 16049

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

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

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

5. カバーインデックス

インデックスには、クエリする必要があるすべてのフィールドの値が含まれています。

次のような利点があります。

• 通常、インデックスはデータ行のサイズよりもはるかに小さく、インデックスのみを読み取ることでデータ アクセスの量を大幅に削減できます。
• 一部のストレージ エンジン (MyISAM など) は、メモリ内のインデックスのみをキャッシュし、データのキャッシュはオペレーティング システムに依存します。したがって、システム コール (通常は時間がかかります) を使用せずに、インデックスのみにアクセスできます。
• InnoDB エンジンの場合、セカンダリ インデックスでクエリをカバーできる場合は、プライマリ インデックスにアクセスする必要はありません。

6. 左端接頭辞原則

名前が示すように、左端が最初であり、左端から始まる連続するインデックスを一致させることができます。

ジョイントインデックスの本質:

(a,b,c) 結合インデックスを作成すると、(a) 単一列インデックスを作成するのと同じになります。(a,b) 結合インデックスと (a,b,c) 結合インデックスを有効にするには、a と a,b および a,b,c の 3 つの組み合わせのみを使用できます。

インデックスの利点

• これにより、サーバーがスキャンする必要があるデータ行の数が大幅に削減されます。
• サーバーがソートやグループ化を回避し、一時テーブルの作成を回避するのに役立ちます (B+Tree インデックスは順序付けされており、ORDER BY および GROUP BY 操作に使用できます。一時テーブルは主にソートおよびグループ化中に作成されます。ソートおよびグループ化は必要ないため、一時テーブルを作成する必要はありません)。
• ランダム I/O をシーケンシャル I/O に変換します (B+ ツリー インデックスは順序付けられ、隣接するデータを一緒に格納します)。

インデックスの使用条件

• 非常に小さいテーブルの場合、ほとんどの場合、単純なフルテーブルスキャンの方がインデックス作成よりも効率的です。
• 中規模から大規模のテーブルの場合、インデックスは非常に効果的です。
• ただし、テーブルが非常に大きい場合は、インデックスの作成と維持にかかるコストもそれに応じて増加します。この場合、一度に 1 つのレコードを照合するのではなく、クエリする必要があるデータのセットを直接区別するテクノロジが必要です。たとえば、パーティショニング テクノロジを使用できます。

まとめ

インデックスはMySQLにおいて非常に重要な機能です。日常の開発においてインデックスをうまく活用できれば、SQL文の実行パフォーマンスを大幅に向上させることができるため、その原理を理解することは非常に重要です。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。