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

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

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

索引良好的數(shù)據(jù)庫不僅運(yùn)行得更快，而且即使缺少一個(gè)索引也會(huì)使數(shù)據(jù)庫慢如蝸牛。使用EXPLAIN(如前所述)查找缺少的索引并添加它們。但是要小心：不要添加你不需要的索引！不必要的索引會(huì)降低數(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ù)量。

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

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

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

?

?

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

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

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

如何知道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è)置它，就不會(huì)有性能問題(本身)。相反，如果連接的數(shù)量不足以滿足您的應(yīng)用程序的需要，那么您的應(yīng)用程序?qū)o法連接到數(shù)據(jù)庫(在您的用戶看來，這就像是停機(jī)時(shí)間)。所以正確處理這個(gè)變量很重要。

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

?

?

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

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

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