技術(shù)老鐵們，工作累了，我們就一起來(lái)放松一下！老張我呢是個(gè)金庸迷，在金庸小說中，降龍十八掌無(wú)愧巔峰外功，它的威力之大可想而知。而今兒，老張要給大家介紹18招式，來(lái)優(yōu)化我們的 MySQL 數(shù)據(jù)庫(kù)，讓它跑起來(lái)更快，更穩(wěn)定！

創(chuàng)新互聯(lián)是一家專注于做網(wǎng)站、成都網(wǎng)站建設(shè)與策劃設(shè)計(jì),昌寧網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)10余年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:昌寧等地區(qū)。昌寧做網(wǎng)站價(jià)格咨詢:18980820575

之前老有學(xué)生問我，張老師該如何優(yōu)化我們的 MySQL 數(shù)據(jù)庫(kù)呢？這個(gè)問題太泛泛了，不是很具體！因?yàn)閿?shù)據(jù)庫(kù)的優(yōu)化要從多個(gè)角度去考慮，通過不同的維度模型去排查問題。老師整理了下思路，大概可以從18個(gè)角度，大致四個(gè)方向去給大家一些建議。

第一掌----亢龍有悔

要想保證數(shù)據(jù)庫(kù)能夠高效，穩(wěn)定地運(yùn)行在服務(wù)器上面，我們首先要保證有充足的內(nèi)存，只有內(nèi)存足夠大了，我們才能緩存住那些我們經(jīng)常訪問的熱數(shù)據(jù)，一些 update 語(yǔ)句的操作當(dāng)然也可以在內(nèi)存中優(yōu)先完成。但是我們要考慮內(nèi)存使用黃金分割法則，由于不同業(yè)務(wù)的存在，對(duì)內(nèi)存的需求當(dāng)然也就不一樣了。

舉個(gè)列子來(lái)說，用戶經(jīng)常訪問的熱數(shù)據(jù)，對(duì)于內(nèi)存的分配就要盡可能達(dá)到達(dá)到數(shù)據(jù)庫(kù)內(nèi)存的 70-80% 左右。眾所周知，我們知道 MySQL 數(shù)據(jù)庫(kù)內(nèi)存主要靠 innodb_buffer_pool，redo log buffer，double write buffer，binlog cache 等組成。如果服務(wù)器上面只跑著 MySQL 一個(gè)應(yīng)用，那大概 innodb_buffer_pool 可以分配到物理內(nèi)存的 50-80% 左右。

TIPS：我們要根據(jù)實(shí)際物理內(nèi)存的大小，具體是什么業(yè)務(wù)類型，去考慮數(shù)據(jù)庫(kù)內(nèi)存的分配。

第二掌----飛龍?jiān)谔?/strong>

要優(yōu)化 MySQL 數(shù)據(jù)庫(kù)，首先要很了解對(duì)手，隨著版本的升級(jí)，MySQL 用到的 CPU 核數(shù)就越多，自從 MySQL 5.6 之后可以使用到 64 個(gè)核。MySQL 連接特點(diǎn)的是這樣，每個(gè)連接對(duì)應(yīng)一個(gè)線程，每個(gè) sql/ 查詢只能使用到一個(gè) cpu 核心，所以需要越多的 CPU，并且更快的 CPU。這樣才能有利于提高數(shù)據(jù)庫(kù)性能，提高我們數(shù)據(jù)庫(kù)的并發(fā)能力！

TIPS：使用多核 CPU。

第三掌----見龍?jiān)谔?/strong>

眾所周知，IO 對(duì)數(shù)據(jù)庫(kù)來(lái)說，一直都是瓶頸，并且有可能將來(lái)一段時(shí)間還會(huì)是。所以對(duì)存儲(chǔ)介質(zhì)的要求就非常高，對(duì)于 IO 系統(tǒng)比較高的情況下，建議我們要使用更快的存儲(chǔ)設(shè)備 SSD 固態(tài)硬盤可提高上百倍的數(shù)據(jù)讀寫性能或者是 PCIE-SSD 固態(tài)硬盤可提高上千倍的數(shù)據(jù)讀寫能力。像現(xiàn)在的一些電商網(wǎng)站，在搞店慶或者促銷活動(dòng)的時(shí)候，都需要借助此設(shè)備，來(lái)滿足大量用戶的影響請(qǐng)求。

TIPS：建議上高轉(zhuǎn)速硬件設(shè)備，SSD 或者 PCIE-SSD

第四掌----鴻漸于陸

自從 web2.0 時(shí)×××啟，基本所有的，我們使用的軟件都是基于 linux 平臺(tái)自主研發(fā)的。我們知道，MySQL 數(shù)據(jù)庫(kù)也是跑在 linux 操作系統(tǒng)上面的。在官方建議估計(jì)最推薦的是 Solaris，但從實(shí)際生產(chǎn)中的角度來(lái)看 CentOS 和 REHL 都是不錯(cuò)的選擇，個(gè)人建議推薦使用 CentOS, 如果非要使用 REHL，建議 版本為6以后的，這里就不推薦使用在 windows 下跑 MySQL 數(shù)據(jù)庫(kù)了，雖然隨著 MySQL 版本提升，對(duì) windows 有了相關(guān)的優(yōu)化，但是對(duì)于高并發(fā)，高負(fù)載的環(huán)境來(lái)說，依舊不建議使用。

TIPS：推薦使用 CentOS，或者 REHL 操作系統(tǒng)類型

第五掌----潛龍勿用

操作系統(tǒng)層面的優(yōu)化，我們要考慮個(gè)可能大家會(huì)比較忽略的問題，首先就是 swappiness 的問題。swappiness 的值大小對(duì)如何使用 swap 分區(qū)有著密切的聯(lián)系。有兩個(gè)極限值，一個(gè)為 0，另一個(gè)為 100，查看可執(zhí)行 cat /proc/sys/vm/swappiness。

0 代表：最大限度地使用物理內(nèi)存，然后才是 swap 分區(qū)，這種行為有可能導(dǎo)致系統(tǒng)內(nèi)存溢出，從而導(dǎo)致mysql被意外kill掉。不建議這樣去設(shè)置。

100則為：積極地使用使用 swap 分區(qū)，并且把內(nèi)存上面的數(shù)據(jù)及時(shí)搬到 swap 分區(qū)里。

TIPS：這里比較建議使用默認(rèn) 60 就可以。

第六掌----利涉大川

與 swappiness 對(duì)應(yīng)的，另一個(gè)操作系統(tǒng)層面的優(yōu)化，還有一個(gè)小細(xì)節(jié)點(diǎn)就是 IO 調(diào)度。這里有 cfq，noop 和 deadline，系統(tǒng)默認(rèn)使用 cfq，這里老師建議使用 deadline。查看方法：

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

TIPS：deadline 可以調(diào)整讀寫時(shí)間，避免寫完沒有被讀取的餓死場(chǎng)景。

第七掌----突如其來(lái)

Oracle 11g 之后多了一個(gè) result_cache，來(lái)緩存數(shù)據(jù)結(jié)果集。MySQL 里面通過 innodb_buffer_pool 里面有個(gè) query cache 來(lái)緩存靜態(tài)結(jié)果集。我們都希望熱數(shù)據(jù)都保存在內(nèi)存里面，我們讀取數(shù)據(jù)快速便捷，數(shù)據(jù)庫(kù)的緩存率也很高！但數(shù)據(jù)庫(kù)中的 query cache 里面的數(shù)據(jù)一但發(fā)生更改，此緩存區(qū)毫無(wú)意義，就會(huì)變成雞肋。而且如果開啟 Query Cache，更新與寫入都要去檢查 query cache 反而增加了寫入的開銷。

TIPS：建議關(guān)閉 query cache

第八掌----震驚百里

對(duì)于磁盤陣列，我們?cè)偈煜げ贿^了，但是對(duì)于陣列卡的 cache 策略，我們又該如何選擇呢。首先對(duì)于qps，tps，業(yè)務(wù)高的系統(tǒng)，一定要配置陣列卡，配 cache 模塊，和 BBU 模塊（用于提供后備電量）。

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

個(gè)人強(qiáng)烈建議使用 write back（WB）。WT含義，數(shù)據(jù)直接寫入磁盤，WB含義：數(shù)據(jù)先寫陣列卡的cache，再由cache寫入磁盤，這樣對(duì)于寫入的性能有所提高。并且對(duì)于加速redo log ,binlog, data file都有好處。

TIPS：強(qiáng)烈建議陣列卡的 cache 策略使用 write back。

第九掌----或躍在淵

前面也涉及到了，盡可能大的給 innodb_buffer_pool 分配空間，在服務(wù)器只跑數(shù)據(jù)庫(kù)一個(gè)應(yīng)用前提下大概為物理內(nèi)存 50-80%。

TIPS：建議應(yīng)用與數(shù)據(jù)庫(kù)分開部署在服務(wù)器上面，后期好排查問題。

第十掌----雙龍取水

MySQL 數(shù)據(jù)庫(kù)的一些核心參數(shù)，我們要在心里銘記。比如雙一的含義，直接影響日志的刷新機(jī)制。影響redo log buffer 的刷新機(jī)制

innodb_flush_log_at_trx_commit  = 1（最安全）

innodb_flush_log_at_trx_commit  = 2 （性能一般）

innodb_flush_log_at_trx_commit  = 0 （性能最好）。

影響binlog cache的刷新機(jī)制~sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL 不做 fsync 之類的磁盤同步指令刷新 binlog_cache 中的信息到磁盤，而讓 Filesystem 自行決定什么時(shí)候來(lái)做同步，或者 cache滿了之后才同步到磁盤。sync_binlog=n，當(dāng)每進(jìn)行 n 次事務(wù)提交之后，MySQL 將進(jìn)行一次 fsync 之類的磁盤同步指令來(lái)將 binlog_cache 中的數(shù)據(jù)強(qiáng)制寫入磁盤。為了確保安全性，我們可以將sync_binlog=1。為了獲得最佳性能我們可以將sync_binlog=0。

TIPS：對(duì)于不同業(yè)務(wù)的公司，保障的點(diǎn)不一樣，所有我們要考慮好，是業(yè)務(wù)最重要，還是數(shù)據(jù)最重要！然后分別去設(shè)置不同的參數(shù)value

第十一掌----魚躍于淵

MySQL 數(shù)據(jù)庫(kù)區(qū)別于其他數(shù)據(jù)庫(kù)最主要就是插件式存儲(chǔ)引擎，最為著名就是 myisam 還有 innodb。它們都有各自的特點(diǎn)，這里強(qiáng)烈建議使用 innodb 存儲(chǔ)引擎表，無(wú)論是對(duì)于事務(wù)的支持，還是在線 DDL 語(yǔ)句快速操作，它都是目前最優(yōu)秀的存儲(chǔ)引擎！MySQL 5.5 之后默認(rèn)使用的存儲(chǔ)引擎都是 innodb

TIPS：生產(chǎn)環(huán)境中，如果還有 myisam 這種存儲(chǔ)引擎的表，建議全部做 myisam-->innodb 存儲(chǔ)引擎的轉(zhuǎn)換！不過 MySQL 5.7之后，系統(tǒng)表也都是 innodb 了！

第十二掌----時(shí)乘六龍

文件系統(tǒng)強(qiáng)烈推薦使用 xfs，不要再使用 ext3,ext4 之類的，因?yàn)?xfs 這種文件系統(tǒng)也是 B-tree 結(jié)構(gòu)最接近于數(shù)據(jù)庫(kù)的樹狀結(jié)構(gòu)。

第十三掌----密云不雨

生產(chǎn)環(huán)境中，經(jīng)常會(huì)出現(xiàn)對(duì)大表進(jìn)行 delete，或者 update 這類的操作。數(shù)據(jù)碎片隨之產(chǎn)生，我們要經(jīng)常去整理主要業(yè)務(wù)表的碎片，讓查詢檢索更快?？梢酝ㄟ^ pt-ioprofile 監(jiān)控與磁盤交互最為緊密的表，然后通過 alter table 或者導(dǎo)入導(dǎo)出數(shù)據(jù)的方法對(duì)表進(jìn)行碎片整理。盡可能回收表空間

第十四掌----損則有孚

利用天兔（lepus）或者 zabbix 做好對(duì)數(shù)據(jù)庫(kù)的監(jiān)控。監(jiān)控事項(xiàng)可以從服務(wù)器的狀態(tài)，內(nèi)存的使用情況，cpu的負(fù)載。數(shù)據(jù)庫(kù)中每秒的增刪改查信息，架構(gòu)中的延遲和復(fù)制狀態(tài)信息去作為監(jiān)控的核心點(diǎn)。

第十五掌----龍戰(zhàn)于野

配合開發(fā)人員合理地設(shè)計(jì)表結(jié)構(gòu)，秉著越簡(jiǎn)單越好的原則，去選擇合適字段的數(shù)據(jù)類型。對(duì)于ipv4，時(shí)間類型的字段，我們完全可以通過整型int來(lái)存取！通過函數(shù)轉(zhuǎn)換就可以了！

ip涉及到兩個(gè)函數(shù)：inet_aton和inet_ntoa

時(shí)間類型的兩個(gè)函數(shù)：from_unixtime和unix_timestamp

第十六掌----事務(wù)隔離級(jí)別的選擇

mysql數(shù)據(jù)庫(kù)中，有四種事務(wù)隔離級(jí)別。它們分別是Read Uncommitted（RU），Read Committed（RC），Repeatable Read（RR），Serializable（SR）。對(duì)于交易類型系統(tǒng)的網(wǎng)站，對(duì)于事務(wù)要求比較高，我們建議使用RR這種隔離級(jí)別。

第十七掌----羝羊觸藩

更改文件句柄  ulimit –n 默認(rèn)1024 太小

進(jìn)程數(shù)限制  ulimit –u   根據(jù)不同版本來(lái)決定

禁掉NUMA  numctl –interleave=all

第十八掌----神龍擺尾

做過數(shù)據(jù)庫(kù)的同學(xué)們，可以經(jīng)常會(huì)遇到too many connections這樣的問題，對(duì)于這樣的問題，我們一定要做好配置數(shù)據(jù)庫(kù)內(nèi)部并發(fā)的情況。innodb_thread_concurrency 這個(gè)參數(shù)來(lái)決定innodb的并發(fā)情況。默認(rèn)的大小是0。在mysql5.7版本中，增加了thread pool，連接復(fù)用的存在，可以取默認(rèn)值就ok。但是5.7之前的版本，就需要考量一下取值了，個(gè)人建議mysql5.6版本中設(shè)置為36。mysql5.6之前可以8-32。

降龍十八掌已經(jīng)打完，希望對(duì)于數(shù)據(jù)庫(kù)愛好者，從事數(shù)據(jù)庫(kù)工作中的同學(xué)來(lái)說有幫助。讓我們每天學(xué)習(xí)一點(diǎn)點(diǎn)，把自己的內(nèi)功練得越來(lái)越深厚，打出屬于自己的武功。讓我們的數(shù)據(jù)庫(kù)飛起來(lái)！