MySQL共通インデックスとユニークインデックスの詳細な説明

シナリオ

1. ID番号のフィールドを備えた市民システムを維持する

2. ビジネス コードでは、重複する ID 番号が 2 つ書き込まれないようにすることができます (ビジネスでこれを保証できない場合は、データベースの一意のインデックスを使用して制約することができます)

3. 一般的な SQL クエリ ステートメント: SELECT name FROM CUser WHERE id_card = 'XXX'

4. インデックスを作成する

• ID番号は比較的大きいため、主キーとして設定することは推奨されません。
• パフォーマンスの観点から、通常のインデックスと一意のインデックスのどちらを選択すべきでしょうか?

フィールドkの値は重複していないと仮定する

クエリプロセス

1. クエリステートメント: SELECT id FROM T WHERE k=5

2. クエリプロセス

• B+ ツリーのルートから始めて、レイヤーごとにリーフ ノード (上図の右下隅にあるデータ ページ) まで検索します。
• バイナリ検索を使用してデータページ内の特定のレコードを検索します

3. 共通インデックスの場合

• 条件（5,500）を満たす最初のレコードを見つけ、次にk=5を満たさない最初のレコードが見つかるまで次のレコードを探します。

4. ユニークインデックスの場合

• インデックスは一意性を定義するため、条件を満たす最初のレコードが見つかった後に検索は停止します。

パフォーマンスの違い

1. パフォーマンスの違い: 非常に小さい

2. InnoDBデータはデータページ単位で読み書きされます。デフォルトは16KBです。

3. レコードを読み取る必要がある場合、レコード自体はディスクから読み取られるのではなく、データ ページ単位で読み取られます。

4. k=5のレコードが見つかった場合、そのレコードが配置されているデータページはすでにメモリ内に存在する。

5. 通常のインデックスでは、ポインタ検索と計算が1回ずつ追加されるだけで済み、CPU消費量は非常に少ない

• レコード k=5 がデータ ページの最後のレコードである場合、次のレコードを取得するには、次のデータ ページを読み取る必要があります。
• 非常に低い確率: 整数フィールドインデックスの場合、データページ (16KB、コンパクト形式) には約 745 個の値を格納できます。

バッファを変更する

1. データページを更新する必要がある場合、データページがメモリ内にある場合は直接更新されます。

2. データページがメモリ内にない場合、データの一貫性に影響を与えずに

• InnoDBはこれらの更新操作を変更バッファにキャッシュします。
• このデータページをディスクから読み取る必要はありません（ランダム読み取り）
• 次回クエリがこのデータページにアクセスする必要がある場合、データページはメモリに読み込まれ、変更バッファ内のこのデータページに関連する操作が実行されます（マージ）。

3. 変更バッファは永続的なデータであり、メモリ内にコピーがあり、ディスクにも書き込まれます。

4. ランダムディスク読み取りを減らし、ステートメントの実行速度を向上させるために、最初に更新操作を変更バッファに記録します。

5. さらに、データページをメモリに読み込むには、バッファプールを占有する必要があります。変更バッファを使用すると、メモリの占有を回避し、メモリの使用率を向上させることができます。

6. 変更バッファはバッファプール内のメモリを使用するため、無制限に増やすことはできません。制御パラメータinnodb_change_buffer_max_size

# デフォルトは25、最大値は50
mysql> '%innodb_change_buffer_max_size%' のような変数を表示します。
+---------------------------------+-------+
| 変数名 | 値 |
+---------------------------------+-------+
| innodb_change_buffer_max_size | 25 |
+---------------------------------+-------+

マージ

1. マージ: 変更バッファ内の操作を元のデータページに適用する

2. マージの実行プロセス

• ディスクからメモリにデータページを読み込む（旧バージョンのデータページ）
• 変更バッファからこのデータ ページの変更バッファ レコードを検索します (複数の場合もあります)
  次に、それらを1つずつ実行して、データページの新しいバージョンを取得します。
• 書き込みREDOログ（データページのテーブル更新+変更バッファの変更を含む）
  

3. マージが実行された後、変更バッファに対応するメモリ内のデータ ページとディスク ページは変更されておらず、ダーティ ページになります。

• 他のメカニズムを通じて、ダーティ ページは対応する物理ディスク ページにフラッシュされます。

4. トリガータイミング

• このデータページにアクセス
• システムのバックグラウンドスレッドは定期的にマージされます
• データベースは正常にシャットダウンされました

利用条件

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

2. ユニークインデックスの更新では変更バッファを使用できません。共通インデックスのみが変更バッファを使用できます。

