????很有用的一章，將為接下來兩個章節(jié)鋪墊，這三個章節(jié)中將 討論邏輯設(shè)計、物理設(shè)計和查詢執(zhí)行，以及它們之間的相互作用。

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

????更小的通常更好：應(yīng)該盡量使用可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型（例如只需要存0-200，使用tinyint unsigned更好）。更小的數(shù)據(jù)類型通常更快，因為它們占用更少的磁盤、內(nèi)存和CPU緩存，并且處理時需要的CPU周期也更少。同時要確保沒有低估需要存儲的值得范圍，因為要在schema中多個地方添加數(shù)據(jù)類型的范圍是一個非常耗時和痛苦的操作。

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

????盡量避免NULL：NULL是列的默認(rèn)屬性，通常最好指定列為NOT NULL，除非真的需要存儲NULL。一般查詢中包含可NULL的列，對MySQL來說更難優(yōu)化，因為可謂NULL的列使得索引、索引統(tǒng)計和值比較都變得更復(fù)雜，而且可為NULL的列會使用更多的存儲空間，在MySQL里也需要特殊處理。當(dāng)NULL列被索引時每個索引記錄需要一個額外的字節(jié)，所以如果計劃在列上建索引，就應(yīng)該盡量避免設(shè)計成為可為NULL的列。值得一提的是，對于InnoDB來說，它使用單獨的位來存儲NULL值，這對于稀疏數(shù)據(jù)（多NULL少NOT NULL）有很好的空間效率。

????在為列選擇數(shù)據(jù)類型時，第一步需要確定合適的大類型：數(shù)字、字符串、時間等，然后下一步是選擇具體類型。很多MySQL數(shù)據(jù)類型可以存儲相同類型的數(shù)據(jù)，但是其存儲的長度和范圍不一樣、允許的精度不同、需要的物理空間也不同。例如，DATATIME和TIMESTAMP都可以存儲時間和日期，精確到秒，然而TIMESTAMP就只有DATATIME一半的存儲空間，并且會根據(jù)時區(qū)變化，具有特殊自動更新能力，但是其時間范圍要小很多。

整數(shù)類型

????有TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分別使用8、16、24、32、64位存儲空間，整型類型有可選UNSIGNED屬性，表示不允許負(fù)值，這大致可以使正數(shù)的上限提高一倍。

????MySQL可以為整數(shù)類型指定寬度，例如INT(11)，對大多數(shù)應(yīng)用這是沒有意義的，它不會限制值得合法范圍，只是規(guī)定了MySQL的一些交互工具用來顯示字符的個數(shù)。對于存儲和計算來說，INT(1)和INT(20)是相同的

實數(shù)類型

????帶有小數(shù)部分的數(shù)字。MySQL既支持精確類型也支持不精確類型。FLOAT和DOUBLE類型支持使用標(biāo)準(zhǔn)的浮點運算進行近似計算會損失精度；DECIAML類型用于存儲精確的小數(shù)，使用MySQL服務(wù)器自身實現(xiàn)高精度的計算，因此相對來說運算較慢。

????浮點和DECIMAL以下都可以指定精度，對于DECIMAL列，可以指定小數(shù)點前后所允許的最大位數(shù)，這會影響列的空間消耗。因為需要額外的空間和計算開銷，所以應(yīng)該盡量只在對小數(shù)進行精確計算時才使用DECIMAL。

字符串類型

????支持多種字符串類型，每個類型還有很多變種。

????VARCHAR：用于存儲可變長的字符串，是最常見的字符串?dāng)?shù)據(jù)類型。它比定長類型更節(jié)省空間，但如果MySQL表使用ROW_FORMAT=FIXED創(chuàng)建的話，每一行就是定長存儲，這會很浪費空間。VARCHAR要使用1或2個額外字節(jié)記錄字符串的長度，如果列的最大長度不大于255字節(jié)就只使用1個字節(jié)表示，否則使用2個字節(jié)。由于行是變長的，在UPDATE時當(dāng)行變得比原來更長時就會導(dǎo)致更多的額外工作（MyISAM將行拆分成不同的片段存儲，InnoDB則需要分裂頁來使行可以放進頁內(nèi)，其他引擎可能從不在原數(shù)據(jù)位置更新）。VARCHAR(5)和VARCAHR(200)存儲‘hello’的空間開銷是一樣的，但是其內(nèi)存消耗卻相差很大，更長的列會消耗更多的內(nèi)存，故最好是只分配需要的空間。

