MySQL 效能最佳化提示

硬體設定是資料庫效能的重要考量因素。在定義硬體設定之前，請務必先充分瞭解應用程式的活躍使用者與同時存取服務的使用者人數、資料庫和索引的大小，以及應用程式或服務的預期延遲時間。以下是一些與硬體相關的重要考量：

處理能力是高效能資料庫系統最重要的要素之一。同時連線/使用者/執行緒數量會決定處理資料庫要求所需的核心數量。分配給資料庫的 CPU 必須能處理一般工作負載 + 尖峰 (極端) 工作負載，才能讓應用程式達到最佳效能。

以 Cloud SQL，Google Cloud 的全代管 MySQL 產品來說，會以虛擬 CPU (vCPU) 的形式分配 CPU。分配給資料庫的 vCPU 數量最終決定了資料庫執行個體的記憶體數量和網路處理量，因為每個 vCPU 都有分配給執行個體的最大記憶體量，而且網路處理量甚至還會因 vCPU 數量而異。Cloud SQL 可讓您彈性擴充執行個體的 vCPU 數量，輕鬆滿足應用程式的記憶體和網路處理量需求。

在決定要分配給資料庫的記憶體量時，您應確保工作集適合緩衝區集區。工作集是指資料庫在任何時間點主動使用的資料。分配的記憶體應足以保存這個工作集或經常存取的資料，這些資料通常包括資料庫資料、索引、工作階段緩衝區、字典快取和雜湊表。如要確認是否已分配足夠的記憶體，其中一種方法是檢查資料庫中的磁碟讀取狀態。在正常工作負載情況下，磁碟讀取量應更少或極低。

如果執行個體的記憶體配置不足，執行個體可能會發生「記憶體不足」的問題，而這會導致資料庫執行個體重新啟動，並導致資料庫或是應用程式進入停機時間。

資料庫儲存空間是另一項元件，在效能最佳化方面扮演著重要的角色。Cloud SQL 提供 2 種儲存空間

• SSD (預設)

• HDD

SSD 的效能和處理量比 HDD 高出許多。因此請一律選用 SSD 以獲得更好的效能，這對於正式環境工作負載而言特別實用。

分配至執行個體的讀取和寫入每秒輸入/輸出作業數 (IOPS)，取決於建立執行個體時分配的儲存空間量。磁碟大小容量越高，讀取和寫入 IOPS 就越大。因此，建議您建立具備較高資料大小容量的執行個體，以提升 IOPS 的效能。以下 Google Cloud 控制台螢幕截圖顯示建立時分配給資料庫執行個體的資源摘要 (包括最大容量)，可協助使用者確認並清楚瞭解資料庫在執行個體化之後會如何設定。

減少網路延遲時間的方法之一，就是選擇最接近該應用程式或服務的執行個體區域。所有 Google Cloud 區域皆提供 MySQL 適用的 Cloud SQL，讓使用者能夠以最接近其使用者的方式將資料庫執行個體化。

Cloud SQL 可讓您輕鬆向上擴充或縮減指派給資料庫執行個體的資源 (CPU、記憶體或儲存空間)。這對於資源需求不同的工作負載而言非常實用。舉例來說，使用者可以在工作負載需求增加的期間增加 (向上擴充) 資源，並在工作負載尖峰情況結束後縮減資源。

本節說明透過 MySQL 資料庫設定提升效能的最佳做法。

建立新資料庫時，請選擇最新版本的 MySQL。最新版本修正了錯誤並提升了效能，比舊版更臻完善。Cloud SQL 提供市面上最新的 MySQL 版本，並在建立新資料庫時以其為預設版本。詳情請參閱 Cloud SQL 支援的 MySQL 版本。

以 MySQL 執行個體來說，InnoDB 是唯一支援的儲存引擎。InnoDB 緩衝區集區大小是使用者第一個會想要定義以獲得最佳效能的參數。緩衝區集區是記憶體區域，用來在清除前儲存資料表快取、索引快取和修改後的資料，並儲存其他內部結構 (例如自動調整雜湊索引 (AHI))。

Cloud SQL 會根據執行個體大小 (預設值會隨執行個體大小而變化)，定義要分配給 InnoDB 緩衝區集區的執行個體記憶體預設值 (~72%)。詳情請參閱不同執行個體大小的緩衝區集區設定。Cloud SQL 可讓您根據應用程式需求，使用資料庫旗標彈性修改緩衝區集區大小。

