技術(shù)老鐵們，工作累了，我們就一起來(lái)放松一下！老張我呢是個(gè)金庸迷，在金庸小說(shuō)中，降龍十八掌無(wú)愧巔峰外功，它的威力之大可想而知。而今兒，老張要給大家介紹18招式，來(lái)優(yōu)化我們的 MySQL 數據庫，讓它跑起來(lái)更快，更穩定！
之前老有學(xué)生問(wèn)我，張老師該如何優(yōu)化我們的 MySQL 數據庫呢？這個(gè)問(wèn)題太泛泛了，不是很具體！因為數據庫的優(yōu)化要從多個(gè)角度去考慮，通過(guò)不同的維度模型去排查問(wèn)題。為此我整理了下思路，大概可以從18個(gè)角度，大致四個(gè)方向去給大家一些建議。
第一掌---亢龍有悔
要想保證數據庫能夠高效，穩定地運行在服務(wù)器上面，我們首先要保證有充足的內存，只有內存足夠大了，我們才能緩存住那些我們經(jīng)常訪(fǎng)問(wèn)的熱數據，一些 update 語(yǔ)句的操作當然也可以在內存中優(yōu)先完成。但是我們要考慮內存使用黃金分割法則，由于不同業(yè)務(wù)的存在，對內存的需求當然也就不一樣了。
舉個(gè)例子來(lái)說(shuō)，用戶(hù)經(jīng)常訪(fǎng)問(wèn)的熱數據，對于內存的分配就要盡可能達到達到數據庫內存的 70-80% 左右。眾所周知，我們知道 MySQL 數據庫內存主要靠 innodb_buffer_pool，redo log buffer，double write buffer，binlog cache 等組成。如果服務(wù)器上面只跑著(zhù) MySQL一個(gè)應用，那大概 innodb_buffer_pool 可以分配到物理內存的 50-80% 左右。
TIPS：我們要根據實(shí)際物理內存的大小，具體是什么業(yè)務(wù)類(lèi)型，去考慮數據庫內存的分配。
第二掌---飛龍在天
要優(yōu)化 MySQL 數據庫，首先要很了解對手，隨著(zhù)版本的升級，MySQL 用到的 CPU 核數就越多，自從 MySQL 5.6 之后可以使用到 64 個(gè)核。MySQL 連接特點(diǎn)的是這樣，每個(gè)連接對應一個(gè)線(xiàn)程，每個(gè) sql/ 查詢(xún)只能使用到一個(gè) cpu 核心，所以需要越多的 CPU，并且更快的 CPU。這樣才能有利于提高數據庫性能，提高我們數據庫的并發(fā)能力！
TIPS：使用多核 CPU。
第三掌---見(jiàn)龍在田
眾所周知，IO 對數據庫來(lái)說(shuō)，一直都是瓶頸，并且有可能將來(lái)一段時(shí)間還會(huì )是。所以對存儲介質(zhì)的要求就非常高，對于 IO 系統比較高的情況下，建議我們要使用更快的存儲設備 SSD 固態(tài)硬盤(pán)可提高上百倍的數據讀寫(xiě)性能或者是 PCIE-SSD 固態(tài)硬盤(pán)可提高上千倍的數據讀寫(xiě)能力。像現在的一些電商網(wǎng)站，在搞店慶或者促銷(xiāo)活動(dòng)的時(shí)候，都需要借助此設備，來(lái)滿(mǎn)足大量用戶(hù)的影響請求。
TIPS：建議上高轉速硬件設備，SSD 或者 PCIE-SSD
第四掌---鴻漸與陸
自從 web2.0 時(shí)代開(kāi)啟，基本所有的，我們使用的軟件都是基于 linux 平臺自主研發(fā)的。我們知道，MySQL 數據庫也是跑在 linux 操作系統上面的。在官方建議估計最推薦的是 Solaris，但從實(shí)際生產(chǎn)中的角度來(lái)看 CentOS 和 REHL 都是不錯的選擇，個(gè)人建議推薦使用 CentOS, 如果非要使用 REHL，建議 版本為6以后的，這里就不推薦使用在 windows 下跑 MySQL 數據庫了，雖然隨著(zhù) MySQL 版本提升，對 windows 有了相關(guān)的優(yōu)化，但是對于高并發(fā)，高負載的環(huán)境來(lái)說(shuō)，依舊不建議使用。
TIPS：推薦使用 CentOS，或者 REHL 操作系統類(lèi)型
第五掌---潛龍勿用
操作系統層面的優(yōu)化，我們要考慮個(gè)可能大家會(huì )比較忽略的問(wèn)題，首先就是 swappiness 的問(wèn)題。swappiness 的值大小對如何使用 swap 分區有著(zhù)密切的聯(lián)系。有兩個(gè)極限值，一個(gè)為 0，另一個(gè)為 100，查看可執行 cat /proc/sys/vm/swappiness。
0 代表：最大限度地使用物理內存，然后才是 swap 分區，這種行為有可能導致系統內存溢出，從而導致mysql被意外kill掉。不建議這樣去設置。
100 則為：積極地使用使用 swap 分區，并且把內存上面的數據及時(shí)搬到 swap 分區里。
TIPS：這里比較建議使用默認 60 就可以。
第六掌---利涉大川
與 swappiness 對應的，另一個(gè)操作系統層面的優(yōu)化，還有一個(gè)小細節點(diǎn)就是 IO 調度。這里有 cfq，noop 和 deadline，系統默認使用 cfq，這里老師建議使用 deadline。查看方法：
cat /sys/block/sda/queue/scheduler/
TIPS：deadline 可以調整讀寫(xiě)時(shí)間，避免寫(xiě)完沒(méi)有被讀取的餓死場(chǎng)景。
第七掌---突如其來(lái)
Oracle 11g 之后多了一個(gè) result_cache，來(lái)緩存數據結果集。MySQL 里面通過(guò) innodb_buffer_pool 里面有個(gè) query cache 來(lái)緩存靜態(tài)結果集。我們都希望熱數據都保存在內存里面，我們讀取數據快速便捷，數據庫的緩存率也很高！但數據庫中的 query cache 里面的數據一但發(fā)生更改，此緩存區毫無(wú)意義，就會(huì )變成雞肋。而且如果開(kāi)啟 Query Cache，更新與寫(xiě)入都要去檢查 query cache 反而增加了寫(xiě)入的開(kāi)銷(xiāo)。
TIPS：建議關(guān)閉 query cache
第八掌---震驚百里
對于磁盤(pán)陣列，我們再熟悉不過(guò)了，但是對于陣列卡的 cache 策略，我們又該如何選擇呢。首先對于qps，tps，業(yè)務(wù)高的系統，一定要配置陣列卡，配 cache 模塊，和 BBU 模塊（用于提供后備電量）。
cache 策略有兩種，一種為：write through（WT）；另一種為：write back；
個(gè)人強烈建議使用 write back（WB）。WT含義，數據直接寫(xiě)入磁盤(pán)，WB含義：數據先寫(xiě)陣列卡的 cache，再由 cache 寫(xiě)入磁盤(pán)，這樣對于寫(xiě)入的性能有所提高。并且對于加速 redo log ,binlog, data file都有好處。
TIPS：強烈建議陣列卡的 cache 策略使用 write back。
第九掌---或躍在淵
前面也涉及到了，盡可能大的給 innodb_buffer_pool 分配空間，在服務(wù)器只跑數據庫一個(gè)應用前提下大概為物理內存 50-80%。
TIPS：建議應用與數據庫分開(kāi)部署在服務(wù)器上面，后期好排查問(wèn)題。
第十掌---雙龍取水
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，當事務(wù)提交之后，MySQL 不做 fsync 之類(lèi)的磁盤(pán)同步指令刷新 binlog_cache 中的信息到磁盤(pán)，而讓 Filesystem 自行決定什么時(shí)候來(lái)做同步，或者 cache滿(mǎn)了之后才同步到磁盤(pán)。sync_binlog=n，當每進(jìn)行 n 次事務(wù)提交之后，MySQL 將進(jìn)行一次 fsync 之類(lèi)的磁盤(pán)同步指令來(lái)將 binlog_cache 中的數據強制寫(xiě)入磁盤(pán)。為了確保安全性，我們可以將 sync_binlog=1。為了獲得最佳性能我們可以將 sync_binlog=0。
TIPS：對于不同業(yè)務(wù)的公司，保障的點(diǎn)不一樣，所有我們要考慮好，是業(yè)務(wù)最重要，還是數據最重要！然后分別去設置不同的參數 value
第十一掌---魚(yú)躍于淵
MySQL 數據庫區別于其他數據庫最主要就是插件式存儲引擎，最為著(zhù)名就是 myisam 還有 innodb。它們都有各自的特點(diǎn)，這里強烈建議使用 innodb 存儲引擎表，無(wú)論是對于事務(wù)的支持，還是在線(xiàn) DDL 語(yǔ)句快速操作，它都是目前最優(yōu)秀的存儲引擎！MySQL 5.5 之后默認使用的存儲引擎都是 innodb
TIPS：生產(chǎn)環(huán)境中，如果還有 myisam 這種存儲引擎的表，建議全部做 myisam-->innodb 存儲引擎的轉換！不過(guò)MySQL 5.7之后，系統表也都是 innodb 了！
第十二掌---時(shí)乘六龍
文件系統強烈推薦使用 xfs，不要再使用 ext3,ext4 之類(lèi)的，因為 xfs 這種文件系統也是 B-tree 結構最接近于數據庫的樹(shù)狀結構。
第十三掌---密云不雨
生產(chǎn)環(huán)境中，經(jīng)常會(huì )出現對大表進(jìn)行 delete，或者 update 這類(lèi)的操作。數據碎片隨之產(chǎn)生，我們要經(jīng)常去整理主要業(yè)務(wù)表的碎片，讓查詢(xún)檢索更快?？梢酝ㄟ^(guò) pt-ioprofile 監控與磁盤(pán)交互最為緊密的表，然后通過(guò) alter table 或者導入導出數據的方法對表進(jìn)行碎片整理。盡可能回收表空間。
第十四掌---損則有孚
利用天兔（lepus）或者 zabbix 做好對數據庫的監控。監控事項可以從服務(wù)器的狀態(tài)，內存的使用情況，cpu的負載。數據庫中每秒的增刪改查信息，架構中的延遲和復制狀態(tài)信息去作為監控的核心點(diǎn)。
第十五掌---龍戰于野
配合開(kāi)發(fā)人員合理地設計表結構，秉著(zhù)越簡(jiǎn)單越好的原則，去選擇合適字段的數據類(lèi)型。對于 ipv4，時(shí)間類(lèi)型的字段，我們完全可以通過(guò)整型 int 來(lái)存??！通過(guò)函數轉換就可以了！
ip涉及到兩個(gè)函數：inet_aton和inet_ntoa
時(shí)間類(lèi)型的兩個(gè)函數：from_unixtime和unix_timestamp
第十六掌---事務(wù)隔離級別的選擇
mysql數據庫中，有四種事務(wù)隔離級別。它們分別是Read Uncommitted（RU），Read Committed（RC），Repeatable Read（RR），Serializable（SR）。對于交易類(lèi)型系統的網(wǎng)站，對于事務(wù)要求比較高，我們建議使用RR這種隔離級別。
第十七掌---羝羊觸藩
更改文件句柄 ulimit –n 默認1024 太小
進(jìn)程數限制 ulimit –u 根據不同版本來(lái)決定
禁掉NUMA numctl –interleave=all
第十八掌---神龍擺尾
做過(guò)數據庫的同學(xué)們，可以經(jīng)常會(huì )遇到 too many connections 這樣的問(wèn)題，對于這樣的問(wèn)題，我們一定要做好配置數據庫內部并發(fā)的情況。innodb_thread_concurrency 這個(gè)參數來(lái)決定 innodb 的并發(fā)情況。默認的大小是0。在 mysql5.7 版本中，增加了 thread pool，連接復用的存在，可以取默認值就 ok。但是5.7之前的版本，就需要考量一下取值了，個(gè)人建議 mysql5.6版本中設置為 36。mysql5.6 之前可以 8-32。
降龍十八掌已經(jīng)打完，希望對于數據庫愛(ài)好者，從事數據庫工作中的同學(xué)來(lái)說(shuō)有幫助。讓我們每天學(xué)習一點(diǎn)點(diǎn)，把自己的內功練得越來(lái)越深厚，打出屬于自己的武功。讓我們的數據庫飛起來(lái)！
作者 | 張甦， 數據庫領(lǐng)域的專(zhuān)家和知名人士、圖書(shū)《MySQL王者晉級之路》作者，51CTO 專(zhuān)家博主。近10年互聯(lián)網(wǎng)線(xiàn)上處理及培訓經(jīng)驗，專(zhuān)注于 MySQL 數據庫，對 MongoDB、Redis 等 NoSQL 數據庫以及 Hadoop 生態(tài)圈相關(guān)技術(shù)有深入研究，具備非常豐富的理論與實(shí)戰經(jīng)驗。 https://blog.51cto.com/sumongodb/1949024
聯(lián)系客服
