存儲引擎

DBMS使用存儲引擎進行數(shù)據(jù)的創(chuàng)建、查詢、更新、刪除操作。

不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能，使用不同的存儲引擎還可以獲得特定的功能。

MySQL提供了多種存儲引擎，最常見的有3種。

1、MyISAM 

MySQL5.5之前的默認的存儲引擎，每個MyISAM在磁盤上存儲成三個文件，文件名和表名相同，擴展名分別是

（1）frm文件：存儲表的定義

（2）MYD文件：存儲表中的數(shù)據(jù)（記錄）

（3）MYI文件：存儲索引。索引保存的是數(shù)據(jù)文件的指針

特點

• 訪問速度快（優(yōu)）
• 支持全文索引（優(yōu)）。但基本不使用MySQL的全文索引，全文索引一般都是使用第三方的ElasticSearch，Solr等更加成熟的解決方案。

• 不支持事務、外鍵（缺）
• 默認的鎖粒度為表級鎖，所以并發(fā)度很差（缺），但加鎖快，鎖沖突較少，不容易發(fā)生死鎖（優(yōu)）；

• 對數(shù)據(jù)的查詢緩存只緩存索引，不緩存數(shù)據(jù)（記錄），而且使用的是操作系統(tǒng)本身的緩存。
• 自動增長列可以是組合主鍵中的任何一列。設置自動增長列：創(chuàng)建表時，在字段后加auto_increment，默認初始值是1

適用場景

• 不需要事務
• 不使用外鍵
• 操作以select、insert為主

MyISAM支持三種不同的存儲格式

（1）靜態(tài)表（fixed）

表中不能包含變長字段（比如VARCHAR, BLOB, TEXT），每個記錄都是固定的長度。如果MyISAM表中沒有一個變長字段，則默認使用靜態(tài)表。

優(yōu)點：存儲迅速，出現(xiàn)故障容易恢復

缺點：占用空間比動態(tài)表大。靜態(tài)表在進行數(shù)據(jù)存儲時會按照事先定義的列寬補足空格，但在訪問的時候會去掉補的空格

（2）動態(tài)表（dynamic）

可包含變長字段（varchar、blob、text），如果一個MyISAM表包含任何可變長度的字段，或者該表創(chuàng)建時用row_format=dynamic指定，則該表使用動態(tài)格式存儲

優(yōu)點：占用空間小

缺點：頻繁的更新、刪除操作會產(chǎn)生碎片，需要定期用optimize table語句或myisamchk -r命令來改善性能，出現(xiàn)故障后較難恢復

（3）壓縮表

由myisampack工具創(chuàng)建，每條記錄都會被單獨壓縮，占據(jù)硬盤空間極小。

2、InnoDB 

MySQL5.5及其之后默認的存儲引擎

InnoDB有2種存儲方式

• 共享表空間存儲：所有表的定義、數(shù)據(jù)、索引存放在同一個表空間中。
• 獨占表空間存儲：一張表獨占一個表空間，表的定義保存在.frm文件中，數(shù)據(jù)、索引保存在.ibd文件中。

特點

• 自動增長列必須是主鍵，如果是組合主鍵，也必須是組合主鍵的第一列

• 支持外鍵約束。MySQL的存儲引擎中只有innoDB支持外鍵。外鍵降低了查詢速度（因為要查多張表）、使多張表耦合在一起，但更好地體現(xiàn)了實體、表之間的關聯(lián)。
• 支持事務，恢復能力強。默認的事務隔離級別為可重復讀
• 使用的鎖粒度為行級鎖，支持更高的并發(fā)。行級鎖、事務，安全性有了，但是以犧牲效率換來的。
• 會將查詢的結果（索引+數(shù)據(jù)）放到緩沖池中，加快后續(xù)查詢的速度
• 發(fā)生故障后恢復性好。未完成的事務將根據(jù)redo log的數(shù)據(jù)重做；已提交但未寫入的修改，將從doublewrite buffer重做；系統(tǒng)閑時會purge buffer
• 主鍵索引是聚集索引(Clustered index，僅InnoDB支持)，根據(jù)主鍵查詢時效率高，但根據(jù)主鍵進行刪改時效率低。聚集索引：物理存儲順序與索引順序相同
• 支持在線熱備

適用場景

• 對安全性有要求（事務+恢復性好），比如財務、計費、銀行
• 并發(fā)高（行級鎖）
• 使用外鍵

MyISAM、InnoDB的對比

（1）鎖粒度不同，InnoDB為行級鎖，MyISAM為表級鎖

• InnoDB對并發(fā)的支持遠比MyISAM高
• 但InnoDB鎖沖突的概率更大，更容易發(fā)生死鎖，而且為每一行加鎖，開銷也很大

（2）InnoDB支持外鍵，MyISAM不支持

（3）InnoDB支持事務，MyISAM不支持

• InnoDB更安全，開銷也更大

（4）InnoDB緩存查詢到的索引、數(shù)據(jù)，MyISAM只緩存索引

（5）查詢效率MyISAM遠高于InnoDB，尤其是在數(shù)據(jù)表行數(shù)多的時候

• MyISAM的索引中存儲的是數(shù)據(jù)（記錄）的指針（地址），先查索引確定要操作的記錄的地址，直接就去訪問這個地址
• InnoDB的索引存儲的是記錄的行號，從索引中查到行號（行坐標），還需要逐行統(tǒng)計行號（從第一行開始數(shù)）
• 而且InnoDB在查詢過程中，要維護緩沖池中的查詢緩存（索引+數(shù)據(jù)），MyISAM只需維護緩存中的索引

（6）InnoDB支持在線熱備，有很成熟的在線熱備解決方案

（7）MyISAM的表文件包括：.frm(表定義)，.MYI(索引)，.MYD(數(shù)據(jù))，InnoDB的表文件為.frm(表定義)，.ibd（索引、數(shù)據(jù)）

一句話，MyISAM效率更高、速度更快，InnoDB功能更全、安全性更好、開銷更大。

3、MEMORY

數(shù)據(jù)（記錄）不是儲存在文件中，而是存儲在內存中。每個memory表對應一個.frm文件（表定義、索引）。

特點

• 訪問速度極快。數(shù)據(jù)存放在內存中+使用HASH索引
• 宕機、關閉服務器，數(shù)據(jù)會丟失
• 表的大小有限制（內初有限）
• 對表的數(shù)據(jù)類型有限制。比如：只支持定長類型，VARCHAR會被自動存儲為CHAR類型，不支持TEXT、BLOB
• 鎖粒度為表級鎖。并發(fā)量大的時候，表級鎖會成為MEMORY存儲引擎的瓶頸

如何選擇合適的存儲引擎

• 安全性要求（事務）
• 并發(fā)高不高
• 使不使用外鍵
• 是否需要支持在線熱備

一個數(shù)據(jù)庫中，不同的表可以使用不同的存儲引擎。

使用合適的存儲引擎，會提高數(shù)據(jù)庫的性能。