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

# 針對 Aurora MySQL 資料庫的查詢效能進行故障診斷
<a name="aurora-mysql-troubleshooting-query"></a>

MySQL 透過系統變數提供[查詢最佳化工具控制](https://dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html)，這些變數會影響查詢計劃的評估方式、可切換最佳化、最佳化工具與索引提示，以及最佳化工具成本模型。這些資料點不僅可以比較不同的 MySQL 環境，還可以比較先前的查詢執行計劃與目前的執行計劃，以及了解 MySQL 查詢在任何時間點的整體執行。

查詢效能取決於許多因素，包括執行計劃、資料表結構描述和大小、統計資料、資源、索引和參數組態。查詢調校需要識別瓶頸並最佳化執行路徑。
+ 尋找查詢的執行計劃，並檢查查詢是否使用適當的索引。您可以使用 `EXPLAIN` 並檢閱每個計劃的詳細資訊，以最佳化查詢。
+ Aurora MySQL 第 3 版 (與 MySQL 8.0 Community Edition 相容) 使用 `EXPLAIN ANALYZE` 陳述式。`EXPLAIN ANALYZE` 陳述式是一種分析工具，可顯示 MySQL 在查詢上花費的時間和原因。透過 `EXPLAIN ANALYZE`，Aurora MySQL 會規劃、準備和執行查詢，同時計數資料列並測量在執行計劃的各個點所花費的時間。查詢完成時，`EXPLAIN ANALYZE` 會列印計劃及其測量結果，而不是查詢結果。
+ 使用 `ANALYZE` 陳述式來更新您的結構描述統計資料。由於統計資料過時，查詢最佳化工具有時可以選擇不佳的執行計劃。這可能會導致查詢效能不佳，因為資料表和索引的基數預估不正確。[innodb\$1table\$1stats](https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html#innodb-persistent-stats-tables) 資料表的 `last_update` 欄會顯示您的結構描述統計資料上次更新的時間，這是「過時」的良好指標。
+ 可能會發生其他問題，例如未考慮資料表基數的資料分佈扭曲。如需詳細資訊，請參閱 MySQL 文件中的[估算 InnoDB 資料表的 ANALYZE TABLE 複雜性](https://dev.mysql.com/doc/refman/8.0/en/innodb-analyze-table-complexity.html)和 [MySQL 中的直方圖統計資料](https://dev.mysql.com/blog-archive/histogram-statistics-in-mysql/)。

## 了解查詢所花費的時間
<a name="ams-query-time"></a>

以下是判斷查詢所花費時間的方法：
+ [分析](https://dev.mysql.com/doc/refman/8.0/en/show-profile.html)
+ [效能結構描述](https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html)
+ [查詢最佳化工具](https://dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html)

**分析**  
根據預設，分析已停用。啟用分析，然後執行緩慢查詢並檢閱其設定檔。  

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

1. 識別花費最多時間的階段。根據 MySQL 文件中的[一般執行緒狀態](https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html)，讀取和處理 `SELECT` 陳述式的資料列通常是指定查詢生命週期中執行時間最長的狀態。您可以使用 `EXPLAIN` 陳述式來了解 MySQL 如何執行此查詢。

1. 檢閱緩慢查詢日誌以評估 `rows_examined` 和 `rows_sent`，以確保工作負載在每個環境中都類似。如需詳細資訊，請參閱[記錄 Aurora MySQL 資料庫](aurora-mysql-troubleshooting-logging.md)。

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

   ```
   SHOW TABLE STATUS\G;
   ```

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

   ```
   SHOW GLOBAL STATUS;
   ```

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

   ```
   SHOW FULL PROCESSLIST;
   
   SHOW ENGINE INNODB STATUS\G;
   ```

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

**效能結構描述**  
效能結構描述提供有關伺服器執行時期效能的實用資訊，同時對該效能的影響最小。這與提供資料庫執行個體相關結構描述資訊的 `information_schema` 不同。如需詳細資訊，請參閱[Aurora MySQL 上 Performance Insights 的效能結構描述概觀](USER_PerfInsights.EnableMySQL.md)。

**查詢最佳化工具追蹤**  
若要了解為何[選擇特定查詢計劃執行](https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html)，您可以設定 `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";
```

## 檢閱查詢最佳化工具設定
<a name="ams-query-parameters"></a>

相較於 Aurora MySQL 第 2 版 (與 MySQL 5.7 Community Edition 相容)，Aurora MySQL 第 3 版 (與 MySQL 8.0 Community Edition 相容) 有許多最佳化工具相關的變更。如果您有 `optimizer_switch` 的一些自訂值，建議您檢閱預設值的差異，並設定最適合工作負載的 `optimizer_switch` 值。我們也建議您測試 Aurora MySQL 第 3 版可用的選項，以檢查您的查詢如何執行。

**注意**  
Aurora MySQL 第 3 版針對 [innodb\$1stats\$1persistent\$1sample\$1pages](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages) 參數使用社群預設值 20。

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

```
SELECT @@optimizer_switch\G;
```

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


| 設定 | Aurora MySQL 第 2 版 | Aurora MySQL 第 3 版 | 
| --- | --- | --- | 
| batched\$1key\$1access | off | off | 
| block\$1nested\$1loop | on | on | 
| condition\$1fanout\$1filter | on | on | 
| derived\$1condition\$1pushdown | – | on | 
| derived\$1merge | on | on | 
| duplicateweedout | on | on | 
| engine\$1condition\$1pushdown | on | on | 
| firstmatch | on | on | 
| hash\$1join | off | on | 
| hash\$1join\$1cost\$1based | on | – | 
| hypergraph\$1optimizer | – | off | 
| index\$1condition\$1pushdown | on | on | 
| index\$1merge | on | on | 
| index\$1merge\$1intersection | on | on | 
| index\$1merge\$1sort\$1union | on | on | 
| index\$1merge\$1union | on | on | 
| loosescan | on | on | 
| materialization | on | on | 
| mrr | on | on | 
| mrr\$1cost\$1based | on | on | 
| prefer\$1ordering\$1index | on | on | 
| semijoin | on | on | 
| skip\$1scan | – | on | 
| subquery\$1materialization\$1cost\$1based | on | on | 
| subquery\$1to\$1derived | – | off | 
| use\$1index\$1extensions | on | on | 
| use\$1invisible\$1indexes | – | off | 

如需詳細資訊，請參閱 MySQL 文件中的[切換最佳化 (MySQL 5.7)](https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html) 和[切換最佳化 (MySQL 8.0)。](https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html)