緩衝區集區的大小應經過調整，除了 InnoDB 緩衝區集區之外，讓執行個體能提供足夠的空閒記憶體以用於工作階段緩衝區、字典快取、performance_schema 資料表 (如啟用)。

使用者可以查看從執行個體發生的磁碟讀取，確認從磁碟讀取的資料量，以及來自緩衝區集區的相符讀取量。如果磁碟讀取作業數增加，則增加緩衝區集區的大小和執行個體記憶體可改善讀取查詢的效能。

InnoDB 記錄檔或重做記錄會將資料變更記錄至資料表。InnoDB 記錄檔大小會定義單一重做記錄檔的大小。

以重做記錄大小較高的著重寫入作業的工作負載來說，如果不需要經常執行檢查點清除活動並儲存磁碟 I/O，就能增加寫入所需的空間，進而改善寫入效能。重做記錄的總計大小，計算方式為 (innodb_log_file_size * innodb_log_files_in_group) 應足以在資料庫存取的忙碌時段容納至少 1 至 2 小時的寫入資料。

Cloud SQL 定義的預設值是 512 MB。Cloud SQL 還提供使用資料庫旗標增加 InnoDB 記錄檔大小的彈性。

注意：增加 InnoDB 記錄檔大小的值會使當機復原時間增加。

旗標 innodb_flush_log_at_trx_commit 可控制記錄檔資料清除至磁碟的頻率，以及是否每次交易修訂都要清除。

只要將 innodb_flush_log_at_trx_commit 的值變更為 0 或 2，就能提高唯讀備用資源的寫入效能。

Cloud SQL 不支援變更 Cloud SQL Primary 的耐用性設定。不過，Cloud SQL 確實允許變更唯讀備用資源的旗標。降低唯讀備用資源的耐用性可提升備用資源的寫入效能。這有助於解決備用資源的複製延遲。進一步瞭解 innodb_flush_log_at_trx_commit。

InnoDB 記錄檔緩衝區空間是 InnoDB 用來在記錄檔 (重做記錄) 中寫入的緩衝區大小。

如果資料庫中的交易 (插入、更新或刪除) 過大，且使用的緩衝區超過 16 MB，InnoDB 就必須在進行交易修訂前執行磁碟 IO，這會影響效能。為避免磁碟 IO，請提高 innodb_log_buffer_size 的值。

Cloud SQL 為 InnoDB 記錄緩衝區大小定義了預設值 16 MB。MySQL 狀態變數 innodb_log_waits 顯示 innodb_log_buffer_size 很小的次數，以及 InnoDB 在修訂交易前必須先等待清除發生的次數。如果 innodb_log_waits 的值大於 0 且持續增加，請使用資料庫旗標增加 innodb_log_buffer_size 的值，以提升效能。您可以在 MySQL 殼層 (CLI) 中執行下列查詢，以找出 innodb_log_buffer_size 和 innodb_log_waits 的值。這些查詢會顯示 MySQL 中的狀態變數和全域變數的值。

SHOW GLOBAL VARIABLES LIKE 'innodb_log_buffer_size';

SHOW GLOBAL STATUS LIKE 'innodb_log_waits';

InnoDB IO 容量定義了背景工作可用的 IOPS 數量 (例如緩衝區集區中清除的網頁和變更緩衝區中的合併資料)。

Cloud SQL 定義 innodb_io_capacity 預設值為 5,000，以及 innodb_io_capacity_max 預設值為 10,000。

這個預設選項最適合大多數工作負載，但如果工作負載有大量的寫入作業或在執行個體上有未套用的變更，且執行個體有足夠的 IOPS，那麼請考慮提高 innodb_io_capacity 和 innodb_io_capacity_max。您可以在 MySQL 殼層中使用下列查詢，找出套用的變更值：

mysql -e 'show engine InnoDB status \G;' | grep Ibuf

工作階段緩衝區是分配給個別工作階段的記憶體。如果您的應用程式或查詢包含大量的插入、更新、排序、彙整作業，且需要較高容量的緩衝區，則在特定工作階段中執行查詢時，若能定義高緩衝區值，就能避免效能負擔。使用者可以避免在全域層級進行過多的緩衝區分配，藉此增加所有連線的值，進而增加執行個體的記憶體總用量。變更下列緩衝區的預設值有助於提高查詢效能。您可以使用資料庫旗標來變更這些值。

