达梦数据库多表关联SQL优化深度解析

---

一、多表关联原理与连接方式选择策略

1. 达梦优化器核心工作机制

达梦数据库采用基于成本的优化器（CBO），其决策流程分为以下阶段：

1. 语法解析：生成逻辑执行计划树。
2. 统计信息加载：读取表、索引、列的统计信息（DBMS_STATS维护）。
3. 连接顺序排列：计算所有可能的表连接排列组合（受OPTIMIZER_MAX_PERM限制）。
4. 连接方式选择：对每种排列评估嵌套循环（Nested Loop）、哈希连接（Hash Join）、排序合并（Merge Sort）的成本。
5. 最终计划生成：选择总成本最低的执行路径。

2. 三大连接方式对比与适用场景

(1) 嵌套循环连接（Nested Loop Join）

工作原理：

for each row in outer_table:  
    for each row in inner_table where join_condition:  
        output result row

适用场景：

• 外层表数据量小（驱动表优选）
• 内层表有高效索引（尤其是等值查询）
• OLTP场景中高选择性查询

达梦优化提示：

/*+ USE_NL(A, B) */   
-- 强制使用NL连接

(2) 哈希连接（Hash Join）

工作原理：

1. 构建阶段：将小表数据加载到内存，构建哈希桶。
2. 探测阶段：扫描大表，计算哈希值匹配桶内数据。

适用场景：

• 无索引的大表关联
• 等值连接且内存充足
• OLAP场景中大数据量处理

达梦优化提示：

/*+ USE_HASH(table1,table2) */  
-- 强制使用哈希连接

(3) 排序合并连接（Merge Sort Join）

工作原理：

1. 对两表按连接键排序。
2. 双指针遍历有序数据集，合并匹配行。

适用场景：

• 数据已预排序（如索引覆盖）
• 非等值连接（如BETWEEN、范围查询）
• 内存资源有限时替代哈希连接

达梦优化提示：

/*+ USE_MERGE(table1,table2) */  
-- 强制使用排序合并

3. 连接方式选择决策矩阵

条件	嵌套循环	哈希连接	排序合并
外层表行数	< 1万行	> 10万行	任意
内层表索引	必须有高效索引	无需索引	有序结构更佳
连接类型	等值/非等值	等值	等值/非等值
内存使用	低	高	中
典型场景	OLTP高频小查询	OLAP批量处理	数据仓库范围查询

二、选择率分析与计算模型

1. 单条件选择率计算

选择率（Selectivity） = 满足条件的行数 / 总行数

(1) 等值查询

-- 列唯一时：sel = 1/NDV （NDV为列去重值数量）
SELECT 1/COUNT(DISTINCT id) FROM table;  

-- 示例：id 列 NDV=1000，则sel=0.001

(2) 范围查询

-- 假设均匀分布：sel = (high - val)/(high - low)
-- 示例：age BETWEEN 20 AND 30，age范围10-50
sel = (30-20)/(50-10) = 0.25

(3) 模糊查询

-- LIKE 'abc%'：sel ≈ 1/NDV_prefix  
-- 使用前缀直方图估算

2. 多条件选择率合成

假设条件间独立：

sel = sel1 * sel2 * ... * selN  

修正因子（避免低估）：

sel = 1 - (1 - sel1)(1 - sel2)...(1 - selN)  

示例：组合条件选择率

WHERE age > 30 AND dept = 'IT'  
-- age>30的sel=0.4，dept='IT'的sel=0.1  
-- 独立假设下：sel=0.4*0.1=0.04  
-- 修正后：1 - (1-0.4)(1-0.1) = 1 - 0.6*0.9 = 0.46  
-- 达梦可能根据列相关性动态调整

3. 达梦统计信息管理

• 收集统计信息：

