資料庫存儲引擎

對于初學者來說我們通常不關注存儲引擎，但是 MySQL 提供了多個存儲引擎，包括處理事務安全表的引擎和處理非事務安全表的引擎。在 MySQL 中，不需要在整個伺服器中使用同一種存儲引擎，針對具體的要求，可以對每一個表使用不同的存儲引擎。

存儲引擎簡介

MySQL中的資料用各種不同的技術存儲在檔案(或者記憶體)中。這些技術中的每一種技術都使用不同的存儲機制、索引技巧、鎖定水準并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，你能夠獲得額外的速度或者功能，進而改善你的應用的整體功能。 存儲引擎說白了就是如何存儲資料、如何為存儲的資料建立索引和如何更新、查詢資料等技術的實作方法。

例如，如果你在研究大量的臨時資料，你也許需要使用記憶體存儲引擎。記憶體存儲引擎能夠在記憶體中存儲所有的表格資料。又或者，你也許需要一個支援事務處理的資料庫(以確定事務處理不成功時資料的回退能力)。

InnoDB

InnoDB是一個健壯的事務型存儲引擎，這種存儲引擎已經被很多網際網路公司使用，為使用者操作非常大的資料存儲提供了一個強大的解決方案。我的電腦上安裝的 MySQL 5.6.13 版，InnoDB就是作為預設的存儲引擎。InnoDB還引入了行級鎖定和外鍵限制，在以下場合下，使用InnoDB是最理想的選擇：

• 更新密集的表。InnoDB存儲引擎特别适合處理多重并發的更新請求。
• 事務。InnoDB存儲引擎是支援事務的标準MySQL存儲引擎。
• 自動災難恢複。與其它存儲引擎不同，InnoDB表能夠自動從災難中恢複。
• 外鍵限制。MySQL支援外鍵的存儲引擎隻有InnoDB。
• 支援自動增加列AUTO_INCREMENT屬性。
• 從5.7開始innodb存儲引擎成為預設的存儲引擎。

一般來說，如果需要事務支援，并且有較高的并發讀取頻率，InnoDB是不錯的選擇。

MyISAM

MyISAM表是獨立于作業系統的，這說明可以輕松地将其從Windows伺服器移植到Linux伺服器；每當我們建立一個MyISAM引擎的表時，就會在本地磁盤上建立三個檔案，檔案名就是表名。例如，我建立了一個MyISAM引擎的tb_Demo表，那麼就會生成以下三個檔案：

• tb_demo.frm，存儲表定義。
• tb_demo.MYD，存儲資料。
• tb_demo.MYI，存儲索引。

MyISAM表無法處理事務，這就意味着有事務處理需求的表，不能使用MyISAM存儲引擎。MyISAM存儲引擎特别适合在以下幾種情況下使用：

1. 選擇密集型的表。MyISAM存儲引擎在篩選大量資料時非常迅速，這是它最突出的優點。
2. 插入密集型的表。MyISAM的并發插入特性允許同時選擇和插入資料。例如：MyISAM存儲引擎很适合管理郵件或Web伺服器日志資料。

MRG_MYISAM

MRG_MyISAM存儲引擎是一組MyISAM表的組合，老版本叫 MERGE 其實是一回事兒，這些MyISAM表結構必須完全相同，盡管其使用不如其它引擎突出，但是在某些情況下非常有用。說白了，Merge表就是幾個相同MyISAM表的聚合器；Merge表中并沒有資料，對Merge類型的表可以進行查詢、更新、删除操作，這些操作實際上是對内部的MyISAM表進行操作。

Merge存儲引擎的使用場景。對于伺服器日志這種資訊，一般常用的存儲政策是将資料分成很多表，每個名稱與特定的時間端相關。例如：可以用12個相同的表來存儲伺服器日志資料，每個表用對應各個月份的名字來命名。當有必要基于所有12個日志表的資料來生成報表，這意味着需要編寫并更新多表查詢，以反映這些表中的資訊。與其編寫這些可能出現錯誤的查詢，不如将這些表合并起來使用一條查詢，之後再删除Merge表，而不影響原來的資料，删除Merge表隻是删除Merge表的定義，對内部的表沒有任何影響。

