MySQL的通用優(yōu)化方法

1、1、硬件層相關優(yōu)化CPU 相關在服務器的BIOS設置中，可調整下面的幾個配置，目的是發(fā)揮CPU最大性能，或者防止經典的NUMA問題：1、選擇Performance Per Watt Optimized （DAPC）模式，發(fā)揮CPU最大性能,跑DB這種通 常需要高運算量的服務就不要考慮節(jié)電了；2、關閉C1E和C States等選項，目的也是為了提升CPU效率；3、Memory Frequency （內存頻率）選擇 Maximum Performance （最正確性能）；4、內存設置菜單中，啟用Node Interleaving,防止NUMA問題；磁盤I/O相關下面幾個是按照IOPS性能提升的幅度

2、排序，對于磁盤I/O可優(yōu)化的一些措施：1、使用SSD或者PCIe SSD設備，至少獲得數百倍甚至萬倍的IOPS提升；2、購置陣列卡同時配備CACHE及BBU模塊，可明顯提升IOPS （主要是指機械盤，SSD或P Cie SSD除外。同時需要定期檢查CACHE及BBU模塊的健康狀況，確保意外時不至于喪失 數據）；3、有陣列卡時，設置陣列寫策略為WB,甚至FORCE WB （假設有雙電保護，或對數據平安性 要求不是特別高的話），嚴禁使用WT策略。并且閉陣列預讀策略，基本上是雞肋，用處不 大；4、盡可能選用RAID-10,而非RAID-5；5、使用機械盤的話，盡可能選擇高轉速的，例如選用15KRPM

3、,而不是7.2KRPM的盤，不 差幾個錢的；2、系統層相關優(yōu)化文件系統層優(yōu)化在文件系統層，下面幾個措施可明顯提升10PS性能：1、使用deadline/noop這兩種I/O調度器，千萬別用cfq （它不適合跑DB類服務）；2、使用xfs文件系統，千萬別用ext3； ext4勉強可用，但業(yè)務量很大的話，那么一定要用 xfs；3、文件系統 mount 參數中增加：noatime, nodiratime, nobarrier 幾個選項（nobarrier 是xfs文件系統特有的）；其他內核參數優(yōu)化針對關鍵內核參數設定合適的值，目的是為了減少swap的傾向，并且讓內存和磁盤I/O不會出現大幅波動,導致

4、瞬間波峰負載：1、將vm.swappiness設置為5To左右即可，甚至設置為0 （RHEL 7以上那么慎重設置為0,除非你允許OOM kill發(fā)生），以降低使用SWAP的機會；2、將 vm. dirty background ratio 設置為 5T0,將 vm. dirty ratio 設置為它的兩倍左 右，以確保能持續(xù)將臟數據刷新到磁盤，防止瞬間I/O寫，產生嚴重等待（和MySQL中的 innodb_max_dirty_pages_pct 類似）；3、將 net. ipv4. tcp_tw_recycle net. ipv4. tcp_tw_reuse 都設置為 1,減少 TIME_WA

5、IT, 提高TCP效率；4、至于網傳的read ahead kb、nr requests這兩個參數，我經過測試后，發(fā)現對讀寫混 合為主的OLTP環(huán)境影響并不大（應該是對讀敏感的場景更有效果），不過沒準是我測試方 法有問題，可自行斟酌是否調整；3、MySQL層相關優(yōu)化關于版本選擇官方版本我們稱為ORACLE MySQL ,這個沒什么好說的，相信絕大多數人會 選擇它。我個人強烈建議選擇Percona分支版本，它是一個相比照擬成熟的、優(yōu)秀的 MySQL分支版本，在性能提升、可靠性、管理型方面做了不少改善。它和官 方ORACLE MySQL版本基本完全兼容，并且性能大約有20%以上的提升，因 此我優(yōu)先

6、推薦它，我自己也從2008年一直以它為主。另一個重要的分支版本是MariaDB，說MariaDB是分支版本其實已經不太合 適了，因為它的目標是取代ORACLE MySQLo它主要在原來的MySQL Server層做了大量的源碼級改進，也是一個非常可靠的、優(yōu)秀的分支版本。但 也由此產生了以GTID為代表的和官方版本無法兼容的新特性（MySQL 5.7開 始，也支持GTID模式在線動態(tài)開啟或關閉了），也考慮到絕大多數人還是會 跟著官方版本走，因此沒優(yōu)先推薦MariaDB。關于最重要的參數選項調整建議建議調整下面幾個關鍵參數以獲得較好的性能：1、選擇Percona或MariaDB版本的話，強烈建議啟

7、用thread pool特性，可使得在高并發(fā) 的情況下，性能不會發(fā)生大幅下降。此外，還有extra_port功能，非常實用，關鍵時刻 能救命的。還有另外一個重要特色是QUERY RESPONSE TIME功能，也能使我們對整體的S QL響應時間分布有直觀感受；2、設置default-storage-engine=IrmoDB,也就是默認采用InnoDB引擎，強烈建議不要 再使用MylSAM引擎了，InnoDB引擎絕對可以滿足99%以上的業(yè)務場景；3、調整innodb_buffer_pool_size大小，如果是單實例且絕大多數是InnoDB引擎表的 話，可考慮設置為物理內存的50%70%左右；

