MySQL優化核心理論與實踐!

背景描述：朋友機關OA系統前不久完成更新大改造，後端用的MySQL存儲資料，上線跑了個把月，抱怨電話開始接二連三打來，不是這裡打不開，就是那裡無響應，有人比喻更新後變成老爺車，越來越慢，問題迫在眉睫，必須馬上想對策呀。由于部署采用了規範文檔，上線前也做了各種測試，于是乎，線上排查，未果，翻出實施文檔，逐條閱讀，未果，于是想起曾經一個業務系統，也碰到類似情況，後來通過各種優化得以緩解，遂有下文，《MySQL優化核心理論與實踐》。

說明：本文理論部分來源葉老師的博文，實踐部分來源工作積累和衆多熱愛MySQL技術分享的網友，整理初衷是為了更加深入地了解MySQL優化，掌握更多MySQL優化方面的技術，提升自己，回饋熱愛技術分享的所有網友。

硬體層的優化

新采購的伺服器預設跑在節能模式下，在并發通路量很大的業務場景，會導緻資料庫性能跟不上，造成大量延遲，最終将拖垮業務系統。與此同時，磁盤選擇與陣列卡設定不當也會使資料庫性能成為整個業務系統的瓶頸。

目标一：全面關閉節能模式，讓MySQL跑在高性能模式下

1.關閉CPU節能模式

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

2.關閉記憶體節能模式

找到Memory Speed選項，選擇Max Performance

找到Power C-States選項，選擇Disable

找到C1 Enhanced Mode選項，選擇Disable

目标二：關閉NUMA，讓CPU能始終高效地使用記憶體

關閉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政策不丢資料

Cache政策選擇Write Back甚至Always Write Back

陣列預讀的Read Policy選項，選擇Normal

使用RAID-10,性能高于RAID-5

使用15KRPM高速磁盤，性能高于7.2KRPM磁盤

備注：伺服器硬體設定的參數來源于IBM X3650M3

系統層的優化

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

目标一：全面提升IOPS性能，讓資料庫不再背鍋

1.配置合理的I/O排程器

機械盤配deadline，執行指令echo deadline >/sys/block/sda/queue/scheduler

固态盤配noop，執行指令echo noop >/sys/block/sda/queue/scheduler

注意sda是資料檔案所在分區

2.檔案系統盡量使用XFS，假如還在使用ext4，希望隻是過度階段

3.mount參數增加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使用傾向甚至禁掉，穩定磁盤I/O和網絡減少等待時間，讓MySQL表現更加穩定

1.vm.swappiness設為5甚至0，假如不關心發生OOM

echo 'vm.swappiness = 5' >>/etc/sysctl.conf

/sbin/sysctl -p

2.vm.dirty_background_ratio設為5，vm.dirty_ratio設為10，讓髒頁持續刷入磁盤，避免磁盤I/O瞬間寫産生TIME_WAIT

echo 'vm.dirty_background_ration = 5' >>/etc/sysctl.conf

echo 'vm.dirty_ratio = 10' >>/etc/sysctl.conf

3.net.ipv4.tcp_tw_recycle和net.ipv4.tcp_tw_reuse設為雙1，減少網絡等待時間，提高效率

echo 'net.ipv4.tcp_tw_recycle = 1' >>/etc/sysctl.conf

echo 'net.ipv4.tcp_tw_reuse = 1' >>/etc/sysctl.conf

MySQL層的優化

選對MySQL版本尤為重要，找到适合業務系統的版本，才能發揮出更大性能。運作參數亦是如此，需要反複斟酌與調校。規範schema設計與sql編寫，還有規範上線後的運維管理流程，同樣也會帶不小的收益。

目标一：選對版本，讓MySQL起跑底氣十足

1.優先推薦Oracle MySQL，越來越多的新上系統在擁抱官方5.7.x版本

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

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

目标二：調校合适的參數，讓MySQL的性能更加穩定

1.如果選擇使用Percona或MariaDB分支版本，強烈推薦開啟thread pool

2.設定default-storage-engine=innodb，innodb可以滿足99%以上的業務場景

3.設定合适的innodb_buffer_pool_size大小，單執行個體多數是innodb表，建議設定實體記憶體的50%-70%

4.設定合适的innodb_flush_log_at_trx_commit和sync_binlog值

設定雙1，不丢資料，性能較低

設定2和10，丢失一點資料，性能一般

設定雙0，資料不×××全，性能最高

5.設定innodb_file_per_table = 1，使用獨立表空間

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

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

8.設定long_query_time = 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、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編寫根據參考規範設定，有助于提高MySQL效率

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

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

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

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

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

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

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

8.多表連接配接查詢時，把過濾後的結果集小的表作為驅動表

優勢：不需要的資料不會出現，SQL查詢範圍小，執行效率高

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

10.多用複合索引，少用多個獨立索引，尤其是基數太小的列則不建議建立索引

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

目标四：管理維護的優化，讓運維更高效

1.online DDL代價太高，機器性能足夠時，建議單表實體不超過10G，單表行數不超過1億，行平均長度不超過8KB

2.不出現OOM KILL和大量使用SWAP，不必擔心MySQL程序占用過多記憶體

3.單執行個體運作中硬體資源還是比較緊張時，不要跑多執行個體

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

5.定期采集slow query log，用pt-query-digest工具進行分析，再結合Anemometer等系統進行slow query管理，以便于分析和優化

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

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

8.定期使用pt-table-checksum、pt-table-sync來檢查并修複mysql主從複制的資料差異

核心綱領：在上線之前，變更任何一個參數，都要做壓力測試，避免漏網之魚導緻MySQL出現各種CRASH。

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

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

​