• ENGINE=MERGE，指明使用MERGE引擎，其實是跟MRG_MyISAM一回事兒，也是對的，在MySQL 5.7已經看不到MERGE了。
• UNION=(t1, t2)，指明了MERGE表中挂接了些哪表，可以通過alter table的方式修改UNION的值，以實作增删MERGE表子表的功能。比如：

alter table tb_merge engine=merge union(tb_log1) insert_method=last;
           

• 1

• INSERT_METHOD=LAST，INSERT_METHOD指明插入方式，取值可以是：0 不允許插入；FIRST 插入到UNION中的第一個表； LAST 插入到UNION中的最後一個表。
• MERGE表及構成MERGE資料表結構的各成員資料表必須具有完全一樣的結構。每一個成員資料表的資料列必須按照同樣的順序定義同樣的名字和類型，索引也必須按照同樣的順序和同樣的方式定義。

MEMORY

使用MySQL Memory存儲引擎的出發點是速度。為得到最快的響應時間，采用的邏輯存儲媒體是系統記憶體。雖然在記憶體中存儲表資料确實會提供很高的性能，但當mysqld守護程序崩潰時，所有的Memory資料都會丢失。獲得速度的同時也帶來了一些缺陷。它要求存儲在Memory資料表裡的資料使用的是長度不變的格式，這意味着不能使用BLOB和TEXT這樣的長度可變的資料類型，VARCHAR是一種長度可變的類型，但因為它在MySQL内部當做長度固定不變的CHAR類型，是以可以使用。

一般在以下幾種情況下使用Memory存儲引擎：

• 目标資料較小，而且被非常頻繁地通路。在記憶體中存放資料，是以會造成記憶體的使用，可以通過參數max_heap_table_size控制Memory表的大小，設定此參數，就可以限制Memory表的最大大小。
• 如果資料是臨時的，而且要求必須立即可用，那麼就可以存放在記憶體表中。
• 存儲在Memory表中的資料如果突然丢失，不會對應用服務産生實質的負面影響。
• Memory同時支援散列索引和B樹索引。B樹索引的優于散列索引的是，可以使用部分查詢和通配查詢，也可以使用<、>和>=等操作符友善資料挖掘。散列索引進行“相等比較”非常快，但是對“範圍比較”的速度就慢多了，是以散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同樣不适合用在order by子句中。

CSV

CSV 存儲引擎是基于 CSV 格式檔案存儲資料。

• CSV 存儲引擎因為自身檔案格式的原因，所有列必須強制指定 NOT NULL 。
• CSV 引擎也不支援索引，不支援分區。
• CSV 存儲引擎也會包含一個存儲表結構的 .frm 檔案，還會建立一個 .csv 存儲資料的檔案，還會建立一個同名的元資訊檔案，該檔案的擴充名為 .CSM ，用來儲存表的狀态及表中儲存的資料量。
• 每個資料行占用一個文本行。

因為 csv 檔案本身就可以被Office等軟體直接編輯，保不齊就有不按規則出牌的情況，如果出現csv 檔案中的内容損壞了的情況，也可以使用 CHECK TABLE 或者 REPAIR TABLE 指令檢查和修複

ARCHIVE

Archive是歸檔的意思，在歸檔之後很多的進階功能就不再支援了，僅僅支援最基本的插入和查詢兩種功能。在MySQL 5.5版以前，Archive是不支援索引，但是在MySQL 5.5以後的版本中就開始支援索引了。Archive擁有很好的壓縮機制，它使用zlib壓縮庫，在記錄被請求時會實時壓縮，是以它經常被用來當做倉庫使用。

BLACKHOLE

