幫助優(yōu)化MySQL數(shù)據(jù)庫性能的7個技巧 - 全文

譯者注： 隨著尺寸和負(fù)載的增長，MySQL的性能會趨于下降。記住這些訣竅，便可保持MySQL的流暢運(yùn)行。

?

測量應(yīng)用程序的方法之一是看性能。而性能的指標(biāo)之一便是用戶體驗(yàn)，通俗的說法就是“用戶是否需要等待更長的時間才能得到他們想要的東西”。

這個指標(biāo)在不同的應(yīng)用場合而有所改變。對于移動購物應(yīng)用，響應(yīng)時間不能超過幾秒鐘。對于員工的人力資源頁面，可能需要多花幾秒鐘的時間。

有很多關(guān)于性能如何影響用戶行為的研究：

79%的客戶不太可能回到慢速網(wǎng)站

47%的消費(fèi)者希望網(wǎng)頁在2秒或更短的時間內(nèi)完成加載

40%的用戶在網(wǎng)站加載時間超過3秒時會放棄

頁面加載時間的1秒延遲可能會導(dǎo)致7%的損失，頁面瀏覽量減少11%

無論采用何種標(biāo)準(zhǔn)，都必須保持良好的應(yīng)用性能。否則，用戶會抱怨(或者更糟的是，轉(zhuǎn)到不同的應(yīng)用程序)。影響應(yīng)用程序性能的因素之一是數(shù)據(jù)庫性能。應(yīng)用程序、網(wǎng)站和數(shù)據(jù)庫之間的交互對于建立應(yīng)用程序性能的好壞至關(guān)重要。

這種交互的一個核心組件是應(yīng)用程序如何查詢數(shù)據(jù)庫以及數(shù)據(jù)庫如何響應(yīng)請求。無論如何，MySQL都是最受歡迎的數(shù)據(jù)庫管理系統(tǒng)之一。在生產(chǎn)環(huán)境中，越來越多的企業(yè)正在轉(zhuǎn)向使用MySQL(和其他開源數(shù)據(jù)庫)作為數(shù)據(jù)庫解決方案。

有許多配置MySQL的方法可以幫助確保數(shù)據(jù)庫對查詢作出快速響應(yīng)，并使應(yīng)用程序性能降低到最低限度。

以下是幫助優(yōu)化MySQL數(shù)據(jù)庫性能的一些基本技巧。

優(yōu)化技巧 #1：學(xué)習(xí)如何使用 EXPLAIN

使用任何數(shù)據(jù)庫所做的兩個最重要的決定是設(shè)計(jì)應(yīng)用程序?qū)嶓w之間的關(guān)系如何映射到表(數(shù)據(jù)庫模式)，以及設(shè)計(jì)應(yīng)用程序如何以所需的格式獲得所需的數(shù)據(jù)(查詢)。

復(fù)雜的應(yīng)用程序可以有復(fù)雜的模式和查詢。如果想得到應(yīng)用程序所需要的性能和擴(kuò)展性，不能僅僅依靠直覺來理解如何執(zhí)行查詢。

應(yīng)該學(xué)習(xí)如何使用EXPLAIN命令，而不是隨意的猜測和想象。此命令展示了如何執(zhí)行查詢，并讓您了解所期望的性能，以及查詢將如何隨著數(shù)據(jù)大小的變化而伸縮。

有許多工具–比如MySQLWorkbench–可以可視化EXPLAIN輸出，但仍然需要理解基礎(chǔ)知識才能理解它。

EXPLAIN命令提供輸出的有兩種不同的格式：老式的表格式和更現(xiàn)代的結(jié)構(gòu)化JSON文檔，它提供了更多的細(xì)節(jié)(如下所示)：

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)” } } }

應(yīng)該查看的一個組件是“query cost”。query cost是指MySQL根據(jù)查詢執(zhí)行的總開銷來考慮這個特定查詢的代價，并且基于許多不同的因素。

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

開銷超過100，000的查詢可以當(dāng)作是昂貴的。通常，當(dāng)您是系統(tǒng)上的單個用戶時，這些查詢?nèi)詴焖龠\(yùn)行，但您應(yīng)該仔細(xì)考慮在交互式應(yīng)用程序中使用此類查詢的頻率(尤其是隨著用戶數(shù)量的增長)。

當(dāng)然，這些數(shù)字只是性能的一個大概的體現(xiàn)，但它們展示了一般原則。您的系統(tǒng)可能更好地處理查詢工作負(fù)載，也可能更糟，這取決于其體系結(jié)構(gòu)和配置。

