????????良好的邏輯設(shè)計(jì)和物理設(shè)計(jì)是高性能的基石拭嫁， 應(yīng)該根據(jù)系統(tǒng)將要執(zhí)行的查詢語(yǔ)句來(lái)設(shè)計(jì)schema, 這往往需要權(quán)衡各種因素从媚。 例如烹植， 反范式的設(shè)計(jì)可以加快某些類型的查詢， 但同時(shí)可能使另一些類型的查詢變慢刻帚。比如添加計(jì)數(shù)表和匯總表是一種很好的優(yōu)化查詢的方式，但這些表的維護(hù)成本可能會(huì)很高涩嚣。 MySQL獨(dú)有的特性和實(shí)現(xiàn)細(xì)節(jié)對(duì)性能的影響也很大崇众。

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

????????MySQL支持的數(shù)據(jù)類型非常多掂僵， 選擇正確的數(shù)據(jù)類型對(duì)于獲得高性能至關(guān)重要。 不管存儲(chǔ)哪種類型的數(shù)據(jù)顷歌， 下面幾個(gè)簡(jiǎn)單的原則都有助于做出更好的選擇锰蓬。

更小的通常更好

? ??????一般情況下，應(yīng)該盡最使用可以正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類型眯漩。更小的數(shù)據(jù)類型通常更快芹扭， 因?yàn)樗鼈冋加酶俚拇疟P、 內(nèi)存和CPU緩存坤塞， 并且處理時(shí)需要的CPU周期也更少冯勉。

????????但是要確保沒(méi)有低估需要存儲(chǔ)的值的范圍， 因?yàn)樵趕chema中的多個(gè)地方增加數(shù)據(jù)類型的范圍是一個(gè)非常耗時(shí)和痛苦的操作摹芙。 如果無(wú)法確 ? ?定哪個(gè)數(shù)據(jù)類型是最好的灼狰， 就選擇你認(rèn)為不會(huì)超過(guò)范圍的最小類型。（如果系統(tǒng)不是很忙或者存儲(chǔ)的數(shù)據(jù)量不多浮禾， 或者是在可以輕易修改設(shè)計(jì)的早期階段交胚， 那之后修改數(shù)據(jù)類型也比較容易）。

簡(jiǎn)單就好

????????簡(jiǎn)單數(shù)據(jù)類型的操作通常需要更少的CPU周期盈电。 例如蝴簇， 整型比字符操作代價(jià)更低， 因?yàn)樽址托?duì)規(guī)則（排序規(guī)則 ）使字符比較比整型比較更復(fù)雜匆帚。 這里有兩個(gè)例子：一個(gè)是應(yīng)該 使用MySQL內(nèi)建的類型注 2 而不是字符串來(lái)存儲(chǔ)日期和時(shí)間熬词， 另外一個(gè)是應(yīng)該用整型存儲(chǔ)IP地址。 稍后我們將專門討論這個(gè)話題吸重。

盡量避免NULL

????????很多表都包含可為NULL (空值）的列互拾， 即使應(yīng)用程序并不需要保存NULL 也是如此， 這是因?yàn)榭蔀镹ULL 是列的默認(rèn)屬性嚎幸。 通常情況下最好指定列為NOT NULL, 除非真的需要存儲(chǔ)NULL 值颜矿。

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

????????通常把可為NULL 的列改為NOT NULL 帶來(lái)的性能提升比較小， 所以（調(diào)優(yōu)時(shí)）沒(méi)有 必要首先在現(xiàn)有schema中查找井修改掉這種情況衣陶，除非確定這會(huì)導(dǎo)致問(wèn)題柄瑰。 但是闸氮， 如果計(jì)劃在列上建索引， 就應(yīng)該盡量避免設(shè)計(jì)成可為 NULL 的列教沾。

????????當(dāng)然也有例外蒲跨， 例如值得一提的是， lnnoDB 使用單獨(dú)的位 (bit) 存儲(chǔ)NULL 值授翻， 所以對(duì)于稀疏數(shù)據(jù) 有很好的空間效率或悲。 但這一點(diǎn)不適用于MyISAM。

????????在為列選擇數(shù)據(jù)類型時(shí)堪唐， 第一步需要確定合適的大類型：數(shù)字巡语、 字符串、 時(shí)間等淮菠。 這通常是很簡(jiǎn)單的男公， 但是我們會(huì)提到一些特殊的不是那么直觀的案例。

