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

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長期合作伙伴，公司提供的服務(wù)項(xiàng)目有：申請域名、虛擬空間、營銷軟件、網(wǎng)站建設(shè)、興縣網(wǎng)站維護(hù)、網(wǎng)站推廣。

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

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

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

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

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

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

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

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

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

機(jī)械盤配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
目標(biāo)二：減少SWAP使用傾向甚至禁掉，穩(wěn)定磁盤I/O和網(wǎng)絡(luò)減少等待時(shí)間，讓MySQL表現(xiàn)更加穩(wěn)定
1.vm.swappiness設(shè)為5甚至0，假如不關(guān)心發(fā)生OOM

echo 'vm.swappiness = 5' >>/etc/sysctl.conf
/sbin/sysctl -p
2.vm.dirty_background_ratio設(shè)為5，vm.dirty_ratio設(shè)為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設(shè)為雙1，減少網(wǎng)絡(luò)等待時(shí)間，提高效率

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è)務(wù)系統(tǒng)的版本，才能發(fā)揮出更大性能。運(yùn)行參數(shù)亦是如此，需要反復(fù)斟酌與調(diào)校。規(guī)范schema設(shè)計(jì)與sql編寫，還有規(guī)范上線后的運(yùn)維管理流程，同樣也會(huì)帶不小的收益。

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

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

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

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

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

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

4.設(shè)置合適的innodb_flush_log_at_trx_commit和sync_binlog值

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

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

7.設(shè)置innodb_log_file_size=256M，innodb_log_files_in_group=2

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

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

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

11.設(shè)置key_buffer_size = 32M，關(guān)閉query cache功能

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

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

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

4.查詢時(shí)，盡量填寫需要的列，不要查詢所有列，避免嚴(yán)重隨機(jī)讀問題

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

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

7.多表連接查詢時(shí)，關(guān)鍵字類型盡量一致，且都要有索引

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

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

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

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

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

2.不出現(xiàn)OOM KILL和大量使用SWAP，不必?fù)?dān)心MySQL進(jìn)程占用過多內(nèi)存

3.單實(shí)例運(yùn)行中硬件資源還是比較緊張時(shí)，不要跑多實(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管理，以便于分析和優(yōu)化

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

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

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

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

到此，關(guān)于“MySQL優(yōu)化的方法是什么”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！