高效能MySql進化論(二):資料類型的最佳化_下

·        BLOB/TEXT
在實際的應用程式中往往需要儲存兩種體積較大的數據，一種是較大的Binary數據，e.g. 一張10M的圖片，另一種是較大的文字e.g.一篇幾萬字的文章。在Oracle中有BOLB和CLOB來應對這兩種數據，而在MySQL中對應的是BLOB以及TEXT.
鑑於這兩種數據類型的特殊性，在MySQL中對BLOB以及TEXT的存儲和操作做了特殊的處理:
          1) BLOB/TEXT 的值往往是作為物件處理，這些物件有自己的ID，以及獨立的儲存空間
        節會被使用，N 對應的是資料庫中的一個常數值(max_sort_length), 如果你想要指定更多的位元組被用來排序，那麼你可以增加max_sort_length的值或是使用ORDER BY SUBSTRING(column, length )函數來處理
          3) 當BLOB/TEXT 被用作索引或排序的時候，不能使用整個字段的值.
在萬不得已的情況下要避免將BOLB/TEXT用作索引或是排序

因為SQLMy 的Memory 引擎不支援BLOB 和TEXT 類型，所以，如果查詢的過程中涉及到BLOB /TEXT,則需要使用MyISAM 磁碟臨時表，即使只有幾行資料也是如此（在最新的Percona Server 的Memory 引擎支援BLOB 和TEXT類型）。

Memory引擎頻繁的存取磁碟臨時表會產生嚴重的效能開銷，最好的解決方案是盡量避免使用BLOB 和TEXT 類型。如果實在無法避免，有一個技巧是在所有用到BLOB 欄位的地方都使用SUBSTRING(column, length) 將列值轉換為字串（在ORDER BY 子句中也適用），這樣就可以使用記憶體臨時表了。但要確保截取的子字串夠短，不會讓臨時表的大小超過max_heap_table_size 或tmp_table_size，超過以後MySQL 會將記憶體暫存表轉換為MyISAM 磁碟暫存表。

 

最壞情況下的長度分配對於排序的時候也是一樣的，所以這一招對於內存中創建大臨時表和文件排序，以及在磁碟上創建大臨時表和文件排序這兩種情況都很有幫助。例如，假設有一個1 000 萬行的表，佔用幾個GB 的磁碟空間。其中有一個utf8字元集的VARCHAR(1000) 欄位。每個字元最多使用3 個位元組，最壞情況下需要3 000位元組的空間。如果在ORDER BY 中用到這個列，並且查詢掃描整個表，為了排序就需要超過30GB 的臨時表

·       DATETIME/TIMESTAMP
在MySQL中包含兩種時間格式DATETIME，TIMESTAMP，通常在使用的過程中這兩種類型差異不是很大，但是在細節上還是存在差異

因為TMESSTAMP會佔用更小的存儲空間，所以可以使用它作為默認的時間格式

·     的欄位主要是透過枚舉的方式來保存列的值，因為在使用的過程中會涉及到枚舉位置與實際值的轉換，所以對於整體的性能可能會有一定的影響，而且枚舉的值是儲存在.frm(資料表結構定義檔)中，所以當建立完ENUM的列後，如果你想對EMUM的內容進行更新，也就相當於做了表結構的更新。
下面是個簡單建立ENUM列的例子：

mysql> CREATE TABLEenum_test(
->  e ENUM('fish', 'apple', 'dog') NOT NULL
-> );
mysql> INSERT INTOenum_test(e) VALUES('fish'), ('dog'), ('apple');

·        BIT

如果需要讓你設計一個表示布林值的欄位要求的佔用空間最少，你會如何去佔用空間佔用空間的數量？用INT,還是用CHAR(1)?相比INT以及CHAR(1)而言BIT(1)或許是個更好的選擇，因為它佔用的空間只是一個BIT。它可以透過BIT(N)的方式來表達多個BIT的值，這種方式最大支持到BIT(64)。

在MySQL5.0之前的版本中，BIT被認為是和TINYINT等同的，在新的版本中被當作兩種完全不同的類型來對待。

當你把一個BIT欄位從資料庫中檢索出來顯示在控制台上時，值會被顯示成ASCII編碼，當欄位的值出在一個數字運算的上下文時，它會被當成是BIT的十進制的值，下面的一個例子可以很清楚的說明這兩種情況

mysql>CREATE TABLE bittest(a bit(8));
mysql> INSERT INTObittest VALUES(b'00111001');
mysql> SELECT a, a+ 0 FROM bittest;
+------+-------+
| a | a + 0 |
+------+-------+
| 9 | 57 |
+------+-------+

上面的這個例子或許會讓你感到困惑，很有可能讓你不再想使用這種機制來存儲單個的位，作為一種替代方案可以把相關欄位設為CHAR(0)，NULL用來表示False,””(Empty String)表示True

以上就是 高效能MySql進化論(二):資料型別的最佳化_下的內容，更多相關內容請關注PHP中文網（www.php.cn）！