編輯手記：mysql作為左手歡迎的開源資料庫，一直是廣大資料庫技術愛好者所關注的。為了給大家提供更好的學習分享平台，我們将會在後續适當分享mysql的内容。感謝廣大朋友們的支援。
肖鵬老師對于開源資料庫尤其是mysql的研究特别深入，今天我們來一起聽他分享自己對mysql資料庫的優化經驗！
作者簡介
肖鵬
微網誌研發中心資料庫技術負責人，主要負責微網誌資料庫（mysql/reids/hbase/memcached）相關的業務保障，性能優化，架構設計以及周邊的自動化系統建設。10年網際網路資料庫架構和管理經驗，專注于資料庫的高性能和高可用技術保障方向。
正文
資料庫是所有架構中不可缺少的一環，一旦資料庫出現性能問題，那對整個系統都回來帶災難性的後果。并且資料庫一旦出現問題，由于資料庫天生有狀态（分主從）帶資料（一般還不小），是以出問題之後的恢複時間一般不太可控，是以，對資料庫的優化是需要我們花費很多精力去做的。接下來就給大家介紹一下微網誌資料庫這些年的一點經驗，希望可以對大家有幫助。
1硬體層優化
這一層最簡單，最近幾年相信大家對ssd這個名詞并不陌生，其超高的iops在剛出現在大家視野中的時候就讓人驚豔了一把，而随着最近價格的不斷下調，已經非常具有成本效益，目前微網誌已經把ssd伺服器作為資料庫類服務的标配。
我們來看下我們早些年自己對ssd的oltp的性能測試：
可以看到oltp的qps可以達到2.7w左右，配合1m2s的架構可以支援5w的qps，在一些簡單場景下，甚至可以不必配置cache層來做緩存。
ps:硬體測試最好自己進行實測，官方資料僅能作為一個參考值，因為很多時候性能要嚴重依賴于場景，細化到不同的sql會得到相差很大的結論，故最好自行測試。
微網誌在12年的時候使用pcie-flash支撐了feed系統在春晚3.5w的qps，在初期很好的支撐了業務的發展，為架構優化和改造争取了非常多的時間。并且大家可以看到，目前很多的雲廠商的實體機基本全都是ssd裝置，aws更是虛機都提供ssd盤來提供io性能，可以預見未來io将不會在是資料庫遇到的最大瓶頸點。
經驗：如果公司不差錢，最好直接投入ssd or pcie-flash裝置，而且投入的時間越早越好
2系統層優化
配合ssd硬體之後，系統層原有的一些設計就出現了問題，比如io scheduler，系統預設的為cfq，主要針對的是機械硬碟進行的優化，由于機械硬碟需要通過懸臂尋道，是以cfq是非常适合的。
complete fair queuing
該算法為每一個程序配置設定一個時間視窗，在該時間視窗内，允許程序發出io請求。通過時間視窗在不同程序間的移動，保證了對于所有程序而言都有公平的發出io請求的機會。同時cfq也實作了程序的優先級控制，可保證高優先級程序可以獲得更長的時間視窗。
但是由于ssd盤已經沒有了尋道而是基于電子的擦除，是以cfq算法已經明顯的不合适了，一般情況下網上都推薦使用noop算法，但是我個人更推薦deadline算法。我們看下這2種算法的特點。
noop算法隻擁有一個等待隊列，每當來一個新的請求，僅僅是按fifo的思路将請求插入到等待隊列的尾部，預設認為 i/o不會存在性能問題，比較節省cpu資源。
deadline排程算法通過降低性能而獲得更短的等待時間，它使用輪詢的排程器,簡潔小巧,提供了最小的讀取延遲和尚佳的吞吐量,特别适合于讀取較多的環境。
從算法的特點看，noop确實更适合ssd媒體，非常的簡單，但是由于資料庫型服務有很多複雜查詢，簡單的fifo可能會造成一些事務很難拿到資源進而一直處于等待狀态，是以個人更推薦使用deadline。
ps：更主要的是因為對這2個算法的壓測顯示性能并沒有太明顯的差別。
以下是我們自己線上上業務調整之後的效果：
除了以上這點之外，還有一些小地方也許要調整，雖然收益不會看上去這麼明顯，但是聚沙成塔，積少成多，還是非常值得優化的。
使用ext4 or xfs
在mount的時候加上 noatime屬性
raid卡的讀寫政策改為write back
使用jemalloc替換現有的glibc
經驗：重點放在針對io的優化上，資料庫尤其是mysql是io密集型服務，解決io的問題會減少不必要的問題。
3mysql自身的優化
我們先說說有那些參數可以帶來性能的改變
innodb_max_dirty_pages_pct
争議比較大，一般來說都是在75-90之間，主要控制bp中的髒資料刷盤的時機，如果太小會頻繁刷盤造成io上升，如果太大會導緻mysql正常關閉的時候需要很長的時間才能normal shutdown，具體需要看實際場景，個人推薦90
innodb_io_capacity
磁盤io吞吐，具體為緩沖區落地的時候，可以刷髒頁的數量，預設200，由于使用了ssd硬碟，是以推薦設定到3000-5000
innodb_read_io_threads
innodb_write_io_threads
增加背景處理線程的數目，預設為4，推薦改成8
sync_binlog
innodb_flush_log_at_trx_commit
著名的雙1參數，對性能影響非常的大
sync_binlog控制刷binlog的政策，mysql在每寫n次 二進制日志binary log時，會使用fdatasync()函數将它的寫二進制日志binary log同步到磁盤中去。
innodb_flush_log_at_trx_commit控制log buffer刷log file的政策，設定為0的時候每秒重新整理一次，設定為1的時候每次commit都會重新整理。
從上述描述就可以看出如果追求資料的安全性，那麼設定雙一是最安全的，如果追求性能最大化，那麼雙0最合适，這中間可以相差至少2倍的性能。
innodb_log_file_size
innodb redo log的size大小，5.5最大4g，5.6最大256g，這個越大可以提升寫的性能，大部分時候不需要等待checkpoint覆寫就可以一直write。
query_cache_type
看上去很美的東西，但是在實際生産環境中，多次給我們帶來了故障，由于每次表的更新都會清空buffer，并且對于sql的比對是逐個字元效驗實際效果很長，大部分時間并沒有得到cache的效果，反而得到了很多wait for query cache lock。建議關閉。
以上，僅針對mysql 5.5，目前我們還在摸索5.6和5.7由于還沒有大規模線上使用，是以還談不上有什麼經驗。
經驗：如果有人力可以投入，可以學習bat針對資料庫進行二次開發，通過path的方式獲得更高的性能和穩定性。如果沒有人力，隻要深入了解mysql自身參數的影響也可以滿足業務的需求，不用一味的追源碼級别的開發改造。
4業務優化
所謂的業務優化其實說白了很多時候就是index的優化，我們dba常說一條慢sql就能将上面所有的優化都付之一炬，cpu直接打滿，rt全都都飙升到500ms甚至1s以上。
優化慢查有三寶：
pt-query-digest
explain
show profiling
首先，使用pt-query-digest可以定位到定位影響最中的慢查是哪條。
然後通過explain具體分析慢查曉的問題所在
重點檢視type，rows和extra這三個字段。
其中type的順序如下：
最後，如果問題還是比較嚴重，可以通過show profiling來定位一下到底是那個環節出現的問題。
可以看到sending data最消耗時間，這時候就需要找到底為什麼在sending上消耗了這麼多的時間，是結果集太大，還是io性能不夠了，諸如此類
以下就是一個複雜語句的優化結果，可以從rows那裡明顯的看出減少了很多查詢的開銷。
經驗：最好建立慢查詢監控系統，每天都花時間在慢查的優化上，避免一條sql引發的血案之類的事情發生。
4架構優化
最後，也就是終極手段了，那就是架構優化，其實很多時候，當我們将上面幾個方向都做了之後發現還沒有很好的效果，那就必須找開發同學一起聊一下了。ps：當然找pm同學聊一下人生會更有效果。
記得有一次，我們找開發聊了一下，最後開發決定将這個功能改掉，這個時候你會突然發現無論什麼優化手段都比不上「不做」這個優化手段，簡直無敵了。
根據我個人的經驗來說架構層的優化有如下幾個普适原則：
cache為王
熱點資料必須使用redis或者mc之類的cache抗量，讓mysql抗流量是不明智的。
使用隊列消峰
衆所周知mysql的異步同步機制是單線程的，所有主庫上的并發到從庫上都是通過io-thread來慢慢做的，即使主庫寫入速度再快，從庫延遲了，整個叢集還是不可用，是以最好采用隊列來進行一定的寫入消峰，使寫入維持在一個較為均衡的水準。
适度的過度設計
很多産品最開始的時候比較小，但是有可能上線之後廣受好評一下用活躍度就上來了，這個時候如果資料庫出現瓶頸需要拆分需要開發、dba、架構師等等一起配合來做，而且很有可能沒有時間。是以在産品初期進行一定的過度設計會為未來這種情況打好鋪墊。最明顯的就是拆庫拆表，最好在一開始就對業務進行适度的垂直拆分和比較過度的水準拆分，以便應對業務的高速增長。
舉一個例子：
1、通過mcq降低對mysql的寫入性能的要求。
2、通過mc和redis來承擔使用者的實際通路，90%的量依靠cache層承載和屏蔽。
3、mysql作為最終的資料落地，存儲全量的資料，但是僅支撐部分業務查詢，小于10%。
經驗：讓合适的軟體做适合的事情，不要光從技術層面思考優化方案，也要從需求方面去分解。
5總結中的總結
轉一篇很經典的資料庫優化漏鬥法則，很多年前就看到過，現在再看依然覺得适用，大家共勉。
唯一不适用的就是最下的增加資源，ssd真是個好東西，誰用誰知道。