MySQL的四種主要儲存引擎

標籤：中文   約束   select   derby   ase   使用者操作   mysqld   產生   ble   

在資料庫中存的就是一張張有著千絲萬縷關係的表，所以表設計的好壞，將直接影響著整個資料庫。而在設計表的時候，我們都會關注一個問題，使用什麼儲存引擎。等一下，儲存引擎？什麼是儲存引擎？

　　什麼是儲存引擎？

　　關聯式資料庫表是用於儲存和組織資訊的資料結構，可以將表理解為由行和列組成的表格，類似於Excel的試算表的形式。有的表簡單，有的表複雜，有的表根本不用來儲存任何長期的資料，有的表讀取時非常快，但是插入資料時去很差；而我們在實際開發過程中，就可能需要各種各樣的表，不同的表，就意味著儲存不同類型的資料，資料的處理上也會存在著差異，那麼。對於MySQL來說，它提供了很多種類型的儲存引擎，我們可以根據對資料處理的需求，選擇不同的儲存引擎，從而最大限度的利用MySQL強大的功能。這篇博文將總結和分析各個引擎的特點，以及適用場合，並不會糾結於更深層次的東西。我的學習方法是先學會用，懂得怎麼用，再去知道到底是如何能用的。下面就對MySQL支援的儲存引擎進行簡單的介紹。

　　MySQL5.5以後預設使用InnoDB儲存引擎，其中InnoDB和BDB提供事務安全表，其它儲存引擎都是非事務安全表。

　　若要修改預設引擎，可以修改設定檔中的default-storage-engine。可以通過：showvariableslike‘default_storage_engine‘;查看當前資料庫到預設引擎。命令：showengines和showvariableslike‘have%‘可以列出當前資料庫所支援到引擎。其中Value顯示為disabled的記錄表示資料庫支援此引擎，而在資料庫啟動時被禁用。在MySQL5.1以後，INFORMATION_SCHEMA資料庫中存在一個ENGINES的表，它提供的資訊與showengines;語句完全一樣，可以使用下面語句來查詢哪些儲存引擎支援事物處理：selectenginefrominformation_chema.engineswheretransactions=‘yes‘;

　　可以通過engine關鍵字在建立或修改資料庫時指定所使用到引擎。

　　主要儲存引擎：MyISAM、InnoDB、MEMORY和MERGE介紹：

　　在建立表到時候通過engine=...或type=...來指定所要使用到引擎。showtablestatusfromDBname來查看指定表到引擎。

　　(一)MyISAM

　　它不支援事務，也不支援外鍵，尤其是訪問速度快，對事務完整性沒有要求或者以SELECT、INSERT為主的應用基本都可以使用這個引擎來建立表。

　　每個MyISAM在磁碟上儲存成3個檔案，其中檔案名稱和表名都相同，但是副檔名分別為：

　　.frm(儲存表定義)

　　MYD(MYData，儲存資料)

　　MYI(MYIndex，儲存索引)

　　資料檔案和索引檔案可以放置在不同的目錄，平均分配IO，擷取更快的速度。要指定資料檔案和索引檔案的路徑，需要在建立表的時候通過DATADIRECTORY和INDEXDIRECTORY語句指定，檔案路徑需要使用絕對路徑。

　　每個MyISAM表都有一個標誌，伺服器或myisamchk程式在檢查MyISAM資料表時會對這個標誌進行設定。MyISAM表還有一個標誌用來表明該資料表在上次使用後是不是被正常的關閉了。如果伺服器以為當機或崩潰，這個標誌可以用來判斷資料表是否需要檢查和修複。如果想讓這種檢查自動進行，可以在啟動伺服器時使用--myisam-recover現象。這會讓伺服器在每次開啟一個MyISAM資料表是自動檢查資料表的標誌並進行必要的修複處理。MyISAM類型的表可能會損壞，可以使用CHECKTABLE語句來檢查MyISAM表的健康，並用REPAIRTABLE語句修複一個損壞到MyISAM表。

