MySQLの通常インデックスとユニークインデックスの違いの詳しい説明

1 概念上の区別

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

通常のインデックスは繰り返すことができますが、主キーなどの一意のインデックスは繰り返すことができません。 一意のインデックスは、データを検証する手段として使用できます。たとえば、学生テーブルの ID カード番号フィールドでは、このフィールドが繰り返されないように意図的に規定すると、一意のインデックスが使用されます。 （通常は学生IDフィールドが主キーとして設定されます）

• 主キーと一意のインデックス
  

主キーは、ID カードや学生 ID など、テーブル内で一意かつ重複しないデータベース内のすべての行が一意であることを保証します。一意のインデックスの役割は主キーの役割と同じです。 違いは、テーブルには主キーが 1 つしか存在できず、主キーを空にできないことです。一意のインデックスは複数存在でき、一意のインデックスには 1 つの空のレコードを含めることができます。つまり、他のレコードと異なる必要があるだけです。 たとえば、学生テーブルでは、学校では通常、学生 ID 番号を主キーとして使用し、ID カードを一意のインデックスとして使用しますが、教育局の場合は、ID カード番号を主キーにし、学生 ID 番号を一意のインデックスにします。 テーブルの主キーの選択は実際のアプリケーションによって異なり、主キーを空にすることはできません。

2 事例紹介

ある住民システムでは、各人が固有のID番号を持っています。システムが ID 番号で名前を検索する必要がある場合、次のような SQL を実行します。

id_card = 'ooxx' の場合、CUser から名前を選択します。

次に、id_card フィールドにインデックスを作成します。ただし、id_card フィールドは大きいため、主キーとして使用することはお勧めしません。したがって、選択肢は 2 つあります。

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

ビジネス コードによって重複した ID 番号が書き込まれないことが保証されていると仮定すると、どちらのオプションも論理的には正しいことになります。しかし、パフォーマンスの観点から見ると、ユニーク インデックスと通常のインデックスのどちらが良いでしょうか?

次のケースを見てみましょう。フィールド k の値は繰り返されないと仮定します。

• InnoDB インデックス構成構造:

次に、パフォーマンスを分析します。

3 クエリパフォーマンス

k=4 の T から ID を選択

B+ツリーをルートからリーフノードまで順番にトラバースすることで、バイナリ検索を通じてデータページが検索されると考えられます。

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

パフォーマンスの差は非常に小さいようです。

InnoDB データはデータ ページ単位で読み書きされます。つまり、レコードを読み取るときは、ディスクから読み取るのではなく、ページ単位でメモリ全体に読み込まれます。

したがって、通常のインデックスの場合、「次のレコードを見つけて決定する」というもう 1 つの操作、つまり 1 つのポインタ検索と 1 つの計算が必要になります。 k=4 レコードがデータ ページの最後のレコードである場合、次のレコードを取得するには、次のデータ ページを読み取る必要がありますが、これは少し複雑な操作です。 整数フィールドの場合、データ ページには 1,000 近くのキーを保存できるため、このような状況が発生する可能性は実際には非常に低くなります。したがって、平均パフォーマンスの差を計算する場合、この操作のコストは現在の CPU オーバーヘッドと比較して無視できるほど小さいと見なすことができます。

MySQL には変更バッファがあることはわかっています。

4 アップデートのパフォーマンス

ここで、テーブルに新しいレコード (4,400) を挿入してみましょう。InnoDB は何を行うでしょうか?

レコードによって更新される対象ページがメモリ内にあるかどうかを区別する必要があります。

4.1 記憶の中で

• ユニークインデックス
  

3 と 5 の間の位置を見つけ、競合がないことを確認し、値を挿入すると、ステートメントの実行が終了します。

• 通常のインデックス
  

3 と 5 の間の位置を見つけて値を挿入すると、ステートメントの実行が終了します。

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

4.2 メモリにない

• ユニークインデックス
  

