MYSQL_マルチバージョン同時実行制御、ストレージ エンジン、インデックス作成の概要

マルチバージョン同時実行制御

mysql のほとんどのトランザクション ストレージ エンジンは、単純な行レベルのロックを実装していません。同時実行パフォーマンスの向上を考慮して、複数バージョンの同時実行制御を同時に実装するのが一般的です。

MVCC は行レベルのロックの一種と考えることができますが、オーバーヘッドが低いため、多くの場合、ロック操作が回避されます。

InnoDB の MVCC は、各行レコードの最後に保存される 2 つの非表示列によって実装されます。これらの 2 つの列のうち、1 つは行の作成時刻を保存し、もう 1 つは行の有効期限 (または削除時刻) を保存します。もちろん、ストレージは実際の時間値ではなく、システムのバージョンです。新しいトランザクションが開始されるたびに、システムのバージョン番号が自動的に増加します。トランザクション開始時のシステム バージョン番号はトランザクションのバージョン番号として使用され、クエリされた各行のバージョン番号を比較するために使用されます。

REPEATABLE READ 分離レベル、MVCC の実装:

• SELECT

• InnoDB は、現在のトランザクションのバージョン番号より前のバージョンのデータ行を検索します。これにより、トランザクションによって読み取られる行が、トランザクションの開始 以前にすでに存在していたか、トランザクション自体によって挿入または変更されました。

• 行の削除されたバージョンは未定義であるか、現在のトランザクションのバージョン番号より大きいです。これにより、トランザクションによって読み取られた行がトランザクションの開始前に削除されていないことが保証されます。

• INSERT

• InnoDB は、新しく挿入された各行の行バージョン番号として現在のシステム バージョン番号を保存します。

• DELETE

• InnoDB は、現在のシステムのバージョン番号を、削除された各行の行削除識別子として保存します。

• UPDATE

• InnoDB は、新しいレコードを挿入するときに現在のシステム バージョン番号を行のバージョン番号として保存し、現在のシステム バージョン番号を行削除のバージョン番号として元の行に保存します。

MVCC は、REPEATABLE READ と READ COMMITED の 2 つの分離レベルでのみ機能します。他の 2 つの分離レベルは MVCC と互換性がありません。 READ UNCOMMITED は、現在のトランザクション バージョンに準拠したデータ行ではなく、常に最新のデータ行を読み取るためです。 SERIALIZABLE は、読み取られたデータのすべての行をロックします。

ストレージ エンジン

InnoDB ストレージ エンジン

InnoDB は、MYSQL のデフォルトのトランザクション エンジンであり、最も重要で広く使用されているストレージ エンジンでもあります。別のストレージ エンジンを使用する特別な理由がない限り、InnoDB エンジンを優先する必要があります。

InnoDB は MVCC を使用して高い同時実行性をサポートし、4 つの標準分離レベルを実装します。デフォルトのレベルは REPEATABLE READ で、ギャップ ロック + MVCC 戦略によりファントム読み取りが防止されます。ギャップ ロックにより、InnoDB はクエリ設計の行をロックするだけでなく、インデックス内のギャップをロックしてファントム行の挿入を防止できます。

ギャップ ロック: 等価条件ではなく範囲条件を使用してデータを取得し、共有ロックまたは排他ロックをリクエストすると、InnoDB は条件範囲内にあるキー値の条件を満たす既存のデータ レコードのインデックス エントリをロックします。存在しないレコードは「ギャップ」と呼ばれます。このロック機構は、いわゆるギャップ ロック (Next-Key ロック) です。
参考: ギャップ ロック (ネクスト キー ロック)

メイン インデックスはクラスター化インデックスであり、ディスクの直接読み取りを避けるためにデータをインデックスに保存するため、クエリのパフォーマンスが大幅に向上します。

InnoDB は、ディスクからデータを読み取る際の予測可能な先読み、メモリ内にハッシュ インデックスを自動的に作成して操作を高速化できる適応型ハッシュ インデックス、挿入操作を高速化できる挿入バッファなど、多くの内部最適化を行いました。等

MyISAM ストレージ エンジン

mysql5.1 以前のバージョンでは、MyISAM がデフォルトのストレージ エンジンです。 MyISAM は、全文インデックス作成、圧縮、空間関数などを含む多数の機能を提供しますが、トランザクションや行レベルのロックはサポートしておらず、クラッシュ後に安全に回復できないことは間違いありません。

読み取り専用データの場合、またはテーブルが比較的小さく修復操作に耐えられる場合は、MyISAM エンジンを引き続き使用できます。

