刺激的な mysql アーキテクチャと InnoDB ストレージ エンジンの知識の詳細な説明

この記事では、MYSQL と InnoDB ストレージ エンジンの高度なアーキテクチャに関する知識を提供します。お役に立てば幸いです。

#MySQL の基本アーキテクチャ図

コネクタ

クエリ キャッシュ

アナライザー

#A Select ステートメントの実行プロセス

上の図は、InnoDB ストレージ エンジンを例にしています。処理プロセスは次のとおりです。

ユーザーは Tomcat にリクエストを送信し、Tomcat 接続プールと mysql 接続プールを介して接続を確立し、SQL ステートメントを MySQL に送信します。接続を通じて;
MySQL には別のリスニング スレッドがあり、リクエスト データを読み取り、接続で要求された SQL ステートメントを取得します。取得した SQL データを実行のために SQL インターフェイスに送信します。
SQL インターフェイスは、解析のために SQL を SQL パーサーに送信します。
# SQL をクエリ オプティマイザーに解析して最適なクエリ Road 強度を見つけ、実行プログラムに送信します。
実行プログラムは、ストレージ エンジン インターフェイスを呼び出して、以下に従って特定の順序とステップで実行します。最適化された実行計画。

• たとえば、エグゼキュータはまずストレージ エンジンのインターフェイスを呼び出して、「users」テーブルの最初のデータ行を取得し、次にこのデータの「id」フィールドを決定します。期待する値と同じ値ですか? そうでない場合は、ストレージ エンジン インターフェイスの呼び出しを続けて、「users」テーブルの次のデータ行を取得します。上記の考え方に基づいて、エグゼキュータはオプティマイザによって生成された一連の実行プランを使用し、ストレージ エンジンのさまざまなインターフェイスを継続的に呼び出して、SQL ステートメントの実行プランを完成させます。これは、大まかに言えば継続的に更新または抽出することです。データが出てきます。

ここにはいくつかの質問があります:

MySQL ドライバーとは正確には何ですか?

Java を例に挙げると、Java システムの MySQL データベースにアクセスしたい場合は、Maven の

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>

#InnoDB ストレージ エンジン

## 図からわかるように、InnoDB ストレージ エンジンは、メモリ プール、バックグラウンド スレッド、ディスク ファイルの 3 つの部分で構成されています。

バッファ プール バッファ プール

InnoDB ストレージ エンジンはディスク ストレージに基づいており、ページ単位でレコードを管理しますが、CPU 速度とディスク速度の間にギャップがあるため、ディスクベースのデータベース システムは通常、データベース全体のパフォーマンスを向上させるためにバッファ プール レコードを使用します。

バッファ プールにキャッシュされるデータ ページのタイプには、インデックス ページ、データ ページ、アンドゥ ページ、挿入バッファ、アダプティブ ハッシュ インデックス、ロック情報、InnoDB に格納されるデータ ディクショナリ情報が含まれます。

データ ページとインデックス ページ

ページ (ページ) は、Innodb ストレージの最も基本的な構造であり、Innodb ディスク管理の最小単位です。データベースに関連するすべてのコンテンツです。ページ構造に格納されます。ページはいくつかのタイプに分類されますが、データ ページとインデックス ページの 2 つの最も重要なタイプです。

挿入バッファ

InnoDB エンジンで挿入操作を実行するときは、通常、より高い挿入率を得るために主キーの順序で挿入する必要があります。パフォーマンス。テーブルに非クラスター化非一意インデックスがある場合、挿入時にデータ ページは引き続き主キーに従って順序で格納されますが、非クラスター化インデックスのリーフ ノードの挿入は連続的ではなくなります。非クラスター化インデックス ページへの個別アクセスは、ランダム読み取りの存在により、挿入操作のパフォーマンスの低下につながります。

したがって、InnoDB ストレージ エンジンは挿入バッファの設計を先駆けて行いました。非クラスター化インデックスの挿入または更新操作の場合、毎回インデックス ページに直接挿入されるのではなく、挿入されたバッファーが非クラスター化かどうかを最初に判断します。インデックス ページはインデックス ページ内にあり、バッファ プールに存在する場合は直接挿入され、存在しない場合は最初に Insert Buffer オブジェクトに挿入されますが、これは不正行為のようです。データベースの非クラスター化インデックスはリーフ ノードに挿入されていますが、実際には存在せず、別の場所に保存されているだけです。次に、特定の頻度と状況で、挿入バッファと補助インデックス ページのサブノードのマージ操作を実行します。このとき、複数の挿入は通常 (1 つのインデックス ページ内にあるため) 1 つの操作にマージできます。操作の効率が大幅に向上し、非クラスター化インデックス挿入のパフォーマンスが向上しました。

