MySQL詳解（2）----------儲存引擎，mysql----------

MySQL詳解（2）----------儲存引擎，mysql----------

儲存引擎是什嗎？

MySQL中的資料用各種不同的技術儲存在檔案(或者記憶體)中。這些技術中的每一種技術都使用不同的儲存機制、索引技巧、鎖定水平並且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，你能夠獲得額外的速度或者功能，從而改善你的應用的整體功能。

例如，如果你在研究大量的臨時資料，你也許需要使用記憶體儲存引擎。記憶體儲存引擎能夠在記憶體中儲存所有的表格資料。又或者，你也許需要一個支援交易處理的資料庫(以確保交易處理不成功時資料的回退能力)。

這 些不同的技術以及配套的相關功能在MySQL中被稱作儲存引擎(也稱作表類型)。MySQL預設配置了許多不同的儲存引擎，可以預先設定或者在MySQL伺服器中啟用。你可以選擇適用於伺服器、資料庫和表格的儲存引擎，以便在選擇如何儲存你的資訊、如何檢索這些資訊以及你需要你的資料結合什麼效能和功能的時候為你提供最大的靈活性。

選擇如何儲存和檢索你的資料的這種靈活性是MySQL為什麼如此受歡迎的主要原因。其它資料庫系統(包括大多數商業選擇)僅支援一種類型的資料存放區。遺憾的是，其它類型的資料庫解決方案採取的“一個尺碼滿足一切需求”的方式意味著你要麼就犧牲一些效能，要麼你就用幾個小時甚至幾天的時間詳細調整你的資料庫。使用MySQL，我們僅需要修改我們使用的儲存引擎就可以了。

在這篇文章中，我們不準備集 中討論不同的儲存引擎的技術方面的問題(儘管我們不可避免地要研究這些因素的某些方面)，相反，我們將集中介紹這些不同的引擎分別最適應哪種需求和如何啟用不同的儲存引擎。為了實現這個目的，在介紹每一個儲存引擎的具體情況之前，我們必須要瞭解一些基本的問題。

MySQL支援許多不同的儲存引擎，有時候也稱作表格類型。資料庫每個表可以有不同的儲存引擎，而且可以輕鬆地對它們進行轉換。
Create　TABLE　tablenameTYPE=typename

 

MySQL5.5以後預設使用InnoDB儲存引擎，其中InnoDB和BDB提供事務安全表，其它儲存引擎都是非事務安全表。
若要修改預設引擎，可以修改設定檔中的default-storage-engine。可以通過：

show variables like 'default_storage_engine';

查看當前資料庫到預設引擎。

命令：show engines和show variables like 'have%'

可以列出當前資料庫所支援到引擎。

其中Value顯示為disabled的記錄表示資料庫支援此引擎，而在資料庫啟動時被禁用。

在MySQL5.1以後，INFORMATION_SCHEMA資料庫中存在一個ENGINES的表，它提供的資訊與show engines;語句完全一樣，可以使用下面語句來查詢哪些儲存引擎支援事物處理：

select engine from information_chema.engines where transactions = 'yes';

可以通過engine關鍵字在建立或修改資料庫時指定所使用到引擎。
主要儲存引擎：MyISAM、InnoDB、MEMORY和MERGE介紹：
在建立表到時候通過engine=...或type=...來指定所要使用到引擎。

show table status from DBname來查看指定表到引擎。

(一)MyISAM
　　它不支援事務，也不支援外鍵，尤其是訪問速度快，對事務完整性沒有要求或者以SELECT、INSERT為主的應用基本都可以使用這個引擎來建立表。
每個MyISAM在磁碟上儲存成3個檔案，其中檔案名稱和表名都相同，但是副檔名分別為：

• .frm(儲存表定義)
• MYD(MYData，儲存資料)
• MYI(MYIndex，儲存索引)

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

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

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

• 靜態(固定長度)表
• 動態表
• 壓縮表

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