????CHAR：是定長的，MySQL通過定義的字符串長度分配足夠的空間。當(dāng)存儲CHAR值時，MySQL會刪除所有的末尾空格（如果存儲“str? ”就會刪除后面的空格），為方便比較CHAR值會根據(jù)需要采用空格進行填充。CHAR適合存儲很短的字符串，或者所有值都接近同一個長度，例如就很適合存儲MD5值。CHAR值不容易產(chǎn)生碎片，如果使用CHAR(1)存儲Y和N的值就只需要一個字節(jié)，但是VARCHAR(1)卻需要兩個字節(jié)因為還有一個字節(jié)記錄長度。

????BINARY和VARBINARY：存儲的是二進制字符串，與常規(guī)的字符串相似，但二進制字符串存儲的是字節(jié)碼而不是字節(jié)，填充也不一樣，MySQL填充BINARY采用的是\0（零字節(jié)）而不是空格而且檢索時也不會去掉填充值。所以如果需要檢索時保持值不變，則需要特別小心BINARY這個類型。

????BLOB和TEXT類型：存儲很大的數(shù)據(jù)而設(shè)計的字符串?dāng)?shù)據(jù)類型，分表采用二進制和字符方式存儲。MySQL會把每個BLOB和TEXT值當(dāng)作一個獨立的對象處理，存儲引擎在存儲時通常會做特殊處理，當(dāng)值太大時，InnoDB會使用專門的“外部”存儲區(qū)域來進行存儲，這時每個值在行內(nèi)都需要1~4個字節(jié)存儲一個指針，然后在外部存儲區(qū)域存儲實際的值。BLOB和TEXT的區(qū)別在于BLOB類型粗才能的是二進制數(shù)據(jù)，沒有排序規(guī)則或字符集，而TEXT類型有字符集和排序規(guī)則。MySQL對這兩種類型的排序不是比較所有的字符串而是對每個列的最前max_sort_length字節(jié)進行排序。

????使用枚舉（ENUM）代替字符串類型：可以把一些不重復(fù)的字符串存儲成一個預(yù)定義的集合。MySQL在存儲枚舉時非常緊湊，會根據(jù)列表值的數(shù)量壓縮到一個或兩個字節(jié)中，形成映射關(guān)系。但是其排序規(guī)則是按照鍵的大小排序的，這點不是太好。最不好的地方是字符串列表是固定的，添加或者刪除其他不同的字符串必須使用DDL語句，對于那些可能改變的字符串并不是一個好辦法。除非你非常確定只有這幾個值，否則還不如使用TINYINT來的好，（別告訴我性別只有男女，要是你的BOSS后面要你加個保密，加個人妖什么的。。）

日期和時間類型

????DATATIME：能保存大范圍的值，從1001年到9999年，精確到秒。它把日期和時間封裝到格式為YYYYMMDDHHMMSS的整數(shù)中，與時區(qū)無關(guān)，使用8個字節(jié)存儲空間。

????TIMESTAMP：保存了時間戳，范圍是1970到2038年，精確到秒，該類型與時區(qū)相關(guān)。默認(rèn)情況下，如果插入時沒有指定第一個TIMESTAMP列的值，MySQL則會設(shè)置這個列為當(dāng)前時間，當(dāng)更新記錄時也會默認(rèn)更新第一個TIMESTAMP列的值。

????除了特殊行為外，盡量使用TIMESTAMP，因為它比DATETIME空間效率更高。TIMESTAMP的行為規(guī)則比較復(fù)雜，并且在不同的MySQL版本里會變動，所以使用SHOW CREATE TABLE命令檢查輸出是有必要的。

位數(shù)據(jù)類型

????MySQL有少數(shù)幾種存儲類型使用緊湊的位存儲數(shù)據(jù)，所有這些位類型，不管底層存儲格式和處理方式如何，從技術(shù)上來說都是字符串類型。

????BIT：可以存儲一個或多個true/false值。不同的存儲類型的行為不同，MyISAM會打包存儲所有的BIT列，所以17個單獨的BIT列只使用3個字節(jié)就能存儲；其他類型Memory和InnoDB，為每個BIT列使用一個足夠存儲的最小整數(shù)類型來存放，所以不能節(jié)省存儲空間。MySQL把BIT當(dāng)作字符串類型而不是數(shù)字類型，在檢索BIT的值時結(jié)果應(yīng)該是一個二進制字符串，如果在數(shù)字上下文中可以得到數(shù)字（a+0）,可根據(jù)二進制輸出對應(yīng)的ASCII（ascii(a)）之類的。

