MySQL のユニークインデックスと通常のインデックスのどちらを選択すればよいでしょうか?

ユーザー テーブルを設計するときに、各人の ID 番号が一意であり、検索する必要があるシナリオを想像してください。ただし、ID番号フィールドは大きいため、主キーとして使用するには適していません。ビジネス コードによって挿入された ID カードが一意であることが保証されている場合、一意のインデックスと通常のインデックスを作成するように選択できます。どのように選択すればよいでしょうか?次に、クエリと更新の実行プロセスを分析します。

クエリプロセス

k はテーブル t のインデックスであると仮定します。select select id from t where k=5検索する場合、B+ ツリー k のルートから開始し、リーフ ノードをレイヤーごとに検索し、k=5 のデータ ページを見つけて、データ ページの内容に対してバイナリ ポジショニングを実行します。

通常のインデックスの場合、k=5 のレコードを見つけた後、5 以外の最初のレコードに遭遇するまで、もう一度検索を続けます。

一意のインデックスの場合、値は一意であるため、見つかったら検索を停止します。

InnoDB はデータ ページ単位で読み取りと書き込みを行うため (データ ページはデフォルトで 16 KB)、データが読み取られるときには、データ ページ全体がまとめてメモリに読み込まれます。 メモリに読み込まれたデータページ内に、k=5 のレコードが含まれている場合、クエリの場合、ユニークインデックスは通常のインデックスよりも検索と判断のプロセスが 1 つ多くなりますが、これは無視できます。

k=5 が現在のデータ ページの最後のエントリである場合、次のデータ ページを読み取る必要があります。しかし、これが起こる可能性は低いので無視できます。

したがって、一般的に、クエリ プロセス中、通常のインデックスと一意のインデックスの間に大きな違いはありません。

バッファを変更する

ユニークインデックスと共通インデックスの影響を分析する前に、まず変更バッファ構造を理解しましょう。

変更バッファとは何ですか?

更新操作を実行する際、更新対象のデータ ページがメモリ内にある場合は、直接更新されます。そうでない場合は、データの一貫性に影響を与えることなく、InnoDB は更新操作を変更バッファにキャッシュし、ディスクからデータ ページを読み取るプロセスを排除します。次のクエリ操作で更新が必要なデータ ページが読み取られると、変更バッファー内の更新ステートメントが実行され、データ ページに書き込まれます。ハードディスクに操作を適用するプロセスはマージと呼ばれます。バックグラウンド スレッドは定期的にマージするか、データベースが正常に閉じられるときにもマージ操作が実行されます。

マージの実行プロセスは次のとおりです。

1. ディスクからデータ ページの古いバージョンを読み取ります。
2. 変更バッファからデータ ページに関連付けられたレコードを検索し、それらを 1 つずつ適用して、データ ページの新しいバージョンを取得します。
3. データの変更と変更バッファの変更を記録するために、REDO ログを書き込みます。

変更バッファは実際にはハードディスクに保存できるデータです。つまり、変更バッファはメモリとハードディスクの両方に存在します。変更バッファは、以前は挿入バッファと呼ばれていました。当初は挿入バッファのみが最適化されていましたが、後に削除と更新のサポートが追加され、名前が変更バッファに変更されました。

最初に更新操作を変更バッファに記録することで、ディスク データ ページをメモリに読み込むプロセスが削減され、ステートメントの実行速度が大幅に向上することがわかります。同時に、メモリへのデータの読み取りはバッファ プール メモリを占有するため、読み取り操作を減らすことでメモリ使用率も向上します。

バッファ プールは、InnoDB がアクセスするときにテーブルとインデックスのデータをキャッシュするメモリ内の領域です。頻繁に使用されるデータをメモリ内で直接更新できるため、処理が高速化されます。一部の専用サーバーでは、物理メモリの 80% がバッファ プールに分割されます。

innodb_change_buffer_max_size を使用して、変更バッファが占有するバッファ プールのサイズを設定できます。

バッファ適用シナリオを変更しますか?

前述のように、変更バッファは更新レコードを事前に保存し、データ ページの読み取りプロセスを削減してパフォーマンスを向上させます。つまり、変更バッファーにさまざまなデータ ページの更新レコードがより多く含まれている場合、メリットは大きくなります。

したがって、書き込みが多く読み取りが少ないビジネス（更新後の即時クエリ）では、変更バッファがより大きな役割を果たします。共通の課金システムやログ記録システムなど。

ビジネスが更新直後にクエリを実行する場合、更新レコードを変更バッファに配置できますが、直後にデータ ページをクエリする必要があるため、マージ プロセスがすぐにトリガーされます。これにより、ランダム アクセス IO の数は減りませんが、変更バッファーのメンテナンス コストが増加し、逆の効果が生じます。

更新プロセス

一意のインデックスの場合、すべての更新操作は一意性制約に違反しているかどうかを判断する必要があります。したがって、必要なデータ ページをメモリに読み込み、変更バッファを使用せずに直接更新する必要があります。したがって、変更バッファは通常のインデックスにのみ役立ちます。

特定の分析を行うには、テーブルに新しいレコードを挿入します。

新しいレコードによって更新されるデータ ページがメモリ内にある場合:

一意のインデックスの場合、適切な位置を見つけ、競合があるかどうかを判断し、値を挿入すると、ステートメントが終了します。

通常のインデックスの場合: 位置を見つけて値を挿入すると、ステートメントが終了します。

したがって、データ ページがメモリ内にある場合、ユニーク インデックスと通常のインデックスの唯一の違いは、判断プロセスです。無視できます。

