良好的邏輯設(shè)計(jì)和物理設(shè)計(jì)是高性能的基石，應(yīng)該根據(jù)系統(tǒng)將要執(zhí)行的查詢語句設(shè)計(jì)schema搂抒，但記住這往往需要權(quán)衡各種因素艇搀。例如，反范式的設(shè)計(jì)可以加快某些類型的查詢求晶，但同時(shí)也可能使一些查詢變慢焰雕。比如添加計(jì)數(shù)表和匯總表是很好的優(yōu)化查詢的方式，但這些表的維護(hù)成本很高芳杏。

==================================================================

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

MySQL支持的數(shù)據(jù)類型非常多矩屁，選擇正確的數(shù)據(jù)類型對于獲得高性能至關(guān)重要。以下羅列選擇的原則：

1）更小的通常更好爵赵。一般情況下吝秕，應(yīng)該使用可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型。例如空幻，只需存儲0－200整數(shù)烁峭，那么相比 int 來說 tinyint unsigned 更好。更小的數(shù)據(jù)類型通常更快，占用更少的磁盤空間约郁、內(nèi)存缩挑、CPU緩存，并且處理時(shí)需要的CPU周期也更少鬓梅。但是需要確保沒有低估存儲值的范圍供置，因?yàn)槲磥硪趕chema中多個(gè)地方增加數(shù)據(jù)類型的范圍是一個(gè)耗時(shí)和痛苦的操作。

2）簡單就好绽快。簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期芥丧，例如整數(shù)型比字符型操作代價(jià)更低，因?yàn)樽址托σ?guī)則（排序規(guī)則）使字符比較比整型更復(fù)雜坊罢。另外的例子续担，使用MySQL內(nèi)建的date、time艘绍、datetime來存儲日期和時(shí)間赤拒，而不是使用字符串秫筏，還有使用整型存儲IP地址诱鞠，也不要使用字符型。

3）盡量避免使用NULL这敬。創(chuàng)建時(shí)盡量使用NOT NULL航夺，因?yàn)镹ULL值會使該列的索引、索引統(tǒng)計(jì)崔涂、值的比較都更復(fù)雜阳掐，而且會使用更多存儲空間。特別是一定要在列上建立索引時(shí)冷蚂，一定是NOT NULL缭保。

如何選擇數(shù)據(jù)類型，第一步蝙茶，需要確定合適的大類型艺骂，第二步，在大類型中選擇小類型隆夯。

==================================================================

1.1.1 整數(shù)類型

整數(shù)類型主要包括 TINYINT（8位钳恕，-128~127）、SMALLINT（16位）蹄衷、MEDIUMINT（24位）忧额、INT（32位）、BIGINT（64位）愧口，他們的存儲的值的范圍為：-2^(N-1)至2^(N-1)-1睦番，以上都可以使用UNSIGNED，取值范圍為0～2^N-1耍属。

注意：這個(gè)選擇僅影響數(shù)據(jù)在內(nèi)存和硬盤中的存儲托嚣，不會對整數(shù)的計(jì)算產(chǎn)生影響大咱，因?yàn)镸ySQL計(jì)算時(shí)均轉(zhuǎn)換為64位 BIGINT 進(jìn)行。

注意：MySQL可以為整數(shù)類型指定寬度注益，這只是顯示的寬度碴巾，如INT(5)，顯示5個(gè)字符寬度丑搔，如123厦瓢，會顯示00123，因此不會影響取值啤月，也就是說比如INT(5)和INT(11)在存儲和計(jì)算時(shí)是相同的煮仇，沒有區(qū)別。

==================================================================

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

實(shí)數(shù)類型包括 FLOAT（不精確類型）谎仲、DOUBLE（不精確類型）浙垫、DECIMAL（精確類型），支持指定精度郑诺，如DECIMAL(18,9)夹姥，表示小數(shù)點(diǎn)兩邊各9個(gè)數(shù)字。

注意：由于指定精度辙诞，會使得MySQL悄悄地選擇不同的數(shù)據(jù)類型辙售，或者在存儲時(shí)對值自動(dòng)取舍，因此建議只指定數(shù)據(jù)類型飞涂，不指定精度旦部，精度根據(jù)需要在前端取舍。

