[TOC]

MySQL存儲(chǔ)引擎

數(shù)據(jù)庫(kù)存儲(chǔ)引擎是數(shù)據(jù)庫(kù)底層軟件組件，數(shù)據(jù)庫(kù)管理系統(tǒng)使用數(shù)據(jù)引擎進(jìn)行創(chuàng)建、查詢、更新和刪除數(shù)據(jù)操作。簡(jiǎn)而言之，存儲(chǔ)引擎就是指表的類型。數(shù)據(jù)庫(kù)的存儲(chǔ)引擎決定了表在計(jì)算機(jī)中的存儲(chǔ)方式。不同的存儲(chǔ)引擎提供不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平等功能，使用不同的存儲(chǔ)引擎還可以獲得特定的功能。

現(xiàn)在許多數(shù)據(jù)庫(kù)管理系統(tǒng)都支持多種不同的存儲(chǔ)引擎。MySQL 的核心就是存儲(chǔ)引擎。

MySQL 提供了多個(gè)不同的存儲(chǔ)引擎，包括處理事務(wù)安全表的引擎和處理非事務(wù)安全表的引擎。在 MySQL 中，不需要在整個(gè)服務(wù)器中使用同一種存儲(chǔ)引擎，針對(duì)具體的要求，可以對(duì)每一個(gè)表使用不同的存儲(chǔ)引擎。

MySQL 5.7 支持的存儲(chǔ)引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等?？梢允褂?code>SHOW ENGINES;語(yǔ)句查看系統(tǒng)所支持的引擎類型，結(jié)果如圖所示。

MySQL數(shù)據(jù)庫(kù)中的存儲(chǔ)引擎

Support 列的值表示某種引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示該引擎為當(dāng)前默認(rèn)的存儲(chǔ)引擎。

存儲(chǔ)引擎，主要是 InnoDB 和 MyISAM 進(jìn)行詳細(xì)講解。像 NDB 這樣的需要更多擴(kuò)展性的討論。

• ARCHIVE：用于數(shù)據(jù)存檔的引擎，數(shù)據(jù)被插入后就不能在修改了，且不支持索引。
• CSV：在存儲(chǔ)數(shù)據(jù)時(shí)，會(huì)以逗號(hào)作為數(shù)據(jù)項(xiàng)之間的分隔符。
• BLACKHOLE：會(huì)丟棄寫操作，該操作會(huì)返回空內(nèi)容。
• FEDERATED：將數(shù)據(jù)存儲(chǔ)在遠(yuǎn)程數(shù)據(jù)庫(kù)中，用來訪問遠(yuǎn)程表的存儲(chǔ)引擎。
• InnoDB：具備外鍵支持功能的事務(wù)處理引擎
• MEMORY：置于內(nèi)存的表MERGE用來管理由多個(gè)
• MyISAM ：表構(gòu)成的表集合MyISAM主要的非事務(wù)處理存儲(chǔ)引擎
• NDBMySQL ：集群專用存儲(chǔ)引擎

MySQL InnoDB存儲(chǔ)引擎

InnoDB 是 MySQL 中第一個(gè)提供外鍵約束的存儲(chǔ)引擎，而且它對(duì)事務(wù)的處理能力是其它存儲(chǔ)引擎無法與之相比的。

MySQL 5.5 版本以后，默認(rèn)存儲(chǔ)引擎由 MyISAM 修改為 InnoDB。InnoDB 是目前最重要、使用最廣泛的存儲(chǔ)引擎。

InnoDB 一直在持續(xù)改進(jìn)，隨著處理能力的不斷提高，其優(yōu)秀的性能和可維護(hù)性使它成為生產(chǎn)中普遍推薦使用的存儲(chǔ)引擎。一般情況下，除非有特別的原因需要使用其它存儲(chǔ)引擎，否則應(yīng)該優(yōu)先考慮 InnoDB 引擎。

InnoDB優(yōu)勢(shì)

InnoDB 之所以如此受寵，主要在于其功能方面的較多優(yōu)勢(shì)。

1. 支持事務(wù)安裝：InnoDB 最重要的一點(diǎn)就是支持事務(wù)，可以說這是 InnoDB 成為 MySQL 中最流行的存儲(chǔ)引擎的一個(gè)非常重要的原因。InnoDB 還實(shí)現(xiàn)了 SQL92 標(biāo)準(zhǔn)所定義的 4 個(gè)隔離級(jí)別（READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ 和 SERIALIZABLE）。
2. 災(zāi)難恢復(fù)性好：InnoDB 通過 commit、rollback、crash-recovery 來保障數(shù)據(jù)的安全。具體來說，crash-recovery 就是指如果服務(wù)器因?yàn)橛布蜍浖膯栴}而崩潰，不管當(dāng)時(shí)數(shù)據(jù)是怎樣的狀態(tài)，在重啟 MySQL 后，InnoDB 都會(huì)自動(dòng)恢復(fù)到發(fā)生崩潰之前的狀態(tài)，并回到用戶離開的地方。
3. 使用行級(jí)鎖：InnoDB 改變了 MyISAM 的鎖機(jī)制，實(shí)現(xiàn)了行鎖。雖然 InnoDB 的行鎖機(jī)制是通過索引來完成的，但畢竟在數(shù)據(jù)庫(kù)中 99%的 SQL 語(yǔ)句都要使用索引來檢索數(shù)據(jù)。行鎖定機(jī)制也為 InnoDB 在承受高并發(fā)壓力的環(huán)境下增強(qiáng)了不小的競(jìng)爭(zhēng)力。
   • 在 SQL 查詢中可以自由地將 InnoDB 類型的表與其他類型的表混合起來，甚至在同一個(gè)查詢中也可以混合。