????????下一步是選擇具體類型合陵。很多MySQL的數(shù)據(jù)類型可以存儲(chǔ)相同類型的數(shù)據(jù)枢赔， 只是存儲(chǔ)的長(zhǎng)度和范圍不一樣、允許的精度不同拥知， 或者需要的物理空間（磁盤和內(nèi)存空間）不同踏拜。相同大類型的不同子類型數(shù)據(jù)有時(shí)也有一些特殊的行為和屬性。

4.1.1整數(shù)類型

????????有兩種類型的數(shù)字：整數(shù)(whole number) 和實(shí)數(shù)(real number)低剔。 如果存儲(chǔ)整數(shù)速梗， 可以使用這幾種整數(shù)類型：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。 分別使用 8, 16, 24, 32, 64 位存儲(chǔ)空間襟齿。 它們可以存儲(chǔ)的值的范圍從-2^(N-1)到2^（N-1）-1,其中N是存儲(chǔ)空間的位數(shù)姻锁。

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

????????有符號(hào)和無(wú)符號(hào)類型使用相同的存儲(chǔ)空間替梨，并具有相同的性能， 因此可以根據(jù)實(shí)際情況選擇合適的類型装黑。

????????你的選擇決定 MySQL 是怎么在內(nèi)存和磁盤中保存數(shù)據(jù)的副瀑。 然而， 整數(shù)計(jì)算一般使用64 位的 BIGINT 整數(shù)恋谭， 即使在 32 位環(huán)境也是如此糠睡。（ 一些聚合函數(shù)是例外， 它們使用DECIMAL 或 DOUBLE 進(jìn)行計(jì)算）疚颊。

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

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

????????實(shí)數(shù)是帶有小數(shù)部分的數(shù)字。 然而油挥， 它們不只是為了存儲(chǔ)小數(shù)部分潦蝇，也可以使用DECIMAL 存儲(chǔ)比 BIGINT 還大的整數(shù)。 MySQL 既支持精確類型深寥，也支持不精確類型攘乒。

????????FLOAT和DOUBLE類型支持使用標(biāo)準(zhǔn)的浮點(diǎn)運(yùn)算進(jìn)行近似計(jì)算。 如果需要知道浮點(diǎn)運(yùn)算是怎么計(jì)算的惋鹅， 則需要研究所使用的平臺(tái)的浮點(diǎn)數(shù)的具體實(shí)現(xiàn)则酝。

????????DECIMAL類型用于存儲(chǔ)精確的小數(shù)。 在MySQL5.0和更高版本闰集，DECIMAL類型支持精確 計(jì)算堤魁。MySQL4.1以及更早版本則使用浮點(diǎn)運(yùn)算來(lái)實(shí)現(xiàn)DECIAML的計(jì)算， 這樣做會(huì)因?yàn)榫葥p失導(dǎo)致一些奇怪的結(jié)果返十。在這些版本的MySQL中妥泉，DECIMAL只是一個(gè)“存儲(chǔ)類型”。

????????因?yàn)镃PU不支持對(duì)DECIMAL的直接計(jì)算洞坑，所以在MySQL5.0以及更高版本中盲链，MySQL 服務(wù)器自身實(shí)現(xiàn)了DECIMAL的高精度計(jì)算。 相對(duì)而言迟杂，CPU直接支持原生浮點(diǎn)計(jì)算刽沾， 所 以浮點(diǎn)運(yùn)算明顯更快。

????????浮點(diǎn)和DECIMAL類型都可以指定精度排拷。 對(duì)于DECIMAL列侧漓， 可以指定小數(shù)點(diǎn)前后所允許的 最大位數(shù)。這會(huì)影響列的空間消耗监氢。MySQL5.0和更高版本將數(shù)字打包保存到一個(gè)二進(jìn)制字符串中 （每4個(gè)字節(jié)存9個(gè)數(shù)字）布蔗。 例如，DECIMAL(18,9)小數(shù)點(diǎn)兩邊將各存儲(chǔ)9個(gè)數(shù)字浪腐， 一共使用9個(gè)字節(jié)：小數(shù)點(diǎn)前的數(shù)字用4個(gè)字節(jié)纵揍， 小數(shù)點(diǎn)后的數(shù)字用4個(gè)字節(jié)， 小數(shù)點(diǎn)本身占1個(gè)字節(jié)议街。

????????MySQL 5.0和更高版本中的DECIMAL類型允許最多65個(gè)數(shù)字泽谨。 而早期的MySQL版本中這個(gè)限制是254個(gè)數(shù)字，并且保存為未壓縮的字符串（每個(gè)數(shù)字一個(gè)字節(jié)）。然而吧雹，這些（早期） 版本實(shí)際上并不能在計(jì)算中使用這么大的數(shù)字骨杂， 因?yàn)镈ECIMAL只是一種存儲(chǔ)格式；在計(jì)算中DECIMAL會(huì)轉(zhuǎn)換為DOUBLE類型雄卷。

