性能優化：MySQL 性能提升之降龍十八掌

作者 | 張甦， 資料庫領域的專家和知名人士、圖書《MySQL王者晉級之路》作者，51CTO 專家部落客。近10年網際網路線上處理及教育訓練經驗，專注于 MySQL 資料庫，對 MongoDB、Redis 等 NoSQL 資料庫以及 Hadoop 生态圈相關技術有深入研究，具備非常豐富的理論與實戰經驗。

技術老鐵們，工作累了，我們就一起來放松一下！老張我呢是個金庸迷，在金庸小說中，降龍十八掌無愧巅峰外功，它的威力之大可想而知。而今兒，老張要給大家介紹18招式，來優化我們的 MySQL 資料庫，讓它跑起來更快，更穩定！

之前老有學生問我，張老師該如何優化我們的 MySQL 資料庫呢？這個問題太泛泛了，不是很具體！因為資料庫的優化要從多個角度去考慮，通過不同的次元模型去排查問題。為此我整理了下思路，大概可以從18個角度，大緻四個方向去給大家一些建議。

第一掌---亢龍有悔

要想保證資料庫能夠高效，穩定地運作在伺服器上面，我們首先要保證有充足的記憶體，隻有記憶體足夠大了，我們才能緩存住那些我們經常通路的熱資料，一些 update 語句的操作當然也可以在記憶體中優先完成。但是我們要考慮記憶體使用黃金分割法則，由于不同業務的存在，對記憶體的需求當然也就不一樣了。

舉個例子來說，使用者經常通路的熱資料，對于記憶體的配置設定就要盡可能達到達到資料庫記憶體的 70-80% 左右。衆所周知，我們知道 MySQL 資料庫記憶體主要靠 innodb_buffer_pool，redo log buffer，double write buffer，binlog cache 等組成。如果伺服器上面隻跑着 MySQL一個應用，那大概 innodb_buffer_pool 可以配置設定到實體記憶體的 50-80% 左右。

TIPS：我們要根據實際實體記憶體的大小，具體是什麼業務類型，去考慮資料庫記憶體的配置設定。

第二掌---飛龍在天

要優化 MySQL 資料庫，首先要很了解對手，随着版本的更新，MySQL 用到的 CPU 核數就越多，自從 MySQL 5.6 之後可以使用到 64 個核。MySQL 連接配接特點的是這樣，每個連接配接對應一個線程，每個 sql/ 查詢隻能使用到一個 cpu 核心，是以需要越多的 CPU，并且更快的 CPU。這樣才能有利于提高資料庫性能，提高我們資料庫的并發能力！

TIPS：使用多核 CPU。

第三掌---見龍在田

衆所周知，IO 對資料庫來說，一直都是瓶頸，并且有可能将來一段時間還會是。是以對存儲媒體的要求就非常高，對于 IO 系統比較高的情況下，建議我們要使用更快的儲存設備 SSD 固态硬碟可提高上百倍的資料讀寫性能或者是 PCIE-SSD 固态硬碟可提高上千倍的資料讀寫能力。像現在的一些電商網站，在搞店慶或者促銷活動的時候，都需要借助此裝置，來滿足大量使用者的影響請求。

TIPS：建議上高轉速硬體裝置，SSD 或者 PCIE-SSD

第四掌---鴻漸與陸

自從 web2.0 時代開啟，基本所有的，我們使用的軟體都是基于 linux 平台自主研發的。我們知道，MySQL 資料庫也是跑在 linux 作業系統上面的。在官方建議估計最推薦的是 Solaris，但從實際生産中的角度來看 CentOS 和 REHL 都是不錯的選擇，個人建議推薦使用 CentOS, 如果非要使用 REHL，建議 版本為6以後的，這裡就不推薦使用在 windows 下跑 MySQL 資料庫了，雖然随着 MySQL 版本提升，對 windows 有了相關的優化，但是對于高并發，高負載的環境來說，依舊不建議使用。