注意：如果真需要DECIMAL较店，建議使用乘以10^N取得整數(shù)后使用INT（或BIGINT）數(shù)據(jù)類型士八，前端顯示時(shí)根據(jù)需要除以10^N獲得正確的結(jié)果，或者將指數(shù)N對應(yīng)以INT存入數(shù)據(jù)庫梁呈，這樣使MySQL得到優(yōu)化婚度。

==================================================================

4.1.3 字符串類型

MySQL有多種字符串類型，每種可以定義不同的字符集捧杉、排序規(guī)則（校對規(guī)則）陕见，這些東西會很大程度上影響性能。

VARCHAR和CHAR是兩種基本的類型味抖，不幸的是评甜，很難解釋清楚兩種類型是怎么存儲在磁盤和內(nèi)存中的，因?yàn)檫@跟存儲引擎有關(guān)仔涩。不同的存儲引擎（MyISAM忍坷、InnoDB、。佩研。柑肴。）都不同。

VARCHAR類型旬薯，用于存儲可變長字符串晰骑，它比定長類型節(jié)省空間，因?yàn)樗鼉H使用必要的空間绊序，即硕舆，越短的字符串使用越少空間。除非使用 ROW_FORMAT=FIXED骤公，每行定長抚官，這樣會浪費(fèi)空間。另外阶捆，VARCHAR使用1～2個(gè)額外字節(jié)記錄字符串的長度凌节，如果列長度小于或等于255，使用1個(gè)字節(jié)表示（8位）洒试，如果大于255倍奢，使用2個(gè)字節(jié)。

VARCHAR類型儡司，雖然節(jié)約了空間娱挨，但由于行是變長的余指，在UPDATE時(shí)可能會使行變得更長捕犬，這就導(dǎo)致額外的工作；如果行變長后酵镜，頁內(nèi)沒有更多空間可以存儲碉碉，這種情況下，就需要拆分片段（MyISAM）或分裂頁（InnoDB）來處理淮韭。

VARCHAR類型垢粮，適用情況，列最大長度比平均長度大很多靠粪，且很少更新蜡吧，因?yàn)樗槠瑢⒉皇菃栴}。

CHAR類型占键，是定長類型昔善，根據(jù)指定的字符串長度分配足夠的空間；CHAR類型適合于存儲很短的字符串畔乙，或者所有值都接近同一個(gè)長度君仆。例如，CHAR類型非常適合于存儲密碼的MD5值，因?yàn)镸D5是一個(gè)定長的值返咱。另一方面钥庇，CHAR類型也適合存儲經(jīng)常變更的數(shù)據(jù)，因?yàn)槎ㄩL的CHAR不容易產(chǎn)生碎片咖摹。對于非常短的列评姨，CHAR比VARCHAR在存儲空間上也更有效率。如萤晴，用CHAR(1)存儲只有Y和N的值参咙，如果用單字節(jié)字符集只需要一個(gè)字節(jié)，但VARCHAR(1)在單字節(jié)情況下則需要兩個(gè)字節(jié)硫眯，因?yàn)檫€有一個(gè)額外字節(jié)用于記錄長度蕴侧。

注意：字符串長度定義不是字節(jié)數(shù)，而是字符數(shù)两入；多字節(jié)字符集會需要更多的空間存儲單個(gè)字符净宵。

注意：字符集（Charset）：是一個(gè)系統(tǒng)支持的所有抽象字符的集合。字符是各種文字和符號的總稱裹纳，包括各國家文字择葡、標(biāo)點(diǎn)符號、圖形符號剃氧、數(shù)字等敏储。，常見字符集名稱：ASCII字符集朋鞍、GB2312字符集已添、BIG5字符集、 GB18030字符集滥酥、Unicode字符集等更舞；

