MySQL優化注意事項

jacklove原創: 2018-05-21 17:35:231555瀏覽

在操作資料庫的時候，需要我們對mysql進行最佳化處理，這篇講講有最佳化的注意事項。

第一點，硬體太老
硬體我們這裡主要從CPU、記憶體、磁碟三個方面來說下，還有一些因素比如網卡，機房網路等因為文章篇幅關係，就不一一介紹了，以後還有機會可以聊。
首先我們來看下MySQL對CPU的利用特性：
5.1可以利用4個核，5.5可以利用到24個核，5.6可以利用到64個核
例如MySQL5.6能用到48個CORE以上，跑得好的，64個CORE都能用到(48CORE-64CORE之間，官方公佈48個CORE，我實際測試能跑到64個CORE)。
MySQL 5.6 可以用到48 core
*MySQL 5.1 前最多可以用到4個核心 **
現在一般的生產環境伺服器，都是32CORE以上。
所以我這裡都推薦大家盡量得去用MySQL5.5或MySQL5.6, 除非你們公司的伺服器一直用的很老舊的伺服器，只有4個核，或1個核。
因為5.1以前(5.0一樣)都是在內部程式碼裡寫死了，是基於innobase的儲存引擎，資料庫對硬體的利用率較差。後面演進為了InnoDB引擎後，好多了。
每個連接一個是一個執行緒(非thread pool)，每個query只能使用到一個核心
另外，在MySQL每個query只能用到一個CPU。
Oracle裡面用平行SQL，並行查詢，這類功能在MySQL裡是不存在的。
無執行計劃快取（無SQL執行計劃預編譯）
其次，MySQL內部沒有SQL預編譯。因此不存在像Oracle記憶體結構裡的library cache（庫快取）這類結構體。所以，MySQL只有硬解析，不存在什麼軟解析，更不存在什麼軟軟解析。
MySQL隨著連線數上升會出現效能下降
這個也是MySQL的一個硬傷，但隨著MySQL的版本演進，還是出現了許多解決方法。
例如：官方推出的thread pool（線程池），簡稱TP。就是為了解決並發連線數過高的問題，不過這屬於MySQL額外的元件，官方的TP是需要額外花錢購買的。
另外，國內有個叫樓層鑫的，開發了一個OneSQL的中間件，也是解決類似問題的。
有Result緩存，但比較雞肋
MySQL裡也有類似Oracle裡的結果緩存，叫Query Cache，但屬於比較雞肋的功能，很少使用。
因為大部分實際的生產環境都是OLTP系統，有頻繁的更新修改操作，這個Query Cache用在資料頻繁更新修改的環境裡，會使MySQL的效能嚴重下降，因此，一般很少使用。
現在用MySQL，基本上都是用InnoDB儲存引擎，以前的MyISAM這些引擎也用得很少了。（什麼是儲存引擎？這個不知道的話，你可以gg了）
InnoDB引擎是完全沒有必要去開啟這個Query Cache的，因為本身就是一個事務型的儲存引擎，用InnoDB就是用它的事務處理能力，肯定會發生頻繁的資料更新和修改嘛。
再次來看下MySQL對記憶體利用特性
64位元作業系統的伺服器可利用記憶體((2^64-1)/1024/1024/1024)G
在高速並發環境，基本上是靠記憶體快取來減少對磁碟的IO衝擊
通常記憶體按實際數據的15%-20%規劃，如果特別熱的數據，需要考慮更大的比例來快取資料
這15%-20%的數據我們通常又叫做熱數據。（這也是通常的經驗值）
例如你評估出你這台MySQL資料總量大概在500G左右，那MySQL要給到的記憶體可能就是75G（5000.15），那你可能需要一台128G左右記憶體的伺服器。
另外有些業務還會存在特別熱、大量熱的數據（大大超出15%-20%這個區間，也是有可能的），例如：QQ農場。
相信大家都玩過以前那種偷菜的遊戲，QQ農場，開心農場之類的。（還有訂票的12306網站）。
這類業務在我們業界裡面都是屬於關注度很高的，這類業務的特點，數據熱的時候，基本100%都是熱數據，比如：QQ農場大家玩的時候，每天都上來玩的，每隔一會兒就上來偷把菜,很多人半夜起來上廁所起來都要偷一把菜。
所以這類業務的MySQL資料庫，記憶體配備還得加高。 15-20%還不夠。
總結：****一般的業務15%-20%來規劃熱數據，例如：用戶中心，訂單之類的常見業務。另外一些特殊點的業務，具體情況具體分析。
可以根據Query回應時間來做指導分配
我們在做這種大型線上架構-大型資料庫規劃設計的時候，
SQL查詢的回應時間也是一個非常重要的指標。
在這種大型系統裡面，要承載數百萬甚至千萬等級使用者同時在線上進行業務，SQL查詢（query）的回應時間是必須去嚴格把控，必須把你這套系統的Query回應時間控制在多少時間內。
例如我們的核心庫，我就要求Query的回應時間（平均回應）在30ms以下。超過30ms，我們就認為這個資料庫可能達到承載極限，需要擴充這個資料庫了。
另外，要對這個Query回應時間進行長期的指標監控。
這個是核心庫，如果另外一些不太重要的輔助庫，比如放日誌的庫，或者說一些性能要求本身不是太高的庫，我們可以放寬點這個Query響應時間，放寬到1秒或2秒內。
根據業務的重要等級程度來定這個Query響應時間的閥值。
這是一個很重要的指導思想，根據Query回應時間來規劃你的性能容量。
容量分兩種：效能容量和空間容量。空間容量很簡單，就是放多少SIZE數據，幾個T。
性能容量是更重要的，決定能否接住你的業務壓力和承載。
大家要記住：你如果要抗的業務是百萬級的活躍用戶，不是幾百個用戶的話，性能才是王道，性能上滿足業務的需求才是最重要的。
你功能再牛B，產品再好，性能抗不了，其他都是扯淡，幾百W人可能在幾秒鐘內就把你的整個系統和項目都搞掛掉，然後你們公司就抓瞎了。
苦心經營的用戶也會大量流失，損失就慘重了。
效能是基礎。性能能抗住，整個架構才有意義。性能抗不住，後面去考慮什麼高可用，這些都沒用。
MySQL對磁碟的利用特性
Binlog,redo log ,undo log順序IO
MySQL的IO類型多種多樣。
binlog，redolog，undolog，這些都是順序IO寫。
這一類東西沒太多必要放到SSD上，順序寫在傳統機械盤上也是很快的，放到SSD上有點暴殄天物，而且SSD存在寫損耗和寫壽命的問題，沒必要放到SSD上。放到傳統的SAS盤就夠用了。沒必要放SSD。
SSD用來放datafile。因為datafile上發生的IO大部分是隨機IO，SSD跑隨機IO是非常有優勢的。 SSD固態磁碟片傳統磁碟片SAS碟一起混合儲存。另外，備份盤也不要用SSD。
Datafile隨機IO和順序IO結合
順序IO永遠是更快的。在資料庫設計裡，決定你是不是牛B的DBA或牛B的架構師，就是看你能否把一個業務盡可能設計為順序IO，同時減少隨機IO。舉個例子：一個好友關係的業務，設計的時候希望一個query以順序IO把好友關係就拿出來，那麼怎麼設計呢？
那在MySQL的InnoDB裡面，我們可以利用InnoDB的一個特性：聚集索引表。（類似Oracle的IOT）。
利用這個特性，可以讓使用者的好友資料盡可能的聚集在一個page或多個相鄰的page。那讀的時候一個順序讀IO就能搞定了，效能大大提高。
好友關係表結構如下（前提表是InnoDB引擎）：
owner_id friend_id（好友id）
上面這樣的兩個欄位做一個主鍵，InnoDB的主鍵就是聚集索引，那讀這兩個字段肯定順序IO就能搞定。
以前有什麼資料庫設計的書上，總說到，每個表上必須添加一個自增的主鍵的規範，其實規範死的，應對是活的，我上面舉例的好友關係就沒有用自增的主鍵，而是具有業務屬性讀取又頻繁的兩個業務字段作主鍵，反而性能更好。
因此，大家學習，不要去死記這些書上的什麼規範和章程，而是應該真正學懂一個東西的原理，比如學好InnoDB的內部原理，然後在實際工作中，有原理的支撐，用原理去舉一反三。
InnoDB的原理是很大的一塊知識，需要日積月累的學習。大家可以多留意我的公眾號，陸續會有一些InnoDB的文章推出來。
OLTP業務更多的需要隨機IO
可以利用記憶體做緩存，從而減少隨機IO
OLAP業務更多需要順序IO
內存緩存作用不大
MySQL5.6之前是不支援修改page的，預設就是16K。
MySQL5.6以後可以改了，這個參數是innodb_page_size，但MySQL5.6也只能修改為8K或4K，不能調大，直到MySQL5.7以上才可以改大為32K或64K。
對OLAP系統來說，更大的page，對效能的提升會有所幫助，因為OLAP系統都是比較大的查詢，掃描的資料很多。
第二點：資料庫設計不好
例如用了很多的資料庫特性，像Trigger, 分區，非常多的預存程序、函數等等。
我們常說什麼，小而美，意思就是簡單才是最好的。你把資料庫的所有功能都用上了，資料庫的效能自然就會被拖慢，可能碰到的BUG，底層故障的幾率也就增加了。
所以大家要明白，一個好的資料庫專案設計，是小而美，精而簡的。另外，資料庫也只是整體專案的一部分，像是Trigger，儲存過程這些能實現的，在整體專案裡面肯定也可以用應用程式程式碼來完成。
所以，我們用MySQL，就是用它厲害的地方，例如：表、索引、事務這些，而不是要它所有的功能都得用上。
另外有一點，在MySQL5.6之前，生產環境的主函式庫裡面是不允許使用子查詢的。
MySQL5.6之前子查詢的效能特別差。（語法上是支援的，但SQL效能非常差）。
例如大家現在如果是用Oracle，想把Oracle遷移到MySQL上的話，建議大家用MySQL5.6版本，MySQL5.6對子查詢的支援和效能上都做了較大的改善。
MySQL5.6跑子查詢的效能會大大提升。
第三點：程式寫太爛