TIPS：推薦使用 CentOS，或者 REHL 作業系統類型

第五掌---潛龍勿用

作業系統層面的優化，我們要考慮個可能大家會比較忽略的問題，首先就是 swappiness 的問題。swappiness 的值大小對如何使用 swap 分區有着密切的聯系。有兩個極限值，一個為 0，另一個為 100，檢視可執行 cat /proc/sys/vm/swappiness。

0 代表：最大限度地使用實體記憶體，然後才是 swap 分區，這種行為有可能導緻系統記憶體溢出，進而導緻mysql被意外kill掉。不建議這樣去設定。

100 則為：積極地使用使用 swap 分區，并且把記憶體上面的資料及時搬到 swap 分區裡。

TIPS：這裡比較建議使用預設 60 就可以。

第六掌---利涉大川

與 swappiness 對應的，另一個作業系統層面的優化，還有一個小細節點就是 IO 排程。這裡有 cfq，noop 和 deadline，系統預設使用 cfq，這裡老師建議使用 deadline。檢視方法：

cat /sys/block/sda/queue/scheduler/  

TIPS：deadline 可以調整讀寫時間，避免寫完沒有被讀取的餓死場景。

第七掌---突如其來

Oracle 11g 之後多了一個 result_cache，來緩存資料結果集。MySQL 裡面通過 innodb_buffer_pool 裡面有個 query cache 來緩存靜态結果集。我們都希望熱資料都儲存在記憶體裡面，我們讀取資料快速便捷，資料庫的緩存率也很高！但資料庫中的 query cache 裡面的資料一但發生更改，此緩存區毫無意義，就會變成雞肋。而且如果開啟 Query Cache，更新與寫入都要去檢查 query cache 反而增加了寫入的開銷。

TIPS：建議關閉 query cache

第八掌---震驚百裡

對于磁盤陣列，我們再熟悉不過了，但是對于陣列卡的 cache 政策，我們又該如何選擇呢。首先對于qps，tps，業務高的系統，一定要配置陣列卡，配 cache 子產品，和 BBU 子產品（用于提供後備電量）。

cache 政策有兩種，一種為：write through（WT）；另一種為：write back；

個人強烈建議使用 write back（WB）。WT含義，資料直接寫入磁盤，WB含義：資料先寫陣列卡的 cache，再由 cache 寫入磁盤，這樣對于寫入的性能有所提高。并且對于加速 redo log ,binlog, data file都有好處。

TIPS：強烈建議陣列卡的 cache 政策使用 write back。

第九掌---或躍在淵

前面也涉及到了，盡可能大的給 innodb_buffer_pool 配置設定空間，在伺服器隻跑資料庫一個應用前提下大概為實體記憶體 50-80%。

TIPS：建議應用與資料庫分開部署在伺服器上面，後期好排查問題。

第十掌---雙龍取水

MySQL 資料庫的一些核心參數，我們要在心裡銘記。比如雙一的含義，直接影響日志的重新整理機制。影響 redo log buffer 的重新整理機制

innodb_flush_log_at_trx_commit  = 1（最安全）

innodb_flush_log_at_trx_commit  = 2 （性能一般）

innodb_flush_log_at_trx_commit  = 0 （性能最好）。

影響binlog cache的重新整理機制~sync_binlog=0，當事務送出之後，MySQL 不做 fsync 之類的磁盤同步指令重新整理 binlog_cache 中的資訊到磁盤，而讓 Filesystem 自行決定什麼時候來做同步，或者 cache滿了之後才同步到磁盤。sync_binlog=n，當每進行 n 次事務送出之後，MySQL 将進行一次 fsync 之類的磁盤同步指令來将 binlog_cache 中的資料強制寫入磁盤。為了確定安全性，我們可以将 sync_binlog=1。為了獲得最佳性能我們可以将 sync_binlog=0。

