MySQL共通ストレージエンジンの機能と使用方法の詳細な説明

この記事では、一般的な MySQL ストレージ エンジンの機能と使用方法を例を使って説明します。ご参考までに、詳細は以下の通りです。

MySQL ストレージ エンジンには主に 2 つのカテゴリがあります。

1. トランザクションセーフテーブル: InnoDB、BDB。

2. 非トランザクションセーフテーブル: MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED など。

MySQL のデフォルトのストレージ エンジンは MyISAM です (バージョン 5.7 のデフォルトは InnoDB です)。

構成ファイルでデフォルトのストレージ エンジン パラメータ default-table-type を設定します。

現在のデータベースでサポートされているストレージ エンジンを照会します。

エンジンを表示します。
'have%' のような変数を表示します。

現在のデフォルトのストレージ エンジンを表示します。

'%table_type%' のような変数を表示します。

新しいテーブルを作成するときにストレージ エンジンを指定します。

テーブルを作成します(...) engine=MyISAM;

以下では、よく使用される 4 つのストレージ エンジン (MyISAM、InnoDB、MEMORY、MERGE) について詳しく紹介します。

1. マイISAM

1. データファイル:

MyISAM データ テーブルは、テーブル名と同じファイル名を持つ 3 つのファイルとしてディスクに保存され、拡張子は次のとおりです。

(1).frm: データテーブル構造定義を格納します。

（２）MYD：テーブルデータを格納します。

（３）MYI：ストレージテーブルインデックス。

その中で、データ ファイルとインデックス ファイルを異なるディレクトリに配置することで、IO を均等に分散し、より高速な速度を実現できます。インデックス ファイルとデータ ファイルのパスを指定します。これは、テーブルを作成するときに、データ ディレクトリとインデックス ディレクトリのステートメントを使用して指定する必要があります。 (ファイルパスは絶対パスで、アクセス権限が必要です)

MyISAM テーブルはさまざまな理由で破損する場合があります。破損したテーブルはアクセスできない可能性があり、修復が必要であることを示すメッセージが表示されたり、アクセス後にエラー結果が返されたりする場合があります。 check table ステートメントを使用して MyISAM テーブルの健全性をチェックし、repair table ステートメントを使用して破損した MyISAM テーブルを修復できます。

2. 保存形式:

（１）静的テーブル（デフォルト）：フィールドはすべて可変長ではありません（各レコードは固定長です）。ストレージは非常に高速で、キャッシュしやすく、障害からの回復も簡単です。通常、動的テーブルよりも多くのスペースを占有します。

（2）動的テーブル：占有スペースは比較的少ないですが、レコードの更新や削除が頻繁に行われるため、断片化が発生します。パフォーマンスを向上させるには、定期的にoptimize tableまたはmyisamchk -rコマンドを実行する必要があります。また、障害発生時の復旧が困難です。

（3）圧縮テーブル：myisampackツールを使用して作成され、ディスク容量をほとんど占有しません。各レコードは個別に圧縮されるため、アクセスのオーバーヘッドはほとんどありません。

静的テーブル内のデータは、保存時に列幅の定義に従ってスペースが埋め込まれ、データがアプリケーションに返される前にこれらのスペースが削除されます。保存するコンテンツの後にスペースがある場合は、結果が返されるときにスペースが削除されます。 (実際、これはデータ型 char の動作です。このデータ型が動的テーブルにある場合、同じ問題が発生します。)

(静的テーブルと動的テーブルは、使用されている列のタイプに基づいて自動的に選択されます。)

3. 利点と欠点:

（１）利点：アクセス速度が速い。

（２）トランザクションや外部キーをサポートしていない。

4. 適用可能な状況:

アプリケーションが主に読み取りと挿入操作に基づいており、更新と削除の操作はわずかで、トランザクションの整合性と同時実行性の要件がそれほど高くない場合は、このストレージ エンジンが非常に適しています。 MyISAM は、Web、データ ウェアハウス、その他のアプリケーション環境で最も一般的に使用されるストレージ エンジンの 1 つです。

2. バイナリ

1. 保管方法：

InnoDB は、テーブルとインデックスを次の 2 つの方法で保存します。

