MySQL InnoDBエンジンのインデックスとストレージ構造の詳細な説明

序文

Oracle や SQL Server などのデータベースには、ストレージ エンジンが 1 つだけあり、すべてのデータ ストレージ管理メカニズムは同じです。

MySQL データベースはさまざまなストレージ エンジンを提供します。ユーザーは、さまざまな要件に応じてデータ テーブルに異なるストレージ エンジンを選択したり、ニーズに応じて独自のストレージ エンジンを作成したりできます。

MySQLの主なストレージエンジンの違い

MySQL のデフォルトのストレージ エンジンは MyISAM です。他によく使用されるものとしては、InnoDB、MERGE、MEMORY (HEAP) などがあります。

いくつかの主要なストレージエンジン

MyISAM は非トランザクション テーブルを管理し、高速なストレージと取得、および全文検索機能を提供します。

MyISAM は MySQL のデフォルトのストレージ エンジンです。 create で新しいテーブルを作成するときに、新しいテーブルのストレージ エンジンが指定されていない場合は、デフォルトで MyISAM が使用されます。各 MyISAM はディスク上の 3 つのファイルに保存されます。ファイル名はテーブル名と同じで、拡張子は .frm (テーブル定義を保存)、.MYD (MYData、データを保存)、.MYI (MYIndex、インデックスを保存) になります。データ ファイルとインデックス ファイルを異なるディレクトリに配置することで、IO を均等に分散し、速度を向上できます。

InnoDB ストレージ エンジンはトランザクション処理アプリケーションに使用され、コミット、ロールバック、クラッシュ回復機能によるトランザクションの安全性を提供する ACID トランザクション サポートなどの多くの機能を備えています。ただし、MyISAM ストレージ エンジンと比較すると、InnoDB は書き込み効率が低く、データとインデックスを保持するためにより多くのディスク領域を占有します。

メモリはすべてのデータをメモリ内に保存し、一時テーブルで使用して、参照やその他の同様のデータの迅速な検索が必要な環境で非常に高速なアクセスを提供できます。メモリはハッシュインデックスを使用するため、データアクセス速度が非常に高速です。

マージを使用すると、MySQL DBA または開発者は、同等の MyISAM テーブルを論理的にグループ化し、1 つのオブジェクトとして参照できます。データ ウェアハウスなどの VLDB 環境に非常に適しています。

さまざまなストレージエンジンの水平比較

ストレージエンジン操作の表示と構成

1. show engines; コマンドを使用して、現在のデータベースでサポートされているストレージ エンジンを表示します。

2. テーブル定義構造やその他の情報を表示するには、次のコマンドを使用します。

Desc[ribe] tablename; //データテーブルの構造を表示します。Show create table tablename; //テーブル作成時に指定したENGINEを表示するためのテーブル作成ステートメントを表示します。
show table status like 'tablename'\G はテーブルの現在のステータス値を表示します

3. テーブルのストレージエンジンを設定または変更する

データベース テーブルを作成するときにストレージ エンジンを設定するための基本的な構文は次のとおりです。

テーブルtableName(を作成します
columnName (列名1) type (データ型) attri (属性設定)、
columnName (列名2) type (データ型) attri (属性設定)、
……..) エンジン = エンジン名

ストレージエンジンを変更するには、次のコマンドを使用します。

テーブル tableName エンジン =engineName を変更します

サーバー全体またはソリューション全体で同じストレージ エンジンを使用する必要はありません。ソリューション内の各テーブルに異なるストレージ エンジンを使用できます。

InnoDB ストレージ構造

InnoDB はページ ストレージ構造を使用します。以下は InnoDB のテーブル スペース構造図です。

ページの保存形式は次の図に示されています。

ページのストレージは次の部分で構成されます。

1. ページ ヘッダー: ページの制御情報を記録し、ページの左右の兄弟ページ ポインター、ページ スペースの使用状況など、合計 150 バイトを占めます。ページ ヘッダーの詳細については、次の記事で説明します。
2. 最小仮想レコードと最大仮想レコード: 固定された場所に保存される 2 つの仮想レコード。それ自体はデータを保存しません。最小の仮想レコードはどのレコードよりも小さく、最大の仮想レコードはどのレコードよりも大きくなります。
3. レコードヒープ: 上図のオレンジ黄色の部分を指します。ページが割り当てられているレコード領域を示し、インデックス データの実際の格納領域でもあります。レコード ヒープには、有効なレコードと削除されたレコードの 2 種類があります。有効なレコードはインデックスによって通常使用されるレコードですが、削除されたレコードはインデックスから削除され、使用されなくなったレコードです (上の図の濃い青色の部分を参照)。レコードの更新と削除が頻繁に行われるようになると、レコードの山の中に削除されたレコードが増え、つまり穴（断片）が増えていきます。これらの削除されたレコードは連結され、ページの空き領域リストを形成します。
4. 未割り当て領域: ページによって使用されていないストレージ領域を指します。ページが継続的に使用されると、未割り当て領域はますます小さくなります。新しいレコードが挿入されると、まず空き領域リストから適切な保存場所（十分なスペースがある）を取得しようとします。適切な保存場所がない場合は、未割り当て領域でそれを申請します。
5. スロット領域: スロットは、ページの有効なレコードへのポインタです。各スロットは 2 バイトを占め、ページの最初のアドレスを基準としたレコードのオフセットを格納します。ページに n 個の有効なレコードがある場合、スロットの数は n/8+2 から n/4+2 の間になります。次のセクションでは、ページ順序の記録とバイナリ検索の鍵となるスロット領域について詳しく説明します。
6. ページ テイラー: ページの最後の部分で、8 バイトを占め、主にページの検証情報を格納します。

ページ ヘッダー、最大/最小仮想レコード、およびページ フッターはすべて、ページ内で固定された保存場所を持ちます。

InnoDB インデックス構造

InnoDB はインデックスを保存するために B+Tree を使用します。

Innodb テーブルには複数のインデックスが含まれる場合があり、各インデックスは B+ ツリーを使用して格納されます。インデックスには、クラスター化インデックスとセカンダリ インデックスが含まれます。クラスター化インデックスは、テーブルの主キーをインデックス キーとして使用し、テーブルのすべてのフィールドを含みます。セカンダリ インデックスには、インデックス キーとクラスター化インデックス キー (プライマリ キー) の内容のみが含まれ、他のフィールドは含まれません。各インデックスは B+ ツリーです。各 B+ ツリーは多数のページで構成され、各ページのサイズは通常 16K です。 B+ ツリーの組織構造から、B ツリーのページは次のように分割できます。

リーフ ノード: B ツリーのレベル 0 にあるページで、記録されたすべてのコンテンツが格納されます。
非リーフ ノード: インデックス キーとページ ポインターのみを格納する、B ツリー レベルが 0 より大きいページ。

典型的な B+ ツリー構造:

上の図からわかるように、同じレベルのページは二重リンクリストによって接続されています。

一般的に言えば、B+ ツリーの左端のリーフ ノードから始めて右にスキャンすると、小さいものから大きいものまで B+ ツリーのすべてのデータを取得できます。したがって、リーフノードには次の特性があります。

ページ内のデータはインデックス キーによってソートされます。

ページ内の任意のレコードのインデックス キー値は、その左側の兄弟ページ内の任意のレコードの値より小さくありません。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。