《為什么你的MySQL跑得很慢？》要點：
本文介紹了為什么你的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(500*0.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跑得很慢,相信你應該多少有了點答案.

文/?黃稚禹

文章來自DBAplus社群

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/4525.html