這篇文章主要介紹“MySQL優(yōu)化的方法是什么”，在日常操作中，相信很多人在MySQL優(yōu)化的方法是什么問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL優(yōu)化的方法是什么”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

硬件層的優(yōu)化
新采購的服務器默認跑在節(jié)能模式下，在并發(fā)訪問量很大的業(yè)務場景，會導致數(shù)據(jù)庫性能跟不上，造成大量延遲，最終將拖垮業(yè)務系統(tǒng)。與此同時，磁盤選擇與陣列卡設置不當也會使數(shù)據(jù)庫性能成為整個業(yè)務系統(tǒng)的瓶頸。

目標一：全面關閉節(jié)能模式，讓MySQL跑在高性能模式下
1.關閉CPU節(jié)能模式

找到OPI Link Speed Select選項，選擇Max Performance
2.關閉內(nèi)存節(jié)能模式

找到Memory Speed選項，選擇Max Performance
找到Power C-States選項，選擇Disable
找到C1 Enhanced Mode選項，選擇Disable
目標二：關閉NUMA，讓CPU能始終高效地使用內(nèi)存
關閉NUMA

找到Socket Interleave選項，選擇Non-NUMA
目標三：全面提升IOPS性能，讓磁盤I/O不再拖后退
1.資金充足時，采購SSD甚至PCIe-SSD

SSD和PCIe-SSD帶來的不只是驚喜，更有踏實，從此磁盤I/O不再是惡魔
2.機械盤搭配陣列卡，Cache策略，BBU電池，RAID-10，15KRPM

陣列卡從容面對多塊機械盤，BBU電池保障高性能模式下的Cache策略不丟數(shù)據(jù)
Cache策略選擇Write Back甚至Always Write Back
陣列預讀的Read Policy選項，選擇Normal
使用RAID-10,性能高于RAID-5
使用15KRPM高速磁盤，性能高于7.2KRPM磁盤
備注：服務器硬件設置的參數(shù)來源于IBM X3650M3

系統(tǒng)層的優(yōu)化
操作系統(tǒng)方面也存在多處值得優(yōu)化的地方，同樣能明顯提升IOPS性能。另外，SWAP要少用，不但不能救命，反而會讓業(yè)務系統(tǒng)處于崩潰邊緣。

目標一：全面提升IOPS性能，讓數(shù)據(jù)庫不再背鍋
1.配置合理的I/O調(diào)度器

機械盤配deadline，執(zhí)行命令echo deadline >/sys/block/sda/queue/scheduler
固態(tài)盤配noop，執(zhí)行命令echo noop >/sys/block/sda/queue/scheduler
注意sda是數(shù)據(jù)文件所在分區(qū)
2.文件系統(tǒng)盡量使用XFS，假如還在使用ext4，希望只是過度階段

3.mount參數(shù)增加noatime，nodiratime，nobarrier

vi /etc/fstab
/dev/sda1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0
/dev/sda2 / xfs defaults,noatime,nodiratime,nobarrier 0 0
/dev/sda3 swap swap defaults,noatime,nodiratime,nobarrier 0 0
mount -o remount /data
mount
目標二：減少SWAP使用傾向甚至禁掉，穩(wěn)定磁盤I/O和網(wǎng)絡減少等待時間，讓MySQL表現(xiàn)更加穩(wěn)定
1.vm.swappiness設為5甚至0，假如不關心發(fā)生OOM

echo 'vm.swappiness = 5' >>/etc/sysctl.conf
/sbin/sysctl -p
2.vm.dirty_background_ratio設為5，vm.dirty_ratio設為10，讓臟頁持續(xù)刷入磁盤，避免磁盤I/O瞬間寫產(chǎn)生TIME_WAIT

echo 'vm.dirty_background_ration = 5' >>/etc/sysctl.conf
echo 'vm.dirty_ratio = 10' >>/etc/sysctl.conf
/sbin/sysctl -p
3.net.ipv4.tcp_tw_recycle和net.ipv4.tcp_tw_reuse設為雙1，減少網(wǎng)絡等待時間，提高效率

echo 'net.ipv4.tcp_tw_recycle = 1' >>/etc/sysctl.conf
echo 'net.ipv4.tcp_tw_reuse = 1' >>/etc/sysctl.conf
/sbin/sysctl -p
MySQL層的優(yōu)化
選對MySQL版本尤為重要，找到適合業(yè)務系統(tǒng)的版本，才能發(fā)揮出更大性能。運行參數(shù)亦是如此，需要反復斟酌與調(diào)校。規(guī)范schema設計與sql編寫，還有規(guī)范上線后的運維管理流程，同樣也會帶不小的收益。