MyISAM テーブルを作成するときに DELAY_KEY_WRITE オプションが指定されている場合、各変更が完了すると、変更されたインデックス データはすぐにディスクに書き込まれず、メモリ内のキー バッファーに書き込まれます。 key 対応するインデックス ブロックは、バッファが閉じられている場合、またはテーブルが閉じられている場合にのみディスクに書き込まれます。この方法では書き込みパフォーマンスを大幅に向上させることができますが、データベースまたはホストがクラッシュしたときにインデックスが損傷し、修復操作が必要になります。

比較

• トランザクション: InnoDB はトランザクションをサポートしますが、MyISAM はトランザクションをサポートしません。

• ロックの粒度: InnoDB はテーブルレベルのロックと行レベルのロックをサポートしますが、MyISAM はテーブルレベルのロックのみをサポートします。

• 外部キー: InnoDB は外部キーをサポートしています。

• バックアップ: InnoDB はホット バックアップをサポートしていますが、ツールが必要です。

• クラッシュ回復: MyISAM がクラッシュした後の損傷の確率は InnoDB よりもはるかに高く、回復速度も遅くなります。

• その他の機能: MyISAM は、全文インデックス作成、圧縮、空間関数、その他の機能をサポートしています。

バックアップの種類

• コールドバックアップ: mysqlサービスをオフにする必要があり、読み取りおよび書き込みリクエストは許可されません。

• ウォームバックアップ: サービスはオンラインですが、読み取りリクエストのみがサポートされます。書き込みリクエストは許可されません。

• ホットバックアップ: バックアップ中、ビジネスには影響しません。

インデックス

インデックス (「キー」とも呼ばれます) は、ストレージ エンジンがレコードを迅速に検索するために使用するデータ構造です。

B ツリー インデックス

ほとんどの mysql エンジンはこのインデックスをサポートしています。

「B-Tree」という用語が使用されていますが、NDB クラスター ストレージ エンジンは実際に内部で T-Tree を使用するのに対し、異なるストレージ エンジンは異なるストレージ構造を使用する場合があります。一方、InnoDB は B+Tree を使用します。

B ツリー インデックスは、ストレージ エンジンが必要なデータを取得するためにテーブル全体のスキャンを実行する必要がなく、代わりにインデックスのルート ノードから検索を開始するため、データへのアクセスを高速化できます。はるかに速くなります。

B-Tree はインデックス列を順番に編成して保存するため、範囲データの検索に非常に適しています。インデックス ツリーは順序付けされているため、ユーザー検索に加えて、並べ替えやグループ化にも使用できます。

複数の列をインデックス列として指定でき、複数のインデックス列がまとめてインデックス キーを形成します。 B ツリー インデックスは、完全なキー値、キー値の範囲、またはキー プレフィックス検索に適しています。キー プレフィックス検索は、左端のプレフィックスに基づく検索にのみ適用されます。検索はインデックスの左端の列から開始する必要があります。

B-Treeインデックスのデータ構造

B-Tree

B-Treeを記述するには、まずデータレコードをタプル[key, data]として定義し、keyをレコードのキー値とします、データ レコードが異なると、キーは互いに異なります。データは、キーを除いたデータ レコード内のデータです。

• すべてのノードの深さは同じです。これは、B ツリーのバランスが取れていることを意味します。

• ノード内のキーは、左から右に非減少的に配置されます。

• ポインタの左と右に隣接するキーがそれぞれ keyi と keyi+1 であり、null でない場合、ポインタが指すノードのすべてのキーは keyi 以上であり、または以下です。 keyi+1 に等しい。

検索アルゴリズム: 最初にルートノードで二分探索を実行し、見つかった場合は、対応するノードのデータを返します。それ以外の場合は、対応する間隔でポインタが指すノードを再帰的に検索します。

新しいデータレコードの挿入と削除はB-Treeのプロパティを破壊するため、挿入と削除の際には、B-Treeのプロパティを維持するためにツリーの分割、結合、回転などを行う必要があります。

B+Tree

B-Treeと比較して、B+Treeには次の特徴があります:

• 各ノードのポインタの上限は2d+1ではなく2dです(dはBです) -ツリーの支出)。

• 内部ノードはデータを保存せず、外部ノードはポインターを保存しないだけです。

順次アクセスポインタを備えたB+ツリー

データベースシステムやファイルシステムで一般的に使用されるB+ツリー構造は、古典的なB+ツリーに基づいて最適化され、順序アクセスポインタが追加されています。

