譯者 | 崔皓

審校 | 孫淑娟

模式設計、索引、查詢、配置、I/O......還會出錯？遵循這10個提示，保證讓你的MySQL服務器流暢到飛起。

MySQL是世界上使用最廣泛的開源數據庫，它在業界的受歡迎程度讓其他數據庫望塵莫及。它是一個關系型數據庫管理系統，多年來都作為流行應用程序的核心。然而，在使用上可能會遇到挑戰，因此在性能提高上存在很多機會。

在過去的幾年里，MySQL也有一些重要的新發展。本文更新了Baron Schwartz??之前??提供的??一套MySQL性能調整技巧??。

下面是10個MySQL性能調優技巧。

目錄

• MySQL性能提示1：模式設計與任何其他MySQL設置一樣重要
• MySQL性能提示2：輔助索引（Secondary Key）不是你的敵人
• MySQL性能提示3：行可以從索引中獲得服務
• MySQL性能提示4 :審查與回顧
• MySQL性能提示5：可見性很重要
• MySQL性能提示6：謹慎使用調優工具
• MySQL性能提示7：I/O操作仍然昂貴
• MySQL性能提示8：利用通用表的表達式優勢
• MySQL性能提示9：注意云計算
• MySQL性能提示10：保持Replication的最新狀態

MySQL性能提示1：模式設計與任何其他MySQL設置一樣重要

模式設計是在數據庫中最應該重視的事情之一。這一數據庫設計原則，早在20世紀70年代就被引入。自MySQL從5.6版本中轉移到InnoDB作為默認存儲引擎后，模式設計變得更加重要。

為什么會這樣呢？在InnoDB中，所有的東西都是主鍵!這與InnoDB組織數據的方式有關。在InnoDB中，主鍵（Primary Key）是集群的，每一個輔助索引（Secondary Key）都會為主鍵增加一個入口指針。如果你在設計模式時沒有考慮到這一點，那么性能將受到負面影響。

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

在一些用例中，順序主鍵不是正確的選擇--這里的一個很好的例子是通用唯一標識符或UUID。

MySQL性能提示2：輔助索引（Secondary Key）不是你的敵人

輔助索引（Secondary Key）是由一個后臺進程更新的。因此，對性能的影響并不像你所期望的那樣嚴重。相反，問題是圍繞著磁盤占用的，因為增加輔助索引（Secondary Key）會增加存儲需求。

對一個沒有索引的字段進行過濾，可能會導致每次查詢運行時都要進行全表掃描。當然，這可能會導致巨大的性能影響。因此，有一個輔助索引（Secondary Key）比沒有要好。

也就是說，不應該過度添加索引，因為增加過多的索引可能不會實現性能改進。同時，這些額外的索引可能會增加你的存儲成本，而且InnoDB必須執行許多后臺操作來保持索引的更新。

MySQL性能提示3：行可以從索引中獲得服務

InnoDB可以直接從索引中找到并實際服務于行記錄，而輔助索引（Secondary Key）則指向主鍵，主鍵則包含行記錄本身。如果InnoDB緩沖池足夠大，它也可以在內存中容納大多數數據。你甚至可以使用復合鍵，這對查詢來說通常比單獨的每列鍵更有效。MySQL可以在每個表的訪問中使用一個索引，所以如果你正在運行帶有WHERE x=1和y=2這樣的子句的查詢，那么在x,y上建立聯合索引比在每個列上有單獨的索引要好。

此外，對x,y的聯合索引也可以提高以下查詢的性能。

SELECT y FROM table WHERE x=1

MySQL將使用覆蓋索引，并從內存中的索引中提供Y。

在實踐中，當你有機會的時候，你可以通過使用聯合索引來提高性能。無論何時，當你設計聯合索引時，可以通過從左到右的方式讀取索引，所以給定一個這樣的查詢。

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

那么，一個關于a,b的聯合索引將有助于查詢。但是如果查詢是下面這個格式。

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

那么，這個a，b的聯合索引就是無效的，因為違反的最左原則，也就是從左往右讀取索引，因此會導致全表掃描。總是從左邊讀取索引的想法也適用于其他一些情況。例如，給定以下查詢。

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

那么在a,b,c上的聯合索引將只讀取第一列，因為沒有通過列b過濾的WHERE子句。所以在這種情況下，MySQL可以部分地讀取索引，這比全表掃描要好，但仍然不足以獲得查詢的最佳性能。

另一個與查詢設計有關的元素是最左邊的索引方法，因為這是MySQL中常用的優化。例如，一個關于a,b,c的索引將不包括像select a,c where c=x這樣的查詢，因為該查詢不能跳過索引的第一部分，即a,b。然而，如果你有一個類似select c,count(c) where a=x and b=y group by c的查詢，它對a,b進行過濾并對c進行分組，那么a,b,c上的一個索引可以幫助進行過濾和分組。

MySQL性能提示4 :審查與回顧

僅僅擁有一輛一級方程式賽車并不能贏得比賽。如果你把一個沒有經驗的司機放在方向盤后面，而他們在第一個彎道就撞車了，那就不會贏。同樣地，你可能有地球上調整得最好的MySQL服務器，但如果你有糟糕的查詢，你的數據庫就會比它應該的慢。

