MySQL優(yōu)化核心理論與實踐

背景描述：朋友單位OA系統(tǒng)前不久完成升級大改造，后端用的MySQL存儲數(shù)據(jù)，上線跑了個把月，抱怨電話開始接二連三打來，不是這里打不開，就是那里無響應，有人比喻升級后變成老爺車，越來越慢，問題迫在眉睫，必須馬上想對策呀。由于部署采用了規(guī)范文檔，上線前也做了各種測試，于是乎，在線排查，未果，翻出實施文檔，逐條閱讀，未果，于是想起曾經(jīng)一個業(yè)務系統(tǒng)，也碰到類似情況，后來通過各種優(yōu)化得以緩解，遂有下文，《MySQL優(yōu)化核心理論與實踐》。
說明：本文理論部分來源葉老師的博文，實踐部分來源工作積累和眾多熱愛MySQL技術(shù)分享的網(wǎng)友，整理初衷是為了更加深入地理解MySQL優(yōu)化，掌握更多MySQL優(yōu)化方面的技術(shù)，提升自己，回饋熱愛技術(shù)分享的所有網(wǎng)友。

硬件層的優(yōu)化

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

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

1.關(guān)閉CPU節(jié)能模式

找到OPI Link Speed Select選項，選擇Max Performance

2.關(guān)閉內(nèi)存節(jié)能模式

找到Memory Speed選項，選擇Max Performance
找到Power C-States選項，選擇Disable
找到C1 Enhanced Mode選項，選擇Disable

目標二：關(guān)閉NUMA，讓CPU能始終高效地使用內(nèi)存

關(guān)閉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è)置的參數(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設(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)絡等待時間，提高效率

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設(shè)計與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.設(shè)置default-storage-engine=innodb，innodb可以滿足99%以上的業(yè)務場景

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

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

設(shè)置雙1，不丟數(shù)據(jù)，性能較低
設(shè)置2和10，丟失一點數(shù)據(jù)，性能一般
設(shè)置雙0，數(shù)據(jù)不×××全，性能最高

5.設(shè)置innodb_file_per_table = 1，使用獨立表空間

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

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

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

9.適當調(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需要在啟動MySQL前配置
query_cache_type = 0
query_cache_size = 0

目標三：Schema設(shè)計和SQL編寫根據(jù)參考規(guī)范設(shè)定，有助于提高MySQL效率

1.所有innodb表都設(shè)計一個無業(yè)務用途的自增列做主鍵

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

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

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

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

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

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

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

優(yōu)勢：不需要的數(shù)據(jù)不會出現(xiàn)，SQL查詢范圍小，執(zhí)行效率高

9.多表連接查詢并且有排序時，排序字段必須是驅(qū)動表里的，否則排序列不走索引

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

11.使用分頁功能的SQL時，選把關(guān)鍵字與主鍵做符合索引，再來執(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ù)差異

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

寫在結(jié)尾：計劃后期再對每個細節(jié)進行理論分析和壓力測試，首次整理寫作，可能有不完善之處，歡迎留言和交流。

強烈推薦兩位實力派老師：葉金榮 和 吳炳錫

原文鏈接：
比較全面的MySQL優(yōu)化參考（上篇）
比較全面的MySQL優(yōu)化參考（下篇）

發(fā)表時間：2018年3月8日17時