決定查詢開銷的主要因素是查詢是否正確使用索引。EXPLAIN 命令可以告訴您查詢是否使用索引(通常是因?yàn)樗饕侨绾卧跀?shù)據(jù)庫中創(chuàng)建的，或者查詢本身是如何設(shè)計(jì)的)。這就是為什么學(xué)會使用 EXPLAIN 是如此重要。

優(yōu)化技巧 #2：創(chuàng)建正確的索引

索引通過減少查詢必須掃描的數(shù)據(jù)庫中的數(shù)據(jù)量來提高查詢效率。MySQL中的索引用于加速數(shù)據(jù)庫中的訪問，并幫助執(zhí)行數(shù)據(jù)庫約束(如 UNIQUE和FOREIGN KEY )。

數(shù)據(jù)庫索引很像圖書索引。它們被保存在自己的位置，并且包含主數(shù)據(jù)庫中已經(jīng)存在的信息。它們是指向數(shù)據(jù)所在位置的參考方法或映射。索引不會更改數(shù)據(jù)庫中的任何數(shù)據(jù)。它們只是指向數(shù)據(jù)的位置。

沒有完全適用于任何工作負(fù)載的索引。而應(yīng)該始終在系統(tǒng)運(yùn)行的查詢上下文中查看索引。

索引良好的數(shù)據(jù)庫不僅運(yùn)行得更快，而且即使缺少一個索引也會使數(shù)據(jù)庫慢如蝸牛。使用EXPLAIN(如前所述)查找缺少的索引并添加它們。但是要小心：不要添加你不需要的索引！不必要的索引會降低數(shù)據(jù)庫的速度
(請查看關(guān)于MySQL索引最佳實(shí)踐的介紹)。

優(yōu)化技巧 #3：拒絕使用默認(rèn)設(shè)置

與任何軟件一樣，MySQL有許多可配置的設(shè)置，可用于修改行為(以及最終的性能)。與任何軟件一樣，管理員忽略了許多這些可配置的設(shè)置，最終在默認(rèn)模式下使用。

要從MySQL中獲得最佳性能，了解可配置的的MySQL設(shè)置是非常重要的，更重要的是將它們設(shè)置為最適合您的數(shù)據(jù)庫環(huán)境。

默認(rèn)情況下，MySQL用于小規(guī)模的開發(fā)安裝，而不是生產(chǎn)規(guī)模。您通常希望配置MySQL以使用所有可用的內(nèi)存資源，并允許應(yīng)用程序需要的連接數(shù)量。

下面是三個MySQL性能優(yōu)化設(shè)置，您應(yīng)該始終仔細(xì)檢查：

innodb_ buffer_ pool_size:緩沖池用于存放緩存數(shù)據(jù)和索引。這是使用具有大容量RAM的系統(tǒng)作為數(shù)據(jù)庫服務(wù)器的主要原因。如果只運(yùn)行InnoDB存儲引擎，通常會將80%的內(nèi)存分配給緩沖池。如果您正在運(yùn)行非常復(fù)雜的查詢，或者有大量的并發(fā)數(shù)據(jù)庫連接，或大量的表，可能需要將此值降低一個檔次，以便為其他操作分配更多的內(nèi)存。

在設(shè)置InnoDB緩沖池大小時，需要確保不要設(shè)置得太大，否則會導(dǎo)致交換。這絕對會影響數(shù)據(jù)庫性能。一種簡單的檢查方法是查看Percona Monitoring and Management中的系統(tǒng)概述圖中的交換活動：

?

?

如圖所示，有時進(jìn)行一些交換是可以的。但是，如果看到持續(xù)每秒1MB或更多的交換活動，則需要減少緩沖池大小(或其他內(nèi)存使用)。

如果在第一次訪問時沒有正確地獲得innodb_ Buffer_ pool_ size的值，不用擔(dān)心。從MySQL5.7開始，便可以動態(tài)更改InnoDB緩沖池的大小，而無需重新啟動數(shù)據(jù)庫服務(wù)器。

innodb_ log_ file_ size：這是單個InnoDB日志文件的大小。默認(rèn)情況下，InnoDB使用兩個值，這樣您就可以將這個數(shù)字加倍，從而獲得InnoDB用于確保事務(wù)持久的循環(huán)重做日志空間的大小。這也優(yōu)化了將更改應(yīng)用到數(shù)據(jù)庫。設(shè)置innodb_ log_ file_ size是一個權(quán)衡的問題。分配的重做空間越大，對于寫密集型工作負(fù)載而言，性能就越好，但是如果系統(tǒng)斷電或出現(xiàn)其他問題，崩潰恢復(fù)的時間就越長。

