Mysql_數(shù)據(jù)類型優(yōu)化_2

關(guān)于BLOB、TEXT類型：

????BLOB與TEXT是為了存儲很大的數(shù)據(jù)而設(shè)計(jì)的字符串?dāng)?shù)據(jù)類型，分別采用二進(jìn)制和字符方式存儲，實(shí)際上它們分別屬于兩組不同的數(shù)據(jù)類型家族：

字符類型：TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT

二進(jìn)制類型：TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB。

????至于BLOB與TEXT的不同，BLOB類型存儲的是二進(jìn)制數(shù)據(jù)，沒有排序規(guī)則或字符集。與其他的類型不同，Mysql會將每個(gè)BLOB，TEXT值當(dāng)做一個(gè)獨(dú)立的對象處理，存儲引擎在存儲時(shí)通常進(jìn)行特殊的處理，當(dāng)它們的值太大是，InnoDB會使用專門的外部存儲區(qū)域來進(jìn)行存儲，此時(shí)每個(gè)值在行內(nèi)需要1~4個(gè)字節(jié)存儲一個(gè)指針，然后在外部存儲區(qū)域存儲實(shí)際的值。

????Mysql對BLOB和TEXT進(jìn)行排序與其他類型不同，它只對每個(gè)列的最前max_sort_length字節(jié)排序，而不是整個(gè)字符串，或者使用order by sustring（column,length），這樣可以減少max_sort_length的配置,減少因?yàn)閷φ麄€(gè)字符串做排序帶來無用的資源浪費(fèi)。此外，Mysql不能對BLOB和TEXT列全部長度的字符串進(jìn)行縮引，也不能使用這些索引消除排序。

????

磁盤臨時(shí)表和文件排序

????因Memory引擎不支持BLOB、TEXT，所以查詢使用了BLOB，TEXT的列，且需要使用臨時(shí)隱式表，將不得不使用MyIASM磁盤臨時(shí)表，即使只有幾行數(shù)據(jù)。這會性能開銷很嚴(yán)重，即使配置Mysql將臨時(shí)表存儲在內(nèi)存塊RAM上，仍然需要許多昂貴系統(tǒng)調(diào)用。

????對于此問題，最好的方式是盡量避免使用BLOB和TEXT類型，若無法避免，可以嘗試在所有用到BLOB字段的地方都用substring(column, length)將列值轉(zhuǎn)為字符串(在order by子句中同樣適用)，這樣就可以使用內(nèi)存臨時(shí)表，但是需要確保截取的字符串足夠短，不會使臨時(shí)表的大小超過max_heap_table_size或tmp_tbale_size,超過后Mysql會將內(nèi)存臨時(shí)表轉(zhuǎn)換為MyISAM磁盤臨時(shí)表，最壞情況下的長度分配對于排序操作也是一樣的處理。

????對于上述的概念，有一個(gè)例子可以較為形象的理解一下：假設(shè)有一個(gè)1000w行的表，占用了幾個(gè)GB的磁盤空間，其中有一個(gè)utf8的字符集varchar（1000）列，每個(gè)字符最多使用3個(gè)字節(jié)，在最壞情況下就需要3000自己的空間，在使用order by中用到這個(gè)列，并且查詢掃描整個(gè)表，那么為了排序就要需要超過30GB的臨時(shí)表。