關於Mysql中四種常用儲存引擎的詳細介紹以及如何正確選擇

MySQL儲存引擎主要有兩大類別：

1. 事務安全表：InnoDB、BDB。

2. 非交易安全表：MyISAM、MEMORY、MERGE、EXAMPLE# 、NDB Cluster、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。

一、MyISAM

1. 資料檔案：

MyISAM資料表在磁碟儲存成3個文件，其檔案名稱都和表名相同，副檔名分別是：

（1）.frm：儲存資料表結構定義。

（2）.MYD：儲存表格資料。

（3）.MYI：儲存表索引。

其中，資料檔案和索引檔案可以放置在不同的目錄，平均分佈IO，獲得更快的速度。指定索引檔案和資料檔案的路徑，需要在建立表格的時候透過data directory和index directory語句指定。 （檔案路徑需要是絕對路徑並且具有訪問的權限）

MyISAM類型的表可能會損壞，原因可能是多種多樣的，損壞後的表可能不能訪問，會提示需要修復或訪問後傳回錯誤的結果。可以使用check table語句來檢查MyISAM表的健康，並用repair table語句修復已經損壞的MyISAM表。

2. 儲存格式：

（1）靜態表（預設）：欄位都是非變長的（每個記錄都是固定長度的）。儲存非常迅速、容易緩存，出現故障容易恢復；佔用空間通常比動態表多。

（2）動態表：佔用的空間相對較少，但是頻繁的更新刪除記錄會產生碎片，需要定期執行optimize table或myisamchk -r命令來改善性能，而且出現故障的時候恢復比較困難。

（3）壓縮表：使用myisampack工具創建，佔用非常小的磁碟空間。因為每個記錄是被單獨壓縮的，所以只有非常小的訪問開支。

靜態表的資料在儲存的時候會依照列的寬度定義補足空格，在傳回資料給應用程式之前先去掉這些空格。如果需要儲存的內容後面本來就有空格，在回傳結果的時候也會被去掉。 （其實是資料型別char的行為，動態表中若有這個資料型別也同樣會有這個問題）

（靜態表和動態表是根據正使用的列的型別自動選擇的。）

 3. 優缺點：

（1）優點：存取的速度快。

（2）不支援交易、也不支援外鍵。

 4. 適用情況：

如果應用程式是以讀取操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性、並發性要求不是很高，那麼選擇這個儲存引擎是非常適合的。 MyISAM 是在Web、資料倉儲和其他應用程式環境下最常使用的儲存引擎之一。

二、InnoDB

1. 儲存方式：

InnoDB儲存資料表和索引有以下兩種方式：

（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中四種常用儲存引擎的詳細介紹以及如何正確選擇的詳細內容。更多資訊請關注PHP中文網其他相關文章！