MySQL 效能最佳化提示
效能最佳化是管理任何資料庫的重要環節。從選擇託管庫資料庫伺服器所需的硬體和軟體元件，到資料模型設計和結構定義設定，都可以在資料庫管理的每個步驟執行效能最佳化。本文說明雲端中 MySQL 資料庫的效能最佳化提示，特別是 MySQL 適用的 Cloud SQL，包括將新資料庫執行個體化以及將現有資料庫最佳化的最佳做法。
硬體注意事項
硬體設定是資料庫效能的重要考量因素。在定義硬體設定之前，務必充分掌握應用程式的活躍和並行使用者數量、資料庫和索引的大小，以及預期的應用程式或服務延遲時間。以下是一些重要的硬體注意事項：
中央處理器 (CPU)
處理效能是效能資料庫系統最重要的因素之一。並行連線數/使用者/執行緒數量會決定處理資料庫要求所需的核心數量。分配至資料庫的 CPU 必須能夠處理一般工作負載和尖峰 (極端) 工作負載，讓應用程式以最佳級別執行。
以 Google Cloud 全代管的 MySQL 產品 Cloud SQL 來說，CPU 會以虛擬 CPU (vCPU) 的形式分配。分配至資料庫的 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 資料庫設定的最佳做法，藉此提升效能。
版本
在建立新的資料庫時，選擇最新版的 MySQL。與較舊版本相比，最新版本已經修正錯誤並進行最佳化，以提升效能。Cloud SQL 提供市場上最新版本的 MySQL，並在建立新的資料庫時設為預設版本。進一步瞭解 Cloud SQL 支援的 MySQL 版本。
InnoDB 緩衝區集區大小
以 MySQL 執行個體來說，InnoDB 是唯一支援的儲存引擎。InnoDB 緩衝集區大小是使用者想要定義的第一個參數以獲得最佳效能。緩衝集區是記憶體區域，用來在清除前儲存資料表快取、索引快取和修改後的資料，並儲存其他內部結構 (例如自動調整雜湊索引 (AHI))。
Cloud SQL 根據執行個體的大小，定義要在 InnoDB 緩衝集區中分配的執行個體的記憶體 (約 72%) 的預設值 (預設值因執行個體大小而異)。進一步瞭解不同執行個體大小的緩衝集區設定。Cloud SQL 可讓您根據應用程式的需求使用資料庫旗標，靈活調整緩衝集區的大小。
緩衝區集區的大小應經過調整，除了 InnoDB 緩衝集區之外，讓執行個體可提供足夠的 free 記憶體用於工作階段緩衝區、字典快取、performance_schema 資料表 (如啟用)。
使用者可以查看從執行個體發生的磁碟讀取，指出從磁碟讀取的資料量，以及緩衝區符合的讀取量。如果磁碟讀取作業數增加，則增加緩衝集區的大小和執行個體記憶體可改善讀取查詢的效能。
重做記錄/InnoDB 記錄檔大小
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 主要執行個體的耐用性設定。然而，Cloud SQL 可以針對唯讀備用資源變更旗標。降低唯讀備用資源的耐用性，可提高備用資源的寫入效能。這有助於解決備用資源的複製延遲。進一步瞭解 innodb_flush_log_at_trx_commit。
InnoDB 記錄檔緩衝區空間
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 中的狀態變數和全域變數的值。
顯示全域變數 LIKE 'innodb_log_buffer_size';
顯示全域狀態 LIKE 'innodb_log_waits';
InnoDB IO 容量
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
工作階段緩衝區
工作階段緩衝區是為各個工作階段分配的記憶體。如果您的應用程式或查詢含有大量的插入、更新、排序、彙整及需要較高的緩衝區，於特定工作階段執行查詢時定義高緩衝區的值可以避免效能負擔。使用者可以避免過度的全域層級的緩衝區分配，進而增加所有連線的值，並增加執行個體的總記憶體用量。變更下列緩衝區的預設值有助於改善查詢效能。您可以使用資料庫旗標來變更這些值。
請注意，這些限制是以工作階段緩衝區值為基礎，提高上限可能會影響所有連線，最終增加整體記憶體用量。
Table_open_cache 和 Table_definition_cache
如果資料庫執行個體 (單一或多個資料庫) 中的資料表數量過多 (超過數千個)，請增加 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 查詢調整的相關建議。
效能調整工具
監控
Cloud SQL 為多項 Google Cloud 產品提供預先定義的資訊主頁，包括預設的 Cloud SQL 監控資訊主頁。使用者可以使用這個資訊主頁監控主要執行個體和備用資源執行個體的一般健康狀態。使用者也可以建立自己的自訂資訊主頁，以呈現感興趣的指標。透過這些資訊主頁和指標，您可以依據先前列出的建議找出並解決各種效能瓶頸，例如高 CPU 或高記憶體使用量。您也可以根據這些指標設定快訊。
慢速查詢旗標
您可以在 MySQL 適用的 Cloud SQL 執行個體上啟用慢速查詢旗標，以識別執行時間超過 long_query_time 的查詢。您可以進一步分析及調整這些慢速查詢，藉此提升效能。瞭解如何為 Cloud SQL 執行個體啟用及檢查慢速查詢。
查詢洞察
查詢洞察是 Cloud SQL 的原生功能，可讓您剖析並分析查詢，藉此改善查詢效能。查詢洞察功能支援直覺化的監控功能，並提供診斷資訊，協助您進一步偵測並找出效能問題的根本原因。
相關產品和服務
Google Cloud 提供代管的 MySQL 資料庫，可滿足您的業務需求，包括淘汰地端部署資料中心、執行軟體式服務 (SaaS) 應用程式，以及遷移核心業務系統等。
