隨著負載和文件大小的增長，性能往往會降低。記住以下的7個關鍵點，讓你的MySQL輕松保持平穩運行。

測量應用程序的方式之一是測量它的性能。用戶體驗是衡量應用程序性能的一個指標，這就意味著用戶是否能在合理的時間內獲得所需的內容。

有很多研究都表明，性能對用戶的行為有很大的影響：

• 79%的用戶表示不太可能再次打開一個緩慢的網站;
• 47%的用戶期望網頁能在2秒鐘以內加載;
• 40%的用戶表示如果加載時間超過三秒鐘，就會放棄這個網站;
• 頁面加載時間延遲一秒可能導致轉換損失7%，頁面瀏覽量減少11%。

無論標準是什么，都要保持良好的應用性能是非常必要的。否則，用戶就會抱怨(或轉到另一個應用程序)。影響應用程序性能的一大因素就是數據庫性能。應用程序、網站和數據庫之間的交互對應用程序性能至關重要。

這種交互的核心部分是應用程序如何查詢數據庫以及數據庫對請求的響應。無論從哪一方面來說，MySQL都是最受歡迎的數據庫管理系統之一。很多企業正在將MySQL(和其他開源數據庫)作為其生產環境中的數據庫解決方案。

有很多配置MySQL的方法可以幫助確保您的數據庫快速響應查詢，并且減少應用程序性能下降。

以下是幫助您優化MySQL數據庫性能的一些重要技巧。

MySQL優化關鍵1：了解如何使用EXPLAIN

對于數據庫，您做出的最重要的兩個決策分別是：

1. 設計應用程序實體之間的關系如何映射到表(數據庫模式)中
2. 設計應用程序如何以所需格式(查詢)獲取所需的數據。

復雜的應用程序可能具有復雜的查詢和模式。如果您要獲得應用程序所需的性能和擴展性，不能僅僅直觀的來了解查詢是如何執行的。

您應該學習如何使用EXPLAIN命令。此命令向您展示了應該如何執行查詢，并讓您深入了解可以預期的性能以及查詢如何隨著數據大小的變化而縮放。

類似于MySQL Workbench的工具，都可以為您顯示EXPLAIN輸出，但您仍然需要學習基礎知識以理解它。

EXPLAIN命令提供輸出有兩種不同格式：舊式表格格式和更現代化的結構化JSON文檔，后者能提供更多的細節(如下所示)：

對于一個組件來說應該關注的是“查詢成本”。查詢成本是指基于許多不同的因素上，MySQL在查詢執行的總體成本考慮了該特定查詢成本。

簡單查詢的查詢成本通常低于1000。成本在1000到100000之間的查詢被視為中等成本查詢，如果您每秒只運行數百個這樣的查詢(而不是數萬)，通常認為是快速的。

超過100000的查詢認為是高成本查詢。通常，當您是系統上的單個用戶時，這些查詢仍然運行得很快，但是必須要考慮到在交互式應用程序中使用這些查詢的頻率(尤其是隨著用戶數量的增長)。

雖然這都是一些大致的數字，但是它們表現出了一般原則。體系結構和配置可能會影響系統的處理查詢工作負載。

確定查詢成本的主要因素是查詢是否使用正確索引。 EXPLAIN命令可以告訴您查詢是否要用索引。這就是為什么學習使用EXPLAIN 的重要原因。

MySQL優化關鍵2：創建正確的索引

索引可以減少查詢必須掃描數據量來提高查詢性能。 MySQL中的索引用于加速數據庫中的訪問，并幫助實施數據庫約束(例如UNIQUE和FOREIGN KEY)。

數據庫索引很像書籍索引。它們保存在自己的位置，并且包含已經在主數據庫中的信息。它們是一種數據所在的參考方法。索引不會更改數據庫中的任何數據，只是指向數據的位置。

在系統運行查詢中，您應該始終查看索引。

一個缺失的索引也可能會使數據庫運行速度速度降低。但要不要添加不需要的索引!不必要的索引會減慢數據庫運行速度。

MySQL優化關鍵3：不要使用默認模式!

像任何軟件一樣，MySQL有許多可配置的設置，可用于修改行為。但是管理員忽略了許多可配置的設置，始終在默認模式下運行。

為了獲取MySQL的最佳性能，了解可配置設置是非常重要的，更重要的是將它們設置為最適合您的數據庫環境。

默認情況下，MySQL適合于小規模開發安裝，而不是用于生產規模。您通常要配置MySQL，以使用可用的所有內存資源，并允許應用程序所需的連接數。

這里有三個MySQL性能調優設置：

(1) innodb_buffer_pool_size：緩沖池是緩存數據和索引的地方。這是使用具有大量RAM的系統作為數據庫服務器的主要原因。如果您只運行InnoDB存儲引擎，通常會為緩沖池分配大約80%的內存。如果運行非常復雜的查詢、有大量的并行數據庫連接或者有大量的表，那么可能需要將此值降低一個級別，為其他的運行分配更多內存。

當您設置InnoDB緩沖池大小時，不要將其設置得太大否則會導致互換。這絕對會破壞數據庫性能。一個簡單的檢查方法是查看Percona監控和管理系統概述圖中的交換活動：

如圖所示，一些交換是非常頻繁的。如果您看到持續的交換活動為每秒1MB或更多，那么將需要減少緩沖池大小(或其他內存使用)。

如果第一次沒有獲得innodb_buffer_pool_size的正確值，不用擔心。從MySQL 5.7開始，可以動態更改InnoDB緩沖池的大小，無需重新啟動數據庫服務器。

