MySQL アーキテクチャ-mysql チュートリアル-php.cn

MySQL アーキテクチャと「ハイパフォーマンスMySQL」の歴史の最初の章を確認してください

1.1 MySQL 論理アーキテクチャ

リファレンス

図 1-1: MySQL サーバーの論理アーキテクチャ図

トップレベルサービスは MySQL に固有のものではありません。ほとんどの Web ベースのクライアント/サーバーツールまたはサービスは同様のアーキテクチャを持っています。接続処理、認可認証、セキュリティなど。

第 2 層のアーキテクチャは、MySQL のより興味深い部分です。 MySQL のコアサービス関数のほとんどは、クエリ解析、分析、最適化、キャッシュ、およびすべての組み込み関数 (日付、時刻、計算、暗号化関数など) を含む、この層にあります。レイヤー。レイヤーの実装: ストアドプロシージャ、トリガー、ビューなど。

3番目の層にはストレージエンジンが含まれています。ストレージエンジンは、MySQL でのデータの保存と取得を担当します。 GNU/Linux のさまざまなファイルシステムと同様、各ストレージエンジンには長所と短所があります。サーバーは API を介してストレージエンジンと通信します。これらのインターフェイスは、異なるストレージエンジン間の違いを保護し、上位層のクエリプロセスに対してこれらの違いを透過的にします。ストレージエンジン API には、「トランザクションの開始」や「主キーに基づいたレコード行の抽出」などの操作を実行するための低レベル関数が多数含まれています。ただし、ストレージエンジンは SQL を解析せず、異なるストレージエンジンは相互に通信せず、上位サーバーのリクエストに応答するだけです。

1.2 同時実行制御

1.2.1 読み取り/書き込みロック

これら 2 種類のロックは、通常、共有ロックおよび排他ロックと呼ばれ、読み取りロックおよび書き込みロック (書き込みロック) とも呼ばれます。読み取りロックは共有、つまり非ブロッキングです。複数のクライアントは、相互に干渉することなく、同時に同じリソースを読み取ることができます。書き込みロックは排他的です。つまり、書き込みロックは他の書き込みロックと読み取りロックをブロックします。

1.2.2 ロック粒度

2つの最も重要なロック戦略: テーブルロックと行レベルロック

テーブルロック(テーブルロック)

テーブルロックはMySQLの最も基本的なロック戦略であり、最も低コストの戦略です。テーブル全体がロックされます。ユーザーがテーブルに対して書き込み操作 (挿入、削除、更新など) を実行するには、書き込みロックを取得する必要があります。これにより、他のユーザーによるテーブルに対するすべての読み取りおよび書き込み操作がブロックされます。書き込みロックがない場合にのみ、他の読み取りユーザーが読み取りロックを取得でき、読み取りロックは相互にブロックしません。

特定のシナリオでは、テーブルロックも優れたパフォーマンスを発揮する可能性があります。たとえば、READ LOCAL テーブルロックは、特定の種類の同時書き込み操作をサポートします。さらに、書き込みロックは読み取りロックよりも優先度が高いため、書き込みロック要求は読み取りロックキューの前に挿入される場合があります (書き込みロックはロックキュー内の読み取りロックの前に挿入できますが、読み取りロックは読み取りロックキューの前に挿入される可能性があります)。ロックは挿入できません) を書き込みロックの前に挿入します)。

行レベルのロック（行ロック）

行レベルのロックは、同時処理を最大限にサポートできます（ロックのオーバーヘッドも最大になります）。ご存知のとおり、行レベルのロックは InnoDB と XtraDB、およびその他のストレージエンジンに実装されています。行レベルのロックはストレージエンジン層でのみ実装され、MySQL サーバー層では実装されません。サーバー層は、ストレージエンジンのロック実装についての情報を持ちません。

1.3 トランザクション

トランザクションは ACID 原則をサポートします。

原子性

トランザクションは、分割できない最小の作業単位と見なされなければなりません。

一貫性

データベースは常に、ある一貫した状態から別の一貫した状態に遷移します。

分離

通常、あるトランザクションによって行われた変更は、最終的にコミットされるまで他のトランザクションには表示されません。

耐久性

トランザクションがコミットされると、その変更はデータベースに永続的に保存されます。

1.3.1 分離レベル

以下は 4 つの分離レベルの簡単な紹介です。

READ UNCOMMITTED (コミットされていない読み取り)

READ UNCOMMITTED レベルでは、トランザクション内の変更は、コミットされていない場合でも他のトランザクションに表示されます。トランザクションはコミットされていないデータを読み取ることができます。これはダーティリードとも呼ばれます。このレベルは、パフォーマンスの点で多くの問題を引き起こす可能性がありますが、他のレベルに比べてそれほど優れているわけではありませんが、本当に必要な理由がない限り、実際のアプリケーションで使用されることはほとんどありません。。

