MySQL存儲引擎的詳細介紹

　　關(guān)系數(shù)據(jù)庫表是用于存儲和組織信息的數(shù)據(jù)結(jié)構(gòu)，可以將表理解為由行和列組成的表格，類似于Excel的電子表格的形式。有的表簡單，有的表復(fù)雜，有的表根本不用來存儲任何長期的數(shù)據(jù)，有的表讀取時非�？�，但是插入數(shù)據(jù)時去很差；而我們在實際開發(fā)過程中，就可能需要各種各樣的表，不同的表，就意味著存儲不同類型的數(shù)據(jù)，數(shù)據(jù)的處理上也會存在著差異，那么。對于MySQL來說，它提供了很多種類型的存儲引擎，我們可以根據(jù)對數(shù)據(jù)處理的需求，選擇不同的存儲引擎，從而最大限度的利用MySQL強大的功能。這篇博文將總結(jié)和分析各個引擎的特點，以及適用場合，并不會糾結(jié)于更深層次的東西。我的學(xué)習(xí)方法是先學(xué)會用，懂得怎么用，再去知道到底是如何能用的。下面就對MySQL支持的存儲引擎進行簡單的介紹。

　　MySQL5.5以后默認使用InnoDB存儲引擎，其中InnoDB和BDB提供事務(wù)安全表，其它存儲引擎都是非事務(wù)安全表。

　　若要修改默認引擎，可以修改配置文件中的default-storage-engine�？梢酝ㄟ^：showvariableslike'default_storage_engine';查看當(dāng)前數(shù)據(jù)庫到默認引擎。命令：showengines和showvariableslike'have%'可以列出當(dāng)前數(shù)據(jù)庫所支持到引擎。其中Value顯示為disabled的記錄表示數(shù)據(jù)庫支持此引擎，而在數(shù)據(jù)庫啟動時被禁用。在MySQL5.1以后，INFORMATION_SCHEMA數(shù)據(jù)庫中存在一個ENGINES的表，它提供的信息與showengines;語句完全一樣，可以使用下面語句來查詢哪些存儲引擎支持事物處理：selectenginefrominformation_chema.engineswheretransactions='yes';

　　可以通過engine關(guān)鍵字在創(chuàng)建或修改數(shù)據(jù)庫時指定所使用到引擎。

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

　　在創(chuàng)建表到時候通過engine=...或type=...來指定所要使用到引擎。showtablestatusfromDBname來查看指定表到引擎。

　　(一)MyISAM

　　它不支持事務(wù)，也不支持外鍵，尤其是訪問速度快，對事務(wù)完整性沒有要求或者以SELECT、INSERT為主的應(yīng)用基本都可以使用這個引擎來創(chuàng)建表。

　　每個MyISAM在磁盤上存儲成3個文件，其中文件名和表名都相同，但是擴展名分別為：

　　.frm(存儲表定義)

　　MYD(MYData，存儲數(shù)據(jù))

　　MYI(MYIndex，存儲索引)

　　數(shù)據(jù)文件和索引文件可以放置在不同的目錄，平均分配IO，獲取更快的速度。要指定數(shù)據(jù)文件和索引文件的路徑，需要在創(chuàng)建表的時候通過DATADIRECTORY和INDEXDIRECTORY語句指定，文件路徑需要使用絕對路徑。

　　每個MyISAM表都有一個標(biāo)志，服務(wù)器或myisamchk程序在檢查MyISAM數(shù)據(jù)表時會對這個標(biāo)志進行設(shè)置。MyISAM表還有一個標(biāo)志用來表明該數(shù)據(jù)表在上次使用后是不是被正常的關(guān)閉了。如果服務(wù)器以為當(dāng)機或崩潰，這個標(biāo)志可以用來判斷數(shù)據(jù)表是否需要檢查和修復(fù)。如果想讓這種檢查自動進行，可以在啟動服務(wù)器時使用--myisam-recover現(xiàn)象。這會讓服務(wù)器在每次打開一個MyISAM數(shù)據(jù)表是自動檢查數(shù)據(jù)表的標(biāo)志并進行必要的修復(fù)處理。MyISAM類型的表可能會損壞，可以使用CHECKTABLE語句來檢查MyISAM表的健康，并用REPAIRTABLE語句修復(fù)一個損壞到MyISAM表。

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

