首先，請安排要最佳化的查詢：

1. 找出需要最佳回應時間的資料庫問題
2. 找出經常執行的資料庫問題

在資料庫結構定義設計期間，必須最佳化這兩個類別。

交易是一種邏輯單元，其中所有的陳述式都會完整修訂或復原。交易是一種針對 MySQL 提供完整性、一致性、獨立性和耐用性 (ACID) 的功能。

在 MySQL 適用的儲存引擎 InnoDB 中，所有使用者活動都發生在交易中。根據預設，系統會啟用自動修訂模式，也就是說，每個 SQL 陳述式都會自行建立單一交易。如要在啟用自動修訂時執行多陳述式交易，請使用 START_TRANSACTION 或 START_TRANSACTION 明確啟動交易，並以 START_TRANSACTION 或 START_TRANSACTION 結尾。如果自動修訂模式已停用，工作階段一律會開啟交易，直到 COMMIT 或 COMMIT 結束該交易並啟動新的交易為止。

交易的的最佳做法就是盡量縮短其時間。這是因為長期交易存在幾項缺點，詳情請參閱本文。

1. 鎖定爭用時間較長，導致查詢速度變慢及查詢可能失敗

• 在交易期間，系統會保留 InnoDB 資料列層級鎖定
• 這可能會增加鎖定等待時間、鎖定逾時和死結，進而拖慢查詢速度或導致查詢完全失敗

2. 大量的復原記錄會造成伺服器效能降低

• 由於 InnoDB 多版本並行控制 (MVCC)，舊版的已變更資料列會儲存在復原記錄中，以便保持一致的讀取和復原作業。如果採用預設的可重複讀取隔離層級，復原記錄在交易完成前不會遭到清除。因此，長時間執行的交易會累積復原記錄。您可以透過 SHOW ENGINE INNODB STATUS 指令列函式的歷史記錄清單觀察和監控這個狀況。
• 如果歷史記錄清單的項目數量超過數百萬個，則伺服器效能會因復原區隔發生互斥爭用情形、讀取復原記錄檔的量增加，以及需要花較長時間掃遍已連結的復原記錄清單而受到負面影響。同時也會產生更多清除執行緒的工作。

3. 磁碟使用量增加

• 儲存在磁碟上的復原記錄會增加，無論是在系統資料表空間中還是在復原資料表空間中的復原記錄，都會增加

4. 關閉時間較長

• 在正常關閉期間，系統會復原進行中的交易。復原時間通常比達成作業所需的時間長。因此，伺服器關閉程序可能會因復原而需要較長時間。

5. 當機復原時間較長

• 在當機復原期間，InnoDB 會重複上一個查核點的交易，並展開非修訂交易。較長的交易會讓對應的步驟花費更長時間。

• SELECT 查詢
• 不會保留資料列鎖定
• 可能會導致復原記錄累積
• 請參閱下方「查詢最佳化」一節
• UPDATE/INSERT/DELETE 查詢
• 批次查詢的執行成效優於多次單資料列變更
• 分割並限制批次執行時間至幾秒內

• 考慮分解 SELECT 查詢
• 如果資料庫查詢之間存在應用程式邏輯，請考慮分割交易
• 評估每個陳述式會保留多少個列鎖定項目
• 評估執行順序，以盡量減少鎖定資料列
• 尋找縮減交易規模的機會

查詢可在應用程式端或資料庫端擷取。

開發時，記錄資料庫查詢及查詢的執行時間，兩者皆是極佳做法。應用程式端記錄可讓您輕鬆評估該查詢在其業務情境中的成效和效率。舉例來說，使用者可能會記錄每筆查詢的回應時間，或針對特定功能記錄回應時間。如要取得多陳述式交易的總執行時間，也相當簡單。

此外，從應用程式端記錄測得的查詢回應時間是一種端對端測量，包括網路時間。這能補足資料庫記錄的查詢執行時間，讓您輕鬆判斷問題是出在網路還是資料庫。

