談談作為DBA我對MySQL資料庫優化的了解

    第一次聽到别人問“MySQL怎麼優化？”的問題是在一次電話面試裡面，當時反應不及，就舉了一個工作中給某張表添加索引然後優化了SQL查詢性能的栗子。最後面試官說沒有get到我的key。然後，當然沒有什麼然後了。後來，陸陸續續參加了好幾個面試，竟然發現不論哪裡、哪個公司、哪個崗位、哪個面試官，千篇一律幾乎都會問到這個問題。後來想想，雖然這個問題就像你告訴别人“我是個好人”然後别人問你“你哪裡好？”一樣，是一個又大又空又寬泛，讓人一臉懵逼的問題，但是作為一個MySQL DBA，還是應該對這個問題的回答做一個完整的準備。我對自己的定位是初級MySQL DBA，還處于坐井觀天的水準，是以以下内容作為工作能力的簡單總結，寫的時候也以自己的了解和邏輯展開。

1.被動優化

    DBA對資料庫的優化工作有很多都處在被動優化階段。一方面，被動優化所面對的資料庫問題是顯性的，不可逃避；另一方面，被動優化往往是長期的人工運維工作，沒有多少成熟有效的規避辦法。

　　①錯誤日志（配置優化）

　　MySQL會将資料庫伺服器運作中的錯誤資訊、警告資訊以及與之相關的建議資訊寫入錯誤日志，這些error、warning和notice記錄應該作為優先級比較高的維護工作。因為error log也會記錄正确的伺服器日志，是以可以寫一個簡單的腳本提取這些對優化工作有幫助的資訊，然後進行優化工作。

　　②慢查詢日志（SQL調優）

　　再精心的預優化，也不能避免慢查詢語句的産生，隻要資料庫系統所服務的應用是動态活躍的，總會出現漏網之魚。是以，定期處理和優化slow log裡的SQL是又一項重要的被動優化工作。

　　③資料庫版本更新

　　版本更新可能是容易被忽略的一個優化點，但往往卻是優化工作中的一步大棋。MySQL的版本更新還是比較快的，一般幾個月就會放出一個小版本（第三個版本号）。大版本（第二個版本号）的更新一般會非常大：MySQL5.5開始InnoDB成為預設存儲引擎；5.6對InnoDB做了很多優化，比如Online DDL、MRR以及ICP等；5.7就當下關注的資料庫安全、國際化以及InnoDB都做了很多的支援和優化工作，并且内置了JSON資料結構。總的來說，最新版的MySQL總是最好的MySQL。在舊版本上的很多優化工作，可能到了新版本就變成多餘了。是以，更新版本總是沒錯的。

2.資料庫系統架構優化

　　優良的資料庫架構系統應該才是展現DBA能動性的地方，這是人為設計的部分，展現對業務場景中資料存取層的了解和實作。

　　①使用多類型資料庫的設計

　　對MySQL資料庫優化的一個最大的局限就是把優化停留在MySQL裡。一個很簡單的哲學道理，元件的合理搭配能讓整體實作大于簡單加和的結果。實際上關系型MySQL并非萬能，至少在一些場景下有其他類型的資料庫可以做的更好。例如，MongoDB的文檔存儲、memcached和Redis的記憶體緩存。在我之前參與的一個項目中，MongoDB和MySQL作為主資料庫（primary database），前者存儲原始文檔後者存儲文檔資訊的中繼資料，memcached和Redis作為輔助資料庫（auxiliary database）緩存計算結果，這種設計有效地支撐了線上應用。RDBMS和NoSQL在資料庫設計中應當是左膀右臂的關系。

　　②根據業務場景選擇合适的存儲引擎

　　一般情況下，選擇MySQL預設的InnoDB存儲引擎是沒有什麼問題的。InnoDB是事物型的存儲引擎，适合OLTP應用場景。然而，為了有優化的餘地，一些建好後就很少修改的非業務表也可以考慮選用MyISAM存儲引擎。例如字典表。OLAP的場景，表主要是作為讀表存在，資料的寫入往往是整表寫或周期性的批量寫，這時候完全可以使用MyISAM。例如，報表資料庫。另外，日志表和流水表可以考慮選用Archive存儲引擎。

　　③分片和讀寫分離

　　Sharding是分擔并發寫的壓力；讀寫分離是拆分資料庫的職能。無論哪種，都是很好的優化政策。MySQL的複制（replication）是很好的高可用方案設計。

3.資料庫伺服器優化

　　MySQL伺服器優化是DBA崗位運維性質的一個展現，涉及到實體機、作業系統和資料庫伺服器的方方面面。

　　①實體優化

　　MySQL伺服器的性能與CPU、RAM以及I/O是直接相關的。一般在項目上線前都要做整個應用和資料庫系統的基準測試。根據阿姆達爾定律，在預估和測試中，去找到整個系統的平衡配置。對于MySQL，最終的工作就是修改相應的内置變量。是以這和“被動優化”部分對錯誤日志處理的落腳點是一樣的，差別在于這裡是主動的工作。就個人的多次面試經曆來看，當面試官問到資料庫優化的時候，很多時候想聽的東西，大概就是和MySQL的參數配置相關的。詳情可以參考個人之前的一篇學習筆記： http://blog.csdn.net/sweeper_freedoman/article/details/73302130。

　　②表設計中範式與反範式結合

　　所有屬性不可再分、非主屬性完全依賴于全部主鍵、非主屬性間沒有依賴關系，即經典的關系型資料庫範式思想。表設計中常常會遇到空間和性能的權衡，精簡和備援的取舍，這就需要深入了解業務場景，做好和産品大爺以及開發大爺的溝通。

　　③表空間優化

　　Analyze和optimize表，頻繁改動的線上表可以寫一個腳本在淩晨跑一下。