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

應(yīng)該盡量使用可以正確儲(chǔ)存數(shù)據(jù)的最小數(shù)據(jù)類(lèi)型。更小的數(shù)據(jù)類(lèi)型通常更快，因?yàn)樗鼈冋加酶俚拇疟P(pán)、內(nèi)存和CPU緩存，并且處理時(shí)需要的CPU周期也更少。如果無(wú)法確定哪個(gè)數(shù)據(jù)類(lèi)型時(shí)最好的，就選擇你認(rèn)為不會(huì)超過(guò)范圍的最小類(lèi)型。

簡(jiǎn)單數(shù)據(jù)類(lèi)型的操作通常需要更少的CPU周期。例如，整形比字符操作代價(jià)更低，因?yàn)樽址托?duì)規(guī)則(排序規(guī)則)使字符比較比整形更復(fù)雜。比如用MySQ內(nèi)建的類(lèi)型而不是使用字符串來(lái)存儲(chǔ)日期和時(shí)間、應(yīng)該用整形存儲(chǔ)IP地址。

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

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

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

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

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

整數(shù)類(lèi)型

存儲(chǔ)整數(shù)可以用這幾種類(lèi)型

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

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

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

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

實(shí)數(shù)類(lèi)型

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

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

浮點(diǎn)類(lèi)型在存儲(chǔ)同樣范圍的值時(shí)，通常比decimal使用更少的空間。float使用4個(gè)字節(jié)存儲(chǔ)，double使用8個(gè)字節(jié)存儲(chǔ)，相比f(wàn)laot有更高的精度和更大的范圍。在浮點(diǎn)計(jì)算時(shí)，MySQL都使用的double作為內(nèi)部浮點(diǎn)計(jì)算的類(lèi)型。

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

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

字符串類(lèi)型

varchar和char是兩種最主要的字符串類(lèi)型。

varchar

varchar類(lèi)型用于存儲(chǔ)可變長(zhǎng)字符串，是最常見(jiàn)的字符串?dāng)?shù)據(jù)類(lèi)型。它比定長(zhǎng)類(lèi)型更節(jié)省空間，因?yàn)樗鼉H使用必要的空間。

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

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

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

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

char

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

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

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