最耗時的有效查詢統計資料

「最耗時的有效查詢」(也稱為「執行時間最長的查詢」) 是資料庫中有效查詢的清單，並按照執行時間排序。深入瞭解這些查詢，有助於在發生系統延遲和 CPU 使用率偏高時找出原因。

Spanner 提供內建資料表 SPANNER_SYS.OLDEST_ACTIVE_QUERIES，列出正在執行的查詢 (包括含有 DML 陳述式的查詢)，並依開始時間升序排序。不包括變更串流查詢。

如果正在執行的查詢數量眾多，由於系統會對這類資料的收集作業強制執行記憶體限制，因此結果可能僅限於部分查詢。因此，Spanner 提供額外的資料表 SPANNER_SYS.ACTIVE_QUERIES_SUMMARY，顯示所有有效查詢 (變更串流查詢除外) 的摘要統計資料。您可以使用 SQL 陳述式，從這兩個內建資料表擷取資訊。

本文將說明這兩個資料表，並展示使用這些資料表的範例查詢，最後說明如何使用這些資料表，協助減輕現行查詢造成的問題。

存取最耗時的有效查詢統計資料

SPANNER_SYS 資料只能透過 SQL 介面取得，例如：

• Google Cloud 控制台中的資料庫「Spanner Studio」頁面

• gcloud spanner databases execute-sql 指令

• executeSql 或 executeStreamingSql 方法

Spanner 不支援下列單一讀取方法搭配 SPANNER_SYS：

• 從資料表中的單一資料列或多個資料列執行強式讀取。
• 從資料表中的單一資料列或多個資料列執行過時讀取。
• 從次要索引中的單一資料列或多個資料列讀取。

OLDEST_ACTIVE_QUERIES 統計資料

SPANNER_SYS.OLDEST_ACTIVE_QUERIES 會傳回依開始時間排序的有效查詢清單。如果正在執行的查詢數量眾多，由於 Spanner 對這類資料的收集作業設有記憶體限制，結果可能只會顯示部分查詢。如要查看所有執行中查詢的統計資料摘要，請參閱ACTIVE_QUERIES_SUMMARY。

所有最耗時的有效查詢統計資料表的結構定義

資料欄名稱	類型	說明
START_TIME	TIMESTAMP	查詢的開始時間。
TEXT_FINGERPRINT	INT64	指紋是要求標記的雜湊，如果沒有標記，則是查詢文字的雜湊。
TEXT	STRING	查詢陳述式文字。
TEXT_TRUNCATED	BOOL	如果 TEXT 欄位中的查詢文字遭到截斷，這個值為 TRUE。如果查詢文字未遭截斷，這個值為 FALSE。
SESSION_ID	STRING	執行查詢的工作階段 ID。
QUERY_ID	STRING	查詢的 ID。您可以使用這個 ID 和 CALL cancel_query(query_id) 取消查詢。
CLIENT_IP_ADDRESS	STRING	要求查詢的用戶端 IP 位址。有時，系統可能會遮蓋用戶端 IP 位址。這裡顯示的 IP 位址與稽核記錄一致，並遵循相同的遮蓋準則。詳情請參閱「 稽核記錄中的呼叫端 IP 位址」。建議您只在需要用戶端 IP 位址時才提出要求，因為要求用戶端 IP 位址可能會導致額外延遲。
API_CLIENT_HEADER	STRING	用戶端的 api_client 標頭。
USER_AGENT_HEADER	STRING	Spanner 從用戶端收到的 user_agent 標頭。
SERVER_REGION	STRING	Spanner 根伺服器處理查詢的區域。詳情請參閱「查詢生命週期」。
PRIORITY	STRING	查詢的優先順序。如要查看可用的優先順序，請參閱 RequestOptions。
TRANSACTION_TYPE	STRING	查詢的交易類型。可能的值為 READ_ONLY、READ_WRITE 和 NONE。

查詢範例

您可以使用用戶端程式庫、Google Cloud CLI 或 Google Cloud 控制台，執行下列範例 SQL 陳述式。

列出執行時間最長的有效查詢

下列查詢會傳回依查詢開始時間排序的執行時間最長查詢清單。

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id,
       query_id,
       api_client_header,
       server_region,
       priority,
       transaction_type
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC;

查詢輸出

下表顯示執行上述查詢的輸出內容：