你應該隨著時間的推移定期審查你的查詢設計，因為你的應用程序會隨著新功能和錯誤的修復而改變。應用程序的數據集和使用模式也可能隨著時間的推移而改變，所有這些都會影響查詢的性能。

留出時間進行查詢審查和監控查詢，執行時間是非常重要的。你可以為此使用慢速查詢日志或性能模式，但實施監控工具將幫助你獲得更好的數據。

請記住，并不總是最慢的查詢才是最需要解決的問題。例如，你可能有一個耗時30秒但每天運行兩次的查詢，與一個耗時1秒但每分鐘運行100次的查詢并存。為了取得大的勝利，你應該開始優化第二個查詢，因為從長遠來看，改進這個查詢可以節省大量的時間和資源。

MySQL性能提示5：可見性很重要

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

例如，在早期版本中，性能模式是一個瓶頸，有相當大的影響，特別是如果你有許多表。在最近的MySQL版本中，就存在許多變化，如新的數據字典，已經改善了性能，現在的版本可以有很多表，但并不會對性能造成大的影響。

大多數現代監控工具都以某種方式使用性能模式，所以一個很好的建議是查看這些工具并選擇最適合你的工具。對性能數據的可見性可能是一筆巨大的財富。

MySQL性能提示6：謹慎使用調優工具

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

例如，將innodb_buffer_pool_size設置為總內存的75%是好的經驗法則。然而，現在在數百GB的內存服務器的情況下，如果你有512GB的內存，那就會留下128GB的自由空間，而不是專門用于緩沖池，這是一種很大的浪費。

innodb_log_file_size和innodb_log_files_in_group也是根據RAM的數量來定義。在內存超過128GB的服務器上，這個設置沒有什么意義，因為它將創建64個重做日志文件（Redo log），每個2GB。這將導致128GB的重做日志（Redo log）存儲在磁盤上。在大多數情況下，不需要大的重做日志文件（Redo log），即使在最繁忙的環境中。因此，這并不是一個好的建議。

innodb_flushing_method是啟用自動配置時唯一正確配置的值。這個變量將flushing 方法設置為O_DIRECT_NO_FSYNC，這是使用Ext4或XFS文件系統時推薦的方法，因為它避免了數據的雙重緩沖。

一個好的建議是，在專用服務器上將innodb_buffer_pool_size設置為75%或80%。在擁有大量內存的服務器上，即超過128GB的服務器，在對內存消耗進行適當的分析后，將其增加到90%甚至更多。同樣，對于innodb_log_file_size和innodb_log_files_in_group  來說大多數情況下，從2GB的文件開始，監測寫日志操作。通常情況下，在確定重做日志（Redo log）的大小時，建議覆蓋大約一個小時的寫入量。

關于innodb_flush_method，對于Ext4或XFS等現代Linux文件系統，這個選項應該被設置為O_DIRECT或O_DIRECT_NO_FSYNC。

MySQL性能提示7：I/O操作仍然昂貴

MySQL和InnoDB試圖最小化它們進行的I/O操作的數量，因為訪問存儲層在應用性能方面是昂貴的。有一些設置可以影響InnoDB執行的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的操作。

一個好的建議是保持默認值，除非你知道你需要改變它們。

還值得一提的是，最新的固態硬盤是專門為交易型數據庫而優化的。西部數據就是一個例子，該公司尋求專家的幫助，以幫助他們滿足正在創建的新一輪應用的要求。

MySQL性能提示8：利用通用表的表達式優勢

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

鑒于CTEs只被實例化一次，它們在運行多個查詢的復雜事務中往往更快。另外，支持CTE遞歸，可以在SQL語言中輕松創建復雜的結構，如分層模型和系列。

MySQL性能提示9：注意云計算

對于MySQL部署，有許多不同的云選項值得考慮，從在虛擬機中實施MySQL服務器實例，到使用數據庫即服務（DBaaS）解決方案，選擇的范圍很廣。

許多這樣的服務承諾提供顯著的性能提升。在一些簡單的用例中，這種做法是可行的。然而，即使是在云端，也必須理解數據庫的基本原理，否則成本將大大增加。這種成本增加往往是通過增加更多的硬件來解決問題，而并沒有從設計上找問題。

MySQL性能提示10：保持Replication的最新狀態

近年來，圍繞著MySQL Replication進行了許多改進，在許多情況下，它無法及時同步主服務器寫入操作。在最新的MySQL主要版本中，Replication默認是并行的，這意味著多個Replication線程正在運行并試圖同時應用事務。

當然，執行效率在很大程度上取決于應用程序寫入的工作量，在大多數情況下，并行復制可以幫助復制體跟上寫入操作。你可以用replica_parallel_type和replica_parallel_workers這兩個變量來控制。使用LOGICAL_CLOCK類型，事務被并行應用，并根據時間戳追蹤依賴關系。

總的來說，MySQL是數百萬開發者的領先開源數據庫，它將繼續成為世界范圍內創建應用程序的首選平臺。通過研究圍繞模式設計、索引、調整和I/O的問題，可以極大地提高應用程序的性能。不要忘了，像轉移到云端這樣的部署方法也會有性能影響，因此要謹慎考慮。

譯者介紹

崔皓，51CTO社區編輯，資深架構師，擁有18年的軟件開發和架構經驗，10年分布式架構經驗。

原文標題：??10 more essential MySQL performance tuning tips??，作者：Tibor K?r?cz