注意：字符編碼（Character Encoding）：是一套法則，使用該法則能夠?qū)ψ匀徽Z言的字符的一個(gè)集合（如字母表或音節(jié)表）坎吻，與其他東西的一個(gè)集合（如號碼或電脈沖）進(jìn)行配對缆蝉。即在符號集合與數(shù)字系統(tǒng)之間建立對應(yīng)關(guān)系，它是信息處理的一項(xiàng)基本技術(shù)瘦真。通常人們用符號集合（一般情況下就是文字）來表達(dá)信息刊头。而以計(jì)算機(jī)為基礎(chǔ)的信息處理系統(tǒng)則是利用元件（硬件）不同狀態(tài)的組合來存儲和處理信息的。元件不同狀態(tài)的組合能代表數(shù)字系統(tǒng)的數(shù)字诸尽，因此字符編碼就是將符號轉(zhuǎn)換為計(jì)算機(jī)可以接受的數(shù)字系統(tǒng)的數(shù)原杂，稱為數(shù)字代碼。UTF8是編碼方法弦讽；

注意：慷慨是不明智的污尉，比如膀哲，使用VARCHAR(5)或VARCHAR(100)存儲“hello”，空間開銷一樣被碗，因?yàn)閂ARCHAR類型是可變長的某宪，但因?yàn)镸ySQL通常會分配固定大小的內(nèi)存塊來存儲內(nèi)部值（意思是定義的長100>10，分配的內(nèi)存塊就大锐朴？）兴喂，尤其是使用內(nèi)存臨時(shí)表進(jìn)行排序或操作時(shí)會特別糟糕。在利用磁盤臨時(shí)表進(jìn)行排序時(shí)也同樣糟糕焚志。

BLOB和TEXT類型衣迷，BLOB以二進(jìn)制存儲（1、0數(shù)據(jù)流酱酬，搞硬件編程的比較熟悉）壶谒，TEXT以字符形式存儲，都是用來存儲很大數(shù)據(jù)的類型膳沽，注意汗菜，他們也是字符串類型，只是存儲形式不同挑社。

BLOB和TEXT類型陨界，在MySQL中會被作為對象處理，盡量避免使用痛阻。

使用枚舉（ENUM）代替字符串類型菌瘪，枚舉會將列的長度壓縮到1個(gè)或2個(gè)字節(jié)，內(nèi)部都保存為整數(shù)阱当，而在表的.frm文件中保存“數(shù)字－字符串”的映射關(guān)系查找表俏扩。

注意：避免使用數(shù)字作為枚舉常量，比如 enum('1', '2', '3')斗这，如果要枚舉的真是數(shù)字动猬，不如直接使用 TINYINT 來得直接，正確的使用情況類似于 enum('private', 'group', 'public')表箭。

注意：枚舉類型列排序時(shí)使用的是映射關(guān)系中的整數(shù)值，而不是按枚舉的字符串排序钮莲。所以最好的辦法是定義時(shí)就先排好序免钻，如上面的例子，應(yīng)寫為 column_type enum('group', 'private', 'public') not null default 'private'崔拥。

注意：需要保證枚舉列的字符串值能夠確定极舔，否則未來要擴(kuò)展類別，就需要使用 ALTER TABLE链瓦。

注意：因?yàn)槊杜e保存為整數(shù)拆魏，因此查找時(shí)必須轉(zhuǎn)換為字符串盯桦，所以枚舉在執(zhí)行查找時(shí)有一定開銷，尤其是當(dāng)前表的枚舉列與其他表相同的VARCHAR列或CHAR列關(guān)聯(lián)查詢時(shí)渤刃，會較不使用枚舉更慢拥峦。例如 關(guān)聯(lián)條件為 ON A.ENUM_TYPE = B.CHAR_TYPE 時(shí)。但什么情況都需要一個(gè)平衡卖子，使用枚舉可以縮減表的大小略号，可能會縮減30%；當(dāng)然關(guān)聯(lián)后雖然慢洋闽，但可能會節(jié)省I/O玄柠。

==================================================================

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

MySQL提供許多類型保存日期和時(shí)間，最小粒度為秒诫舅。具體看看

DATETIME羽利，存儲范圍1001年~9999年，封裝格式為 YYYYMMDDHHMMSS刊懈，與時(shí)區(qū)無關(guān)铐伴，8個(gè)字節(jié)（64位？）俏讹，使用ANSI標(biāo)準(zhǔn)定義的日期和時(shí)間表示方法顯示当宴，如“2014-01-11 22:05:33”，注意這是顯示泽疆。

