MySQL的儲存引擎主要包含:
MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等等。其中InnoDB和BDB是支援事務安全表,其他都是非事務安全表。各種引擎特點對比:
以上幾個規則引擎中,其中MyISAM和InnoDB最常用,所以以下就對這兩個規則引擎進行介紹。
MyISAM:
特點:
- 不支援事務
- 不支援外來鍵
- 訪問速度快
- 支援表級鎖
MyISAM適合只有insert 和 select的場景。每個MyISAM在磁碟上儲存成三個檔案,檔名和資料表名相同,但副檔名不同:
- .frm(儲存表定義)
- .MYD(儲存資料)
- .MYI(儲存索引) 資料檔案和索引檔案如果放在不同的目錄則可以平均IO,更有利於操作速度。在建立資料表的時候可以通過DATA DIRECTORY 和 INDEX DIRECTORY 語句指定資料檔案的位置和索引檔案的位置,需要注意的是指定的路徑需要是絕對路徑且需要有訪問的許可權。
MyISAM還支援三種不同的儲存格式:
- 靜態表(固定長度): 預設的儲存格式,欄位都是非變長欄位,優點:儲存迅速、容易快取、容易恢復 缺點:佔用的空間較多,儲存的時候會安裝列的寬度定義補足空格,但是查詢的時候會去掉這部分空格;所以如果我們本身就想在已有的欄位內容前後加空格的話,查詢的時候也會被去掉。這一點必須要注意。
- 動態表:變長欄位, 優點:佔用的空間少 缺點:頻繁更新、刪除會產生空間碎片,需要執行OPTIMIZE TABLE或myisamchk-r命令來改善效能。
- 壓縮表:佔用的磁碟空間非常小,但是恢復很困難
InnoDB:
特點
- 支援事務
- 自動增長列,自增長列必須是索引,如果是組合索引也必須是第一列,這個一點和MyISAM不同
- 支援外來鍵約束
- 支援行級鎖 適合需要事務支援的場景。操作效率相對於MyISAM相對低一些。
InnoDB支援兩種儲存方式:
- 使用共享表空間儲存,這種方式建立表的結構報錯在.frm檔案中,資料和索引分別報錯在innodb_data_home_dir和innodb_data_file_path指定的對應地址。
- 使用多表空間儲存,這種方式建立的表的結構也是儲存在.frm檔案中,但是每個表的索引和資料單獨儲存在.ibd中,如果是分割槽表則,每個分割槽單獨對應一個.ibd檔案,檔名是 “表名+分割槽名”,也可以在建立表的時候指定分割槽的資料檔案的位置,這樣可以將表的IO均分到各個磁碟部分。要使用多表空間必須注意的是需要配置innodb_file_par_table,且重啟才生效。
還有一點需要注意:即使使用了多表空間儲存,但是共享表空間仍然是需要的,innoDB把內部資料詞典和線上重做日誌儲存在共享空間中。
如何檢視資料庫的儲存檔案
儲存檔案一般存放在/var/lib/mysql/目錄下,以資料庫名劃分子目錄,如: