【51CTO.com原創(chuàng)稿件】隨著容量和負(fù)載的增加，MySQL 的性能會(huì)日趨緩慢。這里有七點(diǎn)建議能夠保證 MySQL 的平穩(wěn)運(yùn)行。

[[213104]]

性能是我們衡量應(yīng)用的一種方式，而應(yīng)用性能的一項(xiàng)指標(biāo)就是用戶體驗(yàn)，也就是平時(shí)我們常說(shuō)的：“用戶需要等待超過(guò)合理的時(shí)間，才能獲得他們想要的東西嗎？”

在不同的情況和場(chǎng)景下，該指標(biāo)會(huì)有所不同。比如說(shuō)：對(duì)于移動(dòng)購(gòu)物應(yīng)用來(lái)說(shuō)，其響應(yīng)時(shí)間不能超過(guò)幾秒鐘；而對(duì)于一個(gè)員工的人力資源頁(yè)面而言，其響應(yīng)時(shí)間則允許比幾秒鐘更長(zhǎng)。

因此，不管是什么樣的標(biāo)準(zhǔn)，維持應(yīng)用程序的良好性能都是至關(guān)重要的，否則就會(huì)引發(fā)用戶的抱怨（或更糟的是用戶轉(zhuǎn)而使用其他的應(yīng)用）。而數(shù)據(jù)庫(kù)性能就是影響應(yīng)用程序性能的因素之一。

可以說(shuō)，應(yīng)用程序、網(wǎng)站和數(shù)據(jù)庫(kù)之間的交互會(huì)直接影響到應(yīng)用服務(wù)水平的確立。

這種交互的一個(gè)核心組成部分是：各種應(yīng)用程序如何去查詢數(shù)據(jù)庫(kù)，以及數(shù)據(jù)庫(kù)是如何響應(yīng)各種請(qǐng)求的。

不論是哪一種標(biāo)準(zhǔn)，MySQL 都是時(shí)下***的數(shù)據(jù)庫(kù)管理系統(tǒng)之一。越來(lái)越多的企業(yè)已將 MySQL（和其他開(kāi)源的數(shù)據(jù)庫(kù)）視為其生產(chǎn)環(huán)境中的數(shù)據(jù)庫(kù)解決方案。

MySQL 有許多配置方法可以確保您的數(shù)據(jù)庫(kù)能夠快速地響應(yīng)各種查詢，同時(shí)僅對(duì)應(yīng)用程序性能造成細(xì)微的下降。

[[213105]]

以下就是能夠幫助您優(yōu)化 MySQL 數(shù)據(jù)庫(kù)性能的 7 點(diǎn)必備技巧：

• 學(xué)習(xí)如何使用EXPLAIN
• 創(chuàng)建正確的索引
• 拒絕默認(rèn)設(shè)置
• 將數(shù)據(jù)庫(kù)載入內(nèi)存中
• 使用SSD存儲(chǔ)
• 橫向擴(kuò)展
• 追求可視性

學(xué)習(xí)如何使用 EXPLAIN

在您對(duì)數(shù)據(jù)庫(kù)做任何設(shè)計(jì)決策時(shí)，有兩個(gè)方面非常重要：

• 應(yīng)用實(shí)體之間如何被映射到各個(gè)數(shù)據(jù)表（數(shù)據(jù)庫(kù)模式架構(gòu)）上。
• 應(yīng)用程序如何獲?。ú樵儯┑剿鼈兯韪袷筋?lèi)型的數(shù)據(jù)。

復(fù)雜的應(yīng)用程序必然有著復(fù)雜的模式架構(gòu)和查詢。如果您想讓自己的各種應(yīng)用具備所需的性能和擴(kuò)展性，那就不能單純依靠直覺(jué)去理解各種查詢的執(zhí)行機(jī)制。

建議您認(rèn)真學(xué)習(xí)如何去使用 EXPLAIN 命令，而不是憑空猜想。該命令會(huì)向您展示查詢是如何被執(zhí)行的；并深入地演示有關(guān)性能的真實(shí)表現(xiàn)情況，以及查詢是如何伴隨著數(shù)據(jù)量的變化進(jìn)行擴(kuò)展的。

