數(shù)據(jù)庫存儲引擎
對于初學(xué)者來說我們通常不關(guān)注存儲引擎弱判，但是 MySQL 提供了多個存儲引擎蘸泻，包括處理事務(wù)安全表的引擎和處理非事務(wù)安全表的引擎咖熟。在 MySQL 中胃惜，不需要在整個服務(wù)器中使用同一種存儲引擎，針對具體的要求鼎姐，可以對每一個表使用不同的存儲引擎钾麸。
存儲引擎簡介
MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件(或者內(nèi)存)中。這些技術(shù)中的每一種技術(shù)都使用不同的存儲機制炕桨、索引技巧饭尝、鎖定水平并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術(shù)献宫，你能夠獲得額外的速度或者功能钥平，從而改善你的應(yīng)用的整體功能。 存儲引擎說白了就是如何存儲數(shù)據(jù)姊途、如何為存儲的數(shù)據(jù)建立索引和如何更新涉瘾、查詢數(shù)據(jù)等技術(shù)的實現(xiàn)方法知态。
例如，如果你在研究大量的臨時數(shù)據(jù)立叛，你也許需要使用內(nèi)存存儲引擎负敏。內(nèi)存存儲引擎能夠在內(nèi)存中存儲所有的表格數(shù)據(jù)。又或者囚巴，你也許需要一個支持事務(wù)處理的數(shù)據(jù)庫(以確保事務(wù)處理不成功時數(shù)據(jù)的回退能力)原在。
InnoDB
InnoDB是一個健壯的事務(wù)型存儲引擎，這種存儲引擎已經(jīng)被很多互聯(lián)網(wǎng)公司使用彤叉，為用戶操作非常大的數(shù)據(jù)存儲提供了一個強大的解決方案。我的電腦上安裝的 MySQL 5.6.13 版村怪，InnoDB就是作為默認(rèn)的存儲引擎秽浇。InnoDB還引入了行級鎖定和外鍵約束，在以下場合下甚负，使用InnoDB是最理想的選擇：
- 更新密集的表柬焕。InnoDB存儲引擎特別適合處理多重并發(fā)的更新請求。
- 事務(wù)梭域。InnoDB存儲引擎是支持事務(wù)的標(biāo)準(zhǔn)MySQL存儲引擎斑举。
- 自動災(zāi)難恢復(fù)。與其它存儲引擎不同病涨，InnoDB表能夠自動從災(zāi)難中恢復(fù)富玷。
- 外鍵約束。MySQL支持外鍵的存儲引擎只有InnoDB既穆。
- 支持自動增加列AUTO_INCREMENT屬性赎懦。
- 從5.7開始innodb存儲引擎成為默認(rèn)的存儲引擎。
一般來說幻工，如果需要事務(wù)支持励两，并且有較高的并發(fā)讀取頻率，InnoDB是不錯的選擇囊颅。
MyISAM
MyISAM表是獨立于操作系統(tǒng)的当悔，這說明可以輕松地將其從Windows服務(wù)器移植到Linux服務(wù)器；每當(dāng)我們建立一個MyISAM引擎的表時踢代，就會在本地磁盤上建立三個文件盲憎，文件名就是表名。例如奸鬓，我建立了一個MyISAM引擎的tb_Demo表焙畔，那么就會生成以下三個文件：
- tb_demo.frm，存儲表定義串远。
- tb_demo.MYD宏多，存儲數(shù)據(jù)儿惫。
- tb_demo.MYI，存儲索引伸但。
MyISAM表無法處理事務(wù)肾请，這就意味著有事務(wù)處理需求的表，不能使用MyISAM存儲引擎更胖。MyISAM存儲引擎特別適合在以下幾種情況下使用：
- 選擇密集型的表铛铁。MyISAM存儲引擎在篩選大量數(shù)據(jù)時非常迅速，這是它最突出的優(yōu)點却妨。
- 插入密集型的表饵逐。MyISAM的并發(fā)插入特性允許同時選擇和插入數(shù)據(jù)。例如：MyISAM存儲引擎很適合管理郵件或Web服務(wù)器日志數(shù)據(jù)彪标。
MRG_MYISAM
MRG_MyISAM存儲引擎是一組MyISAM表的組合倍权，老版本叫 MERGE 其實是一回事兒，這些MyISAM表結(jié)構(gòu)必須完全相同捞烟，盡管其使用不如其它引擎突出薄声，但是在某些情況下非常有用。說白了题画，Merge表就是幾個相同MyISAM表的聚合器默辨；Merge表中并沒有數(shù)據(jù)，對Merge類型的表可以進行查詢苍息、更新缩幸、刪除操作，這些操作實際上是對內(nèi)部的MyISAM表進行操作档叔。
Merge存儲引擎的使用場景桌粉。對于服務(wù)器日志這種信息，一般常用的存儲策略是將數(shù)據(jù)分成很多表衙四，每個名稱與特定的時間端相關(guān)铃肯。例如：可以用12個相同的表來存儲服務(wù)器日志數(shù)據(jù)，每個表用對應(yīng)各個月份的名字來命名传蹈。當(dāng)有必要基于所有12個日志表的數(shù)據(jù)來生成報表押逼，這意味著需要編寫并更新多表查詢，以反映這些表中的信息惦界。與其編寫這些可能出現(xiàn)錯誤的查詢挑格，不如將這些表合并起來使用一條查詢，之后再刪除Merge表沾歪，而不影響原來的數(shù)據(jù)漂彤，刪除Merge表只是刪除Merge表的定義，對內(nèi)部的表沒有任何影響。
- ENGINE=MERGE挫望，指明使用MERGE引擎立润，其實是跟MRG_MyISAM一回事兒，也是對的媳板，在MySQL 5.7已經(jīng)看不到MERGE了桑腮。
- UNION=(t1, t2)，指明了MERGE表中掛接了些哪表蛉幸，可以通過alter table的方式修改UNION的值破讨，以實現(xiàn)增刪MERGE表子表的功能。比如：
alter table tb_merge engine=merge union(tb_log1) insert_method=last;
- INSERT_METHOD=LAST奕纫，INSERT_METHOD指明插入方式提陶，取值可以是：0 不允許插入；FIRST 插入到UNION中的第一個表若锁； LAST 插入到UNION中的最后一個表搁骑。
- MERGE表及構(gòu)成MERGE數(shù)據(jù)表結(jié)構(gòu)的各成員數(shù)據(jù)表必須具有完全一樣的結(jié)構(gòu)。每一個成員數(shù)據(jù)表的數(shù)據(jù)列必須按照同樣的順序定義同樣的名字和類型又固，索引也必須按照同樣的順序和同樣的方式定義。
MEMORY
使用MySQL Memory存儲引擎的出發(fā)點是速度煤率。為得到最快的響應(yīng)時間仰冠，采用的邏輯存儲介質(zhì)是系統(tǒng)內(nèi)存。雖然在內(nèi)存中存儲表數(shù)據(jù)確實會提供很高的性能蝶糯，但當(dāng)mysqld守護進程崩潰時洋只，所有的Memory數(shù)據(jù)都會丟失。獲得速度的同時也帶來了一些缺陷昼捍。它要求存儲在Memory數(shù)據(jù)表里的數(shù)據(jù)使用的是長度不變的格式识虚，這意味著不能使用BLOB和TEXT這樣的長度可變的數(shù)據(jù)類型，VARCHAR是一種長度可變的類型妒茬，但因為它在MySQL內(nèi)部當(dāng)做長度固定不變的CHAR類型担锤，所以可以使用。
一般在以下幾種情況下使用Memory存儲引擎：
- 目標(biāo)數(shù)據(jù)較小乍钻，而且被非常頻繁地訪問肛循。在內(nèi)存中存放數(shù)據(jù)，所以會造成內(nèi)存的使用银择，可以通過參數(shù)max_heap_table_size控制Memory表的大小多糠，設(shè)置此參數(shù)，就可以限制Memory表的最大大小浩考。
- 如果數(shù)據(jù)是臨時的夹孔，而且要求必須立即可用，那么就可以存放在內(nèi)存表中。
- 存儲在Memory表中的數(shù)據(jù)如果突然丟失搭伤，不會對應(yīng)用服務(wù)產(chǎn)生實質(zhì)的負(fù)面影響只怎。
- Memory同時支持散列索引和B樹索引。B樹索引的優(yōu)于散列索引的是闷畸，可以使用部分查詢和通配查詢尝盼，也可以使用<、>和>=等操作符方便數(shù)據(jù)挖掘佑菩。散列索引進行“相等比較”非扯苣快，但是對“范圍比較”的速度就慢多了殿漠，因此散列索引值適合使用在=和<>的操作符中赴精，不適合在<或>操作符中，也同樣不適合用在order by子句中绞幌。
CSV
CSV 存儲引擎是基于 CSV 格式文件存儲數(shù)據(jù)蕾哟。
- CSV 存儲引擎因為自身文件格式的原因，所有列必須強制指定 NOT NULL 莲蜘。
- CSV 引擎也不支持索引谭确，不支持分區(qū)。
- CSV 存儲引擎也會包含一個存儲表結(jié)構(gòu)的 .frm 文件票渠，還會創(chuàng)建一個 .csv 存儲數(shù)據(jù)的文件逐哈，還會創(chuàng)建一個同名的元信息文件，該文件的擴展名為 .CSM 问顷，用來保存表的狀態(tài)及表中保存的數(shù)據(jù)量昂秃。
- 每個數(shù)據(jù)行占用一個文本行。
因為 csv 文件本身就可以被Office等軟件直接編輯杜窄，保不齊就有不按規(guī)則出牌的情況肠骆，如果出現(xiàn)csv 文件中的內(nèi)容損壞了的情況，也可以使用 CHECK TABLE 或者 REPAIR TABLE 命令檢查和修復(fù)
ARCHIVE
Archive是歸檔的意思塞耕，在歸檔之后很多的高級功能就不再支持了蚀腿，僅僅支持最基本的插入和查詢兩種功能。在MySQL 5.5版以前荷科，Archive是不支持索引唯咬，但是在MySQL 5.5以后的版本中就開始支持索引了。Archive擁有很好的壓縮機制畏浆，它使用zlib壓縮庫胆胰，在記錄被請求時會實時壓縮，所以它經(jīng)常被用來當(dāng)做倉庫使用刻获。
BLACKHOLE
黑洞存儲引擎蜀涨，所有插入的數(shù)據(jù)并不會保存，BLACKHOLE 引擎表永遠(yuǎn)保持為空，寫入的任何數(shù)據(jù)都會消失厚柳，
PERFORMANCE_SCHEMA
主要用于收集數(shù)據(jù)庫服務(wù)器性能參數(shù)氧枣。MySQL用戶是不能創(chuàng)建存儲引擎為PERFORMANCE_SCHEMA的表，一般用于記錄binlog做復(fù)制的中繼别垮。在這里有官方的一些介紹MySQL Performance Schema
FEDERATED
主要用于訪問其它遠(yuǎn)程MySQL服務(wù)器一個代理便监，它通過創(chuàng)建一個到遠(yuǎn)程MySQL服務(wù)器的客戶端連接，并將查詢傳輸?shù)竭h(yuǎn)程服務(wù)器執(zhí)行碳想，而后完成數(shù)據(jù)存壬斩；在MariaDB的上實現(xiàn)是FederatedX
其他
這里列舉一些其它數(shù)據(jù)庫提供的存儲引擎胧奔，OQGraph逊移、SphinxSE、TokuDB龙填、Cassandra胳泉、CONNECT、SQUENCE岩遗。提供的名字僅供參考扇商。
常用引擎對比
不同存儲引起都有各自的特點，為適應(yīng)不同的需求宿礁，需要選擇不同的存儲引擎钳吟，所以首先考慮這些存儲引擎各自的功能和兼容。
| 特性 | 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 |
| 數(shù)據(jù)緩存(Data caches) | Yes | No | N/A | No |
| 索引緩存(Index caches) | Yes | Yes | N/A | No |
| 數(shù)據(jù)可壓縮(Compressed data) | Yes | Yes | No | Yes |
| 加密傳輸(Encrypted data[1]) | Yes | Yes | Yes | Yes |
| 集群數(shù)據(jù)庫支持(Cluster databases support) | No | No | No | No |
| 復(fù)制支持(Replication support[2]) | Yes | No | No | Yes |
| 外鍵支持(Foreign key support) | Yes | No | No | No |
| 存儲空間消耗(Storage Cost) | 高 | 低 | N/A | 非常低 |
| 內(nèi)存消耗(Memory Cost) | 高 | 低 | N/A | 低 |
| 數(shù)據(jù)字典更新(Update statistics for data dictionary) | Yes | Yes | Yes | Yes |
| 備份/時間點恢復(fù)(backup/point-in-time recovery[3]) | Yes | Yes | Yes | Yes |
| 多版本并發(fā)控制(Multi-Version Concurrency Control/MVCC) | Yes | No | No | No |
| 批量數(shù)據(jù)寫入效率(Bulk insert speed) | 慢 | 快 | 快 | 非尘秸快 |
| 地理信息數(shù)據(jù)類型(Geospatial datatype support) | Yes | Yes | No | Yes |
| 地理信息索引(Geospatial indexing support[4]) | Yes | Yes | No | Yes |
- 在服務(wù)器中實現(xiàn)（通過加密功能）。在其他表空間加密數(shù)據(jù)在MySQL 5.7或更高版本兼容坝茎。
- 在服務(wù)中實現(xiàn)的涤姊，而不是在存儲引擎中實現(xiàn)的。
- 在服務(wù)中實現(xiàn)的嗤放，而不是在存儲引擎中實現(xiàn)的思喊。
- 地理位置索引，InnoDB支持可mysql5.7.5或更高版本兼容
查看存儲引擎
使用“SHOW VARIABLES LIKE '%storage_engine%';” 命令在mysql系統(tǒng)變量搜索磨人設(shè)置的存儲引擎次酌，輸入語句如下：
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
使用“SHOW ENGINES;”命令顯示安裝以后可用的所有的支持的存儲引擎和默認(rèn)引擎恨课，后面帶上 \G 可以列表輸出結(jié)果，你可以嘗試一下如“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> |
+--------------------+---------+--------------------------------------+-------------+--------+-----------+
由上面命令輸出剂公，可見當(dāng)前系統(tǒng)的默認(rèn)數(shù)據(jù)表類型是InnoDB。當(dāng)然吊宋，我們可以通過修改數(shù)據(jù)庫配置文件中的選項纲辽，設(shè)定默認(rèn)表類型。
設(shè)置存儲引擎
對上面數(shù)據(jù)庫存儲引擎有所了解之后，你可以在my.cnf 配置文件中設(shè)置你需要的存儲引擎拖吼，這個參數(shù)放在 [mysqld] 這個字段下面的 default_storage_engine 參數(shù)值鳞上，例如下面配置的片段
[mysqld]
default_storage_engine=CSV
在創(chuàng)建表的時候，對表設(shè)置存儲引擎吊档，例如：
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;
在創(chuàng)建用戶表 user 的時候篙议，SQL語句最后 ENGINE=InnoDB 就是設(shè)置這張表存儲引擎為 InnoDB。
如何選擇合適的存儲引擎
提供幾個選擇標(biāo)準(zhǔn)怠硼，然后按照標(biāo)準(zhǔn)鬼贱，選擇對應(yīng)的存儲引擎即可，也可以根據(jù)常用引擎對比來選擇你使用的存儲引擎拒名。使用哪種引擎需要根據(jù)需求靈活選擇吩愧，一個數(shù)據(jù)庫中多個表可以使用不同的引擎以滿足各種性能和實際需求。使用合適的存儲引擎增显，將會提高整個數(shù)據(jù)庫的性能雁佳。
- 是否需要支持事務(wù)；
- 是否需要使用熱備同云；
- 崩潰恢復(fù)糖权，能否接受崩潰；
- 是否需要外鍵支持炸站；
- 存儲的限制星澳；
- 對索引和緩存的支持；