ただし、挿入バッファを使用するには、次の 2 つの条件を同時に満たす必要があります。

• インデックスはセカンダリ インデックス (セカンダリ インデックス) である;

• インデックスは一意ではありません。

上記の 2 つの条件が満たされる場合、InnoDB ストレージ エンジンは挿入バッファを使用します。これにより、挿入操作のパフォーマンスが向上します。ただし、アプリケーションが多数の挿入操作を実行し、そのすべてに非一意の非クラスター化インデックスが含まれる、つまり挿入バッファーが使用される状況を考えてみましょう。この時点で MySQL データベースがダウンすると、実際の非クラスター化インデックスにマージされない大量の挿入バッファーが存在することになります。

したがって、現時点での回復には長い時間がかかる可能性があり、極端な場合には数時間かかる場合もあります。バッファを挿入するときに、データベースは挿入されたレコードの一意性を判断するためにインデックス ページを検索しないため、補助インデックスを一意にすることはできません。検索すると必ず離散読み取りが発生し、Insert Buffer の意味が失われます。

コマンド SHOW ENGINE INNODB STATUS

を使用して挿入バッファ情報を表示できます。seg size は、挿入バッファの現在のサイズが 11336×16KB であることを示します。これは約 177MB、フリー リスト len はフリー リストの長さを表し、size はマージされたレコード ページの数を表します。太字の 2 行目は、挿入パフォーマンスの向上を示しているため、ユーザーが本当に気にしている部分である可能性があります。 Inserts は挿入されたレコードの数を表します。merged recs はマージされた挿入レコードの数を表します。merges はマージの数 (実際のページ読み取り数) を表します。 merges: マージされた recs は約 1:3 です。これは、挿入バッファーによって、非クラスター化インデックス ページに対する個別の IO 論理リクエストが約 2/3 削減されることを意味します。

前に述べたように、現在、挿入バッファーに問題があります。書き込みが集中する条件下では、挿入バッファリングがバッファー プール メモリ (innodb バッファー プール) を占有しすぎます。デフォルトでは、最大 1/ を占有する可能性があります。 2 バッファー プール メモリ。以下は、InnoDB ストレージ エンジン ソース コードの挿入バッファーの初期化操作です:

Change Buffer

InnoDB バージョン 1.0 から.x Change Buffer が導入されました。これは、Insert Buffer のアップグレード バージョンと見なすことができます。InnodB ストレージ エンジンは、DML 操作 (INSERT、DELETE、および UPDATE) をバッファリングできます。それらは、Insert Buffer、Delete Buffer、Purge Buffer、そしてもちろんです以前の Insert Like Buffer と同様に、Change Buffer に適用されるオブジェクトは依然として非一意の補助インデックスです。

レコードに対する UPDATE 操作は 2 つのプロセスに分割できます:

• レコードを削除済みとしてマークする;

• 実際に削除するレコード

# したがって、削除バッファは、レコードを削除対象としてマークする UPDATE 操作の最初のプロセスに対応します。 PurgeBuffer は UPDATE 操作の 2 番目のプロセスに対応し、実際の削除を記録しようとしています。同時に、InnoDB ストレージ エンジンは、さまざまなバッファ オプションを有効にするために使用されるパラメータ innodb_change_buffering を提供します。このパラメータのオプションの値は、挿入、削除、パージ、変更、すべて、なしです。挿入、削除、およびパージは、前に説明した 3 つの状況です。 「変更」は挿入と削除を有効にすることを意味し、「すべて」はすべてを有効にすることを意味し、「なし」は何も有効にしないことを意味します。このパラメータのデフォルト値は all です。

从 InnoDB1.2.x版本开始,可以通过参数 innodb_change_buffer_max_size 来控制Change Buffer最大使用内存的数量:

mysql> show variables like 'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set (0.05 sec)

innodb_change_buffer_max_size 值默认为25,表示最多使用1/4的缓冲池内存空间。

而需要注意的是,该参数的最大有效值为50在 MySQL5.5版本中通过命令 SHOW ENGINE INNODB STATUS,可以观察到类似如下的内容:

可以看到这里显示了 merged operations和 discarded operation,并且下面具体显示 Change Buffer中每个操作的次数。 Insert 表示 Insert Buffer; delete mark表示 Delete Buffer; delete表示 Purge Buffer; discarded operations表示当 Change Buffer发生 merge时,表已经被删除,此时就无需再将记录合并(merge)到辅助索引中了。

