MySQL の全体的なアーキテクチャの紹介

MySQL の全体的なアーキテクチャは、サーバー層とストレージ エンジン層に分かれています。サーバー層には、コネクタ、クエリ キャッシュ、アナライザー、オプティマイザー、エグゼキュータなどが含まれます。ストアド プロシージャ、トリガー、ビュー、組み込み関数はすべてこの層に実装されています。データ エンジン レイヤーは、InnoDB、MyISAM、メモリなどのエンジンなどのデータの保存と取得を担当します。クライアントがサーバー層に接続すると、サーバーはデータ エンジンによって提供されるインターフェイスを呼び出してデータの変更を行います。

コネクタ

クライアントとの接続の確立、ユーザー権限の取得、接続の維持と管理を担当します。

接続ステータスを照会するには、 show processlist ; を使用します。ユーザーが接続を確立した後、管理者が接続ユーザーの権限を変更しても、接続ユーザーには影響しません。デフォルトの接続時間は 8 時間で、その後は接続が切断されます。

長い接続について簡単に説明します。

利点: 接続時間中、クライアントは常に同じ接続を使用するため、複数の接続によるリソースの消費を回避できます。

デメリット: MySQL を実行すると、使用されるメモリは接続オブジェクトによって管理されます。長時間解放されないため、システム メモリのオーバーフローが発生し、システムによって強制終了されます。そのため、長い接続を定期的に切断するか、大規模なクエリを実行した後に切断する必要があります。 MySQL 5.7 以降では、 mysql_rest_connectionを使用して、再接続や権限の検証を行わずに接続リソースを初期化できます。

クエリキャッシュ

クエリ要求を受信すると、クエリ キャッシュ (キー/値のストレージ) がクエリされ、実行されたかどうかが確認されます。そうでない場合は、通常の実行プロセスに従ってください。

しかし、実際の状況では、クエリ キャッシュを設定する必要は通常ありません。クエリに関係するテーブルが更新されると、キャッシュがクリアされるためです。したがって、静的テーブルに適しています。 MySQL 8.0 以降ではクエリ キャッシュは廃止されます。

アナライザ

語彙解析:

たとえば、選択、テーブル名、列名を識別し、それらが存在するかどうかを判断します。

構文解析:

ステートメントが MySQL 構文に準拠しているかどうかを判断します。

オプティマイザ

インデックスの使用、結合テーブルの順序などを決定し、最も最適化されたソリューションを選択します。

アクチュエータ

ステートメントを実行する前に、まず権限チェックが実行されます。権限が渡されると、データ エンジンによって提供されるインターフェイスを使用してクエリが実行されます。低速クエリが設定されている場合、対応するログに、スキャンされた行数を示すrows_examinedが表示されます。一部のシナリオ (インデックス) では、エグゼキュータは 1 回呼び出されますが、データ エンジンでは複数の行がスキャンされるため、エンジンによってスキャンされる行数は、 rows_examined とまったく同じではありません。

事前に権限を確認しない理由: たとえば、トリガーなどの場合には、権限はエグゼキュータ段階で決定する必要があり、オプティマイザ段階では検証できません。

プロファイリングを使用してSQL実行プロセスを表示する

プロファイリングをオンにして、ステートメントの実行プロセスを分析します。

mysql> @@プロファイリングを選択します。
+-------------+
| @@プロファイリング |
+-------------+
| 0 |
+-------------+
セットに 1 行、警告 1 件 (0.00 秒)

mysql> プロファイリングを 1 に設定します。
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)

クエリステートメントを実行します。

mysql> SELECT * FROM s 制限 10;
+------+--------+-----+-----+
| s_id | s_name | 年齢 | 性別 |
+------+--------+-----+-----+
| 1 | z | 12 | 1 |
| 2 | 秒 | 14 | 0 |
| 3 | c | 14 | 1 |
+------+--------+-----+-----+
セット内の 3 行 (0.00 秒)

プロファイルを取得します。

mysql> プロファイルを表示します。
+----------+------------+---------------------------+
| Query_ID | 期間 | クエリ |
+----------+------------+---------------------------+
| 1 | 0.00046600 | SELECT * FROM s 制限 10 |
+----------+------------+---------------------------+

mysql> プロファイルを表示;
+----------------------+----------+
| ステータス | 期間 |
+----------------------+----------+
| 開始 | 0.000069 |
| 権限の確認 | 0.000008 | テーブルを開く | 0.000018 | 初期化 | 0.000019 | システム ロック | 0.000010 | 最適化 | 0.000004 | 統計 | 0.000013 |
| 準備中 | 0.000094 | 準備中 | 実行中 | 0.000016 | データ送信中 | 0.000120 |
| 終了 | 0.000010 |
| クエリ終了 | 0.000015 |
| テーブルのクローズ | 0.000014 |
| アイテムの解放 | 0.000032 |
| クリーンアップ | 0.000026 |
+----------------------+----------+
セット内の行数は 15 行、警告数は 1 (0.00 秒)

クエリ固有のステートメント:

