疑難排解 Aurora MySQL 資料庫的查詢效能 - Amazon Aurora
了解查詢花費的時間檢閱查詢最佳化工具

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

疑難排解 Aurora MySQL 資料庫的查詢效能

MySQL 透過影響查詢計畫評估方式、可切換的最佳化、最佳化工具和索引提示，以及最佳化工具成本模型的系統變數，提供查詢最佳化工具控制。這些數據點不僅可以在比較不同的 MySQL 環境時很有幫助，還可以將以前的查詢執行計劃與當前的執行計劃進行比較，並了解 MySQL 查詢在任何時候的整體執行情況。

查詢效能取決於許多因素，包括執行計劃、表格結構描述和大小、統計資料、資源、索引以及參數組態。查詢調整需要識別瓶頸並最佳化執行路徑。

  • 尋找查詢的執行計畫，並檢查查詢是否使用適當的索引。您可以使用EXPLAIN和檢閱每個計劃的詳細資料，將查詢最佳化。

  • Aurora MySQL 版本 3（與 MySQL 8.0 社區版兼容）使用了一個EXPLAIN ANALYZE聲明。該EXPLAIN ANALYZE語句是一個分析工具，顯示 MySQL 在您的查詢上花費時間以及為什麼。Aurora MySQL 使EXPLAIN ANALYZE用計劃、準備和執行查詢，同時計算資料列和測量在執行計畫的各個點花費的時間。查詢完成時，會EXPLAIN ANALYZE列印計劃及其度量，而非查詢結果。

  • 使用ANALYZE陳述式更新您的結構描述統計資料。由於統計資料過時，查詢最佳化工具有時會選擇不良的執行計畫。這可能會導致查詢效能不佳，因為資料表和索引的基數估計不正確。innodb_table_stats 資料表的資last_update料欄會顯示上次更新結構描述統計資料的時間，這是一個很好的「失效」指標。

  • 可能會發生其他問題，例如未考慮資料表基數的分佈偏斜。有關更多信息，請參閱 MySQL 文檔中的評估 InnoDB 表的分析表複雜性和 MySQL 中的直方圖統計信息。

了解查詢花費的時間

以下是確定查詢所花費時間的方法：

分析

依預設，會停用效能分析。啟用效能分析，然後執行緩慢查詢並檢閱其設定檔。

SET profiling = 1;
Run your query.
SHOW PROFILE;

  1. 確定花費最多時間的階段。根據 MySQL 文檔中的常規線程狀態，讀取和處理SELECT語句的行通常是給定查詢生命週期內運行時間最長的狀態。您可以使用該EXPLAIN語句來了解 MySQL 如何運行此查詢。

  2. 檢閱慢速查詢記錄檔以進行評估，rows_examined並rows_sent確定工作負載在每個環境中都是相似的。如需詳細資訊，請參閱 Aurora MySQL 資料庫的記錄。

  3. 針對屬於已識別查詢一部分的資料表執行下列命令：

    SHOW TABLE STATUS\G;

  4. 在每個環境上執行查詢之前和之後擷取下列輸出：

    SHOW GLOBAL STATUS;

  5. 在每個環境上執行下列命令，以查看是否有任何其他查詢/工作階段會影響此範例查詢的效能。

    SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G;

    有時候，當伺服器上的資源忙碌時，它會影響伺服器上的所有其他作業，包括查詢。您也可以在執行查詢時定期擷取資訊，或設定cron工作，以便以有用的間隔擷取資訊。

效能結構描述

效能結構描述提供有關伺服器執行階段效能的實用資訊，同時對該效能的影響最小。這與提供有關資information_schema料庫執行個體的結構描述資訊不同。如需詳細資訊，請參閱 Aurora Performance Insights 的效能結構描述概觀我的 SQL 或我的 SQL。

查詢優化器跟踪

要了解為什麼選擇特定的查詢計劃進行執行，您可optimizer_trace以設置訪問 MySQL 查詢優化器。

執行最佳化處理程式追蹤，以顯示最佳化處理程式可用的所有路徑及其選擇的詳盡資訊。

SET SESSION OPTIMIZER_TRACE="enabled=on"; 
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;

-- Run your query.
SELECT * FROM table WHERE x = 1 AND y = 'A';

-- After the query completes:
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";

檢閱查詢最佳化工具

Aurora MySQL 第 3 版（與 MySQL 8.0 社區版兼容）與 Aurora MySQL 版本 2 相比，具有許多與優化器相關的更改（與 MySQL 5.7 社區版兼容）。如果您有一些自訂值optimizer_switch，建議您檢閱預設值中的差異，並設定最適合您工作負載的optimizer_switch值。我們也建議您測試 Aurora MySQL 第 3 版可用的選項，以檢查查詢的執行方式。

注意

Aurora MySQL 第 3 版使用社群預設值 20 做為群組狀態 _ 持久性參數。

您可以使用以下命令來顯示optimizer_switch值：

SELECT @@optimizer_switch\G;

下表顯示 Aurora MySQL 版本 2 和 3 的預設optimizer_switch值。

設定 Aurora MySQL 第 2 版 Aurora MySQL 第 3 版
batched_key_access off off
block_nested_loop on on
condition_fanout_filter on on
derived_condition_pushdown – on
derived_merge on on
duplicateweedout on on
engine_condition_pushdown on on
firstmatch on on
hash_join off on
hash_join_cost_based on –
hypergraph_optimizer – off
index_condition_pushdown on on
index_merge on on
index_merge_intersection on on
index_merge_sort_union on on
index_merge_union on on
loosescan on on
materialization on on
mrr on on
mrr_cost_based on on
prefer_ordering_index on on
semijoin on on
skip_scan – on
subquery_materialization_cost_based on on
subquery_to_derived – off
use_index_extensions on on
use_invisible_indexes – off

如需詳細資訊，請參閱 MySQL 文件中的可切換最佳化 (MySQL 5.7) 和可切換的最佳化 (MySQL 8.0)。