????????有多種方法可以指定浮點(diǎn)列所需要的精度腊脱， 這會(huì)使得MySQL悄悄選擇不同的數(shù)據(jù)類型，或者在存儲(chǔ)時(shí)對(duì)值進(jìn)行取舍龙亲。 這些精度定義是非標(biāo)準(zhǔn)的陕凹，所以我們建議只指定數(shù)據(jù)類型，不指定精度鳄炉。

????????浮點(diǎn)類型在存儲(chǔ)同樣范圍的值時(shí)杜耙， 通常比DECIMAL使用更少的空間。FLOAT使用4個(gè)字節(jié)存儲(chǔ)拂盯。DOUBLE占用8個(gè)字節(jié)佑女，相比FLOAT有更高的精度和更大的范圍。和整數(shù)類型一樣谈竿， 能選擇的只是存儲(chǔ)類型团驱； MySQL使用DOUBLE作為內(nèi)部浮點(diǎn)計(jì)算的類型。

????????因?yàn)樾枰~外的空間和計(jì)算開(kāi)銷空凸，所以應(yīng)該盡量只在對(duì)小數(shù)進(jìn)行精確計(jì)算時(shí)才使用DECIMAL—例如存儲(chǔ)財(cái)務(wù)數(shù)據(jù)嚎花。 但在數(shù)據(jù)最比較大的時(shí)候， 可以考慮使用BIGINT代替DECIMAL, 將需要存儲(chǔ)的貨幣單位根據(jù)小數(shù)的位數(shù)乘以 相應(yīng)的倍數(shù)即可呀洲。 假設(shè)要存儲(chǔ)財(cái)務(wù)數(shù)據(jù)精確到萬(wàn)分之一分紊选，則可以把所有金額乘以一百萬(wàn)，然后 將結(jié)果存儲(chǔ)在BIGINT里道逗， 這樣可以同時(shí)避免浮點(diǎn)存儲(chǔ)計(jì)算不精確和DECIMAL精確計(jì)算代價(jià)高的問(wèn)題兵罢。

4.1.3字符串類型

????????MySQL支持多種字符串類型， 每種類型還有很多變種滓窍。 這些數(shù)據(jù)類型在4.1和5.0版本發(fā)生了很大的變化卖词，使得情況 更加復(fù)雜。從MySQL4.1開(kāi)始吏夯，每個(gè)字符串列可以定義自己的字符集和排序規(guī)則此蜈，或者說(shuō)校對(duì)規(guī)則(collation)(更多關(guān)于這個(gè)主題的信息請(qǐng)參考第7章）。 這些東西會(huì)很大程度上影響性能锦亦。

VARCHAR和CHAR類型

????????VARCHAR和CHAR是兩種最主要的字符串類型舶替。 不幸的是令境，很難精確地解釋這些值是怎么存儲(chǔ)在磁盤和內(nèi)存中的杠园，因?yàn)檫@跟存儲(chǔ)引擎的具體實(shí)現(xiàn)有關(guān)。下面的描述假設(shè)使用的存儲(chǔ)引擎是 InnoDB和／或者M(jìn)yISAM舔庶。如果使用的不是這兩種存儲(chǔ)引擎抛蚁，請(qǐng)參考所使用的存儲(chǔ)引擎的文檔陈醒。

????????先看看VARCHAR和CHAR值通常在磁盤上怎么存儲(chǔ)。請(qǐng)注意瞧甩，存儲(chǔ)引擎存儲(chǔ)CHAR或者VARCHAR值的方式在內(nèi)存中和在磁盤上可能不一樣钉跷，所以MySQL服務(wù)器從存儲(chǔ)引擎讀出的值可能需要轉(zhuǎn)換為另一種存儲(chǔ)格式。下面是關(guān)于兩種類型的一些比較肚逸。

VARCHAR

????????VARCHAR類型用于存儲(chǔ)可變長(zhǎng)字符串爷辙，是最常見(jiàn)的字符串?dāng)?shù)據(jù)類型。它比定長(zhǎng)類型更節(jié)省空間朦促，因?yàn)樗鼉H使用必要的空間（例如膝晾， 越短的字符串使用越少的空間）。有一種情況例外务冕，如果MySQL 表使用ROW_FORMAT =FIXED創(chuàng)建的話血当，每一行都會(huì)使用定長(zhǎng)存儲(chǔ)，這會(huì)很浪費(fèi)空間禀忆。

