Mysql で一般的に使用される 4 つのストレージ エンジンとその正しい選択方法の詳細な紹介

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

1. トランザクション セキュリティ テーブル: InnoDB、BDB。

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

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

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

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

show engines;
show variables like 'have%';

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

show variables like '%table_type%';

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

create table(...) engine=MyISAM;

以下は、一般的に使用される 4 つのストレージ エンジン、MyISAM、InnoDB、MEMORY、MERGE の詳細な紹介です。

1. MyISAM

1. データ ファイル:

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

(1).frm: ストレージデータテーブル構造定義。

(2).MYD: テーブルデータを格納します。

(3).MYI: ストレージテーブルインデックス。

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

MyISAM タイプ テーブルは、さまざまな理由で破損している可能性があります。破損したテーブルにはアクセスできない可能性があり、アクセスする必要があることを示すプロンプトが表示されます。修復またはアクセスできません。間違った結果が返されます。 check table ステートメントを使用して MyISAM テーブルの状態をチェックし、repair table ステートメントを使用して破損した MyISAM テーブルを修復できます。

2. 格納形式:

(1) 静的テーブル (デフォルト): フィールドは非可変長です (各レコードは固定長です)。ストレージは非常に高速で、キャッシュが容易で、障害からの回復も簡単です。通常、動的テーブルよりも多くのスペースを占有します。

(2) 動的テーブル: 使用するスペースは比較的少ないですが、レコードの頻繁な更新と削除により断片化が発生します。パフォーマンスを向上させ、比較を復元するには、テーブルの最適化または myisamchk -r コマンドを定期的に実行する必要があります。障害が発生した場合の困難さ。

(3) 圧縮テーブル: myisampack ツールを使用して作成され、使用するディスク領域はほとんどありません。各レコードは個別に圧縮されるため、アクセスのオーバーヘッドはほとんどありません。

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

(静的テーブルと動的テーブルは型に基づいて自動的に選択されます)

3. 長所と短所:

(1) 長所: 高速アクセス。

(2) トランザクションまたは外部キーはサポートされません。

4. 該当する状況:

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

2. InnoDB

#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 DISCARD TABLESPACE;
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唯一支持外键约束的引擎。外键约束可以让数据库自己通过外键保证数据的完整性和一致性，但是引入外键会使速度和性能下降。在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

外键约束使用示例：

CREATE TABLE `dep` (
 `id` smallint(6) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `emp` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 `dep_id` smallint(6) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_fk_dep_id` (`dep_id`),
 CONSTRAINT `fk_emp_dep` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

KEY ：定义索引约束名称。

CONSTRAINT：定义外键约束名称。（在数据库中应是唯一的，若不指定系统会自动生成一个约束名）

ON：指定父表操作对子表的影响（不定义默认采用restrict）。

Restrict和no action：在子表有相关记录的情况下父表不能更新或删除。

Cascade：在父表更新或删除时，同时更新或删除子表对应的记录。

Set null：在父表更新或删除的时候，子表的对应字段被设置为null。

当某个表被其他表创建了外键参照，那么这个表的对应索引或者主键禁止被删除。在导入多个表的数据时，如果需要忽略表的导入顺序，可以暂时关闭外键的检查；在执行load data和alter table操作的时候，也可以通过暂时关闭外键约束来加快处理的速度。

关闭命令：

set foreign_key_checks=0;

开启命令：

set foreign_key_checks=1;

4. 优劣势：

（1）优势：提供了具有提交、回滚和崩溃恢复能力的事务安全。

（2）劣势：相比MyISAM，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

5. 适用情况：

如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

三、MEMORY

1. 数据文件：

每个MEMORY表只对应一个.frm磁盘文件，用于存储表的结构定义，表数据存放在内存中。默认使用HASH索引，而不是BTREE索引。

2. 优劣势：

（1）优势：访问速度非常快，因为数据是存在内存中的。

（2）劣势：一旦服务关闭，表中的数据就会丢失；对表的大小有限制。

3. 适用情况：

Memory存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。

四、MERGE

1. 引擎原理：

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的MyISAM表进行的。

通过insert_method子句定义merge表的插入操作：使用first或last可以使插入操作被相应地作用在第一或最后一个表上，不定义或定义为No表示不能对这个merge表进行插入操作。对merge表进行drop操作只是删除了merge的定义，对内部的表没有任何影响。

2. 数据文件：

（1）.frm：存储表定义。

（2）.MRG：存储组合表的信息，包括merge表由哪些表组成、插入新数据时的依据。可以通过修改.mrg文件来修改merge表，但是修改后要通过flush tables刷新。

3. 使用示例：

CREATE TABLE `m1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `m2` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `m` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`m1`,`m2`);

4. 适用情况：

用于将一系列等同的MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE 表的访问效率。这对于诸如数据仓储等VLDB环境十分适合。

以上内容为大家详细介绍了四个常用数据库存储引擎，更多相关问题请访问PHP中文网：Mysql视频教程

以上がMysql で一般的に使用される 4 つのストレージ エンジンとその正しい選択方法の詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。