mysql> クエリ 1 のプロファイルを表示します。
+----------------------+----------+
| ステータス | 期間 |
+----------------------+----------+
| 開始 | 0.000069 |
| 権限を確認中 | 0.000008 |
| オープニングテーブル | 0.000018 |
| 初期化 | 0.000019 |
| システムロック | 0.000010 |
| 最適化中 | 0.000004 |
| 統計 | 0.000013 |
| 準備中 | 0.000094 |
| 実行中 | 0.000016 |
| データ送信中 | 0.000120 |
| 終了 | 0.000010 |
| クエリ終了 | 0.000015 |
| テーブルのクローズ | 0.000014 |
| アイテムの解放 | 0.000032 |
| クリーンアップ | 0.000026 |
+----------------------+----------+
セット内の行数は 15 行、警告数は 1 (0.00 秒)

MySQL ロギングモジュール

前述のように、MySQL はサーバー層とデータエンジン層に分かれており、各層には独自のログファイルも対応しています。 InnoDB エンジンを選択した場合、対応するファイルは REDO ログ ファイルになります。サーバー層は binlog ファイルに対応します。なぜ 2 つのログ記録システムが存在するのか、詳しく見てみましょう。

再実行ログ

Redo ログは InnoDB に固有のログです。なぜ Redo ログを導入する必要があるのでしょうか? MySQL が永続性を確保するためにデータをディスク ファイルに書き込む必要があるシナリオを想像してください。ディスクへの書き込み時には、ファイル IO と検索操作が実行されることがわかります。更新操作のたびにこれが実行されると、全体的な効率が極端に低下し、使用できなくなります。

ディスクに直接書き込むことはできないため、解決策としては、まずメモリに書き込み、システムがアイドル状態のときにディスクに更新します。しかし、メモリを更新するだけでは不十分です。システムがクラッシュしたり、異常再起動したりすると、ディスクに書き込まれていないメモリ上のデータが失われ、データの整合性が問題になります。ここで、REDO ログが役立ちます。更新操作が発生すると、InnoDb は最初に REDO ログ (データがどのように変更されたかを記録する) を書き込み、次にメモリを更新し、最後に適切なタイミング (通常はシステムがアイドル状態のとき) でディスクに書き込みます。最初にログを書き込んでからディスクに書き込む操作は、よく知られている WAL (Write-Ahead-Logging) テクノロジです。

REDO ログの登場により、効率が大幅に向上するだけでなく、MySQL がクラッシュセーフ機能を備えていることが保証され、異常な状況でもデータが失われなくなります。

具体的な実装では、REDO ログのサイズは固定です。4 つのファイルのグループとして構成でき、各ファイルは 1 GB で、更新時に 4 つのファイルがループで書き込まれます。

write pos は現在の書き込み位置を記録し、書き込み後に戻ります。4 番目のファイルの末尾が書き込まれると、位置 0 から書き直されます。

チェックポイントは現在消去可能な位置を示しており、ディスクにデータが更新されるとチェックポイントは後方に移動します。

書き込み位置とチェックポイント間の位置は、更新操作を記録できる領域です。書き込み位置がチェックポイントに追いつくと、新しい操作は実行できなくなるため、まずチェックポイントでデータを書き込むようにします。

innodb_flush_log_at_trx_commit を 1 に設定すると、REDO ログの永続性が有効になります。

バイナリログ

Binlog はサーバー層のログであり、主にアーカイブに使用され、バックアップ、マスターとスレーブの同期、およびデータ復旧の役割を果たします。一般的なログ形式には、行、混合、ステートメントなどがあります。具体的な使用方法については、Binlog リカバリ ログを参照してください。

sync_binlog=1 を設定すると、ディスクへの binlog 書き込みを有効にすることができます。

ここでは、binlog と redo を区別します。

1. 所有者は異なります。Binlog はサーバー レベルにあり、すべてのエンジンで使用できます。 REDO ログは InnoDB に固有です。
2. 種類が異なります。Binlog はステートメントの元のロジックを記録する論理ログです (binlog ステートメント)。 REDO ログは、データ ページがどのように変更されたかを記録する物理ログです。
3. データはさまざまな方法で書き込まれます。Binog ログは継続的に追加されますが、REDO ログはループで書き込まれます。
4. 異なる機能として、binlog はアーカイブに使用され、redo log はクラッシュ安全性を確保するために使用されます。
   

2フェーズコミット

以下は、Update を実行するときの executor と InnoDB の内部プロセスです。

例として、update T set c=c+1 where ID=2; ステートメントを挙げます。

1. 実行者は InooDB エンジンを使用して ID が配置されている行を検索します。ID は主キーです。エンジンはツリー検索を通じて行を見つけ、行が配置されているデータ ページがメモリ内にある場合はそれをエグゼキュータに返します。それ以外の場合は、まずディスクからメモリに読み込まれ、その後返されます。
2. 実行者はエンジンからデータを取得し、C 値に 1 を追加し、新しい行を待機してから、エンジン インターフェイスを通じて新しいデータを書き換えます。
3. エンジンは行をメモリに更新し、更新操作を REDO ログに記録し、REDO ログのステータスを準備に変更します。その後、実行者は適切なタイミングでトランザクションをコミットするように指示されます。
4. 実行者はこの操作のバイナリログを生成し、そのバイナリログをディスクに書き込みます。
5. エグゼキュータはエンジンのトランザクションコミットインターフェースを呼び出して、書き込まれたばかりのREDOログをコミット状態に変更し、更新が完了します。

