データベースのインデックス作成に関する知識ポイントのまとめ。必要な情報はすべてここにあります。

データベースインデックスについては皆さんもよくご存知だと思います。

インデックスは、データベーステーブル内の 1 つ以上の列の値を並べ替える構造です。インデックスを使用すると、データベーステーブル内の特定の情報にすばやくアクセスできます。クエリを支援するツールとして、適切に設計されたインデックスは、データベースへのクエリ負荷を大幅に軽減できます。ご存知のとおり、データベースはプロジェクトの中核であり、最も弱い部分です。負荷が大きすぎると、障害が発生しやすくなり、予期しない影響が生じやすくなります。したがって、日常の開発であっても、インタビューであっても、インデックスの知識体系を習得する必要があります。

もちろん、習得する必要がありますが、インデックス作成に関する知識ポイントは多く、多くの初心者がそれらを見逃しがちです。そのため、この知識ポイントの要約を書きたいと思います。これは読者との共有であるだけでなく、私自身の包括的なレビューでもあります。お役に立てば幸いです。

さて、これ以上前置きせずに、本題に入りましょう。

まず、この記事で紹介されている知識ポイントはすべてMySQLデータベースに基づいていることを述べておきます。

インデックスの長所と短所

アドバンテージ：

1. データクエリを大幅に高速化

2. ユニークインデックスはデータベーステーブル内の各行の一意性を保証することができる

3. 加速テーブル接続時間

欠点:

1. インデックスの作成と維持には時間がかかるため、インデックスの数は多すぎないようにする必要があります。

2. インデックスはディスク領域を占有するデータ構造です。

3. テーブルを更新する際には、インデックスも動的にメンテナンスする必要があり、メンテナンス速度が低下する

インデックスの種類

インデックスの目的はクエリの効率を向上させることですが、インデックスを実装する方法は多数あるため、ここではインデックスモデルの概念を紹介します。ここでは、インデックス作成によく使用される 3 つのデータ構造、つまりハッシュテーブル、順序付き配列、検索ツリーを紹介します。

ハッシュインデックス

ハッシュテーブル (ハッシュテーブルとも呼ばれる) は、ハッシュ関数を使用してキーコードを値が格納されている場所にマップするように設計されています。読み取り時に、キーコードを使用して場所を見つけ、直接保存します。このデータ構造の平均検索の複雑さは O(1) です。

たとえば、ID カード情報とユーザー名のテーブルを保持し、ID カード番号に基づいて名前を照会する必要がある場合、ハッシュインデックスは次のようになります。

このインデックス構造の利点は、単一の要素をランダムに追加または削除するのに効率的であることです。欠点は、ハッシュテーブル内の要素が必ずしも順番に並べられているわけではないため、間隔クエリを実行する場合は非常に遅くなることです。

図の[ID_card_n1、ID_card_n3]の範囲内のID番号を持つすべてのユーザーを見つけたい場合、すべてのユーザーをスキャンする必要があります。

したがって、ハッシュテーブル構造は、等しい値のクエリのみが必要なシナリオに適しています。

順序付き配列インデックス

順序付き配列インデックスは、等値クエリと間隔クエリの両方のシナリオで非常に効率的です。上の図を例に挙げてみましょう。順序付き配列を使用して実装すると、次のようになります。

配列の要素はID番号に従って順番に並べられています。データを照会する必要がある場合、バイナリ検索方式を使用してデータをすばやく取得でき、時間計算量はO(logN)です。また、順番に並べられているため、一定の範囲内のデータの照会も非常に高速です。

もちろん、順序付き配列の欠点も明らかです。ArrayList と同様に、検索は高速ですが、要素を追加および削除すると、後続のすべての要素を移動する必要がある場合があり、これは配列の自然な欠陥です。したがって、順序付けされた配列インデックスは静的ストレージエンジンにのみ適しています。たとえば、2017 年の都市の人口情報をすべて保存する場合、このタイプのデータは再度変更されません。

検索ツリーインデックス

探索木といえば、私たちが最もよく知っているのは二分探索木でしょう。二分探索木の特徴は、各ノードの左の子が親ノードより小さく、親ノードが右の子より小さく、左と右のサブツリーも二分探索木であることです。平均時間計算量は O(log2(n)) です。

リンクリストの挿入・削除操作が高速である特徴と、配列の検索が高速である利点を持ちます。同時に、二分探索木自体が順序付けされているため、範囲検索もサポートします。

