MySQLインデックスとは何ですか？わからない場合は聞いてください

概要

以下は、インデックスを作成する必要がある一般的なシナリオです。比較のために、テスト テーブルが作成されます (a にはインデックスがあり、d にはインデックスがありません)。

mysql> create table test( --テストテーブルを作成-> id int(10) not null AUTO_INCREMENT,
    -> int(10) デフォルトはnull、
    -> b int(10) デフォルトはnull、
    -> c int(10) デフォルトはnull、
    -> d int(10) デフォルトはnull、
    -> 主キー(id)、 --主キーインデックス-> ​​key idx_a(a)、 --補助インデックス-> ​​key idx_b_c(b,c) --結合インデックス-> ​​)engine=InnoDB charset=utf8mb4;
クエリは正常、影響を受けた行は 0 行、警告は 5 件 (0.09 秒)
mysql> insert_test_dataが存在する場合はプロシージャを削除します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)
mysql> delimiter | -- 100,000 個のデータを挿入するストアド プロシージャを作成します。mysql> create procedure insert_test_data()
    -> 開始
    -> i int を宣言します。
    -> i=1 を設定します。
    -> while(i<=100000) を実行する
    -> test(a,b,c,d)values(i,i,i,i) に挿入します。
    -> i=i+1 を設定します。
    -> 終了しながら;
    -> 終了 |
クエリは正常、影響を受けた行は 0 行 (0.11 秒)
mysql> 区切り文字;
mysql> call insert_test_data(); --ストアド プロシージャを実行 クエリは正常に実行されました。1 行が影響を受けました (11 分 44.13 秒)

データを取得するときに条件フィールドにインデックスを追加する

集計関数は集計フィールドにインデックスを追加します

ソートフィールドにインデックスを追加する

テーブルにインデックスを追加しないようにするには

関連クエリの関連フィールドにインデックスを追加する

インデックスを使用した後、クエリ速度の最適化が大幅に改善されていることがわかります。この記事では、MySQL インデックスを基礎から実践まで理解します。

二分木からB+木へ

バイナリツリー:

バイナリ ツリーは、最大 2 つの子ノードを持つツリー データ構造です。親ノードのないノードはルート ノードと呼ばれ、子ノードのないノードはリーフ ノードと呼ばれます。

二分探索木は、任意のノードの左の子が現在のノードのキー値よりも小さく、右の子が現在のノードのキー値よりも大きい木です。

以下の二分探索木に示すように、データを一致させるには ⌈ log ( n ) ⌉ ⌈log(n)⌉ ⌈log(n)⌉、つまり最大で 3 回しか必要ありませんが、線形探索では最悪の場合 nnn 回必要になります。

しかし、下図に示すように、バイナリツリーはリンクリストに退化することがあり、これはフルスキャンと同等であり、効率が低下します。この問題を解決するには、バイナリツリーが常にバランスが取れていること、つまりバランスの取れたバイナリツリーであることを保証する必要があります。

バランスのとれた二分木:

バランスのとれた二分木 (AVL 木) では、二分木の特性を満たすために、各ノードの左サブツリーと右サブツリーの高さの差が 1 を超えないことが求められます。ツリー構造のバランスを確保します。データの挿入や削除によって不均衡が生じた場合、バランスを保つようにノードを調整します（具体的なアルゴリズムは省略）ので、検索効率が確保されます。

バランスのとれたバイナリ ツリーのノードは、キー値とデータに対応します。データを検索するたびに、ディスクからノード (ディスク ブロックと呼ばれる) を読み取る必要があります。1 つのノードは 1 つのディスク ブロックに対応します。膨大な量のデータを保存する場合、ツリーには多数のノードが含まれ、多くのディスク I/O が実行されますが、検索効率は依然として非常に低くなります。これには、単一のノードに複数のキー値とデータを格納できるバランスの取れたツリーが必要です。