像許多 MySQL Workbench 之類(lèi)的工具都可以將 EXPLAIN 的輸出可視化地展示給您，不過(guò)您仍然需要了解與它相關(guān)的基本知識(shí)。

EXPLAIN 命令的輸出有兩種不同的格式：老式的表格形式和較新的、能夠提供更為細(xì)節(jié)化的、結(jié)構(gòu)化的 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)” 
    } 
  } 
} 

其中您需要重點(diǎn)查看的部分是：查詢成本。查詢成本是指基于查詢執(zhí)行的總體成本和許多不同的因素考慮，MySQL 判定一次查詢所付出的花銷(xiāo)。

一般簡(jiǎn)單查詢的成本會(huì)小于 1000。介于 1000 到 100,000 的成本值被視為中等成本的查詢。

因此，如果您每秒只是運(yùn)行上百個(gè)（并非幾萬(wàn)個(gè)）此類(lèi)查詢的話，一般速度應(yīng)該比較快。 

查詢成本如果是超過(guò) 100,000 的話，那么開(kāi)銷(xiāo)就比較大了。而通常當(dāng)您的系統(tǒng)只有單個(gè)用戶時(shí)，此類(lèi)查詢?nèi)匀豢梢员谎杆俚貓?zhí)行。

當(dāng)然，您需要仔細(xì)考慮一下在交互式應(yīng)用程序中，使用此類(lèi)查詢的頻率（尤其在用戶數(shù)量增長(zhǎng)的時(shí)候）。

雖然這些只是大概的數(shù)字，但是它們卻能夠反映出總體的規(guī)律。實(shí)際情況下，您的系統(tǒng)在處理查詢請(qǐng)求負(fù)載時(shí)會(huì)表現(xiàn)得更好還是更糟，完全取決于自身的架構(gòu)與配置。

決定查詢成本的一個(gè)首要因素是：查詢是否正確地使用了各種索引。如果您沒(méi)有使用索引進(jìn)行查詢，那么會(huì)被 EXPLAIN 命令所指出來(lái)，通常源于索引是如何在數(shù)據(jù)庫(kù)中被創(chuàng)建的，以及查詢本身是如何被設(shè)計(jì)的。

這也正是為什么 EXPLAIN 值得去好好學(xué)習(xí)和使用的原因。

創(chuàng)建正確的索引

索引是通過(guò)減少在數(shù)據(jù)庫(kù)里查詢時(shí)，必須掃描的數(shù)據(jù)量來(lái)提高查詢的自身效率。

在 MySQL 中，索引被用于加快對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)，并有助于遵循數(shù)據(jù)庫(kù)的各種約束（例如 UNIQUE 和 FOREIGN KEY）。

數(shù)據(jù)庫(kù)索引就像書(shū)的索引一樣，它們的位置信息被保存，并且包含有數(shù)據(jù)庫(kù)的主要信息。

它們是數(shù)據(jù)位置的一種參考方法或映射，因此索引并不會(huì)更改數(shù)據(jù)庫(kù)中的任何數(shù)據(jù)。它們只是指向數(shù)據(jù)存放的位置而已。

不過(guò)，索引并不總能匹配上任何的負(fù)載請(qǐng)求。在系統(tǒng)運(yùn)行中，您應(yīng)當(dāng)不斷為查詢的上下文環(huán)境創(chuàng)建各種索引。

雖然有著良好索引的數(shù)據(jù)庫(kù)會(huì)運(yùn)行更快速，但是如果出現(xiàn)單個(gè)索引的缺失，則會(huì)拖慢整個(gè)數(shù)據(jù)庫(kù)的效率。

因此，我們需要使用 EXPLAIN 來(lái)查找缺失的索引，并將其添加上去。

需要注意的是：不要添加您所不需要的索引，因?yàn)椴槐匾乃饕龝?huì)反過(guò)來(lái)拖慢數(shù)據(jù)庫(kù)。