実際、バイナリ検索ツリーはインデックス作成に適した選択肢のように思えますが、そうではありません。

まず、このツリーがディスク上に存在することを明確にする必要があります。毎回、ディスクから対応するノードを読み取る必要があります。ただし、バイナリ検索ツリーのノードはファイル内にランダムに格納されるため、ノードの読み取りにはディスク IO が必要になる場合があります。バイナリ検索ツリーは比較的高さがあります。たとえば、100 万の要素を持つバランスの取れたバイナリツリーの高さは 10 層以上です。つまり、ほとんどの場合、データを 1 回取得するには 10 回以上のディスク IO が必要です。このコストは高すぎるため、バイナリ検索ツリーは一般にインデックスとして使用されません。

クエリがディスクをできるだけ少なく読み取るようにするには、クエリプロセスがアクセスするデータブロックをできるだけ少なくする必要があります。つまり、ツリーの高さをできるだけ低くし、多方向検索ツリーを使用する必要があります。InnoDB ストレージエンジンは、この多方向検索ツリー (B+ ツリーと呼ばれることが多い) を使用します。

InnoDB インデックス構造

InnoDB は、MySQL で最も一般的に使用されている検索エンジンです。その基礎となるインデックス構造は B+ ツリーを使用し、すべてのデータは B+ ツリーに格納されます。各インデックスは InnoDB の B+ ツリーに対応します。

B+ツリーの特徴は次のとおりです。

すべてのリーフノードには、すべての要素に関する情報と、これらの要素を含むレコードへのポインターが含まれており、リーフノード自体はキーワードのサイズに応じて昇順にリンクされています。
すべての中間ノード要素は同時に子ノードに存在し、子ノード要素の中で最大 (または最小) の要素です。

この構造には 2 つの利点があります。

1 つのノードに、より多くの要素を格納できます。リーフノードを除き、他のノードにはキーのみが含まれ、値は格納されません。このようにして、ツリーの高さを効果的に削減し、クエリ IO 回数を減らすことができます。
同時に、リーフノードには次のリーフノードへのポインターが含まれているため、範囲クエリ中に最初のリーフノードが見つかった場合、ルートノードからトラバースすることなく、ポインターに基づいて後続のデータをクエリできます。このため、多くの専門家は、範囲クエリの効率が向上するため、テーブルの主キーを自己増加するように設計することを推奨しています。

インデックス分類

データベースインデックスは、構造に応じて、クラスター化インデックスと非クラスター化インデックスに分けられます。

クラスター化インデックスは、各テーブルの主キーに従って B+ ツリーを構築します。同時に、リーフノードにはテーブル全体の行レコードデータが格納されます。簡単に言えば、主キーインデックスと呼ばれるものです。クラスター化インデックスの上に作成されるインデックスはセカンダリインデックスと呼ばれ、セカンダリインデックスを使用してデータにアクセスするには、常に 2 回目の検索が必要になります。

非クラスター化インデックス、非クラスター化インデックス、セカンダリインデックスとも呼ばれます。このタイプのインデックスはデータとインデックスを別々に格納し、インデックス構造のリーフノードはデータの対応する場所を指します。

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

InnoDB はクラスター化インデックスを使用して主キーを B+ ツリーに編成し、行データはリーフノードに格納されます。ID、名前、会社のフィールドを含むユーザーテーブルを想定します。

図は、InnoDB のインデックス構造を次のように示しています。

図からわかるように、「where id = 14」という条件を使用して主キーを検索すると、B+ツリー検索アルゴリズムに従って対応するリーフノードを見つけて、行データを取得できます。

Name 列に対して条件付き検索を実行する場合は、2 つの手順が必要です。最初の手順では、補助インデックス B+ ツリーで Name を取得し、そのリーフノードに到達して対応する主キーを取得します。 2 番目のステップでは、主キーを使用して主インデックス B+ ツリーで別の B+ ツリー検索操作を実行し、最終的にリーフノードに到達してデータの行全体を取得します。（重要な点は、セカンダリインデックスは他のキーを通じて作成する必要があることです）

これはクラスター化インデックスの構造であり、非クラスター化インデックスの代表格はMySQLの一般的な検索エンジンでもあるMyISMです。

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

非クラスター化インデックスの 2 つの B+ ツリーは見た目に違いはありません。ノード構造はまったく同じですが、格納されている内容が異なります。主キーインデックス B+ ツリーのノードには主キーが格納され、セカンダリキーインデックス B+ ツリーのノードにはセカンダリキーが格納されます。インデックス自体にはデータは保存されません。データは独立した場所に保存されます。これら 2 つの B+ ツリーのリーフノードは、アドレスを使用して実際のテーブルデータを指します。