コミット済みを読む

ほとんどのデータベースシステムのデフォルトの分離レベルは READ COMMITTED です (MySQL は除きます)。トランザクションが最初からコミットされるまでに行われた変更は、他のトランザクションには表示されません。同じクエリを 2 回実行すると異なる結果が生じる可能性があるため、このレベルは非反復読み取りと呼ばれることもあります。

REPEATABLE READ（反復可能な読み取り）

REPEATABLE READはダーティリードの問題を解決します。このレベルでは、同じトランザクション内で同じレコードを複数回読み取った結果の一貫性が保証されます。ただし、理論的には、反復読み取り分離レベルでは、別のファントム読み取り (ファントム読み取り) の問題を解決することはできません。いわゆるファントム読み取りとは、トランザクションが特定の範囲のレコードを読み取るときに、前のトランザクションがその範囲のレコードを再度読み取るときに、別のトランザクションがその範囲に新しいレコードを挿入することを意味します。 InnoDB および XtraDB ストレージエンジンは、マルチバージョン同時実行制御 (MVCC) を通じてファントム読み取りの問題を解決します。

Repeatable Read は、MySQL のデフォルトのトランザクション分離レベルです。

SERIALIZABLE（シリアル化可能）

SERIALIZABLEは最も高い分離レベルです。トランザクションを強制的にシリアルに実行することで、前述のファントム読み取りの問題を回避します。簡単に言えば、SERIALIZABLE は取得したデータのすべての行をロックするため、タイムアウトやロック競合の問題が大量に発生する可能性があります。この分離レベルは、実際のアプリケーションではほとんど使用されません。このレベルは、データの一貫性を確保する必要があり、同時実行性が許容されない場合にのみ考慮する必要があります。

1.3.2 デッドロック

デッドロックとは、複数のトランザクションが同じリソースを占有し、占有しているリソースに対してロックを要求し合い、悪循環に陥る現象です。デッドロックは、複数のトランザクションが異なる順序でリソースをロックしようとすると発生する可能性があります。デッドロックは、複数のトランザクションが同じリソースを同時にロックした場合にも発生する可能性があります。

この問題を解決するために、データベースシステムはさまざまなデッドロック検出とデッドロックタイムアウトメカニズムを実装しています。 InnoDB ストレージエンジンなどのより複雑なシステムでは、デッドロックの循環依存関係を検出し、即座にエラーを返す能力が高くなります。このソリューションは非常に効果的ですが、そうでないとデッドロックが発生してクエリが非常に遅くなります。別の解決策は、クエリ時間がロック待機タイムアウト設定に達したときにロック要求を放棄することです。この方法は一般に適切ではありません。 InnoDB の現在のデッドロック処理方法は、最も少ない行レベルの排他ロックを保持しているトランザクションをロールバックすることです (これは比較的単純なデッドロックロールバックアルゴリズムです)。

ロックの動作と順序はストレージエンジンに関連しています。ステートメントを同じ順序で実行すると、デッドロックが発生するストレージエンジンもあれば、デッドロックが発生しないストレージエンジンもあります。デッドロックは 2 つの理由で発生します。1 つは、多くの場合回避が難しい実際のデータの競合によるものですが、完全にストレージエンジンの実装方法に起因するものもあります。

1.3.3 トランザクションログ

トランザクションログを使用すると、ストレージエンジンはテーブルデータを変更するときにメモリコピーを変更するだけで済み、ハードディスク上に永続化されたトランザクションログに変更動作を記録する必要がありません。変更されたデータ自体は毎回ディスクに保存されます。トランザクションログが追加されます。トランザクションログが永続化された後、メモリ内の変更されたデータをバックグラウンドでゆっくりとディスクにフラッシュバックできます。現在、ほとんどのストレージエンジンは、通常、先行書き込みログ (書き込み先行ログ) と呼ばれるこの方法で実装されており、データを変更するにはディスクに 2 回書き込む必要があります。

データの変更がトランザクションログに記録されて永続化されているが、データ自体がディスクに書き戻されておらず、システムがクラッシュした場合、ストレージエンジンは再起動時にこの変更されたデータを自動的に復元できます。具体的な回復方法はストレージエンジンによって異なります。

1.3.4 MySQL のトランザクション

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

MVCC の実装は、特定の時点でのデータのスナップショットを保存することによって実現されます。つまり、実行にどれだけ時間がかかっても、各トランザクションで表示されるデータは一貫しています。トランザクションの開始時刻に応じて、同じテーブル上の各トランザクションで同時に表示されるデータが異なる場合があります。以下では、InnoDB の動作の簡略化されたバージョンを通じて MVCC がどのように機能するかを示します。

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