4. 實(shí)現(xiàn)了緩沖處理：InnoDB 提供了專門的緩存池，實(shí)現(xiàn)了緩沖管理，不僅能緩沖索引也能緩沖數(shù)據(jù)，常用的數(shù)據(jù)可以直接從內(nèi)存中處理，比從磁盤獲取數(shù)據(jù)處理速度要快。相比之下，MyISAM 只是緩存了索引。
   • InnoDB 的表和索引在一個(gè)邏輯表空間中，表空間可以包含數(shù)個(gè)文件（或原始磁盤分區(qū)）。這與 MyISAM 表不同，比如在 MyISAM 表中每個(gè)表被保存在分離的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制為 2GB 的操作系統(tǒng)上。
5. 支持外鍵：InnoDB 支持外鍵約束，檢查外鍵、插入、更新和刪除，以確保數(shù)據(jù)的完整性。在存儲(chǔ)表中數(shù)據(jù)時(shí)每張表的存儲(chǔ)都按主鍵順序存放，如果沒有顯式地在定義表時(shí)指定主鍵，InnoDB 會(huì)為每一行生成一個(gè) 6 字節(jié)的 ROWID ，并以此作為主鍵。
   • InnoDB 實(shí)現(xiàn)外鍵引用這一重要特性，使在數(shù)據(jù)庫(kù)端控制部分?jǐn)?shù)據(jù)的完整性成為可能。雖然很多數(shù)據(jù)庫(kù)系統(tǒng)調(diào)優(yōu)專家都建議不要這樣做，但是對(duì)于不少用戶來說，大部分情況下，在數(shù)據(jù)庫(kù)端加外鍵控制仍然是成本最低的選擇。
6. 適合需要大型數(shù)據(jù)庫(kù)的網(wǎng)站：InnoDB 被用在眾多需要高性能的大型數(shù)據(jù)庫(kù)網(wǎng)站上。InnoDB 是為處理巨大數(shù)據(jù)量時(shí)的最大性能設(shè)計(jì)，它的 CPU 效率可能是任何其他基于磁盤的關(guān)系數(shù)據(jù)庫(kù)引擎所不能匹敵的。

除了以上幾個(gè)亮點(diǎn)之外，InnoDB 常常還有很多其它的功能特色帶給使用者驚喜。當(dāng)然，使用 InnoDB 存儲(chǔ)引擎肯定也有缺點(diǎn)。相對(duì)于其它存儲(chǔ)引擎來說，使用 InnoDB 存儲(chǔ)引擎的讀寫效率稍差，且占用的數(shù)據(jù)空間相對(duì)較大。

物理存儲(chǔ)

使用 InnoDB 時(shí)，MySQL 會(huì)在數(shù)據(jù)目錄（Data）下創(chuàng)建一個(gè)名為 ibdata1 的 10MB 大小的自動(dòng)擴(kuò)展數(shù)據(jù)文件，以及兩個(gè)名為 ib_logfile0 和 ib_logfile1 的 5MB 大小的日志文件。

InnoDB 存儲(chǔ)引擎和 MyISAM 不太一樣，雖然也有 .frm 文件來存放表結(jié)構(gòu)定義相關(guān)的元數(shù)據(jù)，但是表數(shù)據(jù)和索引數(shù)據(jù)是存放在一起的。至于是每個(gè)表單獨(dú)存放還是所有表存放在一起，用戶可以自己設(shè)置（下面會(huì)介紹如何設(shè)置）。

InnoDB 的物理存儲(chǔ)結(jié)構(gòu)分為兩大部分：

數(shù)據(jù)文件（表數(shù)據(jù)和索引數(shù)據(jù)）

數(shù)據(jù)文件用來存放數(shù)據(jù)表中的數(shù)據(jù)和所有的索引數(shù)據(jù)，包括主鍵和其他普通索引。

InnoDB 存儲(chǔ)的數(shù)據(jù)采用表空間（Tablepace）進(jìn)行存放設(shè)計(jì)。表空間是用來存放 MySQL 系統(tǒng)相關(guān)信息的一個(gè)特殊共享表空間。

InnoDB 的表空間分為以下兩種形式：

1. 共享表空間，表數(shù)據(jù)和索引都存放在同一個(gè)表空間。默認(rèn)的表空間文件就是上面所提到的 MySQL 初始化路徑下的 ibdata1 文件。
2. 獨(dú)立表空間，每個(gè)表的數(shù)據(jù)和索引被存放在一個(gè)單獨(dú)的 .ibd 文件中。