如何知道MySQL的性能是否受到當(dāng)前InnoDB日志文件大小的限制？可以通過查看實(shí)際使用了多少可用的重做日志空間來判斷。最簡單的方法是查看Percona Monitor and Management InnoDB Metrics儀表板。在下圖中，InnoDB日志文件的大小不夠大，因?yàn)槭褂玫目臻g非常接近可用的重做日志空間(由紅線表示)。日志文件的大小應(yīng)該至少比保持系統(tǒng)最佳運(yùn)行所用的空間大20%。

?

?

MAX_ Connections：大型應(yīng)用程序連接數(shù)通常需高于默認(rèn)值。不同于其它變量，如果沒有正確設(shè)置它，就不會有性能問題(本身)。相反，如果連接的數(shù)量不足以滿足您的應(yīng)用程序的需要，那么您的應(yīng)用程序?qū)o法連接到數(shù)據(jù)庫(在您的用戶看來，這就像是停機(jī)時間)。所以正確處理這個變量很重要。

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

?

?

需要記住的一點(diǎn)是，如果數(shù)據(jù)庫運(yùn)行緩慢，應(yīng)用程序通常會創(chuàng)建過多的連接。在這種情況下，您應(yīng)該處理數(shù)據(jù)庫的性能問題，而不是簡單地允許更多的連接。更多的連接會使底層的性能問題變得更糟。

(注意：當(dāng)將max_Connections變量設(shè)置為明顯高于默認(rèn)值時，通常需要考慮增加其他參數(shù)，如表緩存的大小和打開的MySQL文件的數(shù)量。但是，這不屬于本文討論的范疇。)

優(yōu)化技巧 #4：將數(shù)據(jù)庫保存在內(nèi)存中

近年來，我們看到了向固態(tài)磁盤(SSD)的過渡。盡管SSD比旋轉(zhuǎn)硬盤快得多，但它們?nèi)匀粺o法與RAM中的數(shù)據(jù)相比。這種差異不僅來自存儲性能本身，還來自數(shù)據(jù)庫在從磁盤或SSD存儲中檢索數(shù)據(jù)時必須做的額外工作。

隨著最新硬件的改進(jìn)，無論是在云端運(yùn)行還是管理自己的硬件，都越來越有可能將數(shù)據(jù)庫存儲在內(nèi)存中。

更好的消息是，您不需要將所有數(shù)據(jù)庫都放入內(nèi)存中，就可以獲得內(nèi)存中的大部分性能優(yōu)勢。您只需將工作數(shù)據(jù)（最頻繁訪問的數(shù)據(jù)）集存入內(nèi)存中。

你可能已經(jīng)看到一些文章提供了一些具體的數(shù)字，說明應(yīng)該將數(shù)據(jù)庫的哪個部分保存在內(nèi)存中，從10%到33%不等。事實(shí)上，沒有“一刀切”的數(shù)字。適合內(nèi)存的最佳性能優(yōu)勢的數(shù)據(jù)量與工作負(fù)載相關(guān)。與其尋找一個特定的“萬能”數(shù)字，不如檢查一下數(shù)據(jù)庫在其穩(wěn)定狀態(tài)下運(yùn)行的I/O(通常在啟動后幾個小時)?？纯碦EAD，因?yàn)槿绻麛?shù)據(jù)庫在內(nèi)存中，則可以完全消除READ。寫總是需要發(fā)生的，不管你有多少內(nèi)存可用。

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

?

?

在上面的圖表中，您可以看到高達(dá)每秒2，000個I/O操作的峰值，這表明(至少對于工作負(fù)載的某些部分)數(shù)據(jù)庫工作集不適合內(nèi)存。

優(yōu)化技巧 #5：使用SSD存儲

如果您的數(shù)據(jù)庫不適合內(nèi)存(即使不適合)，您仍然需要快速存儲來處理寫操作，并在數(shù)據(jù)庫升溫時(重新啟動后)避免性能問題。如今，SSD即是快速存儲的代名詞。

出于成本或可靠性的原因，一些“專家”仍然主張使用旋轉(zhuǎn)磁盤（機(jī)械磁盤）。坦率地說，當(dāng)涉及到操作數(shù)據(jù)庫時，這些論點(diǎn)往往已經(jīng)過時或完全錯誤。今天，SSD以較高的價格提供著可觀的性能和可靠性。

