程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL罕見的底層優化操作教程及相干建議

MySQL罕見的底層優化操作教程及相干建議

編輯：MySQL綜合教程

MySQL罕見的底層優化操作教程及相干建議。本站提示廣大學習愛好者：（MySQL罕見的底層優化操作教程及相干建議）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL罕見的底層優化操作教程及相干建議正文

1、硬件層相干優化

1.1、CPU相干

在辦事器的BIOS設置中，可調劑上面的幾個設置裝備擺設，目標是施展CPU最年夜機能，或許防止經典的NUMA成績：

（1）、選擇Performance Per Watt Optimized(DAPC)形式，施展CPU最年夜機能，跑DB這類平日須要高運算量的辦事就不要斟酌節電了；
（2）、封閉C1E和C States等選項，目標也是為了晉升CPU效力；
（3）、Memory Frequency(內存頻率)選擇Maximum Performance(最好機能)；

（4）、內存設置菜單中，啟用Node Interleaving，防止NUMA成績；
1.2、磁盤I/O相干

上面幾個是依照IOPS機能晉升的幅度排序，關於磁盤I/O可優化的一些辦法：

（1）、應用SSD或許PCIe SSD裝備，至多取得數百倍乃至萬倍的IOPS晉升；
（2）、購買陣列卡同時裝備CACHE及BBU模塊，可顯著晉升IOPS(重要是指機械盤，SSD或PCIe SSD除外。同時須要按期檢討CACHE及BBU模塊的安康狀態，確保不測時不至於喪失數據)；

（3）、有陣列卡時，設置陣列寫戰略為WB，乃至FORCE WB(如有雙電掩護，或對數據平安性請求不是特殊高的話)，嚴禁應用WT戰略。而且閉陣列預讀戰略，根本上是雞肋，用途不年夜；

（4）、盡量選用RAID-10，而非RAID-5；

（5）、應用機械盤的話，盡量選擇高轉速的，例如選用15KRPM，而不是7.2KRPM的盤，不差幾個錢的；
2、體系層相干優化

2.1、文件體系層優化

在文件體系層，上面幾個辦法可顯著晉升IOPS機能：

（1）、應用deadline/noop這兩種I/O調劑器，萬萬別用cfq(它不合適跑DB類辦事)；
（2）、應用xfs文件體系，萬萬別用ext3；ext4委曲可用，但營業量很年夜的話，則必定要用xfs；

（3）、文件體系mount參數中增長：noatime, nodiratime, nobarrier幾個選項(nobarrier是xfs文件體系獨有的)；
2.2、其他內核參數優化

針對症結內核參數設定適合的值，目標是為了削減swap的偏向，而且讓內存和磁盤I/O不會湧現年夜幅動搖，招致剎時波峰負載：

（1）、將vm.swappiness設置為5-10閣下便可，乃至設置為0(RHEL 7以上則鄭重設置為0，除非你許可OOM kill產生)，以下降應用SWAP的機遇；
（2）、將vm.dirty_background_ratio設置為5-10，將vm.dirty_ratio設置為它的兩倍閣下，以確保能連續將髒數據刷新到磁盤，防止剎時I/O寫，發生嚴重期待(和MySQL中的innodb_max_dirty_pages_pct相似)；

（3）、將net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都設置為1，削減TIME_WAIT，進步TCP效力；

（4）、至於網傳的read_ahead_kb、nr_requests這兩個參數，我經由測試後，發明對讀寫混雜為主的OLTP情況影響其實不年夜(應當是對讀敏感的場景更有用果)，不外沒准是我測試辦法有成績，可自行推敲能否調劑；

3、MySQL層相干優化

3.1、關於版本選擇

官方版本我們稱為ORACLE MySQL，這個沒甚麼好說的，信任絕年夜多半人會選擇它。

我小我激烈建議選擇Percona分支版本，它是一個絕對比擬成熟的、優良的MySQL分支版本，在機能晉升、靠得住性、治理型方面做了很多改良。它和官方ORACLE MySQL版本根本完整兼容，而且機能年夜約有20%以上的晉升，是以我優先推舉它，我本身也從2008年一向以它為主。

另外一個主要的分支版本是MariaDB，說MariaDB是分支版本其實曾經不太適合了，由於它的目的是代替ORACLE MySQL。它重要在本來的MySQL Server層做了年夜量的源碼級改良，也是一個異常靠得住的、優良的分支版本。但也由此發生了以GTID為代表的和官方版本沒法兼容的新特征(MySQL 5.7開端，也支撐GTID形式在線靜態開啟或封閉了)，也斟酌到絕年夜多半人照樣會隨著官方版本走，是以沒優先推舉MariaDB。