sort_buffer_size、

join_buffer_size、

tmp_table_size、

max_heap_table_size

請注意，這些是每個工作階段緩衝區的值，提高限制可能會影響所有連線，且最終可能會導致整體記憶體用量增加。

如果資料庫執行個體 (單一或多個資料庫) 中的資料表數量過多 (超過數千個)，請增加 table_open_cache 和 table_definition_cache 的值，以加快開啟資料表的速度。

Table_definition_cache 可以加快開啟資料表的速度，而且每個資料表只會有一個項目。資料表定義快取使用的空間較少，且不會使用檔案描述元。如果字典物件快取中的資料表執行個體數量超過 table_definition_cache 限制，LRU 機制會開始將資料表執行個體標示為剔除，最後再從字典物件快取中移除這些執行個體，以騰出空間給新的資料表定義。每次開啟新的資料表空間時，系統都會執行這項程序。系統只會關閉閒置的資料表空間。這個剔除程序會降低資料表的開啟速度。

Table_open_cache 會定義所有執行緒的開啟資料表數量。您可以檢查 Opened_tables 狀態變數，確認是否需要增加資料表快取。如果 Opened_tables 的值為大而且您不常使用 FLUSH TABLES，可考慮增加 table_open_cache 變數的值。

Table_open_cache 和 table_definition_cache 可以設為執行個體中實際的資料表數量。進一步瞭解 Cloud SQL high-number-of-open-tables 建議工具。

注意事項：Cloud SQL 提供變更這些值的彈性。

將主鍵定義為資料表的實體內容，就能整理資料，進而加快記錄的查詢、擷取和排序作業，藉此提升效能。

最好採用整數值自動增加的主鍵，適合 OLTP 系統。

在以資料列為基礎的複製情境中，主鍵不存在是複製延遲的主要原因之一。

建立索引有助於更快擷取資料，進而改善讀取查詢的效能。為查詢的 WHERE、ORDER BY 和 GROUP BY 子句中使用的資料欄建立索引。

注意：過多或未使用的索引也可能降低資料庫的效能。

您可以執行效能測試或基準，確認設定是否已達到最佳狀態，或是否能調整硬體、MySQL 資料庫或結構定義設計的設定，獲得進一步改善。建議一次只變更一個參數，然後與基準化結果比較，看看是否有改善。

「連線集區」指的是建立及管理連線集區，供任何有需要的程序使用的做法。連線集區可以大幅提高應用程式的效能，同時減少整體資源用量。請參閱如何管理應用程式的連線，包括連線數量和逾時的詳細說明。

您可以使用 (跨可用區的多個) 唯讀備用資源來卸載主要執行個體的讀取工作負載。這樣做能減輕主要執行個體的負擔或負載，進而改善主要執行個體的效能。此外，唯讀備用資源也能提供更多資源讓讀取查詢使用。 

ProxySQL 是一種開放原始碼的高效能 MySQL Proxy，能夠轉送資料庫查詢。您可以使用這個資料庫水平調整 MySQL 適用的 Cloud SQL 資料庫。

執行長達幾分鐘或數小時的查詢已確定會導致效能降低。

• 「復原記錄」會儲存已變更資料列的舊版本，以便復原交易，並在交易中提供一致的讀取結果 (資料快照)。這些復原記錄會以連結清單的形式儲存，較新的版本會指向較舊的版本，以此類推。長時間執行的交易往往會延遲復原記錄檔的清除作業，因此復原記錄檔清單也會增加。InnoDB 必須掃遍大量的復原記錄檔和冗長的連結清單，導致效能降低。
• 長時間執行的查詢也會持續占用記憶體、緩衝區和鎖定等資源，導致其他查詢因為資源不足而受到影響。

單筆交易中的記錄變更 (更新、刪除、插入) 過多，資源 (鎖定、緩衝區) 就會遭大量記錄占用，因而可能導致磁碟 IO 發生記錄檔緩衝區溢位。其餘查詢必須等待資源或鎖定釋出。這會導致緩衝區集區中寫入過多資料，因而無法再進一步使用。復原這類大型交易也會降低資料庫的效能。為克服這個難題，建議將大額交易拆分為執行速度更快的小型交易。

一律將查詢作業最佳化以獲得最佳結果，也就是減少占用資源並加快執行速度。請查看 MySQL 查詢調整的相關建議。