可以通過以下命令查看 MySQL 是否使用獨(dú)立表空間：

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set, 1 warning (0.01 sec)

innodb_file_per_table 值為 ON 時(shí)表示開啟獨(dú)立表文件，InnoDB 表的數(shù)據(jù)和索引都會(huì)以單獨(dú)的形式存放；值為 OFF 時(shí)，InnoDB 表的數(shù)據(jù)和索引都存放在一個(gè)表空間。可以通過設(shè)置該參數(shù)的值來決定是否使用獨(dú)立表空間，具體設(shè)置文章后面會(huì)講解。

共享表空間

共享表空間的數(shù)據(jù)文件可以設(shè)置為固定大小和可自動(dòng)擴(kuò)展大小兩種形式。自動(dòng)擴(kuò)展形式的文件可以設(shè)置文件的最大大小和每次擴(kuò)展量。在創(chuàng)建自動(dòng)擴(kuò)展的數(shù)據(jù)文件時(shí)，建議大家最好加上最大尺寸的屬性，一個(gè)原因是文件系統(tǒng)本身有一定的大小限制，還有一個(gè)原因就是方便自身維護(hù)。

當(dāng)表空間快要用完的時(shí)候，我們必須要為其增加數(shù)據(jù)文件，當(dāng)然，只有共享表空間有此操作。

共享表空間增加數(shù)據(jù)文件的操作比較簡(jiǎn)單，只需要在 innodb_data_file_path 參數(shù)后面按照標(biāo)準(zhǔn)格式設(shè)置好文件路徑和相關(guān)屬性即可。

innodb_data_file_path 參數(shù)負(fù)責(zé)定義共享表空間的路徑、初始化大小、自動(dòng)擴(kuò)展策略。可以使用以下命令查看當(dāng)前共享表空間文件的路徑、大小和自動(dòng)化策略：

mysql> SHOW VARIABLES LIKE 'innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set, 1 warning (0.01 sec)

用戶可以通過 innodb_data_file_path 參數(shù)來指定表空間文件，格式如下：

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

其中，datafile_spec1 格式為表空間文件路徑:大小:屬性，還可以指定多個(gè)文件組成一個(gè)表空間，同時(shí)指定文件的屬性，例如：

[mysqld]
innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

這里將 /db/ibdata1 和 /dr2/db/ibdata2 兩個(gè)文件用來組成表空間。若這兩個(gè)文件位于不同的磁盤上，磁盤的負(fù)載可能被平均，因此可以提高數(shù)據(jù)庫(kù)的整體性能。

指定多個(gè)文件時(shí)，autoextend 屬性只在最后一個(gè)數(shù)據(jù)文件中指定，表示表空間自動(dòng)擴(kuò)展。這里表示文件 ibdata1 的大小為 2000MB，文件 ibdata2 的大小為 2000MB，如果用完了 2000MB，該文件還可以自動(dòng)增長(zhǎng)。

設(shè)置完 innodb_data_file_path 參數(shù)后，所有基于 InnoDB 存儲(chǔ)引擎的表的數(shù)據(jù)都會(huì)記錄到該共享表空間中。

不過這里需要注意的是，InnoDB 在創(chuàng)建新數(shù)據(jù)文件時(shí)不會(huì)創(chuàng)建目錄，如果指定目錄不存在，則會(huì)報(bào)錯(cuò)并無法啟動(dòng)。另外，InnoDB 給共享表空間增加數(shù)據(jù)文件之后，必須要重啟數(shù)據(jù)庫(kù)系統(tǒng)才能生效。

這也是大多數(shù)人一直不太喜歡使用共享表空間而選用獨(dú)立表空間的原因之一。

獨(dú)立表空間

通過設(shè)置 innodb_file_per_table 參數(shù)，可以將每個(gè)基于 InnoDB 存儲(chǔ)引擎的表產(chǎn)生一個(gè)獨(dú)立表空間。

獨(dú)立表空間的命名規(guī)則為表名.ibd。通過這樣的方式，用戶不用將所有數(shù)據(jù)都存放于默認(rèn)的表空間中。

使用 SET 命令打開/關(guān)閉獨(dú)立表空間，命令和運(yùn)行結(jié)果如下：

mysql> SET GLOBAL innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |shell
+-----------------------+-------+
1 row in set, 1 warning (0.03 sec)

mysql> SET GLOBAL innodb_file_per_table=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set, 1 warning (0.00 sec)

需要注意的是，單獨(dú)的表空間文件只存儲(chǔ)該表的數(shù)據(jù)、索引和緩沖等信息。所以無論是使用共享表空間還是獨(dú)享表空間來存放表，共享表空間都是必須存在的。

日志文件

默認(rèn)情況下，InnoDB 存儲(chǔ)引擎的數(shù)據(jù)目錄下會(huì)有兩個(gè)名為 ib_logfile0 和 ib_logfile1 的文件。在 MySQL 官方手冊(cè)中將其稱為 InnoDB 存儲(chǔ)引擎的重做日志文件（redo log file）。

