上一篇文章我們一起探討了MySQL優(yōu)化的存儲引擎選擇，配置參數(shù)優(yōu)化驮捍，索引優(yōu)化等，今天小馬就繼續(xù)接下來的篇幅脚曾，來嘮嗑一下MySQL優(yōu)化剩下的一些招式东且。
表分區(qū)
談起分表可能有些小伙伴還可能沒用上，但對于表分區(qū)本讥，大部分人應(yīng)該并不陌生珊泳。我們先來看百科多分區(qū)表的定義：
分區(qū)表是將大表的數(shù)據(jù)分成稱為分區(qū)的許多小的子集鲁冯，類型有FAT16,FAT32,NTFS,exFAT,Ext2/3/4,HFS+,VMFS。另外色查，分區(qū)表的種類劃分主要有：range（范圍）薯演、list（列表）和hash（散列）分區(qū)。劃分依據(jù)主要是根據(jù)其表內(nèi)部屬性秧了。同時跨扮，分區(qū)表可以創(chuàng)建其獨特的分區(qū)索引。倘若硬盤丟失了分區(qū)表验毡，數(shù)據(jù)就無法按順序讀取和寫入衡创，導(dǎo)致無法操作。
來看一則關(guān)于表分區(qū)的介紹晶通，小馬覺得非常易懂璃氢。
那么MySQL該如何實現(xiàn)表分區(qū)？先來看MySQL支持的分區(qū)的類型有哪些狮辽。
1一也、RANGE分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)喉脖。常用的比如按某一時間范圍分區(qū)椰苟；
2、LIST分區(qū)：類似于按RANGE分區(qū)动看，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇尊剔；小馬比較少用。
3菱皆、HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)须误，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包含MySQL 中有效的仇轻、產(chǎn)生非負整數(shù)值的任何表達式京痢。
4、KEY分區(qū)：類似于按HASH分區(qū)篷店，區(qū)別在于KEY分區(qū)只支持計算一列或多列祭椰，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值疲陕。
值得一提的是方淤，在以往的經(jīng)驗中通常對用來分區(qū)的字段，是整形的則使用hash分區(qū)蹄殃，如PARTITION BY HASH (uId) PARTITIONS 10携茂；是字符串型則使用KEY分區(qū)，如PARTITION BY KEY (openid) PARTITIONS 11诅岩。一般情況下如果對字符串型的列進行hash分區(qū)的話會直接報錯讳苦。
又到了埋彩蛋的時間了带膜，這里注意到KEY分區(qū)為什么是11呢不是10呢？以為有些大神分析鸳谜，當key分區(qū)為合數(shù)的時候膝藕，會出現(xiàn)散列分布不均勻的問題，所以最好建議是質(zhì)數(shù)分區(qū)數(shù)咐扭。
好了芭挽，至于具體如何實現(xiàn)的話，創(chuàng)建表分區(qū)語句可以網(wǎng)上一找一大把草描，這里不再贅述览绿。有一點非常有趣的是，MySQL用于表分區(qū)的字段列/鍵必須在該表的所有唯一索引中（含主鍵）穗慕。這句話什么意思呢饿敲？就是用在分區(qū)上的鍵，如果表有主鍵或者唯一索引存在逛绵，則它必須在主鍵和唯一索引里怀各，是的，你沒看錯术浪，兩個都要在瓢对。所以這點在創(chuàng)建表結(jié)構(gòu)的時候要兼顧考慮一下。參看兩個例子胰苏。
為什么呢硕蛹？因為分區(qū)后索引結(jié)構(gòu)發(fā)生變化，MySQL不能保證之前索引的唯一硕并，所以要求你妥協(xié)法焰，分區(qū)鍵要放唯一索引里組合唯一。這點因為結(jié)構(gòu)有差異和Oracel略有不同倔毙。
小馬認為分區(qū)只是基于在物理結(jié)構(gòu)上的埃仪，畢竟有局限性，比如對伸縮擴展比較麻煩陕赃。那么接下來看基于邏輯上的分表操作卵蛉。這種方式在大型架構(gòu)和分布式中尤為受歡迎，因為分表也便于后續(xù)作一些高可用動作么库。話又說回來傻丝，當系統(tǒng)體量沒有那么大的情況下，就不要分表了诉儒，反而影響性能成為累贅桑滩。
分表
分表的法子比較多樣靈活，但大致可分為兩種。
1运准、利用merge存儲引擎來實現(xiàn)分表
此方法的好處就是只需要處理好表結(jié)構(gòu)和插入邏輯，對于查詢邏輯是透明的缭受，雖然被分成了N個表但查詢時會自動關(guān)聯(lián)胁澳。語句場景參考如下：
2、散列某字段為表索引分表
將表字段其中的某一個唯一標志如openid來哈希作為表前綴來均勻分布米者，直接將表分為N個韭畸。如將user表分10個表，假設(shè)根據(jù)字段qq字段分表蔓搞，則將得到user_(qq%10)這樣的表名后綴索引0-9十個表胰丁，于是邏輯代碼根據(jù)(qq%10)來決定將操作哪個表。這個法子最簡單好用喂分。這里qq號剛好是整形直接取模哈希锦庸，那如果是openid字符串呢該怎么處理？這點留給觀者思考蒲祈。
這里的分表自增主鍵原則上是不受影響的甘萧，因為各自代碼邏輯也是根據(jù)哈希鍵分開來的，但這里建議使用分布式的ID為好梆掸，比如雪花算法扬卷。
題外話，這里既然提到哈希酸钦，那順帶思考下怪得，哈希沖突的解決方式有哪些呢？比如再哈希法卑硫，然后呢徒恋？
分表后如何支持 全表的搜索和后臺的分頁查詢。參考：
主從復(fù)制拔恰，讀寫分離因谎，負載均衡
只要服務(wù)器安裝了mysql proxy或Ameoba軟件就可以實現(xiàn)讀寫分離和負載均衡。小馬認為颜懊，這可以認為這是服務(wù)器實現(xiàn)層面的财岔，也就是中間件自己判斷是讀還是寫自己找到配置的機器，以及自己計算負載自己實現(xiàn)均衡河爹。但在TP框架主從DB配置中似乎主從讀寫分離的實現(xiàn)是從代碼層面實現(xiàn)的匠璧，是否有印象呢？
編寫合理的SQL
這點我想也是老生常談了咸这。就是不要寫查詢效率低的慢查詢SQL語句夷恍。關(guān)于是否有慢查詢這點可以配置打開慢查詢?nèi)罩静⒉榭矗@里不贅述媳维。
關(guān)于效率低下的一些經(jīng)典行為：
1酿雪、盡量避免where語句中對字段進行null判斷（表設(shè)計的時候就盡量不要NULL值）遏暴，否則將放棄使用索引而進行全表掃描，如：select id from where num is null指黎；
2朋凉、最好不要select *，會導(dǎo)致全表掃描醋安。能不用join表連接就不用杂彭，非要用記得join on字段加索引；
3吓揪、查詢有限數(shù)據(jù)請加limit亲怠；盡量給表設(shè)置主鍵，盡管已經(jīng)有了唯一索引柠辞。
4团秽、盡量避免wherer語句后使用！=或者<>操作符或者or钾腺，否則將導(dǎo)致放棄索引而進行全表掃描徙垫，這點可以參看導(dǎo)致索引失效的例子。or語句可以用uion語句來替換放棒。
5姻报、in和not in也要慎用，否則會進行全表查找间螟，對于連續(xù)的數(shù)值吴旋，能用between就不要用in。很多時候用exist是代替in是一個好的選擇厢破。select num from a where num in (select num from b)荣瑟；用下面語句進行替換：select num from a where exists(select 1 from b where num =a.num)；
6摩泪、應(yīng)避免在where子句中對字段進行表達式操作或者對字段進行函數(shù)操作笆焰，這樣導(dǎo)致引擎放棄使用索引而進行全表查詢，比如：select id from t where num/2=100见坑；
7嚷掠、如果開啟事務(wù)請檢查異常或者出錯的情況下是否回滾或者提交了事務(wù)荞驴。
MySQL優(yōu)化就暫告一段落了不皆，歡迎補充指點。
彩蛋時間：
分庫分表后如何解決分頁問題熊楼？
設(shè)計的時候讓查詢的條件都落在一個表上霹娄，無法滿足加一個滿足條件的冗余表；搞個全量索引二次查詢；借助中間件犬耻，比如mycat（根據(jù)sql找到需要的庫表集合查詢結(jié)果踩晶，跟查詢單表一樣）。
三范式
什么是三大范式：
第一范式：當關(guān)系模式R的所有屬性都不能在分解為更基本的數(shù)據(jù)單位時香追，稱R是滿足第一范式的合瓢，簡記為1NF。滿足第一范式是關(guān)系模式規(guī)范化的最低要求透典，否則，將有很多基本操作在這樣的關(guān)系模式中實現(xiàn)不了顿苇。
第二范式：如果關(guān)系模式R滿足第一范式峭咒，并且R得所有非主屬性都完全依賴于R的每一個候選關(guān)鍵屬性，稱R滿足第二范式纪岁，簡記為2NF凑队。
第三范式：設(shè)R是一個滿足第一范式條件的關(guān)系模式，X是R的任意屬性集幔翰，如果X非傳遞依賴于R的任意一個候選關(guān)鍵字漩氨，稱R滿足第三范式，簡記為3NF.
三范式主要是針對數(shù)據(jù)庫 字段 設(shè)計而言遗增，為了好記叫惊，小馬歸納總結(jié)為：
1、字段值的原子性 ：學(xué)生ID? ? 姓名? ? 學(xué)院班級做修；? ? ?不符合：其中學(xué)院班級值非原子性 霍狰；正確：分開兩個字段存 學(xué)院 班級。
2饰及、字段不要冗余：學(xué)生ID? ? 宿舍ID? ? ?宿舍名蔗坯；? ? 不符合：其中宿舍ID和宿舍名冗余；正確： 宿舍名存另一個表燎含，用外鍵關(guān)聯(lián)來實現(xiàn)宾濒。
3、不能有值是與主鍵間接相關(guān)： 學(xué)號? ?姓名? ? 院系名稱? ? ?院系地址屏箍；? 不符合：其中院系地址是與主鍵間接相關(guān)的是與院系才是直接相關(guān)绘梦；正確：學(xué)號 姓名 院系ID外鍵 另一個表：院系ID 院系名稱 院系地址。
很多時候為了查詢效率等其他原因會犧牲一些范式規(guī)范铣除，比如第二范式谚咬，這也是允許的，并非必須嚴格遵循尚粘。
原創(chuàng)文章择卦，未經(jīng)允許請勿轉(zhuǎn)載。