start_time	text_fingerprint	文字	text_truncated	session_id	query_id	api_client_header	server_region	優先順序	transaction_type
2025-05-20T03:29:54.287255Z	-3426560921851907385	SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b;	FALSE	AG46FS6K3adF	9023439241169932454	gl-go/1.25.0-20250216-RC00 gccl/1.73.0 gapic/1.73.0 gax/2.14.1 grpc/1.69.2	us-central1	PRIORITY_HIGH	READ_ONLY
2025-05-20T03:31:52.40808Z	1688332608621812214	SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE STARTS_WITH(s.FirstName, 'FirstName') LIMIT 1000000;	FALSE	AG46FS6paJPKDOb	2729381896189388167	gl-go/1.25.0-20250216-RC00 gccl/1.73.0 gapic/1.73.0 gax/2.14.1 grpc/1.69.2	us-central1	PRIORITY_HIGH	READ_WRITE
2025-05-20T03:31:52.591212Z	6561582859583559006	SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE a.SingerId > 10 LIMIT 1000000;	FALSE	AG46FS7Pb_9H6J6p	9125776389780080794	gl-go/1.25.0-20250216-RC00 gccl/1.73.0 gapic/1.73.0 gax/2.14.1 grpc/1.69.2	us-central1	PRIORITY_LOW	READ_ONLY

列出執行時間最長的前 2 項查詢

這個範例與上一個查詢略有不同，會傳回前 2 個執行時間最久的查詢，並依查詢的開始時間排序。

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 2;

查詢輸出

下表顯示執行上述查詢的輸出內容：

start_time	text_fingerprint	文字	text_truncated	session_id
2039-07-18T07:52:28.225877Z	-3426560921851907385	SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b;	否	ACjbPvYsuRt
2039-07-18T07:54:08.622081Z	-9206690983832919848	SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE STARTS_WITH(s.FirstName, 'FirstName') LIMIT 1000000;	否	ACjbPvaF3yK

ACTIVE_QUERIES_SUMMARY

SPANNER_SYS.ACTIVE_QUERIES_SUMMARY 統計資料表會顯示所有有效查詢的摘要統計資料。查詢會分組到下列儲存區：

• 超過 1 秒
• 超過 10 秒
• 超過 100 秒

ACTIVE_QUERIES_SUMMARY 的資料表結構定義

資料欄名稱	類型	說明
ACTIVE_COUNT	INT64	執行中的查詢總數。
OLDEST_START_TIME	TIMESTAMP	最舊執行中查詢的開始時間上限。
COUNT_OLDER_THAN_1S	INT64	超過 1 秒的查詢次數。
COUNT_OLDER_THAN_10S	INT64	超過 10 秒的查詢次數。
COUNT_OLDER_THAN_100S	INT64	超過 100 秒的查詢數量。

查詢可能會計入多個類別。舉例來說，如果查詢已執行 12 秒，則會計入 COUNT_OLDER_THAN_1S 和 COUNT_OLDER_THAN_10S，因為這項查詢符合兩項條件。

查詢範例

您可以使用用戶端程式庫、gcloud spanner 或 Google Cloud 主控台，執行下列範例 SQL 陳述式。

擷取執行中查詢的摘要

下列查詢會傳回執行中查詢的摘要統計資料。

SELECT active_count,
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;

查詢輸出

active_count	oldest_start_time	count_older_than_1s	count_older_than_10s	count_older_than_100s
22	2039-07-18T07:52:28.225877Z	21	21	1

限制

雖然我們的目標是盡可能提供最全面的洞察資料，但在某些情況下，查詢不會納入這些資料表傳回的資料中。

• 如果 DML 查詢 (UPDATE、INSERT、DELETE) 位於「套用變動」階段，則不會納入其中。

• 如果查詢因暫時性錯誤而重新啟動，就不會納入。

• 不含來自超載或無回應伺服器的查詢。

• 您無法在讀寫交易中讀取或查詢 OLDEST_ACTIVE_QUERIES 資料表。即使在唯讀交易中，系統也會忽略交易時間戳記，並一律傳回執行時的最新資料。在極少數情況下，系統可能會傳回 ABORTED 錯誤和部分結果；如果發生這種情況，請捨棄部分結果，然後再次嘗試查詢。

• 如果 CLIENT_IP_ADDRESS 欄傳回 <error> 字串，表示發生暫時性問題，不應影響其餘查詢。重試查詢，擷取用戶端 IP 位址。

使用執行中的查詢資料排解 CPU 使用率偏高的問題