非クラスター化インデックスは、B+ツリーを2回チェックする必要がないため、クラスター化インデックスよりも効率が高いようです。では、最も一般的に使用されているInnoDBエンジンがなぜこのストレージ構造をまだ使用しているのでしょうか?その利点は何ですか?

1. クラスター化インデックスでは、行データとリーフノードが一緒に格納されるため、同じページに複数の行データが存在することになります。同じデータページの異なる行レコードにアクセスする場合、ページはバッファにロードされています。再度アクセスする場合は、ディスクにアクセスすることなく、メモリ内でアクセスが完了します。このように、主キーと行データが一緒にメモリにロードされ、リーフノードが見つかった後すぐに行データを返すことができます。そのため、主キーIDに従ってデータが整理されている場合は、より速くデータを取得できます。

2. 補助インデックスのポインタとしてアドレス値を使用する代わりに、主キーを「ポインタ」として使用する利点は、行を移動したりデータページを分割したりするときに補助インデックスのメンテナンス作業が軽減されることです。主キー値をポインタとして使用すると、補助インデックスがより多くのスペースを占めることになりますが、利点は、行を移動するときに InnoDB が補助インデックスの「ポインタ」を更新する必要がないことです。 **つまり、行の位置 (実装では 16K ページによって配置) は、データベース内のデータが変更されると (以前の B+ ツリーノードの分割とページの分割)、変更されます。クラスター化インデックスを使用すると、主キー B+ ツリーのノードがどのように変更されても、補助インデックスツリーは影響を受けません。

3. クラスター化インデックスは並べ替えや範囲クエリに適していますが、非クラスター化インデックスは適していません。

カバーインデックス

補助インデックスといえば、カバーリングインデックスという別の特殊なインデックスを拡張することもできます。

前述のように、クラスター化インデックスのデータにアクセスするには、セカンダリ検索が必要です。つまり、最初にセカンダリキーのリーフノードを見つけ、プライマリキーに対応するノードを取得し、次にプライマリキーインデックスを使用してデータをクエリします。これは比較的低速です。実際、必要なフィールドが最初の検索で取得できる場合は、プライマリキーを2回目に検索する必要はありません。つまり、「テーブルに戻る」必要はありません。

たとえば、上のテーブルには、id、name、company の 3 つのフィールドがあります。name にインデックスを追加しました。データをクエリするときは、次のステートメントを記述します。

名前が '张%' のようなユーザーから名前を選択します。

私たちのステートメントはインデックス化されており、返されるフィールドはリーフノードに存在するため、クエリ時にテーブルは返されません。素晴らしいですね~~

したがって、必須フィールドがインデックス列である場合は、 select *などのステートメントを使用する代わりに、このクエリ方法を使用してみてください。

インデックスタイプ

上記のインデックスの分類は構造に基づいていますが、範囲別に分類すると、インデックスは次のカテゴリに分類することもできます。

通常のインデックス: これは最も基本的なインデックスタイプであり、一意性などの制限はありません。

TABLE_NAME(PROPERTY_NAME) に INDEX_NAME を作成します。

ユニークインデックス: 基本的には通常のインデックスと同じですが、一意性を維持するために、すべてのインデックス列は 1 回しか出現できません。

TABLE_NAME(PROPERTY_NAME) に一意のインデックス INDEX_NAME を作成します。

主キー: 一意のインデックスと同様に、重複する列は存在できませんが、本質的には主キーはインデックスではなく制約であり、「PRIMARY KEY」として指定する必要があります。ユニークインデックスとは次の点で異なります。

主キーが作成された後、その主キーには一意のインデックスが含まれている必要がありますが、一意のインデックスは必ずしも主キーであるとは限りません。
一意のインデックス列では null 値が許可されますが、主キー列では null 値は許可されません。
主キー列が作成されると、デフォルトで null 値 + 一意のインデックスに設定されます。
主キーは外部キーとして他のテーブルから参照できますが、一意のインデックスは参照できません。
テーブルには主キーを 1 つしか持てませんが、一意のインデックスは複数持つことができます。
主キーは、自動増分列、ID 番号など、簡単に変更できない一意の識別子に適しています。

