MySQL優化建議

03-07

以以下分為7點下分為7點

學習如何使用 EXPLAIN

在您對資料庫做任何設計決策時，有兩個方面非常重要：

應用實體之間如何被映射到各個數據表（資料庫模式架構）上。
應用程序如何獲取（查詢）到它們所需格式類型的數據。

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

建議您認真學習如何去使用 EXPLAIN 命令，而不是憑空猜想。該命令會向您展示查詢是如何被執行的；並深入地演示有關性能的真實表現情況，以及查詢是如何伴隨著數據量的變化進行擴展的。

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

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

其中您需要重點查看的部分是：查詢成本。查詢成本是指基於查詢執行的總體成本和許多不同的因素考慮，MySQL 判定一次查詢所付出的花銷。

一般簡單查詢的成本會小於 1000。介於 1000 到 100,000 的成本值被視為中等成本的查詢。

因此，如果您每秒只是運行上百個（並非幾萬個）此類查詢的話，一般速度應該比較快。

查詢成本如果是超過 100,000 的話，那麼開銷就比較大了。而通常當您的系統只有單個用戶時，此類查詢仍然可以被迅速地執行。

當然，您需要仔細考慮一下在互動式應用程序中，使用此類查詢的頻率（尤其在用戶數量增長的時候）。

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

決定查詢成本的一個首要因素是：查詢是否正確地使用了各種索引。如果您沒有使用索引進行查詢，那麼會被 EXPLAIN 命令所指出來，通常源於索引是如何在資料庫中被創建的，以及查詢本身是如何被設計的。

這也正是為什麼 EXPLAIN 值得去好好學習和使用的原因。

創建正確的索引

索引是通過減少在資料庫里查詢時，必須掃描的數據量來提高查詢的自身效率。

在 MySQL 中，索引被用於加快對資料庫的訪問，並有助於遵循資料庫的各種約束（例如 UNIQUE 和 FOREIGN KEY）。

資料庫索引就像書的索引一樣，它們的位置信息被保存，並且包含有資料庫的主要信息。

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

不過，索引並不總能匹配上任何的負載請求。在系統運行中，您應當不斷為查詢的上下文環境創建各種索引。

雖然有著良好索引的資料庫會運行更快速，但是如果出現單個索引的缺失，則會拖慢整個資料庫的效率。

因此，我們需要使用 EXPLAIN 來查找缺失的索引，並將其添加上去。

需要注意的是：不要添加您所不需要的索引，因為不必要的索引會反過來拖慢資料庫。

拒絕默認設置

就像其他任何軟體那樣，MySQL 也能通過各種可配置的設置，來修改其行為並最終優化其性能。

同時這些配置的設置經常會被管理員所忽略，並一直保持著默認值的狀態。

為了讓 MySQL 獲得最佳的性能，了解如何配置 MySQL，以及將它們設置為最適合您的資料庫環境的狀態是非常重要的。

在默認情況下，MySQL 是針對小規模的發布、安裝進行調優的，而並非真正的生產環境規模。

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

這裡有三個有關 MySQL 性能優化的設置，值得您去仔細地配置：

innodb_buffer_pool_size

數據和索引被用作緩存的緩衝池。當您的資料庫伺服器有著大量的系統內存時，可以用到該設置。

如果您只運行 InnoDB 存儲引擎，那麼您通常可以分配 80％左右的內存給該緩衝池。

而如果您要運行非常複雜的查詢或者您有大量的並發資料庫連接，亦或您有非常大的數據表的情況，那麼就可能需要將此值下調一個等級，以便為其他的調用分配更多的內存。

您在設置 InnoDB 緩衝池大小的時候，要確保其設置既不要過大，也不要頻繁引起交換（swapping），因為這些絕對會降低您的資料庫性能。有一個簡單的檢查方法就是在「Percona 監控和管理」。

如圖所示，如果你看到有大於 1MB 每秒的持續交換活動的話，您就需要減少緩衝池的大小了，或者使用其他的內存。

如果您一開始並沒有將 innodb_buffer_pool_size 的值設置正確，也不必擔心。

從 MySQL 5.7 開始，您可以動態地改變 InnoDB 緩衝池的大小，而不需要重新啟動資料庫伺服器了。

innodb_log_file_size

這是指單個 InnoDB 日誌文件的大小。默認情況下，InnoDB 使用兩個值，這樣您就可以通過將其增加一倍，來讓 InnoDB 獲得循環的重做日誌空間，以確保交易的持久性。這同時也優化了對資料庫的寫入性能。

設置 innodb_log_file_size 的值是很值得推敲的：如果分配了較大的重做空間，那麼對於寫入密集型的工作負載來說性能會越好。

但是如果您的系統遭受到斷電或其他問題導致崩潰的時候，那麼其恢復時間則會越長。

您可能會問：怎麼才能知道自己的 MySQL 性能是否受限於當前的 InnoDB 日誌文件大小呢？

您可以通過查看未實際使用的重做日誌空間大小來判定。最簡單的方法就是查看「Percona 監控和管理」的 InnoDB 指標儀錶板。

在下圖中，InnoDB 的日誌文件不夠大，使用空間已經屢屢接近於可用的重做日誌空間了，如紅線所示：

因此，您的日誌文件應該至少比使用量大 20％，從而保持系統處於最佳的性能狀態。

