選擇優(yōu)化的數(shù)據(jù)類型

• 1.更小的通常更好
  ?一般情況下，應(yīng)該盡量使用可以正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類型。更小的數(shù)據(jù)類型通常更快，因?yàn)樗鼈冋加酶〉拇疟P、內(nèi)存和CPU緩存，并且處理時(shí)需要的CPU周期也更少。
  但是要確保沒有低估需要存儲(chǔ)的值的范圍

• 2.簡單就好
  ?簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期。例子：a.應(yīng)該用msql內(nèi)建的類型而不是字符串來存儲(chǔ)日期和時(shí)間；b.應(yīng)該用整形來存儲(chǔ)ip地址。

• 3.盡量避免NULL
  ? 很多表都包含可為NULL(空值)的列，即使應(yīng)用程序并不需要保存NULL也是如此，這是因?yàn)榭蔀镹ULL是列的默認(rèn)屬性。通常情況下最好指定列為NOT NULL,除非真的需要存儲(chǔ)NULL值。
  ?原因：如果查詢中包含可為NULL的列，對mysql來說更難優(yōu)化，因?yàn)榭蔀镹ULL的列使得索引、索引統(tǒng)計(jì)和值比較都更復(fù)雜?？蔀镹ULL的列會(huì)使用更多的存儲(chǔ)空間，在Mysql里也需要特殊處理。當(dāng)可為NULL的列被索引事，每個(gè)索引記錄需要一個(gè)額外的字節(jié)。

5.1.1 整數(shù)類型

整數(shù)
? 可以使用這幾種整數(shù)類型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGNIT。分別使用8,16,24,32,64位存儲(chǔ)空間?？梢源鎯?chǔ)的范圍從-2^(n-1) 到 2^(n-1)，n是存儲(chǔ)空間位數(shù)。

? 整數(shù)類型有可選的UNSIGNED屬性，表示不允許負(fù)值。這大致可以使正數(shù)的上限提高一倍。例如TINITY UNSIGNED 可以存儲(chǔ)的范圍是0 ~ 255，而TINITY的存儲(chǔ)范圍是 -128 ~ 127.

? Mysql可以為整數(shù)類型指定寬度，例如INT(11)，對大多數(shù)應(yīng)用這是沒有意義的；它不會(huì)限制值的合法范圍，只是規(guī)定了mysql的一些交互工具（例如mysql命令行客戶端）用來顯示字符的個(gè)數(shù)。對于存儲(chǔ)和計(jì)算來說。INT(11)和INT(20)是相同的

5.1.2 實(shí)數(shù)類型

實(shí)數(shù)
? FLOAT和DOUBLE類型使用標(biāo)準(zhǔn)的浮點(diǎn)運(yùn)算進(jìn)行近似運(yùn)算。
? DECIMAL類型用于存儲(chǔ)精確的小數(shù)，并支持精確計(jì)算。
?因?yàn)镃PU不支持對DECIMAL的直接計(jì)算，mysql服務(wù)器自身實(shí)現(xiàn)了DECIMAL的高精度計(jì)算。相對而言，CPU直接支持原生浮點(diǎn)計(jì)算，所以浮點(diǎn)計(jì)算明顯更快。
?浮點(diǎn)類型在存儲(chǔ)同樣的范圍的值時(shí)，通常比DEMICAL使用更少的空間。FLOAT使用4個(gè)字節(jié)存儲(chǔ)。DOUBLE占用8個(gè)字節(jié)，相比FLOAT有更高的精度和更大的范圍。和整數(shù)類型一樣，能選擇的只是存儲(chǔ)類型，Mysql使用DOUBLE作為內(nèi)部浮點(diǎn)計(jì)算的類型。
?因?yàn)樾枰~外的空間和計(jì)算開銷，所以應(yīng)該盡量只在對小數(shù)進(jìn)行精確計(jì)算時(shí)才使用DECIMAL--例如存儲(chǔ)財(cái)政數(shù)據(jù)。但在數(shù)據(jù)量比較大的時(shí)候，可以考慮使用BIGNIT代替DECIMAL,將需要存儲(chǔ)的貨幣單位根據(jù)小數(shù)的位數(shù)乘以相應(yīng)的倍數(shù)即可。這樣可以避免浮點(diǎn)存儲(chǔ)計(jì)算不精確和DECIMAL**精確計(jì)算代價(jià)高的問題。

