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
/sbin/sysctl -p

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
/sbin/sysctl -p

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。

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

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

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

發表時間：2018年3月8日17時