重做日志文件對(duì) InnoDB 存儲(chǔ)引擎至關(guān)重要。InnoDB 可以通過重做日志將數(shù)據(jù)庫(kù)宕機(jī)時(shí)已經(jīng)完成但還沒有來得及將數(shù)據(jù)寫入磁盤的事務(wù)恢復(fù)，也能將所有部分完成并已經(jīng)寫入磁盤的未完成事務(wù)回滾，并且將數(shù)據(jù)還原，以此來保證數(shù)據(jù)的完整性。

每個(gè) InnoDB 存儲(chǔ)引擎至少有 1 個(gè)重做日志文件組（group），每個(gè)文件組下至少有 2 個(gè)重做日志文件，如默認(rèn)的 ib_logfile0 和 ib_logfile1。

如果你的數(shù)據(jù)庫(kù)中有 InnoDB 的表，那么千萬別全部刪除 InnoDB 的日志文件，這很可能會(huì)讓你的數(shù)據(jù)庫(kù) Crash，無法啟動(dòng)，或者丟失數(shù)據(jù)。

• 數(shù)據(jù)庫(kù)不工作或停止響應(yīng)、進(jìn)程中斷等情況，在業(yè)界也叫做數(shù)據(jù)庫(kù) Crash。

在 MySQL 啟動(dòng)參數(shù)文件設(shè)置中，InnoDB 的所有參數(shù)基本上都帶有前綴“innodb_”，不論是 InnoDB 數(shù)據(jù)還是和日志相關(guān)，或者是其他一些性能，事務(wù)等等相關(guān)的參數(shù)都是一樣。

下面是影響重做日志文件的參數(shù)：

• innodb_log_file_size：指定每個(gè)重做日志的大小。
• innodb_log_files_in_group：指定日志文件組中重做日志文件的數(shù)量，默認(rèn)為 1。
• innodb_mirrored_log_groups：指定日志鏡像文件組的數(shù)量，默認(rèn)為 1。
• innodb_log_group_home_dir：指定日志文件組所在路徑，默認(rèn)為./。

簡(jiǎn)而言之，MySQL 中所有和 InnoDB 相關(guān)的系統(tǒng)變量都以“innodb_”做為前綴。

在 MySQL 中，可以通過 skip-innodb 參數(shù)來屏蔽 InnoDB 存儲(chǔ)引擎，這樣即使我們?cè)诎惭b編譯時(shí)，安裝了 InnoDB 存儲(chǔ)引擎，使用者也無法創(chuàng)建 InnoDB 的表。

MySQL MyISAM存儲(chǔ)引擎

MyISAM 存儲(chǔ)引擎是 MySQL 中常見的存儲(chǔ)引擎，曾（MySQL 5.1及之前版本）是 MySQL 的默認(rèn)存儲(chǔ)引擎。

MyISAM 是基于 ISAM 存儲(chǔ)引擎發(fā)展起來的。實(shí)際上那會(huì)還沒有存儲(chǔ)引擎的概念，ISAM 只是一種算法，或者說是數(shù)據(jù)的處理方式。如同 SQL Server/Oracle 這類產(chǎn)品一樣，MySQL 對(duì)表對(duì)象的管理方式只有一種。隨著 MySQL 架構(gòu)的不斷發(fā)展和演進(jìn)，最終才引入插件式存儲(chǔ)引擎的概念，ISAM 也進(jìn)化為 MyISAM 并一直作為 MySQL 數(shù)據(jù)庫(kù)的默認(rèn)存儲(chǔ)引擎，直到 MySQL 5.5 版本才被 InnoDB 引擎取代了默認(rèn)存儲(chǔ)引擎的地位。

優(yōu)缺點(diǎn)

作為 MySQL 最早的存儲(chǔ)引擎之一，MyISAM 有一些已經(jīng)開發(fā)出來很多年的特性，可以滿足用戶的實(shí)際需求。例如全文索引、壓縮、空間函數(shù)（GIS）等。但 MySQL 官方的重心早就不在 MyISAM 引擎上了，所以近些年來，MyISAM 一直沒有很大的改進(jìn)，也存在著許多的缺陷。

優(yōu)點(diǎn)

• 占用空間小
• 訪問速度快，對(duì)事務(wù)完整性沒有要求或以 SELECT、INSERT 為主的應(yīng)用基本上都可以使用這個(gè)引擎來創(chuàng)建表
• 可以配合鎖，實(shí)現(xiàn)操作系統(tǒng)下的復(fù)制備份
• 支持全文檢索（InnoDB 在 MySQL 5.6 版本以后也支持全文檢索）
• 數(shù)據(jù)緊湊存儲(chǔ)，因此可獲得更小的索引和更快的全表掃描性能。

加鎖與并發(fā)

MyISAM 對(duì)整張表加鎖，而不是針對(duì)行。讀取時(shí)會(huì)對(duì)需要讀到的所有表加共享鎖，寫入時(shí)對(duì)表加排他鎖。但是在表有讀取查詢的同時(shí)，也可以往表中插入新的記錄（這被稱為并發(fā)插入）。

修復(fù)

對(duì)于 MyISAM 表，MySQL 可以手工（執(zhí)行命令 CHECK TABLE tablename）或者自動(dòng)執(zhí)行檢查和修復(fù)（執(zhí)行命令 REPAIR TABLE tablename）操作，但這里說的修復(fù)和事務(wù)恢復(fù)以及崩潰修復(fù)是不同的概念。

