MySQL はどのようにしてデータの整合性を確保するのでしょうか?

オンライン ビジネスにとってデータの一貫性と整合性が重要であることは明らかです。データが失われないようにするにはどうすればよいでしょうか?今日は、データの整合性と強力な一貫性に関して MySQL がどのような改善を行ったかについて説明します。

1. MySQL 2フェーズコミット

Oracle や MySQL などのリレーショナル データベースでは、先行書き込みログ戦略が重要です。ログがディスクに保持されている限り、MySQL の異常な再起動後もデータが失われないことが保証されます。 MySQL では、ログについて話すときには、REDO ログと binlog について言及する必要があります。

1. 再実行ログ

REDO ログ (REDO ログ ファイルとも呼ばれます) は、各データ ページのデータ行への変更を詳細に記録し、データが変更された後の値を記録します。 REDO ログはデータベースのクラッシュ回復に使用され、データのセキュリティを確保するための最も重要な機能の 1 つです。

REDO ログは順次かつ周期的に書き込まれます。REDO ログのファイル サイズと数は、innodb_log_file_size と innodb_log_files_in_group の 2 つのパラメータによって制御されます。 REDO ログをディスクに書き込む前に、まず REDO ログ バッファに書き込まれます。そのサイズは innodb_log_buffer_size によって制御されます。ログは、REDO ログ バッファに書き込まれた後、どのようにディスクに保存されますか? Redo ログの書き込み戦略を制御するために、Innodb は innodb_flush_log_at_trx_commit パラメータの異なる値に応じて異なる戦略を採用します。3 つの異なる値があります。

• 1. 0 に設定した場合: トランザクションがコミットされると、MySQL バックグラウンド マスター スレッドは 1 秒ごとにキャッシュ ファイルをログ ファイルに更新します。
• 2. 1 に設定すると、トランザクションがコミットされるたびに REDO ログがディスクに直接保存され、トランザクション ログが失われないことが保証されますが、データベースのパフォーマンスに若干の影響が出ます。
• 3. 2 に設定すると、トランザクションがコミットされるたびに、REDO ログのみがログ ファイルに書き込まれ、ディスクはフラッシュされません。ファイル システムはディスクを自動的にフラッシュします。

3 つのモードのうち、0 はパフォーマンスが最も優れていますが、安全ではありません。MySQL プロセスがクラッシュすると、1 秒分のデータが失われます。オプション 1 はセキュリティが最も高いですが、パフォーマンスへの影響が最も大きくなります。オプション 2 は、主にオペレーティング システムによるディスク フラッシュ時間を制御します。MySQL のみがクラッシュした場合は、データに影響はありません。ホストが異常クラッシュした場合は、データも失われます。

2. バイナリログ

binlog はバイナリ ログとも呼ばれ、選択操作と表示操作を除く、MySQL データベースを変更するすべての操作を記録します。主に、リカバリ、レプリケーション、監査の機能を果たします。 Binlog 形式には、主にステートメント、行、混合が含まれます。

ステートメント: 操作に基づく SQL ステートメントが binlog に記録されます。これは推奨されません。

行: 行変更の記録に基づいて、行変更の前後のコンテンツを記録します。行モードは、データベースがデータ損失を防ぐための重要な保証でもあるため、使用することをお勧めします。

混合: 最初の 2 つのモードの混合。推奨されません。

Binlog の書き込みロジックも比較的単純です。トランザクションの実行中に、まず binlog キャッシュに書き込まれ、次にトランザクションがコミットされたときに binlog ファイルに書き込まれます。 binlog キャッシュは、binlog_cache_size および max_binlog_size パラメータによって制御されます。各スレッドには binlog キャッシュが割り当てられますが、binlog ファイルは共有されます。

Binlog のログ ファイル書き込みメカニズムは sync_binlog によって制御されます。

• 1. sync_binlog=0 の場合、各トランザクションは fsync なしでのみ書き込まれることを意味します。
• 2. sync_binlog=1 の場合、トランザクションが送信されるたびに fsync が実行され、データがディスクにフラッシュされます。
• 3. sync_binlog=N (N>1) の場合、n 個のトランザクションがコミットされた後、MySQL は fsync アクションを実行して、binlog キャッシュ内のデータをディスクにフラッシュすることを意味します。

innodb_flush_log_at_trx_commit と sync_binlog の両方を 1 に設定すると、MySQL データの従来のダブルワン モードになり、データベースのデータが失われないようにすることができます。

