隨著負(fù)載和文件大小的增長(zhǎng)，性能往往會(huì)降低。記住以下的7個(gè)關(guān)鍵點(diǎn)，讓你的MySQL輕松保持平穩(wěn)運(yùn)行。

測(cè)量應(yīng)用程序的方式之一是測(cè)量它的性能。用戶體驗(yàn)是衡量應(yīng)用程序性能的一個(gè)指標(biāo)，這就意味著用戶是否能在合理的時(shí)間內(nèi)獲得所需的內(nèi)容。

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

• 79%的用戶表示不太可能再次打開(kāi)一個(gè)緩慢的網(wǎng)站;
• 47%的用戶期望網(wǎng)頁(yè)能在2秒鐘以內(nèi)加載;
• 40%的用戶表示如果加載時(shí)間超過(guò)三秒鐘，就會(huì)放棄這個(gè)網(wǎng)站;
• 頁(yè)面加載時(shí)間延遲一秒可能導(dǎo)致轉(zhuǎn)換損失7%，頁(yè)面瀏覽量減少11%。

無(wú)論標(biāo)準(zhǔn)是什么，都要保持良好的應(yīng)用性能是非常必要的。否則，用戶就會(huì)抱怨(或轉(zhuǎn)到另一個(gè)應(yīng)用程序)。影響應(yīng)用程序性能的一大因素就是數(shù)據(jù)庫(kù)性能。應(yīng)用程序、網(wǎng)站和數(shù)據(jù)庫(kù)之間的交互對(duì)應(yīng)用程序性能至關(guān)重要。

這種交互的核心部分是應(yīng)用程序如何查詢數(shù)據(jù)庫(kù)以及數(shù)據(jù)庫(kù)對(duì)請(qǐng)求的響應(yīng)。無(wú)論從哪一方面來(lái)說(shuō)，MySQL都是***的數(shù)據(jù)庫(kù)管理系統(tǒng)之一。很多企業(yè)正在將MySQL(和其他開(kāi)源數(shù)據(jù)庫(kù))作為其生產(chǎn)環(huán)境中的數(shù)據(jù)庫(kù)解決方案。

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

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

MySQL優(yōu)化關(guān)鍵1：了解如何使用EXPLAIN

對(duì)于數(shù)據(jù)庫(kù)，您做出的最重要的兩個(gè)決策分別是：

1. 設(shè)計(jì)應(yīng)用程序?qū)嶓w之間的關(guān)系如何映射到表(數(shù)據(jù)庫(kù)模式)中
2. 設(shè)計(jì)應(yīng)用程序如何以所需格式(查詢)獲取所需的數(shù)據(jù)。

復(fù)雜的應(yīng)用程序可能具有復(fù)雜的查詢和模式。如果您要獲得應(yīng)用程序所需的性能和擴(kuò)展性，不能僅僅直觀的來(lái)了解查詢是如何執(zhí)行的。

您應(yīng)該學(xué)習(xí)如何使用EXPLAIN命令。此命令向您展示了應(yīng)該如何執(zhí)行查詢，并讓您深入了解可以預(yù)期的性能以及查詢?nèi)绾坞S著數(shù)據(jù)大小的變化而縮放。

類似于MySQL Workbench的工具，都可以為您顯示EXPLAIN輸出，但您仍然需要學(xué)習(xí)基礎(chǔ)知識(shí)以理解它。

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

對(duì)于一個(gè)組件來(lái)說(shuō)應(yīng)該關(guān)注的是“查詢成本”。查詢成本是指基于許多不同的因素上，MySQL在查詢執(zhí)行的總體成本考慮了該特定查詢成本。

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

超過(guò)100000的查詢認(rèn)為是高成本查詢。通常，當(dāng)您是系統(tǒng)上的單個(gè)用戶時(shí)，這些查詢?nèi)匀贿\(yùn)行得很快，但是必須要考慮到在交互式應(yīng)用程序中使用這些查詢的頻率(尤其是隨著用戶數(shù)量的增長(zhǎng))。

雖然這都是一些大致的數(shù)字，但是它們表現(xiàn)出了一般原則。體系結(jié)構(gòu)和配置可能會(huì)影響系統(tǒng)的處理查詢工作負(fù)載。

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

MySQL優(yōu)化關(guān)鍵2：創(chuàng)建正確的索引

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

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

在系統(tǒng)運(yùn)行查詢中，您應(yīng)該始終查看索引。

一個(gè)缺失的索引也可能會(huì)使數(shù)據(jù)庫(kù)運(yùn)行速度速度降低。但要不要添加不需要的索引!不必要的索引會(huì)減慢數(shù)據(jù)庫(kù)運(yùn)行速度。

