2017年8月7日星期一
老男孩IT教育每日簡報
每個DBA都需要知道的10個提升MySQL性能的基本技巧
從工作量分析到索引的三條規(guī)則染苛，這些專家見解肯定會讓您的MySQL服務(wù)器尖叫逊躁。
在所有的關(guān)系數(shù)據(jù)庫中，MySQL已經(jīng)被證明了完全是一頭野獸，只要通知停止運(yùn)行就絕對不會讓你多等一秒鐘，使你的應(yīng)用置于困境之中，你的工作也承受極大的風(fēng)險疼进。
不過事實(shí)是，普通的錯誤都在MySQL性能錯誤的射程之內(nèi)秧廉。所以為了使你的MySQL服務(wù)器能夠高速運(yùn)轉(zhuǎn)伞广，提供穩(wěn)定且持續(xù)的服務(wù)，消除這些錯誤是非常有必要的疼电，但是這可能常常會被你的繁忙工作或配置陷阱微妙地遮蔽了嚼锄。
幸運(yùn)的是，許多MySQL性能問題其實(shí)都有相似的解決辦法蔽豺，發(fā)現(xiàn)并解決問題区丑，然后你的MySQL用起來就順手多啦。
接下來就和大家分享一下10個使MySQL性能提升的小技巧。
MySQL性能提升小技巧1：對你的工作進(jìn)行配置
想要了解你的服務(wù)器到底如何支配時間沧侥，最好的辦法就是對服務(wù)器的工作進(jìn)行配置可霎。通過配置你的服務(wù)器，你可以expose最昂貴的query來為將來的調(diào)優(yōu)做準(zhǔn)備正什。從這個角度啥纸，時間就是最重要的衡量標(biāo)準(zhǔn)，因?yàn)楫?dāng)你對你的服務(wù)器發(fā)起一個query之后婴氮，除了它到底多塊的完成之外你不會關(guān)心任何其他事斯棒。
配置你的工作文件的最優(yōu)解就是MySQL Enterprise Monitor的query分析儀或者Percona Toolkit的pt-query-digest。這些工具可以幫助你捕捉你的服務(wù)器正在執(zhí)行的詢問以及返回按響應(yīng)時間遞減順序排序的任務(wù)表主经，它還會持續(xù)不斷地把最昂貴荣暮、費(fèi)時的任務(wù)更新在最上方，這樣你就能知道你的精力應(yīng)該更加集中在什么地方了罩驻。
工作文件配置工具會把相似的詢問分在一個組穗酥，你可以很方便地查看低速運(yùn)行或者是告訴運(yùn)行但是多次進(jìn)行的詢問。
MySQL性能提升小技巧2： 深入理解四個基本資源
一個數(shù)據(jù)庫服務(wù)器需要以下4種資源才能正常運(yùn)轉(zhuǎn)：CPU惠遏，內(nèi)存砾跃，硬盤以及網(wǎng)絡(luò)。如果這其中任何一種性能不足节吮，運(yùn)轉(zhuǎn)不力或者超負(fù)荷運(yùn)轉(zhuǎn)的話抽高，那么數(shù)據(jù)庫服務(wù)器就非常可能表現(xiàn)不佳透绩。
理解基礎(chǔ)的資源是非常重要是以下兩個層面：選擇硬件以及疑難問題解答翘骂。
當(dāng)為MySQL選擇硬件的時候，確保所有的組件都表現(xiàn)良好帚豪。同樣重要的是把它們進(jìn)行合理的配置碳竟。大多是時候，一些機(jī)構(gòu)會選擇高轉(zhuǎn)速的CPU以及硬盤狸臣，但是他們通常來講內(nèi)存都不夠用莹桅。在某些情況下，按照數(shù)量級增加內(nèi)存是提升性能最廉價的辦法烛亦，尤其是工作負(fù)載是綁定磁盤的情況下诈泼。這聽起來可能違反常識，但是在許多情況下此洲，硬盤都是過度使用的厂汗，因?yàn)闆]有足夠的內(nèi)存來儲存數(shù)據(jù)工作集委粉。
另外一個平衡的典范當(dāng)屬CPU呜师。在大多數(shù)情況下，MySQL使用高轉(zhuǎn)速的CPU會運(yùn)轉(zhuǎn)得很好贾节，因?yàn)槊恳粋€詢問都是在單線程中運(yùn)行而不能在CPU之間并行汁汗。
當(dāng)要解答疑難問題的時候衷畦，請了解清楚所有資源的性能和使用情況，用你審慎的目光來判斷它們到底是本來就性能差勁還是因?yàn)槌休d了過多的任務(wù)知牌。這個姿勢應(yīng)該能讓你解決問題快一些祈争。
MySQL性能提升小技巧3:別吧MySQL當(dāng)成一個隊(duì)列使
隊(duì)列和隊(duì)列訪問模式可以在你完全沒有察覺的情況下偷偷進(jìn)入你的應(yīng)用。舉個栗子角寸，如果你設(shè)置了某個項(xiàng)的狀態(tài)菩混，以便某個特定的工作進(jìn)程在調(diào)用它之前可以聲明它，那么你就在無意中創(chuàng)建了一個隊(duì)列扁藕。把郵件標(biāo)記為未發(fā)送沮峡，發(fā)送它們，然后它們被標(biāo)記為已發(fā)送就是一個很好理解的栗子亿柑。
隊(duì)列會產(chǎn)生問題主要有2個原因：它們連續(xù)運(yùn)轉(zhuǎn)你工作邢疙，防止它們并行，那么這通常就會產(chǎn)生一個表格望薄，里面包含了進(jìn)程中的工作還有很久以前已完成工作的歷史數(shù)據(jù)疟游。這不僅會使你的應(yīng)用產(chǎn)生延遲而且也會給MySQL增加不必要的負(fù)荷。
MySQL性能提升小技巧4: 花費(fèi)最少的結(jié)果先過濾
優(yōu)化MySQL性能最好的辦法就是先完成廉價痕支、不確定的工作颁虐，然后在最小的結(jié)果數(shù)據(jù)集中完成艱難、準(zhǔn)確的工作采转。
例如你要通過一個給定的地理位置半徑來找到你想要的東西聪廉。在大多數(shù)程序員的工具箱里，他們首先會想到的一定是計(jì)算球面上的距離的大圓公式(Haversine)故慈。但是用這個公式的問題在于可能要用到很多三角方面的運(yùn)算板熊，這對CPU的要求是非常高的。大圓的計(jì)算往往運(yùn)行緩慢察绷，使得機(jī)器的CPU使用率飆升干签。
在你開始應(yīng)用大圓公式之前，在總集當(dāng)中將你的記錄減少成最小的子集拆撼，并把結(jié)果集整合成一個確切的圓容劳。一個包含圓（確切或不確切的）的正方形是解決這個問題最簡單的方法。這樣的話闸度，正方形之外的一切都不回碰上這些成本昂貴的三角函數(shù)竭贩。
MySQL性能提升小技巧5:了解兩種伸縮性死亡陷阱
伸縮性其實(shí)并不像你想象的那樣捉摸不定。實(shí)際上在數(shù)學(xué)當(dāng)中已經(jīng)有非常明確的將伸縮性表示為方程式的定義莺禁。這些方程式突出展現(xiàn)了為什么系統(tǒng)并沒有如預(yù)期那樣的良好伸縮留量。
參見通用可擴(kuò)展法（Universal Scalability Law）—非常清晰地解釋和量化了一個系統(tǒng)的伸縮性特性。它從兩個基礎(chǔ)成本方面對伸縮性問題進(jìn)行了闡釋：序列化（serialization）和串?dāng)_（crosstalk）。
多進(jìn)程必須為在伸縮性上具有固有限制的序列化停止工作楼熄。相似地忆绰，如果多個進(jìn)程必須時時刻刻互相交流才能配合他們的工作的話，他們就是在互相限制可岂。
避免序列化以及串?dāng)_错敢，你的應(yīng)用伸縮性將會大大提升。那么在對MySQL來說意味著什么呢缕粹？因情況而異稚茅，但有些示例可以避免對行進(jìn)行排它鎖定。關(guān)于隊(duì)列平斩，參見技巧3峰锁，往往會因?yàn)殛?duì)列伸縮性就變得很差。
MySQL性能提升小技巧6:不要太關(guān)注配置
DBA常常耗費(fèi)大量的時間來調(diào)整配置双戳。換來的結(jié)果有時卻是傷害而不是大的提升虹蒋。我看到過許多的最優(yōu)化的服務(wù)器時不時就崩潰，內(nèi)存不足飒货，而且在工作負(fù)載稍微多一點(diǎn)的時候就表現(xiàn)很差魄衅。
MySQL上搭載的默認(rèn)配置是一刀切并且嚴(yán)重過時的，但是它們也不需要完全重新配置塘辅。只要把最基礎(chǔ)的設(shè)置正確晃虫，有需要的話再做小幅調(diào)整即可。在大多數(shù)情況下扣墩，通過正確設(shè)置大約10個選項(xiàng)哲银，你可以獲得服務(wù)器峰值性能的95%。其他無法應(yīng)用此方法的的情況的話應(yīng)該是非常特殊的情況呻惕，所以就不用去管他了荆责。
在大多數(shù)情況下，服務(wù)器“轉(zhuǎn)換”工具是不推薦的亚脆，因?yàn)樗鼈兂３幸恍┰谔囟ㄇ闆r下并不適用的規(guī)則做院。有些甚至存在危險且不準(zhǔn)確編碼—例如緩存命中率和內(nèi)存消耗公式。這些都是不對的濒持，而且隨這時代的進(jìn)步他們變得更加地不對键耕。
MySQL性能提升小技巧7: 小心分頁詢問
分頁應(yīng)用常常會把服務(wù)器搞癱瘓。在向你展示結(jié)果的頁面當(dāng)中柑营，有翻到下一頁的鏈接屈雄，這類應(yīng)用通常不以索引的方式進(jìn)行分類整理，然后他們使用一種 LIMIT和 offset使得服務(wù)器做大量的工作生成官套，然后丟棄行酒奶。
優(yōu)化選項(xiàng)在用戶界面常常自己就能找到蓖议。而不是展示確切的頁數(shù)結(jié)果以及每個頁面的單獨(dú)鏈接，只展示下一列的鏈接就好讥蟆。你也可以防止大家翻到太后面的頁數(shù)。
從質(zhì)詢方面來看纺阔，你可以比你想要的多選取一行瘸彤，然后當(dāng)你點(diǎn)擊“下一頁”鏈接的時候，你可以指定最后一行作為下一組結(jié)果的起點(diǎn)笛钝，而不是使用帶offset的 LIMIT质况。舉個栗子，當(dāng)用戶在查看120行中的第101行時玻靡，你會同時select第121行结榄；為了遞交下一頁，你可以向服務(wù)器詢問第121行或者超過121的行囤捻，限定在21臼朗。
MySQL性能提升小技巧8: 及時保存數(shù)據(jù)，審慎警告
監(jiān)管和預(yù)警是必不可少的蝎土，但是典型的監(jiān)控系統(tǒng)到底怎么了视哑？它開始發(fā)送一些錯誤的手勢，然后系統(tǒng)管理員就設(shè)置了垃圾郵件過濾規(guī)則來停止這些煩擾誊涯。然后很快你的監(jiān)管系統(tǒng)就會完全癱瘓挡毅。
我傾向于從兩個方面來看待監(jiān)管；獲取指標(biāo)以及發(fā)出預(yù)警暴构。盡可能的獲取并保存指標(biāo)是非常重要的跪呈，因?yàn)楫?dāng)你想要知道系統(tǒng)到底改變了什么的時候你會很慶幸你當(dāng)初保存了它們。有一天會突然出現(xiàn)一個很奇怪的錯誤取逾，然后你就會很高興你有能力指出服務(wù)器的工作負(fù)載中的一段然后展示這個改變耗绿。
相比之下，警告就可能有點(diǎn)多了砾隅。人們常常會對緩存命中率或者短期內(nèi)每秒所創(chuàng)建的表格發(fā)出警告缭乘。問題是對這種緩存命中率并沒有一個合適的閾值。正確的閾值并不是隨著服務(wù)器的不同而變化琉用，而是隨著你工作負(fù)載的不同堕绩，每一個小時都是不一樣的。
這就導(dǎo)致邑时，警告只能有節(jié)制地并且只能在預(yù)示一個具體奴紧、可操作的問題時才是可行的。一個低緩存命中率并不是可操作的問題晶丘，而且他也不指向一個實(shí)在的問題黍氮，但對連接嘗試沒有響應(yīng)的服務(wù)器才是真正需要結(jié)局的問題唐含。
MySQL性能提升小技巧9: 學(xué)習(xí)index的三條法則
Index可能是數(shù)據(jù)庫匯總最難理解的概念，因?yàn)楹苋菀拙蛯ndex到底如何工作以及服務(wù)器如何使用它們感到困惑沫浆。確實(shí)要花些力氣才能真正理解它到底是怎么回事捷枯。
Index經(jīng)過適當(dāng)設(shè)計(jì)后，主要在數(shù)據(jù)庫服務(wù)器中提供如下三種服務(wù)：
Index讓服務(wù)器查找相鄰行的集合而不是單獨(dú)的行专执。許多人可能會認(rèn)為index的作用就是為了查找單獨(dú)的行淮捆，但是查找單獨(dú)的行會導(dǎo)致混亂的硬盤操作，速度就會變慢本股。而且查找行的集合要容易多了攀痊，所有或者說大多數(shù)都比一次只查找一個行要有趣多了。
Index通過按照閱讀喜好進(jìn)行排列省去了整理的過程拄显。整理是耗費(fèi)巨大的苟径。按照自己的喜好進(jìn)行閱讀效率也更高。
Index完全滿足了服務(wù)器的詢問躬审，根本就不需要再連接表格棘街。這是眾所周知的覆蓋索引或僅索引查詢。
如果你可以定義自己的索引和詢問來利用這三個機(jī)會承边，你就可以使查詢速度快幾個數(shù)量級蹬碧。
MySQL性能提升小技巧10: 利用同行的專業(yè)知識
不要一個人冒險。如果你對一個問題感到煩惱炒刁，同時也在做一些對你來說有邏輯且隔離的解決方式恩沽，那很好。這在20次中可能會有19次是有效的翔始。但是剩下的1次罗心，你可能會掉進(jìn)兔子洞里，會非常費(fèi)時費(fèi)力城瞎，這完全是因?yàn)槟悻F(xiàn)在所做的努力只是看起來可能是有意義的渤闷。
建立與MySQL相關(guān)的資源網(wǎng)絡(luò)，這超越了工具和故障排除指南脖镀。有一些非常有知識的人潛伏在郵件列表飒箭、論壇、問答網(wǎng)站上蜒灰，等等弦蹂。會議、展會和本地用戶團(tuán)體活動都提供了寶貴的機(jī)會强窖，讓你能與那些在緊要關(guān)頭幫助你的同行建立聯(lián)系凸椿。
每日一句；人生是一條上下波動的曲線翅溺，有時候低脑漫，有時候高髓抑。低的時候你應(yīng)該高興，因?yàn)楹芸炀鸵呦蚋咛帯?/p>