這個估計當過DBA的同學應該都是有體會的，中小型的公司，程式設計師水準參差不齊。
特別是碰到很多剛入行的程式設計師（剛畢業的），更有可能，這些剛入行的程式設計師手上還接了一些進度非常趕的需求。那這種環境下開發出來的程序，想不爛都很難了。
當然，這也不怪我們的程式設計師，不能怪罪他們。
造成我上述現象的原因，主要還是國內的開發環境，也沒辦法，開發需求迫切（產品天天催活），程式設計師忙於趕工（長期加班），只能忙與實現業務程序，根本沒時間去優化程序。
當然，在這種環境下，對我們DBA來說就是機會了。程式設計師寫出來的爛SQL，複雜SQL，造成系統緩慢甚至崩潰，然後我們DBA出馬，對這些爛SQL，慢SQL進行最佳化改造後，系統恢復正常，並且日益穩定。這也是很有成就，也會受到同事和領導者尊重的一件事。
同時，DBA們也可以加強程式設計師的培訓，加強他們快速寫出好SQL的能力。讓他們花較少的時間，也能寫出效能比較好，更得順暢的SQL語句。這樣，也可以給DBA減輕負擔。
我自己比較喜歡跟程式設計師講培訓，一來大家交流技術，都有收穫，二來搞好關係，工作上有什麼事以後需要協商的也好聊。這比請他們吃飯強。
我們針對程式寫得太爛，主要有下面幾個解決方向：
要讓應用程式使用資料庫連線池，特別是像基於JAVA開發的大型高並發應用程式裡，一定要使用連線池。
使用連接池的好處：就是可以限制應用的連接數，另外，不用再額外地去創建每個連接，MySQL創建連接的開銷也是較大的，因為創建一個新連接相當於MySQL創建了一個thread。
剛才我也提到，MySQL隨著連線數上升會出現效能下降。
有寫過程式碼的同學，應該也知道，在我們一般的PC筆記本上(一般4CORE)，你創建400個thread，每個thread就乾1 1 1 1 ..簡單活，再sleep下，你看看你的PC電腦卡還是不卡。你會發現你PC電腦的CPU都快跑滿了。你要敢創造600個thread，那你的機器就快等著重啟吧。這就是因為thread的開銷，把CPU已經佔滿了。
複雜的SQL語句
這個剛才也說了，程式設計師寫的SQL，通常都問題多多，他們畢竟太忙了，不會去考慮這個SQL的效能和運作情況。在某些情況下，程式設計師拼接的SQL，直接可以把整個系統乾跨掉。
我舉個簡單例子：我們一個應用程式對資料庫創建了10個連接(最大連接數=10)，這10個連接每個連接都同時跑相同的一條複雜SQL，執行這個複雜SQL至少要10分鐘，那這10個連線在10分鐘以內都只能執行這個複雜SQL，其他後面的SQL全得堵著。
造成10分鐘大部分應用程式不可用了，對吧。而且有可能引起雪崩，造成系統崩潰。
複雜SQL的最佳化，也是DBA很重要的一個活，需要透過監控的手段找出這些複雜SQL、慢SQL、爛SQL，然後給予最佳化建議到程式設計師（DBA要進行效能對比測試），讓程式設計師改造下程式碼，才能讓系統真正暢快並行地跑起來，像不塞車的高速公路一樣。
那有人會問了，我們公司的程式設計師就是牛B，打死不改SQL程式碼，弄死了也不去優化，無法溝通。那我們該怎麼辦呢？
我們還是有辦法的，我們還可以建立一個專用的從函式庫（Slave函式庫）來處理，你換個函式庫查詢，總可以了吧。
例如舉我們公司的例子，我們的後台出報表的系統，就是連的從庫查詢，不給連主庫。
無效邏輯
全表掃描
例如：update t set a = a 1 ; 忘加where條件了。
以你要想你的系統能支撐百萬級的用戶在線，那還得加入SQL審核系統（SQL Review），杜絕無效邏輯的SQL，和這類全表掃描的SQL。
SQL經過DBA審核通過後，才能發佈上線。
另外，這種大的update SQL應該分批更新，把大的SQL任務拆成小的任務來跑。在MySQL裡面來說，這是要特別注意的。
為什麼要分批更新呢？
**原因1. **上面說的，MySQL的一個query只能用到一個CORE。 SQL事務太大，複雜度太高需要很久才能運作出來，容易造成壅塞。
原因2. 線上環境，MySQL一般都是Master/Slave架構，如果Master發生100W行的大更新事務，很可能造成SLAVE卡在那裡，因為SLAVE是單執行緒結構，造成同步延遲。
MySQL寫SQL，乾成小事務SQL，快速執行，快速提交。讓每個query完成得更快，讓連線更快釋放出來。

本篇對MySQL優化的注意事項作出了講解，更多相關知識請關注php中文網。

MySQL優化注意事項

相關文章