本文整理了一些MySQL的通用優(yōu)化方法，做個(gè)簡(jiǎn)單的總結(jié)分享，旨在幫助那些沒(méi)有專(zhuān)職MySQL DBA的企業(yè)做好基本的優(yōu)化工作，至于具體的SQL優(yōu)化，大部分通過(guò)加適當(dāng)?shù)乃饕纯蛇_(dá)到效果，更復(fù)雜的就需要具體分析了，可以參考本站的一些優(yōu)化案例或者聯(lián)系我們

1、硬件層相關(guān)優(yōu)化

1.1、CPU相關(guān)

在服務(wù)器的BIOS設(shè)置中，可調(diào)整下面的幾個(gè)配置，目的是發(fā)揮CPU最大性能，或者避免經(jīng)典的NUMA問(wèn)題：

1、選擇Performance Per Watt Optimized(DAPC)模式，發(fā)揮CPU最大性能，跑DB這種通常需要高運(yùn)算量的服務(wù)就不要考慮節(jié)電了；

2、關(guān)閉C1E和C States等選項(xiàng)，目的也是為了提升CPU效率；

3、Memory Frequency（內(nèi)存頻率）選擇Maximum Performance（最佳性能）；

4、內(nèi)存設(shè)置菜單中，啟用Node Interleaving，避免NUMA問(wèn)題；

1.2、磁盤(pán)I/O相關(guān)

下面幾個(gè)是按照IOPS性能提升的幅度排序，對(duì)于磁盤(pán)I/O可優(yōu)化的一些措施：

1、使用SSD或者PCIe SSD設(shè)備，至少獲得數(shù)百倍甚至萬(wàn)倍的IOPS提升；

2、購(gòu)置陣列卡同時(shí)配備CACHE及BBU模塊，可明顯提升IOPS（主要是指機(jī)械盤(pán)，SSD或PCIe SSD除外。同時(shí)需要定期檢查CACHE及BBU模塊的健康狀況，確保意外時(shí)不至于丟失數(shù)據(jù)）；

3、有陣列卡時(shí)，設(shè)置陣列寫(xiě)策略為WB，甚至FORCE WB（若有雙電保護(hù)，或?qū)?shù)據(jù)安全性要求不是特別高的話），嚴(yán)禁使用WT策略。并且閉陣列預(yù)讀策略，基本上是雞肋，用處不大；

4、盡可能選用RAID-10，而非RAID-5；

5、使用機(jī)械盤(pán)的話，盡可能選擇高轉(zhuǎn)速的，例如選用15KRPM，而不是7.2KRPM的盤(pán)，不差幾個(gè)錢(qián)的；

2、系統(tǒng)層相關(guān)優(yōu)化

2.1、文件系統(tǒng)層優(yōu)化

在文件系統(tǒng)層，下面幾個(gè)措施可明顯提升IOPS性能：

1、使用deadline/noop這兩種I/O調(diào)度器，千萬(wàn)別用cfq（它不適合跑DB類(lèi)服務(wù)）；

2、使用xfs文件系統(tǒng)，千萬(wàn)別用ext3；ext4勉強(qiáng)可用，但業(yè)務(wù)量很大的話，則一定要用xfs；

3、文件系統(tǒng)mount參數(shù)中增加：noatime, nodiratime, nobarrier幾個(gè)選項(xiàng)（nobarrier是xfs文件系統(tǒng)特有的）；

2.2、其他內(nèi)核參數(shù)優(yōu)化

針對(duì)關(guān)鍵內(nèi)核參數(shù)設(shè)定合適的值，目的是為了減少swap的傾向，并且讓內(nèi)存和磁盤(pán)I/O不會(huì)出現(xiàn)大幅波動(dòng)，導(dǎo)致瞬間波峰負(fù)載：

1、將vm.swappiness設(shè)置為5-10左右即可，甚至設(shè)置為0（RHEL 7以上則慎重設(shè)置為0，除非你允許OOM kill發(fā)生），以降低使用SWAP的機(jī)會(huì)；

2、將vm.dirty_background_ratio設(shè)置為5-10，將vm.dirty_ratio設(shè)置為它的兩倍左右，以確保能持續(xù)將臟數(shù)據(jù)刷新到磁盤(pán)，避免瞬間I/O寫(xiě)，產(chǎn)生嚴(yán)重等待（和MySQL中的innodb_max_dirty_pages_pct類(lèi)似）；

3、將net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都設(shè)置為1，減少TIME_WAIT，提高TCP效率；

4、至于網(wǎng)傳的read_ahead_kb、nr_requests這兩個(gè)參數(shù)，我經(jīng)過(guò)測(cè)試后，發(fā)現(xiàn)對(duì)讀寫(xiě)混合為主的OLTP環(huán)境影響并不大（應(yīng)該是對(duì)讀敏感的場(chǎng)景更有效果），不過(guò)沒(méi)準(zhǔn)是我測(cè)試方法有問(wèn)題，可自行斟酌是否調(diào)整；

3、MySQL層相關(guān)優(yōu)化

