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

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

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

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

　　這種交互的一個(gè)核心組成部分是：各種應用程序如何去查詢(xún)數據庫，以及數據庫是如何響應各種請求的。

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

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

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

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

　　拒絕默認設置

　　追求可視性

　　學(xué)習如何使用 EXPLAIN

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

　　應用實(shí)體之間如何被映射到各個(gè)數據表（數據庫模式架構）上。

　　應用程序如何獲?。ú樵?xún)）到它們所需格式類(lèi)型的數據。

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

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

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

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

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

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

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

　　查詢(xún)成本如果是超過(guò) 100,000 的話(huà)，那么開(kāi)銷(xiāo)就比較大了。而通常當您的系統只有單個(gè)用戶(hù)時(shí)，此類(lèi)查詢(xún)仍然可以被迅速地執行。

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

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

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

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

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

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

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

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

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

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

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

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

　　需要注意的是：不要添加您所不需要的索引，因為不必要的索引會(huì )反過(guò)來(lái)拖慢數據庫。

　　拒絕默認設置

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

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

　　為了讓 MySQL 獲得優(yōu)質(zhì)的性能，了解如何配置 MySQL，以及將它們設置為最適合您的數據庫環(huán)境的狀態(tài)是非常重要的。

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

　　因此，通常您需要將 MySQL 配置為使用所有可用的內存資源，并且能允許您的應用程序所需的連接數。

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

　　innodb_buffer_pool_size

　　數據和索引被用作緩存的緩沖池。當您的數據庫服務(wù)器有著(zhù)大量的系統內存時(shí)，可以用到該設置。

　　如果您只運行 InnoDB 存儲引擎，那么您通?？梢苑峙?80％ 左右的內存給該緩沖池。

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

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

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

　　如果您一開(kāi)始并沒(méi)有將 innodb_buffer_pool_size 的值設置正確，也不必擔心。

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

　　innodb_log_file_size

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

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

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

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

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

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

　　因此，您的日志文件應該至少比使用量大 20％，從而保持系統處于優(yōu)質(zhì)的性能狀態(tài)。

　　max_connections

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

　　也就是說(shuō)，如果連接的數量不足以滿(mǎn)足您的應用需求，那么應用程序將根本無(wú)法連接到數據庫，在用戶(hù)看來(lái)就像宕機了一樣。由此可見(jiàn)，將它設置正確是非常重要的。

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

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

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

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

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

　　將數據庫載入內存中

　　近年來(lái)，出現了固態(tài)硬盤(pán)（SSD）方向上的轉變。盡管固態(tài)硬盤(pán)比傳統機械旋臂硬盤(pán)快得多，但是它們仍然敵不過(guò)將數據存在內存里。

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

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

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

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

　　而事實(shí)上并不存在著(zhù)“一刀切”的規律，數據的訪(fǎng)問(wèn)量決定著(zhù)載入內存所獲得的優(yōu)質(zhì)性能的提升程度。

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

　　請查看一下數據的讀取，因為如果您的數據庫已載入到內存里的話(huà)，那么讀取會(huì )完全結束；而只要有內存可用，寫(xiě)入操作總是會(huì )發(fā)生的。

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

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

　　使用 SSD 存儲

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

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

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

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

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

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

　　橫向擴展
　　

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

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

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

　　您可以從更小型、成本更低的系統中獲益。

　　橫向擴展使得系統的線(xiàn)性擴展更方便、更快捷。

　　由于數據庫會(huì )橫跨增長(cháng)到多個(gè)物理機上，橫向擴展在保護數據庫的同時(shí)，消除了硬件單點(diǎn)故障。

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

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

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

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

　　追求可視性

　　可視性是系統設計的優(yōu)質(zhì)境界，MySQL 也不例外。

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

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

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

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

　　常用的監測工具有：
　　MySQL企業(yè)監控器（Enterprise Monitor）。
　　Monyog。
　　具有免費與開(kāi)源版本的 Percona 監控和管理（PMM）。

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

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

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

　　原文來(lái)自微信公眾號：51CTO技術(shù)棧