本文更新於2020-04-05，使用MySQL 5.7，操作系統為Deepin 15.4。 MySQL體繫結構 MySQL實例由一組後臺線程、一些記憶體塊和若幹服務線程組成。後臺線程包括：主線程：主要負責將臟緩存頁刷新到數據文件，執行purge操作，觸發檢查點，合併插入緩衝區等。 IO線程： in ...

本文更新於2020-04-05，使用MySQL 5.7，操作系統為Deepin 15.4。

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。

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