5.1.3 字符串型

VARCHAR：
?VARCHAR和CHAR是兩中最主要的字符串類型。
?VARCHAR類型用于存儲(chǔ)可變長字符串，是最常用的字符串?dāng)?shù)據(jù)類型。它比定長類型更節(jié)省空間，因?yàn)樗鼉H使用必要的空間（例如，越短的字符串使用越少的空間）。在一種情況例外，如果Mysql表使用ROW_FORMAT=FIXED創(chuàng)建的話，每一行都會(huì)使用定長存儲(chǔ)，這會(huì)很浪費(fèi)空間。
?VARVHAR需要使用1或2個(gè)額外字節(jié)記錄字符串的長度；如果列的最大長度小于或等于255字節(jié)，則只使用1個(gè)字節(jié)表示，否則使用2個(gè)字節(jié)。一個(gè)VARCHAR(10)的列需要11個(gè)字節(jié)的存儲(chǔ)空間。VARCHAR(1000)的列需要1002個(gè)字節(jié)，因?yàn)樾枰?個(gè)字節(jié)存儲(chǔ)長度信息。
?VARCHAR節(jié)省了存儲(chǔ)空間，所以對性能也有幫助。但是，由于行是變長的，在UPDATE時(shí)可能使行變得比原來更長，這就導(dǎo)致需要做額外的工作。如果一個(gè)行占用的空間增長，并且在頁內(nèi)沒有更多的空間可以存儲(chǔ)，在這種情況下，InnoDB需要分裂頁來使行可以放進(jìn)頁內(nèi)。
?使用場景:字符串列的最大長度比平均長度大很多；列的更新很少，所以碎片不是問題；使用了像UTF-8這樣復(fù)雜的字符集，每個(gè)字符都使用不同的字節(jié)數(shù)進(jìn)行存儲(chǔ)。

CHAR：
?CHAR類型是定長的:Mysql總是根據(jù)定義的字符串長度分配足夠的空間。當(dāng)存儲(chǔ)CHAR值時(shí)，Mysql會(huì)刪除所有的末尾空格。CHAR值會(huì)根據(jù)需要采用空格進(jìn)行填充以方便比較。
?使用場景:CHAR適合存儲(chǔ)很短的字符串，或者所有值都接近用一個(gè)長度。例如，CHAR非常適合存儲(chǔ)密碼的MD5值，因?yàn)檫@是一個(gè)定長的值。對于經(jīng)常變更的數(shù)據(jù)，CHAR也比VARCHAR更好，因?yàn)槎ㄩL的CHAR類型不容易產(chǎn)生碎片。對于非常短的列，CHAR也比VARCHAR在存儲(chǔ)空間上也更有效率。例如用CHAR(1)來存儲(chǔ)只有Y和N的值，如果采用單字節(jié)字符集只需要一個(gè)字節(jié)，但是VARCHAE(1)卻需要兩個(gè)字節(jié)，因?yàn)檫€有一個(gè)記錄長度的額外字節(jié)。

image.png

5.1.4 文本類型

?BLOB和TEXT都是為存儲(chǔ)很大的數(shù)據(jù)而設(shè)計(jì)的字符串?dāng)?shù)據(jù)類型，分別采用二進(jìn)制和字符方式存儲(chǔ)。

?實(shí)際上，它們分別屬于兩組不同的數(shù)據(jù)類型家族:字符類型是TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT；對應(yīng)的二進(jìn)制類型是TINYBLOB,SMALLBLOB,BLOB*
,MEDIUMBLOB,LONGBLOB.

5.1.5 日期和時(shí)間類型