????SET：如果需要保存很多true/false值，可以考慮合并這些列到一個SET數(shù)據(jù)類型，它在MySQL內(nèi)部是以一系列打包的位的集合來表示的，這樣就有效的利用了存儲空間，和ENUM一樣它改變列的定義的代價要使用DDL語句，代價高，一般來說也無法在SET列上通過索引檢索。

????在整數(shù)列上進行按位操作：一種代替SET的方法是使用一個整數(shù)包裝一系列的位，比如可以把8個位包裝到一個TINYINT中，并且可以按位操作來使用。這樣可以不使用DDL語句，但是更難理解。

選擇標(biāo)識符

????為標(biāo)識列（identifier column，又稱自增長列）選擇合適的數(shù)據(jù)類型非常重要。一般來說更可能用標(biāo)識列與其他值進行比較，或者通過標(biāo)識列尋找其他列，或者作為關(guān)聯(lián)列。所以應(yīng)該選擇跟關(guān)聯(lián)表中的對應(yīng)列一樣的類型。

????選擇標(biāo)識列的類型時不僅僅需要考慮存儲類型，還需要考慮MySQL對這種類型怎么執(zhí)行計算和比較。一旦選定了一種類型，要確保在所有的關(guān)聯(lián)表中都使用同樣的類型，混用的話可能導(dǎo)致性能問題，或者在比較操作時造成很難發(fā)現(xiàn)的錯誤。

????推薦使用整數(shù)類型來作為標(biāo)識列的類型，因為它們很快而且可自增。使用ENUM和SET類型或者使用字符串類型都是一個糟糕的選擇，前者只能固定值導(dǎo)致限定多，或者非常消耗空間且速度慢。

特殊類型數(shù)據(jù)

某些類型的數(shù)據(jù)并不直接與內(nèi)置類型一致。比如低于秒級精度的時間戳，或者使用VARCHAR來存儲IPv4地址等等都是一些習(xí)慣導(dǎo)致的性能問題。

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

????雖然有一些普遍的好或壞的設(shè)計原則，但也有問題是由MySQL實現(xiàn)機制導(dǎo)致的，這意外著有可能犯一些只有在MySQL下才會發(fā)生的錯誤。

????太多的列：MySQL的存儲引擎API工作時需要在服務(wù)層和存儲引擎層之間通過行緩沖格式拷貝數(shù)據(jù)，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個列。從行緩沖中將編碼過的列轉(zhuǎn)換成行數(shù)據(jù)結(jié)構(gòu)的操作代價非常高，所以應(yīng)該避免寫太多的列。（提取熱點的列，分表保存不怎么用到的列）

????太多的關(guān)聯(lián)：所謂的“實體-屬性-值”（EAV）設(shè)計模式是一個常見的糟糕設(shè)計模式，尤其在MySQL下不能靠譜地工作，因為它會關(guān)聯(lián)很多個表，而MySQL的上限是61張表。但如果希望查詢執(zhí)行速度快并且并發(fā)性好，單個查詢最好在12個表內(nèi)做關(guān)聯(lián)（當(dāng)然越少越好，一般4個關(guān)聯(lián)就很多了，如果有需要最好在業(yè)務(wù)端拆分成多個sql來查詢比較好）。

????全能的枚舉：防止過度使用枚舉ENUM。（別用枚舉了。。）

????變相的枚舉：改用ENUM時不要用SET，ENUM在列中只允許存儲單個值，SET可以存儲多個值，要清楚兩個類型的優(yōu)劣。

????NULL問題：之前說過要避免使用NULL，建議盡可能考慮替代方案，一些“空值”實際上可以用0或者空字符串代替。但也不能走極端，當(dāng)確實需要表示未知值時也不要害怕用NULL，在一些場景下可能使用NULL會更好，如果設(shè)置特殊值表示空值可能帶來更大的麻煩時就不要吝嗇使用NULL。

范式和反范式

????對于任何給定的數(shù)據(jù)通常有很多表示方法，從完全范式化到完全反范式化，以及兩者的折中。在范式化的數(shù)據(jù)庫中，每個事實數(shù)據(jù)會出現(xiàn)并且只出現(xiàn)一次。相反，在反范式化的數(shù)據(jù)庫中，信息是冗余的，可能會存儲在很多地方。

????當(dāng)為性能問題而尋求幫助時經(jīng)常會被建議對schema進行范式化設(shè)計，尤其是寫密集的場景。范式化帶來的好處：

