“良好的邏輯設計和物理設計是高性能的基石昵观，應該根據(jù)將要執(zhí)行的查詢語句來設計schema，這往往需要權衡各種因素”。
適合的數(shù)據(jù)類型
MySQL支持的數(shù)據(jù)類型非常多衅鹿，選擇正確的數(shù)據(jù)類型對于獲得高性能至關重要毅厚。
- 更小的通常更好：更小的數(shù)據(jù)類型通常更快塞颁，因為它們占用更少的磁盤、內存和CPU緩存吸耿，并且處理時需要的CPU周期也更少祠锣。
- 簡單就好：簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期。例如咽安，整型比字符操作代價更低伴网，因為字符集和排序規(guī)則使字符比整型更復雜。
- 盡量避免NULL:可為NULL的列使得索引板乙、索引統(tǒng)計和值比較都更復雜是偷，會使用更過的存儲空間。
在為列選擇數(shù)據(jù)類型時募逞，第一步需要確定合適的大類型：數(shù)字蛋铆、字符串、時間等放接。第二步是選擇具體類型刺啦，很多數(shù)據(jù)類型可以存儲相同類型的數(shù)據(jù)，但是存儲的長度和范圍不一樣纠脾、允許的精度不同玛瘸。
整數(shù)類型
TINYINT蜕青、SMAILLINT、MEDIUMINT糊渊、INT右核、BIGINT，分別使用8渺绒、16贺喝、24、32宗兼、64位存儲空間躏鱼，可以存儲的值的范圍-2（n-1）~2（n-1） -1（n代表位數(shù)）。
整數(shù)類型有可選的UNSIGNED屬性殷绍，表示不允許負值染苛，例如TINYINT UNSIGNED的存儲范圍是0255，而TINYINT的存儲范圍是-128127主到。
可以為整數(shù)類型指定寬度茶行，例如INT(11)，對大多數(shù)應用這是沒有意義的镰烧，它不會限制值的合法范圍拢军，只是規(guī)定了MySQL的一些交互工具用來顯示字符的個數(shù)，對于存儲和計算來說怔鳖，INT(1)和INT(20)是相同的茉唉。
實數(shù)類型
實數(shù)是帶有小數(shù)部分的數(shù)字。FLOAT（4個字節(jié)）和DOUBLE（8個字節(jié)）類型支持使用標準的浮點運算進行近似計算结执。DECIMAL類型用于存儲精確的小數(shù)度陆，支持精確計算（MySQL自己實現(xiàn)了DECIMAL的高精度計算，CPU直接支持原生浮點計算献幔，所以浮點計算更快）懂傀。
浮點和DECIMAL類型都可以指定精度。對于DECIMAL可以指定小數(shù)點前后所允許的最大位數(shù)蜡感，將數(shù)字打包保存到一個二進制字符串中（每4個字節(jié)存9個數(shù)字）蹬蚁。例如，DECIMAL(18,9)小數(shù)點兩邊各存儲9個數(shù)字郑兴，一共占9個字節(jié)：小數(shù)點前的數(shù)字用4個字節(jié)犀斋，小數(shù)點后的數(shù)字用4個字節(jié)，小數(shù)點本身用1個字節(jié)情连。
因為需要額外的空間和計算開銷叽粹，所以應該盡量只在對小數(shù)進行精確計算時才使用DECIMAL，例如存儲財務數(shù)據(jù)。但是數(shù)據(jù)量比較大的時候虫几，可以考慮使用BIGINT代替锤灿，將需要存儲的貨幣單位根據(jù)小數(shù)的位數(shù)乘以相應的倍數(shù)即可。
字符串類型
VARCHAR類型用于存儲可變長字符串辆脸，需要使用1或2個額外字節(jié)記錄字符串的長度但校，最大長度不能超過65535字節(jié)。當字符串列的最大長度比平均長度大很多啡氢；列的更新很少始腾，所以碎片不是問題；使用了像UTF-8這樣復雜的字符集空执，每個字符都使用不同的字節(jié)數(shù)進行存儲適合使用VARCHAR。
CHAR類型是定長的穗椅，適合存儲很短的字符串辨绊，或者所有值都接近同一個長度。例如匹表，CHAR非常適合存儲密碼的MD5值门坷，因為這是一個定長的值。對于經(jīng)常變更的數(shù)據(jù)袍镀，CHAR也比VARCHAR更好默蚌，因為定長的CHAR不容易產(chǎn)生碎片，對于非常短的列苇羡，CHAR比VARCHAR在存儲空間上也更有效率绸吸，例如用CHAR(1)來存儲只有Y和N的值，如果采用單字節(jié)字符集只需要一個字節(jié)设江，但是VARCHAR(1)取需要兩個字節(jié)锦茁，因為還有一個記錄長度的額外字節(jié)。
注：使用VARCHAR(5)和VARCHAR(200)存儲"hello"的空間開銷是一樣的叉存，但是更長的列會消耗更多的內存码俩，因為可變長字符串，其在臨時表和排序時可能導致悲觀的按最大長度分配內存歼捏。所以最好的策略是只分配真正需要的空間稿存。
BLOB和TEXT類型
為存儲很大的數(shù)據(jù)而設計的可變長字符串數(shù)據(jù)類型，分別采用二進制和字符方式存儲瞳秽。字符類型是TINYTEXT瓣履、SMALLTEXT(TEXT)、MEDIUMTEXT寂诱、LONGTEXT拂苹，對應的二進制類型是TINYBLOB、SMALLBLOB(BLOB)、MEDUIMBLOB瓢棒、LONGBLOB浴韭。通過最大字符數(shù)來限制，TEXT的最大長度為65535(216-1)個字符脯宿。
日期和時間類型
DATETIME從1001年到9999年念颈，精度為秒。把日期和時間封裝到格式為YYYYMMDDHHMMSS的整數(shù)中连霉，與時區(qū)無關榴芳，使用8個字節(jié)的存儲空間。例如跺撼，"2018-01-01 08:00:00"窟感。
TIMESTAMP保存了從格林威治時間1970年01月01日00時00分00秒起至現(xiàn)在的秒數(shù)，只使用4個字節(jié)的存儲空間歉井，只能表示從1970年到2038年柿祈。
如果在多個時區(qū)存儲或訪問數(shù)據(jù)，TIMESTAMP和DATETIME的行為將很不一樣哩至，前者提供的值與時區(qū)有關躏嚎，后者則保留文本表示的日期和時間。TIMESTAMP可以在插入時自動生成當前時間戳菩貌，以及在修改時根據(jù)當前時間戳自動更新卢佣。
除了特殊行為外，通常應該盡量使用TIMESTAMP箭阶，因為它比DATETIME空間效率更高虚茶。用整數(shù)保存時間戳的格式通常不方便處理，不推薦這樣做仇参。
BIT媳危、SET、ENUM
BIT的最大長度是64位冈敛。在MyISAM中待笑，BIT的存儲空間很小，是真正的實現(xiàn)了通過bit來存儲抓谴，但是在其他的一些存儲引擎中就不一樣了暮蹂，因為它們是轉換為最小的INT類型存儲的，所以占用的空間也沒有節(jié)省癌压，還不如直接使用INT類的數(shù)據(jù)類型存放來得直觀仰泻。
對于SET和ENUM類型，主要內容基本處于較少變化狀態(tài)且值比較少的字段滩届。雖然這兩個字段所占用的存儲空間都較少集侯，但是由于在使用方面較其他的數(shù)據(jù)類型要略為復雜一些，所以在實際環(huán)境中一般使用還是較少。
選擇標識符
選擇合適的標識符是非常重要棠枉。選擇時不僅應該考慮存儲類型浓体，而且應該考慮MySQL是怎樣進行運算和比較的。一旦選定數(shù)據(jù)類型辈讶，要確保保證所有關聯(lián)表中都使用相同的數(shù)據(jù)類型命浴。
在可以滿足值的范圍的需求，并且預留未來增長空間的前提下贱除，應該選擇最小的數(shù)據(jù)類型生闲。
整數(shù)類型：整數(shù)通常是標識列最好的選擇，因為它們很快并且可以使用AUTO_INCREMENT月幌。
字符串類型：盡量避免使用字符串類型作為標識符碍讯，因為它們很消耗空間，并且通常比數(shù)字類型慢扯躺。而且冲茸，對于隨機的字符串，它們在索引中的位置也是隨機的缅帘，這會導致頁分裂、磁盤隨機訪問难衰，以及對于聚簇存儲引擎產(chǎn)生聚簇索引分裂 钦无。
優(yōu)化Schema設計
混用范式和反范式，適度冗余
范式的優(yōu)點就是讓數(shù)據(jù)庫中盡量的去除數(shù)據(jù)的冗余盖袭，保持數(shù)據(jù)的一致失暂，使數(shù)據(jù)的修改簡單，然而它的缺點是通常需要關聯(lián)鳄虱，這不但代價昂貴弟塞，也可能使一些索引策略無效。反范式因為所有數(shù)據(jù)都在一張表中拙已，可以很好地避免關聯(lián)决记。
雖然冗余字段的更新成本增加了，但是查詢效率提高了倍踪，而且大多冗余字段的選取是查詢頻率遠大于更新頻率的字段系宫。
雖然范式化的數(shù)據(jù)庫中的表一般都較小，使表中相關列最少建车，在某些情況下增強了數(shù)據(jù)庫的可維護性扩借，但在系統(tǒng)要完成一些數(shù)據(jù)查詢時，可能要用復雜的Join才能實現(xiàn)缤至，這勢必會造成查詢性能的低下潮罪，如果通過拆分Join，通過多次簡單的查詢來在應用中實現(xiàn)Join邏輯，將會帶來巨大的網(wǎng)絡開銷嫉到。
大字段垂直拆分
適度冗余策略是將別的表中的字段拿過來在自己身上也存一份數(shù)據(jù)沃暗，而大字段垂直拆分簡單來說就是將自己身上的字段拆分出去放在另外（單獨）的表里面。自相矛盾屯碴？不描睦，將別表的字段拿過來，是因為很多時候查詢需要使用該字段导而，為了減少Join帶來的性能消耗忱叭。而將大字段拿出去，是在大部分查詢不需要使用該字段的時候才會拿出去今艺。首先大字段（文章的內容韵丑、帖子的內容、產(chǎn)品的介紹等）虚缎，其次是和表中其他字段相比訪問頻率明顯要少的字段很多適合拆分出去撵彻。
大字段存放的內容較多，占整條記錄的80%以上实牡，而數(shù)據(jù)庫中數(shù)據(jù)在數(shù)據(jù)文件中的格式一般都是以一條一條記錄為單位來存放陌僵，也就是說，要查詢某些記錄的某幾個字段创坞，數(shù)據(jù)庫并不是只需要訪問這幾個字段碗短，而是需要讀取其它所有字段（可以在索引中完成整個查詢的情況除外），這就不得不讀取包括大字段在內的很多并不相干的數(shù)據(jù)题涨，而由于大字段所占的空間比例非常大偎谁，自然所浪費的IO資源也就非常大了。故將該大字段從原表中拆分出來纲堵，通過單獨的表進行存放巡雨，這樣在訪問其它數(shù)據(jù)時大大降低IO訪問，從而使查詢性能得到較大的改善席函。
實際上铐望，不一定非要大字段才能進行垂直拆分，某些場景下茂附，有的表中大部分字段平時都很少訪問蝌以，而其中的某幾個字段卻是訪問頻率非常高，對于這種表何之，也非常適合通過垂直拆分來達到優(yōu)化性能的目的跟畅。
大表水平拆分
舉個例子，某論壇系統(tǒng)有張post表存放帖子數(shù)據(jù)溶推，現(xiàn)在有個需求徊件，系統(tǒng)管理員能夠發(fā)布系統(tǒng)消息奸攻，并且在貼子的每一頁置頂顯示。第一反應肯定是通過在post表中增加一個標識列虱痕，用來存放帖子的類型睹耐，標識出是用戶的普通貼還是管理員的置頂貼，然后在每個列表展示頁面都通過對post表的兩次查詢（一次置頂貼部翘，一個普通貼）并在應用程序中合并再展示硝训。這樣可能造成的結果是由于整個post表的數(shù)據(jù)較大，查詢置頂?shù)腝uery成本會相對有些高新思。
置頂信息和普通帖完全不會產(chǎn)生任何關聯(lián)交互窖梁、置頂信息的變化相對于普通帖來說變化很少、置頂消息的訪問頻率非常高、置頂信息的量和普通帖子來比非常之少。經(jīng)過這幾個分析土榴，可以將置頂消息單獨存放在普通貼之外的其它表里面。由于訪問頻率非常高假哎，這種方案使得每次檢索置頂消息的成本下降，數(shù)量少而且變化不怎么頻繁的特點則非常適合使用MySQL的Query Cache鞍历，而如果和普通貼存放在一起會由于普通貼的頻繁變化帶來post表相關Query Cache失效舵抹。
統(tǒng)計表準實時優(yōu)化
通過定時統(tǒng)計數(shù)據(jù)來替代實時統(tǒng)計查詢。 因為實時統(tǒng)計的性能消耗成本太高劣砍，每一次展示都需要進行統(tǒng)計計算惧蛹，帶來大量的重復資源浪費。對于準確性要求并不是特別嚴格秆剪、對時間并不是太敏感、訪問非常頻繁爵政，重復執(zhí)行較多仅讽、參與統(tǒng)計數(shù)據(jù)量較大這類統(tǒng)計數(shù)據(jù)可以準實時統(tǒng)計表來優(yōu)化。例如钾挟，系統(tǒng)當前在線人數(shù)洁灵，論壇系統(tǒng)當前總帖數(shù)、回帖數(shù)掺出，虛擬積分排名等徽千。
這些統(tǒng)計的計算都會涉及到大量數(shù)據(jù)，同時也需要大量的計算資源汤锨，訪問頻率也都非常的高双抽。如果都通過實時統(tǒng)計，只要數(shù)據(jù)量稍微大些闲礼，都會帶來非常大的硬件資源開銷牍汹。但在短時間內的不夠精確铐维，并不會帶來太大用戶體驗的降低，所以完全可以通過定時任務程序慎菲，每隔一定時間段進行一次統(tǒng)計后存放在專門設計的統(tǒng)計表中嫁蛇。
參考
- 《高性能MySQL》
- 《MySQL性能調優(yōu)與架構設計》
