MySQL のストレージ エンジンの違いと比較

MyISAM ストレージエンジン

MyISAM は ISAM ストレージ エンジンに基づいており、それを拡張しています。これは、Web、データ ウェアハウス、その他のアプリケーション環境で最も一般的に使用されるストレージ エンジンの 1 つです。 MyISAM は挿入速度とクエリ速度が高速ですが、トランザクションをサポートしていません。

MyISAM の主な機能は次のとおりです。

1. 大きなファイル (最大 63 ビットの長さ) は、大きなファイルをサポートするファイル システムおよびオペレーティング システムでサポートされます。

2. 行のサイズが動的に変更されると、削除、更新、挿入の各操作が混在しても断片化が少なくなります。これは、隣接する削除されたブロックを結合し、次のブロックが削除された場合に次のブロックに拡張することによって自動的に実行されます。

3. 各 MyISAM テーブルのインデックスの最大数は 64 ですが、再コンパイルによって変更できます。インデックスあたりの列の最大数は16です。

4. インデックス列ではNULLが許可されており、この値は各キーに対して0〜1バイトを占めます。

5. データファイルとインデックスファイルを別のディレクトリに置くことができます（InnoDBは1つのディレクトリに置かれます）

MyISAM エンジンはインデックス構造としてB+Treeを使用し、リーフ ノードのデータ フィールドにデータ レコードのアドレスを格納します。

次の図は、MyISAM インデックスの概略図です。

ここでは、テーブルに 3 つの列があると仮定します。Col1 が主キーであると仮定すると、上図は MyISAM テーブルの主キーの図になります。

MyISAM インデックス ファイルには、データ レコードのアドレスのみが保存されていることがわかります。

MyISAM では、プライマリ インデックスとセカンダリ インデックス (セカンダリ キー) の間に構造上の違いはありませんが、プライマリ インデックスではキーが一意である必要があるのに対し、セカンダリ インデックス キーは繰り返すことができます。

Col2 にセカンダリ インデックスを作成すると、このインデックスの構造は次のようになります。

これはB+ ツリーでもあり、データ フィールドにはデータ レコードのアドレスが格納されます。

したがって、 MyISAM のインデックス検索アルゴリズムは、まず B+Tree 検索アルゴリズムに従ってインデックスを検索します。指定されたキーが存在する場合は、そのデータ フィールドの値が取り出され、次にデータ フィールドの値をアドレスとして使用して対応するデータ レコードが読み取られます。

MyISAM インデックス方式は「非クラスター化」とも呼ばれ、InnoDB のクラスター化インデックスと区別するためにこのように呼ばれています。

InnoDB ストレージ エンジン

InnoDB は、トランザクション データベースに適したエンジンです。トランザクション セーフ テーブル (ACID)、行ロック、外部キーをサポートしています。上の図からわかるように、 InnoDB はデフォルトの MySQL エンジンです。

InnoDB の主な機能は次のとおりです。

1. InnoDB は、コミット、ロールバック、クラッシュリカバリ機能を備えたトランザクションセーフ (ACID 準拠) ストレージ エンジンを MySQL に提供します。 InnoDB は行レベルでロックし、SELECT ステートメントで Oracle のような非ロック読み取りも提供します。これらの機能により、マルチユーザー展開とパフォーマンスが向上します。 SQL クエリでは、同じクエリ内であっても、InnoDB テーブルと他の MySQL テーブル タイプを自由に混在させることができます。

2. InnoDB は、膨大な量のデータを処理する際に最大のパフォーマンスを発揮するように設計されています。その CPU 効率は、おそらく他のディスクベースのリレーショナル データベース エンジン ロックとは比べものにならないほど優れています。

3. InnoDB ストレージ エンジンは MySQL サーバーと完全に統合されています。InnoDB ストレージ エンジンは、データとインデックスをメイン メモリにキャッシュするための独自のバッファー プールを維持します。 InnoDB は、テーブルとインデックスを論理テーブルスペースに保存します。このテーブルスペースには、複数のファイル (または生のディスク ファイル) を含めることができます。これは、各テーブルが個別のファイルに保存されるMyISAM テーブルとは異なります。 InnoDB テーブルは、ファイル サイズが 2 GB に制限されているオペレーティング システムでも、任意のサイズにすることができます。

4. InnoDB は外部キー整合性制約をサポートしています。テーブルにデータを格納する場合、各テーブルは主キーの順序で格納されます。テーブルの定義時に主キーが指定されていない場合、InnoDB は各行に対して 6 バイトの ROWID を生成し、それを主キーとして使用します。

InnoDB もインデックス構造として B+Tree を使用しますが、その具体的な実装は MyISAM とはまったく異なります。

最初の大きな違いは、InnoDB のデータ ファイル自体がインデックス ファイルであることです。

上記から、 MyISAM インデックス ファイルとデータ ファイルは別々であり、インデックス ファイルにはデータ レコードのアドレスのみが保存されることがわかります。

InnoDB では、テーブル データ ファイル自体が B+ ツリーとして編成されたインデックス構造であり、このツリーのリーフ ノード データ フィールドに完全なデータ レコードが格納されます。

このインデックスのキーはデータ テーブルの主キーであるため、InnoDB テーブル データ ファイル自体が主インデックスになります。

上図は、InnoDB プライマリ インデックス (データ ファイルでもある) の概略図です。リーフ ノードに完全なデータ レコードが含まれていることがわかります。