自适应哈希索引

InnoDB 会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。InnoDB 存储引擎会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希索引。

自适应哈希索引通过缓冲池的B+树页构建而来，因此建立速度很快，而且不需要对整张数据表建立哈希索引。其有一个要求，即对这个页的连续访问模式必须一样的，也就是说其查询的条件必须完全一样，而且必须是连续的。

锁信息（lock info）

我们都知道，InnoDB 存储引擎会在行级别上对表数据进行上锁，不过 InnoDB 打开一张表，就增加一个对应的对象到数据字典。

数据字典

对数据库中的数据、库对象、表对象等的元信息的集合。在 MySQL 中，数据字典信息内容就包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容，MySQL INFORMATION_SCHEMA 库提供了对数据局元数据、统计信息、以及有关MySQL Server的访问信息（例如：数据库名或表名，字段的数据类型和访问权限等）。该库中保存的信息也可以称为MySQL的数据字典。

预读机制

MySQL的预读机制，就是当你从磁盘上加载一个数据页的时候，他可能会连带着把这个数据页相邻的其他数据页，也加载到缓存里去！

举个例子，假设现在有两个空闲缓存页，然后在加载一个数据页的时候，连带着把他的一个相邻的数据页也加载到缓存里去了，正好每个数据页放入一个空闲缓存页！

哪些情况下会触发MySQL的预读机制？

• 有一个参数是innodb_read_ahead_threshold，他的默认值是56，意思就是如果顺序的访问了一个区里的多个数据页，访问的数据页的数量超过了这个阈值，此时就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去。

• 如果Buffer Pool里缓存了一个区里的13个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时就会直接触发预读机制，把这个区里的其他的数据页都加载到缓存里去这个机制是通过参数innodb_random_read_ahead来控制的，他默认是OFF，也就是这个规则是关闭的。

所以默认情况下，主要是第一个规则可能会触发预读机制，一下子把很多相邻区里的数据页加载到缓存里去。

预读机制的好处为了提升性能。假设你读取了数据页01到缓存页里去，那么接下来有可能会接着顺序读取数据页01相邻的数据页02到缓存页里去，这个时候，是不是可能在读取数据页02的时候要再次发起一次磁盘IO？

所以为了优化性能，MySQL才设计了预读机制，也就是说如果在一个区内，你顺序读取了好多数据页了，比如数据页01到数据页56都被你依次顺序读取了，MySQL会判断，你可能接着会继续顺序读取后面的数据页。那么此时就提前把后续的一大堆数据页（比如数据页57到数据页72）都读取到Buffer Pool里去。

缓冲池内存管理

这里需要了解三个链表（Free List、Flush List、LRU List），

• Free List ディスク上のデータ ページとキャッシュ ページは 1 対 1 に対応しており、どちらも 16KB で、1 つのデータ ページが 1 つのキャッシュ ページに対応します。データベースは、双方向リンク リスト データ構造であるバッファ プール用のフリー リンク リストを設計します。このフリー リンク リストでは、各ノードはデータ ブロックを記述するフリー キャッシュ ページのアドレスになります。 1 つのキャッシュ ページが空いている限り、その記述データ ブロックはこの空きリンク リストに入れられます。データベースが最初に起動されるとき、それはデータがまったくない空のデータベースである可能性があるため、すべてのキャッシュ ページが空きである可能性があるため、この時点ですべてのキャッシュ ページの記述データ ブロックがこの空きリンク リストに入れられます。 , この無料リンク リストには、リンク リストの先頭ノードと末尾ノードを参照する基本ノードがあり、リンク リスト内にデータ ブロックを記述するノードがいくつあるか、つまり空きキャッシュ ページがいくつあるかも格納されます。 . .

• フラッシュ リストはフリー リストのリンク リストに似ています。フラッシュ リンク リストの本質は、キャッシュ ページの記述データ ブロック内の 2 つのポインタを使用して、キャッシュ ページの記述データ ブロックを作成することです。変更されたキャッシュ ページ。二重リンク リスト。変更されたキャッシュ ページには、その記述データ ブロックがフラッシュ リンク リストに追加されます。フラッシュとは、これらがダーティ ページであり、将来ディスクにフラッシュされることを意味します。
  