明るい色はエグゼキュータによって実行され、暗い色はエンジンによって実行されます。

メモリを更新した後、REDO ログの書き込みは準備とコミットの 2 つのステップに分割され、2 フェーズ コミットと呼ばれることがよくあります。予期しない状況が発生した場合にデータの一貫性を確保するために使用されます。

ここで、2 フェーズ コミットを使用しなかった場合に何が起こるかを考えてみましょう。

1. まず redo ログを書き込み、次に binlog を書き込みます。MySQL が redo ログの書き込み後に異常再起動し、この時点で binlog が書き込まれていないとします。再起動後は、REDOLOG が書き込まれているため、データベースの内容に問題はありません。ただし、この時点で、binlog をバックアップまたはリカバリに使用すると、最後のエントリの更新ロジックが欠落し、データの不整合が発生することがわかります。
2. 最初に binlog を書き込み、次に redo ログを書き込みます。binlog が書き込まれた後、MySQL が異常再起動し、redo ログが書き込まれません。再起動後、REDO ログが正常に書き込まれていないことがわかり、トランザクションは無効とみなされます。ただし、binlog には追加の更新ステートメントがあります。リカバリ後、データは当然不整合になります。

2 段階の送信プロセスを分析してみましょう。

1. プロセスは、REDO ログ準備フェーズ中、時刻 A にクラッシュします。再起動後、REDO ログが書き込まれていないことが判明したため、トランザクションはロールバックされました。

2. binlog の書き込み中にシステムがクラッシュした場合、再起動後に binlog が書き込まれていないことが判明するため、操作をロールバックします。

3. バイナリログが書き込まれるが、REDOログコミットステータスを送信するとクラッシュが発生する

• REDO ログ内のトランザクションが完了し、コミット フラグがある場合は、直接コミットします。
• REDO ログに完全な準備のみがある場合は、対応する binlog が完全であるかどうかを判断します。

完了した場合、トランザクションをコミットします。不完全な場合、トランザクションをロールバックします。

バイナリログが完了したかどうかをどのように判断しますか?

• ステートメント形式 binlog、COMMIT; マーク付き
• 行形式のバイナリログには XID イベントが含まれます。
• 5.6 以降では、binlog の正確性を検証するための binlog-checksum パラメータも存在します。
  

同じ操作を表すために、REDO ログと binlog を関連付けるにはどうすればよいですか?

構造体には共通のデータ フィールド XID があります。クラッシュ リカバリ中、REDO ログは順番にスキャンされます。

• 準備およびコミット REDO ログがある場合は、直接コミットします。
• 準備 REDO ログのみがあり、コミット REDO ログがない場合は、XID を使用して、バイナリログ内の対応するトランザクションを見つけて判断します。
  

データが書き込まれた後、最終的なディスクの場所と REDO ログの間に何らかの関係はありますか?

• 通常実行中のインスタンスの場合、メモリ内のページが変更されると、ディスク上のデータ ページと不整合が発生し、これをダーティ ページと呼びます。データをディスクにフラッシュするプロセスは、メモリ内のデータ ページをディスクに書き込むことです。
• クラッシュ シナリオでは、InnoDB はデータ ページの更新が失われたかどうかを判断し、それをメモリに読み込み、REDO ログによってメモリの内容を更新します。更新が完了すると、メモリ ページはダーティ ページになり、最初のケースの状態に戻ります。

REDO ログ バッファと REDO ログの関係は何ですか?

トランザクションの更新処理中には複数の SQL ステートメントが存在するため、複数のログを書き込む必要があります。
ただし、書き込みプロセス中は、生成されたログを最初に保存する必要がありますが、コミット前に REDO ログに直接書き込むことはできません。
そのため、まずはメモリ内のREDOログバッファを使用してREDOログを格納します。コミット時に、バッファの内容をREDOログに書き込みます。

要約する

記事の冒頭では、MySQL の全体的なアーキテクチャがサーバー層とエンジン層に分かれていることを説明し、ステートメントの実行プロセスについて簡単に説明します。その後、MySQL は、ネイティブの MyISAM よりもトランザクション機能とクラッシュセーフ機能が優れているため、5.5 以降では InnoDB をデフォルトのエンジンとして選択しました。

クラッシュセーフはREDOログによって実現されます。 REDO ログと同様に、サーバー エンジンのログであり、データのアーカイブとバックアップに使用される binlog があります。

最後に、データの一貫性を確保するために、REDO ログと binlog が同じトランザクションに配置され、これは 2 フェーズ コミット操作と呼ばれることが多いことが説明されています。

以上がMySQL全体のアーキテクチャの詳細な紹介です。MySQL全体のアーキテクチャの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。