介紹提高MySQL效能的7個技巧-mysql教程-PHP中文網

首頁

資料庫

mysql教程

介紹提高MySQL效能的7個技巧

jacklove

Jun 11, 2018 pm 05:25 PM

mysql

譯者註：隨著尺寸和負載的成長，MySQL的效能會趨於下降。記住這些訣竅，便可保持MySQL的流暢運作。

介紹提高MySQL效能的7個技巧

測量應用程式的方法之一是看效能。而效能的指標之一便是使用者體驗，通俗的說法就是「使用者是否需要等待更長的時間才能得到他們想要的東西」。

這個指標在不同的應用場合而有所改變。對於行動購物應用，回應時間不能超過幾秒鐘。對於員工的人力資源頁面，可能需要多花幾秒鐘的時間。

有許多關於效能如何影響使用者行為的研究：

79%的客戶不太可能回到慢速網站
47%的消費者希望網頁在2秒或更短的時間內完成載入
#40%的使用者在網站載入時間超過3秒時會放棄
頁面載入時間的1秒延遲可能會導致7%的損失，頁面瀏覽量減少11%

無論採用何種標準，都必須保持良好的應用效能。否則，用戶會抱怨(或更糟的是，轉到不同的應用程式)。影響應用程式效能的因素之一是資料庫效能。應用程式、網站和資料庫之間的互動對於建立應用程式效能的好壞至關重要。

這種互動的一個核心元件是應用程式如何查詢資料庫以及資料庫如何回應請求。無論如何，MySQL都是最受歡迎的資料庫管理系統之一。在生產環境中，越來越多的企業正在轉向使用MySQL(和其他開源資料庫)作為資料庫解決方案。

有許多設定MySQL的方法可以幫助確保資料庫對查詢作出快速回應，並使應用程式效能降低到最低限度。

以下是一些幫助優化MySQL資料庫效能的基本技巧。

最佳化技巧#1：學習如何使用EXPLAIN

使用任何資料庫所做的兩個最重要的決定是設計應用程式實體之間的關係如何映射到表(資料庫模式)，以及設計應用程式如何以所需的格式獲得所需的資料(查詢)。

複雜的應用程式可以有複雜的模式和查詢。如果想要得到應用程式所需的效能和擴充性，不能只依靠直覺來理解如何執行查詢。

應該學習如何使用EXPLAIN指令，而不是隨意的猜測和想像。此命令展示如何執行查詢，並讓您了解所期望的效能，以及查詢將如何隨著資料大小的變化而伸縮。

有許多工具–例如MySQLWorkbench–可以視覺化EXPLAIN輸出，但仍需要理解基礎知識才能理解它。

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

mysql> explain format=json select avg(k) from sbtest1 where 介紹提高MySQL效能的7個技巧 between 1000 and 2000 \G
*************************** 1. row ***************************
EXPLAIN: {
  “query_block”: {
    “select_介紹提高MySQL效能的7個技巧”: 1,
    “cost_info”: {
      “query_cost”: “762.40”
    },
    “table”: {
      “table_name”: “sbtest1”,
      “access_type”: “range”,
      “possible_keys”: [
        “PRIMARY”
      ],
      “key”: “PRIMARY”,
      “used_key_parts”: [
        “介紹提高MySQL效能的7個技巧”
      ],
      “key_length”: “4”,
      “rows_examined_per_scan”: 1874,
      “rows_produced_per_join”: 1874,
      “filtered”: “100.00”,
      “cost_info”: {
        “read_cost”: “387.60”,
        “eval_cost”: “374.80”,
        “prefix_cost”: “762.40”,
        “data_read_per_join”: “351K”
      },
      “used_columns”: [
        “介紹提高MySQL效能的7個技巧”,
        “k”
      ],
      “attached_condition”: “(`sbtest`.`sbtest1`.`介紹提高MySQL效能的7個技巧` between 1000 and 2000)”
    }
  }
}