3.1、關(guān)于版本選擇

官方版本我們稱(chēng)為ORACLE MySQL，這個(gè)沒(méi)什么好說(shuō)的，相信絕大多數(shù)人會(huì)選擇它。

我個(gè)人強(qiáng)烈建議選擇Percona分支版本，它是一個(gè)相對(duì)比較成熟的、優(yōu)秀的MySQL分支版本，在性能提升、可靠性、管理型方面做了不少改善。它和官方ORACLE MySQL版本基本完全兼容，并且性能大約有20%以上的提升，因此我優(yōu)先推薦它，我自己也從2008年一直以它為主。

另一個(gè)重要的分支版本是MariaDB，說(shuō)MariaDB是分支版本其實(shí)已經(jīng)不太合適了，因?yàn)樗哪繕?biāo)是取代ORACLE MySQL。它主要在原來(lái)的MySQL Server層做了大量的源碼級(jí)改進(jìn)，也是一個(gè)非常可靠的、優(yōu)秀的分支版本。但也由此產(chǎn)生了以GTID為代表的和官方版本無(wú)法兼容的新特性（MySQL 5.7開(kāi)始，也支持GTID模式在線動(dòng)態(tài)開(kāi)啟或關(guān)閉了），也考慮到絕大多數(shù)人還是會(huì)跟著官方版本走，因此沒(méi)優(yōu)先推薦MariaDB。

3.2、關(guān)于最重要的參數(shù)選項(xiàng)調(diào)整建議

建議調(diào)整下面幾個(gè)關(guān)鍵參數(shù)以獲得較好的性能（可使用本站提供的my.cnf生成器生成配置文件模板）：

1、選擇Percona或MariaDB版本的話，強(qiáng)烈建議啟用thread pool特性，可使得在高并發(fā)的情況下，性能不會(huì)發(fā)生大幅下降。此外，還有extra_port功能，非常實(shí)用， 關(guān)鍵時(shí)刻能救命的。還有另外一個(gè)重要特色是 QUERY_RESPONSE_TIME 功能，也能使我們對(duì)整體的SQL響應(yīng)時(shí)間分布有直觀感受；

2、設(shè)置default-storage-engine=InnoDB，也就是默認(rèn)采用InnoDB引擎，強(qiáng)烈建議不要再使用MyISAM引擎了，InnoDB引擎絕對(duì)可以滿(mǎn)足99%以上的業(yè)務(wù)場(chǎng)景；

3、調(diào)整innodb_buffer_pool_size大小，如果是單實(shí)例且絕大多數(shù)是InnoDB引擎表的話，可考慮設(shè)置為物理內(nèi)存的50% ~ 70%左右；

4、根據(jù)實(shí)際需要設(shè)置innodb_flush_log_at_trx_commit、sync_binlog的值。如果要求數(shù)據(jù)不能丟失，那么兩個(gè)都設(shè)為1。如果允許丟失一點(diǎn)數(shù)據(jù)，則可分別設(shè)為2和10。而如果完全不用care數(shù)據(jù)是否丟失的話（例如在slave上，反正大不了重做一次），則可都設(shè)為0。這三種設(shè)置值導(dǎo)致數(shù)據(jù)庫(kù)的性能受到影響程度分別是：高、中、低，也就是第一個(gè)會(huì)另數(shù)據(jù)庫(kù)最慢，最后一個(gè)則相反；

5、設(shè)置innodb_file_per_table = 1，使用獨(dú)立表空間，我實(shí)在是想不出來(lái)用共享表空間有什么好處了；

6、設(shè)置innodb_data_file_path = ibdata1:1G:autoextend，千萬(wàn)不要用默認(rèn)的10M，否則在有高并發(fā)事務(wù)時(shí)，會(huì)受到不小的影響；

7、設(shè)置innodb_log_file_size=256M，設(shè)置innodb_log_files_in_group=2，基本可滿(mǎn)足90%以上的場(chǎng)景；

8、設(shè)置long_query_time = 1，而在5.5版本以上，已經(jīng)可以設(shè)置為小于1了，建議設(shè)置為0.05（50毫秒），記錄那些執(zhí)行較慢的SQL，用于后續(xù)的分析排查；

9、根據(jù)業(yè)務(wù)實(shí)際需要，適當(dāng)調(diào)整max_connection（最大連接數(shù)）、max_connection_error（最大錯(cuò)誤數(shù)，建議設(shè)置為10萬(wàn)以上，而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache這幾個(gè)參數(shù)則可設(shè)為約10倍于max_connection的大??；

10、常見(jiàn)的誤區(qū)是把tmp_table_size和max_heap_table_size設(shè)置的比較大，曾經(jīng)見(jiàn)過(guò)設(shè)置為1G的，這2個(gè)選項(xiàng)是每個(gè)連接會(huì)話都會(huì)分配的，因此不要設(shè)置過(guò)大，否則容易導(dǎo)致OOM發(fā)生；其他的一些連接會(huì)話級(jí)選項(xiàng)例如：sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等，也需要注意不能設(shè)置過(guò)大；