Cloud SQL MySQL 查詢洞察

Cloud SQL 查詢洞察工具可用於擷取、監控及診斷查詢。

查詢洞察功能可讓您根據執行時間和執行頻率，輕鬆找出熱門查詢。

這項工具提供篩選選項，包括時間範圍、資料庫、使用者帳戶和用戶端位址。當中的圖表可以顯示 CPU 使用率，以及 IO 和鎖定等待時間的明細。「熱門查詢和標記」表格會根據執行時間列出最熱門的查詢，並將查詢正規化。除了執行時間，這項工具還會提供「平均掃描列數」和「平均傳回的列數」等統計資料，深入分析查詢效率。

請參閱說明文件以瞭解這項產品提供的所有功能，以及如何啟用。

使用 performance_schema

在 MySQL 適用的 Cloud SQL 中，MySQL 8.0.26 以及具備 15 GB 以上記憶體的更高版本的系統預設會啟用 performance_schema 功能。如要啟用或停用該功能，您必須重新啟動執行個體。

當 performance_schema=ON 時，查詢陳述式工具會預設為啟用。sys.statement_analysis 資料表提供正規化查詢的匯總統計資料。這可回答以下這類問題：

• 哪些查詢會執行完整資料表掃描？
• full_scan/exec_count：找出查詢是否經常執行效率低落的完整資料表掃描作業
• 哪些查詢的執行速度緩慢？
• avg_latency：平均查詢執行時間
• 哪種查詢效率較低？
• rows_examined_avg/rows_examined_avg：用於讀取查詢。理想比例為 1。該比率越大，查詢效率就越差。
• rows_examined_avg/rows_examined_avg：用於寫入查詢。理想比例為 1。該比率越大，查詢效率就越差。
• 哪些查詢使用臨時資料表，且必須轉換為磁碟中的臨時資料表？
• tmp_disk_tables/tmp_disk_tables：查看 tmp_disk_tables/tmp_disk_tables 是否足夠
• 哪些查詢會使用檔案排序？
• rows_sorted/rows_sorted、rows_sorted/rows_sorted：用來識別大量排序的查詢，且可能使用較大的 rows_sorted

如果你使用 MySQL Workbench，該服務會根據系統檢視產生效能結構定義報表。報表中會有一個「高成本 SQL 陳述式」專區，提供查詢效能的深入分析資訊。

使用慢速記錄 + 工具

慢速記錄功能會擷取執行超過 long_query_time 的所有查詢。還會記錄查詢執行時間、鎖定時間、已檢查的資料列和已傳送的資料列。相較於使用一般記錄，額外的執行統計資料使其成為分析資料庫查詢時的更理想選擇。

建議您啟用慢速記錄功能。一般來說，long_query_time 應維持在合理的門檻，以便擷取您打算查看和最佳化的查詢。

log_output=FILE

slow_query_log=ON

long_query_time=2

一段時間過後，建議您將 long_query_time=0 設定為在短時間內擷取所有查詢，並取得查詢量和效能的總覽。

有一些工具 (例如 mysqldumpslow 和 pt-query-digest) 會擷取查詢簽名，並產生報告，顯示查詢統計資料。

此外，還有其他第三方監控工具可以針對查詢統計資料產生報告，例如：Percona Monitoring and Management、SolarWinds Database Performance Monitor (舊稱 VividCortex) 等。

擷取交易中的查詢後，下一步就是最佳化查詢。

EXPLAIN 指令提供查詢執行計畫，從 8.0.18 開始，EXPLAIN ANALYZE 指令會執行陳述式並產生 EXPLAIN 輸出及執行的時間。

MySQL 查詢洞察可以讓您輕鬆存取 EXPLAIN 方案。

要在輸出結果中尋找什麼內容？

