讓資料庫跑的更快的7個MySQL最佳化建議！

效能是我們衡量應用的一種方式，而應用效能的一項指標就是使用者體驗，也就是平時我們常說的：“使用者需要等待超過合理的時間，才能獲得他們想要的東西嗎？”

在不同的情況和場景下，該指標會有所不同。比如說：對於移動購物應用來說，其響應時間不能超過幾秒鐘；而對於一個員工的人力資源頁面而言，其響應時間則允許比幾秒鐘更長。

因此，不管是什麼樣的標準，維持應用程式的良好效能都是至關重要的，否則就會引發使用者的抱怨（或更糟的是使用者轉而使用其他的應用）。而資料庫效能就是影響應用程式效能的因素之一。

可以說，應用程式、網站和資料庫之間的互動會直接影響到應用服務水平的確立。

這種互動的一個核心組成部分是：各種應用程式如何去查詢資料庫，以及資料庫是如何響應各種請求的。

不論是哪一種標準，MySQL 都是時下最流行的資料庫管理系統之一。越來越多的企業已將 MySQL（和其他開源的資料庫）視為其生產環境中的資料庫解決方案。

MySQL 有許多配置方法可以確保您的資料庫能夠快速地響應各種查詢，同時僅對應用程式效能造成細微的下降。

以下就是能夠幫助您最佳化 MySQL 資料庫效能的 7 點必備技巧：

• 學習如何使用EXPLAIN

• 建立正確的索引

• 拒絕預設設定

• 將資料庫載入記憶體中

• 使用SSD儲存

• 橫向擴充套件

• 追求可視性

在您對資料庫做任何設計決策時，有兩個方面非常重要：

• 應用物體之間如何被對映到各個資料表（資料庫樣式架構）上。

• 應用程式如何獲取（查詢）到它們所需格式型別的資料。

複雜的應用程式必然有著複雜的樣式架構和查詢。如果您想讓自己的各種應用具備所需的效能和擴充套件性，那就不能單純依靠直覺去理解各種查詢的執行機制。

建議您認真學習如何去使用 EXPLAIN 命令，而不是憑空猜想。該命令會向您展示查詢是如何被執行的；並深入地演示有關效能的真實表現情況，以及查詢是如何伴隨著資料量的變化進行擴充套件的。

像許多 MySQLWorkbench 之類的工具都可以將 EXPLAIN 的輸出視覺化地展示給您，不過您仍然需要瞭解與它相關的基本知識。

EXPLAIN 命令的輸出有兩種不同的格式：老式的表格形式和較新的、能夠提供更為細節化的、結構化的 JSON 檔案。

如下所示：

mysql> explain format=json select avg(k) from sbtest1 where id between 1000 and 2000 G
*************************** 1. row ***************************
EXPLAIN: {
  “query_block”: {
    “select_id”: 1,
    “cost_info”: {
      “query_cost”: “762.40”
    },
    “table”: {
      “table_name”: “sbtest1”,
      “access_type”: “range”,
      “possible_keys”: [
        “PRIMARY”
      ],
      “key”: “PRIMARY”,
      “used_key_parts”: [
        “id”
      ],
      “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”: [
        “id”,
        “k”
      ],
      “attached_condition”: “(`sbtest`.`sbtest1`.`id` between 1000 and 2000)”
    }
  }
}

其中您需要重點檢視的部分是：查詢成本。查詢成本是指基於查詢執行的總體成本和許多不同的因素考慮，MySQL 判定一次查詢所付出的花銷。

一般簡單查詢的成本會小於 1000。介於 1000 到 100,000 的成本值被視為中等成本的查詢。

因此，如果您每秒只是執行上百個（並非幾萬個）此類查詢的話，一般速度應該比較快。 

查詢成本如果是超過 100,000 的話，那麼開銷就比較大了。而通常當您的系統只有單個使用者時，此類查詢仍然可以被迅速地執行。

當然，您需要仔細考慮一下在互動式應用程式中，使用此類查詢的頻率（尤其在使用者數量增長的時候）。