TIMESTAMP户矢，時(shí)間戳，記錄了一個(gè)累計(jì)秒數(shù)殉疼，從1970年1月1日午夜（格林尼治標(biāo)準(zhǔn)時(shí)間）以來梯浪。4個(gè)字節(jié)，與UNIXTIME相同瓢娜。比DATETIME小很多了挂洛，最大記錄從 1970年~2038年的時(shí)間。轉(zhuǎn)換：FROM_UNIXTIME() 將時(shí)間戳轉(zhuǎn)換為日期眠砾，UNIX_TIMESTAMP() 將日期轉(zhuǎn)換為時(shí)間戳虏劲。

TIMESTAMP值與時(shí)區(qū)有關(guān)，DATETIME與時(shí)區(qū)無關(guān)褒颈，DATETIME記錄的是客戶端提交到數(shù)據(jù)庫的當(dāng)?shù)貢r(shí)間字符串柒巫。如果提交數(shù)據(jù)時(shí)不指定TIMESTAMP的值，則MySQL默認(rèn)提供當(dāng)前時(shí)間賦值谷丸，且默認(rèn)NOT NULL堡掏。

注意：以上優(yōu)勢，建議使用 TIMESTAMP刨疼，而不是 DATETIME泉唁，2038年以后怎么辦鹅龄？

==================================================================

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

主要有 BIT 和 SET，避免使用亭畜。

==================================================================

1.1.6 選擇標(biāo)識符（identifier）

標(biāo)識列的數(shù)據(jù)類型扮休，最好選擇整數(shù)，且符合上面的講的原則贱案，能TINYINT的不INT肛炮；不要選用ENUM、SET宝踪、CHAR侨糟、VARCHAR，除非沒有辦法瘩燥。

另外秕重，標(biāo)識列一般還與外鍵有關(guān)，所以記住厉膀，標(biāo)識列不管在主表還是關(guān)聯(lián)表中溶耘，選擇的數(shù)據(jù)類型要完全一致，精確匹配服鹅，像 INT 和 UNSINGED INT 都會產(chǎn)生性能問題凳兵，即使沒有性能問題，也可能導(dǎo)致難以發(fā)現(xiàn)的錯(cuò)誤企软。因?yàn)槿绻皇蔷_匹配庐扫，MySQL需要進(jìn)行隱式類型轉(zhuǎn)換。

標(biāo)識列不能使用字符型的原因仗哨，慢形庭，消耗空間，對于MyISAM厌漂，字符型要使用壓縮索引萨醒，導(dǎo)致查詢慢6倍。且對于使用隨機(jī)生成的字符串更要注意苇倡，如使用MD5()富纸、SHA1()、UUID()雏节，這些新產(chǎn)生的值會分布在很大的空間范圍內(nèi)胜嗓，使得INSERT和SELECT都很慢，可能分布于不同的內(nèi)存和磁盤不同位置钩乍，不會相鄰，來回掃描怔锌，導(dǎo)致整個(gè)數(shù)據(jù)集都成為熱數(shù)據(jù)寥粹。

==================================================================

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

兩個(gè)例子变过，低于秒級的存儲，IPv4的存儲涝涤，人們經(jīng)常錯(cuò)誤使用 VARCHAR(15)存儲IP地址媚狰，實(shí)際應(yīng)使用32位 UNSIGNED INT存儲，因?yàn)镮P地址本身就是整數(shù)阔拳，小數(shù)點(diǎn)只是為了顯示崭孤，因此應(yīng)利用 MySQL提供的 INET_ATON()和INET_NTOA()切換。

==================================================================

1.2 MySQL Schema 設(shè)計(jì)中的陷阱

太多的列（上千個(gè)）糊肠、太多的關(guān)聯(lián)（單個(gè)查詢不要超過12個(gè)表）辨宠、全能的枚舉、變相的枚舉货裹、非此發(fā)明的NULL（為避免不用NULL嗤形，不要亂給值如0000-00-00 00:00:00，給個(gè)0就好了）弧圆。