范式化的更新操作通常比反范式化要快

當(dāng)數(shù)據(jù)較好地范式化時，就只有很少或者沒有重復(fù)數(shù)據(jù)，因此只要修改更少的數(shù)據(jù)

范式化的表通常小，可以更好地放在內(nèi)存里，所以執(zhí)行操作會更快。

很少有多余的數(shù)據(jù)意味著檢索列表數(shù)據(jù)時更少需要DISTINCT或者GROUP BY

????但是也有很明顯的缺點就是要關(guān)聯(lián)，一些復(fù)雜查詢語句可能要關(guān)聯(lián)多個表，這不但代價昂貴而且可能使一些索引策略無效。例如，范式化可能將列存放在不同的表中，而這些列如果在一個表中本可以屬于同一個索引。

????反范式化的schema因為所有數(shù)據(jù)都在一張表中可以很好避免關(guān)聯(lián)，如果不需要關(guān)聯(lián)，那么當(dāng)數(shù)據(jù)比內(nèi)存大時，即使使用全表掃描都比關(guān)聯(lián)要快的多。因為避免了隨機I/O，而全表掃描基本上是順序I/O。

????既然都有優(yōu)缺點，在實際應(yīng)用中通常是混用模式，可能使用部分范式化的schema、緩存表、以及其他技巧。最常見的反范式化的方法是復(fù)制冗余或者緩存。

緩存表和匯總表

????有時提升性能最好的方法是在同一張表中保存衍生的冗余數(shù)據(jù)。然而，有時也需要創(chuàng)建一張完全獨立的匯總表或緩存表（特別是為滿足檢索需要時）。如果能容許少量的臟數(shù)據(jù)，這是個非常好的方法。

????一般緩存表存一些可以比較簡單地從一些關(guān)聯(lián)表中獲取的數(shù)據(jù)（單次獲取會比較慢），而匯總表一般保存的是使用GROUP BY語句聚合數(shù)據(jù)的表。

????有時候需要很多不同的索引組合來加速各種類型的查詢。這些矛盾的需求有時需要創(chuàng)建一張只包含主表中部分列的緩存表，一個有效的技巧是對緩存表使用不同的存儲引擎，例如主表使用InnoDB，那么用MyISAM作為緩存表的引擎將會得到更小的索引占用空間，并且可以做全文搜索。

物化視圖

????物化視圖實際上是預(yù)先計算并且存儲在磁盤上的表，可以通過各種策略刷新和更新。MySQL并不原生支持物化視圖，而是使用Flexviews外部實現(xiàn)物化視圖。對比傳統(tǒng)的維護匯總表和緩存表，F(xiàn)lexviews通過提取對源表的更改，可以增量地重新計算物化視圖的內(nèi)容。這意味著不需要通過查詢原始數(shù)據(jù)來更新視圖。這樣計算增量數(shù)據(jù)比從源表中讀取數(shù)據(jù)的效率要高很多。

計數(shù)器表

????如果應(yīng)用在表中保存計數(shù)器，則在更新計數(shù)器時可能碰到并發(fā)問題。計數(shù)器表在Web應(yīng)用中很常見?？梢杂眠@種表緩存一個用戶的朋友數(shù)、文件下載次數(shù)等。創(chuàng)建一張獨立的表存儲計數(shù)器通常是個好主意，這樣可以使計數(shù)器表小且快。使用獨立的表可以幫助避免查詢緩存失效，并且可以使用一些高級技巧，比如計數(shù)的時候可以多增加幾行計數(shù)，或者隔一段時間增加一行計數(shù)，這樣update就不會因為鎖行（表）而導(dǎo)致并發(fā)效率低。

????為了提高讀的速度，有時候相應(yīng)的會增加寫的負(fù)擔(dān)，也需要額外的維護任務(wù)。這些都是設(shè)計高性能數(shù)據(jù)庫時，要承擔(dān)的代價。

加快ALTER TABLE操作的速度

????MySQL的ALTER TABLE操作的性能對大表來說是個大問題。MySQL執(zhí)行大部分修改表結(jié)構(gòu)操作的方法是用新的結(jié)構(gòu)創(chuàng)建一個空表，從舊表中查出所有數(shù)據(jù)插入新表，然后刪除舊表。這樣的操作可能需要花費很長的時間，如果內(nèi)存不足而表又很大，而且還有很多索引的情況下尤其如此。有時候需要花數(shù)個小時甚至數(shù)天才能完成。