8、4、根據實際需要設置innodb_flush_log_at_trx_commit sync_binlog的值。如果要求數 據不能喪失，那么兩個都設為1。如果允許喪失一點數據，那么可分別設為2和10。而如果 完全不用care數據是否喪失的話（例如在slave上，反正大不了重做一次），那么可都設為 0o這三種設置值導致數據庫的性能受到影響程度分別是：高、中、低，也就是第一個會另 數據庫最慢，最后一個那么相反；5、設置innodb_file_per_table=1,使用獨立表空間，我實在是想不出來用共享表空間 有什么好處了；6、設置 innodb data file path = ibdatal:

9、lG:autoextend,千萬不要用默認的 10M,否 那么在有高并發(fā)事務時，會受到不小的影響；7、設置 innodb log file size=256M, 設置 innodb log files in group=2, 基本可滿足 90%以上的場景；8、設置long_que:ry_time=1,而在5.5版本以上，已經可以設置為小于1 了，建議設置 為0.05 （50毫秒），記錄那些執(zhí)行較慢的SQL,用于后續(xù)的分析排查；9、根據業(yè)務實際需要，適當調整max connection （最大連接數）、max connection erro r （最大錯誤數，建議設置為 10 萬以上,而 ope

10、n_files_limit、innodb_open_filestab le open cache table definition cache 這幾個參數那么可設為約 10 倍于 max connection 的大小；10常見的誤區(qū)是把tinp table size和max heap table size設置的比擬大，曾經見過設 置為1G的，這2個選項是每個連接會話都會分配的，因此不要設置過大，否那么容易導致0 0M發(fā)生；其他的一些連接會話級選項例如：sort buffer_size join buffer_size read buffer size、read rnd buffer_size等

11、，也需要注意不能設置過大；11、由于已經建議不再使用My ISAM引擎了,因此可以把key buffer size設置為32M左 右，并且強烈建議關閉query cache功能；33.關于Schema設計規(guī)范及SQL使用建議下面列舉了幾個常見有助于提升MySQL效率的Schema設計規(guī)范及SQL使用建議：1、所有的InnoDB表都設計一個無業(yè)務用途的自增列做主鍵，對于絕大多數場景都是如 此，真正純只讀用InnoDB表的并不多，真如此的話還不如用TokuDB來得劃算；2、字段長度滿足需求前提下，盡可能選擇長度小的。此外，字段屬性盡量都加上NOT NUL L約束，可一定程度提高性能；3、盡可能不使

12、用TEXT/BLOB類型，確實需要的話，建議拆分到子表中，不要和主表放在一 起，防止SELECT *的時候讀性能太差。4、讀取數據時，只選取所需要的列，不要每次都SELECT *,防止產生嚴重的隨機讀問 題，尤其是讀到一些TEXT/BLOB歹U；5、對一個VARCHAR(N)列創(chuàng)立索引時，通常取其50% (甚至更小)左右長度創(chuàng)立前綴索引就 足以滿足80%以上的查詢需求了，沒必要創(chuàng)立整列的全長度索引；6、通常情況下，子查詢的性能比擬差，建議改造成JOIN寫法;7、多表聯接查詢時，關聯字段類型盡量一致，并且都要有索引;8、多表連接查詢時，把結果集小的表（注意，這里是指過濾后的結果集，不一定是全表數

13、 據量小的）作為驅動表；9、多表聯接并且有排序時，排序字段必須是驅動表里的，否那么排序列無法用到索弓I；10、多用復合索引，少用多個獨立索引，尤其是一些基數（Cardinality）太小（比方說, 該列的唯一值總數少于255）的列就不要創(chuàng)立獨立索引了；11、類似分頁功能的SQL,建議先用主鍵關聯，然后返回結果集，效率會高很多;33.其他建議關于MySQL的管理維護的其他建議有:1、通常地，單表物理大小不超過10GB,單表行數不超過1億條，行平均長度不超過8KB, 如果機器性能足夠，這些數據量MySQL是完全能處理的過來的，不用擔憂性能問題，這么 建議主要是考慮ONLINE DDL的代價較高；2、不用太擔憂mysqld進程占用太多內存，只要不發(fā)生OOM kill和用到大量的SWAP都還 好；3、在以往，單機上跑多實例的目的是能最大化利用計算資源，如果單實例已經能耗盡大部 分計算資源的話，就沒必要再跑多實例了；4、定期使用pt-duplicate-key-checker檢查并刪除重復的索引。定期使用pt-index-usa ge工具檢查并刪除使用頻率很低的索引；5、定期采集slow query log,用pt-query-digest工具進行分析，可結合Anemometer系 統進行slo