B ツリー: B ツリー (バランス ツリー) は、1 つのノードに複数のキー値とデータを格納できるバランス ツリーです。各ノードはページと呼ばれ、データのページになります。各ノードにはさらに多くのキー値とデータが格納され、キー値とデータの両方が 1 つのページに配置され、各ノードにはさらに多くの子ノードがあります。子ノードの数は一般に順序と呼ばれます。データ検索時にBツリーがディスクを読み取る回数が大幅に削減され、検索効率はAVLよりもはるかに高くなります。

下の図に示す3次Bツリーで、id=42のデータを検索します。まず、最初のページでキー値 42 が 39 より大きいことを判断し、ポインタ P3 に従って 4 ページ目を見つけて比較します。キー値 45 より小さいです。次に、ポインタ P1 に従って 9 ページ目を見つけて、一致するキー値 42 がある、つまり対応するデータが見つかったことを確認します。

B+ツリー:

B+ ツリーは B ツリーをさらに最適化したものです。簡単に言えば、B+ ツリーの非リーフ ノードにはデータが格納されず、キー値のみが格納されます。その理由は、データベースのページサイズが固定されているからです（InnoDB のデフォルトは 16KB）。データが格納されていない場合は、より多くのキー値を格納でき、ノードの数が増えるほど、データを検索するためのディスク I/O 回数がさらに削減されます。

さらに、B+ ツリーの順序はキー値の数と同じです。ノードに 1,000 個のキー値が保存されている場合、10 億個のデータを保存するには 3 つのレイヤーのみが必要です。したがって、通常、10 億個のデータを検索するには 2 回のディスク I/O のみが必要です (すばらしい)。

同時に、B+ツリーのリーフノードのデータは順番に並べられているため、B+ツリーは範囲検索、ソート検索、グループ化検索に適しています（Bのデータはノード上に分散しているため、比較的困難です）。これが、MySQLがB+ツリーインデックスを使用する理由です。

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

クラスター化インデックスは、ディスク上の実際のデータを再編成し、指定された 1 つ以上の列の値で並べ替えるインデックスの一種です。データ行の物理的な順序は、列の値（通常は主キー列）の論理的な順序と同じです。テーブルにはクラスター化インデックスを 1 つだけ存在させることができます（1 つの物理的な順序でしか格納できないため）。

InnoDBクラスター化インデックスを使用します。テーブル内のすべてのデータには主キーがあります。主キーを作成しなくても、 InnoDB主キーとして一意のキーを選択します。テーブルに一意のキーが定義されていない場合、 InnoDB row_id という名前の非表示の列を主キーとしてテーブルに追加します。

つまり、InnoDB を介して B+ ツリーにデータを保存し、B+ ツリーのキー値が主キーになります。次に、B+ ツリーのリーフ ノードにテーブル内のすべてのデータ (つまり、主キーに対応するデータの行全体) を保存します。データ ファイルとインデックス ファイルは同じファイルです。インデックスが見つかるとデータが見つかるため、これをクラスター化インデックスと呼びます。

クラスター化インデックスの更新にはコストがかかります。新しい行を挿入したり、主キーを更新したりすると、更新された各行は強制的に新しい場所に移動されます (主キーでソートされるため)。行を移動すると、ページ分割の問題 (つまり、ページがいっぱいになる) が発生する場合もあります。ストレージ エンジンは、それに対応するためにページを 2 つのページに分割しますが、ページ分割によってディスク領域がさらに必要になります。つまり、インデックスが再配置され、リソースが無駄になります。

クラスター化インデックスは範囲クエリに適しています。クラスター化インデックス クエリは非常に高速で、範囲チェック (between、<、<=、>、>=) やグループ化および順序付けクエリに特に適しています。クラスター化インデックスが最初の値を含む行を見つけた後、後続のインデックス値の行はそれ以上検索せずに物理的に接続されるため、広範囲のスキャンが回避され、クエリ速度が大幅に向上します。