• LRU リスト バッファプールのサイズは確定しているため、つまりフリーリンクリスト内の空きキャッシュページのデータは確定しています。ディスク上では、ページはフリー キャッシュ ページにロードされ、フリー キャッシュ ページはフリー リンク リストから常に削除されます。遅かれ早かれ、フリー リンク リストにフリー キャッシュ ページはなくなります。現時点では、一部のキャッシュ ページは、誰が排除されるべきですか？これにはキャッシュ ヒット率を使用する必要があり、最も多くのキャッシュ ヒットがあったものが一般的に使用され、一般的に使用されないものは削除できます。したがって、どのキャッシュ ページが一般的に使用されないかを判断するために、LRU リンク リストが導入されています。
  

LRU リンク リストの削除戦略は何ですか?

データ ページをディスクからキャッシュ ページにロードするときに、このキャッシュ ページの記述データ ブロックを LRU リンク リストの先頭に置くとします。その後、キャッシュ ページが存在する限り、データがある場合、それは LRU 内にあり、最近データがロードされたキャッシュ ページは LRU リンク リストの先頭に配置され、その後、特定のキャッシュ ページが末尾に追加されます。クエリが発生すると、クエリは先頭に移動され、その後、末尾を削除する必要があります。

#このとき、空きキャッシュ ページがない場合は、この時点で新しいデータ ページを読み込む必要があります。いわゆる「最も最近使用されていないキャッシュ ページ」をデータから取り出す必要がありますか? LRU リンク リストの最後を削除してディスクにフラッシュしますか? その後、空きキャッシュ ページを解放します。これは明らかに非常に不合理です。

2 番目の状況は、頻繁にアクセスされるキャッシュ ページが削除されるシナリオにつながる可能性があります。

フル テーブル スキャンにより、テーブル内のすべてのデータ ページがディスクから一度に直接ロードされます。バッファプール。このとき、このテーブルのすべてのデータ ページが各キャッシュ ページに 1 つずつロードされる可能性があります。現時点では、LRU リンク リストの先頭にあるキャッシュ ページの大きなリストがすべて、フル テーブル スキャンを通じてロードされたキャッシュ ページである可能性があります。では、この完全なテーブル スキャンの後、このテーブル内のデータが今後ほとんど使用されなくなったらどうなるでしょうか?この時点で、LRU リンク リストの末尾はすべて、以前に頻繁にアクセスされたキャッシュされたページである可能性があります。次に、スペースを空けるために一部のキャッシュ ページを削除する場合は、LRU リストの最後にある頻繁にアクセスされたキャッシュ ページを削除し、以前のフル テーブル スキャンでロードされたアクセス頻度の低い多数のページを残します。ページがキャッシュされました!

LRU アルゴリズムの最適化: ホット データとコールド データの分離のアイデアに基づいた LRU リンク リストの設計

MySQL が LRU リンク リストを設計するとき、実際には次のアイデアが採用されます。ホット データとコールド データを分離します。 LRU リンク リストは、ホット データとコールド データの 2 つの部分に分割されます。ホット データとコールド データの比率は、innodb_old_blocks_pct パラメータによって制御されます。デフォルト値は 37 で、コールド データが 37 を占めることを意味します。 %。データ ページが初めてキャッシュにロードされるとき、キャッシュ ページは実際にはコールド データ領域のリンク リストの先頭に配置されます。

その後、MySQL はルールを設定しました。彼は innodb_old_blocks_time パラメータを設計しました。デフォルト値は 1000、つまり 1000 ミリ秒です。言い換えると、データ ページがキャッシュ ページにロードされた後、1 秒後にキャッシュにアクセスします。 、ホット データ領域のリンク リストの先頭に移動されます。データ ページをキャッシュにロードし、1 秒後にこのキャッシュ ページにアクセスすると仮定します。これは、今後頻繁にアクセスする可能性が高いことを意味します。制限時間は 1 秒なので、このキャッシュされたページには 1 秒後にのみアクセスします。ページをキャッシュすると、ホット データ領域のリンク リストの先頭にキャッシュ ページが配置されます。

この場合、先読みとフルテーブルスキャンのデータはコールドデータヘッダーにのみ存在し、最初からホットデータ領域には入りません。

LRU アルゴリズムの徹底的な最適化

LRU リンク リストのホット データ領域、つまり最後のキャッシュ ページのみのアクセス ルールを最適化します。ホットデータ領域の 3/4 はアクセス後、リンクリストの先頭に移動します。ホット データ領域のキャッシュ ページの最初の 1/4 がアクセスされた場合、それらのページはリンク リストの先頭に移動されません。

たとえば、ホットデータ領域のリンクリストにキャッシュページが100ページあるとすると、上位25ページのキャッシュページはアクセスされてもリンクリストの先頭には移動しません。ただし、次の 75 キャッシュ ページについては、アクセスされている限り、リンク リストの先頭に移動されます。このようにして、リンクされたリスト内のノードの移動を可能な限り減らすことができます。