目標一：選對版本，讓MySQL起跑底氣十足
1.優(yōu)先推薦Oracle MySQL，越來越多的新上系統(tǒng)在擁抱官方5.7.x版本

2.其次推薦Percona分支版本，在這里能享受免費的thread pool和audit plugin

3.最后是MariaDB分支版本，除了線程池和審計插件，在這里能享受免費的黑科技

目標二：調(diào)校合適的參數(shù)，讓MySQL的性能更加穩(wěn)定
1.如果選擇使用Percona或MariaDB分支版本，強烈推薦開啟thread pool

2.設置default-storage-engine=innodb，innodb可以滿足99%以上的業(yè)務場景

3.設置合適的innodb_buffer_pool_size大小，單實例多數(shù)是innodb表，建議設置物理內(nèi)存的50%-70%

4.設置合適的innodb_flush_log_at_trx_commit和sync_binlog值

設置雙1，不丟數(shù)據(jù)，性能較低
設置2和10，丟失一點數(shù)據(jù)，性能一般
設置雙0，數(shù)據(jù)不×××全，性能最高
5.設置innodb_file_per_table = 1，使用獨立表空間

6.設置innodb_data_file_path = ibdata1:1G:autoextend，在高并發(fā)事務時獲得良好性能

7.設置innodb_log_file_size=256M，innodb_log_files_in_group=2

8.設置long_query_time = 0.05，記錄超過50毫秒的慢SQL

9.適當調(diào)大max_connection，建議設置max_connection_error為10萬以上，設置open_files_limit、innodb_open_files、table_open_cache、table_definition_cache約10倍于max_connection

10.不宜設置過大的參數(shù)tmp_table_size、max_heap_table_size、sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size

11.設置key_buffer_size = 32M，關閉query cache功能

關閉QC需要在啟動MySQL前配置
query_cache_type = 0
query_cache_size = 0
目標三：Schema設計和SQL編寫根據(jù)參考規(guī)范設定，有助于提高MySQL效率
1.所有innodb表都設計一個無業(yè)務用途的自增列做主鍵

2.字段類型在滿足夠用時，盡量選長度小的；字段屬性盡量都加上NOT NULL約束

3.盡量不用TEXT和BLOB字段類型，一定需要時拆分至子表

4.查詢時，盡量填寫需要的列，不要查詢所有列，避免嚴重隨機讀問題

5.一般varchar(n)列建索引是，取前50%長度即可

6.子查詢處理時性能低，建議改使用JOIN改寫SQL

7.多表連接查詢時，關鍵字類型盡量一致，且都要有索引

8.多表連接查詢時，把過濾后的結(jié)果集小的表作為驅(qū)動表

優(yōu)勢：不需要的數(shù)據(jù)不會出現(xiàn)，SQL查詢范圍小，執(zhí)行效率高
9.多表連接查詢并且有排序時，排序字段必須是驅(qū)動表里的，否則排序列不走索引

10.多用復合索引，少用多個獨立索引，尤其是基數(shù)太小的列則不建議創(chuàng)建索引

11.使用分頁功能的SQL時，選把關鍵字與主鍵做符合索引，再來執(zhí)行，效率會高很多

目標四：管理維護的優(yōu)化，讓運維更高效
1.online DDL代價太高，機器性能足夠時，建議單表物理不超過10G，單表行數(shù)不超過1億，行平均長度不超過8KB

2.不出現(xiàn)OOM KILL和大量使用SWAP，不必擔心MySQL進程占用過多內(nèi)存

3.單實例運行中硬件資源還是比較緊張時，不要跑多實例

4.定期用pt-duplicate-key-checker檢查和刪除重復索引，定期用pt-index-usage檢查和刪除不太用的索引

5.定期采集slow query log，用pt-query-digest工具進行分析，再結(jié)合Anemometer等系統(tǒng)進行slow query管理，以便于分析和優(yōu)化

6.可以使用pt-kill殺掉超長時間的SQL請求，Percona版本中有個選項 innodb_kill_idle_transaction也能實現(xiàn)該功能

7.可以使用pt-online-schema-change來完成大表的ONLINE DDL需求

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

核心綱領：在上線之前，變更任何一個參數(shù)，都要做壓力測試，避免漏網(wǎng)之魚導致MySQL出現(xiàn)各種CRASH。

到此，關于“MySQL優(yōu)化的方法是什么”的學習就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關知識，請繼續(xù)關注創(chuàng)新互聯(lián)-成都網(wǎng)站建設公司網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
網(wǎng)站標題：MySQL優(yōu)化的方法是什么-創(chuàng)新互聯(lián)
本文URL：http://weahome.cn/article/dgeojd.html