(1) 共有テーブルスペースストレージを使用する: この方法で作成されたテーブル構造は.frmファイルに保存され、データとインデックスはinnodb_data_home_dirとinnodb_data_file_pathで定義されたテーブルスペースに保存されます。これらのファイルは複数にすることができます。

（2）マルチテーブルスペースストレージを使用する：この方法で作成されたテーブル構造は.frmファイルに保存されますが、各テーブルのデータとインデックスは.idbファイルに個別に保存されます。パーティション テーブルの場合、各パーティションは個別の .idb ファイルに対応します。ファイル名は「テーブル名 + パーティション名」です。パーティションを作成するときに、パーティションごとにデータ ファイルの場所を指定して、テーブルの IO を複数のディスクに均等に分散できます。

マルチテーブルスペースストレージ方式を使用するには、パラメータ innodb_file_per_table を設定し、サーバーを再起動して有効にする必要があります。この設定は新しく作成されたテーブルにのみ有効になります。複数の表領域のデータ ファイルにはサイズ制限はありません。初期サイズ、最大ファイル制限、拡張サイズなどのパラメータを設定する必要はありません。マルチテーブルスペースストレージモードの場合でも、共有テーブルスペースは必要です。InnoDB は、内部データディクショナリと作業ログをこのファイルに保存します。そのため、マルチテーブルスペース機能を使用するテーブルをバックアップするときに、.idb ファイルを直接コピーすることはできません。次のコマンドを使用して、データバックアップをデータベースに復元できます。

ALTER TABLE tbl_name でテーブルスペースを削除します。
ALTER TABLE tbl_name IMPORT TABLESPACE;

ただし、これではテーブルを元のデータベースに復元することしかできません。別のデータベースに復元する必要がある場合は、mysqldump と mysqlimport を使用する必要があります。

2. データファイル:

InnoDB データ ファイルは、テーブルの保存方法によって決まります。

(1) 共有テーブルスペースファイル: パラメータ innodb_data_home_dir および innodb_data_file_path によって定義され、データ辞書やログなどを保存するために使用されます。

（2）.frm: テーブル構造定義を格納します。

（3）.idb：このファイルは、複数の表領域ストレージ方法を使用する場合に、表データとインデックスを格納するために使用されます。共有表領域ストレージを使用する場合、このファイルは存在しません。

3. 外部キー制約:

InnoDB は、外部キー制約をサポートする唯一の MySQL エンジンです。外部キー制約により、データベースは外部キーを通じてデータの整合性と一貫性を確保できますが、外部キーを導入すると速度とパフォーマンスが低下します。外部キーを作成する場合、親テーブルには対応するインデックスが必要です。また、外部キーを作成すると、子テーブルでは対応するインデックスが自動的に作成されます。

外部キー制約の使用例:

テーブル `dep` を作成します (
 `id` smallint(6) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) デフォルト NULL,
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8;
テーブル `emp` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) デフォルト NULL,
 `dep_id` smallint(6) NOT NULL,
 主キー (`id`)、
 キー `idx_fk_dep_id` (`dep_id`)、
 制約 `fk_emp_dep` 外部キー (`dep_id`) 参照 `dep` (`id`) ON UPDATE CASCADE
)ENGINE=InnoDB デフォルト文字セット=utf8;

KEY : インデックス制約名を定義します。

CONSTRAINT : 外部キー制約名を定義します。 (データベース内で一意である必要があります。指定しない場合は、システムが自動的に制約名を生成します)

ON : 親テーブル操作が子テーブルに与える影響を指定します (定義されていない場合は、デフォルトで制限が使用されます)。

制限あり、アクションなし: 子テーブルに関連レコードがある場合、親テーブルを更新または削除することはできません。

カスケード: 親テーブルが更新または削除されると、子テーブル内の対応するレコードも更新または削除されます。

null を設定: 親テーブルが更新または削除されると、子テーブルの対応するフィールドが null に設定されます。

テーブルが別のテーブルの外部キーによって参照されている場合、このテーブルの対応するインデックスまたは主キーは削除できません。複数のテーブルからデータをインポートするときに、テーブルのインポート順序を無視する必要がある場合は、外部キー チェックを一時的に無効にすることができます。データのロードやテーブルの変更操作を実行するときに、外部キー制約を一時的に無効にすることで、処理を高速化することもできます。