黑洞存儲引擎，所有插入的資料并不會儲存，BLACKHOLE 引擎表永遠保持為空，寫入的任何資料都會消失，

PERFORMANCE_SCHEMA

主要用于收集資料庫伺服器性能參數。MySQL使用者是不能建立存儲引擎為PERFORMANCE_SCHEMA的表，一般用于記錄binlog做複制的中繼。在這裡有官方的一些介紹MySQL Performance Schema

FEDERATED

主要用于通路其它遠端MySQL伺服器一個代理，它通過建立一個到遠端MySQL伺服器的用戶端連接配接，并将查詢傳輸到遠端伺服器執行，而後完成資料存取；在MariaDB的上實作是FederatedX

其他

這裡列舉一些其它資料庫提供的存儲引擎，OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE。提供的名字僅供參考。

常用引擎對比

不同存儲引起都有各自的特點，為适應不同的需求，需要選擇不同的存儲引擎，是以首先考慮這些存儲引擎各自的功能和相容。

特性	InnoDB	MyISAM	MEMORY	ARCHIVE
存儲限制(Storage limits)	64TB	No	YES	No
支援事物(Transactions)	Yes	No	No	No
鎖機制(Locking granularity)	行鎖	表鎖	表鎖	行鎖
B樹索引(B-tree indexes)	Yes	Yes	Yes	No
T樹索引(T-tree indexes)	No	No	No	No
哈希索引(Hash indexes)	Yes	No	Yes	No
全文索引(Full-text indexes)	Yes	Yes	No	No
叢集索引(Clustered indexes)	Yes	No	No	No
資料緩存(Data caches)	Yes	No	N/A	No
索引緩存(Index caches)	Yes	Yes	N/A	No
資料可壓縮(Compressed data)	Yes	Yes	No	Yes
加密傳輸(Encrypted data[1])	Yes	Yes	Yes	Yes
叢集資料庫支援(Cluster databases support)	No	No	No	No
複制支援(Replication support[2])	Yes	No	No	Yes
外鍵支援(Foreign key support)	Yes	No	No	No
存儲空間消耗(Storage Cost)	高	低	N/A	非常低
記憶體消耗(Memory Cost)	高	低	N/A	低
資料字典更新(Update statistics for data dictionary)	Yes	Yes	Yes	Yes
備份/時間點恢複(backup/point-in-time recovery[3])	Yes	Yes	Yes	Yes
多版本并發控制(Multi-Version Concurrency Control/MVCC)	Yes	No	No	No
批量資料寫入效率(Bulk insert speed)	慢	快	快	非常快
地理資訊資料類型(Geospatial datatype support)	Yes	Yes	No	Yes
地理資訊索引(Geospatial indexing support[4])	Yes	Yes	No	Yes

1. 在伺服器中實作（通過加密功能）。在其他表空間加密資料在MySQL 5.7或更高版本相容。
2. 在服務中實作的，而不是在存儲引擎中實作的。
3. 在服務中實作的，而不是在存儲引擎中實作的。
4. 地理位置索引，InnoDB支援可mysql5.7.5或更高版本相容

檢視存儲引擎

使用“SHOW VARIABLES LIKE ‘%storage_engine%’;” 指令在mysql系統變量搜尋磨人設定的存儲引擎，輸入語句如下：

mysql> SHOW VARIABLES LIKE '%storage_engine%';
+----------------------------------+---------+
| Variable_name                    | Value   |
|----------------------------------+---------|
| default_storage_engine           | InnoDB  |
| default_tmp_storage_engine       | InnoDB  |
| disabled_storage_engines         |         |
| internal_tmp_disk_storage_engine | InnoDB  |
+----------------------------------+---------+
4 rows in set
Time: 0.005s
           

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

使用“SHOW ENGINES;”指令顯示安裝以後可用的所有的支援的存儲引擎和預設引擎，後面帶上 \G 可以清單輸出結果，你可以嘗試一下如“SHOW ENGINES\G;”。

