針對適用於 MySQL 的 Azure 資料庫 - 彈性伺服器進行查詢效能的疑難排解
適用於：
適用於 MySQL 的 Azure 資料庫 - 單一伺服器適用於 MySQL 的 Azure 資料庫 - 彈性伺服器
重要
適用於 MySQL 的 Azure 資料庫單一伺服器已位於淘汰路徑上。 強烈建議您升級至適用於 MySQL 的 Azure 資料庫彈性伺服器。 如需移轉至適用於 MySQL 的 Azure 資料庫彈性伺服器的詳細資訊，請參閱 適用於 MySQL 的 Azure 資料庫單一伺服器發生了什麽狀況?
查詢效能可能會受到多個因素影響，因此請務必先查看您在適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體中遇到的徵兆範圍。 例如，查詢效能緩慢的原因是：
- 在適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體上執行的所有查詢？
- 一組特定的查詢？
- 一個特定的查詢？
也請記住，您查詢的資料表結構或基礎資料的任何最近變更可能會影響效能。
啟用記錄功能
分析個別查詢之前，您需要定義查詢基準。 透過這項資訊，您可以在資料庫伺服器上實作記錄功能，以追蹤超出根據應用程式需求所指定閾值的查詢。
使用適用於 MySQL 的 Azure 資料庫彈性伺服器時，建議您使用慢速查詢記錄功能來識別執行超過 N 秒的查詢。 從慢速查詢記錄識別查詢之後，您可以使用 MySQL 診斷來針對這些查詢進行疑難排解。
您必須先使用 Azure 入口網站或 Azure CLI 來啟用 slow_query_log 參數，才能開始追蹤長時間執行的查詢。 啟用此參數後，您也應該設定 long_query_time 參數的值，以指定查詢在識別為「執行緩慢」查詢之前可以執行的秒數。 參數的預設值為 10 秒，但您可以調整該值以應對應用程式 SLA 的需求。
雖然慢速查詢記錄是追蹤長時間執行查詢的絕佳工具，但在某些情況下可能無效。 例如，慢速查詢記錄：
- 如果查詢數目很高，或查詢語句很大，則會對效能造成負面影響。 請據此調整
long_query_time參數的值。 - 如果您也已啟用
log_queries_not_using_index參數，這將會指定用來記錄預期擷取所有資料列的查詢，因此可能不會有所幫助。 執行完整索引掃描的查詢可利用索引，但系統會記錄查詢，因為索引不會限制傳回的資料列數目。
從記錄擷取資訊
記錄最多可以從建立開始算起保留七天。 您可以透過 Azure 入口網站或 Azure CLI 列出並下載慢速查詢記錄。 在 Azure 入口網站中，瀏覽至您的伺服器，在 [監視] 下，選取 [伺服器記錄]，然後選取專案旁的向下箭號，以下載與所調查日期和時間相關聯的記錄。
此外，如果您的慢速查詢記錄透過診斷記錄與 Azure 監視器記錄整合，您可以在編輯器中執行查詢，以進一步分析：
AzureDiagnostics
| where Resource == '<your server name>'
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10
注意
如需深入了解如何透過診斷記錄診斷慢速查詢記錄的範例，請參閱分析 Azure 監視器記錄中的記錄。
下列快照集描述範例慢速查詢。
# Time: 2021-11-13T10:07:52.610719Z
# User@Host: root[root] @ [172.30.209.6] Id: 735026
# Query_time: 25.314811 Lock_time: 0.000000 Rows_sent: 126 Rows_examined: 443308
use employees;
SET timestamp=1596448847;
select * from titles where DATE(from_date) > DATE('1994-04-05') AND title like '%senior%';;
請注意，查詢會在 26 秒內執行、檢查超過 443k 個資料列，並傳回 126 個結果資料列。
通常，您應該專注於具有高 Query_time 和 Rows_examined 值的查詢。 不過，如果您注意到具有高 Query_time 但只有少數 Rows_examined 的查詢，這通常表示資源瓶頸存在。 在這些情況下，您應該檢查是否有任何 IO 節流或 CPU 使用量。
分析查詢
識別出特定執行緩慢的查詢之後，您可以使用 EXPLAIN 命令和分析來收集其他詳細資料。
若要檢查查詢計劃，請執行下列命令：
EXPLAIN <QUERY>
注意
如需使用 EXPLAIN 陳述式的詳細資訊，請參閱 如何使用 EXPLAIN 來分析適用於 MySQL 的 Azure 資料庫彈性伺服器中的查詢效能。
除了建立查詢的 EXPLAIN 方案之外，您還可以使用 SHOW PROFILE 命令，這可讓您診斷目前工作階段內已執行的陳述式執行。
若要在工作階段中啟用分析並分析特定查詢，請執行下列一組命令：
SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;
注意
分析個別查詢只能在工作階段中使用，而且無法分析歷程記錄陳述式。
讓我們進一步了解使用這些命令來分析查詢。 首先，啟用目前工作階段的分析，執行 SET PROFILING = 1 命令：
mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
接下來，執行進行完整資料表掃描的次佳查詢：
mysql> select * from sbtest8 where c like '%99098187165%';
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 10 | 5035785 | 81674956652-89815953173-84507133182-62502329576-99098187165-62672357237-37910808188-52047270287-89115790749-78840418590 | 91637025586-81807791530-84338237594-90990131533-07427691758 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (27.60 sec)
然後，執行 SHOW PROFILES 命令來顯示所有可用查詢設定檔的清單：
mysql> SHOW PROFILES;
+----------+-------------+----------------------------------------------------+
| Query_ID | Duration | Query |
+----------+-------------+----------------------------------------------------+
| 1 | 27.59450000 | select * from sbtest8 where c like '%99098187165%' |
+----------+-------------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
最後，若要顯示查詢 1 的設定檔，請執行 SHOW PROFILE FOR QUERY 1 命令。
mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status | Duration |
+----------------------+-----------+
| starting | 0.000102 |
| checking permissions | 0.000028 |
| Opening tables | 0.000033 |
| init | 0.000035 |
| System lock | 0.000018 |
| optimizing | 0.000017 |
| statistics | 0.000025 |
| preparing | 0.000019 |
| executing | 0.000011 |
| Sending data | 27.594038 |
| end | 0.000041 |
| query end | 0.000014 |
| closing tables | 0.000013 |
| freeing items | 0.000088 |
| cleaning up | 0.000020 |
+----------------------+-----------+
15 rows in set, 1 warning (0.00 sec)
列出資料庫伺服器上最常使用的查詢
每當您針對查詢效能進行疑難排解時，了解哪些查詢最常在適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體上執行會很有幫助。 您可以使用這項資訊來測量是否有任何前幾個查詢花費的時間超過一般執行時間。 此外，開發人員或 DBA 可以使用這項資訊來識別是否有任何查詢突然增加查詢執行計數和持續時間。
若要針對您適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體列出前 10 個最常執行的查詢，請執行下列查詢:
SELECT digest_text AS normalized_query,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time,
Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time,
Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime,
sum_rows_affected AS sum_rows_changed,
sum_rows_sent AS sum_rows_selected,
sum_rows_examined AS sum_rows_scanned,
sum_created_tmp_tables,
sum_select_scan,
sum_no_index_used,
sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;
注意
使用此查詢來基準測試資料庫伺服器中最上層執行的查詢，並判斷頂端查詢中是否有變更，或初始基準中的任何常設查詢是否在運行期間內增加。
依總執行時間列出 10 個最昂貴的查詢
下列查詢的輸出提供針對資料庫伺服器執行的前 10 個查詢及其在資料庫伺服器上執行次數的相關資訊。 它也會提供其他有用的資訊，例如查詢延遲、其鎖定時間、在查詢執行階段中建立的臨時資料表數目等等。使用此查詢輸出來追蹤資料庫上的熱門查詢，以及延遲等因素的變更，這可能表示有機會進一步微調查詢，以協助避免任何未來的風險。
SELECT REPLACE(event_name, 'statement/sql/', '') AS statement,
count_star AS all_occurrences ,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency,
Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency,
Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time ,
sum_rows_affected AS sum_rows_changed,
sum_rows_sent AS sum_rows_selected,
sum_rows_examined AS sum_rows_scanned,
sum_created_tmp_tables, sum_created_tmp_disk_tables,
IF(sum_created_tmp_tables = 0, 0, Concat( Truncate(sum_created_tmp_disk_tables /
sum_created_tmp_tables * 100, 0))) AS
tmp_disk_tables_percent,
sum_select_scan,
sum_no_index_used,
sum_no_good_index_used
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE event_name LIKE 'statement/sql/%'
AND count_star > 0
ORDER BY sum_timer_wait DESC
LIMIT 10;
監視 InnoDB 記憶體回收
當 InnoDB 記憶體回收遭到封鎖或延遲時，資料庫可以開發大幅清除延遲，這可能會對儲存體使用率和查詢效能有負面影響。
InnoDB 復原區段歷程記錄清單長度 (HLL) 測量復原記錄中儲存的變更記錄數目。 成長的 HLL 值表示 InnoDB 的記憶體回收執行緒 (清除執行緒) 無法跟上寫入工作負載，或清除遭到長時間執行的查詢或交易封鎖。
記憶體回收中若是有過多延遲，可能會造成嚴重的負面結果：
- InnoDB 系統資料表空間將會擴充，進而加速基礎儲存體磁碟區的成長。 有時候，系統資料表的空間可能會因為封鎖清除而增加數 TB 之多。
- 標記為刪除的記錄不會及時移除。 這可能會導致 InnoDB 資料表空間成長，並組織引擎重新使用這些記錄所佔用的儲存體。
- 所有查詢的效能可能因此而降低，CPU 使用率也可能會因為 InnoDB 儲存體結構的成長而增加。
因此，請務必監視 HLL 值、模式和趨勢。
尋找 HLL 值
您可以執行 show engine innodb status 命令來尋找 HLL 值。 此值將會列在輸出的 TRANSACTIONS 標題底下：
mysql> show engine innodb status\G
*************************** 1. row ***************************
(...)
------------
TRANSACTIONS
------------
Trx id counter 52685768
Purge done for trx's n:o < 52680802 undo n:o < 0 state: running but idle
History list length 2964300
(...)
您也可以查詢 information_schema.innodb_metrics 資料表來判斷 HLL 值：
mysql> select count from information_schema.innodb_metrics
-> where name = 'trx_rseg_history_len';
+---------+
| count |
+---------+
| 2964300 |
+---------+
1 row in set (0.00 sec)
解譯 HLL 值
解譯 HLL 值時，請考慮下表所列的指導方針：
| 值 | 注意事項 |
|---|---|
| 小於 ~10,000 | 一般值，表示記憶體回收並未落後。 |
| 介於 ~10,000 到 ~1,000,000 | 這些值表示記憶體回收略有延遲。 如果這些值保持穩定且不會增加，則或許尚可接受。 |
| 大於 ~1,000,000 | 應該調查這些值，而且可能需要進行更正動作 |
定址過多的 HLL 值
如果 HLL 顯示大量尖峰或顯示定期成長的模式，請立即調查適用於 MySQL 的 Azure 資料庫彈性伺服器執行個體上執行的查詢和交易。 然後，您可以解決可能防止記憶體回收程序進度的任何工作負載問題。 雖然並未預期資料庫完全沒有清除延遲，但您不應讓延遲無法控制地增長。
例如，若要從 information_schema.innodb_trx 資料表取得交易資訊，請執行下列命令：
select * from information_schema.innodb_trx
order by trx_started asc\G
trx_started 資料行中的詳細資料可協助您計算交易存留期。
mysql> select * from information_schema.innodb_trx
-> order by trx_started asc\G
*************************** 1. row ***************************
trx_id: 8150550
trx_state: RUNNING
trx_started: 2021-11-13 20:50:11
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 19
trx_query: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(…)
如需目前資料庫工作階段的相關資訊，包括工作階段目前狀態所花費的時間，請檢查 information_schema.processlist 資料表。 例如，下列輸出會顯示過去 1462 秒主動執行查詢的工作階段：
mysql> select user, host, db, command, time, info
-> from information_schema.processlist
-> order by time desc\G
*************************** 1. row ***************************
user: test
host: 172.31.19.159:38004
db: employees
command: Query
time: 1462
info: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(...)
建議
請確定您的資料庫已配置足夠的資源來執行查詢。 有時候，您可能需要相應增加執行個體大小，以取得更多 CPU 核心和額外的記憶體，來容納您的工作負載。
將交易分成較小的交易，以避免出現大型或長時間執行的交易。
根據您的工作負載設定 innodb_purge_threads，以改善背景清除作業的效率。
注意
針對每個環境測試此伺服器變數的任何變更，以量測引擎行為的變更。
使用「主機 CPU 百分比」、「主機記憶體百分比」和「連線總數」的警示，以便在系統超過任何指定的臨界值時收到通知。
使用查詢效能深入解析或 Azure Workbooks 來識別任何有問題或執行速度緩慢的查詢，然後將其最佳化。
針對生產資料庫伺服器，定期收集診斷，以確保所有事項都能順利執行。 如果沒有，則請針對您識別的任何問題進行疑難排解，並加以解決。
下一步
若想知道是否有人可解答您最重要的問題，或是要張貼或回答問題，請造訪 Stack Overflow。