CALL DBMS_STATS.GATHER_TABLE_STATS('大写的用户名', '大写的表名',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

• 查看统计信息：

SELECT ST.* FROM SYSSTATS ST
WHERE ID IN (SELECT ID
FROM SYS.SYSOBJECTS WHERE NAME = '大写的表名' AND SYSOBJECTS.SCHID = (SELECT ID
FROM SYS.SYSOBJECTS  WHERE TYPE$ = 'SCH'
AND SYSOBJECTS.NAME = '大写的用户名'))
ORDER BY ST.LAST_GATHERED DESC;

• 直方图类型：
  • 频率直方图（NDV小）
  • 等高直方图（NDV大）

---

三、多表关联优化实战案例

案例1：错误使用嵌套循环导致性能下降

场景：

SELECT * FROM orders o 
JOIN customers c ON o.cust_id = c.id  
WHERE o.amount > 1000;  

• orders表：100万行，amount>1000的选择率0.2
• customers表：10万行，id为主键

问题：
优化器选择嵌套循环，导致外层orders扫描20万行，每次驱动索引查customers，总成本高。

优化：

SELECT /*+ USE_HASH(o,c) */  * FROM orders o  
JOIN customers c ON o.cust_id = c.id  
WHERE o.amount > 1000;  

强制哈希连接后，构建cust_id哈希表，单次扫描效率提升。

案例2：统计信息过期导致连接顺序错误

场景：

SELECT * FROM A 
JOIN B ON A.id = B.a_id  
JOIN C ON B.c_id = C.id;  

• 表A：1万行（实际增长至50万行）
• 表B：100万行
• 表C：5000行

问题：
统计信息未更新，优化器仍按A→B→C顺序连接，实际A表增大后应优先过滤。

解决方案：

-- 更新统计信息
stat 100 on a(id);  --更新列统计信息
-- 使用提示调整连接顺序
/*+ order(C,B,A)  USE_HASH(B,A) */  

---

四、优化注意事项与常见问题

1. 优化禁忌清单

• ❌ 盲目添加索引导致写性能下降
• ❌ 过度使用优化器提示（Hint）使计划僵化
• ❌ 忽视统计信息更新导致成本计算偏差
• ❌ 频繁全表扫描未分区的大表

2. 执行计划突变问题排查

检查项：

• 统计信息是否及时更新,特别注意关联条件列的统计信息
• 系统参数变化（dm.ini）
• 索引状态（是否失效或不可见）

诊断命令：

-- 查看当前计划
EXPLAIN FOR SELECT ...;  

--找到需要的sql缓存计划, 与看到的计划做对比，
select cache_item,sqlstr from v$cachepln where sqlstr like '%SQL特征片段%';
--通过trace事件,将计划以追加方式导出到指定目录下,?号传入cache_item值，语句中''是两个单引号（含义为使用单引号对单引号转义）
Alter session set events 'immediate trace name plndump level 上面查出来的cache_item值,dump_file ''/home/dmdba/sqlplan.log''';

--如果确定计划不正确，清理不正确的sql计划
sp_clear_plan_cache(cache_item值);

五、达梦特有功能助力优化

1. 执行计划hint提示以及计划绑定

• 防止计划退化：hint提示计划修正：

--打开hint注入开关
sf_set_system_para_value('ENABLE_INJECT_HINT',1,0,1);
--对指定sql语句使用hint注入
SF_INJECT_HINT('SELECT * FROM SM_USER_EX WHERE EXSTATUSCODE IN ','ENABLE_INDEX_JOIN(0)','TEST_INJECT_01','达梦数据库测试hint注入',TRUE,TRUE);
--查询所有hint注入配置
SELECT 'SF_INJECT_HINT('''||SQL_TEXT||''','''||HINT_TEXT||''','''||NAME||''','''||DESCRIPTION||''',TRUE,TRUE);' FROM SYSINJECTHINT;
--删除指定hint注入配置
SF_DEINJECT_HINT('TEST_INJECT_01');

hint注入使用到的相关参数请查考官方dba手册。

• 固定优质计划：

select  BINDED,sqlstr ,hash_value,* from v$cachepln where sqlstr like'%T_CODE%' ;--查找执行sql语句的hash值，BINDED字段用于表示执行计划的绑定情况，'N'：未绑定；'M'：内存中绑定；'P'：持久化绑定；'B'：内存中绑定与持久化绑定同时生效
--SP_SET_PLN_BINDED(HASH_VALUE ,'模式名', '执行计划缓存类型TYPE$',参数);--该方法的效果是叠加
SP_SET_PLN_BINDED(263595526, 'SYSDBA', 'SQL', 1);--M
SP_SET_PLN_BINDED(263595526, 'SYSDBA', 'SQL', 2);--P

表结构、索引变动会导致绑定计划失效，请注意。

2. 实时SQL监控

SELECT * FROM  V$SQL_STAT ;
SELECT * FROM  V$SQL_STAT_HISTORY ;

监控字段包括逻辑读、物理读、执行时间等。

3. sql日志分析

使用达梦官方推荐的sql日志分析工具，抓取高峰时期的sql进行优化，特别注意高频sql。 使用方法和介绍这里就不多赘述可以到达梦eco社区查找。

六、总结与最佳实践

1. 连接方式选择三步法：
   • 小表驱动选NL，大表无索引用Hash，有序数据用Merge。
2. 统计信息管理铁律：
   • 定期收集，重大变更后立即更新。
3. 执行计划分析：
   • 关注COST、ROWS、TIME三要素，对比实际资源消耗。
   • 关注CSCN2全表扫描、BLKUP2 回表操作 ，尽可能使用索引规避全表扫描和回表。
4. 渐进式优化：
   • 每次只改一个变量，通过disql 的set autotrace trace|traceonly功能验证效果。

通过理论结合实践，深入掌握达梦优化器行为模式，方能游刃有余应对复杂SQL性能挑战。