　　MyISAM的表還支援3種不同的儲存格式：

　　靜態(固定長度)表

　　動態表

　　壓縮表

　　其中靜態表是預設的儲存格式。靜態表中的欄位都是非變長欄位，這樣每個記錄都是固定長度的，這種儲存方式的優點是儲存非常迅速，容易緩衝，出現故障容易恢複；缺點是佔用的空間通常比動態表多。靜態表在資料存放區時會根據列定義的寬度定義補足空格，但是在訪問的時候並不會得到這些空格，這些空格在返回給應用之前已經去掉。同時需要注意：在某些情況下可能需要返回欄位後的空格，而使用這種格式時後面到空格會被自動處理掉。

　　動態表包含變長欄位，記錄不是固定長度的，這樣儲存的優點是佔用空間較少，但是頻繁到更新刪除記錄會產生片段，需要定期執行OPTIMIZETABLE語句或myisamchk-r命令來改善效能，並且出現故障的時候恢複相對比較困難。

　　壓縮表由myisamchk工具建立，佔據非常小的空間，因為每條記錄都是被單獨壓縮的，所以只有非常小的訪問開支。

　　(二)InnoDB

　　InnoDB是一個健壯的事務型儲存引擎，這種儲存引擎已經被很多互連網公司使用，為使用者操作非常大的資料存放區提供了一個強大的解決方案。我的電腦上安裝的MySQL5.6.13版，InnoDB就是作為預設的儲存引擎。InnoDB還引入了行級鎖定和外鍵約束，在以下場合下，使用InnoDB是最理想的選擇：

　　1.更新密集的表。InnoDB儲存引擎特別適合處理多重並發的更新要求。

　　2.事務。InnoDB儲存引擎是支援事務的標準MySQL儲存引擎。

　　3.自動災難恢複。與其它儲存引擎不同，InnoDB表能夠自動從災難中恢複。

　　4.外鍵約束。MySQL支援外鍵的儲存引擎只有InnoDB。

　　5.支援自動增加列AUTO_INCREMENT屬性。

　　InnoDB儲存引擎提供了具有提交、復原和崩潰恢複能力的事務安全。但是對比MyISAM的儲存引擎，InnoDB寫的處理效率差一些並且會佔用更多的磁碟空間以保留資料和索引。

　　1)自動成長列：

　　InnoDB表的自動成長列可以手工插入，但是插入的如果是空或0，則實際插入到則是自動成長後到值。可以通過"ALTERTABLE...AUTO_INCREMENT=n;"語句強制設定自動成長值的起始值，預設為1，但是該強制到預設值是儲存在記憶體中，資料庫重啟後該值將會丟失。可以使用LAST_INSERT_ID()查詢當前線程最後插入記錄使用的值。如果一次插入多條記錄，那麼返回的是第一條記錄使用的自動成長值。

　　對於InnoDB表，自動成長列必須是索引。如果是複合式索引，也必須是複合式索引的第一列，但是對於MyISAM表，自動成長列可以是複合式索引的其他列，這樣插入記錄後，自動成長列是按照複合式索引到前面幾列排序後遞增的。

　　2)外鍵約束：

　　MySQL支援外鍵的儲存引擎只有InnoDB，在建立外鍵的時候，父表必須有對應的索引，子表在建立外鍵的時候也會自動建立對應的索引。

　　在建立索引的時候，可以指定在刪除、更新父表時，對子表進行的相應操作，包括restrict、cascade、setnull和noaction。其中restrict和noaction相同，是指限制在子表有關聯的情況下，父表不能更新；casecade表示父表在更新或刪除時，更新或者刪除子表對應的記錄；setnull則表示父表在更新或者刪除的時候，子表對應的欄位被setnull。

　　當某個表被其它表建立了外鍵參照，那麼該表對應的索引或主鍵被禁止刪除。