　　動態表包含變長欄位，記錄不是固定長度的，這樣儲存的優點是佔用空間較少，但是頻繁到更新刪除記錄會產生片段，需要定期執行OPTIMIZE TABLE語句或myisamchk -r命令來改善效能，並且出現故障的時候恢複相對比較困難。
壓縮表由myisamchk工具建立，佔據非常小的空間，因為每條記錄都是被單獨壓縮的，所以只有非常小的訪問開支。

 

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

1)自動成長列：
　　InnoDB表的自動成長列可以手工插入，但是插入的如果是空或0，則實際插入到則是自動成長後到值。可以通過"ALTER TABLE...AUTO_INCREMENT=n;"語句強制設定自動成長值的起始值，預設為1，但是該強制到預設值是儲存在記憶體中，資料庫重啟後該值將會丟失。可以使用LAST_INSERT_ID()查詢當前線程最後插入記錄使用的值。如果一次插入多條記錄，那麼返回的是第一條記錄使用的自動成長值。
對於InnoDB表，自動成長列必須是索引。如果是複合式索引，也必須是複合式索引的第一列，但是對於MyISAM表，自動成長列可以是複合式索引的其他列，這樣插入記錄後，自動成長列是按照複合式索引到前面幾列排序後遞增的。

2)外鍵約束：
　　MySQL支援外鍵的儲存引擎只有InnoDB，在建立外鍵的時候，父表必須有對應的索引，子表在建立外鍵的時候也會自動建立對應的索引。
      在建立索引的時候，可以指定在刪除、更新父表時，對子表進行的相應操作，包括restrict、cascade、set null和no action。其中restrict和no action相同，是指限制在子表有關聯的情況下，父表不能更新；casecade表示父表在更新或刪除時，更新或者刪除子表對應的記錄；set null 則表示父表在更新或者刪除的時候，子表對應的欄位被set null。
　　當某個表被其它表建立了外鍵參照，那麼該表對應的索引或主鍵被禁止刪除。
　　可以使用set foreign_key_checks=0;臨時關閉外鍵約束，set foreign_key_checks=1;開啟約束。

(三)MEMORY
　　memory使用存在記憶體中的內容來建立表。每個MEMORY表實際對應一個磁碟檔案，格式是.frm。MEMORY類型的表訪問非常快，因為它到資料是放在記憶體中的，並且預設使用HASH索引，但是一旦伺服器關閉，表中的資料就會丟失，但表還會繼續存在。
      預設情況下，memory資料表使用散列索引，利用這種索引進行“相等比較”非常快，但是對“範圍比較”的速度就慢多了。因此，散列索引值適合使用在"="和"<=>"的操作符中，不適合使用在"<"或">"操作符中，也同樣不適合用在order by字句裡。如果確實要使用"<"或">"或betwen操作符，可以使用btree索引來加快速度。
　　儲存在MEMORY資料表裡的資料行使用的是長度不變的格式，因此加快處理速度，這意味著不能使用BLOB和TEXT這樣的長度可變的資料類型。VARCHAR是一種長度可變的類型，但因為它在MySQL內部當作長度固定不變的CHAR類型，所以可以使用。

 

create table tab_memory engine=memoryselect id,name,age,addrfrom man orderby id;

　　

 

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

 

create index mem_hashusing hash on tab_memory(city_id);

　

　在啟動MySQL服務的時候使用--init-file選項，把insert into...select或load data infile 這樣的語句放入到這個檔案中，就可以在服務啟動時從持久穩固的資料來源中裝載表。
　　伺服器需要足夠的記憶體來維持所在的在同一時間使用的MEMORY表，當不再使用MEMORY表時，要釋放MEMORY表所佔用的記憶體，應該執行DELETE FROM或truncate table或者刪除整個表。
　　每個MEMORY表中放置到資料量的大小，受到max_heap_table_size系統變數的約束，這個系統變數的初始值是16M，同時在建立MEMORY表時可以使用MAX_ROWS子句來指定表中的最大行數。

(四)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表，但是修改後要通過flush table重新整理。

 

createtable man_all(id int,namevarchar(20))engine=mergeunion=(man1,man2) insert_methos=last;

 

 

