選擇合適的表的類型,防止數據文件中產生碎塊,同樣可以大大優化檢索的速度,是數據庫的性能最大化。
選擇一種表類型
用MySQL,當前(版本 3 .23.5)你能從一個速度觀點在4可用表的格式之間選擇。
·靜態MyISAM
這種格式是最簡單且最安全的格式,它也是在磁盤格式最快的。速度來自於數據能在磁盤上被找到的難易方式。當所定有一個索引和靜態格式的東西時,它很簡單,只是行長度乘以行數量。而且在掃描一張表時,用每次磁盤讀取來讀入常數個記錄是很容易的。安全性來自於如果當寫入一個靜態MyISAM文件時,你的計算機崩潰,myisamchk能很容易指出每行在哪兒開始和結束,因此它通常能回收所有記錄,除了部分被寫入的那個。注意,在MySQL中,所有索引總能被重建。
·動態MyISAM
這種格式有點復雜,因為每一行必須有一個頭說明它有多長。當一個記錄在更改時變長時,它也可以在多於一個位置上結束。你能使用OPTIMIZE table或myisamchk整理一張表。如果你在同一個表中有象某些VARCHAR或BLOB列那樣存取/改變的靜態數據,將動態列移入另外一個表以避免碎片可能是一個好主意。
·壓縮MyISAM
這是一個只讀類型,用可選的myisampack工具生成。
·內存(HEAP 堆)
這種表格式對小型/中型查找表十分有用。對拷貝/創建一個常用的查找表(用聯結)到一個(也許臨時)HEAP表有可能加快多個表聯結。假定我們想要做下列聯結,用同樣數據可能要幾倍時間。
SELECT tab1.a, tab3.a FROM tab1, tab2, tab3 WHERE tab1.a = tab2.a and tab2.a = tab3.a and tab2.c != 0;
為了加速它,我們可用tab2和tab3的聯結創建一張臨時表,因為用相同列( tab1.a )查找。這裡是創建該表和結果選擇的命令。
CREATE TEMPORARY TABLE test TYPE=HEAP SELECT tab2.a as a2, tab3.a as a3 FROM tab2, tab3 WHERE tab2.a = tab3.a and c = 0; SELECT tab1.a, test.a3 from tab1, test where tab1.a = test.a1; SELECT tab1.b, test.a3 from tab1, test where tab1.a = test.a1 and something;
靜態(定長)表的特點
這是缺省格式。它用在表不包含VARCHAR、BLOB或TEXT列時候。
所有的CHAR、NUMERIC和DECIMAL列充填到列寬度。
非常快。
容易緩沖。
容易在崩潰後重建,因為記錄位於固定的位置。
不必被重新組織(用myisamchk),除非一個巨量的記錄被刪除並且你想要歸還空閒磁盤空間給操作系統。
通常比動態表需要更多的磁盤空間。
動態表的特點
如果表包含任何VARCHAR、BLOB或TEXT列,使用該格式。
所有字符串列是動態的(除了那些長度不到4的列)。
每個記錄前置一個位圖,對字符串列指出哪個列是空的(''),或對數字列哪個是零(這不同於包含NULL值的列)。如果字符串列在刪除尾部空白後有零長度,或數字列有零值,它在位圖中標記並且不保存到磁盤上。非空字符串存儲為一個長度字節加字符串內容。
通常比定長表占更多的磁盤空間。
每個記錄僅使用所需的空間。如果一個記錄變得更大,它按需要被切開多段,這導致記錄碎片。
如果你與超過行長度的信息更新行,行將被分段。在這種情況中,你可能必須時時運行myisamchk -r以使性能更好。使用myisamchk -ei tbl_name做一些統計。
在崩潰後不容易重建,因為一個記錄可以是分很多段並且一個連接(碎片)可以丟失。
對動態尺寸記錄的期望行長度是:
3 + (number of columns + 7) / 8 + (number of char columns) + packed size of numeric columns + length of strings + (number of NULL columns + 7) / 8
對每個連接有6個字節的懲罰。無論何時更改引起記錄的增大,一個動態記錄被鏈接。每個新鏈接將至少是20個字節,因此下一增大將可能在同一鏈連中。如果不是,將有另外一個鏈接。你可以用myisamchk -ed檢查有多少鏈接。所有的鏈接可以用 myisamchk -r 刪除。
壓縮表的特點
一張用myisampack實用程序制作的只讀表。所有具有MySQL擴展電子郵件支持的客戶可以為其內部使用保留一個myisampack拷貝。
解壓縮代碼存在於所有MySQL分發,以便甚至沒有myisampack的客戶能讀取用myisampack壓縮的表。
占據很小的磁盤空間,使磁盤使用量減到最小。
每個記錄被單獨壓縮(很小的存取開銷)。對一個記錄的頭是定長的(1-3 字節),取決於表中最大的記錄。每列以不同方式被壓縮。一些壓縮類型是:
通常對每列有一張不同的哈夫曼表。
後綴空白壓縮。
前綴空白壓縮。
用值0的數字使用1位存儲。
如果整數列的值有一個小范圍,列使用最小的可能類型來存儲。例如,如果所有的值在0到255的范圍,一個BIGINT列(8個字節)可以作為一個TINYINT列(1字節)存儲。
如果列僅有可能值的一個小集合,列類型被變換到ENUM。
列可以使用上面的壓縮方法的組合。
能處理定長或動態長度的記錄,然而不能處理BLOB或TEXT列。
能用myisamchk解壓縮。
MySQL能支持不同的索引類型,但是一般的類型是ISAM。這是一個B樹索引並且你能粗略地為索引文件計算大小為(key_length+4)*0.67,在所有的鍵上的總和。這是對最壞情況,當所有鍵以排序順序被插入時。)
字符串索引是空白壓縮的。如果第一個索引部分是一個字符串,它也將壓縮前綴。如果字符串列有很多尾部空白或是一個總不能用到全長的VARCHAR列,空白壓縮使索引文件更小。如果很多字符串有相同的前綴,前綴壓縮是有幫助的。