　　靜態(tài)(固定長度)表

　　動態(tài)表

　　壓縮表

　　其中靜態(tài)表是默認的存儲格式。靜態(tài)表中的字段都是非變長字段，這樣每個記錄都是固定長度的，這種存儲方式的優(yōu)點是存儲非常迅速，容易緩存，出現(xiàn)故障容易恢復(fù)；缺點是占用的空間通常比動態(tài)表多。靜態(tài)表在數(shù)據(jù)存儲時會根據(jù)列定義的寬度定義補足空格，但是在訪問的時候并不會得到這些空格，這些空格在返回給應(yīng)用之前已經(jīng)去掉。同時需要注意：在某些情況下可能需要返回字段后的空格，而使用這種格式時后面到空格會被自動處理掉。

　　動態(tài)表包含變長字段，記錄不是固定長度的，這樣存儲的優(yōu)點是占用空間較少，但是頻繁到更新刪除記錄會產(chǎn)生碎片，需要定期執(zhí)行OPTIMIZETABLE語句或myisamchk-r命令來改善性能，并且出現(xiàn)故障的時候恢復(fù)相對比較困難。

　　壓縮表由myisamchk工具創(chuàng)建，占據(jù)非常小的空間，因為每條記錄都是被單獨壓縮的，所以只有非常小的訪問開支。

　　(二)InnoDB

　　InnoDB是一個健壯的事務(wù)型存儲引擎，這種存儲引擎已經(jīng)被很多互聯(lián)網(wǎng)公司使用，為用戶操作非常大的數(shù)據(jù)存儲提供了一個強大的解決方案。我的電腦上安裝的MySQL5.6.13版，InnoDB就是作為默認的存儲引擎。InnoDB還引入了行級鎖定和外鍵約束，在以下場合下，使用InnoDB是最理想的選擇：

　　1.更新密集的表。InnoDB存儲引擎特別適合處理多重并發(fā)的更新請求。

　　2.事務(wù)。InnoDB存儲引擎是支持事務(wù)的標(biāo)準(zhǔn)MySQL存儲引擎。

　　3.自動災(zāi)難恢復(fù)。與其它存儲引擎不同，InnoDB表能夠自動從災(zāi)難中恢復(fù)。

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

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

　　InnoDB存儲引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引。

　　1)自動增長列：

　　InnoDB表的自動增長列可以手工插入，但是插入的如果是空或0，則實際插入到則是自動增長后到值�？梢酝ㄟ^"ALTERTABLE...AUTO_INCREMENT=n;"語句強制設(shè)置自動增長值的起始值，默認為1，但是該強制到默認值是保存在內(nèi)存中，數(shù)據(jù)庫重啟后該值將會丟失。可以使用LAST_INSERT_ID()查詢當(dāng)前線程最后插入記錄使用的值。如果一次插入多條記錄，那么返回的是第一條記錄使用的自動增長值。

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

　　2)外鍵約束：

　　MySQL支持外鍵的存儲引擎只有InnoDB，在創(chuàng)建外鍵的時候，父表必須有對應(yīng)的索引，子表在創(chuàng)建外鍵的時候也會自動創(chuàng)建對應(yīng)的索引。

　　在創(chuàng)建索引的時候，可以指定在刪除、更新父表時，對子表進行的相應(yīng)操作，包括restrict、cascade、setnull和noaction。其中restrict和noaction相同，是指限制在子表有關(guān)聯(lián)的情況下，父表不能更新；casecade表示父表在更新或刪除時，更新或者刪除子表對應(yīng)的記錄；setnull則表示父表在更新或者刪除的時候，子表對應(yīng)的字段被setnull。

　　當(dāng)某個表被其它表創(chuàng)建了外鍵參照，那么該表對應(yīng)的索引或主鍵被禁止刪除。

　　可以使用setforeign_key_checks=0;臨時關(guān)閉外鍵約束，setforeign_key_checks=1;打開約束。

　　(三)MEMORY

　　memory使用存在內(nèi)存中的內(nèi)容來創(chuàng)建表。每個MEMORY表實際對應(yīng)一個磁盤文件，格式是.frm。MEMORY類型的表訪問非�？�，因為它到數(shù)據(jù)是放在內(nèi)存中的，并且默認使用HASH索引，但是一旦服務(wù)器關(guān)閉，表中的數(shù)據(jù)就會丟失，但表還會繼續(xù)存在。

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

　　存儲在MEMORY數(shù)據(jù)表里的數(shù)據(jù)行使用的是長度不變的格式，因此加快處理速度，這意味著不能使用BLOB和TEXT這樣的長度可變的數(shù)據(jù)類型。VARCHAR是一種長度可變的類型，但因為它在MySQL內(nèi)部當(dāng)作長度固定不變的CHAR類型，所以可以使用。

　　createtabletab_memoryengine=memoryselectid,name,age,addrfrommanorderbyid;

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

　　createindexmem_hashusinghashontab_memory(city_id);

　　在啟動MySQL服務(wù)的時候使用--init-file選項，把insertinto...select或loaddatainfile這樣的語句放入到這個文件中，就可以在服務(wù)啟動時從持久穩(wěn)固的數(shù)據(jù)源中裝載表。

　　服務(wù)器需要足夠的內(nèi)存來維持所在的在同一時間使用的MEMORY表，當(dāng)不再使用MEMORY表時，要釋放MEMORY表所占用的內(nèi)存，應(yīng)該執(zhí)行DELETEFROM或truncatetable或者刪除整個表。

　　每個MEMORY表中放置到數(shù)據(jù)量的大小，受到max_heap_table_size系統(tǒng)變量的約束，這個系統(tǒng)變量的初始值是16M，同時在創(chuàng)建MEMORY表時可以使用MAX_ROWS子句來指定表中的最大行數(shù)。

使用MySQL Memory存儲引擎的出發(fā)點是速度。為得到最快的響應(yīng)時間，采用的邏輯存儲介質(zhì)是系統(tǒng)內(nèi)存。雖然在內(nèi)存中存儲表數(shù)據(jù)確實會提供很高的性能，但當(dāng)mysqld守護進程崩潰時，所有的Memory數(shù)據(jù)都會丟失。獲得速度的同時也帶來了一些缺陷。它要求存儲在Memory數(shù)據(jù)表里的數(shù)據(jù)使用的是長度不變的格式，這意味著不能使用BLOB和TEXT這樣的長度可變的數(shù)據(jù)類型，VARCHAR是一種長度可變的類型，但因為它在MySQL內(nèi)部當(dāng)做長度固定不變的CHAR類型，所以可以使用。

　　(四)MERGE

　　merge存儲引擎是一組MyISAM表的組合，這些MyISAM表結(jié)構(gòu)必須完全相同，MERGE表中并沒有數(shù)據(jù)，對MERGE類型的表可以進行查詢、更新、刪除的操作，這些操作實際上是對內(nèi)部的MyISAM表進行操作。對于對MERGE表進行的插入操作，是根據(jù)INSERT_METHOD子句定義的插入的表，可以有3個不同的值，first和last值使得插入操作被相應(yīng)的作用在第一個或最后一個表上，不定義這個子句或者為NO，表示不能對這個MERGE表進行插入操作�？梢詫�MERGE表進行drop操作，這個操作只是刪除MERGE表的定義，對內(nèi)部的表沒有任何影響。MERGE在磁盤上保留2個以MERGE表名開頭文件：.frm文件存儲表的定義；.MRG文件包含組合表的信息，包括MERGE表由哪些表組成，插入數(shù)據(jù)時的依據(jù)�？梢酝ㄟ^修改.MRG文件來修改MERGE表，但是修改后要通過flushtable刷新。

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

以上就是MySQL存儲引擎的詳細介紹的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！