MySQL資料庫儲存引擎簡介

MySQL 提供了多個儲存引擎——包括處理事務安全表的引擎和處理非事務安全表的引擎，在 MySQL 中，不需要在整個伺服器中使用同一種儲存引擎，針對具體的要求，可以對每一個表使用不同的儲存引擎。

儲存引擎簡介

MySQL中的資料用各種不同的技術儲存在檔案(或者記憶體)中。

這些技術中的每一種技術都使用不同的儲存機制、索引技巧、鎖定水平並且最終提供廣泛的不同的功能和能力。

通過選擇不同的技術，你能夠獲得額外的速度或者功能，從而改善你的應用的整體功能。

而儲存引擎說白了就是

• 如何儲存資料
• 如何為儲存的資料建立索引
• 如何更新、查詢資料 等技術的實現方法。

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

儲存引擎分類介紹

下面就簡單介紹一下MySQL的儲存引擎。

InnoDB

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儲存引擎很適合管理伺服器日誌資料。

MRG_MYISAM

MRG_MyISAM儲存引擎是一組MyISAM表的組合，老版本叫 MERGE 其實是一回事兒。

這些MyISAM表結構必須完全相同，儘管其使用不如其它引擎突出，但是在某些情況下非常有用。

說白了，Merge表就是幾個相同MyISAM表的聚合器；Merge表中並沒有資料，對Merge型別的表可以進行查詢、更新、刪除操作，這些操作實際上是對內部的MyISAM表進行操作。

Merge儲存引擎的使用場景

對於伺服器日誌這種資訊，一般常用的儲存策略是將資料分成很多表，每個名稱與特定的時間段相關。

例如，可以用12個相同的表來儲存伺服器日誌資料，每個表用對應各個月份的名字來命名。當有必要基於所有12個日誌表的資料來生成報表，這意味著需要編寫並更新多表查詢，以反映這些表中的資訊。與其編寫這些可能出現錯誤的查詢，不如將這些表合併起來使用一條查詢，之後再刪除Merge表，而不影響原來的資料，刪除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;
複製程式碼

• 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

MySQL在5.x系列提供了Blackhole引擎–“黑洞”，其作用正如其名字一樣：任何寫入到此引擎的資料均會被丟棄掉， 不做實際儲存；Select語句的內容永遠是空。

這和Linux中的 /dev/null 檔案完成的作用完全一致。

那麼， 一個不能儲存資料的引擎有什麼用呢？

Blackhole雖然不儲存資料，但是MySQL還是會正常的記錄下Binlog，而且這些Binlog還會被正常的同步到Slave上，可以在Slave上對資料進行後續的處理。

這樣對於在Master上只需要Binlog而不需要資料的場合下，balckhole就有用了。

BlackHole 還可以用在以下場景

• 驗證語法 驗證dump file語法的正確性
• 檢測負載 以使用blackhole引擎來檢測binlog功能所需要的額外負載
• 檢測效能 由於blackhole效能損耗極小，可以用來檢測除了儲存引擎這個功能點之外的其他MySQL功能點的效能。

PERFORMANCE_SCHEMA

主要用於收集資料庫伺服器效能引數。

MySQL使用者是不能建立儲存引擎為PERFORMANCE_SCHEMA的表，一般用於記錄binlog做複製的中繼。

在這裡有官方的一些介紹: MySQL Performance Schema

FEDERATED

主要用於訪問其它遠端MySQL伺服器一個代理，它通過建立一個到遠端MySQL伺服器的客戶端連線，並將查詢傳輸到遠端伺服器執行，而後完成資料存取；在MariaDB上的實現是FederatedX

其他

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

提供的名字僅供參考，想了解細節的小夥伴可以自行查閱資料。

常用引擎對比

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
複製程式碼

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>    |
+--------------------+---------+--------------------------------------+-------------+--------+-----------+
複製程式碼

[mysqld]
default_storage_engine=CSV
複製程式碼

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;
複製程式碼