• 主キーも変更バッファを使用できません。
• たとえば、(4,400) を挿入するには、まずテーブルに k=4 のレコードがあるかどうかを判断する必要があります。この判断の前提は、データ ページをメモリに読み込むことです。
• データ ページはメモリに読み込まれているため、変更バッファに書き込まずにメモリ内のデータ ページを直接更新するだけで十分です。

使用シナリオ

1. データ ページがマージされる前に変更バッファーに記録されている変更が多いほど、メリットは大きくなります。

2. 読み取りよりも書き込みが多いビジネスの場合、ページが書き込まれた直後にアクセスされる可能性は極めて低いため、この時点で変更バッファの使用が最も効果的です。

• 例えば、課金システムやログシステム

3. ビジネスの更新モードの場合：書き込み後すぐにクエリが実行されます

• 更新操作は変更バッファに記録されますが、クエリの直後にデータ ページがディスクから読み取られ、マージ プロセスがトリガーされます。
• ランダム読み取りが削減される代わりに、変更バッファを維持するためのコストが増加します。

更新プロセス

挿入(4,400)

対象ページはメモリ内にあります

• 一意のインデックスについては、3と5の間の位置を見つけ、競合がないかどうかを判断し、この値を挿入します。
• 通常のインデックスの場合は、3と5の間の位置を見つけてこの値を挿入します
• パフォーマンスの違い: 最小

対象ページがメモリ内にありません

1. ユニークインデックスの場合、データページをメモリに読み込み、競合があるかどうかを判断し、値を挿入する必要があります。

• ランダムディスク読み取りは非常にコストがかかる

共通インデックスの場合は、変更バッファに更新操作を記録するだけです。

• ランダムディスク読み取りが削減され、パフォーマンスが大幅に向上しました

インデックスの選択

1. 共通インデックスとユニークインデックスのクエリパフォーマンスに大きな違いはありません。主な考慮事項は更新パフォーマンスです。共通インデックスを選択することをお勧めします。

2. 変更バッファを閉じることが推奨されるシナリオ

• すべての更新の後にこのレコードのクエリが続く場合
• 制御パラメータ innodb_change_buffering

mysql> '%innodb_change_buffering%' のような変数を表示します。
+-------------------------+-------+
| 変数名 | 値 |
+-------------------------+-------+
| innodb_change_buffering | すべて |
+-------------------------+-------+

# 有効な値（>= 5.5.4）
なし / 挿入 / 削除 / 変更 / 削除 / すべて

# 有効な値（<= 5.5.3）
なし / 挿入

# 変更バッファの前身は挿入バッファであり、挿入操作のみを最適化できます。

バッファの変更 + redolog

更新プロセス

k-treeの現在の状態: 対応する場所を見つけた後、k1が配置されているデータページページ1はメモリ内にあり、k2が配置されているデータページページ2はメモリ内にありません。

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

# メモリ: バッファプール
# 再ログ:ib_logfileX
# データテーブルスペース: t.ibd
# システムテーブルスペース: ibdata1

1. ページ1はメモリ内にあるので、メモリを直接更新します

2. ページ2がメモリにない場合は、チェンジャーバッファに記録します。ページ2に(id2,k2)を追加します。

3. 上記の2つのアクションは、REDOLOG（ディスクシーケンシャル書き込み）に含まれます。

4. トランザクションは完了し、更新ステートメントの実行コストは非常に低くなります。

• メモリに2回書き込み、ディスクに1回書き込み

5. トランザクションがコミットされると、変更バッファ操作レコードもREDOログに記録されます。

• したがって、クラッシュ回復中に変更バッファを復元できます。

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

読み取りプロセス

前提: 読み取りステートメントは更新ステートメントの直後に実行され、メモリ内のデータはまだ存在し、システム テーブルスペース (ibdata1) および redolog (ib_logfileX) とは関係ありません。

SELECT * FROM t WHERE k IN (k1,k2); 

1. ページ 1 を読み取り、メモリから直接返します (この時点では、ページ 1 はまだダーティ ページであり、実際にはディスクに書き込まれていない可能性があります)

2. ページ2を読み取り、ランダムディスク読み取りを通じてデータページをメモリに読み込み、変更バッファ内の操作ログを適用します（マージ）

• 正しいバージョンを生成して返す

更新パフォーマンスの向上

1. redolog: ランダムディスク書き込みIO消費を節約します（シーケンシャル書き込み）

2. バッファの変更: ランダムディスク読み取りのIO消費を節約します

参考文献

「MySQL実践45講義」

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。