MySQL支持的數(shù)據(jù)類型很多，選擇正確的數(shù)據(jù)類型對于高性能至關(guān)重要。下面幾個簡單的原則都有助于做出更好的選擇。

應(yīng)該盡量使用可以正確儲存數(shù)據(jù)的最小數(shù)據(jù)類型。更小的數(shù)據(jù)類型通常更快，因為它們占用更少的磁盤、內(nèi)存和CPU緩存，并且處理時需要的CPU周期也更少。如果無法確定哪個數(shù)據(jù)類型時最好的，就選擇你認(rèn)為不會超過范圍的最小類型。

簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期。例如，整形比字符操作代價更低，因為字符集和校對規(guī)則(排序規(guī)則)使字符比較比整形更復(fù)雜。比如用MySQ內(nèi)建的類型而不是使用字符串來存儲日期和時間、應(yīng)該用整形存儲IP地址。

如果查詢中包含可為null的列，對MySQL來說更難優(yōu)化，因為可為null的列使得索引、索引統(tǒng)計和值比較都較為復(fù)雜。可為null的列會使用更多的存儲空間，在MySQL里也需要特殊處理。當(dāng)可為null當(dāng)列被索引時，每個索引記錄需要一個額外的字節(jié)，在MyISAM里甚至還可能導(dǎo)致固定大小的索引變成可變大小的索引。

通常把可為null的列改為not null帶來的性能提升比較小，所以沒有必要在現(xiàn)有的架構(gòu)中查找并修改這種情況，除非確定這回導(dǎo)致問題。

例外的是在InnoDB使用獨立的位存儲null值，所以對于稀疏數(shù)據(jù)(很多值為null，只有少數(shù)行的列有非null值)有很好的空間效率，但這一點不適用于MyISAM。

在為列選擇數(shù)據(jù)類型時。

第一步需要確定合適的大類型：數(shù)字、字符串、時間等第二步是選擇具體類型。MySQL的很多數(shù)據(jù)類型都可以存儲相同類型的數(shù)據(jù)，只是存儲的長度和范圍不一樣、允許的精度不一樣，或者需要的物理空間不同。

整數(shù)類型

存儲整數(shù)可以用這幾種類型

類型 存儲位數(shù) tinyint 8 smallint 16 mediumint 24 int 32 bigint 64

它們可以存儲的值的范圍-2^(N-1)^到2^(N-1)^-1，其中N是存儲空間的位數(shù)。整數(shù)類型還有可選有符號屬性，表示不允許負(fù)值，這可以使正數(shù)的上限提高一倍。如tinyint無符號可以存儲的范圍是0到255，但他允許負(fù)值但存儲范圍是-128到127。

MySQL選擇不同的整數(shù)類型是決定怎么在內(nèi)存和磁盤中保存數(shù)據(jù)的。但是在整數(shù)計算一般使用64位但bigint整數(shù)，即使32位環(huán)境也是一樣。(除了聚合函數(shù))

MySQL還可以為整數(shù)類型指定寬度。如int(11),但是它不會限制值的合法范圍，只是規(guī)定了MySQL的一些交互工具(如SQLyog，navicat)用來顯示字符的個數(shù)。對于存儲和計算來說，int(1)和int(11)是相同的。

實數(shù)類型

實數(shù)是帶有小數(shù)部分的類型。MySQL中可以用decimal、float和double來存儲實數(shù)類型。

float和double類型支持使用標(biāo)準(zhǔn)的浮點運算進(jìn)行近似計算。decimal類型用于存儲精確的小數(shù)，在MySQL5.0以及更高的版本中，MySQL服務(wù)器自身實現(xiàn)了decimal的高精度計算。

浮點類型在存儲同樣范圍的值時，通常比decimal使用更少的空間。float使用4個字節(jié)存儲，double使用8個字節(jié)存儲，相比flaot有更高的精度和更大的范圍。在浮點計算時，MySQL都使用的double作為內(nèi)部浮點計算的類型。

在考慮對小數(shù)進(jìn)行精確計算時，使用decimal(如財務(wù)數(shù)據(jù))，但在數(shù)據(jù)量大時，可以考慮使用bigint代替decimal，將需要存儲但貨幣單位根據(jù)小數(shù)的位數(shù)乘以相同的倍數(shù)存儲。這樣可以避免decimal精確計算代價高的問題。

float和double使用的是cpu支持的原生浮點計算，所以快。decimal是MySQL服務(wù)器自身實現(xiàn)的高精度計算。

字符串類型

varchar和char是兩種最主要的字符串類型。

varchar

varchar類型用于存儲可變長字符串，是最常見的字符串?dāng)?shù)據(jù)類型。它比定長類型更節(jié)省空間，因為它僅使用必要的空間。

varchar需要使用1個或2個額外字節(jié)記錄字符串長度：如果列的最大長度小于或等于255字節(jié)，則只使用1個字節(jié)表示，否則使用2個字節(jié)。

varchar節(jié)省了存儲空間，所以對性能也有幫助。但是，由于行是變長的，在update時可能使行變得比原來更長，這就導(dǎo)致需要做額外的工作。因為行占用的空間增長，并且在頁內(nèi)沒有更多的空間可以存儲。這種情況下，MyISAM會將行拆成不同的片段存儲，InnoDB需要分裂頁來使行可以放進(jìn)頁內(nèi)。其他一些存儲引擎也許不在原數(shù)據(jù)位置更新數(shù)據(jù)。

使用varchar最合適的場景是：在字符串列的最大長度比平均長度大很多；列更新很少(碎片不是問題)；使用了像utf-8這樣復(fù)雜的字符集，每個字符都使用不同的字節(jié)數(shù)進(jìn)行存儲。

使用varchar(5)和varchar(200)存儲‘hello’的空間開銷是一樣的，但是使用更長的列會消耗更多的內(nèi)存，MySQL通常會分配固定大小的內(nèi)存快來保存內(nèi)部值。尤其是使用內(nèi)存臨時表進(jìn)行排序或操作時會特別糟糕，在利用磁盤臨時表進(jìn)行排序同樣糟糕。所以最好的策略是只分配真正需要的空間。

char

char類型是定長的。MySQL總是根據(jù)定義的字符串長度分配足夠的空間。當(dāng)存儲char值時，MySQL會刪除所有的末尾空格。char值會根據(jù)需要采用空格進(jìn)行填充以方便比較。

char適合存儲很短的字符串，或者所有值都接近同一個長度。例如密碼的MD5值。對于經(jīng)常變更的數(shù)據(jù)，char也比varchar更好，因為定長的char不容易產(chǎn)生碎片。對于非常短的列，char比varchar在存儲空間上也更有效率，因為varchar需要額外字節(jié)記錄長度。

以上就是MySQL數(shù)據(jù)類型優(yōu)化原則的詳細(xì)內(nèi)容，更多關(guān)于MySQL數(shù)據(jù)類型優(yōu)化的資料請關(guān)注好吧啦網(wǎng)其它相關(guān)文章！