在操作資料庫的時候,需要我們對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優化注意事項的詳細內容。更多資訊請關注PHP中文網其他相關文章!