SELECT

InnoDB は、次の 2 つの条件に基づいてレコードの各行をチェックします。

a. InnoDB は、現在のトランザクションバージョンより前のバージョンのデータ行のみを検索します (つまり、行のシステムバージョン番号がトランザクションのシステムバージョン番号以下であること)。これにより、行がトランザクションによって読み取られることが保証されます。トランザクションが開始される前にすでに存在しており、トランザクション自体によって挿入または変更されています。

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

上記の 2 つの条件を満たすレコードのみがクエリ結果として返されます。

INSERT

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

DELETE

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

UPDATE

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

ほとんどの読み取り操作をロックせずに実行できるように、これら 2 つの追加のシステムバージョン番号を保存します。この設計により、データ読み取り操作が非常にシンプルになり、パフォーマンスが非常に向上し、また、規格を満たす行のみが確実に読み取られるようになります。欠点は、レコードの各行に追加の記憶域スペース、より多くの行チェック、および追加のメンテナンス作業が必要になることです。

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

1.5 MySQL ストレージエンジン

ファイルシステムでは、MySQL は各データベース (スキーマとも呼ばれます) をデータディレクトリの下のサブディレクトリとして保存します。テーブルを作成するとき、MySQL はテーブル定義を保存するためにデータベースのサブディレクトリにテーブルと同じ名前の .frm ファイルを作成します。たとえば、MyTable という名前のテーブルを作成すると、MySQL はテーブルの定義を MyTable.frm ファイルに保存します。 MySQL はファイルシステムのディレクトリとファイルを使用してデータベースとテーブルの定義を保存するため、大文字と小文字の区別は特定のプラットフォームと密接に関係しています。 Windows では大文字と小文字は区別されませんが、Unix の場合は大文字と小文字が区別されます。ストレージエンジンが異なればデータとインデックスの保存方法も異なりますが、テーブルの定義は MySQL サービス層で均一に処理されます。

SHOW TABLE STATUS コマンド (MySQL 5.0 以降のバージョンでは、INFORMATION SCHEMA 内の対応するテーブルをクエリすることもできます) を使用して、テーブル関連の情報を表示できます。たとえば、mysql データベース内のユーザーテーブルの場合:

mysql> SHOW TABLE STATUS LIKE 'user' : 6

Avg_row_length: 59

データ長: 356

最大データ長: 4294967295

インデックス長: 2048

Data_free: 0

Auto_increment: NULL

Create_time: 2002-01-24 18:07:17

Update_time : 2002-01-24 21 : 56 : 29

Check_time: NULL

セット内の 1 行 ( o.oo sec)

elm out 結果は、これが MyISAM テーブルであることを示しています。出力には統計だけでなく、他にも多くの情報が含まれます。以下に各行の意味を簡単に紹介します。

名前

テーブル名。

Engine

テーブルのストレージエンジンのタイプ。古いバージョンでは、列の名前は Engine ではなく Type でした。	行形式
行形式。 MyISAM テーブルの場合、オプションの値は Dynamic、Fixed、または Compressed です。 Dynamic の行の長さは可変であり、通常は VARCHAR や BLOB などの可変長フィールドが含まれます。固定行の長さは固定されており、CHAR や INTEGER などの固定長の列のみが含まれます。圧縮された行は、圧縮されたテーブルにのみ存在します。	Rows
テーブル内の行数。 MyISAM およびその他のストレージエンジンの場合、値は正確ですが、InnoDB の場合は推定値です。	Avg_ row_length	各行に含まれる平均バイト数。
Data_length	テーブルデータのサイズ（バイト単位）。
Max-data_length	この値は、ストレージエンジンに関連します。
Index_length	インデックスのサイズ (バイト単位)。
Data_free	MyISAM テーブルの場合、割り当てられているが現在使用されていないスペースを表します。スペースのこの部分には、以前に削除された行と、後で INSERT で使用できるスペースが含まれます。
Auto_increment	次の AUTO INCREMENT の値。
Create_time	テーブルの作成時間。
Update_time	テーブルデータの最終変更時刻。
Check_ time	CKECK TABLE コマンドまたは myisamchk ツールを使用してテーブルが最後にチェックされた時刻。
Collation	テーブルのデフォルトの文字セットと文字列の照合順序。
Checksum	有効にすると、テーブル全体のリアルタイムのチェックサムが保存されます。
Create_options	テーブルの作成時に指定されたその他のオプション。
コメント	この列には、その他の追加情報が含まれています。 MyISAM テーブルの場合、テーブルの作成時に含まれたコメントが保存されます。 InnoDB テーブルの場合、InnoDB テーブルスペースの残りのスペース情報が保存されます。ビューの場合、この列にはテキスト単語「VIEW」が含まれます。