另外，如果 MySQL 服務(wù)器已經(jīng)關(guān)閉，也可以通過 myisamchk 命令行工具進(jìn)行檢查和修復(fù)操作。

索引特性

MyISAM 支持以下 3 種類型的索引：

• B-Tree 索引：顧名思義，就是所有的索引節(jié)點(diǎn)都按照 balance tree 的數(shù)據(jù)結(jié)構(gòu)來存儲(chǔ)，所有的索引數(shù)據(jù)節(jié)點(diǎn)都在葉節(jié)點(diǎn)。
• R-Tree 索引：R-Tree 索引的存儲(chǔ)方式和 b-tree 索引有一些區(qū)別，主要設(shè)計(jì)用于為存儲(chǔ)空間和多維數(shù)據(jù)的字段做索引，所以對(duì)于目前的 MySQL 版本來說，也僅支持 geometry 類型的字段作索引。
• Full-text 索引：就是全文索引，它的存儲(chǔ)結(jié)構(gòu)也是 b-tree。主要是為了解決需要用 like 查詢時(shí)的低效問題。

MyISAM 上面三種索引類型中，最經(jīng)常使用的就是 B-Tree 索引了，偶爾會(huì)使用到 Full-text，但是 R-Tree 索引一般系統(tǒng)中都是很少用到的。另外 MyISAM 的 B-Tree 索引有一個(gè)較大的限制，那就是參與一個(gè)索引的所有字段的長(zhǎng)度之和不能超過 1000 字節(jié)。

缺點(diǎn)

• 不支持事務(wù)的完整性和并發(fā)性
• 不支持行級(jí)鎖，使用表級(jí)鎖，并發(fā)性差
• 主機(jī)宕機(jī)后，MyISAM表易損壞，災(zāi)難恢復(fù)性不佳
• 數(shù)據(jù)庫(kù)崩潰后無法安全恢復(fù)
• 只緩存索引，數(shù)據(jù)的緩存是利用操作系統(tǒng)緩沖區(qū)來實(shí)現(xiàn)的，可能會(huì)引發(fā)過多的系統(tǒng)調(diào)用，且效率不佳

物理存儲(chǔ)

MyISAM 存儲(chǔ)引擎的表在數(shù)據(jù)庫(kù)中被存儲(chǔ)成 3 個(gè)物理文件，文件名與表名相同。擴(kuò)展名為 frm、MYD 和 MYI。其中：

• frm 為擴(kuò)展名的文件存儲(chǔ)表的結(jié)構(gòu)；
• MYD 為擴(kuò)展名的文件存儲(chǔ)數(shù)據(jù)，其是 MYData 的縮寫；
• MYI 為擴(kuò)展名的文件存儲(chǔ)索引，其是 MYIndex 的縮寫。不管表有多少索引，都是存放在同一個(gè) .MYI 文件中。

MyISAM 類型的數(shù)據(jù)文件和索引文件可以放置在不同的目錄，平均分布 IO，以此來獲得更快的速度。

要指定索引文件和數(shù)據(jù)文件的路徑，需要在創(chuàng)建表的時(shí)候通過 DATA DIRECTORY 和 INDEX DIRECTORY 語(yǔ)句指定，也就是說不同 MyISAM 表的索引文件和數(shù)據(jù)文件可以放置到不同的路徑下。文件路徑需要是絕對(duì)路徑，并且具有訪問權(quán)限。

雖然每一個(gè) MyISAM 的表數(shù)據(jù)都存放在后綴名為 .MYD 的文件中，但是每個(gè)文件的存放格式可能并不完全一樣。因?yàn)?MyISAM 支持 3 種不同的數(shù)據(jù)存放格式，即靜態(tài)型、動(dòng)態(tài)型和壓縮型。

靜態(tài)型

靜態(tài)型為 MyISAM 存儲(chǔ)引擎的默認(rèn)存儲(chǔ)格式，其字段是固定長(zhǎng)度，這樣每個(gè)記錄都是固定長(zhǎng)度的，這種存儲(chǔ)方式存儲(chǔ)非常迅速，容易緩存，出現(xiàn)故障容易恢復(fù)。缺點(diǎn)是占用的空間比動(dòng)態(tài)表多。靜態(tài)型的表的數(shù)據(jù)在存儲(chǔ)的時(shí)候會(huì)按照列的寬度定義去補(bǔ)足空格，但是在應(yīng)用訪問的時(shí)候并不會(huì)得到這些空格，空格在返回給應(yīng)用之前就被去掉了。

需要注意的是，如果需要保存的內(nèi)容后面本來就帶有空格，那么在返回結(jié)果的時(shí)候也會(huì)被去掉。這一點(diǎn)開發(fā)人員在編寫程序的時(shí)候需要特別注意，因?yàn)殪o態(tài)表是默認(rèn)的存儲(chǔ)格式，開發(fā)人員可能并沒有意識(shí)到這一點(diǎn)，從而丟失了尾部的空格。