フルテキストインデックス: フルテキストインデックスのインデックスタイプは FULLTEXT であり、VARCHAR または TEXT タイプの列に作成できます。 MySQL 5.6 より前のバージョンでは、MyISAM ストレージエンジンのみがフルテキストインデックスをサポートしています。バージョン 5.6 以降では、MyISAM と InnoDB の両方のストレージエンジンがフルテキストインデックスをサポートしています。

TABLE_NAME(PROPERTY_NAME) に FULLTEXT インデックス INDEX_NAME を作成します。

結合インデックス: 結合インデックスはインデックスの分類の一種ではなく、複数のフィールドを含む共通のインデックスです。たとえば、index(a, b) という結合インデックスがある場合、検索時にa and b条件として使用できます。

左端一致原則

結合インデックスでは、左端のインデックスが優先され、左端のインデックスから始まる連続するインデックスはどれでも一致できます。同時に、範囲クエリ (>、<、between、like) に遭遇すると、マッチングは停止します。

前述のように、クエリ条件として index(a, b) または a のみを使用した場合はインデックスが使用されますが、クエリ条件として b のみを使用した場合はインデックスは使用されません。

または、(a、b、c、d) の順序でインデックスを作成し、a = 1 かつ b = 2 かつ c > 3 かつ d = 4 を使用して検索すると、c フィールドは範囲クエリであるため d のインデックスは使用されず、それ以降のフィールドは一致しなくなります。

インデックスはいつ無効になりますか?

1. インデックス列で関数や式を使用する。

num + 1 = 5 となるテストから * を選択

MySQL ではこのような方程式を解くことはできません。これは完全にユーザーの行動です。インデックスが有効になるように、インデックス列は独立した列として扱う必要があります。

2. NULL値の条件がある

user_id が null でないユーザーから * を選択します。

データベーステーブルを設計するときは、NULL 値を避けるように最善を尽くしてください。データが空の場合は、数値型の場合は 0 または -1、文字型の場合は空の文字列などのデフォルト値を指定できます。

3. 条件として or 式を使用します。1 つの列にインデックスがない場合、他の列のインデックスは機能しません。

select * from user where user_id = 700 or user_name = "老薛";

この場合、user_id はインデックス化されているが user_name はインデックス化されていない場合、実行時に user_id のインデックスは無効になります。このため、両方のフィールドにインデックスが付けられていない限り、開発中はまたはをできるだけ使用しないでください。

4. 列間の比較。テーブルでは、2 つの列 (id と c_id) に別々のインデックスがあります。次のクエリ条件では、インデックスは使用されません。

id = c_id の場合、テストから * を選択します。

5. データ型の変換。列の型が文字列の場合、条件内でデータを引用符で囲む必要があります。そうしないと、インデックスは使用されません。

インデックス `idx_user_name` を user(user_name) に作成します。
user_name = 123 の場合、ユーザーから * を選択します。

上記の例では、user_name に対してインデックスが作成されていますが、クエリ時に条件が文字列として扱われないため、インデックスは使用されません。

6. NOT条件

クエリ条件が満たされていない場合、インデックスの配置が難しくなり、実行プランはフルテーブルスキャンに傾く可能性があります。このようなクエリ条件には、<>、NOT、in、not exists などがあります。

user_id<>500 のユーザーから * を選択します。
user_idが(1,2,3,4,5)であるユーザーから*を選択します。
user_id が (6,7,8,9,0) に含まれないユーザーから * を選択します。
user_id が存在する user から * を選択します (user_record.user_id = user.user_id である user_record から 1 を選択します)。

7. クエリは%で始まる

あいまい検索を使用する場合は、姓 Zhang の後にワイルドカードを使用するようにしてください。たとえば、姓 Zhang の人を検索する場合は、 user_name like '張%'使用できます。このように、インデックスを検索するときに、インデックス列を先頭から一致させることができます。ただし、 user_name like '%張'の場合、テーブル全体がスキャンされます。

8. 複数列インデックスは、上で述べた左端一致の原則に従います。

インデックスを使用する場合

前述のように、インデックスはクエリ速度を高速化できますが、スペースも消費します。したがって、作成するインデックスの数が多いほど効果的です。インデックスを効果的に適用するには、最も有用なクエリフィールドにインデックスを予約する必要があります。一般的に、インデックスは次のフィールドに作成する必要があります。

言うまでもなく、主キーフィールドです。
where 条件で頻繁に使用されるフィールドなど、頻繁に検索される列。
他のテーブルの外部キーフィールドをテーブル結合の条件フィールドとして使用して、結合されたテーブルのクエリを効果的に高速化できます。
クエリでの並べ替え、統計、またはグループ化に使用されるフィールド。

