針對適用於 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 命令：
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 命令來顯示所有可用查詢設定檔的清單：
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 命令。
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 標題底下：
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 來識別任何有問題或執行速度緩慢的查詢，然後將其最佳化。
針對生產資料庫伺服器，定期收集診斷，以確保所有事項都能順利執行。 如果沒有，則請針對您識別的任何問題進行疑難排解，並加以解決。