#LRU リンク リストの削除キャッシュ ページのタイミング

• バックグラウンド スレッドは、スケジュールされたタスクを実行します。このスケジュールされたタスクは、一定期間が経過すると、LRU リンク リストのコールド データ領域の末尾にある一部のキャッシュ ページがディスクにフラッシュされ、これらのキャッシュ ページがクリアされ、再び追加されます。無料リンクリストへ。

無料リンク リストには無料キャッシュ ページがありません

REDO ログ バッファREDO ログ バッファ

InnoDB にはバッファー プール (略して bp) があります。 bp はデータベース ページのキャッシュです。InnoDB に対する変更操作は、最初に bp のページで実行されます。その後、そのようなページはダーティ (ダーティ ページ) としてマークされ、特別なフラッシュ リストに配置されます。その後、マスター スレッドまたは A専用のクリーニング スレッドは、これらのページを定期的にディスク (ディスクまたは SSD) に書き込みます。

これの利点は、書き込み操作ごとにディスクを操作することを回避できることです。その結果、大量のランダム IO が発生します。定期的なブラッシングにより、ページに対する複数の変更を 1 つの IO 操作にマージでき、非同期書き込みにより、コスト、アクセス遅延。ただし、ダーティ ページがディスクにフラッシュされる前にサーバーが異常にシャットダウンされた場合、これらの変更操作は失われ、書き込み操作が進行中の場合は、データ ファイルの破損によりデータベースが使用できなくなることもあります。

上記の問題を回避するために、Innodb はすべてのページ変更操作を特殊なファイルに書き込み、データベースの起動時にこのファイルから回復操作を実行します。このファイルは REDO ログ ファイルです。このテクノロジーは bp ページの更新を遅らせるため、データベースのスループットが向上し、アクセス遅延が効果的に短縮されます。

問題は、REDO ログ操作 (もちろん非常に高速なシーケンシャル IO) の書き込みによる追加のオーバーヘッドと、データベースの起動時に操作を再開するのに必要な時間です。

REDO ログは、REDO ログ バッファと REDO ログ ファイル (ディスク ファイルのセクションで説明) の 2 つの部分で構成されます。 InnoDB は、トランザクションをサポートするストレージ エンジンです。トランザクションがコミットされると、最初にトランザクションのすべてのログが REDO ログ ファイルに書き込まれる必要があります。トランザクションのコミット操作が完了するまで、トランザクション操作全体は完了しません。 REDO ログ・バッファが REDO ログ・ファイルに書き込まれるたびに、fsync 操作を呼び出す必要があります。これは、REDO ログ・バッファは最初に内容をオペレーティング・システムのバッファ・システムに書き込むだけであり、コンテンツが直接書き込まれることを保証しないためです。 fsync 操作を実行する必要があります。したがって、ディスクのパフォーマンスによって、トランザクション送信のパフォーマンスもある程度決まります (REDO ログのディスク ドロップ メカニズムについては後で紹介します)。

InnoDB ストレージ エンジンは、まず REDO ログ情報を REDO ログ バッファに入れ、次にそれを一定の頻度で REDO ログ ファイルにフラッシュします。通常、REDO ログ バッファは毎秒ログ ファイルにフラッシュされるため、非常に大きく設定する必要はありません。デフォルトは 8MB である構成パラメータ Innodb_log_buffer_size によって制御できます。

Double Write Double Write

Insert Buffer が InnoDB ストレージ エンジンのパフォーマンス向上をもたらすのであれば、Double Write は InnoDB ストレージ エンジンにデータ ページをもたらします。

InnoDB のページサイズは一般的に 16KB であり、データ検証もこの 16KB を基準に計算され、ディスクへのデータの書き込みはページ単位で行われます。大きなデータ ページ (InnoDB の 16KB など) では、ほとんどの場合ファイル システムがアトミックではないため、サーバーがダウンすると書き込みの一部しか実行されない可能性があることがわかっています。 16K のデータを 4K に書き込むときに、システムの停電と OS クラッシュが発生し、書き込みの一部のみが成功しました。この場合は、部分ページ書き込みの問題でした。