然而，并非所有SSD都是適用的。對于數(shù)據(jù)庫服務(wù)器，您應(yīng)該使用為服務(wù)器工作負(fù)載設(shè)計(jì)的SSD，這種SSD會對數(shù)據(jù)起到保護(hù)作用(例如，在斷電期間)。避免使用為臺式計(jì)算機(jī)和筆記本電腦設(shè)計(jì)的商用SSD。

通過NVMe或Intel OpTan技術(shù)連接的SSD可提供最佳性能。即使作為SAN、NAS或cloud block設(shè)備遠(yuǎn)程連接，與旋轉(zhuǎn)磁盤相比，SSD仍然具有更優(yōu)越的性能。

優(yōu)化技巧 #6：橫向擴(kuò)展

即使是高性能的服務(wù)器也有其局限性。有兩種擴(kuò)展方式：up和out。縱向擴(kuò)展意味著購買更多的硬件。這可能很昂貴，而且硬件很快就會過時。
橫向擴(kuò)展以處理更多的負(fù)載有幾個好處：
1.可以利用較小且成本較低的系統(tǒng)。 ? ??
? 2.通過橫向擴(kuò)展，進(jìn)行線性擴(kuò)展更快更容易。 ? ??
3.因?yàn)閿?shù)據(jù)庫分布在多臺物理機(jī)器上，所以數(shù)據(jù)庫不會受到單個硬件故障點(diǎn)的影響。

雖然橫向擴(kuò)展是有好處的，但也有一定的局限性。擴(kuò)展需要復(fù)制，例如基本的MySQL復(fù)制或Percona XtraDB Cluster，以實(shí)現(xiàn)數(shù)據(jù)同步。但是作為回報，可以獲得額外的性能和高可用性。如果您需要更大的擴(kuò)展，請使用MySQL分片。

您還需要確保連接到集群體系結(jié)構(gòu)的應(yīng)用程序能夠找到所需的數(shù)據(jù)–通常通過一些代理服務(wù)器和負(fù)載平衡器(如ProxySQL或HAProxy)。

在計(jì)劃橫向擴(kuò)展時，避免過早地?cái)U(kuò)展。使用分布式數(shù)據(jù)庫往往更復(fù)雜?，F(xiàn)代硬件和MySQL服務(wù)器只使用一臺服務(wù)器就可以得到良好的體驗(yàn)。最近發(fā)布的MySQL 8候選版本表明，它能夠在單個系統(tǒng)上處理200多萬個簡單查詢。

優(yōu)化技巧 #7：可觀測性

設(shè)計(jì)最好的系統(tǒng)時要考慮到可觀察性-MySQL也不例外.。

一旦您啟動、運(yùn)行并正確調(diào)整了MySQL環(huán)境，就不能僅僅設(shè)置而不進(jìn)行管理。數(shù)據(jù)庫環(huán)境會受到系統(tǒng)或工作負(fù)載更改的影響。準(zhǔn)備好應(yīng)對諸如流量高峰、應(yīng)用程序錯誤和MySQL故障等意外。這些事情能夠而且將會發(fā)生。

當(dāng)發(fā)生問題時，你需要迅速而有效地解決它們。這樣做的唯一方法是設(shè)置某種監(jiān)視解決方案并對其進(jìn)行適當(dāng)?shù)某跏蓟?。這使您能夠在數(shù)據(jù)庫環(huán)境在生產(chǎn)中運(yùn)行時看到它正在發(fā)生的情況，并在出現(xiàn)問題時分析服務(wù)器數(shù)據(jù)。理想情況下，系統(tǒng)允許您在問題發(fā)生之前或在問題發(fā)展到用戶可以看到其影響之前進(jìn)行預(yù)防。

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

隨著越來越多的公司轉(zhuǎn)向開源數(shù)據(jù)庫(特別是MySQL)，以便在大規(guī)模生產(chǎn)環(huán)境中管理和服務(wù)其業(yè)務(wù)數(shù)據(jù)，他們將需要集中精力保持這些數(shù)據(jù)庫的優(yōu)化和最佳運(yùn)行效率。與所有對您的業(yè)務(wù)目標(biāo)至關(guān)重要的事情一樣，您的數(shù)據(jù)庫性能可能會導(dǎo)致或破壞你的業(yè)務(wù)目標(biāo)或成果。MySQL是一個可以為應(yīng)用程序和網(wǎng)站提供優(yōu)質(zhì)的數(shù)據(jù)庫解決方案，但需要進(jìn)行調(diào)整以滿足您的需要，并進(jìn)行監(jiān)視以發(fā)現(xiàn)和防止瓶頸和性能問題。