この最適化の目的は、間隔アクセスのパフォーマンスを提供することです。たとえば、図では、18 から 49 までのキーを持つすべてのレコードをクエリする場合です。

利点

赤黒ツリーなどのバランスツリーもインデックスの実装に使用できますが、ファイルシステムとデータベースシステムは通常、主に次の 2 つの理由からインデックス構造として B ツリーを使用します。取得時間の向上: バランスの取れたツリーでデータを取得する時間計算量はツリーの高さ h に等しく、ツリーの高さはおよそ O(h) = O(logN) です。ここで、d は各ノードの出次数です。赤黒ツリーの出次数は 2 ですが、B ツリーの出次数は一般に非常に大きく、赤黒ツリーの木の高さ h は明らかに B ツリーの高さよりもはるかに高くなります。そのため検索数も多くなります。 B+Tree は、B-Tree よりも外部メモリのインデックスに適しています。これは、B+Tree のノードからデータ フィールドが削除されているため、より大きな出次数を持つことができ、検索効率が高くなるためです。

• コンピューターの先読み機能の使用: ディスク I/O を削減するために、ディスクは多くの場合、厳密にオンデマンドで読み取られるのではなく、毎回先読みされます。この理論的根拠は、コンピュータ サイエンスにおけるよく知られた局所性原理です。つまり、データの一部が使用されると、通常は近くのデータがすぐに使用されます。先読みプロセスでは、ディスクはシーケンシャルに読み取られ、ディスクのシークは必要なく、回転時間も短いため、速度は非常に高速です。オペレーティング システムは通常、メモリとディスクを固体サイズのブロックに分割し、各ブロックはページと呼ばれ、メモリとディスクはページ単位でデータを交換します。データベース システムは、インデックス内のノードのサイズをページのサイズに設定するため、ノードは 1 回の I/O で完全にロードでき、また、先読み機能を使用して隣接するノードをプリロードすることもできます。

参考: MySQL インデックスの背後にあるデータ構造とアルゴリズムの原則

ハッシュ インデックス

InnoDB エンジンには、特定のインデックス値が非常に頻繁に使用される場合、ハッシュ インデックスが作成されます。これにより、B+Tree インデックスには高速ハッシュ検索などのハッシュ インデックスの利点がいくつか追加されます。

ハッシュインデックスはO(1)時間で検索できますが、順序性が失われます:

• ハッシュインデックスにはハッシュ値と行ポインタのみが含まれますが、フィールド値は格納されません。すべての行の損失を避けるためにインデックスの値を使用することはできません。

• は並べ替えやグループ化には使用できません。

• 正確な検索のみをサポートしており、部分検索や範囲検索には使用できません。

• ハッシュの競合が発生した場合、ストレージ エンジンはリンク リスト内のすべての行ポインターを走査する必要があります。

空間データインデックス (R-ツリー)

MyISAM テーブルは空間インデックスをサポートしており、地理データ ストレージとして使用できます。空間インデックスはすべてのディメンションからデータにインデックスを付け、クエリは任意のディメンションに基づいて組み合わせることができます。

データを維持するには、MBRONTAINS() などの Mysql の GIS 関連関数を使用する必要があります。

全文インデックス

全文インデックスは、インデックス内の値を直接比較するのではなく、テキスト内のキーワードを検索する特別なタイプのインデックスです。検索条件には、単純な WHERE の代わりに MATCH AGAINST が使用されます。

全文インデックスは通常、逆ソートインデックスを使用して実装され、キーワードの有効期限が切れるドキュメントのマッピングを記録します。

MyISAM ストレージ エンジンはフルテキスト インデックス作成をサポートし、InnoDB ストレージ エンジンも Mysql 5.6.4 バージョンでフルテキスト インデックス作成をサポートします。

インデックス

• の利点により、サーバーがスキャンする必要があるデータ行の数が大幅に削減されます。

• サーバーがソートや一時テーブルの作成を回避できるようにします (B+Tree インデックスは順序付けされており、Order by 操作や group by 操作に使用できます)。

• ランダム I/O をシーケンシャル I/O に変換します (B+ツリー インデックスは順序付けされます。つまり、隣接するデータは一緒に格納されます)。

関連記事:

MySQL データベース InnoDB ストレージ エンジン マルチバージョン コントロール (MVCC) 実装原理分析

MySQL ストレージ エンジンの紹介

以上がMYSQL_マルチバージョン同時実行制御、ストレージ エンジン、インデックス作成の概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。