1. 項目概述從“慢”到“快”的數據庫調優實戰最近在幾個生產環境的達夢數據庫項目上又處理了一批性能卡頓的工單。看著開發同事發來的“頁面轉圈圈”截圖和動輒幾十秒的SQL執行時間我意識到很多朋友對達夢數據庫的SQL優化尤其是執行計劃這個核心工具的掌握還停留在比較基礎的層面。大家可能知道要建索引但為什么建了索引有時反而更慢面對一個復雜的多表關聯查詢優化器為什么選擇了那個看起來“很笨”的全表掃描路徑這些問題光靠猜測和試錯是解決不了的必須深入到執行計劃的層面去理解數據庫的“思考”過程。達夢作為一款成熟的關系型數據庫其SQL執行引擎的優化器已經相當智能但它依然需要清晰、準確的指令也就是我們寫的SQL和合適的數據結構如表設計、索引來發揮最大效能。SQL優化不是玄學而是一門結合了數據庫原理、統計信息解讀和實戰經驗的工程技術。本次分享我將以一個多年DBA和開發者的雙重視角拆解達夢SQL優化的核心路徑并重點聚焦于執行計劃的解讀——這是所有優化工作的“地圖”和“診斷報告”。無論你是剛接觸達夢的開發者還是需要維護系統性能的運維工程師掌握這套方法都能讓你在面對性能問題時從被動響應變為主動洞察。2. 達夢SQL優化核心思路與工具箱在動手優化任何一條SQL之前確立正確的思路比掌握一堆零散的技巧更重要。達夢SQL優化的核心目標是在保證業務結果正確的前提下盡可能減少數據庫服務端的資源消耗主要是CPU和I/O和執行時間。這個目標可以分解為幾個遞進的原則。2.1 優化器的工作邏輯與我們的干預點達夢的SQL優化器本質上是一個“成本估算器”。當你提交一條SQL時優化器會做以下幾件事語法語義解析檢查SQL語句是否正確確認表、列是否存在權限是否足夠。生成邏輯執行計劃根據SQL的語義生成一系列可能的操作順序比如先關聯哪兩張表在哪里應用過濾條件。生成物理執行計劃為邏輯計劃中的每一個操作選擇具體的執行算法。例如對于表關聯JOIN是使用嵌套循環NESTED LOOP、哈希連接HASH JOIN還是歸并連接MERGE JOIN。對于數據訪問是使用全表掃描FULL SCAN還是索引掃描INDEX SCAN。成本估算基于數據庫收集的統計信息如表的數據量、索引的區分度、數據分布直方圖為每一個物理執行計劃計算出一個預估的“成本”。成本單位是抽象的但綜合反映了預期的I/O和CPU開銷。選擇最優計劃從所有候選的物理計劃中選擇預估成本最低的一個將其編譯成最終的可執行代碼也就是我們看到的執行計劃。我們的優化工作大部分時候是在“幫助”或“引導”優化器做出更明智的選擇。主要干預點有兩個提供更優的SQL寫法避免導致優化器難以理解或產生高成本計劃的語法。例如在WHERE子句中對索引列使用函數或運算會使索引失效。提供更優的數據結構創建合適的索引是降低數據訪問成本最直接的手段。但索引不是越多越好需要權衡查詢加速與增刪改操作的維護開銷。提供準確的統計信息優化器的成本估算嚴重依賴統計信息。如果統計信息過期例如表剛導入了大量新數據但未更新統計信息優化器可能會基于錯誤的數據量做出糟糕的選擇比如該用索引時卻選擇了全表掃描。2.2 必備的監控與診斷工具在達夢數據庫中我們有幾個必須熟悉的工具來定位性能問題V$SESSIONS 和 V$SQL_HISTORY這是實時監控的入口。通過查詢V$SESSIONS可以找到當前正在執行的、消耗資源多的會話。V$SQL_HISTORY則記錄了歷史SQL的執行信息包括執行時間、物理讀/邏輯讀次數是定位“慢SQL”TOP榜單的關鍵視圖。-- 查找當前正在運行且耗時較長的SQL SELECT sess_id, sql_text, state, last_recv_time FROM V$SESSIONS WHERE state ACTIVE AND sql_text IS NOT NULL ORDER BY last_recv_time; -- 查詢歷史慢SQL示例查找最近1小時執行時間超過5秒的SQL SELECT SQL_TEXT, EXEC_TIME, N_EXEC, LOGIC_READ, PHYS_READ FROM V$SQL_HISTORY WHERE START_TIME SYSDATE - 1/24 -- 最近1小時 AND EXEC_TIME 5000 -- 執行時間大于5000毫秒 ORDER BY EXEC_TIME DESC;ET執行時間跟蹤這是達夢提供的非常輕量級的SQL性能剖析工具。通過在SQL前加上ET( )可以輸出該SQL執行過程中各個操作步驟的詳細耗時。ET( SELECT * FROM large_table WHERE user_name test );執行后會在消息窗口或輸出文件中看到一份詳細的耗時報告精確告訴你時間花在了解析、優化、還是數據讀取上。這對于區分“網絡慢”和“數據庫慢”非常有用。AWR報告達夢性能診斷工具對于需要深度分析系統級性能瓶頸的場景達夢的AWR自動工作負載倉庫報告是終極武器。它定期采集系統快照生成包含等待事件、TOP SQL、鎖爭用、內存和I/O分析的詳細報告。生成AWR報告通常需要使用SP_CREATE_SYSTEM_SNAPSHOTS()創建快照然后通過管理工具或特定腳本生成。注意ET和AWR是診斷利器但切忌在生產環境高峰時段頻繁執行ET或生成AWR因為其本身也有一定開銷。通常先通過V$SQL_HISTORY鎖定問題SQL再在測試環境或業務低峰期進行深度剖析。3. 執行計劃深度解讀看懂數據庫的“作戰地圖”拿到一條慢SQL后最核心的動作就是查看并解讀它的執行計劃。執行計劃以樹形結構展示了SQL的執行路徑每個節點代表一個操作符operator。讀懂它你就知道了數據庫準備如何“打仗”。3.1 獲取執行計劃的幾種方式EXPLAIN 命令最常用、最標準的方式。它只生成執行計劃并不實際執行SQL因此沒有副作用。EXPLAIN SELECT a.*, b.department_name FROM employee a JOIN department b ON a.dept_id b.id WHERE a.salary 10000;在達夢的管理工具如DM管理工具或DBeaver中執行通常會以表格或圖形化的方式展示計劃。在SQL前加上SET STAT ON這種方式會實際執行SQL并在執行結束后輸出詳細的執行計劃及實際的運行時統計信息如實際返回行數、實際耗時等。這對于驗證優化器估算是否準確至關重要。SET STAT ON; SELECT a.*, b.department_name FROM employee a JOIN department b ON a.dept_id b.id WHERE a.salary 10000; SET STAT OFF; -- 記得關閉3.2 關鍵操作符解析與性能含義執行計劃由一系列操作符構成。理解常見操作符的含義和開銷是解讀計劃的基礎。下面以一個虛擬的復雜查詢計劃為例拆解關鍵節點#NSET2: [1, 1000, 156] #PRJT2: [1, 1000, 156] #NESTED LOOP INNER JOIN2: [1, 1000, 156] #INDEX SCAN: EMPLOYEE(IDX_EMP_SALARY), [1, 100, 104] #INDEX SCAN: DEPARTMENT(PK_DEPARTMENT), [1, 10, 52]我們自上而下、從內到外解讀最內層葉子節點數據訪問路徑#INDEX SCAN: EMPLOYEE(IDX_EMP_SALARY), [1, 100, 104]操作通過索引IDX_EMP_SALARY掃描EMPLOYEE表。估算信息[1, 100, 104]這是關鍵。三個數字通常分別表示估算成本、估算返回行數、估算輸出結果集字節數。這里成本為1很低估算返回100行。#INDEX SCAN: DEPARTMENT(PK_DEPARTMENT), [1, 10, 52]操作通過主鍵索引PK_DEPARTMENT掃描DEPARTMENT表。估算返回10行。中間層節點連接操作#NESTED LOOP INNER JOIN2: [1, 1000, 156]操作對上述兩個結果集進行嵌套循環連接。這是連接算法的一種。優化器選擇它通常是因為驅動表第一個INDEX SCAN結果集較小100行且內層表第二個INDEX SCAN有高效的索引訪問路徑主鍵。估算信息成本仍為1說明優化器認為這是一個高效計劃估算最終連接后產生1000行數據100 * 10。最外層節點結果處理#PRJT2投影操作負責選擇最終需要輸出的列。#NSET2結果集收集操作負責將結果返回給客戶端。需要警惕的高開銷操作符CSCN2或FULL SCAN全表掃描。當表很大且沒有合適的索引時這是性能殺手。看到它首先要問為什么優化器不用索引是索引缺失還是SQL寫法導致索引失效HASH JOIN哈希連接。當連接的兩個表都很大且沒有高效的索引用于嵌套循環時優化器可能選擇哈希連接。它需要在內存在構建哈希表如果結果集巨大可能導致內存溢出使用磁盤臨時空間性能急劇下降。SET STAT ON看到的實際“溢出”次數是重要指標。SORT2排序。如果SQL中有ORDER BY、GROUP BY非索引列或DISTINCT就可能出現排序操作。排序是CPU和內存密集型操作對于大數據集非常消耗資源。考慮是否可以通過索引來避免排序例如在ORDER BY的列上建立索引。SLCT2過濾。這個操作本身開銷不大但它出現的位置很重要。理想情況下過濾條件應盡可能在靠近數據源的掃描階段CSCN2或INDEX SCAN就應用以減少后續操作處理的數據量。如果SLCT2出現在計劃樹的很上層意味著大量數據被傳遞上來后才被過濾這通常不是好現象。3.3 如何判斷一個執行計劃的好壞看數據訪問路徑盡量讓查詢通過索引定位數據避免CSCN2全表掃描。尤其是對于大表索引掃描的成本通常遠低于全表掃描。看連接順序與算法觀察多表關聯時哪張表被選為驅動表。通常應該將過濾后結果集更小的表作為驅動表。連接算法的選擇NESTED LOOP vs HASH JOIN vs MERGE JOIN要適合數據特征。看估算與實際的差異使用SET STAT ON對比估算行數和實際行數。如果差異巨大例如估算100行實際返回10萬行說明統計信息不準確優化器基于錯誤信息制定了糟糕的計劃。這是導致性能問題的一個非常常見的原因。看額外開銷操作警惕不必要的SORT2排序、DISTINCT等。思考業務是否真的需要這些操作或者能否通過索引消除它們。4. 實戰優化從診斷到解決的完整流程讓我們結合一個模擬的慢SQL案例走一遍完整的優化流程。假設我們有一個訂單系統orders表有千萬級數據現在有一個查詢速度很慢。4.1 案例訂單明細查詢優化原始慢SQLSELECT o.order_no, o.order_date, c.customer_name, SUM(oi.amount) as total_amount FROM orders o JOIN order_items oi ON o.id oi.order_id JOIN customers c ON o.customer_id c.id WHERE o.order_date DATEADD(month, -1, SYSDATE) -- 查詢近一個月的訂單 AND o.status SHIPPED AND c.region EAST GROUP BY o.order_no, o.order_date, c.customer_name ORDER BY o.order_date DESC;執行時間超過30秒。第一步獲取并解讀執行計劃使用EXPLAIN或SET STAT ON查看計劃。假設我們看到的計劃關鍵部分如下#NSET2: [高成本] #SORT2: [高成本] -- 排序開銷大 #HASH2 GROUP BY: [高成本] -- 哈希分組 #NESTED LOOP INNER JOIN2: [較高成本] #HASH2 INNER JOIN: [高成本] -- 哈希連接 #CSCN2: ORDERS [成本極高] -- 全表掃描 #CSCN2: CUSTOMERS [成本高] #INDEX SCAN: ORDER_ITEMS(IDX_ITEMS_ORDER_ID)問題診斷最嚴重問題對ORDERS和CUSTOMERS表進行了全表掃描CSCN2。這是耗時的主要根源。連接與分組由于驅動表數據量巨大全表掃描的結果導致使用了高開銷的HASH2 INNER JOIN和HASH2 GROUP BY。排序最終的ORDER BY導致了SORT2操作。第二步針對性優化優化1為過濾條件創建復合索引ORDERS表上的WHERE條件涉及order_date和status。創建一個復合索引可以高效定位數據。CREATE INDEX IDX_ORDERS_DATE_STATUS ON ORDERS(order_date, status);實操心得在復合索引中將區分度更高即唯一值更多的列放在前面通常過濾效果更好。這里order_date是范圍查詢status是等值查詢達夢優化器可以有效地利用這個索引進行范圍掃描過濾。優化2為連接條件確保索引存在CUSTOMERS表被region過濾并且通過id與ORDERS關聯。確保CUSTOMERS.id是主鍵已有索引。在CUSTOMERS.region上創建索引加速過濾。CREATE INDEX IDX_CUSTOMERS_REGION ON CUSTOMERS(region);ORDER_ITEMS表通過order_id與ORDERS關聯已有索引IDX_ITEMS_ORDER_ID這很好。優化3更新統計信息在創建新索引后務必更新相關表的統計信息讓優化器了解新的數據分布。CALL SP_STAT_ON_TABLE(SYSDBA, ORDERS); CALL SP_STAT_ON_TABLE(SYSDBA, CUSTOMERS); -- 或者使用DBMS_STATS包如果版本支持第三步驗證優化效果再次執行EXPLAIN新的計劃可能變為#NSET2: [成本顯著降低] #SORT2: [成本降低] #HASH2 GROUP BY: [成本降低] #NESTED LOOP INNER JOIN2: #NESTED LOOP INNER JOIN2: -- 連接算法變為嵌套循環 #INDEX SCAN: ORDERS(IDX_ORDERS_DATE_STATUS) -- 全表掃描消失 #INDEX SCAN: CUSTOMERS(IDX_CUSTOMERS_REGION) -- 全表掃描消失 #INDEX SCAN: ORDER_ITEMS(IDX_ITEMS_ORDER_ID)最關鍵的改變是CSCN2全表掃描被INDEX SCAN取代。驅動表的數據量從“全表”縮減為“近一個月且狀態為SHIPPED的訂單”可能只有幾萬行。這使得優化器可以選擇更高效的NESTED LOOP連接并且后續的分組和排序操作處理的數據量也大大減少。實測查詢時間從30秒以上降至1秒內。4.2 進階優化技巧與模式除了加索引還有一些寫法上的技巧避免在索引列上使用函數或計算-- 壞索引失效 SELECT * FROM orders WHERE YEAR(order_date) 2024; -- 好利用索引范圍掃描 SELECT * FROM orders WHERE order_date 2024-01-01 AND order_date 2025-01-01;謹慎使用SELECT *只獲取需要的列。特別是當表中有大字段如CLOB,BLOB時SELECT *會導致不必要的I/O和內存消耗。這也可能影響覆蓋索引的使用。合理使用WITH(CTE) 和子查詢復雜的查詢可以拆分成多個CTE提高可讀性。但要注意達夢優化器可能會將CTE物化作為臨時結果集對于簡單查詢有時不如直接連接高效。需要結合執行計劃判斷。分頁查詢優化對于深度分頁LIMIT ... OFFSET很大使用ORDER BY索引列 條件過濾WHERE id ?的方式比傳統的OFFSET性能好得多。5. 常見問題排查與避坑指南在實際操作中你會遇到各種“詭異”的情況。這里記錄一些典型問題和排查思路。5.1 為什么建了索引卻沒生效這是最常見的問題之一。除了上面提到的“對索引列使用函數”還有以下可能統計信息過期索引創建后如果沒有及時更新統計信息優化器可能不知道這個索引的存在或它的高效性。解決方法創建或重建索引后執行SP_STAT_ON_INDEX(模式名,表名,索引名)或更新整個表的統計信息。數據傾斜嚴重如果某個列的值99%都是‘A’那么查詢WHERE col A時優化器可能認為全表掃描比索引掃描更快因為要返回幾乎全部數據。解決方法這種情況下索引確實意義不大需要考慮其他過濾條件或業務設計。復合索引順序不當對于WHERE a ? AND b ?索引(a, b)有效而(b, a)可能效果很差。解決方法根據查詢條件的最左前綴原則設計復合索引。使用了OR條件WHERE indexed_col ? OR non_indexed_col ?這樣的條件可能導致優化器放棄使用索引。解決方法考慮改寫為UNION ALL兩個查詢。5.2 執行計劃突然變差Plan Regression昨天還很快的查詢今天突然慢了。很可能發生了“執行計劃回退”。首要懷疑對象統計信息。是否有定時作業更新了統計信息但新統計信息反而產生了誤導或者表數據量發生了劇烈變化如大批量導入/刪除而未更新統計信息排查比較當前和歷史上的執行計劃如果開啟了SQL審計或AWR并手動更新統計信息看是否恢復。參數綁定Bind Peeking問題對于使用綁定變量的SQL達夢在第一次硬解析時會根據傳入的綁定變量值來生成計劃。如果第一次傳入的值不具有代表性例如查詢一個不存在的ID返回0行優化器可能生成一個針對“小數據量”的計劃。當后續傳入一個典型值返回大量數據時這個計劃就變得非常低效。解決方法對于數據分布極不均勻的列考慮不使用綁定變量或者使用提示HINT強制索引。系統資源變化內存不足可能導致哈希連接溢出到磁盤或排序操作變慢。排查檢查V$MEM_POOL、V$BUFFERPOOL等視圖確認內存使用情況。5.3 鎖爭用導致的性能假象有時SQL本身沒問題但執行時被阻塞了表現為“慢”。現象查詢長時間處于“執行中”但CPU和I/O不高。通過V$SESSIONS查看該會話的BLOCKED字段為TRUEWAIT_EVENT顯示等待鎖。排查查詢V$LOCK和V$TRXWAIT視圖找出誰阻塞了誰。解決優化事務設計避免長事務和大事務盡快提交或回滾。在讀寫頻繁的鍵上考慮使用SELECT ... FOR UPDATE NOWAIT或樂觀鎖機制。5.4 工具連接與配置相關從熱搜詞看很多朋友在基礎工具使用上遇到問題這也間接影響優化工作。連接工具DBeaver、達夢自帶的DM管理工具、IDEA插件都是不錯的選擇。確保使用的JDBC驅動版本與數據庫服務器版本兼容。權限問題如“授予用戶schema權限”錯誤通常是因為達夢中用戶和模式SCHEMA概念緊密關聯。GRANT權限給用戶時需要明確權限到具體模式下的對象。GRANT SELECT ON SYSDBA.TABLE1 TO USER_A;而不是簡單授予模式權限。Docker鏡像達夢官方提供了Docker鏡像方便搭建測試環境。下載后注意初始化數據和端口映射配置。SQL優化是一個持續的過程沒有一勞永逸的方案。核心在于養成習慣監控 - 抓取慢SQL - 解讀執行計劃 - 針對性優化 - 驗證效果。把執行計劃這張“地圖”看懂了你就掌握了在達夢數據庫性能世界里導航的能力。每當解決一個棘手的性能問題那份對系統更深一層的理解就是這份工作最大的樂趣所在。