TIPS：對于不同業務的公司，保障的點不一樣，所有我們要考慮好，是業務最重要，還是資料最重要！然後分别去設定不同的參數 value

第十一掌---魚躍于淵

MySQL 資料庫差別于其他資料庫最主要就是插件式存儲引擎，最為著名就是 myisam 還有 innodb。它們都有各自的特點，這裡強烈建議使用 innodb 存儲引擎表，無論是對于事務的支援，還是線上 DDL 語句快速操作，它都是目前最優秀的存儲引擎！MySQL 5.5 之後預設使用的存儲引擎都是 innodb

TIPS：生産環境中，如果還有 myisam 這種存儲引擎的表，建議全部做 myisam-->innodb 存儲引擎的轉換！不過MySQL 5.7之後，系統表也都是 innodb 了！

第十二掌---時乘六龍

檔案系統強烈推薦使用 xfs，不要再使用 ext3,ext4 之類的，因為 xfs 這種檔案系統也是 B-tree 結構最接近于資料庫的樹狀結構。

第十三掌---密雲不雨

生産環境中，經常會出現對大表進行 delete，或者 update 這類的操作。資料碎片随之産生，我們要經常去整理主要業務表的碎片，讓查詢檢索更快。可以通過 pt-ioprofile 監控與磁盤互動最為緊密的表，然後通過 alter table 或者導入導出資料的方法對表進行碎片整理。盡可能回收表空間。

第十四掌---損則有孚

利用天兔（lepus）或者 zabbix 做好對資料庫的監控。監控事項可以從伺服器的狀态，記憶體的使用情況，cpu的負載。資料庫中每秒的增删改查資訊，架構中的延遲和複制狀态資訊去作為監控的核心點。

第十五掌---龍戰于野

配合開發人員合理地設計表結構，秉着越簡單越好的原則，去選擇合适字段的資料類型。對于 ipv4，時間類型的字段，我們完全可以通過整型 int 來存取！通過函數轉換就可以了！

   ip涉及到兩個函數：inet_aton和inet_ntoa

   時間類型的兩個函數：from_unixtime和unix_timestamp

第十六掌---事務隔離級别的選擇

mysql資料庫中，有四種事務隔離級别。它們分别是Read Uncommitted（RU），Read Committed（RC），Repeatable Read（RR），Serializable（SR）。對于交易類型系統的網站，對于事務要求比較高，我們建議使用RR這種隔離級别。

第十七掌---羝羊觸藩

更改檔案句柄  ulimit –n 預設1024 太小

   程序數限制  ulimit –u   根據不同版本來決定

   禁掉NUMA  numctl –interleave=all

第十八掌---神龍擺尾

做過資料庫的同學們，可以經常會遇到 too many connections 這樣的問題，對于這樣的問題，我們一定要做好配置資料庫内部并發的情況。innodb_thread_concurrency 這個參數來決定 innodb 的并發情況。預設的大小是0。在 mysql5.7 版本中，增加了 thread pool，連接配接複用的存在，可以取預設值就 ok。但是5.7之前的版本，就需要考量一下取值了，個人建議 mysql5.6版本中設定為 36。mysql5.6 之前可以 8-32。

降龍十八掌已經打完，希望對于資料庫愛好者，從事資料庫工作中的同學來說有幫助。讓我們每天學習一點點，把自己的内功練得越來越深厚，打出屬于自己的武功。讓我們的資料庫飛起來！

。

資源下載下傳

關注公衆号：資料和雲（OraNews）回複關鍵字擷取

2018DTCC , 資料庫大會PPT

2017DTC，2017 DTC 大會 PPT

DBALIFE ，“DBA 的一天”海報

DBA04 ，DBA 手記4 電子書

122ARCH ，Oracle 12.2體系結構圖

2017OOW ，Oracle OpenWorld 資料

PRELECTION ，大講堂講師課程資料