　　可以使用setforeign_key_checks=0;臨時關閉外鍵約束，setforeign_key_checks=1;開啟約束。

　　(三)MEMORY

　　memory使用存在記憶體中的內容來建立表。每個MEMORY表實際對應一個磁碟檔案，格式是.frm。MEMORY類型的表訪問非常快，因為它到資料是放在記憶體中的，並且預設使用HASH索引，但是一旦伺服器關閉，表中的資料就會丟失，但表還會繼續存在。

　　預設情況下，memory資料表使用散列索引，利用這種索引進行“相等比較”非常快，但是對“範圍比較”的速度就慢多了。因此，散列索引值適合使用在"="和"<=>"的操作符中，不適合使用在"<"或">"操作符中，也同樣不適合用在orderby字句裡。如果確實要使用"<"或">"或betwen操作符，可以使用btree索引來加快速度。

　　儲存在MEMORY資料表裡的資料行使用的是長度不變的格式，因此加快處理速度，這意味著不能使用BLOB和TEXT這樣的長度可變的資料類型。VARCHAR是一種長度可變的類型，但因為它在MySQL內部當作長度固定不變的CHAR類型，所以可以使用。

　　createtabletab_memoryengine=memoryselectid,name,age,addrfrommanorderbyid;

　　使用USINGHASH/BTREE來指定特定到索引。

　　createindexmem_hashusinghashontab_memory(city_id);

　　在啟動MySQL服務的時候使用--init-file選項，把insertinto...select或loaddatainfile這樣的語句放入到這個檔案中，就可以在服務啟動時從持久穩固的資料來源中裝載表。

　　伺服器需要足夠的記憶體來維持所在的在同一時間使用的MEMORY表，當不再使用MEMORY表時，要釋放MEMORY表所佔用的記憶體，應該執行DELETEFROM或truncatetable或者刪除整個表。

　　每個MEMORY表中放置到資料量的大小，受到max_heap_table_size系統變數的約束，這個系統變數的初始值是16M，同時在建立MEMORY表時可以使用MAX_ROWS子句來指定表中的最大行數。

      使用MySQL Memory儲存引擎的出發點是速度。為得到最快的回應時間，採用的邏輯儲存介質是系統記憶體。雖然在記憶體中儲存表資料確實會提供很高的效能，但當mysqld守護進程崩潰時，所有的Memory資料都會丟失。獲得速度的同時也帶來了一些缺陷。它要求儲存在Memory資料表裡的資料使用的是長度不變的格式，這意味著不能使用BLOB和TEXT這樣的長度可變的資料類型，VARCHAR是一種長度可變的類型，但因為它在MySQL內部當做長度固定不變的CHAR類型，所以可以使用。

　　(四)MERGE

　　merge儲存引擎是一組MyISAM表的組合，這些MyISAM表結構必須完全相同，MERGE表中並沒有資料，對MERGE類型的表可以進行查詢、更新、刪除的操作，這些操作實際上是對內部的MyISAM表進行操作。對於對MERGE表進行的插入操作，是根據INSERT_METHOD子句定義的插入的表，可以有3個不同的值，first和last值使得插入操作被相應的作用在第一個或最後一個表上，不定義這個子句或者為NO，表示不能對這個MERGE表進行插入操作。可以對MERGE表進行drop操作，這個操作只是刪除MERGE表的定義，對內部的表沒有任何影響。MERGE在磁碟上保留2個以MERGE表名開標頭檔：.frm檔案儲存體表的定義；.MRG檔案包含組合表的資訊，包括MERGE表由哪些表組成，插入資料時的依據。可以通過修改.MRG檔案來修改MERGE表，但是修改後要通過flushtable重新整理。

　　createtableman_all(idint,namevarchar(20))engine=mergeunion=(man1,man2)insert_methos=last;

MySQL的四種主要儲存引擎