雖然這些只是大概的數字，但是它們卻能夠反映出總體的規律。實際情況下，您的系統在處理查詢請求負載時會表現得更好還是更糟，完全取決於自身的架構與配置。

決定查詢成本的一個首要因素是：查詢是否正確地使用了各種索引。如果您沒有使用索引進行查詢，那麼會被 EXPLAIN 命令所指出來，通常源於索引是如何在資料庫中被建立的，以及查詢本身是如何被設計的。

這也正是為什麼 EXPLAIN 值得去好好學習和使用的原因。

索引是透過減少在資料庫裡查詢時，必須掃描的資料量來提高查詢的自身效率。

在 MySQL 中，索引被用於加快對資料庫的訪問，並有助於遵循資料庫的各種約束（例如 UNIQUE 和 FOREIGN KEY）。

資料庫索引就像書的索引一樣，它們的位置資訊被儲存，並且包含有資料庫的主要資訊。

它們是資料位置的一種參考方法或對映，因此索引並不會更改資料庫中的任何資料。它們只是指向資料存放的位置而已。

不過，索引並不總能匹配上任何的負載請求。在系統執行中，您應當不斷為查詢的背景關係環境建立各種索引。

雖然有著良好索引的資料庫會執行更快速，但是如果出現單個索引的缺失，則會拖慢整個資料庫的效率。

因此，我們需要使用 EXPLAIN 來查詢缺失的索引，並將其新增上去。

需要註意的是：不要新增您所不需要的索引，因為不必要的索引會反過來拖慢資料庫。

就像其他任何軟體那樣，MySQL 也能透過各種可配置的設定，來修改其行為並最終最佳化其效能。

同時這些配置的設定經常會被管理員所忽略，並一直保持著預設值的狀態。

為了讓 MySQL 獲得最佳的效能，瞭解如何配置 MySQL，以及將它們設定為最適合您的資料庫環境的狀態是非常重要的。

在預設情況下，MySQL 是針對小規模的釋出、安裝進行調優的，而並非真正的生產環境規模。

因此，通常您需要將 MySQL 配置為使用所有可用的記憶體資源，並且能允許您的應用程式所需的最大連線數。

這裡有三個有關 MySQL 效能最佳化的設定，值得您去仔細地配置：

資料和索引被用作快取的緩衝池。當您的資料庫伺服器有著大量的系統記憶體時，可以用到該設定。

如果您只執行 InnoDB 儲存引擎，那麼您通常可以分配 80％ 左右的記憶體給該緩衝池。

而如果您要執行非常複雜的查詢或者您有大量的併發資料庫連線，亦或您有非常大的資料表的情況，那麼就可能需要將此值下調一個等級，以便為其他的呼叫分配更多的記憶體。

您在設定 InnoDB 緩衝池大小的時候，要確保其設定既不要過大，也不要頻繁引起交換（swapping），因為這些絕對會降低您的資料庫效能。有一個簡單的檢查方法就是在“Percona 監控和管理”。 

如圖所示，如果你看到有大於 1MB 每秒的持續交換活動的話，您就需要減少緩衝池的大小了，或者使用其他的記憶體。

如果您一開始並沒有將 innodb_buffer_pool_size 的值設定正確，也不必擔心。

從 MySQL5.7 開始，您可以動態地改變 InnoDB 緩衝池的大小，而不需要重新啟動資料庫伺服器了。 

這是指單個 InnoDB 日誌檔案的大小。預設情況下，InnoDB 使用兩個值，這樣您就可以透過將其增加一倍，來讓 InnoDB 獲得迴圈的重做日誌空間，以確保交易的永續性。這同時也優化了對資料庫的寫入效能。

設定 innodb_log_file_size 的值是很值得推敲的：如果分配了較大的重做空間，那麼對於寫入密集型的工作負載來說效能會越好。

但是如果您的系統遭受到斷電或其他問題導致崩潰的時候，那麼其恢復時間則會越長。

您可能會問：怎麼才能知道自己的 MySQL 效能是否受限於當前的 InnoDB 日誌檔案大小呢？

