MySQL常用存儲引擎功能與用法詳解

本文實例講述了MySQL常用存儲引擎功能與用法。分享給大家供大家參考，具體如下：

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

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

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

MySQL默認的存儲引擎是MyISAM（5.7版本中默認為InnoDB）。

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

查詢當前數(shù)據(jù)庫支持的存儲引擎：

show engines;
show variables like 'have%';

查看當前的默認存儲引擎：

show variables like '%table_type%';

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

create table(...) engine=MyISAM;

下面詳細介紹4個比較常用的存儲引擎：MyISAM、InnoDB、MEMORY和MERGE。

一、MyISAM

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

MyISAM數(shù)據(jù)表在磁盤存儲成3個文件，其文件名都和表名相同，擴展名分別是：

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

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

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

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

MyISAM類型的表可能會損壞，原因可能是多種多樣的，損壞后的表可能不能訪問，會提示需要修復或者訪問后返回錯誤的結(jié)果。可以使用check table語句來檢查MyISAM表的健康，并用repair table語句修復已經(jīng)損壞的MyISAM表。

 2. 存儲格式：

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

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

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

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

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

 3. 優(yōu)劣勢：

（1）優(yōu)勢：訪問的速度快。

（2）不支持事務、也不支持外鍵。

 4. 適用情況：

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

二、InnoDB

1. 存儲方式：

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

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

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

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

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

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

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

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

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

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

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

3. 外鍵約束：

InnoDB是MySQL唯一支持外鍵約束的引擎。外鍵約束可以讓數(shù)據(jù)庫自己通過外鍵保證數(shù)據(jù)的完整性和一致性，但是引入外鍵會使速度和性能下降。在創(chuàng)建外鍵的時候，要求父表必須有對應的索引，子表在創(chuàng)建外鍵的時候也會自動創(chuà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 ：定義索引約束名稱。

CONSTRAINT：定義外鍵約束名稱。（在數(shù)據(jù)庫中應是唯一的，若不指定系統(tǒng)會自動生成一個約束名）

ON：指定父表操作對子表的影響（不定義默認采用restrict）。

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

Cascade：在父表更新或刪除時，同時更新或刪除子表對應的記錄。

Set null：在父表更新或刪除的時候，子表的對應字段被設置為null。

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

關閉命令：

set foreign_key_checks=0;

開啟命令：

set foreign_key_checks=1;

4. 優(yōu)劣勢：

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

（2）劣勢：相比MyISAM，InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引。

5. 適用情況：

如果應用對事務的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢以外，還包括很多的更新、刪除操作，那么InnoDB 存儲引擎應該是比較合適的選擇。InnoDB 存儲引擎除了有效地降低由于刪除和更新導致的鎖定，還可以確保事務的完整提交和回滾，對于類似計費系統(tǒng)或者財務系統(tǒng)等對數(shù)據(jù)準確性要求比較高的系統(tǒng)，InnoDB 都是合適的選擇。

三、MEMORY

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

每個MEMORY表只對應一個.frm磁盤文件，用于存儲表的結(jié)構(gòu)定義，表數(shù)據(jù)存放在內(nèi)存中。默認使用HASH索引，而不是BTREE索引。

2. 優(yōu)劣勢：

（1）優(yōu)勢：訪問速度非?？?，因為數(shù)據(jù)是存在內(nèi)存中的。

（2）劣勢：一旦服務關閉，表中的數(shù)據(jù)就會丟失；對表的大小有限制。

3. 適用情況：

Memory存儲引擎主要用在那些內(nèi)容變化不頻繁的代碼表，或者作為統(tǒng)計操作的中間結(jié)果表，便于高效地對中間結(jié)果進行分析并得到最終的統(tǒng)計結(jié)果。

四、MERGE

1. 引擎原理：

Merge存儲引擎是一組MyISAM表的組合，這些MyISAM表必須結(jié)構(gòu)完全相同，merge表本身并沒有數(shù)據(jù)，對merge類型的表可以進行查詢、更新、刪除的操作，這些操作實際上是對內(nèi)部的實際的MyISAM表進行的。

通過insert_method子句定義merge表的插入操作：使用first或last可以使插入操作被相應地作用在第一或最后一個表上，不定義或定義為No表示不能對這個merge表進行插入操作。對merge表進行drop操作只是刪除了merge的定義，對內(nèi)部的表沒有任何影響。

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

（1）.frm：存儲表定義。

（2）.MRG：存儲組合表的信息，包括merge表由哪些表組成、插入新數(shù)據(jù)時的依據(jù)?？梢酝ㄟ^修改.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 表的優(yōu)點在于可以突破對單個MyISAM 表大小的限制，并且通過將不同的表分布在多個磁盤上，可以有效地改善MERGE 表的訪問效率。這對于諸如數(shù)據(jù)倉儲等VLDB環(huán)境十分適合。

更多關于MySQL相關內(nèi)容感興趣的讀者可查看本站專題：《MySQL存儲過程技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》及《MySQL數(shù)據(jù)庫鎖相關技巧匯總》

希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。