MySQL儲存引擎初探
- 一个新手原創
- 2017-09-08 11:10:481285瀏覽
MySQL儲存引擎初探
#目錄：
1、儲存引擎介紹
2、InnoDB及MyISAM效能比較
##3 、MyISAM與InnoDB的鎖定比較
4、兩個儲存引擎索引比較
#1
#儲存引擎介紹
說明：基於5.7.19的MySQL資料庫。
#在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.5 db.opt檔案內容
（2）MyISAM：
.frm檔案：儲存與資料表相關的元資料訊息，包括表結構的定義資訊等；
.MYD檔案：大小為853.34MB，存放MyISAM表的資料。
db.opt：定義了資料庫的一些定義資訊。
####################################################################################################################### ##########圖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：
l delete where（1）InnoDB：
#
# #圖2.16 InnoDB的delete 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的資料是以檔案的形式存儲，所以在跨平台的資料轉移中會很方便。在備份和復原時可單獨針對某個表進行操作 |
交易安全性 |
||
|
不支援事務，每次查詢具有原子性 |
增 |
|
#（0.40秒） |
||
更優（16.51秒） |
||
更優（0.24秒) |
||
| ## （0.20秒） 更優（0.12秒） |
#查 |
|
| （139.75秒） 更優（65.57秒） |
#################################################################### ####支援表鎖、行鎖，行鎖大幅提高了多用戶並發操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。 ##################只支援表鎖定############################外鍵#####################支援####################################################################### |
|
count without where |
沒有儲存表格的具體行數，需要逐行掃描統計（70.88秒） |
#更優，因為MyISAM保存了表格的特定行數，只需簡單讀出。 （0.09秒） |
#group by |
附註：
[1]表空間：InnoDB用來組織與機器無關的檔案的工具，包括資料、索引及回滾機制。預設情況下，所有表共享一個表空間（稱為共享表空間）。共享表空間不會自動擴展成多個檔案。預設情況下，一個表空間只佔據單一文件，該文件隨資料增加而成長。使用autoextend選項可以允許表空間建立新的檔案。
[2]崩潰修復能力：InnoDB儲存引擎使用兩種基於磁碟的機制儲存數據，即日誌檔案和表空間。在關機或當機之前，InnoDB會使用這些日誌來重建資料復原。在程式啟動時，InnoDB讀取日誌並自動將髒頁寫入磁碟，從而在系統崩潰前恢復緩衝中的更新。
3 MyISAM與InnoDB的鎖定比較
（1）表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的機率最高，並發度最低。
（2）行級鎖定：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖定衝突的機率最低，並發度也最高。
（3）對MyISAM表的讀取操作，不會阻塞其他使用者對相同表的讀取請求，但會阻塞對同一表的寫入請求；對MyISAM表的寫入操作，則會阻塞其他使用者對同一表的讀和寫請求；MyISAM表的讀和寫操作之間，以及寫和寫操作之間是串行的（當一線程獲得對一個表的寫鎖後，只有持有鎖的執行緒可以對錶進行更新操作。允許一個事務去讀一行，阻止其他事務獲得相同資料集的排他鎖。
（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 主要差異：
##l 主索引的差異：InnoDB的資料檔本身就是索引檔。而MyISAM的索引和資料是分開的。
l 輔助索引的差異：InnoDB的輔助索引data域儲存對應記錄主鍵的值而不是位址。而MyISAM的輔助索引和主索引沒有太大差別。
附註：###l 樹中每個結點最多含有m棵樹。 ############ ###########B+樹：
對於一棵m階B+樹，具有下列特點：
l 有n棵樹的節點中含有n個關鍵字。
l 所有的葉子結點中包含了全部關鍵字的訊息，及指向含這些關鍵字記錄的指標。且葉子結點本身依照關鍵字的大小自小而大順序連結。
l 所有的非終端結點可以看成索引部分，結點中僅含其子樹（根結點）中的最大（或最小）關鍵字。
l 在B+樹，不管尋找成功與否，每次查找都是走了一條從根到葉子結點的路徑。
以上是MySQL儲存引擎初探的詳細內容。更多資訊請關注PHP中文網其他相關文章！