(2) innodb_log_file_size：這是一個單獨的InnoDB日志文件大小。默認情況下，InnoDB使用兩個值，以便您可以將此數字加倍，以獲取循環重做日志空間的大小，確保事務持久運行。這也優化了應用對數據庫的更改。設置innodb_log_file_size是一個需要權衡的問題，分配的重做空間越大，寫入密集型工作負載的性能越好，但如果系統遇到電源丟失或其他問題，花費的恢復時間也越長。

如何知道MySQL性能受當前InnoDB日志文件大小的限制呢?可以通過查看實際使用的重做日志空間來判斷。最簡單的方法是查看Percona Monitoring and Management InnoDB Metrics儀表板。在下圖中，InnoDB日志文件大小不夠大，因為使用的空間非常接近可用的重做日志空間(由紅線表示)。日志文件大小應至少比用于保持系統執行最佳性能的空間大20%。

(3) max_connections：大型應用程序通常需要比默認的連接數量多得多。與其他變量不同，如果不正確設置，就不會出現性能問題(本質上)。相反，如果連接數量不足以滿足應用需求，那么您的應用程序將無法連接到數據庫(這對用戶來說看起來就像停機了)。獲取這個政權變量是非常重要的。

在多個服務器上運行許多組件的復雜應用程序中，可能難以知道需要多少連接。但幸運的是，MySQL可以很容易地看到在峰值操作時使用了多少個連接。通常，為確保應用程序使用的最大可用連接數比最大連接數至少大30%。查看這些數字的簡單方法是在Percona監控和管理的MySQL概述儀表板中使用MySQL連接圖。下圖顯示了一個健康的系統，其中有很多額外的連接可用。

要記住的一點是，如果您的數據庫運行緩慢，應用程序通常會創建過多的連接。在這種情況下，您應該處理數據庫性能問題，而不是簡單地允許更多的連接。過多的連接可能會使基礎性能問題更糟。

(注意：當您將max_connections變量設置為顯著高于默認值時，通常需要考慮增加其他參數，如表緩存的大小和MySQL允許的打開文件數)

MySQL優化關鍵4：將數據庫保存在內存中

近年來，我們看到了固態硬盤(SSD)的轉型。即使SSD比旋轉硬盤驅動器要快得多，但是它們仍然與RAM中的數據不兼容。這中差異不僅來自于存儲性能本身，還來自數據庫在從磁盤或SSD存儲中檢索數據時必須執行的其他工作。

隨著硬件改進，無論您是在云端運行還是管理自己的硬件，都越來越有可能將您的數據庫存儲在內存中 -。

更好的消息是，您不需要將所有數據庫都裝入內存，只需將常訪問的工作數據集合放到內存中即可。

檢查數據庫在穩定狀態下運行的I / O數量(通常在啟動后幾個小時)。下圖您可以在Percona監控和管理的InnoDB Metrics儀表板上的InnoDBI / O。

在上圖中，您可以看到峰值高達每秒2000個I / O，這表明(至少對于工作負載的某些部分)，數據庫工作集與內存不匹配。

MySQL優化關鍵5：使用SSD存儲

如果您的數據庫不適合內存，但仍然需要快速存儲來處理寫入，并避免數據庫加速(重新啟動之后)時出現性能問題。 這些快速存儲意味著需要使用SSD。

由于成本或可靠性原因，一些“專家”仍然主張使用旋轉磁盤。但在操作數據庫中，這些觀點往往是過時的或錯誤的。今天，SSD在友好的價格上提供了令人印象深刻的性能和可靠性。

然而，不是所有的SSD都是相同的。對于數據庫服務器，您應該使用專為服務器工作負載設計的SSD。

一種直接通過NVMe或Intel Optane技術直接連接的SSD可提供最佳性能。即使作為SAN，NAS或云塊設備進行遠程連接，與旋轉磁盤相比，SSD仍然具有優異的性能。

MySQL優化關鍵6：向外擴展

即使是性能最好的服務器也有局限性。有兩種擴展方式：up和out。up意味著購買更多的硬件，但硬件很貴且很快就會過時。out有幾個好處：

• 可以利用更小、成本更低的系統。
• 通過向外擴展能更快更容易的線性放縮。
• 由于數據庫分布在多臺物理機上，因此數據庫不會收到單椅硬件故障的影響。

雖然向外擴展有優勢，但也有一定的局限性。味了數據同步，擴展需要復制，例如基本的MySQL或Percona XtraDB集群復制。

您還需要確保連接到集群架構的應用程序可以找到所需的數據，通常要通過一些代理服務器和負載平衡器來實現，如ProxySQL或HAProxy。

在計劃擴展的同時，要避免過早的擴張，使用分布式數據庫往往更復雜。

MySQL優化關鍵7：擁有可觀察性

最好的系統在設計時要考慮到可觀察性。

您將MySQL環境設置好、運行并正確調整之后，也不能就將它放置不管，數據庫環境可能受到系統或工作負載更改的影響。為流量達到峰值、應用程序錯誤和MySQL故障等情況做好準備。

當這些情況發生時，你需要快速有效地解決它們。實現這一點的唯一方法是設置一些監控解決方案并進行正確的檢測。這可以讓您看到數據庫環境中正在運行的情況，并在出現問題時分析錯誤。理想情況下，系統能在發生事件之前進行攔截。

MySQL Enterprise Monitor，Monyog和Percona監控和管理(PMM)都是不錯的監控工具，具有免費和開源的優勢。這些工具為監控和故障排除提供了良好的操作可見性

隨著越來越多的公司轉向開源數據庫(特別是MySQL)，以此來管理和服務于大規模生產環境中的業務數據，他們需要專注于保持這些數據庫的調整和運行的最佳效率。數據庫性能可能會導致或破壞您的業務目標，MySQL為您的應用程序和網站提供優質的數據庫解決方案，但要根據您的需求進行調整，以滿足您的需求并進行監控、查找、防止瓶頸和性能問題。

（本文轉自