データ ページをメモリに読み込む必要があり、競合がないと判断された場合は値が挿入され、ステートメントの実行が終了します。

• 通常のインデックス
  

更新は変更バッファに記録され、ステートメントの実行が終了します。

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

5 実際のインデックス選択

通常のインデックスとユニークインデックスのどちらを選択すればよいですか?クエリ パフォーマンスの点では、これら 2 種類のインデックスに違いはなく、主な考慮事項は更新パフォーマンスへの影響です。したがって、可能な限り共通のインデックスを選択することをお勧めします。

すべての更新の後に同じレコードのクエリが続く場合は、変更バッファを閉じる必要があります。 その他の場合では、変更バッファによって更新パフォーマンスが向上することがあります。 通常のインデックスと変更バッファの組み合わせは、大量のデータを持つテーブルの更新を最適化するのに非常に効果的です。

機械式ハードドライブを使用する場合、変更バッファメカニズムは非常に効果的です。 したがって、「履歴データ」のようなライブラリがあり、コストを考慮して機械式ハードディスクを使用する場合は、これらのテーブル内のインデックスに注意を払い、通常のインデックスを使用するようにし、変更バッファを増やし、「履歴データ」テーブルのデータ書き込み速度を確保する必要があります。

6 バッファとREDOログの変更

パフォーマンスを向上させる WAL の中心的なメカニズムは、ランダムな読み取りと書き込みを最小限に抑えることです。これら 2 つの概念は混同されやすいです。 そこで、ここでは違いを説明するために、これらを同じプロセスに置きます。

6.1 挿入プロセス

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

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

• 変更バッファを使用した更新フローチャート。図の 2 つの矢印はバックグラウンド操作であり、更新応答には影響しません。

このアップデートでは次のことが行われます。

1. ページ1はメモリ内にあるため、メモリを直接更新します
2. Page2はメモリではなく変更バッファにあり、「Page2にレコードの行を挿入する」という情報をキャッシュしています。
3. 最初の2つのアクションをREDOログに記録する
   

その後取引は完了します。この更新ステートメントを実行するコストは非常に低く、2 つのメモリに書き込み、次に 1 つのディスクに書き込むだけ (最初の 2 つの操作を組み合わせると 1 つのディスクに書き込みます) で、順番に書き込まれます。

6.2 後続の読み取り要求をどのように処理しますか?

k が (k1, k2) の範囲内にある t から * を選択します。

更新ステートメントの後に読み取りステートメントが続き、メモリ内のデータはまだそこにあります。この時点では、これらの 2 つの読み取り操作は、システム テーブルスペースと REDO ログとは関係ありません。なのでこの二人は絵には描きませんでした。

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

Page1 を読み取る場合は、メモリから直接返されます。 WAL の後にデータを読み取る場合、ディスクを読み取る必要がありますか? 戻る前に、REDO ログ内のデータを更新する必要がありますか?実際、必要ありません。 上図の状態を見ると、ディスクにはまだ以前のデータが残っていますが、メモリから直接結果が返されており、結果は正しいです。

Page2 を読み取るには、Page2 をディスクからメモリに読み取り、変更バッファー内の操作ログを適用して正しいバージョンを生成し、結果を返す必要があります。 Page2 の読み取りが必要になるまで、データ ページはメモリに読み込まれないことがわかります。

したがって、これら 2 つのメカニズムが更新パフォーマンスに与える影響を単純に比較する必要があります。

• REDOログは主にディスクへのランダム書き込み（シーケンシャル書き込みへの変換）のIO消費を節約します。
• バッファの変更は主にランダムディスク読み取りのIO消費を節約します
  

7 結論

ユニーク インデックスは変更バッファー最適化メカニズムを使用できないため、ビジネスで受け入れられる場合は、パフォーマンスの観点から、ユニークでないインデックスを優先することをお勧めします。

7.1 ユニークインデックスを使用するかどうか