MySQL優(yōu)化關(guān)鍵3：不要使用默認(rèn)模式!

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

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

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

這里有三個(gè)MySQL性能調(diào)優(yōu)設(shè)置：

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

當(dāng)您設(shè)置InnoDB緩沖池大小時(shí)，不要將其設(shè)置得太大否則會(huì)導(dǎo)致互換。這絕對(duì)會(huì)破壞數(shù)據(jù)庫(kù)性能。一個(gè)簡(jiǎn)單的檢查方法是查看Percona監(jiān)控和管理系統(tǒng)概述圖中的交換活動(dòng)：

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

如果***次沒(méi)有獲得innodb_buffer_pool_size的正確值，不用擔(dān)心。從MySQL 5.7開(kāi)始，可以動(dòng)態(tài)更改InnoDB緩沖池的大小，無(wú)需重新啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)器。

(2) innodb_log_file_size：這是一個(gè)單獨(dú)的InnoDB日志文件大小。默認(rèn)情況下，InnoDB使用兩個(gè)值，以便您可以將此數(shù)字加倍，以獲取循環(huán)重做日志空間的大小，確保事務(wù)持久運(yùn)行。這也優(yōu)化了應(yīng)用對(duì)數(shù)據(jù)庫(kù)的更改。設(shè)置innodb_log_file_size是一個(gè)需要權(quán)衡的問(wèn)題，分配的重做空間越大，寫(xiě)入密集型工作負(fù)載的性能越好，但如果系統(tǒng)遇到電源丟失或其他問(wèn)題，花費(fèi)的恢復(fù)時(shí)間也越長(zhǎng)。

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

(3) max_connections：大型應(yīng)用程序通常需要比默認(rèn)的連接數(shù)量多得多。與其他變量不同，如果不正確設(shè)置，就不會(huì)出現(xiàn)性能問(wèn)題(本質(zhì)上)。相反，如果連接數(shù)量不足以滿足應(yīng)用需求，那么您的應(yīng)用程序?qū)o(wú)法連接到數(shù)據(jù)庫(kù)(這對(duì)用戶來(lái)說(shuō)看起來(lái)就像停機(jī)了)。獲取這個(gè)政權(quán)變量是非常重要的。

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

要記住的一點(diǎn)是，如果您的數(shù)據(jù)庫(kù)運(yùn)行緩慢，應(yīng)用程序通常會(huì)創(chuàng)建過(guò)多的連接。在這種情況下，您應(yīng)該處理數(shù)據(jù)庫(kù)性能問(wèn)題，而不是簡(jiǎn)單地允許更多的連接。過(guò)多的連接可能會(huì)使基礎(chǔ)性能問(wèn)題更糟。

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

MySQL優(yōu)化關(guān)鍵4：將數(shù)據(jù)庫(kù)保存在內(nèi)存中

近年來(lái)，我們看到了固態(tài)硬盤(pán)(SSD)的轉(zhuǎn)型。即使SSD比旋轉(zhuǎn)硬盤(pán)驅(qū)動(dòng)器要快得多，但是它們?nèi)匀慌cRAM中的數(shù)據(jù)不兼容。這中差異不僅來(lái)自于存儲(chǔ)性能本身，還來(lái)自數(shù)據(jù)庫(kù)在從磁盤(pán)或SSD存儲(chǔ)中檢索數(shù)據(jù)時(shí)必須執(zhí)行的其他工作。

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

更好的消息是，您不需要將所有數(shù)據(jù)庫(kù)都裝入內(nèi)存，只需將常訪問(wèn)的工作數(shù)據(jù)集合放到內(nèi)存中即可。

檢查數(shù)據(jù)庫(kù)在穩(wěn)定狀態(tài)下運(yùn)行的I / O數(shù)量(通常在啟動(dòng)后幾個(gè)小時(shí))。下圖您可以在Percona監(jiān)控和管理的InnoDB Metrics儀表板上的InnoDBI / O。

在上圖中，您可以看到峰值高達(dá)每秒2000個(gè)I / O，這表明(至少對(duì)于工作負(fù)載的某些部分)，數(shù)據(jù)庫(kù)工作集與內(nèi)存不匹配。

MySQL優(yōu)化關(guān)鍵5：使用SSD存儲(chǔ)

如果您的數(shù)據(jù)庫(kù)不適合內(nèi)存，但仍然需要快速存儲(chǔ)來(lái)處理寫(xiě)入，并避免數(shù)據(jù)庫(kù)加速(重新啟動(dòng)之后)時(shí)出現(xiàn)性能問(wèn)題。 這些快速存儲(chǔ)意味著需要使用SSD。