3.2、關於最主要的參數選項調劑建議

建議調劑上面幾個症結參數以取得較好的機能：
（1）、選擇Percona或MariaDB版本的話，激烈建議啟用thread pool特征，可以使得在高並發的情形下，機能不會產生年夜幅降低。另外，還有extra_port功效，異常適用，症結時辰能救命的。還有別的一個主要特點是 QUERY_RESPONSE_TIME 功效，也能使我們對全體的SQL呼應時光散布有直不雅感觸感染；

（2）、設置default-storage-engine=InnoDB，也就是默許采取InnoDB引擎，激烈建議不要再應用MyISAM引擎了，InnoDB引擎相對可以知足99%以上的營業場景；

（3）、調劑innodb_buffer_pool_size年夜小，假如是單實例且絕年夜多半是InnoDB引擎表的話，可斟酌設置為物理內存的50% ~ 70%閣下；

（4）、依據現實須要設置innodb_flush_log_at_trx_commit、sync_binlog的值。假如請求數據不克不及喪失，那末兩個都設為1。假如許可喪失一點數據，則可分離設為2和10。而假如完整不消care數據能否喪失的話(例如在slave上，橫豎年夜不了重做一次)，則可都設為0。這三種設置值招致數據庫的機能遭到影響水平分離是：高、中、低，也就是第一個會另數據庫最慢，最初一個則相反；

（5）、設置innodb_file_per_table = 1，應用自力表空間，我其實是想不出來用同享表空間有甚麼利益了；

（6）、設置innodb_data_file_path = ibdata1:1G:autoextend，萬萬不要用默許的10M，不然在有高並發事務時，會遭到不小的影響；

（7）、設置innodb_log_file_size=256M，設置innodb_log_files_in_group=2，根本可知足90%以上的場景；

（8）、設置long_query_time = 1，而在5.5版本以上，曾經可以設置為小於1了，建議設置為0.05(50毫秒)，記載那些履行較慢的SQL，用於後續的剖析排查；

（9）、依據營業現實須要，恰當調劑max_connection(最年夜銜接數)、max_connection_error(最年夜毛病數，建議設置為10萬以上，而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache這幾個參數則可設為約10倍於max_connection的年夜小；

（10）、罕見的誤區是把tmp_table_size和max_heap_table_size設置的比擬年夜，已經見過設置為1G的，這2個選項是每一個銜接會話都邑分派的，是以不要設置過年夜，不然輕易招致OOM產生；其他的一些銜接會話級選項例如：sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等，也須要留意不克不及設置過年夜；

（11）、因為曾經建議不再應用MyISAM引擎了，是以可以把key_buffer_size設置為32M閣下，而且激烈建議封閉query cache功效；
3.3、關於Schema設計標准及SQL應用建議

上面羅列了幾個罕見有助於晉升MySQL效力的Schema設計標准及SQL應用建議：

（1）、一切的InnoDB表都設計一個無營業用處的自增列做主鍵，關於絕年夜多半場景都是如斯，真正純只讀用InnoDB表的其實不多，真如斯的話還不如用TokuDB來得劃算；

（2）、字段長度知足需求條件下，盡量選擇長度小的。另外，字段屬性盡可能都加上NOT NULL束縛，可必定水平進步機能；

（3）、盡量不應用TEXT/BLOB類型，確切須要的話，建議拆分到子表中，不要和主表放在一路，防止SELECT * 的時刻讀機能太差。

（4）、讀取數據時，只拔取所須要的列，不要每次都SELECT *，防止發生嚴重的隨機讀成績，特別是讀到一些TEXT/BLOB列；

（5）、對一個VARCHAR(N)列創立索引時，平日取其50%(乃至更小)閣下長度創立前綴索引就足以知足80%以上的查詢需求了，沒需要創立整列的全長度索引；

（6）、平日情形下，子查詢的機能比擬差，建議改革成JOIN寫法；

（7）、多表聯接查詢時，聯系關系字段類型盡可能分歧，而且都要有索引；

（8）、多表銜接查詢時，把成果集小的表(留意，這裡是指過濾後的成果集，紛歧定是全表數據量小的)作為驅動表；

（9）、多表聯接而且有排序時，排序字段必需是驅動內外的，不然排序列沒法用到索引；

（10）、多用復合索引，罕用多個自力索引，特別是一些基數(Cardinality)太小(好比說，該列的獨一值總數少於255)的列就不要創立自力索引了；