高性能MySql進(jìn)化論(二):數(shù)據(jù)類型的優(yōu)化_下

·????????blob/text
在實(shí)際的應(yīng)用程序中往往需要存儲(chǔ)兩種體積較大的數(shù)據(jù)，一種是較大的binary數(shù)據(jù)，e.g. 一張10m的圖片，另外一種是 較大的文本 e.g.一篇幾萬字的文章。在oracle中有bolb和clob來應(yīng)對(duì)這兩種數(shù)據(jù)，而在mysql中對(duì)應(yīng)的是blob以及text.
鑒于這兩種數(shù)據(jù)類型的特殊性，在mysql中對(duì)blob以及text的存儲(chǔ)和操作做了特殊的處理:
????????? 1) blob/text 的值往往是作為對(duì)象來處理，這些對(duì)象有自己的id，以及獨(dú)立的存儲(chǔ)空間
????????? 2) blob/text的值被用來排序的時(shí)候，只有前n個(gè)字節(jié)會(huì)被使用，n 對(duì)應(yīng)的是數(shù)據(jù)庫中的一個(gè)常量值 (max_sort_length), 如果你想指定更多的字節(jié)被用來排序，那么你可以增加max_sort_length的值或者是使用order by substring(column, length)函數(shù)來處理
????????? 3) 當(dāng)blob/text 被用作索引或者排序的時(shí)候，不能使用整個(gè)字段的值.
在萬不得已的情況下要避免把bolb/text用作索引或是排序

因?yàn)閙ysql 的memory 引擎不支持blob 和text 類型，所以，如果查詢的過程中涉及到blob /text,則需要使用myisam 磁盤臨時(shí)表，即使只有幾行數(shù)據(jù)也是如此（在最新的percona server 的memory 引擎支持blob 和text 類型）。

Memory引擎頻繁的訪問磁盤臨時(shí)表會(huì)產(chǎn)生嚴(yán)重的性能開銷，最好的解決方案是盡量避免使用BLOB 和TEXT 類型。如果實(shí)在無法避免，有一個(gè)技巧是在所有用到BLOB 字段的地方都使用SUBSTRING(column, length) 將列值轉(zhuǎn)換為字符串（在ORDER BY 子句中也適用），這樣就可以使用內(nèi)存臨時(shí)表了。但是要確保截取的子字符串足夠短，不會(huì)使臨時(shí)表的大小超過max_heap_table_size 或tmp_table_size，超過以后MySQL 會(huì)將內(nèi)存臨時(shí)表轉(zhuǎn)換為MyISAM 磁盤臨時(shí)表。

?

最壞情況下的長(zhǎng)度分配對(duì)于排序的時(shí)候也是一樣的，所以這一招對(duì)于內(nèi)存中創(chuàng)建大臨時(shí)表和文件排序，以及在磁盤上創(chuàng)建大臨時(shí)表和文件排序這兩種情況都很有幫助。例如，假設(shè)有一個(gè)1 000 萬行的表，占用幾個(gè)GB 的磁盤空間。其中有一個(gè)utf8字符集的VARCHAR(1000) 列。每個(gè)字符最多使用3 個(gè)字節(jié)，最壞情況下需要3 000字節(jié)的空間。如果在ORDER BY 中用到這個(gè)列，并且查詢掃描整個(gè)表，為了排序就需要超過30GB 的臨時(shí)表

·?????? DATETIME/TIMESTAMP
在MySQL中包含兩種時(shí)間格式 DATETIME，TIMESTAMP， 通常在使用的過程中這兩種類型區(qū)別不是很大，但是在細(xì)節(jié)上還是存在差別

因?yàn)門MESSTAMP會(huì)占用更小的存儲(chǔ)空間，所以可以使用它作為默認(rèn)的時(shí)間格式

·?????? ENUM
這種類型的字段主要是通過枚舉的方式來保存列的值，因?yàn)樵谑褂玫倪^程中會(huì)涉及到枚舉位置與實(shí)際值的轉(zhuǎn)換，所以對(duì)于整體的性能可能會(huì)有一定的影響，而且枚舉的值是存儲(chǔ)在.frm(數(shù)據(jù)表結(jié)構(gòu)定義文件)中，所以當(dāng)建立完ENUM的列后，如果你想對(duì)EMUM的內(nèi)容進(jìn)行更新，也就相當(dāng)于做了表結(jié)構(gòu)的更新。
下面是個(gè)簡(jiǎn)單建立ENUM列的例子：

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

·????????BIT
如果需要讓你設(shè)計(jì)一個(gè)表示布爾值的字段要求占用的空間最少，你會(huì)如何去設(shè)計(jì)？用INT,還是用CHAR(1)?相比INT以及CHAR(1)而言BIT(1)或許是個(gè)更好的選擇，因?yàn)樗加玫目臻g只是一個(gè)BIT。它可以通過BIT(N)的方式來表達(dá)多個(gè)BIT的值，這種方式最大支持到BIT(64)。

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

當(dāng)你把一個(gè)BIT字段從數(shù)據(jù)庫中檢索出來顯示在控制臺(tái)上時(shí)，值會(huì)被顯示成ASCII編碼，當(dāng)字段的值出在一個(gè)數(shù)字運(yùn)算的上下文時(shí)，它會(huì)被當(dāng)成是BIT的十進(jìn)制的值，下面的一個(gè)例子可以很清楚的說明這兩種情況

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?|  +------+-------+

上面的這個(gè)例子或許會(huì)讓你感到困惑，很有可能讓你不再想使用這種機(jī)制來存儲(chǔ)單個(gè)的位，作為一種替代方案可以把相關(guān)字段設(shè)置成CHAR(0)，NULL用來表示False,””(Empty String)表示True

以上就是?高性能MySql進(jìn)化論(二):數(shù)據(jù)類型的優(yōu)化_下的內(nèi)容，更多相關(guān)內(nèi)容請(qǐng)關(guān)注PHP中文網(wǎng)（www.php.cn）！