拒絕默認(rèn)設(shè)置

就像其他任何軟件那樣，MySQL 也能通過(guò)各種可配置的設(shè)置，來(lái)修改其行為并最終優(yōu)化其性能。

同時(shí)這些配置的設(shè)置經(jīng)常會(huì)被管理員所忽略，并一直保持著默認(rèn)值的狀態(tài)。

為了讓 MySQL 獲得***的性能，了解如何配置 MySQL，以及將它們?cè)O(shè)置為最適合您的數(shù)據(jù)庫(kù)環(huán)境的狀態(tài)是非常重要的。

在默認(rèn)情況下，MySQL 是針對(duì)小規(guī)模的發(fā)布、安裝進(jìn)行調(diào)優(yōu)的，而并非真正的生產(chǎn)環(huán)境規(guī)模。

因此，通常您需要將 MySQL 配置為使用所有可用的內(nèi)存資源，并且能允許您的應(yīng)用程序所需的***連接數(shù)。

這里有三個(gè)有關(guān) MySQL 性能優(yōu)化的設(shè)置，值得您去仔細(xì)地配置：

innodb_buffer_pool_size

數(shù)據(jù)和索引被用作緩存的緩沖池。當(dāng)您的數(shù)據(jù)庫(kù)服務(wù)器有著大量的系統(tǒng)內(nèi)存時(shí)，可以用到該設(shè)置。

如果您只運(yùn)行 InnoDB 存儲(chǔ)引擎，那么您通?？梢苑峙?80％ 左右的內(nèi)存給該緩沖池。

而如果您要運(yùn)行非常復(fù)雜的查詢或者您有大量的并發(fā)數(shù)據(jù)庫(kù)連接，亦或您有非常大的數(shù)據(jù)表的情況，那么就可能需要將此值下調(diào)一個(gè)等級(jí)，以便為其他的調(diào)用分配更多的內(nèi)存。

您在設(shè)置 InnoDB 緩沖池大小的時(shí)候，要確保其設(shè)置既不要過(guò)大，也不要頻繁引起交換（swapping），因?yàn)檫@些絕對(duì)會(huì)降低您的數(shù)據(jù)庫(kù)性能。有一個(gè)簡(jiǎn)單的檢查方法就是在“Percona 監(jiān)控和管理”。 

如圖所示，如果你看到有大于 1MB 每秒的持續(xù)交換活動(dòng)的話，您就需要減少緩沖池的大小了，或者使用其他的內(nèi)存。

如果您一開(kāi)始并沒(méi)有將 innodb_buffer_pool_size 的值設(shè)置正確，也不必?fù)?dān)心。

從 MySQL 5.7 開(kāi)始，您可以動(dòng)態(tài)地改變 InnoDB 緩沖池的大小，而不需要重新啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)器了。 

innodb_log_file_size

這是指單個(gè) InnoDB 日志文件的大小。默認(rèn)情況下，InnoDB 使用兩個(gè)值，這樣您就可以通過(guò)將其增加一倍，來(lái)讓 InnoDB 獲得循環(huán)的重做日志空間，以確保交易的持久性。這同時(shí)也優(yōu)化了對(duì)數(shù)據(jù)庫(kù)的寫(xiě)入性能。

設(shè)置 innodb_log_file_size 的值是很值得推敲的：如果分配了較大的重做空間，那么對(duì)于寫(xiě)入密集型的工作負(fù)載來(lái)說(shuō)性能會(huì)越好。

但是如果您的系統(tǒng)遭受到斷電或其他問(wèn)題導(dǎo)致崩潰的時(shí)候，那么其恢復(fù)時(shí)間則會(huì)越長(zhǎng)。

您可能會(huì)問(wèn)：怎么才能知道自己的 MySQL 性能是否受限于當(dāng)前的 InnoDB 日志文件大小呢？