應該查看的一個元件是「query cost」。 query cost是指MySQL根據查詢執行的總開銷來考慮這個特定查詢的代價，並且基於許多不同的因素。

簡單查詢的查詢開銷通常小於1，000。開銷在1，000到100，000之間的查詢被認為是中等開銷的查詢，而且如果每秒只運行數百個這樣的查詢(而不是數萬個)，通常會比較快。

開銷超過100，000的查詢可以當作是昂貴的。通常，當您是系統上的單一使用者時，這些查詢仍會快速運行，但您應該仔細考慮在互動式應用程式中使用此類查詢的頻率(尤其是隨著使用者數量的增長)。

當然，這些數字只是性能的一個大概的體現，但它們展示了一般原則。您的系統可能會更好地處理查詢工作負載，也可能更糟，這取決於其體系結構和配置。

決定查詢開銷的主要因素是查詢是否正確使用索引。 EXPLAIN 指令可以告訴您查詢是否使用索引(通常是因為索引是如何在資料庫中建立的，或是查詢本身是如何設計的)。這就是為什麼學會使用 EXPLAIN 是如此重要。

最佳化技巧 #2：建立正確的索引

索引透過減少查詢必須掃描的資料庫中的資料量來提高查詢效率。 MySQL中的索引用於加速資料庫中的訪問，並幫助執行資料庫約束(如 UNIQUE和FOREIGN KEY )。

資料庫索引很像圖書索引。它們被保存在自己的位置，並且包含主資料庫中已經存在的資訊。它們是指向資料所在位置的參考方法或映射。索引不會更改資料庫中的任何資料。它們只是指向資料的位置。

沒有完全適用於任何工作負載的索引。而應該始終在系統運行的查詢上下文中查看索引。

索引良好的資料庫不僅運行得更快，而且即使缺少一個索引也會使資料庫慢如蝸牛。使用EXPLAIN(如前所述)尋找缺少的索引並新增它們。但是要小心：不要加入你不需要的索引！不必要的索引會降低資料庫的速度
(請參閱關於MySQL索引最佳實務的介紹)。

最佳化技巧 #3：拒絕使用預設設定

與任何軟體一樣，MySQL有許多可設定的設置，可用於修改行為(以及最終的效能)。與任何軟體一樣，管理員忽略了許多這些可配置的設置，最終在預設模式下使用。

要從MySQL中獲得最佳效能，了解可設定的的MySQL設定是非常重要的，更重要的是將它們設定為最適合您的資料庫環境。

預設情況下，MySQL用於小規模的開發安裝，而不是生產規模。您通常希望配置MySQL以使用所有可用的記憶體資源，並允許應用程式所需的連線數量。

下面是三個MySQL效能最佳化設置，您應該始終仔細檢查：

innodb_ buffer_ pool_size:緩衝池用於存放快取資料和索引。這是使用具有大容量RAM的系統作為資料庫伺服器的主要原因。如果只運行InnoDB儲存引擎，通常會將80%的記憶體分配給緩衝池。如果您正在執行非常複雜的查詢，或者有大量的並發資料庫連接，或大量的表，可能需要將此值降低一個檔次，以便為其他操作分配更多的記憶體。

在設定InnoDB緩衝池大小時，需要確保不要設定太大，否則會導致交換。這絕對會影響資料庫效能。一個簡單的檢查方法是查看Percona Monitoring and Management中的系統概述圖中的交換活動：

##如圖所示，有時進行一些交換是可以的。但是，如果看到持續每秒1MB或更多的交換活動，則需要減少緩衝池大小(或其他記憶體使用)。

如果在第一次造訪時沒有正確地獲得innodb_ Buffer_ pool_ size的值，不用擔心。從MySQL5.7開始，便可以動態變更InnoDB緩衝池的大小，而無需重新啟動資料庫伺服器。

