歡迎光臨
每天分享高質量文章

讓資料庫跑的更快的7個MySQL優化建議!

隨著容量和負載的增加,MySQL 的性能會日趨緩慢。這裡有七點建議能夠保證 MySQL 的平穩運行。

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

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

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

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

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

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

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

以下就是能夠幫助您優化 MySQL 資料庫性能的 7 點必備技巧:

  • 學習如何使用EXPLAIN

  • 創建正確的索引

  • 拒絕預設設置

  • 將資料庫載入記憶體中

  • 使用SSD儲存

  • 橫向擴展

  • 追求可視性

學習如何使用 EXPLAIN

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

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

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

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

建議您認真學習如何去使用 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_buffer_pool_size

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

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

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

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

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

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

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

innodb_log_file_size

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

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

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

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

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

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

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

max_connections

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

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

對於在多台服務器上運行著具有多個組件的複雜應用來說,您想獲知到底需要多少個連接是非常困難的。

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

查看這些數字的一個簡單方法是:在“Percona 監控和管理”的系統概述界面中查看使用 MySQL 連接圖。

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

還有一點需要記住:如果您的應用程式所創建的連接數量過多,通常會導致資料庫運行緩慢。

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

將資料庫載入記憶體中

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

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

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

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

您可能已經看過一些文章,有介紹將資料庫多少比例(如:10% 到 33%)載入到記憶體里。

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

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

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

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

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

使用 SSD 儲存

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

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

並非所有的固態硬碟都是同等生產的。對於資料庫服務器來說,您應該選用那些專供服務器工作負載、且能精心呵護資料的 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、資料庫、運維等。

赞(0)

分享創造快樂