您可以通過(guò)查看未實(shí)際使用的重做日志空間大小來(lái)判定。最簡(jiǎn)單的方法就是查看“Percona 監(jiān)控和管理”的 InnoDB 指標(biāo)儀表板。

在下圖中，InnoDB 的日志文件不夠大，使用空間已經(jīng)屢屢接近于可用的重做日志空間了，如紅線所示：

因此，您的日志文件應(yīng)該至少比使用量大 20％，從而保持系統(tǒng)處于***的性能狀態(tài)。

max_connections

大型應(yīng)用程序通常需要比默認(rèn)數(shù)量多得多的連接。不同于其他的變量，如果您沒(méi)能將該值設(shè)置正確，您就會(huì)碰到性能方面的問(wèn)題。

也就是說(shuō)，如果連接的數(shù)量不足以滿足您的應(yīng)用需求，那么應(yīng)用程序?qū)⒏緹o(wú)法連接到數(shù)據(jù)庫(kù)，在用戶看來(lái)就像宕機(jī)了一樣。由此可見(jiàn)，將它設(shè)置正確是非常重要的。

對(duì)于在多臺(tái)服務(wù)器上運(yùn)行著具有多個(gè)組件的復(fù)雜應(yīng)用來(lái)說(shuō)，您想獲知到底需要多少個(gè)連接是非常困難的。

幸運(yùn)的是，MySQL 能夠在峰值操作時(shí)輕易地獲悉所用到的連接數(shù)量。通常，您需要確保在應(yīng)用程序所使用到的***連接數(shù)和可用的***連接數(shù)之間至少有 30％ 的差額。

查看這些數(shù)字的一個(gè)簡(jiǎn)單方法是：在“Percona 監(jiān)控和管理”的系統(tǒng)概述界面中查看使用 MySQL 連接圖。

下圖顯示了一個(gè)健康的系統(tǒng)，它有著足夠數(shù)量的可用額外連接。

還有一點(diǎn)需要記?。?/span>如果您的應(yīng)用程序所創(chuàng)建的連接數(shù)量過(guò)多，通常會(huì)導(dǎo)致數(shù)據(jù)庫(kù)運(yùn)行緩慢。

在這種情況下，您應(yīng)該在數(shù)據(jù)庫(kù)性能上做文章，而不是簡(jiǎn)單地允許建立更多的連接。更多的連接會(huì)使得潛在的性能問(wèn)題更加惡化。

將數(shù)據(jù)庫(kù)載入內(nèi)存中

近年來(lái)，出現(xiàn)了固態(tài)硬盤(pán)（SSD）方向上的轉(zhuǎn)變。盡管固態(tài)硬盤(pán)比傳統(tǒng)機(jī)械旋臂硬盤(pán)快得多，但是它們?nèi)匀粩巢贿^(guò)將數(shù)據(jù)存在內(nèi)存里。

這種差別不僅來(lái)自于存儲(chǔ)性能本身，還來(lái)自于數(shù)據(jù)庫(kù)從磁盤(pán)或 SSD 里存取數(shù)據(jù)時(shí)所產(chǎn)生的額外工作。

隨著近年來(lái)硬件技術(shù)的改進(jìn)，不管您是運(yùn)行在云端，還是管理著自己的硬件，將數(shù)據(jù)庫(kù)載入內(nèi)存已經(jīng)變得可行。

更令人振奮的是：您并不需要將整個(gè)數(shù)據(jù)庫(kù)載入內(nèi)存以獲得其性能優(yōu)勢(shì)，您只需要將最頻繁訪問(wèn)的數(shù)據(jù)集放入其中便可。

您可能已經(jīng)看過(guò)一些文章，有介紹將數(shù)據(jù)庫(kù)多少比例（如：10％ 到 33％）載入到內(nèi)存里。

而事實(shí)上并不存在著“一刀切”的規(guī)律，數(shù)據(jù)的訪問(wèn)量決定著載入內(nèi)存所獲得的***性能的提升程度。

