《MYSQL教程MySQL常見的底層優(yōu)化操作教程及相關(guān)建議》要點(diǎn)：
本文介紹了MYSQL教程MySQL常見的底層優(yōu)化操作教程及相關(guān)建議，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

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

1.1、CPU相關(guān)MYSQL必讀

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

(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(最佳性能)；MYSQL必讀

(4)、內(nèi)存設(shè)置菜單中,啟用Node Interleaving,避免NUMA問題；
1.2、磁盤I/O相關(guān)MYSQL必讀

?? 下面幾個(gè)是依照IOPS性能提升的幅度排序,對(duì)于磁盤I/O可優(yōu)化的一些措施：MYSQL必讀

(1)、使用SSD或者PCIe SSD設(shè)備,至少獲得數(shù)百倍甚至萬倍的IOPS提升；
(2)、購置陣列卡同時(shí)配備CACHE及BBU模塊,可明顯提升IOPS(主要是指機(jī)械盤,SSD或PCIe SSD除外.同時(shí)必要定期檢查CACHE及BBU模塊的健康狀況,確保意外時(shí)不至于丟失數(shù)據(jù))；MYSQL必讀

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

(4)、盡可能選用RAID-10,而非RAID-5；MYSQL必讀

(5)、使用機(jī)械盤的話,盡可能選擇高轉(zhuǎn)速的,例如選用15KRPM,而不是7.2KRPM的盤,不差幾個(gè)錢的；
2、系統(tǒng)層相關(guān)優(yōu)化MYSQL必讀

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

?? 在文件系統(tǒng)層,下面幾個(gè)步伐可明顯提升IOPS性能：MYSQL必讀

(1)、使用deadline/noop這兩種I/O調(diào)度器,千萬別用cfq(它不適合跑DB類服務(wù))；
(2)、使用xfs文件系統(tǒng),千萬別用ext3；ext4勉強(qiáng)可用,但業(yè)務(wù)量很大的話,則必定要用xfs；MYSQL必讀

(3)、文件系統(tǒng)mount參數(shù)中增加：noatime, nodiratime, nobarrier幾個(gè)選項(xiàng)(nobarrier是xfs文件系統(tǒng)特有的)；
2.2、其他內(nèi)核參數(shù)優(yōu)化MYSQL必讀

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

(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ù)刷新到磁盤,避免瞬間I/O寫,產(chǎn)生嚴(yán)重等待(和MySQL中的innodb_max_dirty_pages_pct類似)；MYSQL必讀

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

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

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

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

?? 官方版本我們稱為ORACLE MySQL,這個(gè)沒什么好說的,相信絕大多數(shù)人會(huì)選擇它.MYSQL必讀

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

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

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

?? 建議調(diào)整下面幾個(gè)關(guān)鍵參數(shù)以獲得較好的性能：
(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í)間分布有直觀感受；MYSQL必讀

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

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

(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ù)庫的性能受到影響程度分別是：高、中、低,也就是第一個(gè)會(huì)另數(shù)據(jù)庫最慢,最后一個(gè)則相反；MYSQL必讀

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

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

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

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

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

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

(11)、由于已經(jīng)建議不再使用MyISAM引擎了,因此可以把key_buffer_size設(shè)置為32M左右,而且強(qiáng)烈建議關(guān)閉query cache功能；
3.3、關(guān)于Schema設(shè)計(jì)規(guī)范及SQL使用建議MYSQL必讀

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

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

(2)、字段長度滿足需求前提下,盡可能選擇長度小的.此外,字段屬性盡量都加上NOT NULL約束,可必定程度提高性能；MYSQL必讀

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

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

(5)、對(duì)一個(gè)VARCHAR(N)列創(chuàng)建索引時(shí),通常取其50%(甚至更小)左右長度創(chuàng)建前綴索引就足以滿足80%以上的查詢需求了,沒需要?jiǎng)?chuàng)建整列的全長度索引；MYSQL必讀

(6)、通常情況下,子查詢的性能比擬差,建議改造成JOIN寫法；MYSQL必讀

(7)、多表聯(lián)接查詢時(shí),關(guān)聯(lián)字段類型盡量一致,而且都要有索引；MYSQL必讀

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

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

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

(11)、類似分頁功能的SQL,建議先用主鍵關(guān)聯(lián),然后返回結(jié)果集,效率會(huì)高很多；
3.4、其他建議MYSQL必讀

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

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

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

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

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

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

(6)、可使用pt-kill殺掉超長時(shí)間的SQL哀求,Percona版本中有個(gè)選項(xiàng) innodb_kill_idle_transaction 也可實(shí)現(xiàn)該功能；MYSQL必讀

(7)、使用pt-online-schema-change來完成大表的ONLINE DDL需求；MYSQL必讀

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

維易PHP培訓(xùn)學(xué)院每天發(fā)布《MYSQL教程MySQL常見的底層優(yōu)化操作教程及相關(guān)建議》等實(shí)戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/13945.html