11、由于已經(jīng)建議不再使用MyISAM引擎了，因此可以把key_buffer_size設(shè)置為32M左右，并且強(qiáng)烈建議關(guān)閉query cache功能；

3.3、關(guān)于Schema設(shè)計(jì)規(guī)范及SQL使用建議

下面列舉了幾個(gè)常見(jiàn)有助于提升MySQL效率的Schema設(shè)計(jì)規(guī)范及SQL使用建議：

1、所有的InnoDB表都設(shè)計(jì)一個(gè)無(wú)業(yè)務(wù)用途的自增列做主鍵，對(duì)于絕大多數(shù)場(chǎng)景都是如此，真正純只讀用InnoDB表的并不多，真如此的話還不如用TokuDB來(lái)得劃算；

2、字段長(zhǎng)度滿(mǎn)足需求前提下，盡可能選擇長(zhǎng)度小的。此外，字段屬性盡量都加上NOT NULL約束，可一定程度提高性能；

3、盡可能不使用TEXT/BLOB類(lèi)型，確實(shí)需要的話，建議拆分到子表中，不要和主表放在一起，避免SELECT * 的時(shí)候讀性能太差。

4、讀取數(shù)據(jù)時(shí)，只選取所需要的列，不要每次都SELECT *，避免產(chǎn)生嚴(yán)重的隨機(jī)讀問(wèn)題，尤其是讀到一些TEXT/BLOB列；

5、對(duì)一個(gè)VARCHAR(N)列創(chuàng)建索引時(shí)，通常取其50%（甚至更?。┳笥议L(zhǎng)度創(chuàng)建前綴索引就足以滿(mǎn)足80%以上的查詢(xún)需求了，沒(méi)必要?jiǎng)?chuàng)建整列的全長(zhǎng)度索引；

6、通常情況下，子查詢(xún)的性能比較差，建議改造成JOIN寫(xiě)法；

7、多表聯(lián)接查詢(xún)時(shí)，關(guān)聯(lián)字段類(lèi)型盡量一致，并且都要有索引；

8、多表連接查詢(xún)時(shí)，把結(jié)果集小的表（注意，這里是指過(guò)濾后的結(jié)果集，不一定是全表數(shù)據(jù)量小的）作為驅(qū)動(dòng)表；

9、多表聯(lián)接并且有排序時(shí)，排序字段必須是驅(qū)動(dòng)表里的，否則排序列無(wú)法用到索引；

10、多用復(fù)合索引，少用多個(gè)獨(dú)立索引，尤其是一些基數(shù)（Cardinality）太小（比如說(shuō)，該列的唯一值總數(shù)少于255）的列就不要?jiǎng)?chuàng)建獨(dú)立索引了；

11、類(lèi)似分頁(yè)功能的SQL，建議先用主鍵關(guān)聯(lián)，然后返回結(jié)果集，效率會(huì)高很多；

3.4、其他建議

關(guān)于MySQL的管理維護(hù)的其他建議有：

1、通常地，單表物理大小不超過(guò)10GB，單表行數(shù)不超過(guò)1億條，行平均長(zhǎng)度不超過(guò)8KB，如果機(jī)器性能足夠，這些數(shù)據(jù)量MySQL是完全能處理的過(guò)來(lái)的，不用擔(dān)心性能問(wèn)題，這么建議主要是考慮ONLINE DDL的代價(jià)較高；

2、不用太擔(dān)心mysqld進(jìn)程占用太多內(nèi)存，只要不發(fā)生OOM kill和用到大量的SWAP都還好；

3、在以往，單機(jī)上跑多實(shí)例的目的是能最大化利用計(jì)算資源，如果單實(shí)例已經(jīng)能耗盡大部分計(jì)算資源的話，就沒(méi)必要再跑多實(shí)例了；

4、定期使用pt-duplicate-key-checker檢查并刪除重復(fù)的索引。定期使用pt-index-usage工具檢查并刪除使用頻率很低的索引；

5、定期采集slow query log，用pt-query-digest工具進(jìn)行分析，可結(jié)合Anemometer系統(tǒng)進(jìn)行slow query管理以便分析slow query并進(jìn)行后續(xù)優(yōu)化工作；

6、可使用pt-kill殺掉超長(zhǎng)時(shí)間的SQL請(qǐng)求，Percona版本中有個(gè)選項(xiàng) innodb_kill_idle_transaction 也可實(shí)現(xiàn)該功能；

7、使用pt-online-schema-change來(lái)完成大表的ONLINE DDL需求；

8、定期使用pt-table-checksum、pt-table-sync來(lái)檢查并修復(fù)mysql主從復(fù)制的數(shù)據(jù)差異；

這次的優(yōu)化參考，大部分情況下都介紹了適用的場(chǎng)景，如果你的應(yīng)用場(chǎng)景和本文描述的不太一樣，那么建議根據(jù)實(shí)際情況進(jìn)行調(diào)整，而不是生搬硬套。

最新評(píng)論