（五）Archive引擎

Archive儲存引擎只支援INSERT和SELECT操作，在MySQL5.1之前不支援索引。

Archive表適合日誌和資料擷取類應用。

Archive引擎支援行級鎖和專用的緩衝區，所以可以實現高並發的插入，但它不是一個事物型的引擎，而是一個針對高速插入和壓縮做了最佳化的簡單引擎。

 

（六）Blackhole引擎

Blackhole引擎沒有實現任何儲存機制，它會丟棄所有插入的資料，不做任何儲存。但伺服器會記錄Blackhole表的日誌，所以可以用於複製資料到備庫，或者簡單地記錄到日誌。但這種應用方式會碰到很多問題，因此並不推薦。

 

（七）CSV引擎

CSV引擎可以將普通的SCV檔案作為MySQL的表來處理，但不支援索引。

CSV引擎可以作為一種資料交換的機制，非常有用。

 

（八）Federated引擎

Federated引擎是訪問其他MySQL伺服器的一個代理，儘管該引擎看起來提供了一種很好的跨伺服器的靈活性，但也經常帶來問題，因此預設是禁用的。

 

 

 

二、選擇合適的引擎

大部分情況下，InnoDB都是正確的選擇，可以簡單地歸納為一句話“除非需要用到某些InnoDB不具備的特性，並且沒有其他辦法可以替代，否則都應該優先選擇InnoDB引擎”。

除非萬不得已，否則建議不要混合使用多種儲存引擎，否則可能帶來一系列負責的問題，以及一些潛在的bug和邊界問題。

如果應用需要不同的儲存引擎，請先考慮以下幾個因素：

事務：

    如果應用需要事務支援，那麼InnoDB(或者XtraDB)是目前最穩定並且經過驗證的選擇。

備份：

    如果可以定期地關閉伺服器來執行備份，那麼備份的因素可以忽略。反之，如果需要線上熱備份，那麼選擇InnoDB就是基本的要求。

崩潰恢複

    MyISAM崩潰後發生損壞的機率比InnoDB要高很多，而且恢複速度也要慢。

特有的特性

    如果一個儲存引擎擁有一些關鍵的特性，同時卻又缺乏一些必要的特性，那麼有時候不得不做折中的考慮，或者在架構設計上做一些取捨。

 

有些查詢SQL在不同的引擎上表現不同。比較典型的是：

SELECT COUNT(*) FROM table;

對於MyISAM確實會很快，但其他的可能都不行。

 

三、應用舉例

 

1、日誌型應用

MyISAM或者Archive儲存引擎對這類應用比較合適，因為他們開銷低，而且插入速度非常快。

如果需要對記錄的日誌做分析報表，產生報表的SQL很可能會導致插入效率明顯降低，這時候該怎麼辦？

一種解決方案，是利用MySQL內建的複製方案將資料複製一份到備庫，然後在備庫上執行比較消耗時間和CPU的查詢。當然也可以在系統負載較低的時候執行報表查詢操作，但應用在不斷變化，如果依賴這個策略可能以後會導致問題。

另一種方法，在記錄資料表的名字中包含年和月的資訊，這樣可以在已經沒有插入操作的曆史表上做頻繁的查詢操作，而不會干擾到最新的當前表上的插入操作。

 

2、唯讀或者大部分情況下唯讀表

有些表的資料用於編製類目或者分列清單（如工作崗位），這種應用情境是典型的讀多寫少的業務。如果不介意MyISAM的崩潰恢複問題，選用MyISAM引擎是合適的。（MyISAM只將資料寫到記憶體中，然後等待作業系統定期將資料刷出到磁碟上）

 

3、訂單處理

涉及訂單處理，支援事務是必要的，InnoDB是訂單處理類應用的最佳選擇。

 

4、大資料量

如果資料增長到10TB以上的層級，可能需要建立資料倉儲。Infobright是MySQL資料倉儲最成功的方案。也有一些大資料庫不適合Infobright，卻可能適合TokuDB。