排解 Spanner 資料庫的延遲問題時，查詢統計資料和交易統計資料可提供實用資訊。這些工具提供已完成查詢的相關資訊。不過，有時必須瞭解系統中正在執行的項目。舉例來說，假設 CPU 使用率相當高，您想回答下列問題。

• 目前正在執行的查詢數量是多少？
• 這些查詢是什麼？
• 有多少查詢作業已執行超過 100 秒？
• 哪個工作階段正在執行查詢？

根據上述問題的答案，您可以決定採取下列行動。

• 刪除執行查詢的工作階段，立即解決問題。
• 加入索引，提高查詢效能。
• 如果查詢與週期性背景工作相關聯，請降低查詢頻率。
• 找出發出查詢的使用者或元件，這些使用者或元件可能未獲授權執行查詢。

在本逐步導覽中，我們將檢查有效查詢，並判斷是否要採取任何行動。

擷取執行中查詢的摘要

在範例情境中，我們發現 CPU 使用率高於正常值，因此決定執行下列查詢，傳回有效查詢的摘要。

SELECT active_count,
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;

查詢會產生下列結果。

active_count	oldest_start_time	count_older_than_1s	count_older_than_10s	count_older_than_100s
22	2039-07-18T07:52:28.225877Z	21	21	1

結果發現有一項查詢的執行時間超過 100 秒。這在我們的資料庫中並不常見，因此我們想進一步調查。

擷取執行中查詢的清單

我們在上一個步驟中判斷出，有查詢的執行時間超過 100 秒。如要進一步調查，請執行下列查詢，傳回執行時間最長的前 5 個查詢的相關資訊。

SELECT start_time,
       text_fingerprint,
       text,
       text_truncated,
       session_id,
       query_id
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 5;

在本例中，我們在 2024 年 3 月 28 日下午 4 點 44 分 09 秒 (EDT) 左右執行查詢，並傳回下列結果。(您可能需要水平捲動畫面，才能看到完整輸出內容)。

start_time	text_fingerprint	文字	text_truncated	session_id	query_id
2024-03-28 16:44:09.356939+00:00	-2833175298673875968	select * from spanner_sys.oldest_active_queries	false	ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw	37190103859320827
2039-07-18T07:52:28.225877Z	-3426560921851907385	SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b;	false	ACjbPvaF3yKiNfxXFod2LPoFaXjKR759Bw1o34206vv0t7eOrD3wxZhu8U6ohQ	48946620525959556

表格會醒目顯示最舊的查詢 (特徵 = -2833175298673875968)。這是一項昂貴的CROSS JOIN。我們決定採取行動。

取消耗用大量資源的查詢

在這個範例中，我們發現某個查詢正在執行耗費大量資源的 CROSS JOIN，因此決定取消該查詢。我們在上一個步驟收到的查詢結果包含 query_id。我們可以執行下列 CALL cancel_query(query_id) GoogleSQL 指令和 spanner.cancel_query(query_id) PostgreSQL 指令，取消查詢。

GoogleSQL

CALL cancel_query(query_id)

PostgreSQL

CALL spanner.cancel_query(query_id)

舉例來說，在下列程式碼中，CALL 陳述式會取消 ID 為 37190103859320827 的查詢：

CALL cancel_query('37190103859320827')

注意： 在 Spanner 中，CALL 作業僅支援 cancel_query 程序呼叫。如要進一步瞭解預存程序，請參閱預存程序頁面 (GoogleSQL 或 PostgreSQL)。

您需要查詢 spanner_sys.oldest_active_queries 資料表，確認查詢已取消。

注意： CALL cancel_query 作業會盡量執行，如果查詢負載量過大，導致 Spanner 伺服器忙碌，系統可能無法取消查詢。如果發生這種情況，請嘗試再次執行 CALL cancel_query。

這份逐步操作說明將示範如何使用 SPANNER_SYS.OLDEST_ACTIVE_QUERIES 和 SPANNER_SYS.ACTIVE_QUERIES_SUMMARY 分析執行中的查詢，並視需要對導致 CPU 使用率偏高的查詢採取行動。當然，避免昂貴的作業，並為您的用途設計正確的結構，一律是較便宜的做法。如要進一步瞭解如何建構有效執行的 SQL 陳述式，請參閱 SQL 最佳做法。

後續步驟

• 瞭解其他內省工具。
• 瞭解 Spanner 為每個資料庫儲存的其他資訊。這些資訊儲存在資料庫的資訊結構定義資料表中。
• 進一步瞭解 Spanner 適用的 SQL 最佳做法。