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