経験豊富な DBA は、書き込みエラーが発生した場合、MySQL は REDO ログに基づいて回復できると考えるかもしれません。これは方法ですが、REDO ログに記録されるのは、オフセット 800、write 'aaaa' レコードなどのページの物理的な変更であることを明確に理解する必要があります。ページ自体が破損している場合は、やり直しても意味がありません。 MySQL はリカバリ プロセス中にページのチェックサムをチェックします。チェックサムはページの最後のトランザクション番号です。部分的なページ書き込みの問題が発生すると、ページが破損しており、ページ内のトランザクション番号が見つかりません。 InnoDB の観点からは、そのようなデータ ページはチェックサム検証に合格できず、回復できません。強制的に検証を通過させたとしても、InnoDB の現在のログ タイプの一部 (論理操作も含む) は冪等にできないため、クラッシュから回復することはできません。

この問題を解決するために、InnoDB は二重書き込みバッファを実装しています。簡単に言えば、データ ページを書き込む前に、まずデータ ページを独立した物理ファイルの場所 (ibdata) に書き込み、次にそれをデータに書き込みます。ページ。このように、マシンがダウンして再起動されるとき、データ ページが破損している場合、REDO ログを適用する前に、ページのコピーによってページを復元し、その後 REDO ログをやり直す必要があります。これは二重書き込みです。 。二重書き込みテクノロジーが innodb ストレージ エンジンにもたらすのは、データ ページの信頼性です。二重書き込みテクノロジーについては以下で分析されます

如上图所示，Double Write 由两部分组成，一部分是内存中的 double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过 memcpy 函数将脏页先复制到内存中的该区域，之后通过 double write buffer 再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免操作系统缓冲写带来的问题。在完成double write 页的写入后，再将 double wirite buffer 中的页写入各个表空间文件中。

在这个过程中，doublewrite 是顺序写，开销并不大，在完成 doublewrite 写入后，在将 double write buffer写入各表空间文件，这时是离散写入。

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB 存储引擎可以从共享表空间中的double write 中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志。

InnoDB 存储引擎第二部分：后台线程

IO 线程

在 InnoDB 中使用了大量的 AIO（Async IO） 来做读写处理，这样可以极大提高数据库的性能。在 InnoDB 1.0 版本之前共有4个 IO Thread，分别是 write，read，insert buffer和log thread，后来版本将 read thread和 write thread 分别增大到了4个，一共有10个了。

• - read thread ： 负责读取操作，将数据从磁盘加载到缓存page页。4个

• - write thread：负责写操作，将缓存脏页刷新到磁盘。4个

• - log thread：负责将日志缓冲区内容刷新到磁盘。1个

• - insert buffer thread ：负责将写缓冲内容刷新到磁盘。1个

Purge 线程

事务提交之后，其使用的 undo 日志将不再需要，因此需要 Purge Thread 回收已经分配的 undo 页。show variables like '%innodb*purge*threads%';

Page Cleaner 线程

作用是将脏数据刷新到磁盘，脏数据刷盘后相应的 redo log 也就可以覆盖，即可以同步数据，又能达到 redo log 循环使用的目的。会调用write thread线程处理。show variables like '%innodb*page*cleaners%';

InnoDB 存储引擎第三部分：磁盘文件

InnoDB 的主要的磁盘文件主要分为三大块：一是系统表空间，二是用户表空间，三是 redo 日志文件和归档文件。

二进制文件（binlong）等文件是 MySQL Server 层维护的文件，所以未列入 InnoDB 的磁盘文件中。

系统表空间和用户表空间

系统表空间包含 InnoDB 数据字典（元数据以及相关对象）并且 double write buffer , change buffer , undo logs 的存储区域。

系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。

系统表空间是一个共享的表空间，因为它是被多个表共享的。

系统表空间是由一个或者多个数据文件组成。默认情况下,1个初始大小为10MB，名为 ibdata1 的系统数据文件在MySQL的data目录下被创建。用户可以使用 innodb_data_file_path 对数据文件的大小和数量进行配置。

innodb_data_file_path 的格式如下：

innodb_data_file_path=datafile1[,datafile2]...

用户可以通过多个文件组成一个表空间，同时制定文件的属性:

innodb_data_file_path = /db/ibdata1:1000M;/dr2/db/ibdata2:1000M:autoextend

这里将 /db/ibdata1 和 /dr2/db/ibdata2 两个文件组成系统表空间。如果这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能。两个文件的文件名之后都跟了属性，表示文件 ibdata1 的大小为1000MB，文件 ibdata2 的大小为1000MB，而且用完空间之后可以自动增长。

设置 innodb_data_file_path 参数之后，所有基于 InnoDB 存储引擎的表的数据都会记录到该系统表空间中，如果设置了参数 innodb_file_per_table ，则用户可以将每个基于 InnoDB 存储引擎的表产生一个独立的用户空间。

