充分運用 MySQL 效能：查詢微調
資料庫是應用程式效能的重要關鍵。MySQL 資料庫也不例外。因此，請務必瞭解資料庫調整、設計和設定可以透過多種方式提高應用程式效能。您可以透過下列幾種方式最佳化 MySQL，以達到最佳效能。
相關文章
總覽
查詢調整
從應用程式開始執行資料庫效能調整。應用程式如何將業務需求轉化為資料庫查詢，對於應用程式的複雜度和效率有重大影響。實際效能是各個資料庫執行個體對業務需求貢獻的效率。
結構定義設計
在關聯資料庫中定義實體和關係的方式，會決定回應資料庫查詢的直接性和複雜程度。此外，主鍵和次要索引的定義方式也扮演著重要的角色。
伺服器設定
伺服器設定負責最佳化及充分提高系統資源的使用率。系統資源是核心處理單元 (CPU) 核心、實體機器或虛擬機器 (VM) 上的記憶體、基礎儲存系統和網路。
動態伺服器微調
持續監控、最佳化和效能微調，以配合實際動態調整資料庫工作負載。
本文著重於查詢微調。後續文章將涵蓋其他主題。
我們通常會藉由查看資料庫查詢來啟動查詢微調程序。建議的做法是先評估業務需求翻譯為資料庫查詢的效率。企業對於查詢的解讀與處理作業，決定查詢費用的多寡。
設計查詢
首先，請安排要最佳化的查詢：
- 找出需要最佳回應時間的資料庫問題
- 找出經常執行的資料庫問題
在資料庫結構定義設計中，這兩個類別必須進行最佳化。
縮短交易
交易是一種邏輯單元，其中所有包含的陳述式都可以完全提交或復原。交易可提供 MySQL 的原型、一致性、隔離性和耐用性 (ACID)。
在 MySQL 儲存引擎 InnoDB 中，所有使用者活動都在交易中發生。自動提交模式預設為啟用，這代表每個 SQL 陳述式都會形成單一交易。如要在已啟用自動修訂版本時執行多陳述式交易，請以 START_TRANSACTION 或 BEGIN 明確啟動交易，並使用 COMMIT 或 ROLLBACK 來結束。如果自動修訂模式已停用，工作階段會保持開啟交易，直到 COMMIT 或 ROLLBACK 結束並啟動新的。
交易的最佳做法是盡可能縮短交易時間。這是因為長時間執行的交易有幾項缺點，將於稍後說明。
長時間執行的交易的缺點
- 較長的鎖定爭用事件，這會導致查詢速度變慢及潛在的查詢失敗
- 在交易期間會保留 InnoDB 資料列層級鎖定
- 可能會增加鎖定等待時間、鎖定逾時和死結，導致查詢執行速度緩慢或完全失敗
- 大量復原記錄造成伺服器效能降低
- 基於 InnoDB 多重版本並行控制 (MVCC) 的緣故，舊版資料列變更會儲存於復原記錄檔，以提供一致的讀取與復原作業。在預設的可重複讀取隔離層級中，系統會等到開始的交易早於交易完成，才會清除復原記錄。因此，長時間執行的交易會累積復原記錄。如要觀察及監控這種情況，可以使用 SHOW ENGINE INNODB STATUS 指令列函式的記錄清單。
- 當記錄清單超過百萬個時，伺服器效能會受到負面影響，因為復原區隔的互斥爭用情況會增加、讀取復原記錄的數量增加，以及掃遍復原記錄的連結清單的時間增加。也會為清除執行緒建立更多工作。
- 增加的磁碟使用量
- 增加儲存在磁碟上的復原記錄，無論是系統資料表空間或復原資料表空間中的復原記錄，都會增加
- 關閉時間較長
- 一般關閉期間，進行中的交易將復原。復原作業所需時間通常會比達到這個檢查點所需的時間長。因此，伺服器關閉作業可能需要長時間才能完成復原。
- 延長當機復原時間
- 在當機復原期間，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_sent_avg：適用於讀取查詢。理想的比率是 1。比率越大，查詢的效率就越低。
- rows_examined_avg/rows_affected_avg：這適用於寫入查詢。理想的比率是 1。比率越大，查詢的效率就越低。
- 哪些查詢會使用臨時資料表，且必須轉換為磁碟上的臨時資料表？
- tmp_disk_tables/tmp_tables：查看 tmp_table_size/max_heap_table_size 是否足夠
- 哪些查詢使用檔案排序？
- rows_sorted/exec_count、sort_merge_passes/exec_count：用來識別大量排序的查詢，且可能使用較大的 sort_buffer_size
如果您使用 MySQL Workbench，就能依據 sys 檢視提供效能結構定義報表。這份報表含有「高成本 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-diges) 會擷取查詢簽名，並產生報告，顯示查詢統計資料。
此外，還有其他第三方監控工具可以針對查詢統計資料產生報告，例如：Percona Monitoring and Management、SolarWinds Database Performance Monitor (舊稱 VividCortex) 等。
最佳化查詢
擷取交易中的查詢後，下一步就是針對這些查詢進行最佳化。
EXPLAIN - 報表重點
EXPLAIN 指令提供查詢執行計畫，從 8.0.18 開始，EXPLAIN ANALYZE 指令會執行陳述式並產生 EXPLAIN 輸出及執行的時間。
MySQL 查詢洞察可以讓您輕鬆存取 EXPLAIN 方案。
輸出內容有什麼要注意的？
- `rows` 欄位會顯示要讀取的列數
- IO 是最耗時的部分。如果查詢需要讀取大量資料，處理速度可能較慢。如要瞭解這件事，請在彙整後的資料表中乘以 `rows`。在上述範例中，也就是 858 * 23523。如要讀取 t2 中各 23523 列，來自 t1 的 858 列中的每一列都不太理想。因此，最佳化是減少每項疊代在 t2 中的資料存取量。
- `type` 欄位代表資料表彙整類型
- 輸入 ‘index’ 表示已掃描索引。如果該索引符合資料表中的所有必要資料，「額外」欄位就會顯示「使用索引」。
- 「range」類型代表不只使用索引，而且會提供範圍條件來限制資料掃描。
- 針對彙整後的後續資料表，‘eq_ref’ 類型表示在此資料表中讀取上一個資料表的每個資料列組合的一列，這最有效。
- 輸入 ‘ref’ 表示索引比對的尺寸是 1:m 而非 1:1。在這個資料表中，前一個資料表的資料列組合會讀取多個資料列。
- 應避免的類型為「全部」。這表示系統會針對前一個資料表內的各個資料列組合執行完整的資料表掃描作業。
- `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 定義資料表彙整順序
- 使用最佳化提示
針對工作階段執行最佳化作業
為了針對特定查詢最佳化伺服器設定，強烈建議您使用工作階段層級變數，而不要變更影響所有工作階段的全域值。
常用的工作階段值如下：
總結
簡單來說，我們討論了查詢微調的三個方面：
- 撰寫查詢時，請小心謹慎。以上因素為查詢效能、整體伺服器處理量和伺服器效能的驅動因素。
- 在應用程式端和資料庫端追蹤查詢執行資料。應用程式端記錄非常重要。可以根據業務利益設定並反映業務營運。
- 最後，我們提供多種工具，協助您瞭解查詢執行計畫、與不同步驟相關的費用，以及將查詢最佳化的方法。
相關產品和服務
Google Cloud 提供代管的 MySQL 資料庫，可滿足您的業務需求，包括淘汰地端部署資料中心、執行軟體式服務 (SaaS) 應用程式，以及遷移核心業務系統等。
