MySQL存儲(chǔ)引擎主要有兩大類(lèi)：

1. 事務(wù)安全表：InnoDB、BDB。

2. 非事務(wù)安全表：MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等

MySQL默認(rèn)的存儲(chǔ)引擎是MyISAM（5.7版本中默認(rèn)為InnoDB）。

配置文件中設(shè)置默認(rèn)存儲(chǔ)引擎的參數(shù)：default-table-type。

查詢(xún)當(dāng)前數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎：

show engines; show variables like 'have%';

查看當(dāng)前的默認(rèn)存儲(chǔ)引擎：

show variables like '%table_type%';

創(chuàng)建新表時(shí)指定存儲(chǔ)引擎：

create table(...) engine=MyISAM;

下面詳細(xì)介紹4個(gè)比較常用的存儲(chǔ)引擎：MyISAM、InnoDB、MEMORY和MERGE。

一、MyISAM

1. 數(shù)據(jù)文件：

MyISAM數(shù)據(jù)表在磁盤(pán)存儲(chǔ)成3個(gè)文件，其文件名都和表名相同，擴(kuò)展名分別是：

（1）.frm：存儲(chǔ)數(shù)據(jù)表結(jié)構(gòu)定義。

（2）.MYD：存儲(chǔ)表數(shù)據(jù)。

（3）.MYI：存儲(chǔ)表索引。

其中，數(shù)據(jù)文件和索引文件可以放置在不同的目錄，平均分布IO，獲得更快的速度。指定索引文件和數(shù)據(jù)文件的路徑，需要在創(chuàng)建表的時(shí)候通過(guò)data directory和index directory語(yǔ)句指定。（文件路徑需要是絕對(duì)路徑并且具有訪(fǎng)問(wèn)的權(quán)限）

MyISAM類(lèi)型的表可能會(huì)損壞，原因可能是多種多樣的，損壞后的表可能不能訪(fǎng)問(wèn)，會(huì)提示需要修復(fù)或者訪(fǎng)問(wèn)后返回錯(cuò)誤的結(jié)果?？梢允褂胏heck table語(yǔ)句來(lái)檢查MyISAM表的健康，并用repair table語(yǔ)句修復(fù)已經(jīng)損壞的MyISAM表。

2. 存儲(chǔ)格式：

（1）靜態(tài)表（默認(rèn)）：字段都是非變長(zhǎng)的（每個(gè)記錄都是固定長(zhǎng)度的）。存儲(chǔ)非常迅速、容易緩存，出現(xiàn)故障容易恢復(fù)；占用空間通常比動(dòng)態(tài)表多。

（2）動(dòng)態(tài)表：占用的空間相對(duì)較少，但是頻繁的更新刪除記錄會(huì)產(chǎn)生碎片，需要定期執(zhí)行optimize table或myisamchk -r命令來(lái)改善性能，而且出現(xiàn)故障的時(shí)候恢復(fù)比較困難。

（3）壓縮表：使用myisampack工具創(chuàng)建，占用非常小的磁盤(pán)空間。因?yàn)槊總€(gè)記錄是被單獨(dú)壓縮的，所以只有非常小的訪(fǎng)問(wèn)開(kāi)支。

靜態(tài)表的數(shù)據(jù)在存儲(chǔ)的時(shí)候會(huì)按照列的寬度定義補(bǔ)足空格，在返回?cái)?shù)據(jù)給應(yīng)用之前去掉這些空格。如果需要保存的內(nèi)容后面本來(lái)就有空格，在返回結(jié)果的時(shí)候也會(huì)被去掉。（其實(shí)是數(shù)據(jù)類(lèi)型char的行為，動(dòng)態(tài)表中若有這個(gè)數(shù)據(jù)類(lèi)型也同樣會(huì)有這個(gè)問(wèn)題）

（靜態(tài)表和動(dòng)態(tài)表是根據(jù)正使用的列的類(lèi)型自動(dòng)選擇的。）

3. 優(yōu)劣勢(shì)：

（1）優(yōu)勢(shì)：訪(fǎng)問(wèn)的速度快。

（2）不支持事務(wù)、也不支持外鍵。

4. 適用情況：

如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對(duì)事務(wù)的完整性、并發(fā)性要求不是很高，那么選擇這個(gè)存儲(chǔ)引擎是非常適合的。MyISAM 是在Web、數(shù)據(jù)倉(cāng)庫(kù)和其他應(yīng)用環(huán)境下最常使用的存儲(chǔ)引擎之一。

二、InnoDB

1. 存儲(chǔ)方式：

InnoDB存儲(chǔ)表和索引有以下兩種方式：

（1）使用共享表空間存儲(chǔ)：這種方式創(chuàng)建的表結(jié)構(gòu)保存在.frm文件中，數(shù)據(jù)和索引保存在innodb_data_home_dir和innodb_data_file_path定義的表空間中，可以是多個(gè)文件。