新しいレコードによって更新されるデータ ページがメモリ内にない場合:

一意のインデックスの場合、データ ページがメモリに読み込まれ、競合が判断され、データが挿入されて、ステートメントが終了します。

共通インデックスの場合、ステートメントは変更バッファに記録され、ステートメントは終了します。

ディスクからメモリへのランダム IO アクセスが関係するため、これはデータベース内で最もコストのかかる操作の 1 つです。通常のインデックスでは、一意のインデックスと比較して読み取り操作が削減されるため、パフォーマンスが大幅に向上します。

ユニークまたは通常のインデックスの選択

クエリと更新の観点から 2 つを比較します。クエリ プロセス中、非常に特殊な状況を除いて、2 つの違いは実際にはそれほど大きくないことがわかっています。

主な違いは、更新中に、更新されるデータ ページがコンテンツ内にない場合です。このとき、ユニークインデックスはユニーク性チェックが必要なため、変更バッファを使用できません。ディスクからコンテンツにデータを読み込むという追加のプロセスがあり、ランダム IO アクセスが伴い、比較的非効率的です。

したがって、ビジネスで良好なパフォーマンスを更新する必要がある場合は、通常のインデックスを選択できます。もちろん、すべてはデータの正確性を確保するという前提に基づいています。

更新の後にクエリが続く場合は、変更バッファをオフにすることを検討してください。その他の場合は、変更バッファによって大幅な改善が得られる可能性があります。

特に機械式ハードドライブの場合、変更バッファの影響は非常に重要です。

REDOログと変更バッファの比較

InnoDB に REDO ログが導入されたことで、最初にログを書き込み、次に WAL を介してディスクに書き込むことでクラッシュセーフとなり、効率が向上しました。

変更バッファは、ディスクからメモリにデータ ページを読み取るランダム IO プロセスを保存します。

挿入ステートメントを通じて、2 つの関係を分析してみましょう。

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

k が通常のインデックスであると仮定すると、k1 によって挿入されたデータ ページはメモリ内にありますが、k2 はメモリ内にありません。

挿入操作を実行する場合、主に次の 4 つの部分が関係します。

InnoDB バッファプール: メモリ領域

再実行ログ: ログ

システム表スペース (ibdata1): システム表スペース

data(t.idb): データテーブルスペース

innodb_file_per_table がオンの場合、テーブルは別のテーブルスペースに作成されます。それ以外の場合は、システム テーブルスペースに作成されます。

実行プロセスは次のとおりです。

1. k1が配置されているページ1はメモリ内にあるため、メモリを直接更新します。
2. k2 が配置されているページ 2 はメモリ内にはありませんが、変更バッファーに記録されています。
3. k1 と k2 の操作を REDO ログに記録します。
4. トランザクションをコミットします。
   

この更新ステートメント (挿入、削除、更新操作を含む) の実行コストは、メモリへの書き込みが 2 回、ディスクへの順次書き込みが1 回と非常に低いことがわかります。点線でマークされた操作はバックグラウンド操作であり、応答時間には影響しません。

別のクエリステートメントを見てみましょう。

(k1, k2) 内の k が t から * を選択する

読み取りステートメントが更新ステートメントの直後に実行され、メモリ内のデータがまだそこにあると仮定すると、読み取り操作はシステム テーブルスペースおよび REDO ログとは関係ありません。

実行プロセス:

1. メモリ内の k1 が配置されているページ 1 を読み取り、直接返します。ディスク上のデータは読み取られず、ディスク上のデータは以前のバージョンのままである可​​能性があることに注意してください。
2. k2 が配置されているページ 2 を読み取ります。このとき、ページ 2 をディスクからメモリにロードし、変更バッファの内容を適用してから、正しい結果を返す必要があります。ここでも、変更バッファは更新直後の読み取りには適していないことがわかります。
   

REDO ログと変更バッファの関係をまとめると次のようになります。

保存場所: 変更バッファもハードディスク上に保存されますが、システム テーブルスペース ibdata1 に保存されます。 REDO ログは別のファイルです。

記録内容: 変更バッファには更新操作の内容が記録され、REDO ログには通常のデータ ページの変更と変更バッファ内の変更が記録されます。

ディスク同期プロセス: メモリ内のデータ ページへの変更の同期は、REDO ログに基づくのではなく、マージ操作によって実行されます。

更新プロセスの観点から見ると、REDO ログはランダム ディスク書き込み IO をシーケンシャル書き込みに変換し、変更バッファはランダム ディスク読み取り IO の消費を節約します。

サーバーの電源が予期せず失われた場合、変更バッファは失われますか?

いいえ、変更バッファ内のデータはREDOログに記録されているため、失われることはありません。

変更バッファ データの一部はディスク上にあり、一部はメモリ内にあるためです。ディスク上のデータは結合されているため、失われることはありません。
メモリ内のデータの場合:

1. 変更バッファが書き込まれても、REDO ログと binlog がコミットされていない場合、トランザクションはロールバックされ、この部分のデータは存在しなくなります。
2. 変更バッファ、REDO ログ、および binlog が書き込まれてコミットされた場合、それらは失われません。 REDO ログから直接復元します。
3. 変更バッファが書き込まれ、REDO ログが書き込まれたがコミットされておらず、バイナリログが書き込まれている場合は、バイナリログから REDO ログを復元してから、変更バッファを復元します。

参考文献

バッファプール

上記は、MySQL のユニーク インデックスと通常のインデックスのどちらを選択すればよいかということです。 MySQL のユニークインデックスと共通インデックスの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。