たとえば、id>=19 かつ id<30 のデータを照会する場合、通常、ルート ノードはメモリ内に常駐します (つまり、ページ 1 はすでにメモリ内にあります)。まず、ページ 1 のキー値 19 とそれに対応するポインター P2 を見つけ、P2 を介してページ 3 を読み取ります (この時点ではページ 3 はメモリ内になく、ディスクからロードする必要があります)。次に、ページ 3 のキー値 19 のポインター P1 を見つけ、ページ 8 (これもディスクからメモリにロードされます) を見つけます。データはリンク リストで順番にリンクされているため、キー値 19 に対応するデータはバイナリ検索で見つけることができます。

キー値 19 を見つけた後、範囲検索であるため、リーフ ノードのリンク リストを照会し、満たされる条件を順番にトラバースして一致させ、キー値 21 を探し続けます。最後のデータはまだ要件を満たすことができません。この時点で、ページ 8 のポインター P を使用してページ 9 のデータを読み取ります。ページ 9 はメモリ内にないため、ディスクからロードしてメモリに読み込む必要があります。その後、キー値 34 が一致し、条件が満たされなくなるまで、随時推論され、その後終了します。これは、クラスター化インデックスを介してデータを検索する方法です。

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

非クラスター化インデックスまたは非クラスター化インデックス (セカンダリ インデックス) は、主キー以外の列をキー値として構築された B+ ツリー インデックスです。インデックス内のインデックスの論理順序は、ディスク上の行の物理的な格納順序とは異なります。テーブルには複数の非クラスター化インデックスを含めることができます。 InnoDBでは、主キーインデックスを除いて、その他のインデックスは補助インデックスまたはセカンダリインデックスと呼ばれることがあります。

MySQL のMyISAMは非クラスター化インデックスを使用します。テーブルデータの格納順序はインデックスデータとは関係ありません。リーフノードには、インデックスフィールド値とデータページデータ行への論理ポインターが含まれています（行数はデータテーブル内のデータ量と同じです）。したがって、データを検索する場合は、主キーに基づいてクラスター化インデックスを検索する必要があります。クラスター化インデックスに基づいてデータを検索するプロセスをテーブルリターンと呼びます。

たとえば、 test.frm 、 tsst.myd 、 test.myiで構成されるデータ テーブル test を定義します。

• .frm: テーブル定義ステートメントを記録します。
• myd: 実際のテーブルデータを記録します。
• myi: インデックスデータを記録する

データを取得するときは、まずインデックスツリーtest.myiを検索し、データが配置されているtest.mydの行位置を取得して、データを取得します。したがって、 MyISAMエンジンのインデックス ファイルとデータ ファイルは別々で独立しています。インデックスを見つけることは、データ、つまり非クラスター化インデックスを見つけることを意味しません。

テーブルには、複数の非クラスター化インデックスを設定できます。実際、各テーブルには最大 249 個の非クラスター化インデックスを設定できます。ただし、フィールドに新しいインデックスが作成されるたびに、フィールドのデータがコピーされてインデックスが生成されます。そのため、テーブルにインデックスを追加すると、テーブルのサイズが大きくなり、大量のディスク領域とメモリを消費することになります。したがって、ディスク容量とメモリが限られている場合は、非クラスター化インデックスの数を制限する必要があります。

さらに、非クラスター化インデックスを持つテーブル内のデータを変更するたびに、インデックスも同時に更新する必要があるため、非クラスター化インデックスによって挿入と更新の速度が低下します。

たとえば、データ 36 を検索するには、2 つの数字を使用します。最初の数字 36 はインデックスのキー値を表し、2 番目の数字 64 はデータの主キーを表します。したがって、36 を見つけた後でもデータは取得できず、対応する主キーに基づいてデータを見つけるためにクラスター化インデックス テーブルに移動する必要があります。

ジョイントインデックスとカバーインデックス

結合インデックスは、その名前が示すように、テーブル上の複数の列を結合するインデックスです。共同インデックスを作成する場合、MySQL インデックス クエリは左端のプレフィックス一致の原則に従うため、最も頻繁に使用される列はビジネス ニーズに応じて左側に配置されます。

最左接頭辞一致原則とは、最左が最初であることを意味します。データを取得する際、一致は結合インデックスの最左から始まります。したがって、(a, b, c) などの結合インデックスを作成すると、(a)、(a, b)、(a, b, c) の 3 つのインデックスを作成するのと同じになります。これが最左一致原則です。

