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