mysql> SHOW ENGINES;
+--------------------+---------+--------------------------------------+-------------+--------+-----------+
| Engine             | Support | Comment                              | Transactions| XA     | Savepoints|
|--------------------+---------+--------------------------------------+-------------+--------+-----------|
| InnoDB             | DEFAULT | Supports transactions,               | YES         | YES    | YES       |
|                    |         | row-level locking, and foreign keys  |             |        |           |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables| NO          | NO     | NO        |
| MEMORY             | YES     | Hash based, stored in memory, useful | NO          | NO     | NO        |
|                    |         | for temporary tables                 |             |        |           |
| BLACKHOLE          | YES     | /dev/null storage engine (anything   | NO          | NO     | NO        |
|                    |         | you write to it disappears)          |             |        |           |
| MyISAM             | YES     | MyISAM storage engine                | NO          | NO     | NO        |
| CSV                | YES     | CSV storage engine                   | NO          | NO     | NO        |
| ARCHIVE            | YES     | Archive storage engine               | NO          | NO     | NO        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                   | NO          | NO     | NO        |
| FEDERATED          | NO      | Federated MySQL storage engine       | <null>      | <null> | <null>    |
+--------------------+---------+--------------------------------------+-------------+--------+-----------+
           

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17

由上面指令輸出，可見目前系統的預設資料表類型是InnoDB。當然，我們可以通過修改資料庫配置檔案中的選項，設定預設表類型。

設定存儲引擎

對上面資料庫存儲引擎有所了解之後，你可以在

my.cnf

配置檔案中設定你需要的存儲引擎，這個參數放在 [mysqld] 這個字段下面的 default_storage_engine 參數值，例如下面配置的片段

[mysqld]
default_storage_engine=CSV
           

• 1
• 2

在建立表的時候，對表設定存儲引擎，例如：