カバーリング インデックスは、特定の SELECT ステートメントに固有の結合インデックスです。つまり、SELECT ステートメントの場合、ジョイント インデックスは、クエリのためにテーブルに戻ることなく、インデックスを介してクエリ結果を直接取得できます。このジョイント インデックスは、この SELECT ステートメントをカバーすると言われています。非クラスター化インデックスのバックテーブルクエリの問題を完全に解決できますが、前提として、クエリを実行するときにインデックスの左端の一致原則に注意する必要があります。

B+ツリーインデックスとハッシュインデックス

原理：

• B+ ツリー インデックスでは、対応するデータ ブロックを見つけるためにバイナリ検索を複数回使用する必要がある場合があります。
• ハッシュ インデックスを使用する場合、ハッシュ関数を使用してハッシュ値を計算し、テーブル内の対応するデータを検索します。

ハッシュインデックスは、多数の異なるデータの等しい値を正確に検索するのに適していますが、あいまい検索や範囲検索をサポートしておらず、並べ替えに使用できず、結合インデックスの左端一致原則をサポートしていません。また、重複するキー値が多数ある場合、ハッシュ衝突の問題が発生します。

通常のインデックスとユニークインデックス

共通インデックスのフィールドには重複した値を書き込むことができますが、一意インデックスのフィールドには重複した値を書き込むことはできません。まず、挿入バッファと変更バッファを紹介します。

• バッファを挿入
• 非クラスター化インデックスを挿入する場合は、まず、挿入された非クラスター化インデックスがキャッシュ プール内にあるかどうかを判断します。存在する場合は直接挿入されます。存在しない場合は、まず挿入バッファに入れられ、その後、挿入バッファと補助インデックス ページの子ノードのマージ操作が一定の頻度と状況でミラーリングされます。複数の挿入を 1 つの操作にマージして、個別のディスク読み取りを減らします。インデックスはセカンダリであり、一意ではない必要があります。
• バッファの変更
• 挿入だけでなく削除や変更もサポートする Insert Buffer のアップグレード版です。使用シナリオは innodb_change_buffer を通じて設定され、デフォルトは all です (none、inserts、changes などの値もあります)。最大メモリ使用率は innodb_change_buffer_max_size を通じて設定されます (デフォルトは 25%、最大値は 50%) が、RR 分離レベルではデッドロックが発生する可能性があります。また、インデックスはセカンダリ インデックスであり、一意ではないことも必要です。

ユニーク インデックスでは、一意性制約に違反しているかどうかを判断するために挿入バッファーを使用します。すべてがメモリに読み込まれている場合は、メモリを直接更新する方が高速であり、変更バッファーを使用する必要がないためです。

通常のインデックスでは、条件を満たす最初のレコードが見つかった後、条件が満たされなくなるまで次のレコードの検索が続けられます。一意のインデックスの場合、最初のレコードが見つかった時点で検索は終了します。ただし、InnoDB はページごとにデータをメモリに読み込み、後で要件を満たすデータはすべて前のデータ ページにある可能性があるため、通常のインデックスをさらに数回スキャンすることによるメモリ消費は無視できます。

まとめ：

• データが変更されると、通常のインデックスは変更バッファを使用できますが、一意のインデックスは使用できません。
• データが変更されると、一意のインデックスは RR 分離レベルでデッドロックが発生しやすくなります。
• データをクエリする場合、共通インデックスはレコードを見つけた後も次のレコードを決定し続ける必要がありますが、一意のインデックスはレコードを見つけた後すぐに戻ります。
• ビジネスでフィールドが一意であることが求められる場合、コードによって一意の値が書き込まれることが保証されるなら通常のインデックスを使用し、そうでない場合は一意のインデックスを使用します。

InnoDB と MyISAM

対照的に、基本的にはMyISAM代わりにInnoDBを使用することを検討できます。 InnoDBも現在の MySQL のデフォルトエンジンですが、具体的な問題を具体的に分析する必要があり、実際の状況に応じて両者の長所と短所を比較し、より適切なものを選択することもできます。

