本文更新于2020-04-05，使用MySQL 5.7，作業系統為Deepin 15.4，

MySQL體系結構
MySQL記憶體優化
- MyISAM記憶體優化
  - key_buffer_size設定
  - 使用多索引快取
  - 調整中點插入策略
  - 調整read_buffer_size和read_rnd_buffer_size
- InnoDB記憶體優化
  - innodb_buffer_pool_size設定
  - 調整old sublist大小
  - innodb_old_blocks_time設定
  - 調整快取池數量，減少內部對快取池資料結構的爭用
  - 控制快取池重繪，延長資料快取時間，減少磁盤IO
  - InnoDB雙寫策略
- 調整用戶服務執行緒排序快取區
- 使用查詢快取
InnoDB日志優化
- innodb_flush_log_at_trx_commit設定
- 設定innodb_log_file_size，控制檢查點
- innodb_log_buffer_size設定
調整MySQL并發相關的引數
- max_connections設定
- open_files_limit設定
- back_log設定
- table_open_cache設定
- thread_cache_size設定
- innodb_lock_wait_timeout設定

MySQL體系結構

MySQL實體由一組后臺執行緒、一些記憶體塊和若干服務執行緒組成，

后臺執行緒包括：

主執行緒：主要負責將臟快取頁重繪到資料檔案，執行purge操作，觸發檢查點，合并插入緩沖區等，
IO執行緒：
- insert buffer執行緒：主要負責插入緩沖區的合并操作，
- read執行緒：負責資料庫讀操作，可配置多個讀執行緒，
- write執行緒：負責資料庫寫操作，可配置多個寫執行緒，
- log執行緒：將重做日志重繪到logfile中，
鎖執行緒：負責鎖控制和死鎖檢測，
錯誤監控執行緒：主要負責錯誤監控和錯誤處理，
purge執行緒：MySQL5.5之后用單獨的執行緒執行purge操作，

可通過SHOW ENGINE INNODB STATUS查看執行緒的狀態，

MySQL記憶體優化

記憶體優化原則：

將盡量多的記憶體分配給MySQL做快取，但要給作業系統和其他應用程式的運行預留足夠的記憶體，否則如果產生SWAP頁交換，將嚴重影響系統性能，
MyISAM的資料檔案讀取依賴于作業系統自身的IO快取，因此，如果有MyISAM表，就要預留更多的記憶體給作業系統做IO快取，
排序區、連接區等快取是分配給每個資料庫會話專用的，其默認值的設定要根據最大連接數合理分配，如果設定太大，不但浪費記憶體資源，而且在并發連接較高時會導致物理記憶體耗盡，

MyISAM記憶體優化

MyISAM存盤引擎使用索引快取（key buffer）快取索引塊，對于資料塊沒有特別的快取機制，完全依賴于作業系統的IO快取，

key_buffer_size設定

key_buffer_size決定MyISAM索引快取區的大小，它直接影響MyISAM表的存取效率，建議至少分配1/4可用物理記憶體，

通過檢查系統狀態變數可評估MyISAM快取的效率：

讀比率：key_reads/key_read_requests，一般應小于0.01，
寫比率：key_writes/key_write_requests，對于更新和洗掉特別多的應用可能接近1，對于每次更新很多行的應用就會比較小，
使用率：1-(key_blocks_unused*key_cache_block_size/key_buffer_size)，一般在0.8左右比較合適，

使用多索引快取

多索引快取的機制，可以將不同表的索引快取放到不同的key buffer中，減少session間對key buffer的競爭導致熱資料被淘汰，

創建新的索引快取（keybuffername為新建的快取名，下同）：

SET GLOBAL keybuffername.key_buffer_size = n

洗掉索引快取：

SET GLOBAL keybuffername.key_buffer_size = 0

指定表的索引快取（不指定則使用默認索引快取）：

CACHE INDEX tablename[, ...] IN keybuffername