您可以透過檢視未實際使用的重做日誌空間大小來判定。最簡單的方法就是檢視“Percona 監控和管理”的 InnoDB 指標儀錶板。

在下圖中，InnoDB 的日誌檔案不夠大，使用空間已經屢屢接近於可用的重做日誌空間了，如紅線所示：

因此，您的日誌檔案應該至少比使用量大 20％，從而保持系統處於最佳的效能狀態。

大型應用程式通常需要比預設數量多得多的連線。不同於其他的變數，如果您沒能將該值設定正確，您就會碰到效能方面的問題。

也就是說，如果連線的數量不足以滿足您的應用需求，那麼應用程式將根本無法連線到資料庫，在使用者看來就像宕機了一樣。由此可見，將它設定正確是非常重要的。

對於在多臺伺服器上執行著具有多個元件的複雜應用來說，您想獲知到底需要多少個連線是非常困難的。

幸運的是，MySQL 能夠在峰值操作時輕易地獲悉所用到的連線數量。通常，您需要確保在應用程式所使用到的最大連線數和可用的最大連線數之間至少有 30％ 的差額。

檢視這些數字的一個簡單方法是：在“Percona 監控和管理”的系統概述介面中檢視使用 MySQL 連線圖。

下圖顯示了一個健康的系統，它有著足夠數量的可用額外連線。

還有一點需要記住：如果您的應用程式所建立的連線數量過多，通常會導致資料庫執行緩慢。

在這種情況下，您應該在資料庫效能上做文章，而不是簡單地允許建立更多的連線。更多的連線會使得潛在的效能問題更加惡化。

近年來，出現了固態硬碟（SSD）方向上的轉變。儘管固態硬碟比傳統機械旋臂硬碟快得多，但是它們仍然敵不過將資料存在記憶體裡。

這種差別不僅來自於儲存效能本身，還來自於資料庫從磁碟或 SSD 裡存取資料時所產生的額外工作。

隨著近年來硬體技術的改進，不管您是執行在雲端，還是管理著自己的硬體，將資料庫載入記憶體已經變得可行。

更令人振奮的是：您並不需要將整個資料庫載入記憶體以獲得其效能優勢，您只需要將最頻繁訪問的資料集放入其中便可。

您可能已經看過一些文章，有介紹將資料庫多少比例（如：10％ 到 33％）載入到記憶體裡。

而事實上並不存在著“一刀切”的規律，資料的訪問量決定著載入記憶體所獲得的最佳效能的提升程度。

您與其去尋找某個特定的“神奇”數字，不如去檢查資料庫達到穩定執行狀態時的 I/O（通常是在它開始執行的幾個小時之後）。

請檢視一下資料的讀取，因為如果您的資料庫已載入到記憶體裡的話，那麼讀取會完全結束；而只要有記憶體可用，寫入操作總是會發生的。

下圖是“Percona 監控和管理”的 InnoDB 指標儀錶板中的 InnoDB I/O圖：

如上圖所示，那些峰值高達每秒 2,000 的 I/O 操作表明（至少是流量負載的一部分）它們與載入記憶體中資料庫的資料集並不相配。 

無論您的資料庫是否已被載入記憶體，您都需要使用快速儲存來處理寫入操作，並且避免在資料庫啟動後（重啟之後）出現效能問題。這裡的快速儲存就是指固態硬碟。

一些所謂的“專家”仍在基於成本和可靠性的基礎上，主張使用機械旋臂硬碟。坦率地說，當涉及到資料庫操作時，這些建議往往是過時的或是完全錯誤的。現如今，固態硬碟的效能已經非常卓越、可靠且價格低廉了。

並非所有的固態硬碟都是同等生產的。對於資料庫伺服器來說，您應該選用那些專供伺服器工作負載、且能精心呵護資料的 SSD。

例如：防止斷電損壞的，而避免使用那些專為臺式和膝上型電腦設計的商用固態硬碟。

透過 NVMe 或英特爾 Optane 技術來直接連線的 SSD 往往能夠提供最佳的效能。

