技術更新！10個MySQL效能調優技巧

1. 模式設計很重要
2. 輔助索引（Secondary Key）
3. 行可以從索引中獲得服務
4. 審查與回顧
5. 可見性很重要
6. 謹慎使用調優工具
7. I/O操作仍然昂貴
8. 利用通用表的表示式優勢
9. 注意雲端計算
10. 保持Replication的最新狀態

1、模式設計很重要

模式設計是在資料庫中最應該重視的事情之一。自MySQL從5.6版本中轉移到InnoDB作為預設儲存引擎後，模式設計變得更加重要。

在InnoDB中，所有東西都是主鍵，這與InnoDB組織資料方式有關。在InnoDB中，主鍵（Primary Key）是叢集的，每一個輔助索引（Secondary Key）都會為主鍵增加一個入口指標。在設計模式時如沒有考慮到這點，效能將受到負面影響。

資料使用B樹索引儲存，以有序的方式插入資料（即準序列值）防止主鍵碎片化，從而減少尋找葉節點所需的I/O操作。

2、輔助索引（Secondary Key）

輔助索引（Secondary Key）是由一個後臺程式更新，對效能影響並不大。主要影響磁碟空間佔用，即增加輔助索引會增加儲存需求。

對一個沒有索引的欄位進行過濾，可能會導致每次查詢執行時都要進行全表掃描。因此， 過度新增索引不會實現效能改進反而會增加儲存成本，而且InnoDB必須執行許多後臺操作來保持索引的更新。

3、行可以從索引中獲得服務

InnoDB可以直接從索引中找到並服務於行記錄，而輔助索引（Secondary Key）指向主鍵，主鍵包含行記錄本身。如果InnoDB緩衝池足夠大，可以在記憶體中容納大多數資料。甚至可以使用複合鍵，這比單獨的每列鍵更有效。 

MySQL可以在每個表的訪問中使用一個索引，如果正在執行帶有WHERE x=1和y=2這樣的子句的查詢，那麼在x,y上建立聯合索引比在每個列上有單獨的索引要好。

SELECT y FROM table WHERE x=1

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE a=1 and c=2

4、審查與回顧

定期審查設計，因為應用程式會隨著新功能和錯誤的修復而改變。應用程式的資料集和使用模式會隨著時間的推移而改變，這些都會影響查詢的效能。

定期審查執行時間非常重要，使用慢速查詢日誌或效能模式，或使用監控工具可以快速獲取資料。

當然，並不是最慢的查詢才是最需要解決的問題。例如，一個耗時30秒但每天執行兩次的查詢，與一個耗時1秒但每分鐘執行100次的查詢並存。真正應該最佳化的是第二個查詢，因為最佳化這個查詢可以節省大量的時間和資源。

5、可見性很重要

監測是效能調整的關鍵因素之一。如果不瞭解當前的工作負載和模式，就很難給出具體的建議。近年來，MySQL改進了對低階別的MySQL/InnoDB指標的暴露，這有助於瞭解工作負載。

在早期版本中，效能模式是一個瓶頸，有相當大的影響，特別是如果你有許多表。在最近的MySQL版本中，就有明顯變化，如新的資料字典，已經改善了效能，即使有很多表，也不會對效能產生大的影響。

6、謹慎使用調優工具

調優工具給出的建議在大多數情況下是有效的。然而，每個工作負載和每個模式有所不同。在某些情況下，調優工具的建議並不奏效，在相信這些建議時，謹慎行事是明智的選擇。對於MySQL而言，可以對配置進行如下更改。

7、I/O操作仍然昂貴

innodb_io_capacity和innodb_io_capacity_max是與後臺Flushing的I/O運算元量有關的變數。許多客戶增加這些設定的值，以利用現代固態硬碟的優勢，它可以在相對較低的延遲下提供非常高的I/O容量。雖然看上去很合理，但增加I/O容量設定會導致一些問題。

第一個問題是透過使InnoDB過快地重新整理髒頁導致效能下降，從而減少了“被重新整理前多次修改一個頁面的機會”。將髒頁保留在記憶體中可以大大減少將資料寫入儲存的I/O操作。

其次，固態硬碟在出現效能下降之前有一個預期的寫入次數。因此，增加寫操作的數量會影響你的固態硬碟的壽命，即使你使用的是高階硬碟。

雖說雲主機最近很流行，在雲中執行MySQL服務例項也是可行的。然而，雲中的伺服器往往會有I/O限制，或者會對使用更多的I/O收取更多的費用。透過瞭解這些限制，你可以仔細配置這些引數，以確保不達到這些限制，並使I/O操作最小化。

提到innodb_lru_scan_depth也很重要，因為這個設定控制了緩衝池LRU頁面列表中，頁面清潔器執行緒在多遠的位置掃描髒頁。如果你有一個大的緩衝池和許多緩衝池例項的重寫工作負載，你可以透過減少這個變數來減少I/O的操作。

一個好的建議是保持預設值。

8、利用通用表的表示式優勢

MySQL 8.0引入通用表的表示式（CTE），可以避免建立派生表的巢狀查詢。這個功能允許建立一個自定義查詢並引用結果，就好像是一個臨時表或一個檢視一樣。不同的是，CTEs可以在一個事務中被多次引用，而不需要明確地建立和刪除它們。

在執行多個查詢的複雜事務中往往更快。支援CTE遞迴，可以在SQL語言中輕鬆建立複雜的結構，如分層模型和系列。

9、注意雲端計算

對於MySQL部署，有許多不同的雲選項值得考慮，從在虛擬機器中實施MySQL伺服器例項，到使用資料庫即服務（DBaaS）解決方案，選擇的範圍很廣。 即使是在雲端，也必須理解資料庫的基本原理，否則成本將大大增加。往往透過增加更多硬體來解決問題，並未從設計上找問題。

10、保持Replication的最新狀態

近年來，圍繞著MySQL Replication進行許多改進，在多數情況下無法及時同步主伺服器寫入操作。在最新的MySQL主要版本中，Replication預設是並行的，即多個Replication執行緒正在執行並試圖同時應用事務。

執行效率很大程度上取決於應用程式寫入的工作量，在大多數情況下，並行複製可以幫助複製體跟上寫入操作。可以用replica_parallel_type和replica_parallel_workers這兩個變數來控制。使用LOGICAL_CLOCK型別，事務被並行應用，並根據時間戳追蹤依賴關係。

總體來說，MySQL是數百萬開發者的領先開源資料庫，它將繼續成為世界範圍內建立應用程式的首選平臺。透過研究圍繞模式設計、索引、調整和I/O的問題，可以極大地提高應用程式的效能。當然轉移到雲端部署方法不同也會有效能差異