????????VARCHAR需要使用1 或2個(gè)額外字節(jié)記錄字符串的長(zhǎng)度：如果列的最大長(zhǎng)度小于或等于255 字節(jié)臊旭，則只使用1個(gè)字節(jié)表示，否則使用2個(gè)字節(jié)箩退。假設(shè)采用latinl字符集离熏，一個(gè)VARCHAR(10)的列需要11個(gè)字節(jié)的存儲(chǔ)空間。VARCHAR(1000)的列則需要1002個(gè)字節(jié)戴涝，因?yàn)樾枰?個(gè)字節(jié)存儲(chǔ)長(zhǎng)度信息撤奸。

????????VARCHAR節(jié)省了存儲(chǔ)空間，所以對(duì)性能也有幫助喊括。但是胧瓜，由于行是變長(zhǎng)的，在UPDATE時(shí)可能使行變得比原來(lái)更長(zhǎng)郑什，這就導(dǎo)致需要做額外的工作府喳。如果一個(gè)行占用的空間增長(zhǎng)，并且在頁(yè)內(nèi)沒(méi)有更多的空間可以存儲(chǔ)蘑拯，在這種情況下钝满，不同的存儲(chǔ)引擎的處理方式是不一樣的。例如申窘，MyISAM會(huì)將行拆成不同的片段存儲(chǔ)弯蚜，InnoDB則需要分裂頁(yè)來(lái)使行可以放進(jìn)頁(yè)內(nèi)。其他一些存儲(chǔ)引擎也許從不在原數(shù)據(jù)位置更新數(shù)據(jù)剃法。

????????下面這些情況下使用VARCHAR是合適的： 字符串列的最大長(zhǎng)度比平均長(zhǎng)度大很多碎捺；列的更新很少，所以碎片不是問(wèn)題，使用了像UTF-8這樣復(fù)雜的字符集收厨，每個(gè)字符都使用不同的字節(jié)數(shù)進(jìn)行存儲(chǔ)晋柱。在5.0 或者更高版本，MySQL 在存儲(chǔ)和檢索時(shí)會(huì)保留末尾空格诵叁。但在4.1 或更老的版本雁竞，MySQL會(huì)剔除末尾空格。

CHAR

? ??????CHAR類型是定長(zhǎng)的： MySQL總是根據(jù)定義的字符串長(zhǎng)度分配足夠的空間拧额。當(dāng)存儲(chǔ)CHAR值時(shí)碑诉，MySQL會(huì)刪除所有的末尾空格（在 MySQL4.1 和更老版本中VARCHAR也是這樣實(shí)現(xiàn)的——也就是說(shuō)這些版本中CHAR和VARCHAR在邏輯上是一樣的，區(qū)別只是在存儲(chǔ)格式上）侥锦。CHAR值會(huì)根據(jù) 需要采用空格進(jìn)行填充以方便比較联贩。 CHAR適合存儲(chǔ)很短的字符串，或者所有值都接近同一個(gè)長(zhǎng)度捎拯。例如泪幌，CHAR非常適合存儲(chǔ)密碼的MD5值，因?yàn)檫@是一個(gè)定長(zhǎng)的值署照。對(duì)于經(jīng)常變更的數(shù)據(jù)祸泪，CHAR也比VARCHAR 更好，因?yàn)槎ㄩL(zhǎng)的 CHAR類型不容易產(chǎn)生碎片建芙。對(duì)于非常短的列没隘，CHAR比 VARCHAR在存儲(chǔ)空間上也更有效率。例如用 CHAR(1)來(lái)存儲(chǔ)只有Y和N的值禁荸，如果采用單字節(jié)字符集只需要一個(gè)字節(jié)右蒲，但是VARCHAR(1)卻需要兩個(gè)字節(jié)，因?yàn)檫€有 一個(gè)記錄長(zhǎng)度的額外字節(jié)赶熟。

????????數(shù)據(jù) 如何存儲(chǔ)取決于存儲(chǔ)引擎奴烙，并非所有的存儲(chǔ)引擎都會(huì)按照相同的方式處理定長(zhǎng)和 變長(zhǎng)的字符串杯巨。Memory引擎只支持定長(zhǎng)的行尉剩，即使有變長(zhǎng)字段也會(huì)根據(jù)最大長(zhǎng)度分配最大空間面粮。不過(guò)， 填充和截取空格的行為在不同存儲(chǔ)引擎都是一樣的邑退， 因?yàn)檫@是在 MySQL 服務(wù)器層進(jìn)行處理的竹宋。