innodb_ log_ file_ size：這是單一InnoDB記錄檔的大小。預設情況下，InnoDB使用兩個值，這樣您就可以將這個數字加倍，從而獲得InnoDB用於確保交易持久的循環重做日誌空間的大小。這也優化了將變更應用到資料庫。設定innodb_ log_ file_ size是一個權衡的問題。分配的重做空間越大，對於寫入密集型工作負載而言，效能就越好，但是如果系統斷電或出現其他問題，崩潰恢復的時間就越長。

如何知道MySQL的效能是否受到目前InnoDB日誌檔案大小的限制？可以透過查看實際使用了多少可用的重做日誌空間來判斷。最簡單的方法是查看Percona Monitor and Management InnoDB Metrics儀表板。在下圖中，InnoDB日誌檔案的大小不夠大，因為使用的空間非常接近可用的重做日誌空間(由紅線表示)。日誌檔案的大小應該至少比保持系統最佳運作所使用的空間大20%。

#MAX_ Connections：大型應用程式連線數通常需高於預設值。不同於其它變量，如果沒有正確設定它，就不會有性能問題(本身)。相反，如果連接的數量不足以滿足您的應用程式的需要，那麼您的應用程式將無法連接到資料庫(在您的使用者看來，這就像是停機時間)。所以正確處理這個變數很重要。

如果在多個伺服器上運行多個元件的複雜應用程序，很難知道需要多少連接。幸運的是，MySQL可以很容易地看到在峰值操作時使用了多少連接。通常，您希望確保應用程式使用的最大連線數與可用的最大連線數之間至少有30%的差距。查看這些數字的簡單方法是在Percona監控和管理的MySQL概述儀表板中使用MySQL連接圖。下圖顯示了一個健全的系統，其中有大量的附加連線可用。

#需要記住的一點是，如果資料庫運作緩慢，應用程式通常會創建過多的連接。在這種情況下，您應該處理資料庫的效能問題，而不是簡單地允許更多的連接。更多的連接會使底層的效能問題變得更糟。

(注意：當將max_Connections變數設定為明顯高於預設值時，通常需要考慮增加其他參數，如表快取的大小和開啟的MySQL檔案的數量。但是，這不屬於本文討論的範疇。)

優化技巧#4：將資料庫保存在記憶體中

近年來，我們看到了向固態磁碟(SSD)的過渡。儘管SSD比旋轉硬碟快得多，但它們仍然無法與RAM中的資料相比。這種差異不僅來自儲存效能本身，還來自資料庫在從磁碟或SSD儲存中檢索資料時必須做的額外工作。

隨著最新硬體的改進，無論是在雲端運行還是管理自己的硬件，都越來越有可能將資料庫儲存在記憶體中。

更好的消息是，您不需要將所有資料庫放入記憶體中，就可以獲得記憶體中的大部分效能優勢。您只需將工作資料（最常存取的資料）集存入記憶體中。

你可能已經看到一些文章提供了一些具體的數字，說明應該將資料庫的哪個部分保存在記憶體中，從10%到33%不等。事實上，沒有「一刀切」的數字。適合記憶體的最佳效能優勢的資料量與工作負載相關。與其尋找一個特定的「萬用」數字，不如檢查資料庫在其穩定狀態下運行的I/O(通常在啟動後幾個小時)。看看READ，因為如果資料庫在記憶體中，則可以完全消除READ。寫總是需要發生的，不管你有多少內存可用。

下面，您可以在Percona監控和管理的InnoDBMetrics儀表板中的 InnoDB I/O圖中看到 I/O。

#在上面的圖表中，您可以看到高達每秒2，000個I/O操作的峰值，這表示(至少對於工作負載的某些部分)資料庫工作集不適合記憶體。

最佳化技巧#5：使用SSD儲存

如果您的資料庫不適合記憶體(即使不適合)，您仍然需要快速儲存來處理寫入操作，並在資料庫升溫時(重新啟動後)避免效能問題。如今，SSD就是快速儲存的代名詞。