您與其去尋找某個(gè)特定的“神奇”數(shù)字，不如去檢查數(shù)據(jù)庫(kù)達(dá)到穩(wěn)定運(yùn)行狀態(tài)時(shí)的 I/O（通常是在它開(kāi)始運(yùn)行的幾個(gè)小時(shí)之后）。

請(qǐng)查看一下數(shù)據(jù)的讀取，因?yàn)槿绻臄?shù)據(jù)庫(kù)已載入到內(nèi)存里的話，那么讀取會(huì)完全結(jié)束；而只要有內(nèi)存可用，寫(xiě)入操作總是會(huì)發(fā)生的。

下圖是“Percona 監(jiān)控和管理”的 InnoDB 指標(biāo)儀表板中的 InnoDB I/O圖：

如上圖所示，那些峰值高達(dá)每秒 2,000 的 I/O 操作表明（至少是流量負(fù)載的一部分）它們與載入內(nèi)存中數(shù)據(jù)庫(kù)的數(shù)據(jù)集并不相配。 

使用 SSD 存儲(chǔ)

無(wú)論您的數(shù)據(jù)庫(kù)是否已被載入內(nèi)存，您都需要使用快速存儲(chǔ)來(lái)處理寫(xiě)入操作，并且避免在數(shù)據(jù)庫(kù)啟動(dòng)后（重啟之后）出現(xiàn)性能問(wèn)題。這里的快速存儲(chǔ)就是指固態(tài)硬盤(pán)。

一些所謂的“專(zhuān)家”仍在基于成本和可靠性的基礎(chǔ)上，主張使用機(jī)械旋臂硬盤(pán)。坦率地說(shuō)，當(dāng)涉及到數(shù)據(jù)庫(kù)操作時(shí)，這些建議往往是過(guò)時(shí)的或是完全錯(cuò)誤的?，F(xiàn)如今，固態(tài)硬盤(pán)的性能已經(jīng)非常卓越、可靠且價(jià)格低廉了。

并非所有的固態(tài)硬盤(pán)都是同等生產(chǎn)的。對(duì)于數(shù)據(jù)庫(kù)服務(wù)器來(lái)說(shuō)，您應(yīng)該選用那些***服務(wù)器工作負(fù)載、且能精心呵護(hù)數(shù)據(jù)的 SSD。

例如：防止斷電損壞的，而避免使用那些專(zhuān)為臺(tái)式和筆記本電腦設(shè)計(jì)的商用固態(tài)硬盤(pán)。

通過(guò) NVMe 或英特爾 Optane 技術(shù)來(lái)直接連接的 SSD 往往能夠提供***的性能。

即使遠(yuǎn)程連接到 SAN、NAS 或云端的塊設(shè)備上，固態(tài)硬盤(pán)也能比機(jī)械旋臂硬盤(pán)提供更為優(yōu)越的性能。

橫向擴(kuò)展

即使是性能***的服務(wù)器也有局限性。業(yè)界一般用兩種方法來(lái)進(jìn)行擴(kuò)展：縱向和橫向。

縱向擴(kuò)展意味著購(gòu)買(mǎi)更多的硬件。這樣做不但成本昂貴，而且硬件折舊速度快。

而橫向擴(kuò)展，則在處理負(fù)載方面有如下幾點(diǎn)優(yōu)勢(shì)：

• 您可以從更小型、成本更低的系統(tǒng)中獲益。
• 橫向擴(kuò)展使得系統(tǒng)的線性擴(kuò)展更方便、更快捷。
• 由于數(shù)據(jù)庫(kù)會(huì)橫跨增長(zhǎng)到多個(gè)物理機(jī)上，橫向擴(kuò)展在保護(hù)數(shù)據(jù)庫(kù)的同時(shí)，消除了硬件單點(diǎn)故障。

盡管橫向擴(kuò)展有著諸多優(yōu)勢(shì)，不過(guò)它還是具有一定的局限性。橫向擴(kuò)展需要數(shù)據(jù)復(fù)制，例如基本的 MySQL Replication 或是用于數(shù)據(jù)同步的 Percona XtraDB 群集。