????????與 CHAR 和 VARCHAR 類似的類型還有 BINARY 和 VARBINARY, 它們存儲(chǔ)的是二進(jìn)制字符串。 二進(jìn)制字符串跟常規(guī)字符串非常相似地技，但是二進(jìn)制字符串存儲(chǔ)的是字節(jié)碼而不是字符蜈七。 填充也不一樣：MySQL 填充 BINARY采用的是 \0 (零字節(jié)）而不是空格， 在檢索時(shí)也不會(huì)去掉填充值

????????當(dāng)需要存儲(chǔ)二進(jìn)制數(shù)據(jù)莫矗， 井且希望 MySQL 使用字節(jié)碼而不是字符進(jìn)行比較時(shí)飒硅， 這些類型是非常有用的砂缩。 二進(jìn)制比較的優(yōu)勢(shì)井不僅僅體現(xiàn)在大小寫(xiě)敏感上。 MySQL 比較BINARY字符串時(shí)狡相， 每次按一個(gè)字節(jié)梯轻， 并且根據(jù)該字節(jié)的數(shù)值進(jìn)行比較食磕。 因此尽棕， 二進(jìn)制比 較比字符比較簡(jiǎn)單很多， 所以也就更快彬伦。

????????使用 VARCHAR(5) 和 VARCHAR(200) 存儲(chǔ)'hello'的空間開(kāi)銷是一樣的滔悉。 那么使用更 短的列有什么優(yōu)勢(shì)嗎？

????????事實(shí)證明有很大的優(yōu)勢(shì)单绑。 更長(zhǎng)的列會(huì)消耗更多的內(nèi)存回官， 因?yàn)?MySQL 通常會(huì)分配固定大小的內(nèi)存塊來(lái)保存內(nèi)部值。 尤其是使用內(nèi)存臨時(shí)表進(jìn)行排序或操作時(shí)會(huì)特別糟糕搂橙。 在利用磁盤臨時(shí)表進(jìn)行排序時(shí)也同樣糟糕歉提。

????????所以最好的策略是只分配真正需要的空間。

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

