MySql のインデックス、ロック、トランザクションの知識ポイントのまとめ

この記事では、MySql のインデックス、ロック、トランザクションに関する知識のポイントをまとめています。ご参考までに、詳細は以下の通りです。

1. 索引

索引は本の目次に似ており、目次のページ番号に基づいて対応するレコードをすぐに見つけることができます。

インデックスの利点:

1. 自然なソート。
2. クイック検索。

インデックス作成の欠点:

1. スペースを占有します。
2. テーブルの更新速度を遅くします。

注: 小さなテーブルの場合はフル テーブル スキャンを使用する方が高速であり、中規模および大規模のテーブルの場合はインデックスが使用されます。非常に大きなテーブルインデックスは基本的に効果がありません。

実装の観点から、インデックスはクラスター化インデックスと補助インデックス（セカンダリインデックスまたは非クラスター化インデックスとも呼ばれる）の2つのタイプに分けられます。

機能的には、通常のインデックス、ユニークインデックス、主キーインデックス、複合インデックス、外部キーインデックス、フルテキストインデックスの 6 種類があります。

6 種類のインデックスについて詳しく説明します。

1. 通常のインデックス: 制約のない最も基本的なインデックス。
2. ユニーク インデックス: 通常のインデックスに似ていますが、一意性の制約があります。
3. 主キー インデックス: null 値を許可しない特別な一意のインデックス。
4. 複合インデックス: 複数の列を組み合わせてインデックスを作成し、複数の列をカバーできます。
5. 外部キー インデックス: データの一貫性と整合性を確保し、カスケード操作を実装するために外部キー インデックスを使用できるのは、InnoDB タイプのテーブルのみです。
6. 全文インデックス：MySQLに付属する全文インデックスはInnoDBとMyISAMのみ使用可能で、英語のみの全文検索が可能です。一般的には全文インデックスエンジン（ES、Solr）を使用します。

注: 主キーは一意のインデックスですが、一意のインデックスは必ずしも主キーであるとは限りません。一意のインデックスは空にすることができますが、空の値は 1 つしか存在できず、主キーは空にできません。

さらに、InnoDB はデータを主キーでクラスター化します。主キーとクラスター化インデックスが定義されていない場合、MySql は代わりに一意の空でないインデックスを選択します。そのようなインデックスがない場合、6 バイトの主キーがクラスター化インデックスとして暗黙的に定義されますが、ユーザーはこれを表示したりアクセスしたりすることはできません。

簡単に言えば:

1. 主キーを設定すると、一意のインデックスが自動的に生成されます。以前にクラスター化インデックスが存在しない場合は、クラスター化インデックスが主キーになります。
2. 主キーが設定されていない場合は、空でない一意のインデックスがクラスター化インデックスとして選択されます。 何もない場合は、暗黙的な 6 バイトのインデックスが生成されます。

MySql はデータをページに保存し、デフォルトのページ サイズは 16kb です。クエリを実行すると、特定のデータが読み込まれるだけでなく、そのデータが配置されているすべてのページが pageCache に読み込まれます。これは実際には、OS の近接アクセスの原則に似ています。

MySql のインデックスは B+ ツリー構造を使用します。 B+ ツリーについて説明する前に、まず B ツリーについて説明します。B ツリーは、多方向バランス検索ツリーです。通常のバイナリ ツリーと比較して、極端に不均衡になることはなく、多方向でもあります。

B ツリーの特徴は、ページ以外の子ノードにもデータを格納することです。

写真からわかるように:

この機能は、ページ以外の子ノードが大量のインデックスを保存できないことを意味します。

B+ ツリーは、この目的のために B ツリーを最適化します。次の図に示すように:

B+ ツリーではすべてのデータがリーフ ノードに格納され、子以外のノードにはインデックスとポインターのみが格納されていることがわかります。

非ページ子ノードは 16kb、各インデックス、つまり主キーは bigint、つまり 8b、ポインターは 8b であると想定します。すると、各ページには約 1000 個のインデックス (16kb/8b + 8b) を保存できます。

3 層 B+ ツリーはいくつのインデックスを保存できますか?以下のように表示されます。

約 10 億個のインデックスを保存できます。通常、B+ ツリーの高さは 2 ～ 4 層です。MySQL の実行中はルート ノードがメモリ内に常駐するため、各検索に必要な IO は 2 ～ 3 回程度です。 B+ツリーの設計は、機械ディスクの特性に基づいていると言えます。

インデックスの設計がわかれば、他の情報もわかります。

1. MySql の主キーは大きすぎることはできません。UUID を使用すると、B+ ツリーの非リーフ ノードが無駄になります。
2. MySql の主キーは自動増分が望ましいです。UUID を使用すると、挿入ごとに B+ ツリーが調整され、ページ分割が発生してパフォーマンスに重大な影響を及ぼします。

したがって、プロジェクトでシャーディングを使用する場合、通常はシャーディング用の主キーが必要になりますが、どうすればよいでしょうか?実装の面では、自動インクリメント主キーを保持し、論理主キーを一意のインデックスとして使用することができます。

2. ロック機構

MySQL のロックに関しては、さまざまな概念が出てきます。実際、ロックにはいくつかの側面があります。説明しましょう。

1. タイプディメンション

• 共有ロック（読み取りロック/Sロック）
• 排他ロック（書き込みロック/Xロック）

タイプ別内訳:

• 意図共有ロック

• 意図的排他ロック