由于成本或可靠性原因，一些“專家”仍然主張使用旋轉(zhuǎn)磁盤(pán)。但在操作數(shù)據(jù)庫(kù)中，這些觀點(diǎn)往往是過(guò)時(shí)的或錯(cuò)誤的。今天，SSD在友好的價(jià)格上提供了令人印象深刻的性能和可靠性。

然而，不是所有的SSD都是相同的。對(duì)于數(shù)據(jù)庫(kù)服務(wù)器，您應(yīng)該使用專為服務(wù)器工作負(fù)載設(shè)計(jì)的SSD。

一種直接通過(guò)NVMe或Intel Optane技術(shù)直接連接的SSD可提供***性能。即使作為SAN，NAS或云塊設(shè)備進(jìn)行遠(yuǎn)程連接，與旋轉(zhuǎn)磁盤(pán)相比，SSD仍然具有優(yōu)異的性能。

MySQL優(yōu)化關(guān)鍵6：向外擴(kuò)展

即使是性能***的服務(wù)器也有局限性。有兩種擴(kuò)展方式：up和out。up意味著購(gòu)買(mǎi)更多的硬件，但硬件很貴且很快就會(huì)過(guò)時(shí)。out有幾個(gè)好處：

• 可以利用更小、成本更低的系統(tǒng)。
• 通過(guò)向外擴(kuò)展能更快更容易的線性放縮。
• 由于數(shù)據(jù)庫(kù)分布在多臺(tái)物理機(jī)上，因此數(shù)據(jù)庫(kù)不會(huì)收到單椅硬件故障的影響。

雖然向外擴(kuò)展有優(yōu)勢(shì)，但也有一定的局限性。味了數(shù)據(jù)同步，擴(kuò)展需要復(fù)制，例如基本的MySQL或Percona XtraDB集群復(fù)制。

您還需要確保連接到集群架構(gòu)的應(yīng)用程序可以找到所需的數(shù)據(jù)，通常要通過(guò)一些代理服務(wù)器和負(fù)載平衡器來(lái)實(shí)現(xiàn)，如ProxySQL或HAProxy。

在計(jì)劃擴(kuò)展的同時(shí)，要避免過(guò)早的擴(kuò)張，使用分布式數(shù)據(jù)庫(kù)往往更復(fù)雜。

MySQL優(yōu)化關(guān)鍵7：擁有可觀察性

***的系統(tǒng)在設(shè)計(jì)時(shí)要考慮到可觀察性。

您將MySQL環(huán)境設(shè)置好、運(yùn)行并正確調(diào)整之后，也不能就將它放置不管，數(shù)據(jù)庫(kù)環(huán)境可能受到系統(tǒng)或工作負(fù)載更改的影響。為流量達(dá)到峰值、應(yīng)用程序錯(cuò)誤和MySQL故障等情況做好準(zhǔn)備。

當(dāng)這些情況發(fā)生時(shí)，你需要快速有效地解決它們。實(shí)現(xiàn)這一點(diǎn)的唯一方法是設(shè)置一些監(jiān)控解決方案并進(jìn)行正確的檢測(cè)。這可以讓您看到數(shù)據(jù)庫(kù)環(huán)境中正在運(yùn)行的情況，并在出現(xiàn)問(wèn)題時(shí)分析錯(cuò)誤。理想情況下，系統(tǒng)能在發(fā)生事件之前進(jìn)行攔截。

MySQL Enterprise Monitor，Monyog和Percona監(jiān)控和管理(PMM)都是不錯(cuò)的監(jiān)控工具，具有免費(fèi)和開(kāi)源的優(yōu)勢(shì)。這些工具為監(jiān)控和故障排除提供了良好的操作可見(jiàn)性

隨著越來(lái)越多的公司轉(zhuǎn)向開(kāi)源數(shù)據(jù)庫(kù)(特別是MySQL)，以此來(lái)管理和服務(wù)于大規(guī)模生產(chǎn)環(huán)境中的業(yè)務(wù)數(shù)據(jù)，他們需要專注于保持這些數(shù)據(jù)庫(kù)的調(diào)整和運(yùn)行的***效率。數(shù)據(jù)庫(kù)性能可能會(huì)導(dǎo)致或破壞您的業(yè)務(wù)目標(biāo)，MySQL為您的應(yīng)用程序和網(wǎng)站提供優(yōu)質(zhì)的數(shù)據(jù)庫(kù)解決方案，但要根據(jù)您的需求進(jìn)行調(diào)整，以滿足您的需求并進(jìn)行監(jiān)控、查找、防止瓶頸和性能問(wèn)題。