????????MySQL可以使用許多類型來(lái)保存日期和時(shí)間值区转，例如YEAR和DATE苔巨。MySQL能存儲(chǔ)的最小時(shí)間粒度為秒(MariaDB支持微秒級(jí)別的時(shí)間類型）。 但是MySQL 也可以使用微秒級(jí)的粒度進(jìn)行臨時(shí)運(yùn)算废离， 我們會(huì)展示怎么繞開(kāi)這種存儲(chǔ)限制侄泽。

? ??????大部分時(shí)間類型 都沒(méi)有替代品， 因此沒(méi)有什么是最佳選擇的問(wèn)題蜻韭。 唯一 的問(wèn)題是保存日期和時(shí)間的時(shí)候需要做什么悼尾。 MySQL提供兩種相似的日期類型： DATE TIME和 TIMESTAMP。 對(duì)于很多應(yīng)用程序肖方，它們都能工作闺魏， 但是在某些場(chǎng)景， 一個(gè)比另一個(gè)工作得好俯画。 讓我們來(lái)看一下舷胜。

DATETIME

????????這個(gè)類型能保存大范圍的值， 從1001年到9999年活翩， 精度為秒烹骨。 它把日期和時(shí)間封裝到格式為YYYY MMDDHHMMSS的整數(shù)中，與時(shí)區(qū)無(wú)關(guān)材泄。使用8 個(gè)字節(jié)的存儲(chǔ)空間沮焕。

? ? ? ??默認(rèn)情況下，MySQL 以一種可排序的拉宗、 無(wú)歧義的格式顯示 DATETIME 值峦树，例如"2008-01-16 22:37:08"辣辫。這是ANSI標(biāo)準(zhǔn)定義的日期和時(shí)間表示方法。

TIMESTAMP

????????就像它的名字一樣魁巩，TIMETAMP類型保存了從1970年1月1日午夜（格林尼治標(biāo)準(zhǔn) 時(shí)間） 以來(lái)的 秒數(shù)急灭，它和UNIX時(shí)間戳相同。TIMESTAMP只使用4個(gè)字節(jié)的存儲(chǔ)空間谷遂，因此 它的范圍比DATETIME 小得多：只能表示從1970年到2038年葬馋。MySQL提供了 FROM_ UNIXTIME ()函數(shù)把Unix時(shí)間戳轉(zhuǎn)換為日期，井提供了UNIX_TIMESTAMP()函數(shù)把日期轉(zhuǎn)換為Unix時(shí)間戳肾扰。

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

????????雖然有一些普遍的好或壞的設(shè)計(jì)原則畴嘶， 但也有一些問(wèn)題是由MySQL的實(shí)現(xiàn)機(jī)制導(dǎo)致的，這意味著有可能犯一些只在MySQL下發(fā)生的特定錯(cuò)誤集晚。本節(jié)我們討論設(shè)計(jì)MySQL的schema的問(wèn)題窗悯。這也許會(huì)幫助你避免這些錯(cuò)誤， 井且選擇在MySQL特定實(shí)現(xiàn)下工作得更好的替代方案偷拔。

太多的列

? ??????MySQL的存儲(chǔ)引擎API工作時(shí)需要在服務(wù)器層和存儲(chǔ)引擎層之間通過(guò)行緩沖格式拷貝數(shù)據(jù)蒋院， 然后在服務(wù)器層將緩沖內(nèi)容解碼成各個(gè)列。從行緩沖中將編碼過(guò)的列轉(zhuǎn)換成行數(shù)據(jù)結(jié)構(gòu)的操作代價(jià)是非常高的莲绰。MylSAM的定長(zhǎng)行結(jié)構(gòu)實(shí)際上與服務(wù)器層的行結(jié)構(gòu)正好匹配欺旧， 所以不需要轉(zhuǎn)換。然而钉蒲，MyISAM的變長(zhǎng)行結(jié)構(gòu)和InnoDB的行結(jié)構(gòu)則總是需要轉(zhuǎn)換切端。轉(zhuǎn)換的代價(jià)依賴千列的數(shù)量。當(dāng)我們研究一個(gè)CPU占用非常高的案例時(shí)顷啼， 發(fā)現(xiàn)客戶使用了非常寬的表（數(shù)千個(gè)字段）踏枣， 然而只有一小部分列會(huì)實(shí)際用到， 這時(shí)轉(zhuǎn)換的代價(jià)就非常高钙蒙。如果計(jì)劃使用數(shù)千個(gè)字段茵瀑， 必須意識(shí)到服務(wù)器的性能運(yùn)行特征會(huì)有一些不同。

太多的關(guān)聯(lián)

????????所謂的“實(shí)體－屬性－值"(EAV)設(shè)計(jì)模式是一個(gè)常見(jiàn)的糟糕設(shè)計(jì)模式躬厌， 尤其是在MySQL下不能靠譜地工作马昨。MySQL限制了每個(gè)關(guān)聯(lián)操作最多只能有61張表，但是EAV數(shù)據(jù)庫(kù)需要許多自關(guān)聯(lián)扛施。我們見(jiàn)過(guò)不少EAV數(shù)據(jù)庫(kù)最后超過(guò)了這個(gè)限制鸿捧。事實(shí)上在許多關(guān)聯(lián)少千61張表的情況下， 解析和優(yōu)化查詢的代價(jià)也會(huì)成為MySQL的問(wèn)題疙渣。一個(gè)粗略的經(jīng)驗(yàn)法則匙奴， 如果希望查詢執(zhí)行得快速且井發(fā)性好， 單個(gè)查詢最好在12個(gè)表以內(nèi)做關(guān)聯(lián)妄荔。

全能的枚舉

????????注意防止過(guò)度使用枚舉(ENUM)泼菌。用枚舉值類型也許在任何支持枚舉類型的數(shù)據(jù)庫(kù)都是一個(gè)有問(wèn)題的設(shè)計(jì)方案谍肤， 這里應(yīng)該用整數(shù)作為外鍵關(guān)聯(lián)到字典表或者查找表來(lái)查找具體值。但是在MySQL中哗伯， 當(dāng)需要在枚舉列表中增加一個(gè)新的國(guó)家時(shí)就要做一次ALTER TABLE 操作荒揣。在MySQL5.0以及更早的版本中ALTER TABLE 是一種阻塞操作1 即使在5.1和更新版本中， 如果不是在列表的末尾增加值也會(huì)一樣需要ALTER TABLE (我們將展示一些駭客式的方法來(lái)避免阻塞操作焊刹，但是這只是駭客的玩法系任， 別輕易用在生產(chǎn)環(huán)境中）。

變相的枚舉

????????枚舉(ENUM)列允許在列中存儲(chǔ)一組定義值中的單個(gè)值伴澄， 集合(SE T)列則允許在列中存儲(chǔ)一組定義值中的一個(gè)或多個(gè)值赋除。有時(shí)候這可能比較容易導(dǎo)致混亂.

非此發(fā)明(Not Invent Here)的NULL

????????我們之前寫(xiě)了避免使用NULL 的好處阱缓，井且建議盡可能地考慮替代方案非凌。即使需要存儲(chǔ)一個(gè)事實(shí)上的“空值” 到表中時(shí)， 也不一定非得使用NULL荆针。也許可以使用0敞嗡、某個(gè)特殊值， 或者空字符串作為代替航背。但是遵循這個(gè)原則也不要走極端喉悴。當(dāng)確實(shí)需要表示未知值時(shí)也不要害怕使用NULL。在一些場(chǎng)景中玖媚， 使用NULL 可能會(huì)比某個(gè)神奇常數(shù)更好箕肃。從特定類型的值域中選擇一個(gè)不可能的值， 例如用-1代表一個(gè)未知的整數(shù)今魔， 可能導(dǎo)致代碼復(fù)雜很多勺像，井容易引入bug, 還可能會(huì)讓事情變得一團(tuán)糟。處理NULL確實(shí)不容易错森，但有時(shí)候會(huì)比它的替代方案更好吟宦。

4.4緩存表和匯總表

????????有時(shí)提升性能最好的方法是在同一張表中保存衍生的冗余數(shù)據(jù)。然而涩维，有時(shí)也需要?jiǎng)?chuàng)建一張完全獨(dú)立的匯總表或緩存表（特別是為滿足檢索的需求時(shí)）殃姓。如果能容許少最的臟數(shù)據(jù)，這是非常好的方法瓦阐，但是有時(shí)確實(shí)沒(méi)有選擇的余地（例如蜗侈，需要避免復(fù)雜、昂貴的實(shí)時(shí)更新操作）睡蟋。