動(dòng)態(tài)型

動(dòng)態(tài)型包含變長(zhǎng)字段，記錄的長(zhǎng)度不是固定的。這樣存儲(chǔ)的優(yōu)點(diǎn)是占用的空間相對(duì)較少，但是頻繁的更新刪除記錄會(huì)產(chǎn)生碎片，需要定期執(zhí)行 OPTIMIZE TABLE 語(yǔ)句或 myisamchk -r 命令來改善性能，并且出現(xiàn)故障的時(shí)候恢復(fù)相對(duì)比較困難。

壓縮型

與上面兩種格式相比，壓縮型的表就顯得特殊一些。壓縮型的表需要使用 myisampack 工具創(chuàng)建，解壓縮則用另外的 myisamchk 命令。壓縮表是制度的，不支持添加或修改記錄。

壓縮表是基于靜態(tài)或動(dòng)態(tài)格式表的，優(yōu)點(diǎn)在于占用的磁盤空間非常小，可以減少磁盤 I/O，從而提升查詢性能。因?yàn)槊總€(gè)記錄都是被單獨(dú)壓縮的，所以只有非常小的開支。

理論上，MyISAM 存儲(chǔ)引擎的表可以被多個(gè)數(shù)據(jù)庫(kù)實(shí)例同時(shí)使用同時(shí)操作，但是一般不建議這樣做，關(guān)于這點(diǎn)，MySQL 官方的用戶手冊(cè)中也有提到，建議盡量不要在多個(gè) mysqld 之間共享 MyISAM 存儲(chǔ)文件。

如果表在創(chuàng)建并導(dǎo)入數(shù)據(jù)以后，不會(huì)再進(jìn)行修改操作，這樣的表或許適合采用 MyISAM 壓縮表。

不同存儲(chǔ)引擎的數(shù)據(jù)表在文件系統(tǒng)中的表示

MySQL 支持 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 幾種存儲(chǔ)引擎，不同存儲(chǔ)引擎的數(shù)據(jù)表在文件系統(tǒng)中的表示也各不相同。

MySQL 中的每一個(gè)數(shù)據(jù)表在磁盤上至少被表示為一個(gè)文件，即存放著該數(shù)據(jù)表結(jié)構(gòu)定義的 .frm 文件。不同的存儲(chǔ)引擎還有其它用來存放數(shù)據(jù)和索引信息的文件。

注意：從 MySQL 8.0 版本開始，frm 表結(jié)構(gòu)定義文件被取消，MySQL 把表結(jié)構(gòu)信息都寫到了系統(tǒng)表空間。

不同存儲(chǔ)引擎的數(shù)據(jù)表在文件系統(tǒng)中是如何表示的。

MyISAM

MyISAM 存儲(chǔ)引擎的數(shù)據(jù)表在數(shù)據(jù)庫(kù)目錄里使用 3 個(gè)文件來代表，這些文件的基本名與數(shù)據(jù)表的名字相同，擴(kuò)展名則表明了文件的具體用途。這三個(gè)文件的擴(kuò)展名分別是：

• .frm：表結(jié)構(gòu)定義文件，存放著該數(shù)據(jù)表的結(jié)構(gòu)定義。
• .MYD：MY Data 的縮寫，數(shù)據(jù)文件，存放著該數(shù)據(jù)表中各個(gè)行的數(shù)據(jù)。
• .MYI： MY Index 的縮寫，索引文件，存放著該數(shù)據(jù)表的全部索引信息。

下面創(chuàng)建存儲(chǔ)引擎為 MyISAM 的 tb_myisam 表，SQL 語(yǔ)句如下。

mysql> SET default_storage_engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE tb_myisam(
    -> id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

MERGE

MERGE 存儲(chǔ)引擎的數(shù)據(jù)表其實(shí)是一個(gè)邏輯結(jié)構(gòu)。它代表著由一組結(jié)構(gòu)完全相同的 MyISAM 數(shù)據(jù)表所構(gòu)成的集合。有關(guān)的查詢命令會(huì)把它當(dāng)作一個(gè)大數(shù)據(jù)表來對(duì)待。

MERGE 存儲(chǔ)引擎的數(shù)據(jù)表除了擁有存儲(chǔ)表結(jié)構(gòu)定義的 .frm 文件以外，還有一個(gè)擴(kuò)展名為 .mgr 的文件，這個(gè)文件里不保存數(shù)據(jù)，而是數(shù)據(jù)的來源地。通俗的說，就是一份由多個(gè) MyISAM 數(shù)據(jù)表的名單構(gòu)成的 MERGE 數(shù)據(jù)表。

下面創(chuàng)建存儲(chǔ)引擎為 MERGE 的 tb_merge 表，SQL 語(yǔ)句如下。

mysql> SET default_storage_engine=Merge;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_merge(
    -> id INT
    -> );
Query OK, 0 rows affected (0.02 sec)

InnoDB

對(duì)于 InnoDB 存儲(chǔ)引擎的數(shù)據(jù)表，一個(gè)表對(duì)應(yīng)兩個(gè)文件，一個(gè)是 *.frm，存儲(chǔ)表結(jié)構(gòu)信息；一個(gè)是 *.ibd，存儲(chǔ)表中數(shù)據(jù)。

下面創(chuàng)建存儲(chǔ)引擎為 InnoDB 的 tb_innodb 表，SQL 語(yǔ)句如下。

mysql> SET default_storage_engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_innodb(
    -> id INT
    -> );
Query OK, 0 rows affected (0.10 sec)

Memory

Memory 存儲(chǔ)引擎的數(shù)據(jù)表是創(chuàng)建在內(nèi)存中的數(shù)據(jù)表。因?yàn)?MySQL 服務(wù)器把 Memory 數(shù)據(jù)表的數(shù)據(jù)和索引都存放在了內(nèi)存中而不是硬盤上，所以除了相應(yīng)的 .frm 文件外，Memory 引擎表在文件系統(tǒng)里沒有其它相應(yīng)的代表文件。