CREATE TABLE `user` (
  `id`     int(100) unsigned NOT NULL AUTO_INCREMENT,
  `name`   varchar(32) NOT NULL DEFAULT '' COMMENT '姓名',
  `mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手機',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB;
           

• 1
• 2
• 3
• 4
• 5
• 6

在建立使用者表 user 的時候，SQL語句最後 ENGINE=InnoDB 就是設定這張表存儲引擎為 InnoDB。

如何選擇合适的存儲引擎

提供幾個選擇标準，然後按照标準，選擇對應的存儲引擎即可，也可以根據常用引擎對比來選擇你使用的存儲引擎。使用哪種引擎需要根據需求靈活選擇，一個資料庫中多個表可以使用不同的引擎以滿足各種性能和實際需求。使用合适的存儲引擎，将會提高整個資料庫的性能。

1. 是否需要支援事務；
2. 是否需要使用熱備；
3. 崩潰恢複，能否接受崩潰；
4. 是否需要外鍵支援；
5. 存儲的限制；
6. 對索引和緩存的支援；

轉自 https://github.com/jaywcjlove/mysql-tutorial/blob/master/chapter3/3.5.md

資料庫存儲引擎

對于初學者來說我們通常不關注存儲引擎，但是 MySQL 提供了多個存儲引擎，包括處理事務安全表的引擎和處理非事務安全表的引擎。在 MySQL 中，不需要在整個伺服器中使用同一種存儲引擎，針對具體的要求，可以對每一個表使用不同的存儲引擎。

存儲引擎簡介

MySQL中的資料用各種不同的技術存儲在檔案(或者記憶體)中。這些技術中的每一種技術都使用不同的存儲機制、索引技巧、鎖定水準并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，你能夠獲得額外的速度或者功能，進而改善你的應用的整體功能。 存儲引擎說白了就是如何存儲資料、如何為存儲的資料建立索引和如何更新、查詢資料等技術的實作方法。

例如，如果你在研究大量的臨時資料，你也許需要使用記憶體存儲引擎。記憶體存儲引擎能夠在記憶體中存儲所有的表格資料。又或者，你也許需要一個支援事務處理的資料庫(以確定事務處理不成功時資料的回退能力)。

InnoDB

InnoDB是一個健壯的事務型存儲引擎，這種存儲引擎已經被很多網際網路公司使用，為使用者操作非常大的資料存儲提供了一個強大的解決方案。我的電腦上安裝的 MySQL 5.6.13 版，InnoDB就是作為預設的存儲引擎。InnoDB還引入了行級鎖定和外鍵限制，在以下場合下，使用InnoDB是最理想的選擇：

• 更新密集的表。InnoDB存儲引擎特别适合處理多重并發的更新請求。
• 事務。InnoDB存儲引擎是支援事務的标準MySQL存儲引擎。
• 自動災難恢複。與其它存儲引擎不同，InnoDB表能夠自動從災難中恢複。
• 外鍵限制。MySQL支援外鍵的存儲引擎隻有InnoDB。
• 支援自動增加列AUTO_INCREMENT屬性。
• 從5.7開始innodb存儲引擎成為預設的存儲引擎。

一般來說，如果需要事務支援，并且有較高的并發讀取頻率，InnoDB是不錯的選擇。

MyISAM

MyISAM表是獨立于作業系統的，這說明可以輕松地将其從Windows伺服器移植到Linux伺服器；每當我們建立一個MyISAM引擎的表時，就會在本地磁盤上建立三個檔案，檔案名就是表名。例如，我建立了一個MyISAM引擎的tb_Demo表，那麼就會生成以下三個檔案：

• tb_demo.frm，存儲表定義。
• tb_demo.MYD，存儲資料。
• tb_demo.MYI，存儲索引。

MyISAM表無法處理事務，這就意味着有事務處理需求的表，不能使用MyISAM存儲引擎。MyISAM存儲引擎特别适合在以下幾種情況下使用：

1. 選擇密集型的表。MyISAM存儲引擎在篩選大量資料時非常迅速，這是它最突出的優點。
2. 插入密集型的表。MyISAM的并發插入特性允許同時選擇和插入資料。例如：MyISAM存儲引擎很适合管理郵件或Web伺服器日志資料。

MRG_MYISAM

MRG_MyISAM存儲引擎是一組MyISAM表的組合，老版本叫 MERGE 其實是一回事兒，這些MyISAM表結構必須完全相同，盡管其使用不如其它引擎突出，但是在某些情況下非常有用。說白了，Merge表就是幾個相同MyISAM表的聚合器；Merge表中并沒有資料，對Merge類型的表可以進行查詢、更新、删除操作，這些操作實際上是對内部的MyISAM表進行操作。

Merge存儲引擎的使用場景。對于伺服器日志這種資訊，一般常用的存儲政策是将資料分成很多表，每個名稱與特定的時間端相關。例如：可以用12個相同的表來存儲伺服器日志資料，每個表用對應各個月份的名字來命名。當有必要基于所有12個日志表的資料來生成報表，這意味着需要編寫并更新多表查詢，以反映這些表中的資訊。與其編寫這些可能出現錯誤的查詢，不如将這些表合并起來使用一條查詢，之後再删除Merge表，而不影響原來的資料，删除Merge表隻是删除Merge表的定義，對内部的表沒有任何影響。

• ENGINE=MERGE，指明使用MERGE引擎，其實是跟MRG_MyISAM一回事兒，也是對的，在MySQL 5.7已經看不到MERGE了。
• UNION=(t1, t2)，指明了MERGE表中挂接了些哪表，可以通過alter table的方式修改UNION的值，以實作增删MERGE表子表的功能。比如：

alter table tb_merge engine=merge union(tb_log1) insert_method=last;
           

• 1

• INSERT_METHOD=LAST，INSERT_METHOD指明插入方式，取值可以是：0 不允許插入；FIRST 插入到UNION中的第一個表； LAST 插入到UNION中的最後一個表。
• MERGE表及構成MERGE資料表結構的各成員資料表必須具有完全一樣的結構。每一個成員資料表的資料列必須按照同樣的順序定義同樣的名字和類型，索引也必須按照同樣的順序和同樣的方式定義。

MEMORY

使用MySQL Memory存儲引擎的出發點是速度。為得到最快的響應時間，采用的邏輯存儲媒體是系統記憶體。雖然在記憶體中存儲表資料确實會提供很高的性能，但當mysqld守護程序崩潰時，所有的Memory資料都會丢失。獲得速度的同時也帶來了一些缺陷。它要求存儲在Memory資料表裡的資料使用的是長度不變的格式，這意味着不能使用BLOB和TEXT這樣的長度可變的資料類型，VARCHAR是一種長度可變的類型，但因為它在MySQL内部當做長度固定不變的CHAR類型，是以可以使用。

一般在以下幾種情況下使用Memory存儲引擎：

• 目标資料較小，而且被非常頻繁地通路。在記憶體中存放資料，是以會造成記憶體的使用，可以通過參數max_heap_table_size控制Memory表的大小，設定此參數，就可以限制Memory表的最大大小。
• 如果資料是臨時的，而且要求必須立即可用，那麼就可以存放在記憶體表中。
• 存儲在Memory表中的資料如果突然丢失，不會對應用服務産生實質的負面影響。
• Memory同時支援散列索引和B樹索引。B樹索引的優于散列索引的是，可以使用部分查詢和通配查詢，也可以使用<、>和>=等操作符友善資料挖掘。散列索引進行“相等比較”非常快，但是對“範圍比較”的速度就慢多了，是以散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同樣不适合用在order by子句中。

CSV

CSV 存儲引擎是基于 CSV 格式檔案存儲資料。

• CSV 存儲引擎因為自身檔案格式的原因，所有列必須強制指定 NOT NULL 。
• CSV 引擎也不支援索引，不支援分區。
• CSV 存儲引擎也會包含一個存儲表結構的 .frm 檔案，還會建立一個 .csv 存儲資料的檔案，還會建立一個同名的元資訊檔案，該檔案的擴充名為 .CSM ，用來儲存表的狀态及表中儲存的資料量。
• 每個資料行占用一個文本行。

因為 csv 檔案本身就可以被Office等軟體直接編輯，保不齊就有不按規則出牌的情況，如果出現csv 檔案中的内容損壞了的情況，也可以使用 CHECK TABLE 或者 REPAIR TABLE 指令檢查和修複

ARCHIVE

Archive是歸檔的意思，在歸檔之後很多的進階功能就不再支援了，僅僅支援最基本的插入和查詢兩種功能。在MySQL 5.5版以前，Archive是不支援索引，但是在MySQL 5.5以後的版本中就開始支援索引了。Archive擁有很好的壓縮機制，它使用zlib壓縮庫，在記錄被請求時會實時壓縮，是以它經常被用來當做倉庫使用。

BLACKHOLE

黑洞存儲引擎，所有插入的資料并不會儲存，BLACKHOLE 引擎表永遠保持為空，寫入的任何資料都會消失，

PERFORMANCE_SCHEMA

主要用于收集資料庫伺服器性能參數。MySQL使用者是不能建立存儲引擎為PERFORMANCE_SCHEMA的表，一般用于記錄binlog做複制的中繼。在這裡有官方的一些介紹MySQL Performance Schema

FEDERATED

主要用于通路其它遠端MySQL伺服器一個代理，它通過建立一個到遠端MySQL伺服器的用戶端連接配接，并将查詢傳輸到遠端伺服器執行，而後完成資料存取；在MariaDB的上實作是FederatedX

其他

這裡列舉一些其它資料庫提供的存儲引擎，OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE。提供的名字僅供參考。

常用引擎對比

不同存儲引起都有各自的特點，為适應不同的需求，需要選擇不同的存儲引擎，是以首先考慮這些存儲引擎各自的功能和相容。