同様に、インデックスを作成すべきでない列もあります。これらの列には以下が含まれます。

頻繁に更新されるフィールドは、更新のたびにレコードが更新されるだけでなく、インデックスも更新され、インデックスファイルが保存されるため、インデックスの作成には適していません。
where 条件で使用されていないフィールドにはインデックスは作成されません。
テーブル内のレコードが少なすぎるため、インデックスを作成する必要はありません。
テキストまたは画像タイプとして定義された列にはインデックスを追加しないでください。これは、これらの列のデータ量が非常に多いか、値が非常に少ないため、インデックスの使用に適さないためです。
データが繰り返され、均等に分散されるフィールド。そのため、頻繁にクエリされ、頻繁にソートされるフィールドに対してインデックスが作成されます。一部のデータには重複データが多数含まれているため、このフィールドをインデックス化してもあまり効果的ではないことに注意してください。たとえば、性別フィールドには男性と女性のデータしかないため、インデックス化には適していません。

キーワードを説明する

explain は MySQL キーワードであり、これを使用して検索ステートメントのパフォーマンスを表示できます。

これはクエリテーブルの数で、合計3000万行以上あります。これだけの量のデータが検索されると、インデックスを使用する必要があります。インデックスが有効かどうかについては、このキーワードを使用して確認することもできます。

ご覧のとおり、検索結果の数は瞬時に 16 に減少し、使用されたインデックスはindex_user_idであり、インデックスが効果的であることが証明されました。

説明のいくつかの重要なパラメータを理解する必要があります。

id: クエリのシリアル番号

select_type: クエリのタイプ。主に通常のクエリと、ユニオンクエリやサブクエリなどの複雑なクエリを区別します。

タイプ：

タイプはアクセスタイプを示し、より重要な指標です。結果の値は良いものから悪いものの順です。

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

システムは最も効率的で、ALL は完全なテーブルスキャンです。一般的に、クエリは少なくとも範囲レベルに到達する必要があります。

鍵：

MySQL が実際に使用することを決定したキーを表示します。インデックスが選択されていない場合、キーは NULL になります。

key=primary の場合、主キーが使用されることを意味します。

key=null はインデックスが使用されないことを意味します。
可能なキー:

MySQL がこのテーブル内の行を検索するために使用できるインデックスを示します。空の場合、関連付けられたインデックスはありません。このとき、ステートメント内にインデックスが失敗する原因となるものがあるかどうかを確認します。

行:

実行プランでスキャンされる行の推定数を示します。これは推定値です。

余分な：

「Only index」の場合は、インデックスツリー内の情報のみを使用して情報が取得されることを意味します。これは、テーブル全体をスキャンするよりも高速です。

where が使用されている場合は、where 制限が使用されます。

where が不可能な場合は、where の必要がないことを意味し、通常は何も見つからなかったことを意味します。

インデックスを使用しているように見えるということは、インデックスが有効であることを意味します。

要約する

さて、インデックスに関する豆知識は以上です。最後にインデックスの注意点をまとめておきます。

1. インデックスは、テーブルデータの使用状況に基づいて作成する必要があります。インデックスを作成しすぎないでください。通常、テーブルに 6 つ以上のインデックスフィールドを含めることは推奨されません。

2. 最も必要とされる場所では、優れたナイフを使用する必要があります。クエリによく使用されます。重複するデータはあまりありません。検索行の数がテーブルデータ量の 4% を超えないフィールドでは、インデックスの方が適しています。

3. 共同インデックスを作成するときは、左端の一致原則に注意してください。左端のフィールドは必須フィールドであることを忘れないでください。この点で私は大きな損失を被りました。

4. Explain 実行プランを使用して、クエリステートメントのパフォーマンスを確認します。

参照:

https://www.jianshu.com/p/fa8192853184

MySQL 実践 45 講義

やっと

どれも基本的な知識ですが、まとめるのに1日かかりました。5,000語以上あるので、しっかりした記事と言えます。読者の皆さんが何か得したと感じたら、転送やいいねをお願いします。4つのいいねは求めていませんが、2つまたは1つのいいねで満足します。あなたの小さな努力が私の継続的な創作のモチベーションです！

これで、データベースインデックスに関するナレッジポイントのまとめは終了です。必要な情報はすべてここにあります。データベースインデックスに関するより関連性の高いナレッジポイントについては、123WORDPRESS.COM の過去の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: