MySQL共通インデックスとユニークインデックスの選択に関する詳細な分析

各人が固有の携帯電話番号で登録し、ビジネスコードによって重複する携帯電話番号が 2 つ書き込まれないようにするユーザー管理システムがあるとします。ユーザー管理システムが携帯電話番号で名前を検索する必要がある場合、次のような SQL ステートメントを実行します。

mobile = '15202124529' のユーザーから名前を選択します。

通常は、モバイルフィールドにインデックスを作成することを検討します。携帯電話番号フィールドは比較的大きいため、通常は主キーとして使用されません。そのため、次の 2 つのオプションがあります。

1. id_cardフィールドに一意のインデックスを作成する
2. 通常のインデックスを作成する

ビジネスコードによって重複した ID 番号が書き込まれないことが保証されている場合、両方のオプションは論理的に正しいことになります。

パフォーマンスの観点から、一意のインデックスと通常のインデックスのどちらを選択すべきでしょうか?

図に示すように、フィールドkの値は重複していないと仮定します。

次に、これら 2 つの (ID、k) インデックスがクエリステートメントと更新ステートメントに与えるパフォーマンスへの影響を分析します。

クエリプロセス

クエリステートメントが select id from T where k=5 であると仮定します。クエリステートメントは、B+ ツリーのルートから始めて、レイヤーごとにリーフノード (図の右下隅にあるデータページ) までインデックスツリーを検索します。次に、データページがバイナリ検索を使用してレコードを検索すると想定できます (データページは、順序付けられた配列を使用してノードを格納します。データページは、双方向リンクリストを介して直列に接続されます)。

通常のインデックスの場合、条件 (5,500) を満たす最初のレコードを見つけた後、k=5 条件を満たさない最初のレコードに遭遇するまで次のレコードを見つける必要があります。
一意のインデックスの場合、インデックスによって一意性が定義されるため、条件を満たす最初のレコードが見つかった後に検索が停止します。

では、この違いによってパフォーマンスの差はどの程度大きくなるのでしょうか?答えは、ほとんどない、です。

理由: キー列が非常に大きく、複数の連続したキーがページ全体を占有しない限り、ページ IO が発生し、パフォーマンスの違いがより顕著になります。平均償却の観点から見ると、違いはほとんど無視できます。

InnoDB データはデータページ単位で読み書きされます。つまり、レコードを読み取る必要がある場合、レコード自体はディスクから読み取られるのではなく、ページ単位でメモリ全体に読み込まれます。 InnoDB では、各データページのデフォルトサイズは 16 KB です。

更新プロセス

共通インデックスと一意インデックスが更新ステートメントのパフォーマンスに与える影響を説明するには、まず変更バッファを導入する必要があります。

データページを更新する必要がある場合、データページがメモリ内にある場合は直接更新されます。
データページがまだメモリ内にない場合、データの一貫性に影響を与えずに次の操作が実行されます。

InnoDB はこれらの更新操作を変更バッファにキャッシュするため、ディスクからデータページを読み取る必要はありません。
次回クエリがこのデータページにアクセスする必要があるときに、データページがメモリに読み込まれます。
次に、変更バッファ内でこのページに関連する操作を実行します。

このようにして、データロジックの正確さが保証される。

名前は変更バッファと呼ばれますが、実際には永続データであることに注意してください。つまり、変更バッファはメモリ内にコピーされ、ディスクにも書き込まれます。

変更バッファ内の操作を古いデータページに適用して新しいデータページを取得するプロセスは、マージと呼ばれます。

P.S. このデータページにアクセスしてマージをトリガーするだけでなく、システムには定期的にマージするバックグラウンドスレッドがあります。マージ操作は、データベースの通常のシャットダウン時にも実行されます。

(変更バッファのマージ操作は、まず変更バッファ操作をメモリ内のデータページに更新します。この操作は、REDO ログに書き込まれます。MySQL がダウンしておらず、REDO ログがいっぱいでチェックポイントを移動する必要がある場合は、メモリ内のデータがディスク上のデータと整合しているかどうか、つまりダーティページかどうかを判断して、メモリ内のデータをディスクに更新します。MySQL がダウンしていてメモリがない、つまりダーティページがない場合は、REDO ログを通じて回復されます。)

明らかに、更新操作を最初に変更バッファに記録してディスクの読み取りを減らすことができれば、ステートメントの実行速度が大幅に向上します。

さらに、データをメモリに読み込むにはバッファプールを占有する必要があるため、この方法ではメモリの占有を回避し、メモリの使用率を向上させることもできます。

変更バッファはどのような条件下で使用できますか?

一意のインデックスの場合、すべての更新操作は、まずその操作が一意性制約に違反しているかどうかを判断する必要があります。

たとえば、レコード (4,400) を挿入するには、まず k=4 のレコードがテーブル内に既に存在するかどうかを判断する必要があり、これを判断するにはデータページをメモリに読み込む必要があります。

すべてがすでにメモリに読み込まれている場合は、メモリを直接更新する方が高速であり、変更バッファを使用する必要はありません。

したがって、変更バッファを使用してユニークインデックスを更新することはできず、実際には通常のインデックスのみを使用できます。

変更バッファはバッファプール内のメモリを使用するため、無制限に増やすことはできません。変更バッファのサイズは、パラメータ innodb_change_buffer_max_size を介して動的に設定できます。このパラメータを 50 に設定すると、変更バッファのサイズはバッファプールの最大 50% しか占有できないことを意味します。

Ps. データベースバッファプール (バッファプール) https://www.jianshu.com/p/f9ab1cb24230

分析: 新しいレコードの挿入 InnoDB 処理フロー

変更バッファのメカニズムを理解したところで、このテーブルに新しいレコード (4,400) を挿入する InnoDB プロセスは何でしょうか?

1. 最初のケースは、このレコードによって更新されるターゲットページがメモリ内にある場合です。

このとき、InnoDB の処理フローは次のようになります。一意のインデックスについては、3 と 5 の間の位置を見つけ、競合がないことを確認し、この値を挿入して、ステートメントの実行を終了します。
通常のインデックスの場合、3 と 5 の間の位置を見つけて値を挿入すると、ステートメントの実行が終了します。

このように、通常のインデックスと一意のインデックスが更新ステートメントのパフォーマンスに与える影響の違いは、単なる判断の問題であり、消費される CPU 時間はごくわずかです。しかし、これは焦点ではない

2. 2 番目の状況は、このレコードによって更新される対象ページがメモリ内に存在しないことです。このとき、InnoDB の処理フローは次のようになります。

一意のインデックスの場合、データページをメモリに読み込む必要があり、競合がない場合、値が挿入され、ステートメントの実行が終了します。
共通インデックスの場合、更新は変更バッファに記録され、ステートメントの実行が完了します。

ディスクからメモリへのデータの読み取りにはランダム IO アクセスが含まれ、データベースで最もコストのかかる操作の 1 つです。変更バッファによりランダムディスクアクセスが削減されるため、更新パフォーマンスの向上は明らかです。

変更バッファは主に更新操作をキャッシュし、非同期で処理するために使用されます。この方法では、各更新を変更バッファに直接記録できるため、非常に高速になり、ディスクへの複数の書き込みを 1 回のディスクへの書き込みに変換できます。

バッファ使用シナリオの変更

上記の分析から、変更バッファを使用すると更新プロセスが高速化されることがわかります。また、変更バッファは一般的なインデックスシナリオの使用に限定されており、一意のインデックスには適していないことも明らかです。

変更バッファを使用すると、共通インデックスのすべてのシナリオを高速化できますか?

マージはデータが実際に更新されるときであり、変更バッファの主な目的は記録された変更をキャッシュすることであるため、データページがマージされる前に、変更バッファに記録される変更が多いほど (つまり、このページを更新する必要がある回数が多いほど)、メリットは大きくなります。

したがって、書き込みが多く読み取りが少ないビジネスでは、ページが書き込まれた直後にアクセスされる可能性は比較的小さく、この時点で変更バッファが最も効果的になります。このタイプのビジネスモデルは、課金システムやログ記録システムでは一般的です。 (書き込みが多く読み取りが少ないシナリオに適しています。読み取りが多く書き込みが少ないと、変更バッファのメンテナンスコストが増加します)

逆に、業務の更新モードが書き込み直後にクエリを実行する場合、条件が満たされ、更新が最初に変更バッファに記録されたとしても、データページがすぐにアクセスされるため、マージプロセスがすぐにトリガーされます。これにより、ランダムアクセス IO の数は減りませんが、変更バッファのメンテナンスコストが増加します。したがって、このビジネスモデルでは、変更バッファには実際には副作用があります。（通常のインデックスの更新操作結果をすぐにクエリすると、マージ操作がトリガーされ、ディスク上のデータが変更バッファ内の操作レコードとマージされ、大量のIOが発生します）

インデックスの選択と実践

上記の分析に基づいて、共通インデックスと一意のインデックスのどちらを選択すればよいでしょうか?

実際、クエリ機能の点では、これら 2 種類のインデックスに違いはありません。主な考慮事項は、更新パフォーマンスへの影響です。したがって、可能な限り共通のインデックスを選択することをお勧めします。

すべての更新の後に同じレコードのクエリが続く場合は、変更バッファを閉じる必要があります。

それ以外の場合、変更バッファによって更新パフォーマンスが向上します。実際の使用においては、通常のインデックスと変更バッファの組み合わせは、大量のデータを持つテーブルの更新を最適化するのに非常に効果的です。

P.S. 特に機械式ハードディスクを使用する場合、変更バッファ機構の効果は非常に重要です。したがって、「履歴データ」のようなライブラリがある場合は、これらのテーブル内のインデックスに特に注意し、共通のインデックスを使用するようにし、変更バッファをできるだけ増やして、この「履歴データ」テーブルのデータ書き込み速度を確保する必要があります。

バッファとREDOログの変更

変更バッファの原理を理解すると、REDO ログや WAL (Write-Ahead Logging、その重要な点は最初にログを書き込んでからディスクに書き込むことです) を思い出すかもしれません。

パフォーマンスを向上させるWALのコアメカニズムは、ランダムな読み取りと書き込みを最小限に抑えることです。

テーブルに対して次の挿入ステートメントを実行します。

mysql> t(id,k) に値 (id1,k1),(id2,k2) を挿入します。

k インデックスツリーの現在の状態を想定すると、場所を見つけた後、k1 が配置されているデータページはメモリ (InnoDB バッファープール) 内にあり、k2 が配置されているデータページはメモリ内にありません。次の図は、変更バッファを使用した更新ステータス図を示しています。

図3: 変更バッファを使用した更新プロセス

この更新ステートメントを分析すると、次の 4 つの部分が含まれていることがわかります。

メモリ、REDO ログ (ib_log_fileX)、データテーブルスペース (t.ibd)、システムテーブルスペース (ibdata1)。

データテーブルスペース: テーブルデータファイルです。対応するディスクファイルは「テーブル名.ibd」です。システムテーブルスペース: データディクショナリなどのシステム情報を格納するために使用されます。対応するディスクファイルは「ibdata1」です。

データテーブルスペースとシステムテーブルスペースは、B+ ツリーに対応する複雑な構造を表しているようです。

この更新ステートメントは、次の操作を実行します (図の番号順)。

ページ 1 メモリ内で、メモリを直接更新します。
ページ2はメモリ内にないため、メモリ内の変更バッファ領域に「ページ2に行を挿入したい」と記録されます。
この情報は、上記の 2 つのアクションを REDO ログに記録します (図の 3 と 4)。

上記を実行すると、取引を完了できます。したがって、この更新ステートメントを実行するコストは非常に低く、2 つのメモリに書き込み、次に 1 つのディスクに書き込み (2 つの操作を合わせて 1 つのディスクに書き込み)、順番に書き込まれることがわかります。

変更バッファと REDO ログの粒度が異なります。変更されたデータがあるページがメモリ内に存在しない場合にのみ、変更バッファが変更バッファに一時的に保存されるためです。 REDO ログには、変更されたデータがすでにメモリ内にある場合でも、トランザクション内でデータを変更するすべての操作が記録されます。

同時に、図中の 2 つの点線矢印はバックグラウンド操作であり、更新応答時間には影響しません。

では、この後の読み取り要求をどのように処理すればよいのでしょうか?

たとえば、 select * from t where k in (k1, k2)を実行するとします。

読み取りステートメントが更新ステートメントの直後に実行され、メモリ内にデータがまだ存在する場合、この時点での 2 つの読み取り操作は、システムテーブルスペース (ibdata1) および REDO ログ (ib_log_fileX) とは関係ありません。

図4 変更バッファを使用した読み取りプロセス

図からわかるように、ページ 1 を読み取るときは、メモリから直接返されます。

WAL の後にデータを読み取る場合、ディスクを読み取る必要がありますか? 戻る前に、REDO ログ内のデータを更新する必要がありますか?

実際、それは必要ありません。ディスクにはまだ以前のデータが残っていますが、結果はメモリから直接返され、正しいものになります。ページ 2 を読み取るには、ページ 2 をディスクからメモリに読み取り、変更バッファー内の操作ログを適用して正しいバージョンを生成し、結果を返す必要があります。ご覧のとおり、このデータページは、ページ 2 の読み取りが必要になるまでメモリに読み込まれません。

更新パフォーマンスの向上におけるこれら 2 つのメカニズムの利点を単純に比較すると、REDO ログは主にランダムディスク書き込み (シーケンシャル書き込みに変換) の IO 消費を節約し、変更バッファは主にランダムディスク読み取りの IO 消費を節約します。

検討すべき質問:

1. 図 3 からわかるように、変更バッファは最初にメモリに書き込まれます。この時点でマシンの電源が切れて再起動すると、変更バッファは失われますか?変更バッファが失われるのは、決して小さな問題ではありません。ディスクからデータを再度読み取るときに、マージプロセスが実行されず、データが失われることになります。このような状況は起こるのでしょうか？

答え：

1. 変更バッファは部分的にメモリ内にあり、部分的に ibdata 内にあります。

パージ操作は、変更バッファ内の対応するデータをibdataに保持する必要があります。

2. REDOログはデータページの変更と変更バッファに書き込まれた新しい情報を記録する。

電源が失われた場合、永続的な変更バッファのデータは消去されるため、復元する必要はありません。主に持続性のないデータを分析する

状況は以下のカテゴリーに分類されます。

（１）変更バッファが書き込まれると、REDOログは同期されるがコミットされず、バイナリログはディスクに同期されないため、この部分のデータは失われます。
（2）変更バッファが書き込まれ、REDOログが書き込まれますが、コミット、バイナリログ、またはディスクへのfsyncは行われません。まずバイナリログからREDOログを復元し、次に変更バッファをREDOログから復元します。
（３）変更バッファが書き込まれた後、REDOログとバイナリログの両方がfsyncされているので、REDOログから直接データを復元できます。

要約する

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

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