但是作為回報(bào)，您也會(huì)獲得更高的性能和可用性。如果您需要更高級(jí)的擴(kuò)展性，那么請(qǐng)考慮使用 MySQL 分片（sharding）。

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

當(dāng)然，過(guò)早地規(guī)劃?rùn)M向擴(kuò)展，會(huì)增加分布式數(shù)據(jù)庫(kù)的復(fù)雜性。最近發(fā)布的 MySQL 8 候選版本已聲稱(chēng)自己能夠在單一的系統(tǒng)上處理超過(guò) 200 萬(wàn)個(gè)簡(jiǎn)單查詢。

追求可視性

可視性是系統(tǒng)設(shè)計(jì)的***境界，MySQL 也不例外。

一旦完成了 MySQL 環(huán)境的搭建、運(yùn)行并調(diào)優(yōu)，您千萬(wàn)不要認(rèn)為已經(jīng)萬(wàn)事大吉了。

數(shù)據(jù)庫(kù)環(huán)境既會(huì)受到來(lái)自系統(tǒng)更改或流量負(fù)荷的影響，也會(huì)遇到例如流量高峰、應(yīng)用程序錯(cuò)誤以及 MySQL 自身的各種問(wèn)題。

為了快速、有效地解決各種問(wèn)題，您需要建立和實(shí)施一些監(jiān)控機(jī)制，從而能獲悉數(shù)據(jù)庫(kù)環(huán)境的狀態(tài)，并在出現(xiàn)錯(cuò)誤時(shí)及時(shí)分析服務(wù)器上的數(shù)據(jù)。

因此理想情況就是在系統(tǒng)出現(xiàn)問(wèn)題或是被用戶所察覺(jué)之前就做到防范于未然。

常用的監(jiān)測(cè)工具有：

• MySQL企業(yè)監(jiān)控器（Enterprise Monitor）。
• Monyog。
• 具有免費(fèi)與開(kāi)源版本的 Percona 監(jiān)控和管理（PMM）。

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

隨著越來(lái)越多的公司在大規(guī)模生產(chǎn)環(huán)境中使用開(kāi)源的數(shù)據(jù)庫(kù)（特別是MySQL）來(lái)管理和服務(wù)他們的業(yè)務(wù)數(shù)據(jù)，他們需要把工作重心放在保持?jǐn)?shù)據(jù)庫(kù)的調(diào)優(yōu)和運(yùn)行效率上。

MySQL 的確是一款能夠提升您的應(yīng)用程序和網(wǎng)站性能的優(yōu)秀數(shù)據(jù)庫(kù)，當(dāng)然您需要通過(guò)對(duì)它進(jìn)行調(diào)整，以滿足業(yè)務(wù)需求，監(jiān)測(cè)、發(fā)現(xiàn)并防止任何瓶頸和性能方面的問(wèn)題。

[[213106]]

陳峻（Julian Chen） ，有著十多年的 IT 項(xiàng)目、企業(yè)運(yùn)維和風(fēng)險(xiǎn)管控的從業(yè)經(jīng)驗(yàn)，日常工作深入系統(tǒng)安全各個(gè)環(huán)節(jié)。作為 CISSP 證書(shū)持有者，他在各專(zhuān)業(yè)雜志上發(fā)表了《IT運(yùn)維的“六脈神劍”》、《律師事務(wù)所IT服務(wù)管理》 和《股票交易網(wǎng)絡(luò)系統(tǒng)中的安全設(shè)計(jì)》等論文。他還持續(xù)分享并更新《廉環(huán)話》系列博文和各種外文技術(shù)翻譯，曾被(ISC)2 評(píng)為第九屆亞太區(qū)信息安全***成就表彰計(jì)劃的“信息安全踐行者”和 Future-S 中國(guó) IT 治理和管理的 2015 年度踐行人物。

【51CTO原創(chuàng)稿件，合作站點(diǎn)轉(zhuǎn)載請(qǐng)注明原文作者和出處為51CTO.com】