MySQL データは、各ダーティ データのフラッシュによって発生するパフォーマンスへの影響を軽減するために WAL メカニズムを採用しています。「ダブル ワン」戦略が設定されている場合、データベースのパフォーマンスに影響しますか?実際、これは主に、REDO ログと binlog の両方が順次書き込まれるという事実によるものです。ディスクの順次書き込み速度は、ランダム書き込み速度よりもはるかに高速です。さらに、MySQL 内のグループ コミット メカニズムにより、ディスクの IOPS 消費が大幅に削減されました。

3. 2フェーズコミット

MySQL では、2 フェーズ コミット (2pc) が導入されています。MySQL は通常のトランザクションを XA トランザクション (内部分散トランザクション) として扱い、各トランザクションに一意の ID (XID) を自動的に割り当てます。COMMIT は、準備とコミットの 2 つのフェーズに受動的に分割されます。

フェーズ 1: トランザクション準備フェーズ

この時点で、SQL は正常に実行され、xid 情報と redo および undo メモリ ログが生成されました。次に、準備メソッドを呼び出して最初のフェーズを完了し、トランザクション ステータスを TRX_PREPARED に設定し、REDO ログをディスクにフラッシュします。

フェーズ2: コミットフェーズ

トランザクションの最初のフェーズが準備フェーズに入ると、生成されたバイナリログがファイルに書き込まれ、ディスクにフラッシュされます。この時点で、トランザクションはコミットされることになります。

特定の異常シナリオ分析:

1. 準備フェーズでトランザクションがクラッシュし、データベースがバイナリ ログに書き込まれず、リカバリ中にストレージ エンジンがコミットされない場合、トランザクションはロールバックされます。

2. トランザクションが binlog フェーズでクラッシュすると、ログはディスクに正常に書き込まれません。トランザクションは開始時にロールバックされます。 3. バイナリログがディスクに fsync() された後にトランザクションがクラッシュし、InnoDB がコミットする時間がなかった場合、MySQL データベースはリカバリ中にバイナリログの Xid_log_event を読み取り、これらの XID を持つトランザクションをコミットするように InnoDB に指示します。InnoDB はこれらのトランザクションをコミットした後、他のトランザクションをロールバックして、ストレージ エンジンとバイナリ ログの一貫性を保ちます。

MySQL の 2 フェーズ コミットにより、異常なシャットダウンによりデータベースが再起動された後でもデータが失われないことが保証されます。

2. 二重書き込み

前述したように、REDO ログ、バイナリログ、および 2 フェーズ コミットにより、MySQL の異常な再起動後にロールフォワードとロールバックを通じてデータを回復できるようになります。 MySQL はリカバリに redo ログを使用します。 redo ログにはページ上の物理的な操作が記録されますが、問題があります。部分的な書き込みの問題 (ページ サイズは 16K です。メモリ内のダーティ ページをデータベースに書き込むときに、4K が書き込まれ、突然電源が切れたとします。つまり、最初の 2 つの 4K は新しく、最後の 12K は古いため、このデータ ページは不完全で、不良データ ページです) など、ページ自体に問題がある場合、 redo リカバリではデータ ページの整合性を検証します。この時点で、データ ページはすでに破損しているため、 redo ログをリカバリに使用できず、データが失われます。

二重書き込みの原則:

1. バッファ プールのダーティ ページを更新する場合、ダーティ ページはデータ ファイルに直接書き込まれるのではなく、最初にダブル書き込みバッファにコピーされます。

2. 次に、ダブル書き込みバッファをディスク上の共有テーブルスペースに 2 回 (1 回につき 1 MB) 書き込みます。

3. 最後に、ダブル書き込みバッファからデータ ファイルを書き込みます。データは常に 2 回書き込まれますが、二重書き込みは順番に書き込まれるため、実際にはシステム パフォーマンスが約 10% 低下します。

これにより、ディスク上の共有テーブルスペースにデータ ページのコピーが存在するため、前述の部分的な書き込み失敗の問題を解決できます。ページをデータ ファイルに書き込むプロセス中にデータベースがクラッシュした場合、インスタンスを復元すると、共有テーブルスペースにページのコピーが見つかり、コピーされて元のデータ ページが上書きされ、その後、REDO ログを適用できます。

3. まとめ

今日は、MySQL の 2 フェーズ コミットと二重書き込みメカニズムについて説明しました。これらはそれぞれ、MySQL がクラッシュして再起動する場合と部分的なページ書き込みの場合のシナリオで、MySQL がデータ損失を防ぐ方法を解決します。では、オペレーティング システムがクラッシュして起動できない場合はどうすればよいでしょうか?データが失われないようにするために、MySQL はクラスター アーキテクチャでどのような最適化を行いましたか?次の章では、クラスター アーキテクチャにおける MySQL の最適化と改善について説明します。