• 悲観的ロック（更新用にロックを使用する）
• 楽観的ロック（CAS メカニズムに似たバージョン番号フィールドを使用し、つまり、ユーザー自身が制御します。欠点: 同時実行性が非常に高い場合、無駄な再試行が多くなります）

2. ロックの粒度（粒度ディメンション）

• テーブルロック
• ページ ロック (MySQL BerkeleyDB エンジン)
• 行ロック (InnoDB)

3. ロックアルゴリズム（アルゴリズムディメンション）

• レコードロック（単一行レコード）
• ギャップ ロック (範囲をロックしますが、ロックされたレコードは含まれません)
• ネクストキー ロック (レコード ロック + ギャップ ロック、範囲をロックし、レコード自体をロックします。MySql はこのロックを使用してファントム リードを防止します)

4. デフォルトの読み取り操作はロックされていますか?

• デフォルトでは、MVCC メカニズム (「一貫性のある非ロック読み取り」) によって RR レベルの分離の正確性が保証され、ロックされません。

手動でロックすることを選択できます: 更新の場合は xxxx を選択します (排他ロック)。共有モードで xxxx ロックを選択します (共有ロック)。これは「一貫性のあるロック読み取り」と呼ばれます。

ロックを使用した後、RR レベルでファントム リードが回避されます。もちろん、デフォルトの MVCC 読み取りでもファントム読み取りを回避できます。

RR はファントム リードを防止できるので、SERIALIZABLE は何の役に立つのでしょうか?

更新の損失を防ぎます。たとえば、次の図:

このとき、シリアル読み取りには SERIALIZABLE レベルを使用する必要があります。

最後に、行ロックの実装原則は、クラスター化インデックスをロックすることです。クエリ時にインデックスに正しくヒットしない場合、MySql オプティマイザは行ロックを放棄し、テーブル ロックを使用します。

3. 取引

トランザクションは、データベースにおける永遠のテーマであり、ACID (原子性、一貫性、独立性、永続性) です。

4 つの特性のうち、最も重要なのは一貫性です。一貫性は、原子性、独立性、永続性によって保証されます。

• アトミック性は Undo ログによって保証されます。元に戻すログは、各変更前のレコードを保存するため、エラーが発生したときにロールバックできます。
• 分離は MVCC とロックによって保証されます。これについては後で話します。
• Redo ログによって耐久性が保証されます。実際にデータを変更する前に、毎回レコードが Redo ログに書き込まれます。Redo ログが正常に書き込まれた場合にのみ、B+ ツリーに実際に書き込まれます。送信前に停電が発生した場合は、Redo ログを通じてレコードを復元できます。

それでは、隔離について話しましょう。

分離レベル:

1. コミットされていない読み取り (RU)
2. 読み取りコミット (RC)
3. 繰り返し読み取り (RR)
4. シリアル化可能

各レベルは異なる問題を解決しますが、通常はダーティ リード、反復不可能なリード、ファントム リードの 3 つの問題を解決します。古典的な写真：

ここで、ファントム リードについて注意すべき点があります。データベースの仕様では、RR レベルではファントム リードが発生します。ただし、MySQL の最適化により、MySQL の RR レベルではファントム リードは発生しません。デフォルトの選択を使用する場合、MySQL は MVCC メカニズムを使用して、ファントム リードが発生しないことを保証します。ロックを使用することもできます。更新 (X ロック)、共有モードでのロック (S ロック) などのロックを使用する場合、MySQL は Next-Key Lock を使用して、ファントム リードが発生しないことを保証します。前者はスナップショット読み取りと呼ばれ、後者は現在の読み取りと呼ばれます。

原則分析:

• RU ダーティ リードが発生する理由: RU の原則では、トランザクション全体をロックするのではなく、各更新ステートメントの行レコードをロックするため、ダーティ リードが発生します。 RC と RR はトランザクション全体をロックします。
• RC が繰り返し読み取れない理由: RC は SQL ステートメントを実行するたびに新しい読み取りビューを生成し、読み取られる内容は毎回異なります。 RR トランザクションは最初から最後まで同じ読み取りビューを使用します。
• RR にファントム リードがない理由は上記のとおりです。

では、RR と Serializble の違いは何でしょうか? A: 更新が失われました。この記事のロック部分について言及しました。

MVCC の概要: 正式名称はマルチバージョン同時実行制御です。

innoDB の各クラスター化インデックスには、主キー (RowID)、最新の変更のトランザクション ID (MVCC コア)、Undo ログのポインター (分離コア)、およびインデックス削除マーク (削除時にすぐに削除されるのではなく、マークされてから非同期的に削除されます) の 4 つの隠しフィールドがあります。

基本的に、MVCC は Undo Log リンク リストを使用して実装されます。

MVCC 実装方法: トランザクションは排他ロックで元のデータを変更し、変更前のデータを Undo ログに保存し、そのデータをロールバック ポインターに関連付けます。変更が成功した場合は何も行われません。変更が失敗した場合は、Undo ログのデータが復元されます。

もう 1 つ、通常、MVCC はバージョン番号を使用するという点で楽観的ロックに似ていると考えられていますが、実際には InnoDB はこのように実装されていません。もちろん、これは MySql の使用には影響しません。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL インデックス操作スキルの概要」、「MySQL 共通関数の概要」、「MySQL ログ操作スキルの概要」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアド プロシージャ スキルの概要」、および「MySQL データベース ロック関連スキルの概要」。

この記事が皆様のMySQLデータベース設計に役立つことを願っています。