這是一個建立於 的文章，其中的資訊可能已經有所發展或是發生改變。
【編者的話】資料庫是所有架構中不可缺少的一環，一旦資料庫出現效能問題，那對整個系統都會來帶災難性的後果。並且資料庫一旦出現問題，由於資料庫天生有狀態（分主從）帶資料（一般還不小），所以出問題之後的恢復一般不太可控，所以，對資料庫的最佳化是需要我們花費很多精力去做的。接下來就給大家介紹一下微博資料庫這些年的一點經驗，希望可以對大家有協助。
硬體層最佳化
這一層最簡單，最近幾年相信大家對SSD這個名詞並不陌生，其超高的IOPS在剛出現在大家視野中的時候就讓人驚豔了一把，而隨著最近價格的不斷下調，已經非常具有性價比，目前微博已經把SSD伺服器作為資料庫類服務的標配。
我們來看下我們早些年自己對SSD的OLTP的效能測試：
可以看到OLTP的QPS可以達到2.7w左右，配合1m2s的架構可以支援5w的QPS，在一些簡單情境下，甚至可以不必配置Cache層來做緩衝。
PS：硬體測試最好自己進行實測，官方資料僅能作為一個參考值，因為很多時候效能要嚴重依賴於情境，細化到不同的SQL會得到相差很大的結論，故最好自行測試。
微博在2012年的時候使用PCIE-FLASH支撐了Feed系統在春晚3.5w的QPS，在初期很好的支撐了業務的發展，為架構最佳化和改造爭取了非常多的時間。
並且大家可以看到，目前很多的雲廠商的物理機基本全都是SSD裝置，AWS更是虛機都提供SSD盤來提供IO效能，可以預見未來IO將不會在是資料庫遇到的最大瓶頸點。
經驗：如果公司不差錢，最好直接投入SSD or PCIE-FLASH裝置，而且投入的時間越早越好。
系統層最佳化
配合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的問題會減少不必要的問題。
MySQL自身的最佳化
我們先說說有那些參數可以帶來效能的改變。
- innodb_max_dirty_pages_pct
爭議比較大，一般來說都是在75-90之間，主要控制BP中的髒資料刷盤的時機，如果太小會頻繁刷盤造成IO上升，如果太大會導致MySQL正常關閉的時候需要很長的時間才能normal shutdown，具體需要看實際情境，個人推薦90。
磁碟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 redo log的size大小，5.5最大4G，5.6最大256G，這個越大可以提升寫的效能，大部分時候不需要等待checkpoint覆蓋就可以一直write。
看上去很美的東西，但是在實際生產環境中，多次給我們帶來了故障，由於每次表的更新都會清空Buffer，並且對於SQL的匹配是逐個字元效驗實際效果很長，大部分時間並沒有得到cache的效果，反而得到了很多wait for query cache lock。建議關閉。
以上，僅針對MySQL 5.5，目前我們還在摸索5.6和5.7由於還沒有大規模線上使用，所以還談不上有什麼經驗。
經驗：如果有人力可以投入，可以學習BAT針對資料庫進行二次開發，通過path的方式獲得更高的效能和穩定性。如果沒有人力，只要深入瞭解MySQL自身參數的影響也可以滿足業務的需求，不用一味的追源碼層級的開發改造。
業務最佳化
所謂的業務最佳化其實說白了很多時候就是index的最佳化，我們DBA常說一條慢SQL就能將上面所有的最佳化都付之一炬，CPU直接打滿，RT全都都飆升到500ms甚至1s以上。
最佳化慢查有三寶：
- pt-query-digest
- explain
- show profiling
首先，使用pt-query-digest可以定位到定位影響最中的慢查是哪條。
然後通過explain具體分析慢查曉的問題所在。
重點查看type，rows和extra這三個欄位。
其中type的順序如下：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
最後，如果問題還是比較嚴重，可以通過show profiling來定位一下到底是那個環節出現的問題。
可以看到sending data最消耗時間，這時候就需要找到底為什麼在sending上消耗了這麼多的時間，是結果集太大，還是IO效能不夠了，諸如此類。
以下就是一個複雜語句的最佳化結果，可以從rows那裡明顯的看出減少了很多查詢的開銷。
經驗：最好建立慢查詢監控系統，每天都花時間在慢查的最佳化上，避免一條SQL引發的血案之類的事情發生。
架構最佳化
最後，也就是終極手段了，那就是架構最佳化，其實很多時候，當我們將上面幾個方向都做了之後發現還沒有很好的效果，那就必須找開發同學一起聊一下了。
PS：當然找PM同學聊一下人生會更有效果。
記得有一次，我們找開發聊了一下，最後開發決定將這個功能改掉，這個時候你會突然發現無論什麼最佳化手段都比不上「不做」這個最佳化手段，簡直無敵了。
根據我個人的經驗來說架構層的最佳化有如下幾個普適原則：
熱點資料必須使用Redis或者mc之類的Cache抗量，讓MySQL抗流量是不明智的。
眾所周知MySQL的非同步同步機制是單線程的，所有主庫上的並發到從庫上都是通過io-thread來慢慢做的，即使主庫寫入速度再快，從庫延遲了，整個叢集還是不可用，所以最好採用隊列來進行一定的寫入消峰，使寫入維持在一個較為均衡的水平。
很多產品最開始的時候比較小，但是有可能上線之後廣受好評一下用活躍度就上來了，這個時候如果資料庫出現瓶頸需要拆分需要開發、DBA、架構師等等一起配合來做，而且很有可能沒有時間。所以在產品初期進行一定的過度設計會為未來這種情況打好鋪墊。最明顯的就是拆庫拆表，最好在一開始就對業務進行適度的垂直分割和比較過度的水平分割，以便應對業務的高速增長。
舉一個栗子：
- 通過mcq降低對MySQL的寫入效能的要求。
- 通過mc和Redis來承擔使用者的實際訪問，90%的量依靠cache層承載和屏蔽。
- MySQL作為最終的資料落地，儲存全量的資料，但是僅支撐部分業務查詢，小於10%。
經驗：讓合適的軟體做適合的事情，不要光從技術層面思考最佳化方案，也要從需求方面去分解。
總結中的總結
轉一篇很經典的資料庫最佳化漏鬥法則，很多年前就看到過，現在再看依然覺得適用，大家共勉。
唯一不適用的就是最下的增加資源，SSD真是個好東西，誰用誰知道。
問答
Q：MySQL緩衝你們是如何用調配的？能否用類似Redis的NoSQL進行代替？
A：我們預設都是關閉qc的，緩衝有用mc也有用Redis，一般都是將熱資料發在Cache中。
Q：在開發過程中，進行多表聯集查詢時有什麼好的建議或技巧？比如學生表、班級表、教師表、學生選修課程表，課程資訊表。
A：其實我想說，MySQL對join的最佳化並不太好，如果可能最好不用，如果要用最好也不要太多表join，而且最好用小結果集去驅動。
Q：SSD壽命多少呢？
A：看官方的說明，不過我們最早的用了快4年了，有部分出現了效能問題，但是大部分還都穩定運行。
Q：MySQL單表支援多少資料量的時候效能最好？
A：經驗值，不要超過3kw行，不要超過30G。
Q：我想問一下MySQL選型有考慮非官方，如MariaDB嗎 ？MySQL叢集方案和分庫分表比較各有啥特點？
A：官方和非官方的各有優勢，我們是使用社區版本，主要方便交流，對於MariaDB只要有人持續跟進也是很不錯的，我周圍有很多朋友也在使用。不過對於DBA不充足的公司來說，還是建議社區版本，這樣問題可以得到及時的解答。
Q：這次分享有值鑒作用，對於架構方面，中介軟體使用的是什麼，還是自己開發？主從同步存在的延遲是如何解決的？
A：中介軟體我們是自己研發的，不過老實說也不是都用了，還是要看情境。主從延遲說起來都是淚，目前使用5.7的並行複製在解決。
Q：在開發過程中，經常遇到一個表中欄位非常多的情況，針對這種情況如何處理，是否要分表等處理如何去平衡？
A：欄位多不一定非要去分表，主要還是看是否存在效能問題，不過一般欄位多會帶來建index上的麻煩，所以最好還是進行垂直分割的好。
Q：什麼階段適合分庫分區？對業務不穩定改動較大的業務資料庫需要怎麼做？
A：預計短期內內會影響業務發展的就要做，如果不想對業務造成較大的影響，最好投入一定的伺服器成本，先mirror一套叢集改造完畢之後在切服務。
Q：請問使用什麼樣的策略或者方式保證緩衝與資料庫中資料的一致性？在使用分庫分表，主從複製等情況下。
A：使用訊息系統，或者採用類似異構複製中介軟體的軟體（比如我們自己開發的Databus），先更新MySQL然後在更新Cache。
Q：第一個問題，請教下上文提到MySQL非同步同步機制是單線程的問題，是否考慮過修改為多線程同步來減少延遲，或者升級到5.6版本？第二個問題，除了效能最佳化，資料庫的一致性也很重要這裡能否有相關的分享，謝謝。
A：是的，延遲目前看來最大的問題就是單線程複製導致，所以我們對5.7的並行複製非常的期待，而5.6的並行複製是基於庫的，並不會有很大的改善。至於一致性，看情境，如果追求的非常嚴格，最好上雙1以及半同步複製，或者改用PXC。
Q：經驗：重點放在針對IO的最佳化上，資料庫尤其是MySQL是IO密集型服務，解決IO的問題會減少不必要的問題。-----有沒有好的定位IO或者是監控IO的工具和好的經驗？
A：監控是很重要的，有能力就根據自己的需要寫，如果不想投入開發人力直接使用開源的就好了，比如小米的Open-Falcon，主要就是監控細緻一些，自然而然就能發現問題。
Q：目前NoSQL非常火，基於文檔的、基於列的、基於對象等等，前段時間用了用MongoDB，感覺如果資料結構設計和控製得好的話，要比MySQL效率高，你怎麼看？
A：對於資料庫的選型我個人是秉著適合情境最優理論的，也就是每個資料庫都有一個最適合自己的情境，在這個情境下選擇它絕對是對的。目前我也在看MongoDB，AliCloud最近組織的杭州MongoDB使用者會就挺火，不過對於MongoDB來說，最大的優勢我認為是schema less和sharding，這對於開發和DBA來說都能節省很多的事情，但是最關鍵的是你還得hold住，否則還是用MySQL比較好。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
以上內容根據2016年3月8日晚群分享內容整理。分享人
肖鵬，微博研發中心技術經理，主要負責微博資料（MySQL/Reids/HBase/Memcached）相關的業務保障，效能最佳化，架構設計以及周邊的自動化系統建設。經曆了微博資料庫各個階段的架構改造，包括服務保障及SLA體系建設，微博多機房部署，微博平台化改造等項目，10年互連網資料庫結構描述和管理經驗，專註於資料庫的高效能和高可用技術保證方向。 DockOne每周都會組織定向的技術分享，歡迎感興趣的同學加：liyingjiesz，進群參與，您有想聽的話題或者想分享的話題都可以給我們留言。
