mysql存儲引擎初探
目錄：
1、存儲引擎介紹
2、InnoDB及MyISAM性能對比
3、MyISAM和InnoDB的鎖對比
4、兩種存儲引擎索引對比
1?存儲引擎介紹
說明：基于5.7.19的mysql數據庫。
?
圖1.1 數據庫版本
在Navicat for MySQL中進行測試：
輸入sql：show engines;
?
圖1.2 存儲引擎類別
參數說明：
Engine：存儲引擎名稱
Support：MySQL是否支持該引擎
Comment：對該引擎的說明
Transaction：是否支持事務處理
XA：是否分布式交易處理XA規范
Savepoints：是否支持保存點，以便事務回滾到保存點
?
l?InnoDB
適用于高性能和事務處理環境，支持外鍵，默認的存儲引擎，“拆包即用”。
l?MyISAM
適用于以只讀為主的數據倉庫、電子商務和企業應用中。MyISAM使用高級緩存和索引機制提高數據檢索和索引速度，但不支持事務，不支持外鍵。
l?Blackhole
適用于測試應用程序確實在寫數據而又不希望在磁盤上存儲任何數據的場景。Blackhole存儲引擎滿足一個特定的需求，如果啟用了二進制日志，SQL語句將被寫入日志，將Blackhole存儲引擎作為復制拓撲中的中繼或代理。在這種情況下，中繼代理處理來自master的數據，并將這些數據發送到它的slave上去，但是它本身并不存儲任何數據。
l?CSV
適用于寫CSV日志文件，將結構化業務數據快速導入電子表格。CSV存儲引擎以表格形式創建、讀取和寫入逗號分隔值（CSV）文件。不提供任何索引機制，在存儲和轉換日期時間值時也存在某些問題，存儲數據的效率不高，因此應該謹慎使用。
l?Memory
適用于頻繁訪問而很少更改的靜態數據的情況，例如郵編列表、省市區列表、分類列表等等，以及適用于利用快照技術訪問分布數據或歷史數據的數據庫。Memory（有時被稱為HEAP）是內存中的存儲器，使用哈希機制檢索頻繁使用的數據，從而檢索更快。由于數據存儲在內存中，只在MySQL會話中有效，關機時數據被刷新并刪除。
l?Federated
適用于分布式或數據集環境。Federated存儲引擎允許將多個數據庫服務器的表連接起來。它不移動數據，也不要求遠程表適用相同的存儲引擎。目前Federated存儲引擎在MySQL的大部分發行版中是禁用的。
l?Archive
適用于存儲和檢索大量的很少訪問的存檔或歷史數據。Archive存儲引擎以壓縮格式存儲大量數據，不支持索引，只能通過表掃描訪問。
l?MRG_MYISAM
適用于非常大的數據庫應用，如數據倉庫，其中數據存儲在一個或多個數據庫的多個表中。MRG_MYISAM存儲引擎的最優的特點就是快速，它將一個大表分割成許多不同的小表，存儲在不同的磁盤上，把這些小表合并，然后同時訪問它們，搜索和排序執行得更快，因為每個小表需要管理的數據變少了。
缺點：
l?必須使用相同的MyISAM表組成一個合成表；
l?替換操作不可用；
l?索引比單表的索引效率低。
2?InnoDB及MyISAM性能對比
說明：測試表中含有36個字段，并含有988218條記錄。
InnoDB存儲引擎的測試數據庫名為Innodbtest，其中含有該表，表名為Innodbtable；MyISAM存儲引擎的測試數據庫名為Myisamtest，其中含有該表，表名為Myisamtable。
在MySQL中分別使用InnoDB及MyISAM存儲引擎對該表進行測試，首先進行前期工作：
（1）將測試MyISAM存儲引擎的表的存儲引擎從默認的InnoDB修改為MyISAM：
alter?table?myisamtable?engine=myisam;
?
圖2.1 修改存儲引擎
（2）修改數據庫的字符編碼，將其設置為utf-8
alter?database?myisamtest?character?set?utf8; alter?database?innodbtest?character?set?utf8;
?
圖2.2 修改InnoDB存儲引擎測試庫字符編碼
?
圖2.3 修改MyISAM存儲引擎測試庫字符編碼
對兩種存儲引擎的一些特性進行測試：
l?存儲結構
（1）InnoDB：
表數據都保存在一個大小為1.21GB的數據文件中——Innodbtable.ibd，與表相關的元數據信息存放在innodbtable.frm文件中，包括表結構的定義信息。db.opt中定義了數據庫的一些定義信息。
?
圖2.4 InnoDB磁盤存儲目錄
?
圖2.5 db.opt文件內容
（2）MyISAM：
.frm文件：存儲與表相關的元數據信息，包括表結構的定義信息等；
.MYD文件：大小為853.34MB，存放MyISAM表的數據。
.MYI文件：大小為34.11MB，存放MyISAM表的索引相關信息。
db.opt：定義了數據庫的一些定義信息。
?
圖2.6 MyISAM磁盤存儲目錄
?
圖2.7 db.opt文件內容
l?select
（1）InnoDB：
?
圖2.8 InnoDB的select測試
（2）MyISAM：
圖2.9 MyISAM的select測試
l?insert
（1）InnoDB：
?
圖2.10 InnoDB的insert測試
（2）MyISAM：
?
圖2.11 MyISAM的insert測試
l?update
（1）InnoDB：
?
圖2.12 InnoDB的update測試
（2）MyISAM：
?
圖2.13 MyISAM的update測試
l?delete
（1）InnoDB：
?
圖2.14 InnoDB的delete測試
（2）MyISAM：
圖2.15 MyISAM的delete測試
l?delete where
（1）InnoDB：
?
圖2.16 InnoDB的delete where測試
（2）MyISAM：
圖2.17 MyISAM的delete where測試
l?count without where
（1）InnoDB：
?
圖2.18 InnoDB的count without where測試
（2）MyISAM：
圖2.19 MyISAM的count without where測試
l?group by
（1）InnoDB：
?
圖2.20 InnoDB的group by測試
（2）MyISAM：
圖2.21 MyISAM的group by測試
l?外鍵
創建一個新表，將測試表的主鍵作為新表的外鍵進行測試：
create?table?`foreigntest`( `id`?int?primary?key?not?null, `taskid`?varchar(64)?not?null, `host`?varchar(128)?not?null?default?'', `month`?char(8)?not?null, constraint?`fk_task_h_m`?foreign?key?(`taskid`,`host`,`month`) references?`innodbtable`(`taskid`,`host`,`month`) )?charset=utf8mb4
（1）InnoDB：
?
圖2.22 InnoDB的外鍵測試
（2）MyISAM：
?
圖2.23 MyISAM的外鍵測試
?
總結如下表：
|
? |
InnoDB |
MyISAM |
|
存儲結構 |
.ibd：存放表數據； .frm文件：存儲與表相關的元數據信息，包括表結構的定義信息等； 基于磁盤的資源是InnoDB表空間數據文件和它的日志文件，InnoDB表的大小只受限于操作系統文件的大小，一般為2GB。 |
每個表在磁盤上存儲成三個文件： .MYD文件：存放表的數據。 .MYI文件：存放表的索引相關信息。 .frm文件：存儲與表相關的元數據信息，包括表結構的定義信息等； |
|
存儲空間 |
InnoDB的表需要更多的內存和磁盤存儲，它會在主內存中建立其專用的緩沖池用于高速緩沖數據和索引。 |
MyISAM可被壓縮，存儲空間較小。 |
|
可移植性 |
免費的方案可以是拷貝數據文件、備份 binlog，或者用 mysqldump，在數據量達到幾十G的時候就相對困難了 |
由于MyISAM的數據是以文件的形式存儲，所以在跨平臺的數據轉移中會很方便。在備份和恢復時可單獨針對某個表進行操作 |
|
事務安全 |
支持事務，具有事務(commit)、回滾(rollback)和崩潰修復能力 |
不支持事務，每次查詢具有原子性 |
|
增 |
更優（0.15秒） |
（0.40秒） |
|
刪(帶where) |
（32.79秒） |
更優（16.51秒） |
|
全刪 |
（263.86秒） |
更優（0.24秒） |
|
改 |
（0.20秒） |
更優（0.12秒） |
|
查 |
（139.75秒） |
更優（65.57秒） |
|
鎖 |
支持表鎖、行鎖，行鎖大幅度提高了多用戶并發操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。 |
只支持表鎖 |
|
外鍵 |
支持 |
不支持 |
|
count without where |
沒有保存表的具體行數，需要逐行掃描統計（70.88秒） |
更優，因為MyISAM保存了表的具體行數，只需簡單讀出。（0.09秒） |
|
group by |
（35.14秒） |
更優（4.75秒） |
附注：
[1]表空間：InnoDB用來組織與機器無關的文件的工具，包括數據、索引及回滾機制。默認情況下，所有表共享一個表空間（稱為共享表空間）。共享表空間不會自動擴展成多個文件。默認情況下，一個表空間只占據單個文件，該文件隨數據增加而增長。使用autoextend選項可以允許表空間創建新的文件。
[2]崩潰修復能力：InnoDB存儲引擎使用兩種基于磁盤的機制存儲數據，即日志文件和表空間。在關機或死機之前，InnoDB會使用這些日志來重建數據恢復。在程序啟動時，InnoDB讀取日志并自動將臟頁寫入磁盤，從而在系統崩潰前恢復緩沖中的更新。
3?MyISAM和InnoDB的鎖對比
（1）表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低。
（2）行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。
（3）對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫請求；MyISAM表的讀和寫操作之間，以及寫和寫操作之間是串行的（當一線程獲得對一個表的寫鎖后，只有持有鎖的線程可以對表進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。）
（4）共享鎖（s）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
（5）排他鎖（X）：允許獲取排他鎖的事務更新數據，阻止其他事務取得相同的數據集共享讀鎖和排他寫鎖。
（6）對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數據集加排他鎖（X）；對于普通SELECT語句，InnoDB不會加任何鎖。
4?兩種存儲引擎索引對比
l?InnoDB：
l?在InnoDB中，表數據文件本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點data域保存了完整的數據記錄。這個索引的key是數據表的主鍵，因此InnoDB表數據文件本身就是主索引。
l?InnoDB表使用的索引是聚集索引。聚集索引是一種數據結構，不僅存儲索引，也存儲數據本身。因此，一旦定位到索引中的某個值，就可以直接檢索數據而無需額外的磁盤尋道。
l?主鍵索引或者表的第一個索引都采用聚集索引創建。
l?InnoDB的所有輔助索引都引用主鍵作為data域。如果如果創建了輔助索引，聚集索引的關鍵字（主鍵、唯一鍵或行ID）也會存在輔助索引中，這樣可以快速按照關鍵字查找和快速獲取聚集索引中的原始數據。也就是如果使用主鍵列掃描輔助索引，則查詢只需要用輔助索引就可以獲取數據。
l?MyISAM：
l?索引文件和數據文件是分離的，索引文件僅保存數據記錄的地址。使用B+樹作為索引結構，葉節點的data域存放的是數據記錄的地址。
l?在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重復。
?
l?主要區別：
l?主索引的區別：InnoDB的數據文件本身就是索引文件。而MyISAM的索引和數據是分開的。
l?輔助索引的區別：InnoDB的輔助索引data域存儲相應記錄主鍵的值而不是地址。而MyISAM的輔助索引和主索引沒有多大區別。
附注：
B+樹：對于一棵m階B+樹，具有如下特點：
l?有n棵子樹的節點中含有n個關鍵字。
l?所有的葉子結點中包含了全部關鍵字的信息，及指向含這些關鍵字記錄的指針。且葉子結點本身依照關鍵字的大小自小而大順序鏈接。
l?所有的非終端結點可以看成索引部分，結點中僅含其子樹（根結點）中的最大（或最小）關鍵字。
l?在B+樹，不管查找成功與否，每次查找都是走了一條從根到葉子結點的路徑。
l?樹中每個結點最多含有m棵子樹。
?