用户表空间的命名规则为：表名.ibd。通过这种方式，用户不用将所有数据都存放于默认的系统表空间中，但是用户表空间只存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的系统表空间中。

下图显示 InnoDB 存储引擎对于文件的存储方式，其中frm文件是表结构定义文件，记录每个表的表结构定义。

REDO ログ ファイル (REDO ログ ファイル) とアーカイブ ファイル

デフォルトでは、InnoDB ストレージ エンジンのデータ ディレクトリに ib_logfile0 と ib_logfile1 という名前の 2 つのファイルが存在します。 InnoDB REDO ログ ファイル。InnoDB ストレージ エンジンのトランザクション ログを記録します。

InnoDB のデータ ストレージ ファイルでエラーが発生した場合、REDO ログ ファイルが役に立ちます。 InnoDB ストレージ エンジンは、REDO ログ ファイルを使用してデータを正しい状態に復元し、データの正確性と整合性を確保できます。

各 InnoDB ストレージ エンジンには少なくとも 1 つの REDO ログ ファイルがあり、各ファイル グループには少なくとも 2 つの REDO ログ ファイルと、デフォルトの ib_logfile0 および ib_logfile1 があります。

より高い信頼性を得るために、ユーザーは複数のミラー ログ グループを設定し、異なるファイル グループを異なるディスクに配置して、REDO ログの高可用性を向上させることができます。

ログ グループ内の各 REDO ログ ファイルのサイズは同じで、[ループ書き込み] モードで実行されます。 InnoDB ストレージ エンジンは、最初に REDO ログ ファイル 1 に書き込みます。ファイルがいっぱいになると、REDO ログ ファイル 2 に切り替わります。REDO ログ ファイル 2 もいっぱいになると、REDO ログ 1 に切り替わります。

ユーザーは Innodb_log_file_size を使用して REDO ログ ファイルのサイズを設定できます。これは InnoDB ストレージ エンジンのパフォーマンスに大きな影響を与えます。

REDO ログ ファイルの設定が大きすぎると、データが失われたときに回復に時間がかかる可能性があります。一方、REDO ログ ファイルの設定が小さすぎると、REDO ログ ファイルが小さくなりすぎます。チェックではダーティ ページをディスクに頻繁にフラッシュする必要があるため、パフォーマンスのジッターが発生します。

REDO ログ フラッシュ メカニズム

InnoDB は、データ ファイルとログ ファイルをフラッシュするために WAL (書き込み先行 REDO ログ) と Force-log-at- に従います。どちらもトランザクションの耐久性を保証します。 WAL では、データ変更をディスクに書き込む前に、まずメモリ内のログをディスクに書き込む必要があり、強制ログアットコミットでは、トランザクションがコミットされるときに、生成されたすべてのログをディスクにフラッシュする必要があります。リフレッシュが成功した場合、バッファー・プール内のデータがディスクにリフレッシュされる前にデータベースがクラッシュした場合、データベースは再始動時にログからデータをリカバリーできます。

上の図に示すように、InnoDB がバッファ プール内のデータを変更すると、まず関連する変更が REDO ログ バッファに書き込まれ、次に時間通りに更新されます。 (毎秒更新など) メカニズム)、またはトランザクションがコミットされたときにディスクに書き込まれます。これは、コミット時にログを強制する原則と一致します。REDO ログがディスクに書き込まれると、変更されたデータはバッファ プールは、WAL の原則と一致するチェックポイント メカニズムに従ってディスクに書き込まれます。

チェックポイントタイミングの仕組みでは、REDOログファイルが満杯であるとの判断があるため、前述したようにREDOログファイルが小さすぎて頻繁に満杯になると、チェックポイントが頻繁に変更されてしまいます。データがディスクに書き込まれるため、パフォーマンスのジッターが発生します。

オペレーティング システムのファイル システムにはキャッシュがあり、InnoDB がディスクにデータを書き込む場合、データはファイル システムのキャッシュにのみ書き込まれるため、実際の「安全性」はありません。

InnoDB の innodb_flush_log_at_trx_commit 属性は、トランザクションがコミットされるたびに InnoDB の動作を制御できます。属性値が 0 の場合、トランザクションがコミットされると、REDO ログは書き込まれませんが、メインスレッドが時間通りに書き込むのを待ちます。属性値が 1 の場合、トランザクションがコミットされると、REDO ログは書き込まれます。システムはファイル システムをキャッシュし、fsync を呼び出して、データ損失が発生しないようにファイル システム バッファ内のデータをディスク ストレージに実際に書き込みます。属性値が 2 の場合、ログ ファイルも書き込まれます。キャッシュしますが、fsync は呼び出しませんが、キャッシュをいつディスクに書き込むかをファイル システムに決定させます。