????一般而言，大部分ALTER TABLE操作將導(dǎo)致MySQL服務(wù)中斷。一般情況下回使用兩種技巧：一種是先在一臺不提供服務(wù)的機器上執(zhí)行ALTER TABLE操作，然后和提供服務(wù)的主庫進行切換；另一種就是“影子拷貝”，影子拷貝是用要求的表結(jié)構(gòu)創(chuàng)建一張和源表無關(guān)的新表，然后通過重命名和三標(biāo)操作交換兩張表。

????不是所有的ALTER TABLE操作都會引起表重建，有兩種方法可以改變或者刪除一個列的默認(rèn)值，一種是跳過創(chuàng)建新表的步驟，直接在.frm文件中修改列的默認(rèn)值，即直接修改文件而不需要改動表本身；另一種是通過ALTER COLUMN的操作來該表列的默認(rèn)值，這個語句會直接修改.frm文件而不涉及表數(shù)據(jù)。

只修改.frm文件

????直接修改.frm文件是很快的，但是MySQL有時候會在沒有必要的時候也重建表。如果愿意冒一些風(fēng)險，可以讓MySQL做一些其他類型的修改二不用重建表。但是這些技巧需要承擔(dān)風(fēng)險的，所以在執(zhí)行之前先確保做了數(shù)據(jù)備份。

????這些操作時可能不需要重建表的：1、移除（不是增加）一個列的AUTO_INCREMENT屬性。2、增加、移除或者更改ENUM和SET常量，如果移除的是一件有行數(shù)據(jù)用到其值得常量，查詢將會返回一個空字串值。

????基本的技術(shù)是為想要的表結(jié)構(gòu)創(chuàng)建一個新的.frm文件，然后用它替換掉已經(jīng)存在的那張表的.frm文件，像這樣：

1、創(chuàng)建一張有相同結(jié)構(gòu)的空表，并進行所需要的修改（例如增加ENUM常量）。

2、執(zhí)行FLUSH TABLES WITH READ LOCK。這將會關(guān)閉所有正在使用的表，并且禁止任何表被打開。

3、交換.frm文件

4、執(zhí)行UNLOCK TABLES來釋放第2步的讀鎖。

快速創(chuàng)建MyISAM索引

????為了高效地載入數(shù)據(jù)到MyISAM表中，一個常用的技巧是先禁用索引、載入數(shù)據(jù)，然后重新啟用索引。

mysql>altertabletest.load_datadisablekeys;

–load thedata

mysql>altertabletest.loadenablekeys;

????這個技巧能夠發(fā)揮作用，是因為構(gòu)架索引的工作被延遲到數(shù)據(jù)完全載入之后，這個時候已經(jīng)通過排序來構(gòu)建索引了。這樣會快很多，并且使得索引樹的碎片更少、更緊湊。

????不幸的是，這個辦法對唯一索引無效，因為disable keys只對非唯一索引有效。

????InnoDB有一個類似的技巧，這依賴于InnoDB的快速在線索引創(chuàng)建功能。先刪除所有的非唯一索引，然后增加新的列，最后重新創(chuàng)建刪除掉的索引。也可以使用前面說的ALTER TABLE的駭客方法來加速這個操作，但需要多做一些工作并承擔(dān)一定風(fēng)險。這對從備份中載入數(shù)據(jù)是很有用的，例如，當(dāng)已經(jīng)知道所有數(shù)據(jù)都是有效并且沒有必要做唯一性檢查時就可以這么來操作。

????下面的步驟用于快速創(chuàng)建MyISAM表索引（記得備份數(shù)據(jù)）：

1、用需要的表結(jié)構(gòu)創(chuàng)建一張表，但不包括索引。

2、載入數(shù)據(jù)到表中以創(chuàng)建.MYD文件。

3、按照需要的結(jié)構(gòu)創(chuàng)建另外一張空表，這次要包含索引。這會創(chuàng)建需要的.frm和.MYI文件。

4、獲取讀鎖并刷新表。

5、重命名第二張表的.frm和.MYI文件，讓MySQL人為是第一張表的文件。

6、釋放讀鎖。

7、使用REPAIR TABLE來重建表的索引。該操作會通過排序來構(gòu)建所有索引，包括唯一索引。

????這個操作步驟對大表來說會快很多。

總結(jié)

????良好的schema設(shè)計原則是普遍適用的，但MySQL有自己的實現(xiàn)細(xì)節(jié)要注意。概況來說，盡可能保持任何東西小且簡單總是好的。