（2）使用多表空間存儲(chǔ)：這種方式創(chuàng)建的表結(jié)構(gòu)仍然保存在.frm文件中，但是每個(gè)表的數(shù)據(jù)和索引單獨(dú)保存在.idb文件中。如果是個(gè)分區(qū)表，則每個(gè)分區(qū)對(duì)應(yīng)單獨(dú)的.idb文件，文件名是“表名+分區(qū)名”，可以在創(chuàng)建分區(qū)的時(shí)候指定每個(gè)分區(qū)的數(shù)據(jù)文件的位置，以此來(lái)將表的IO均勻分布在多個(gè)磁盤(pán)上。

要使用多表空間的存儲(chǔ)方式，需要設(shè)置參數(shù)innodb_file_per_table并重啟服務(wù)器后才可以生效，而且只對(duì)新建的表生效。多表空間的數(shù)據(jù)文件沒(méi)有大小限制，不需要設(shè)置初始大小，也不需要設(shè)置文件的最大限制、擴(kuò)展大小等參數(shù)。即使在多表空間的存儲(chǔ)方式下，共享表空間仍然是必須的，InnoDB把內(nèi)部數(shù)據(jù)詞典和工作日志放在這個(gè)文件中，所以備份使用多表空間特性的表時(shí)直接復(fù)制.idb文件是不行的，可以通過(guò)命令將數(shù)據(jù)備份恢復(fù)到數(shù)據(jù)庫(kù)中：

ALTER TABLE tbl_name DISCARD TABLESPACE; ALTER TABLE tbl_name IMPORT TABLESPACE;

但是這樣只能恢復(fù)到表原來(lái)所在數(shù)據(jù)庫(kù)中，如果需要恢復(fù)到其他數(shù)據(jù)庫(kù)則需要通過(guò)mysqldump和mysqlimport來(lái)實(shí)現(xiàn)。

2. 數(shù)據(jù)文件：

InnoDB的數(shù)據(jù)文件由表的存儲(chǔ)方式?jīng)Q定。

（1）共享表空間文件：由參數(shù)innodb_data_home_dir和innodb_data_file_path定義，用于存放數(shù)據(jù)詞典和日志等。

（2）.frm：存放表結(jié)構(gòu)定義。

（3）.idb：使用多表空間存儲(chǔ)方式時(shí)，用于存放表數(shù)據(jù)和索引，若使用共享表空間存儲(chǔ)則無(wú)此文件。

3. 外鍵約束：

InnoDB是MySQL唯一支持外鍵約束的引擎。外鍵約束可以讓數(shù)據(jù)庫(kù)自己通過(guò)外鍵保證數(shù)據(jù)的完整性和一致性，但是引入外鍵會(huì)使速度和性能下降。在創(chuàng)建外鍵的時(shí)候，要求父表必須有對(duì)應(yīng)的索引，子表在創(chuàng)建外鍵的時(shí)候也會(huì)自動(dòng)創(chuàng)建對(duì)應(yīng)的索引。

外鍵約束使用示例：

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 ：定義索引約束名稱(chēng)。

CONSTRAINT：定義外鍵約束名稱(chēng)。（在數(shù)據(jù)庫(kù)中應(yīng)是唯一的，若不指定系統(tǒng)會(huì)自動(dòng)生成一個(gè)約束名）

ON：指定父表操作對(duì)子表的影響（不定義默認(rèn)采用restrict）。

Restrict和no action：在子表有相關(guān)記錄的情況下父表不能更新或刪除。

Cascade：在父表更新或刪除時(shí)，同時(shí)更新或刪除子表對(duì)應(yīng)的記錄。

Set null：在父表更新或刪除的時(shí)候，子表的對(duì)應(yīng)字段被設(shè)置為null。

當(dāng)某個(gè)表被其他表創(chuàng)建了外鍵參照，那么這個(gè)表的對(duì)應(yīng)索引或者主鍵禁止被刪除。在導(dǎo)入多個(gè)表的數(shù)據(jù)時(shí)，如果需要忽略表的導(dǎo)入順序，可以暫時(shí)關(guān)閉外鍵的檢查；在執(zhí)行l(wèi)oad data和alter table操作的時(shí)候，也可以通過(guò)暫時(shí)關(guān)閉外鍵約束來(lái)加快處理的速度。

關(guān)閉命令：

set foreign_key_checks=0;

開(kāi)啟命令：

set foreign_key_checks=1;

4. 優(yōu)劣勢(shì)：

（1）優(yōu)勢(shì)：提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。

（2）劣勢(shì)：相比MyISAM，InnoDB寫(xiě)的處理效率差一些，并且會(huì)占用