このタイプのインデックスはクラスター化インデックスと呼ばれます。 InnoDB のデータ ファイル自体は主キーによってクラスター化されているため、 InnoDB ではテーブルに主キーが必要です(MyISAM には主キーがない場合があります)。明示的に指定されていない場合、MySQL システムはデータ レコードを一意に識別できる列を主キーとして自動的に選択します。そのような列が存在しない場合、MySQL は InnoDB テーブルの暗黙的なフィールドを主キーとして自動的に生成します。このフィールドは 6 バイト長で、長整数型です。

MyISAM インデックスとの 2 番目の違いは、InnoDB 補助インデックス データ フィールドに、アドレスではなく、対応するレコードの主キーの値が格納されることです。つまり、InnoDB のすべてのセカンダリ インデックスは、データ フィールドとしてプライマリ キーを参照します。

たとえば、次の図は Col3 に定義された補助インデックスを示しています。

ここでは、英語文字の ASCII コードを比較基準として使用します。クラスター化インデックスの実装により、主キーによる検索は非常に効率的になりますが、補助インデックス検索では 2 つのインデックス検索が必要になります。最初に補助インデックスを検索して主キーを取得し、次に主キーを使用して主インデックスからレコードを取得します。

さまざまなストレージ エンジンのインデックス実装方法を理解することは、インデックスを正しく使用して最適化するために非常に役立ちます。たとえば、InnoDB のインデックス実装を理解すれば、長すぎるフィールドを主キーとして使用することが推奨されない理由を簡単に理解できます。これは、すべてのセカンダリ インデックスがプライマリ インデックスを参照し、プライマリ インデックスが長すぎるとセカンダリ インデックスが大きくなりすぎるためです。

別の例として、InnoDB データ ファイル自体が B+Tree であるため、InnoDB の主キーとして非単調フィールドを使用することはお勧めできません。非単調な主キーでは、B+Tree の特性を維持するために、新しいレコードを挿入するときにデータ ファイルが頻繁に分割および調整されるため、非常に非効率的です。自動増分フィールドを主キーとして使用するのは良い選択です。

メモリストレージエンジン

MEMORY ストレージ エンジンは、メモリ内のテーブルにデータを保存し、他のテーブルのデータを照会または参照することなく高速アクセスを提供します。

MEMORY の主な機能は次のとおりです。

1. 各 MEMORY テーブルには最大 32 個のインデックス、インデックスあたり 16 個の列、最大キー長 500 バイトを設定できます。

2. MEMORYストレージエンジンはHASHとBTREEミクロコスモスを実行します

3. MEMORYテーブルには一意でないキー値を持つことができます

4. MEMORYテーブルは固定レコード長形式を使用する

5. MEMORYはBLOBまたはTEXT列をサポートしていません

6. MEMORYはAUTO_INCREMENT列とNULL値を含むことができる列のインデックスをサポートします。

7. MEMORY テーブルはすべてのクライアント間で共有されます (他の非 TEMPORARY テーブルと同様に)

8. MEMORY テーブルのメモリはメモリに保存されます。メモリは、MEMORY テーブルと、クエリが処理されるときにサーバーによって作成される内部テーブルによって共有されます。

9. MEMORYテーブルの内容が不要になった場合は、MEMORYテーブルが使用しているメモリを解放するために、DELETE FROMまたはTRUNCATE TABLEを実行するか、テーブル全体を削除する必要があります（DROP TABLEを使用）。

アーカイブストレージエンジンストレージエンジンの選択

次の表に示すように、さまざまなストレージ エンジンには、さまざまなニーズを満たすための独自の特性があります。

InnoDB : コミット、ロールバック、クラッシュリカバリ機能を備えたトランザクションの安全性（ACID互換性）機能を提供し、同時実行制御が必要な場合は、InnoDBが適しています。

InnoDB と MyISAM の違い:

1>. InnoDBはトランザクションをサポートしますが、MyISAMはトランザクションをサポートしません

2>.InnoDBは行レベルのロックをサポートし、MyISAMはテーブルレベルのロックをサポートします。

3>.InnoDBはMVCCをサポートしていますが、MyISAMはサポートしていません

4>.InnoDBは外部キーをサポートしますが、MyISAMはサポートしません

5>.InnoDB はフルテキスト インデックスをサポートしていませんが、MyISAM はサポートしています。 （バツ）

MyISAM : データ テーブルが主にレコードの挿入とクエリに使用される場合、MyISAM エンジン (トランザクションはサポートされていません) を使用すると、より高い処理効率が得られます。

メモリ: 一時的にデータを保存するだけでよく、データ量が多くなく、高いデータ セキュリティが要求されない場合は、メモリ エンジンにデータを保存することを選択できます。MySQL は、このエンジンを一時テーブルとして使用して、クエリの中間結果を保存します。データは非常に速く処理されますが、セキュリティは高くありません。

アーカイブ: INSERT および SELECT 操作のみがある場合は、アーカイブを選択できます。アーカイブは、高同時挿入操作をサポートしますが、トランザクションセーフではありません。アーカイブは、ログ情報の記録など、アーカイブされたデータを保存するのに非常に適しています。

使用するエンジンを柔軟に選択する必要があります。データベース内の複数のテーブルでは、さまざまなパフォーマンスと実際のニーズを満たすために、異なるエンジンを使用できます。適切なストレージ エンジンを使用すると、データベース全体のパフォーマンスが向上します。

上記は私の個人的な経験です。参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。