MyISAMクエリがInnoDBよりも高速である理由を詳しく説明しましょう。

• InnoDB はデータとインデックスをキャッシュしますが、MyISAM はインデックスのみをキャッシュし、スワップインとスワップアウトを削減します。
• InnoDB アドレスはブロックにマップされ、次に行にマップされます。MyISAM はファイルの OFFSET を直接記録するため、位置決めが速くなります。
• InnoDB は MVCC の一貫性も維持する必要があります。シナリオにそれがない場合もありますが、それでもチェックして維持する必要があります。

MVCC (マルチバージョン同時実行制御)

InnoDB は、MVCC を実装するために、各行レコードに 2 つの追加の隠し値 (作成バージョン番号と削除バージョン番号) を追加します。1 つは行データの作成時刻を記録し、もう 1 つは行データの有効期限/削除時刻を記録します。ただし、InnoDB はこれらのイベントが発生した実際の時刻を保存しません。代わりに、これらのイベントが発生したときのシステム バージョン番号のみを保存します。トランザクションが作成され、拡大するにつれて、各トランザクションは最初に独自のシステム バージョン番号を記録し、各クエリはデータの各行のバージョン番号がトランザクション バージョン番号と同じかどうかを確認する必要があります。つまり、各データ行の作成バージョン番号は、トランザクションが作成される前に行データが存在することを保証するために、トランザクション バージョン番号より大きくありません。また、行データの削除バージョン番号は、トランザクションが開始する前に行データが削除されないことを保証するために、トランザクション バージョン番号より大きいか、未定義です。

explain を使用してインデックスの使用状況を分析する

explain SQL ステートメントの実行効果を表示できるため、より適切なインデックスを選択し、クエリ ステートメントを最適化するのに役立ちます。構文は、 explain select... from ... [where...]。

前のセクションで概説したテスト テーブルを使用して、次の項目をテストします。

mysql> explain select * from test where a=88888;
+----+-------------+---------+-----------+-------+---------------+--------+-------+-------+------+------+------+------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+-----------+-------+---------------+--------+-------+-------+------+------+------+------+
| 1 | SIMPLE | テスト | NULL | ref | idx_a | idx_a | 5 | const | 1 | 100.00 | NULL |
+----+-------------+---------+-----------+-------+---------------+--------+-------+-------+------+------+------+------+
セットに 1 行、警告 1 回 (0.03 秒)
mysql> explain select b,c from test where b=88888;
+----+-------------+--------+-----------+---------+---------------+----------+--------+----------+-----------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+---------------+----------+--------+----------+-----------+-------------+
| 1 | SIMPLE | テスト | NULL | ref | idx_b_c | idx_b_c | 5 | const | 1 | 100.00 | インデックスを使用 |
+----+-------------+--------+-----------+---------+---------------+----------+--------+----------+-----------+-------------+
セットに 1 行、警告 1 件 (0.00 秒)
mysql> explain select * from test where a=(select a from test where a=88888);
+----+-------------+--------+-----------+--------+---------------+--------+-------+-------+-------+--------+--------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+--------+---------------+--------+-------+-------+-------+--------+--------+
| 1 | PRIMARY | test | NULL | ref | idx_a | idx_a | 5 | const | 1 | 100.00 | where の使用 |
| 2 | サブクエリ | テスト | NULL | ref | idx_a | idx_a | 5 | const | 1 | 100.00 | インデックスを使用 |
+----+-------------+--------+-----------+--------+---------------+--------+-------+-------+-------+--------+--------+
セットに 2 行、警告 1 件 (0.00 秒)

select_type 、 type 、 extra 3 つの列に注目してください。

タイプ（このデータ行がどのテーブルに関するものかを示します）

追加（クエリを解決するための詳細）

要約する

この記事はこれで終わりです。皆さんのお役に立てれば幸いです。また、123WORDPRESS.COM のその他のコンテンツにも注目していただければ幸いです。