==================================================================

1.3 范式和反范式

一般都是先進(jìn)行范式化赋兵，然后根據(jù)需要進(jìn)行反范式化，取各自的有點(diǎn)進(jìn)行折中搔预。

范式的優(yōu)點(diǎn)：表小霹期、不重復(fù)、更新快拯田；

范式的缺點(diǎn)：純粹范式历造，查詢基本都需要關(guān)聯(lián)，代價(jià)昂貴勿锅。

反范式的優(yōu)點(diǎn)：反范式恰當(dāng)?shù)脑捙聊ぃ恍枰P(guān)聯(lián)，快溢十！

反范式的缺點(diǎn)：更新關(guān)聯(lián)數(shù)據(jù)的難處垮刹。

混用：實(shí)際中是混用，部分的范式化schema张弛、緩存表荒典、以及其他技巧，貌似是除了有經(jīng)驗(yàn)可以預(yù)先處理外吞鸭，邊開發(fā)可以邊改動(dòng)表的結(jié)構(gòu)寺董，將范式化的某些列，緩存到需要的主表中刻剥。

==================================================================

1.4 緩存表和匯總表（重要Ｕ诳А！Ｔ炻病）

緩存表：表示存儲那些可以比較簡單從schema其他表獲取數(shù)據(jù)的表御吞，而這樣的數(shù)據(jù)每次獲取的速度又比較慢麦箍，且在邏輯上有冗余的數(shù)據(jù)。緩存表需要開發(fā)者決定是實(shí)時(shí)維護(hù)還是定期重建陶珠，定期重建不僅節(jié)省資源挟裂，還可以保持表不會有很多碎片。定期重建注意使用“影子表”保持原表的可用性揍诽。

匯總表：保存的是使用 GROUP BY 語句聚合數(shù)據(jù)的表诀蓉，例如，數(shù)據(jù)不是邏輯上冗余的暑脆。

物化視圖：常被認(rèn)為是一種功能渠啤，MySQL不提供這一功能；但實(shí)際上饵筑，物化視圖指的就是緩存表埃篓，以及與之相關(guān)的一系列解決方案。使用 Justin Swanhart 開發(fā)的 Flexviews 可以實(shí)現(xiàn)自己的物化視圖根资。實(shí)際使用中架专，只要你告訴 Flexviews 你希望從源數(shù)據(jù)庫或表提前信息的 Select 語句，F(xiàn)lexviews自己將其轉(zhuǎn)換玄帕，獲取結(jié)果存儲下來部脚。開發(fā)者只需要面向 Flexviews查詢就行了，再不用查詢 數(shù)據(jù)庫裤纹。Flexviews能做到這樣委刘，是因?yàn)?Flexviews 基于行的二進(jìn)制日志更新數(shù)據(jù)，他的提前對象是數(shù)據(jù)庫日志鹰椒。

計(jì)數(shù)器表：創(chuàng)建獨(dú)立計(jì)數(shù)器表記錄網(wǎng)站訪問量锡移、用戶朋友數(shù)字、文件下載次數(shù)等漆际，是個(gè)好主意淆珊，這種表小且快。但有一個(gè)問題奸汇，如果是InnoDB會執(zhí)行行級鎖施符，如果是MyISAM會執(zhí)行表級鎖，會造成高并發(fā)串行執(zhí)行問題擂找。書中列出了 使用 InnoDB 會用到的技巧（隨機(jī)槽戳吝、INSERT INTO...ON DUPLICATE KEY UPDATE）。

問題：以上內(nèi)容贯涎，都在增加讀操作速度听哭，但會造成寫操作變慢，甚至增加讀操作和寫操作的開發(fā)難度琼了。

==================================================================

1.5 加快 ALTER TABLE 操作的速度

本節(jié)講到兩個(gè)重要技術(shù)，來通過速度镀琉，一是修改 .frm 文件谷醉，二是對于MyISAM表先載入數(shù)據(jù)，再創(chuàng)建索引了嚎。

==================================================================

1.6 總結(jié)