出於成本或可靠性的原因，一些「專家」仍然主張使用旋轉磁碟（機械磁碟）。坦白說，當涉及到操作資料庫時，這些論點往往已經過時或完全錯誤。今天，SSD以較高的價格提供可觀的性能和可靠性。

然而，並非所有SSD都是適用的。對於資料庫伺服器，您應該使用為伺服器工作負載設計的SSD，這種SSD會對資料起到保護作用(例如，在斷電期間)。避免使用為桌上型電腦和筆記型電腦設計的商用SSD。

透過NVMe或Intel OpTan技術連接的SSD可提供最佳效能。即使作為SAN、NAS或cloud block設備遠端連接，與旋轉磁碟相比，SSD仍然具有更優越的效能。

優化技巧 #6：橫向擴展

即使是高效能的伺服器也有其限制。有兩種擴充方式：up和out。縱向擴展意味著購買更多的硬體。這可能很昂貴，而且硬體很快就會過時。橫向擴展以處理更多的負載有幾個好處：

1.可利用較小且成本較低的系統。

2.透過橫向擴展，線性擴展則更快更容易。

3.因為資料庫分佈在多台實體機器上，所以資料庫不會受到單一硬體故障點的影響。

雖然橫向擴展是有好處的，但也有一定的限制。擴充功能需要複製，例如基本的MySQL複製或Percona XtraDB Cluster，以實現資料同步。但是作為回報，可以獲得額外的性能和高可用性。如果您需要更大的擴展，請使用MySQL分片。

您還需要確保連接到叢集體系結構的應用程式能夠找到所需的資料–通常透過一些代理伺服器和負載平衡器(如ProxySQL或HAProxy)。

在規劃橫向擴展時，避免過早擴展。使用分散式資料庫往往更複雜。現代硬體和MySQL伺服器只使用一台伺服器就可以得到良好的體驗。最近發布的MySQL 8候選版本表明，它能夠在單一系統上處理200多萬個簡單查詢。

優化技巧 #7：可觀測性

設計最好的系統時要考慮到可觀察性-MySQL也不例外.。

一旦您啟動、執行並正確調整了MySQL環境，就不能只設定而不進行管理。資料庫環境會受到系統或工作負載變更的影響。準備好應對諸如流量高峰、應用程式錯誤和MySQL故障等意外。這些事情能夠而且將會發生。

當發生問題時，你需要迅速而有效地解決它們。這樣做的唯一方法是設定某種監視解決方案並對其進行適當的初始化。這使您能夠在資料庫環境在生產中運行時看到它正在發生的情況，並在出現問題時分析伺服器資料。理想情況下，系統允許您在問題發生之前或在問題發展到用戶可以看到其影響之前進行預防。

監控工具有諸如MySQL Enterprise Monitor、Monyog和 Percona Monitoring and Management (PMM)，後者俱有免費和開源的額外優勢。這些工具為監視和故障排除提供了很好的可操作性。

隨著越來越多的公司轉向開源資料庫(特別是MySQL)，以便在大規模生產環境中管理和服務其業務數據，他們將需要集中精力保持這些資料庫的最佳化和最佳運作效率。就像所有對您的業務目標至關重要的事情一樣，您的資料庫效能可能會導致或破壞您的業務目標或成果。 MySQL是一個可以為應用程式和網站提供優質的資料庫解決方案，但需要調整以滿足您的需要，並進行監視以發現和防止瓶頸和效能問題。

PeterZaitsev是Percona的共同創辦人兼首席執行官，Percona時企業級MySQL和MongoDB解決方案和服務的提供者。由O‘Reilly出版的《High Performance MySQL》是最受歡迎的MySQL效能書籍之一。 Zaitsev經常在PerconaDatabasePerformanceBlog.com上發表博客，並在世界各地的會議中發言。

本文詳細介紹了提高MySQL效能的7個技巧，更多相關內容請關注php中文網。