????????術(shù)語(yǔ)“緩存表” 和“匯總表“ 沒(méi)有標(biāo)準(zhǔn)的含義踏幻。我們用術(shù)語(yǔ)“緩存表” 來(lái)表示存儲(chǔ)那些可以比較簡(jiǎn)單地從schema其他表獲取（但是每次獲取的速度比較慢） 數(shù)據(jù)的表（例如薄湿， 邏輯上冗余的數(shù)據(jù)）叫倍。而術(shù)語(yǔ)“匯總表”時(shí)偷卧，則保存的是使用GROUP BY語(yǔ)句聚合數(shù)據(jù)的表（例如，數(shù)據(jù)不是邏輯上冗余的）吆倦。也有人使用術(shù)語(yǔ)“ 累積表(Roll-Up Table)"稱呼這些表听诸。因?yàn)檫@些數(shù)據(jù)被“ 累積” 了。

????????仍然以網(wǎng)站為例蚕泽， 假設(shè)需要計(jì)算之前24小時(shí)內(nèi)發(fā)送的消息數(shù)晌梨。在一個(gè)很繁忙的網(wǎng)站不可能維護(hù)一個(gè)實(shí)時(shí)精確的計(jì)數(shù)器。作為替代方案须妻，可以每小時(shí)生成一張匯總表仔蝌。這樣也許一條簡(jiǎn)單的查詢就可以做到，井且比實(shí)時(shí)維護(hù)計(jì)數(shù)器要高效得多荒吏。缺點(diǎn)是計(jì)數(shù)器并不是100%精確敛惊。

????????如果必須獲得過(guò)去24小時(shí)準(zhǔn)確的消息發(fā)送數(shù)量（沒(méi)有遺漏），有另外一種選擇绰更。以每小時(shí)匯總表為基礎(chǔ)瞧挤，把前23個(gè)完整的小時(shí)的統(tǒng)計(jì)表中的計(jì)數(shù)全部加起來(lái)， 最后再加上開(kāi)始階段和結(jié)束階段不完整的小時(shí)內(nèi)的計(jì)數(shù)儡湾。

????????不管是哪種方法——不嚴(yán)格的計(jì)數(shù)或通過(guò)小范圍查詢填滿間隙的嚴(yán)格計(jì)數(shù)——都比計(jì)算 message 表的所有行要有效得多特恬。這是建立匯總表的最關(guān)鍵原因。實(shí)時(shí)計(jì)算統(tǒng)計(jì)值是很昂貴的操作徐钠，因?yàn)橐葱枰獟呙璞碇械拇蟛糠謹(jǐn)?shù)據(jù)癌刽，要么查詢語(yǔ)句只能在某些特定的索引上才能有效運(yùn)行，而這類特定索引一般會(huì)對(duì)UPDATE操作有影響尝丐，所以一般不希望創(chuàng)建這樣的索引显拜。計(jì)算最活躍的用戶或者最常見(jiàn)的 ”標(biāo)簽” 是這種操作的典型例子。