即使遠端連線到 SAN、NAS 或雲端的塊裝置上，固態硬碟也能比機械旋臂硬碟提供更為優越的效能。

即使是效能最高的伺服器也有侷限性。業界一般用兩種方法來進行擴充套件：縱向和橫向。

縱向擴充套件意味著購買更多的硬體。這樣做不但成本昂貴，而且硬體折舊速度快。

而橫向擴充套件，則在處理負載方面有如下幾點優勢：

• 您可以從更小型、成本更低的系統中獲益。

• 橫向擴充套件使得系統的線性擴充套件更方便、更快捷。

• 由於資料庫會橫跨增長到多個物理機上，橫向擴充套件在保護資料庫的同時，消除了硬體單點故障。

儘管橫向擴充套件有著諸多優勢，不過它還是具有一定的侷限性。橫向擴充套件需要資料複製，例如基本的 MySQL Replication 或是用於資料同步的 Percona XtraDB 群集。

但是作為回報，您也會獲得更高的效能和可用性。如果您需要更高階的擴充套件性，那麼請考慮使用 MySQL 分片（sharding）。

另外，您還需要確保連線到群集架構的應用程式可以找到它們所需的資料。這通常是透過諸如 ProxySQL 或 HAProxy 的一些代理伺服器和負載平衡器來實現的。

當然，過早地規劃橫向擴充套件，會增加分散式資料庫的複雜性。最近釋出的 MySQL 8 候選版本已聲稱自己能夠在單一的系統上處理超過 200 萬個簡單查詢。

可視性是系統設計的最佳境界，MySQL 也不例外。

一旦完成了 MySQL 環境的搭建、執行並調優，您千萬不要認為已經萬事大吉了。

資料庫環境既會受到來自系統更改或流量負荷的影響，也會遇到例如流量高峰、應用程式錯誤以及 MySQL 自身的各種問題。

為了快速、有效地解決各種問題，您需要建立和實施一些監控機制，從而能獲悉資料庫環境的狀態，併在出現錯誤時及時分析伺服器上的資料。

因此理想情況就是在系統出現問題或是被使用者所察覺之前就做到防範於未然。

常用的監測工具有：

• MySQL企業監控器（Enterprise Monitor）。

• Monyog。

• 具有免費與開源版本的 Percona 監控和管理（PMM）。

這些工具在監控和故障排除方面提供了很好的操作可視性。

隨著越來越多的公司在大規模生產環境中使用開源的資料庫（特別是MySQL）來管理和服務他們的業務資料，他們需要把工作重心放在保持資料庫的調優和執行效率上。

MySQL 的確是一款能夠提升您的應用程式和網站效能的優秀資料庫，當然您需要透過對它進行調整，以滿足業務需求，監測、發現並防止任何瓶頸和效能方面的問題。

來自：51CTO技術棧（微訊號：blog51cto）

作者：Peter Zaitsev，陳峻 編譯

編輯：陶家龍、孫淑娟

來源：https://www.infoworld.com/article/3234637/sql/7-keys-to-better-mysql-performance.html

陳峻（Julian Chen） ，有著十多年的 IT 專案、企業運維和風險管控的從業經驗，日常工作深入系統安全各個環節。作為 CISSP 證書持有者，他在各專業雜誌上發表了《IT運維的“六脈神劍”》、《律師事務所IT服務管理》 和《股票交易網路系統中的安全設計》等論文。他還持續分享並更新《廉環話》系列博文和各種外文技術翻譯，曾被(ISC)2 評為第九屆亞太區資訊保安領袖成就表彰計劃的“資訊保安踐行者”和 Future-S 中國 IT 治理和管理的 2015 年度踐行人物。

---

●本文編號262，以後想閱讀這篇文章直接輸入262即可

●輸入m獲取到文章目錄

推薦↓↓↓

 

Web開發

更多推薦《18個技術類公眾微信》

涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。

贊賞

長按二維碼向我轉賬

受蘋果公司新規定影響，微信 iOS 版的贊賞功能被關閉，可透過二維碼轉賬支援公眾號。

即將開啟”“小程式

取消
開啟