下面創(chuàng)建存儲(chǔ)引擎為 Memory 的 tb_memory 表，SQL 語(yǔ)句如下。

mysql> SET default_storage_engine=Memory;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_memory(
    -> id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

Archive

Archive 存儲(chǔ)引擎的數(shù)據(jù)表除了擁有 .frm 表結(jié)構(gòu)定義文件外，還有一個(gè)擴(kuò)展名為 .arz 的數(shù)據(jù)文件，用來存儲(chǔ)歷史歸檔數(shù)據(jù)。執(zhí)行優(yōu)化操作時(shí)可能還會(huì)出現(xiàn)一個(gè)擴(kuò)展名為 .arn 的文件。

下面創(chuàng)建存儲(chǔ)引擎為 Archive 的 tb_archive 表，SQL 語(yǔ)句如下。

mysql> SET default_storage_engine=Archive;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_archive(
    -> id INT
    -> );
Query OK, 0 rows affected (0.04 sec)

CSV

與其它類型的存儲(chǔ)引擎相同，CSV 引擎表也會(huì)包含一個(gè) .frm 表結(jié)構(gòu)定義文件，此外還會(huì)創(chuàng)建一個(gè)擴(kuò)展名為 .CSV 的數(shù)據(jù)文件。這個(gè)文件是 CSV 格式的文本文件，用來保存表中的實(shí)際數(shù)據(jù)。

.CSV 文件可以直接在 Excel 中打開，或者是使用其它文件編輯工具查看。另外，還有一個(gè)同名的元信息文件，文件擴(kuò)展名為 .CSM，用來保存表的狀態(tài)及表中保存的數(shù)據(jù)量。

注意：由于 CSV 文件可被直接編輯，如果操作得當(dāng)，可以不通過 SQL 語(yǔ)句直接修改 CSV 文件中的內(nèi)容。

CSV 存儲(chǔ)引擎基于 CSV 格式文件存儲(chǔ)數(shù)據(jù)，由于自身文件格式的原因，所有列必須強(qiáng)制指定 NOT NULL。

下面創(chuàng)建存儲(chǔ)引擎為 CSV 的 tb_csv 表，SQL 語(yǔ)句如下。

mysql> SET default_storage_engine=csv;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE tb_csv(
    -> id INT NOT NULL,
    -> name CHAR(10) NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)

BLACKHOLE

由于在 BLACKHOLE 存儲(chǔ)引擎的數(shù)據(jù)表中寫入任何數(shù)據(jù)都會(huì)消失，所以除了 .frm 文件，BLACKHOLE 引擎表沒有其他相應(yīng)的代表文件。

下面創(chuàng)建存儲(chǔ)引擎為 BLACKHOLE 的 tb_blackhole 表，SQL 語(yǔ)句如下。

mysql> SET default_storage_engine=BLACKHOLE;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE tb_blackhole(
    -> id INT
    -> );
Query OK, 0 rows affected (0.03 sec)

不同存儲(chǔ)引擎的數(shù)據(jù)表在文件系統(tǒng)中的表示。

img

MySQL查看和修改默認(rèn)存儲(chǔ)引擎

如果需要操作默認(rèn)存儲(chǔ)引擎，首先需要查看默認(rèn)存儲(chǔ)引擎?？梢酝ㄟ^執(zhí)行下面的語(yǔ)句來查看默認(rèn)的存儲(chǔ)引擎，具體 SQL 語(yǔ)句如下：

SHOW VARIABLES LIKE 'default_storage_engine%';

執(zhí)行上面的 SQL 語(yǔ)句，其結(jié)果如圖所示。

查看數(shù)據(jù)庫(kù)默認(rèn)存儲(chǔ)引擎

執(zhí)行結(jié)果顯示，InnoDB 存儲(chǔ)引擎為默認(rèn)存儲(chǔ)引擎。

使用下面的語(yǔ)句可以修改數(shù)據(jù)庫(kù)臨時(shí)的默認(rèn)存儲(chǔ)引擎：

SET default_storage_engine=< 存儲(chǔ)引擎名 >

例如，將 MySQL 數(shù)據(jù)庫(kù)的臨時(shí)默認(rèn)存儲(chǔ)引擎修改為 MyISAM，輸入的 SQL 語(yǔ)句和運(yùn)行結(jié)果如圖所示。

修改MySQL數(shù)據(jù)庫(kù)中的默認(rèn)存儲(chǔ)引擎

此時(shí)，可以發(fā)現(xiàn) MySQL 的默認(rèn)存儲(chǔ)引擎已經(jīng)變成了 MyISAM。但是當(dāng)再次重啟客戶端時(shí)，默認(rèn)存儲(chǔ)引擎仍然是 InnoDB。

如何選擇MySQL存儲(chǔ)引擎？

在使用 MySQL 數(shù)據(jù)庫(kù)管理系統(tǒng)時(shí)，選擇一個(gè)合適的存儲(chǔ)引擎是一個(gè)非常復(fù)雜的問題。不同的存儲(chǔ)引擎都有各自的特性、優(yōu)勢(shì)和使用的場(chǎng)合，正確的選擇存儲(chǔ)引擎可以提高應(yīng)用的使用效率。

為了能夠正確地選擇存儲(chǔ)引擎，必須掌握各種存儲(chǔ)引擎的特性。下面重點(diǎn)介紹幾種常用的存儲(chǔ)引擎，它們對(duì)各種特性的支持如下表所示。

表中主要介紹了 MyISAM、InnoDB 和 MEMORY 三種存儲(chǔ)引擎特性的對(duì)比。下面詳細(xì)介紹這 3 個(gè)存儲(chǔ)引擎的應(yīng)用場(chǎng)合并給出相應(yīng)的建議。

MyISAM

在 MySQL 5.1 版本及之前的版本，MyISAM 是默認(rèn)的存儲(chǔ)引擎。

MyISAM 存儲(chǔ)引擎不支持事務(wù)和外鍵，所以訪問速度比較快。如果應(yīng)用主要以讀取和寫入為主，只有少量的更新和刪除操作，并且對(duì)事務(wù)的完整性、并發(fā)性要求不是很高，那么選擇 MyISAM 存儲(chǔ)引擎是非常適合的。

MyISAM 是在 Web 數(shù)據(jù)倉(cāng)儲(chǔ)和其他應(yīng)用環(huán)境下最常使用的存儲(chǔ)引擎之一。

InnoDB

MySQL 5.5 版本之后默認(rèn)的事務(wù)型引擎修改為 InnoDB。

InnoDB 存儲(chǔ)引擎在事務(wù)上具有優(yōu)勢(shì)，即支持具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安裝，所以比 MyISAM 存儲(chǔ)引擎占用更多的磁盤空間。

如果應(yīng)用對(duì)事務(wù)的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢以外，還包括很多的更新、刪除操作，那么 InnoDB 存儲(chǔ)引擎是比較合適的選擇。

InnoDB 存儲(chǔ)引擎除了可以有效地降低由于刪除和更新導(dǎo)致的鎖定，還可以確保事務(wù)的完整提交（Commit）和回滾（Rollback），對(duì)于類似計(jì)費(fèi)系統(tǒng)或者財(cái)務(wù)系統(tǒng)等對(duì)數(shù)據(jù)準(zhǔn)確性要求比較高的系統(tǒng)，InnoDB 都是合適的選擇。

MEMORY

MEMORY 存儲(chǔ)引擎將所有數(shù)據(jù)保存在 RAM 中，所以該存儲(chǔ)引擎的數(shù)據(jù)訪問速度快，但是安全上沒有保障。

MEMORY 對(duì)表的大小有限制，太大的表無法緩存在內(nèi)存中。由于使用 MEMORY 存儲(chǔ)引擎沒有安全保障，所以要確保數(shù)據(jù)庫(kù)異常終止后表中的數(shù)據(jù)可以恢復(fù)。

如果應(yīng)用中涉及數(shù)據(jù)比較少，且需要進(jìn)行快速訪問，則適合使用 MEMORY 存儲(chǔ)引擎。

總結(jié)

不同應(yīng)用的特點(diǎn)是千差萬別的，選擇適應(yīng)存儲(chǔ)引擎才是最佳方案也不是絕對(duì)的，這需要根據(jù)實(shí)際應(yīng)用進(jìn)行測(cè)試，從而得到最適合的結(jié)果。

MySQL修改數(shù)據(jù)表的存儲(chǔ)引擎

MySQL 的核心就是存儲(chǔ)引擎。MySQL 存儲(chǔ)引擎主要有 InnoDB、MyISAM、Memory、BDB、Merge、Archive、Federated、CSV、BLACKHOLE 等。

MySQL 中修改數(shù)據(jù)表的存儲(chǔ)引擎的語(yǔ)法格式如下：

ALTER TABLE <表名> ENGINE=<存儲(chǔ)引擎名>;

ENGINE 關(guān)鍵字用來指明新的存儲(chǔ)引擎。

以上這種方法適用于修改單個(gè)表的存儲(chǔ)引擎，如果希望修改默認(rèn)的存儲(chǔ)引擎，就需要修改 my.cnf 配置文件。在 my.cnf 配置文件的 [mysqld] 后面加入以下語(yǔ)句：

default-storage-engine=存儲(chǔ)引擎名稱

然后保存就可以了。