索引預加載：

LOAD INDEX INTO CACHE tablename[, ...]

調整中點插入策略

MySQL默認使用LRU（Last Recently Used）策略來選擇要淘汰的索引資料塊，可使用中點插入策略（Midpoint Insertion Strategy）來優化索引塊淘汰演算法，其將LRU鏈被分為hot子表和warm子表兩部分，能避免偶爾被訪問的索引塊將訪問頻繁的熱塊淘汰，

可以通過設定key_cache_division_limit來控制多大比例的快取用做warm子表，默認值為100，也就是不啟用中點插入策略，
還可以通過key_cache_age_threshold設定資料塊由hot子表向warm子表降級的時間，對于有N個塊的索引快取來說，如果一個在hot子表頭部的索引塊在最后N*key_cache_age_threshold/100次快取命中未被訪問過，就會被降級到warm子表，

調整read_buffer_size和read_rnd_buffer_size

如果需要經常順序掃描MyISAM表，可以增大read_buffer_size，如果需要做排序的查詢（如ORDER BY），可以增大read_rnd_buffer_size，但需注意，二者都是按session分配的，

InnoDB記憶體優化

InnoDB快取池（buffer pool）不僅用來快取索引塊，也用來快取資料塊，

InnoDB快取池邏輯上由空閑快取塊串列（free list）、需要重繪到磁盤的快取塊串列（flush list）和正在使用的快取塊串列（LRU list）組成，

InnoDB使用的LRU演算法與MyISAM的中點插入策略LRU演算法類似，其將LRU list分為young sublist和old sublist，頁的重繪存在于flush list和LRU list中，從LRU list淘汰的資料頁會立刻放到free list中，

innodb_buffer_pool_size設定

innodb_buffer_pool_size決定InnoDB存盤引擎表資料和索引資料的最大快取池大小，在專用資料庫服務器上，可分配80%的物理記憶體，

可通過SHOW STATUS LIKE 'innodb_buffer_pool%'查看快取池的使用情況，

InnoDB快取池命中率：1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request，

調整old sublist大小

old sublist的比例由innodb_old_blocks_pct決定，其取值范圍為5-95，默認為37，

innodb_old_blocks_time設定

innodb_old_blocks_time決定了快取資料塊從old sublist轉移到young sublist的快慢，單位為毫秒，

調整快取池數量，減少內部對快取池資料結構的爭用

InnoDB會將innodb_buffer_pool_size指定大小的快取平分給innodb_buffer_pool_instances個buffer pool，

控制快取池重繪，延長資料快取時間，減少磁盤IO

在InnoDB找不到干凈的可用快取頁或檢查點被觸發等情況下，后臺執行緒就會開始把臟的快取頁回寫到磁盤檔案中，這個程序叫快取重繪，

快取重繪的快慢主要取決于兩個引數：

innodb_max_dirty_pages_pct：控制快取池中臟頁的最大比例，默認為75%，
innodb_io_capacity：代表磁盤的IO能力，決定一批重繪臟頁的數量，默認為200，

InnoDB雙寫策略

在進行臟頁重繪時，InnoDB采用雙寫（doublewrite）策略，首先將臟頁的副本寫到系統表空間的doublewrite buffer，原因是：MySQL的資料頁大小（一般是16K）與作業系統的IO資料頁大小（一般是4K）不一致，無法保證快取頁被完整、一致地重繪到磁盤，由于同步到doublewrite buffer是對連續磁盤空間的順序寫，因此開啟雙寫對性能的影響并不太大，

可通過SHOW VARIABLES LIKE '%doublewrite%'查看雙寫是否開啟，

調整用戶服務執行緒排序快取區

如果通過SHOW GLOBAL STATUS看到sort_merge_passes的值很大，可以增大sort_buffer_size來增大排序快取區，
對于無法通過索引進行連接操作的查詢，可以增大join_buffer_size，
不過需注意，二者的快取區都是面向服務執行緒分配的，如果是多表關聯的復雜查詢，還可能會分配多個表連接快取區，