ログ フラッシュ メカニズムを次の図に示します。

Innodb_flush_log_at_commit は、InnoDB の書き込み効率とデータの安全性に関係する、InnoDB パフォーマンス チューニングの基本パラメータです。パラメータ値が 0 の場合、書き込み効率は最も高くなりますが、データのセキュリティは最も低くなります。パラメータ値が 1 の場合、書き込み効率は最も低くなりますが、データのセキュリティは最も高くなります。パラメータ値が 2 の場合は、データのセキュリティが最も高くなります。 、両方とも中レベルであり、一般に属性を設定することをお勧めします。値はセキュリティを高めるために 1 に設定され、1 に設定された場合にのみトランザクションの耐久性が保証されます。

UPDATE ステートメントを使用して InnoDB ストレージ エンジンの詳細を確認する

InnoDB ストレージ エンジンの基本アーキテクチャについての上記の紹介では、次のようになります。 UPDATEをもう一度分析してみましょう データ更新の具体的なプロセス。

この図は上下に分かれており、上が MySQL Server 層の処理フロー、下が MySQL InnoDB ストレージ エンジンの処理フローです。

MySQL サーバー層の処理プロセス

• さまざまなユーザー操作により、Web プロジェクトに付属するデータベース接続プール (dbcp、c3p0、druid など) を介してバックグラウンド SQL 実行がトリガーされ、データベース サーバーのデータベース接続プールとのネットワーク接続が確立されます。

##データベース接続プール内スレッドはリクエストを監視した後、受信した SQL ステートメントを使用して SQL インターフェイスを介してクエリ パーサーに応答します。クエリ パーサーは SQL 構文に従って SQL を解析します。クエリするテーブルのどのフィールドとクエリ条件を決定します。
その後、クエリ オプティマイザによって処理され、sq に最適な実行プランが選択されます。
次に、エグゼキュータは、ストレージ エンジンの一連のインターフェイスを呼び出して実行する責任があります。この計画により、SQL ステートメント全体の実行が完了します。
この部分は、このプロセスは基本的に、上で分析した選択リクエスト処理プロセスの分析と一致しています。

##特定の実行ステートメントは、ストレージ エンジンによって完了する必要があります。上の図に示すように:

• 同時に、同時に更新されるデータのセキュリティを確保するために、このデータは最初にロックされ、他のトランザクションが更新されないようにします。

• 次に、更新する前に値をバックアップし、それを元に戻すログに書き込みます (トランザクションがロールバックされたときに古いデータをフェッチしやすくするため)。たとえば、update ステートメントは値を保存します。更新されたフィールドの前。

• バッファプール内のキャッシュデータを最新のデータに更新すると、この時のメモリ上のデータはダーティデータになります(メモリ上のデータとディスク上のデータはinconsistent)

#バッファ プールでの実行プロセスが完了しました (上の図を参照)。

Binlog を配置した後、Binlog ファイル名、ファイル パス情報、およびコミット マークが、同期されたシーケンスで REDO ログに書き込まれます。この手順の重要性は、REDO ログと binlog ログの一貫性を保つことです。コミット マークは、トランザクションが正常に送信されたかどうかを判断するための重要な基準です。たとえば、ステップ 5 またはステップ 6 が正常に実行された後に MySQL がクラッシュした場合、今回は REDO ログに最終トランザクションのコミット マークがないため、このトランザクションは失敗と判断される可能性があります。 REDO ログ ファイルにはこの更新のログはありませんが、binlog ログ ファイルにはこの更新のログは存在しないため、データの不整合の問題は発生しません。

上記の完了後、メモリ データが変更され、トランザクションが送信され、ログがディスクに配置されますが、ディスク データは同期的に変更されていません。 InnoDB ストレージ エンジンのバックグラウンドには IO スレッドがあり、データベースの負荷が低いピーク時には、トランザクションによって更新されたものの、まだディスクに書き込まれる時間がないバッファ プール内のデータ (ダーティ データ) 、ディスク データとメモリ データが利用できなくなるため (一貫性のある)、トランザクションの永続化を完了するためにディスクにフラッシュされます。

したがって、InnoDB の作成プロセスは次の図で表すことができます。

推奨される学習: mysql ビデオ チュートリアル
#

以上が刺激的な mysql アーキテクチャと InnoDB ストレージ エンジンの知識の詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。