如何從每個資料庫取得執行計畫
每個引擎都有兩種類型的計畫。一個 預估 計畫是最佳化器的預測,不會真正執行陳述式。而 實際 實際計畫會執行陳述式並記錄真實列數——這是唯一能看出最佳化器哪裡猜錯的方法。實際擷取也會執行插入、更新和刪除,因此測試資料的變更會包在你隨後復原的交易裡。
實際計畫——執行查詢,並在結果之後針對每個陳述式傳回一份計畫文件:
SET STATISTICS XML ON;
SELECT ... ; -- your query
SET STATISTICS XML OFF;
預估計畫——編譯但不執行。 SET SHOWPLAN_XML 必須是批次中唯一的陳述式,所以才會有 GO 分隔符:
SET SHOWPLAN_XML ON;
GO
SELECT ... ; -- your query
GO
SET SHOWPLAN_XML OFF;
在 SSMS 中,開啟 包含實際執行計畫 (Ctrl+M)或使用
顯示估計執行計畫 (Ctrl+L),然後在圖形化執行計畫上按滑鼠右鍵,選擇 執行計畫另存為… 來取得一個 .sqlplan 檔案,你可以在這裡開啟,或 顯示執行計畫 XML… 來複製 XML。已經在快取或查詢存放區裡的計畫也可以: sys.dm_exec_query_plan(plan_handle) 傳回 XML,並且
sys.query_store_plan.query_plan 會以文字形式保存。如果你從 SSMS 結果網格複製大型執行計畫,而檢視器回報 XML 不完整,那是網格把它截斷了(其 XML 上限設定於 Tools > Options > Query Results > SQL Server > Results to Grid);請改將計畫另存為檔案。
PostgreSQL:EXPLAIN (FORMAT JSON)
-- Actual plan: runs the query
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ... ;
-- Estimated plan: does not run it
EXPLAIN (FORMAT JSON) SELECT ... ;
-- Measure a data change without keeping it
BEGIN;
EXPLAIN (ANALYZE, FORMAT JSON) UPDATE ... ;
ROLLBACK;
檢視器讀取的是 JSON 格式,而不是 PostgreSQL 的預設文字輸出。直接從 psql 貼上也沒問題—— QUERY PLAN 標頭與 + 會被移除——或者執行 psql -XqAt -c "EXPLAIN (ANALYZE, FORMAT JSON) ..." 以獲得乾淨的輸出。對於應用程式所執行查詢的計畫, auto_explain 模組可以使用
auto_explain.log_format = json.
-- Estimated plan as JSON: does not run the query
EXPLAIN FORMAT=JSON SELECT ... ;
-- Actual plan (MySQL 8.0.18 and later): runs the query
EXPLAIN ANALYZE SELECT ... ;
兩者都能讀取,檢視器會靠形狀分辨。mysql 客戶端的表格邊框、
\G 列標題與「1 row in set」頁尾會在貼上時被移除。較新的 MySQL 版本也可能輸出第二種 JSON 版面(explain_json_format_version = 2);檢視器讀的是原本那份,所以請執行 SET explain_json_format_version = 1; ,如果你的伺服器預設使用第 2 版。
-- Estimated plan: does not run the query
EXPLAIN FORMAT=JSON SELECT ... ;
-- Actual plan with r_rows, r_loops and timings: runs the query
ANALYZE FORMAT=JSON SELECT ... ;
EXPLAIN QUERY PLAN SELECT ... ;
SQLite 不會為 EXPLAIN QUERY PLAN,而它的計畫不含任何成本或列數估算——只有步驟,但這仍足以看出一個 SCAN 應該是
SEARCH。 sqlite3 shell 的樹狀結構(QUERY PLAN,
|--, `--)會照原樣讀取,原始的也一樣
id | parent | notused | detail 其他工具傳回的資料列。在 shell 中,
.eqp on 在你執行的每個陳述式之前印出計畫。
-- Estimated plan
EXPLAIN (FORMAT JSON) SELECT ... ;
-- Actual plan with per-operator rows and timing: runs the query
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ... ;
貼上 JSON,來源為 explain_value 欄位。DuckDB 沒有成本單位,因此實際執行計畫會依運算子的耗時深淺著色。
EXPLAIN json = 1, indexes = 1
SELECT ...
FORMAT TSVRaw;
indexes = 1 會加上每個主鍵、分區鍵和跳過索引各保留了多少部分與顆粒;檢視器就是靠這個找出完全沒有修剪任何東西的讀取。 FORMAT TSVRaw
傳回未轉義的 JSON。ClickHouse 沒有 EXPLAIN ANALYZE,所以它的執行計畫永遠是估計出來的。
-- Estimated plan: does not run the query
EXPLAIN PLAN FOR SELECT ... ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Actual plan: run the query with row-source statistics, then read its cursor
SET SERVEROUTPUT OFF
SET LINESIZE 300
SELECT /*+ GATHER_PLAN_STATISTICS */ ... ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
貼上整個輸出,包括 Predicate Information 部分 - 檢視器會將每個
access 和 filter 述詞附加到其操作上。 DISPLAY_CURSOR
需要 SELECT 權限才能存取 V$SESSION, V$SQL, V$SQL_PLAN 和
V$SQL_PLAN_STATISTICS_ALL,並直接授予使用者。請保持 SERVEROUTPUT 關閉,否則它顯示的「最後一個游標」會是輸出呼叫而不是你的查詢;並加寬
LINESIZE 這樣計畫表格才不會換行。