• 「rows」欄位會顯示要讀取的列數
• IO 是最耗時的部分。如果查詢需要讀取大量資料，則速度可能較慢。如要瞭解大致的想法，請將已彙整的資料表中的「rows」相乘。以上述範例而言，會是 858 * 23523。如要從 t1 的 858 列中的每一列讀取 t2 的 23523 列資料，聽起來不太理想。因此，其最佳化便是在每次疊代時，減少從 t2 存取的資料量。
• 「type」欄位代表資料表彙整類型
• 輸入「index」表示已掃描索引。如果索引能滿足所有來自資料表的資料需求，「Extra」欄位就會顯示「Uses index」。
• 「range」類型代表不只使用索引，而且會提供範圍條件來限制資料掃描。
• 針對彙整後的後續資料表，「eq_ref」類型表示在此資料表中讀取上一個資料表的每個資料列組合的一列，這最有效。
• 輸入「ref」表示索引比對的尺寸是 1:m 而非 1:1。在這個資料表中，前一個資料表的每個資料列組合都會讀取多個資料列。
• 應避免的類型為「ALL」。這表示系統會對先前資料表中的每個資料列組合進行完整的資料表掃描。
• 「key」欄位會顯示目前使用的實際索引。
• 請根據索引基數選擇要使用的索引，但該索引基數可能已過時。因此，請務必驗證是否使用了選擇性最高的索引。  
• 「key_len」欄位代表金鑰長度 (以位元組為單位)。
• 透過多欄索引，key_len 會暗示出所使用的索引部分。舉例來說，如果索引包含 (col1, col2, col3)，且查詢條件為「col1 = n and col2 like ‘%string%’」，則只有 col1 會用於篩選索引。如果查詢可以變更為「col1 = n and col2 like ‘string%’」，則兩者 (col1、col2) 均會用於篩選索引。這項小幅變更可能會對查詢效能產生巨大影響。
• 「Extra」欄位包含查詢計畫的額外資訊
• 「使用臨時資料表」代表建立了內部的臨時資料表，這可能會產生磁碟上的臨時資料表
• 「使用檔案排序」代表排序功能無法使用任何索引，必須使用排序緩衝區，而且可能需要臨時的磁碟檔案
• 「使用索引」是指所有需要從此資料表取得的資料都包含在索引中，不需要讀取資料列

工作階段狀態變數可用來取得查詢執行作業詳細資料。

首先，請清除工作階段變數，然後執行查詢並檢查計數器。舉例來說，Handler_* 狀態會顯示資料存取模式和資料列數量。Created_* 會顯示是否已建立臨時資料表和/或磁碟上的臨時資料表。Sort_* 則會顯示排序合併通過的次數，以及已排序的列數。如要進一步瞭解工作階段變數，請參閱此說明文件。

SHOW PROFILE 陳述式可依據執行階段提供查詢執行時間，或許也會派上用場。

瞭解查詢執行計畫後，您可透過多種方式影響及最佳化查詢執行計畫。

• 新增或更新索引定義
• 要提高篩選效率，減少資料存取
• 如為排序，請避免檔案排序
• 更新索引統計資料 (若該功能處於關閉狀態)
• ANALYZE TABLE <tbl>;
• 然後重新檢查 EXPLAIN 方案的輸出內容
• 使用索引提示
• 索引提示可用來建議或強制使用特定索引進行篩選、彙整或排序依據以及分組依據
• 使用 STRAIGHT_JOIN 定義資料表彙整順序
• 使用最佳化器提示

為了針對特定查詢最佳化伺服器設定，強烈建議您使用工作階段層級變數，而不要變更會影響所有工作階段的全域值。

常用的工作階段值如下：

簡單來說，我們討論了查詢微調的三個面向：

• 撰寫查詢時做出謹慎的決定。這些決定是查詢效能、整體伺服器處理量和伺服器效能的驅動因素。
• 在應用程式端和資料庫端追蹤查詢執行資料。應用程式端的記錄很重要。可根據商業利益進行設定，並反映業務營運狀況。
• 最後，有多種工具可協助您瞭解查詢執行計畫、各步驟的相關費用，以及最佳化查詢的方式。