max_connections

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

也就是說，如果連接的數量不足以滿足您的應用需求，那麼應用程序將根本無法連接到資料庫，在用戶看來就像宕機了一樣。由此可見，將它設置正確是非常重要的。

對於在多台伺服器上運行著具有多個組件的複雜應用來說，您想獲知到底需要多少個連接是非常困難的。

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

查看這些數字的一個簡單方法是：在「Percona 監控和管理」的系統概述界面中查看使用 MySQL 連接圖。

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

還有一點需要記住：如果您的應用程序所創建的連接數量過多，通常會導致資料庫運行緩慢。

在這種情況下，您應該在資料庫性能上做文章，而不是簡單地允許建立更多的連接。更多的連接會使得潛在的性能問題更加惡化。

將資料庫載入內存中

近年來，出現了固態硬碟（SSD）方向上的轉變。儘管固態硬碟比傳統機械旋臂硬碟快得多，但是它們仍然敵不過將數據存在內存里。

這種差別不僅來自於存儲性能本身，還來自於資料庫從磁碟或 SSD 里存取數據時所產生的額外工作。

隨著近年來硬體技術的改進，不管您是運行在雲端，還是管理著自己的硬體，將資料庫載入內存已經變得可行。

更令人振奮的是：您並不需要將整個資料庫載入內存以獲得其性能優勢，您只需要將最頻繁訪問的數據集放入其中便可。

您可能已經看過一些文章，有介紹將資料庫多少比例（如：10％到 33％）載入到內存里。

而事實上並不存在著「一刀切」的規律，數據的訪問量決定著載入內存所獲得的最佳性能的提升程度。

您與其去尋找某個特定的「神奇」數字，不如去檢查資料庫達到穩定運行狀態時的 I/O（通常是在它開始運行的幾個小時之後）。

請查看一下數據的讀取，因為如果您的資料庫已載入到內存里的話，那麼讀取會完全結束；而只要有內存可用，寫入操作總是會發生的。

下圖是「Percona 監控和管理」的 InnoDB 指標儀錶板中的 InnoDB I/O圖：

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

使用 SSD 存儲

無論您的資料庫是否已被載入內存，您都需要使用快速存儲來處理寫入操作，並且避免在資料庫啟動後（重啟之後）出現性能問題。這裡的快速存儲就是指固態硬碟。

一些所謂的「專家」仍在基於成本和可靠性的基礎上，主張使用機械旋臂硬碟。坦率地說，當涉及到資料庫操作時，這些建議往往是過時的或是完全錯誤的。現如今，固態硬碟的性能已經非常卓越、可靠且價格低廉了。

並非所有的固態硬碟都是同等生產的。對於資料庫伺服器來說，您應該選用那些專供伺服器工作負載、且能精心呵護數據的 SSD。

例如：防止斷電損壞的，而避免使用那些專為台式和筆記本電腦設計的商用固態硬碟。

通過 NVMe 或英特爾 Optane 技術來直接連接的 SSD 往往能夠提供最佳的性能。

即使遠程連接到 SAN、NAS 或雲端的塊設備上，固態硬碟也能比機械旋臂硬碟提供更為優越的性能。

橫向擴展

即使是性能最高的伺服器也有局限性。業界一般用兩種方法來進行擴展：縱向和橫向。

縱向擴展意味著購買更多的硬體。這樣做不但成本昂貴，而且硬體折舊速度快。

而橫向擴展，則在處理負載方面有如下幾點優勢：

您可以從更小型、成本更低的系統中獲益。
橫向擴展使得系統的線性擴展更方便、更快捷。
由於資料庫會橫跨增長到多個物理機上，橫向擴展在保護資料庫的同時，消除了硬體單點故障。

儘管橫向擴展有著諸多優勢，不過它還是具有一定的局限性。橫向擴展需要數據複製，例如基本的 MySQL Replication 或是用於數據同步的 Percona XtraDB 群集。

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

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

當然，過早地規劃橫向擴展，會增加分散式資料庫的複雜性。最近發布的 MySQL 8 候選版本已聲稱自己能夠在單一的系統上處理超過 200 萬個簡單查詢。

追求可視性

可視性是系統設計的最佳境界，MySQL 也不例外。

一旦完成了 MySQL 環境的搭建、運行並調優，您千萬不要認為已經萬事大吉了。

資料庫環境既會受到來自系統更改或流量負荷的影響，也會遇到例如流量高峰、應用程序錯誤以及 MySQL 自身的各種問題。

為了快速、有效地解決各種問題，您需要建立和實施一些監控機制，從而能獲悉資料庫環境的狀態，並在出現錯誤時及時分析伺服器上的數據。

因此理想情況就是在系統出現問題或是被用戶所察覺之前就做到防範於未然。

常用的監測工具有：

MySQL企業監控器（Enterprise Monitor）。
Monyog。
具有免費與開源版本的 Percona 監控和管理（PMM）。

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

隨著越來越多的公司在大規模生產環境中使用開源的資料庫（特別是MySQL）來管理和服務他們的業務數據，他們需要把工作重心放在保持資料庫的調優和運行效率上。

MySQL 的確是一款能夠提升您的應用程序和網站性能的優秀資料庫，當然您需要通過對它進行調整，以滿足業務需求，監測、發現並防止任何瓶頸和性能方面的問題。