DATETIME
?這個(gè)類型能保存大范圍的值，從1001年到9999年，精度為秒。它把日期和時(shí)間封裝到格式為YYYYMMDDHHMMSS的整數(shù)中，與時(shí)區(qū)無關(guān)。使用8個(gè)字節(jié)的存儲(chǔ)空間。
TIMESTAMP
?TIMETAMP類型保存了從1970年1月1日午夜（格林尼治標(biāo)準(zhǔn)時(shí)間）以來的秒數(shù)，它和UNIX時(shí)間戳相同。TIMESTAMP只使用4個(gè)字節(jié)的存儲(chǔ)空間，因此它的范圍比DATETINE小得多；只能表示從1970年到2038年。

image.png

Mysql schema設(shè)計(jì)中的陷阱

• 1.太多的列
  ?Mysql的存儲(chǔ)引擎API工作時(shí)需要在服務(wù)器層和存儲(chǔ)引擎之間通過緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個(gè)列。從行緩沖中將編碼過的列轉(zhuǎn)換成行數(shù)據(jù)結(jié)構(gòu)的操作代價(jià)是非常高的。
• 2.太多的關(guān)聯(lián)
  ?所謂的“實(shí)體-屬性-值（EVA）”設(shè)計(jì)模式是一個(gè)常見的槽糕設(shè)計(jì)模式，尤其是在Mysql下不能靠譜地工作。一個(gè)粗略的經(jīng)驗(yàn)法則，如果希望查詢執(zhí)行得快速且并發(fā)性好，單個(gè)查詢最好在12個(gè)表以內(nèi)做關(guān)聯(lián)。
  *3.變相的枚舉
  ?枚舉(enum)列允許在列中存儲(chǔ)一組定義值中的單個(gè)值，集合（SET）列則允許在列中存儲(chǔ)一組定義值中的一個(gè)或多個(gè)值。有時(shí)候這可能比較容易導(dǎo)致混亂。這是一個(gè)例子:

   CREATE TABLE ...(
      is_default set('Y','N') NOT NULL default 'N'
   )

?如果這里真和假兩種情況不會(huì)同時(shí)出現(xiàn)，那么毫無疑問應(yīng)該可以使用枚舉列代替集合列。
*4.非此發(fā)明（Not Inent Here）的NULL
?應(yīng)該避免使用NULL。即使需要存儲(chǔ)一個(gè)事實(shí)上的“空值”到表中時(shí)，也不一定非得使用NULL。也許可以使用0、某個(gè)特殊值，或者空字符串作為代替。

總結(jié)

• 盡量避免過度設(shè)計(jì)，例如會(huì)導(dǎo)致極其復(fù)雜查詢的schema設(shè)計(jì)，或者有很多列的表設(shè)計(jì)；
• 使用小而簡單的合適數(shù)據(jù)類型，除非真實(shí)數(shù)據(jù)模型中有確切的需要，否則應(yīng)該盡可能地避免使用NULL值;
• 盡量使用相同的數(shù)據(jù)類型存儲(chǔ)相似或相關(guān)的值，尤其是要在關(guān)聯(lián)條件中使用的列。
• 注意可變字符串，其在臨時(shí)表和排序時(shí)可能導(dǎo)致悲觀的按最大長度分配內(nèi)存。
• 盡量使用整型定義標(biāo)識列。
• 避免使用Mysql已經(jīng)遺棄的特性，例如指定浮點(diǎn)數(shù)的精度，或者整型的顯示寬度。
• 小心使用ENUM和SET。雖然它們用起來很方便，但是不要濫用，否則有時(shí)候會(huì)變成陷阱。最好避免使用BIT。

范式是好的，但是反范式（大多情況下意味著重復(fù)數(shù)據(jù)）有時(shí)也是必需的，并且能帶來好處。

ALTER TABLE是讓人痛苦的操作，大部分情況下會(huì)鎖表并且會(huì)重建整張表。我們可以在備機(jī)執(zhí)行ALTER并在完成后把它切換為主庫。