Mysql で一般的に使用される 4 つのストレージエンジンとその正しい選択方法の詳細な紹介-mysql チュートリアル-php.cn

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 &#39;have%&#39;;

ログイン後にコピー

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

show variables like &#39;%table_type%&#39;;

ログイン後にコピー

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

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 サイトの他の関連記事を参照してください。