使用查詢快取

查詢快取存盤SELECT查詢的文本及相應結果，如果隨后收到一個相同的查詢，直接從查詢快取中得到結果，而不再需要決議和查詢，

可以通過SHOW VARIABLES LIKE '%query_cache%'可以查看快取相關的引數，通過SHOW STATUS LIKE '%Qcache%'可以查看查詢快取的使用情況，

InnoDB日志優化

當更新資料時，InnoDB內部的操作流程大致是：

將資料讀入快取池，并對相關記錄加獨占鎖，
將UNDO資訊寫入undo表空間的回滾段中，
更新快取頁中的資料，并將更新記錄寫入重做日志快取池（另一個快取池redo buffer）中，
提交時，根據innodb_flush_log_at_trx_commit的設定，用不同的方式將重做日志快取池中的更新記錄重繪到重做日志檔案中，然后釋放獨占鎖，
后臺IO執行緒根據需要擇機將快取中更新過的資料重繪到磁盤檔案中，

LSN（Log Sequence Number）稱為日志序列號，實際上對應日志檔案的偏移量，生成公式為：新的LSN=舊的LSN+寫入的日志大小，

innodb_flush_log_at_trx_commit設定

控制將redo buffer中的更新記錄寫入到日志檔案以及將日志檔案資料重繪到磁盤的操作時機，

值為0：在事務提交時，不會立即將快取中的redo日志寫到磁盤檔案，而是每秒觸發一次，并呼叫作業系統fsync重繪IO快取，如果資料庫崩潰，資料就會丟失，
值為1（默認值）：事務提交時，立即將快取中的redo日志回寫到磁盤檔案，并呼叫作業系統fsync重繪IO快取，
值為2：事務提交時，立即將快取中的redo日志回寫到磁盤檔案，但并不馬上呼叫fsync重繪IO快取，而是每秒觸發一次，如果資料庫崩潰，只要作業系統沒有崩潰，資料就不會丟失，

設定innodb_log_file_size，控制檢查點

當一個日志檔案寫滿后，InnoDB會自動切換到另一個日志檔案，但切換時會觸發資料庫檢查點（checkpoint），這將導致InnoDB快取臟頁的小批量重繪，會明顯降低InnoDB的性能，

一般來說，平均每半小時寫滿一個日志檔案比較合適，

innodb_log_buffer_size設定

innodb_log_buffer_size決定InnoDB重做日志快取池的大小，默認是8MB，

調整MySQL并發相關的引數

max_connections設定

max_connections控制允許連接到MySQL資料庫的最大數量，默認是151，

如果狀態變數connection_errors_max_connections不為0且一直增長，說明不斷有連接請求因資料庫連接數已達到最大允許值而失敗，

open_files_limit設定

因資料庫連接也要占用檔案描述符，也需注意open_files_limit是否足夠，

back_log設定

back_log控制MySQL監聽TCP埠時的積壓請求堆疊大小，MySQL 5.6.6以后默認為50+(max_connections/5)，但最大不超過900，

table_open_cache設定

table_open_cache控制所有SQL執行執行緒可打開的表快取數量，該值應設定為：max_connections*N，N為每個連接執行關聯查詢時所涉及到的表的最大個數，

thread_cache_size設定

thread_cache_size控制MySQL快取可供重用的客戶服務執行緒的數量，

可以通過執行緒cache的失效率threads_created/connections來衡量tread_cache_size的設定是否合適，

innodb_lock_wait_timeout設定

innodb_lock_wait_timeout可以控制InnoDB事務等待行鎖的時間，默認為50ms，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/4575.html

標籤：MySQL

上一篇：自我介紹

下一篇：MySql的回顧三:流程控制函式/統計函式/分組查詢

MySQL學習筆記（20）：優化MySQL Server