シャットダウンコマンド:

foreign_key_checks=0 を設定します。

開くコマンド:

foreign_key_checks=1 を設定します。

4. 利点と欠点:

（1）利点：コミット、ロールバック、クラッシュリカバリ機能を備えたトランザクションセキュリティを提供します。

（2）デメリット：MyISAMと比較すると、InnoDBは書き込み処理効率が低く、データとインデックスを保持するためにより多くのディスク領域を占有します。

5. 適用可能な状況:

アプリケーションがトランザクションの整合性に対して比較的高い要件を持ち、同時実行条件下でデータの一貫性を必要とし、データ操作に挿入やクエリに加えて多くの更新や削除の操作が含まれる場合は、InnoDB ストレージ エンジンがより適切な選択肢になります。 InnoDB ストレージ エンジンは、削除や更新によって発生するロックを効果的に削減するだけでなく、トランザクションの送信とロールバックを完全に保証することもできます。請求システムや金融システムなど、データの精度に対する要件が高いシステムの場合、InnoDB は適切な選択肢です。

3. 記憶

1. データファイル:

各 MEMORY テーブルは 1 つの .frm ディスク ファイルのみに対応し、テーブル構造定義を格納するために使用され、テーブル データはメモリに格納されます。デフォルトでは、BTREE インデックスの代わりに HASH インデックスが使用されます。

2. 利点と欠点:

（１）利点：データがメモリに保存されるため、アクセス速度が非常に速い。

（２）デメリット：サービスが停止するとテーブル内のデータは失われます。テーブルのサイズには制限があります。

3. 適用可能な状況:

メモリ ストレージ エンジンは、主に、内容が頻繁に変更されないコード テーブルや、統計演算の中間結果テーブルとして使用され、中間結果を効率的に分析して最終的な統計結果を取得します。

マージ

1. エンジン原理：

Merge ストレージ エンジンは、MyISAM テーブルのグループの組み合わせです。これらの MyISAM テーブルは、まったく同じ構造を持つ必要があります。マージ テーブル自体にはデータは含まれません。マージ タイプのテーブルは、クエリ、更新、および削除できます。これらの操作は、実際の内部 MyISAM テーブルに対して実行されます。

insert_method 句を使用して、マージ テーブルの挿入操作を定義します。first または last を使用すると、挿入操作はそれぞれ最初のテーブルまたは最後のテーブルに対して実行されます。定義しないか、No として定義すると、このマージ テーブルに対して挿入操作を実行できません。マージ テーブルに対するドロップ操作では、マージの定義のみが削除され、内部テーブルには影響しません。

2. データファイル:

(1).frm: テーブル定義を格納します。

（２）MRG：結合テーブルがどのテーブルで構成されているか、新しいデータを挿入するための基準など、結合テーブルに関する情報を格納します。 .mrg ファイルを変更することでマージ テーブルを変更できますが、変更後にテーブルをフラッシュする必要があります。

3. 使用例:

テーブル `m1` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) デフォルト NULL,
 主キー (`id`)
)ENGINE=MyISAM デフォルト文字セット=utf8;
テーブル `m2` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) デフォルト NULL,
 主キー (`id`)
)ENGINE=MyISAM デフォルト文字セット=utf8;
テーブル `m` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) デフォルト NULL,
 主キー (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`m1`,`m2`);

4. 適用可能な状況:

一連の同一の MyISAM テーブルを論理的にグループ化し、単一のオブジェクトとして参照するために使用されます。 MERGE テーブルの利点は、単一の MyISAM テーブルのサイズ制限を突破できることと、異なるテーブルを複数のディスクに分散することで、MERGE テーブルのアクセス効率を効果的に向上できることです。これは、データ ウェアハウスなどの VLDB 環境に非常に適しています。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL ストアド プロシージャ スキル」、「MySQL 共通関数の概要」、「MySQL ログ操作スキル」、「MySQL トランザクション操作スキルの概要」、および「MySQL データベース ロック関連スキルの概要」

この記事が皆様のMySQLデータベース設計に役立つことを願っています。