最大の懸念は「事業が確保できない可能性がある」ことだ。この記事では、「ビジネス コードが重複データを書き込まないことが保証されている」という前提でパフォーマンスの問題について説明します。

ビジネスでこれを保証できない場合、またはビジネスでデータベースに制約を設定する必要があるときは、一意のインデックスを作成する以外に選択肢はありません。この場合、この記事の重要性は、大量のデータがゆっくりと挿入され、メモリヒット率が低い状況に遭遇した場合に、追加のトラブルシューティングのアイデアを提供することです。
次に、一部の「アーカイブ ライブラリ」シナリオでは、一意のインデックスの使用を検討できます。たとえば、オンライン データは半年だけ保持する必要があり、履歴データはアーカイブ ライブラリに保存されます。この時点で、アーカイブされたデータには一意のキーの競合がないことが保証されます。アーカイブの効率を向上させるには、テーブルの一意のインデックスを共通インデックスに変更することを検討してください。

7.2 変更バッファが書き込みに使用され、その後ホストが異常再起動した場合、変更バッファのデータは失われますか?

失われることはありません。 更新されるのはメモリのみですが、トランザクションがコミットされたときに変更バッファ操作も REDO ログに記録されるため、クラッシュ回復時に変更バッファを取得することもできます。

7.3 マージ プロセスではデータが直接ディスクに書き戻されますか?

マージ実行プロセス

1. ディスクからメモリにデータページを読み込む（旧バージョンのデータページ）
2. 変更バッファからデータページの変更バッファレコードを見つけ（複数ある場合もあります）、それを1つずつ適用して、データページの新しいバージョンを取得します。
3. REDOログの書き込み
   

REDO ログには、データの変更と変更バッファの変更が含まれます。

マージプロセスはここで終了します。 この時点では、データ ページとメモリ内の変更バッファの対応するディスク位置は変更されておらず、ダーティ ページです。その後、それぞれが独自の物理データをフラッシュ バックしますが、これは別のプロセスです。

考えるべき質問

最初の例を構築する過程で、セッション A の協力を得て、セッション B にデータを削除してからデータを再挿入するように依頼します。すると、Explain の結果で、行フィールドが 10001 から 37000 以上に変わることがわかります。 セッション A が使用されず、t からの削除、idata() の呼び出し、および explain のみが個別に実行される場合、行フィールドがまだ約 10,000 であることがわかります。その理由は何でしょうか?

再発しない場合は、

• 分離レベルは RR (繰り返し読み取り) ですか?
• 作成されたテーブルは InnoDB エンジンですか?
  

この一連の操作の後、説明結果が間違っているのはなぜですか? delete ステートメントによってすべてのデータが削除され、その後 idata() 呼び出しを通じて 100,000 行のデータが挿入されましたが、これにより元の 100,000 行が上書きされたようです。 ただし、セッション A はトランザクションを開始しましたが、コミットしなかったため、以前に挿入された 100,000 行のデータは削除できません。このように、以前のデータの各行には 2 つのバージョンがあり、古いバージョンは削除前のデータであり、新しいバージョンは削除済みとしてマークされたデータです。 このように、インデックス a には実際にはデータのコピーが 2 つ存在します。

すると、それは正しくない、主キーのデータは削除できない、と言うかもしれません。では、force index ステートメントがないのに、explain コマンドでスキャンされた行数がなぜ 100,000 前後のままなのでしょうか。 (これも 2 倍になると、おそらくオプティマイザはフィールド a をインデックスとして選択する方が適切であると判断するでしょう) はい、ただしこれは主キーであり、主キーはテーブル内の行数に応じて直接推定されます。テーブル内の行数に関しては、オプティマイザはshow table statusの値を直接使用します。 マシンの IO 機能が貧弱な場合は、この検証を行うときにinnodb_flush_log_at_trx_commitとsync_binlogを 0 に設定できます。

上記は、MySQL 共通インデックスとユニークインデックスの違いについての詳細な説明です。MySQL 共通インデックスとユニークインデックスの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。