????????緩存表則相反摊崭，其對(duì)優(yōu)化搜索和檢索查詢語(yǔ)句很有效讼油。這些查詢語(yǔ)句經(jīng)常需要特殊的表和索引結(jié)構(gòu)，跟普通OLTP操作用的表有些區(qū)別呢簸。

????????例如矮台，可能會(huì)需要很多不同的索引組合來(lái)加速各種類型的查詢。這些矛盾的需求有時(shí)需要?jiǎng)?chuàng)建一張只包含主表中部分列的緩存表根时。一個(gè)有用的技巧是對(duì)緩存表使用不同的存儲(chǔ)引擎瘦赫。例如，如果主表使用InnoDB, 用MyISAM作為緩存表的引擎將會(huì)得到更小的索引占用空間蛤迎，井且可以做全文搜索确虱。有時(shí)甚至想把整個(gè)表導(dǎo)出My SQL, 插入到專門的搜索系統(tǒng)中獲得更高的搜索效率，例如Lucene或者 Sphinx搜索引擎替裆。

????????在使用緩存表和匯總表時(shí)校辩，必須決定是實(shí)時(shí)維護(hù)數(shù)據(jù)還是定期重建窘问。哪個(gè)更好依賴于應(yīng)用程序，但是定期重建并不只是節(jié)省資源宜咒，也可以保持表不會(huì)有很多碎片惠赫，以及有完全順序組織的索引（這會(huì)更加高效）。

4.6總結(jié)

????????良好的schema設(shè)計(jì)原則是普遍適用的故黑，但MySQL有它自己的實(shí)現(xiàn)細(xì)節(jié)要注意儿咱。 概括來(lái)說(shuō)，盡可能保持任何東西小而簡(jiǎn)單總是好的场晶。MySQL喜歡簡(jiǎn)單混埠，需要使用數(shù)據(jù)庫(kù)的人應(yīng)該也同樣會(huì)喜歡簡(jiǎn)單的原則：

? 盡量避免過(guò)度設(shè)計(jì)，例如會(huì)導(dǎo)致極其復(fù)雜查詢的schema設(shè)計(jì)诗轻，或者有很多列的表設(shè)計(jì)（很多的意思是介于有點(diǎn)多和非常多之間）钳宪。

? 使用小而簡(jiǎn)單的合適數(shù)據(jù)類型，除非真實(shí)數(shù)據(jù)模型中有確切的需要概耻，否則應(yīng)該盡可能地避免使用 NULL 值使套。

? 盡量使用相同的數(shù)據(jù)類型存儲(chǔ)相似或相關(guān)的值罐呼，尤其是要在關(guān)聯(lián)條件中使用的列鞠柄。

? 注意可變長(zhǎng)字符串，其在臨時(shí)表和排序時(shí)可能導(dǎo)致悲觀的按最大長(zhǎng)度分配內(nèi)存嫉柴。

? 盡扯使用整型定義標(biāo)識(shí)列厌杜。

? 避免使用MySQL已經(jīng)遺棄的特性，例如指定浮點(diǎn)數(shù)的精度计螺，或者整數(shù)的顯示寬度夯尽。

? 小心使用 ENUM和SET。雖然它們用起來(lái)很方便登馒，但是不要濫用匙握，否則有時(shí)候會(huì)變成陷阱。 最好避免使用 BIT陈轿。

????????范式是好的圈纺，但是反范式（大多數(shù)情況下意味著重復(fù)數(shù)據(jù)） 有時(shí)也是必需的，并且能帶來(lái)好處麦射。 第5章我們將看到更多的例子蛾娶。 預(yù)先計(jì)算、 緩存或生成匯總表也可能獲得很大 的好處潜秋。Justin Swanhart的Flexviews工具可以幫助維護(hù)匯總表蛔琅。

????????最后，ALTER TABLE 是讓人痛苦的操作峻呛，因?yàn)樵诖蟛糠智闆r下罗售，它都會(huì)鎖表并且重建整張表辜窑。 我們展示了一些特殊的場(chǎng)景可以使用駭客方法，但是對(duì)大部分場(chǎng)景寨躁，必須使用其他更常規(guī)的方法谬擦，例如在備機(jī)執(zhí)行ALTER井在完成后把它切換為主庫(kù)。 本書(shū)后續(xù)章節(jié)會(huì) 有更多關(guān)千這方面的內(nèi)容朽缎。