shield_lock
JinDaGe
← Back to skills
extension
Category: Development & EngineeringNo API key required

达梦数据库sql速度优化

优化达梦数据库（DM Database）SQL 操作性能。当用户需要以下场景时触发： (1) 分析或优化慢 SQL、(2) 编写高效的 INSERT/SELECT/UPDATE/DELETE 语句、 (3) 使用 HINT 干预执行计划、(4) 分析执行计划（EXPLAIN）、 (5) 批量数据导入优化、(6) 索引设计与调优、(7) 达梦数据库参数调优。 适用于 DM7/DM8 版本。

personAuthor: FZKKKKhubModelScope
downloadDownload package

达梦数据库 SQL 优化技能

快速诊断清单

遇到 SQL 性能问题时，按顺序检查以下 5 个问题：

| # | 检查项 | 关键判断 | |---|--------|----------| | 1 | 是否走了全表扫描？ | EXPLAIN 中出现 CSCN 且表数据量大 → 需要加索引或改写 SQL | | 2 | 索引是否生效？ | WHERE 条件中的列是否有索引？是否对列做了函数运算导致索引失效？ | | 3 | 是否频繁硬解析？ | 是否使用了绑定变量？USE_PLN_POOL 是否开启？ | | 4 | 事务是否过大？ | 批量操作是否每行都 COMMIT？是否锁持有时间过长？ | | 5 | 统计信息是否过期？ | 是否定期收集统计信息？优化器是否拿到了准确的数据分布？ |

核心优化工作流

第一步：获取执行计划

-- 方式一：EXPLAIN 直接查看
EXPLAIN SELECT * FROM orders WHERE customer_id = 1001;

-- 方式二：带实际执行统计（更准确）
EXPLAIN SELECT /*+ MONITOR */ * FROM orders WHERE customer_id = 1001;

第二步：识别瓶颈节点

阅读执行计划时关注：

  • CSCN（全表扫描）：大表出现 → 优先解决
  • SSEK/SSCN（索引扫描）：确认是否命中预期索引
  • NLOOP/HEAP SORT：排序操作是否在内存中完成
  • HASH JOIN：小表是否作为驱动表
  • 代价（Cost）：对比不同方案的代价差异

第三步：应用优化策略

根据瓶颈类型选择对应的优化模式（见下方"常见 SQL 优化模式"）。

第四步：验证效果

-- 对比优化前后的执行计划
EXPLAIN <原始SQL>;
EXPLAIN <优化后SQL>;

-- 查看实际执行时间
SET TIMING ON;
SELECT * FROM orders WHERE customer_id = 1001;

第五步：收集统计信息（如需要）

-- 收集单表统计信息
DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

-- 收集全库统计信息（低峰期执行）
DBMS_STATS.GATHER_DATABASE_STATS();

常见 SQL 优化模式

模式 1：SELECT 查询优化

原则：避免全表扫描，合理使用索引，减少返回数据量。

-- ❌ 避免：SELECT * 和无索引条件
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- ✅ 优化：指定列 + 避免列上函数运算
SELECT order_id, customer_id, amount, order_date
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

子查询优化：用 EXISTS 替代 IN（大数据量时）

-- ❌ 慢：IN 子查询
SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);

-- ✅ 快：EXISTS
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

JOIN 优化：小表驱动大表

-- 确保小表在 JOIN 的左侧（DM 优化器通常自动处理，但可用 HINT 强制）
SELECT /*+ USE_HASH(o c) */ o.order_id, c.name
FROM small_table c JOIN large_table o ON c.id = o.customer_id;

详见 dm-execution-plan.md 和 dm-hint-reference.md

模式 2：INSERT 批量插入优化

核心原则：减少 COMMIT 次数，使用批量插入，必要时使用直接路径插入。

-- ❌ 避免：逐行插入逐行提交
INSERT INTO t VALUES (1, 'a'); COMMIT;
INSERT INTO t VALUES (2, 'b'); COMMIT;

-- ✅ 优化：批量插入 + 批量提交
INSERT INTO t VALUES (1, 'a'), (2, 'b'), (3, 'c'), ...;  -- 每批 500-1000 行
COMMIT;

直接路径插入（绕过缓冲区，适合大批量）：

INSERT /*+ DIRECT_INSERT */ INTO target_table SELECT * FROM source_table;

超大批量：使用 dmldr 工具

dmldr USERID=SYSDBA/SYSDBA/SYSDBA CONTROL=loader.ctl DIRECT=TRUE BUFFER=102400

详见 dm-batch-operations.md

模式 3：UPDATE/DELETE 优化

核心原则：分批提交，避免长事务和大锁。

-- ❌ 避免：一次性更新百万行（锁表 + 巨大 UNDO）
UPDATE orders SET status = 'archived' WHERE order_date < '2020-01-01';

-- ✅ 优化：分批更新
BEGIN
  FOR i IN 1..1000 LOOP
    UPDATE orders SET status = 'archived'
    WHERE order_date < '2020-01-01' AND status != 'archived'
    AND ROWNUM <= 5000;
    COMMIT;
    EXIT WHEN SQL%ROWCOUNT = 0;
  END LOOP;
END;
/

模式 4：排序与分页优化

-- ❌ 避免：大偏移量分页
SELECT * FROM orders ORDER BY order_id LIMIT 1000000, 20;

-- ✅ 优化：基于游标的分页（Keyset Pagination）
SELECT * FROM orders
WHERE order_id > :last_seen_id
ORDER BY order_id
LIMIT 20;

模式 5：绑定变量与计划缓存

-- ❌ 避免：硬编码值导致硬解析
SELECT * FROM orders WHERE customer_id = 1001;
SELECT * FROM orders WHERE customer_id = 1002;  -- 新的硬解析

-- ✅ 优化：使用绑定变量
PREPARE stmt FROM 'SELECT * FROM orders WHERE customer_id = ?';
EXECUTE stmt USING 1001;
EXECUTE stmt USING 1002;  -- 复用执行计划

确保计划缓存已开启：

-- 检查参数
SELECT * FROM V$PARAMETER WHERE NAME = 'USE_PLN_POOL';
-- 设置为 1 或 2 开启
SP_SET_PARA_VALUE(1, 'USE_PLN_POOL', 2);

模式 6：HINT 干预执行计划

当优化器选择了次优计划时，用 HINT 强制指定：

-- 强制使用索引
SELECT /*+ INDEX(orders idx_customer_id) */ * FROM orders WHERE customer_id = 1001;

-- 指定连接方式
SELECT /*+ USE_HASH(a b) */ * FROM table_a a JOIN table_b b ON a.id = b.a_id;

-- 指定连接顺序
SELECT /*+ ORDERED */ * FROM small_table a JOIN large_table b ON a.id = b.a_id;

-- 并行执行
SELECT /*+ PARALLEL(4) */ COUNT(*) FROM large_table;

详见 dm-hint-reference.md

执行计划关键节点速查

| 节点缩写 | 含义 | 优化关注点 | |----------|------|-----------| | CSCN | 全表扫描（Cluster Scan） | 大表出现 → 检查索引 | | SSCN | 索引全扫描 | 检查是否可以改为索引查找 | | SSEK | 索引查找（Index Seek） | 理想的索引访问方式 | | BLKUP | 回表查询 | SSEK 后的回表，确认是否必要 | | NSET | 结果集 | 最终输出 | | PRJT | 投影 | 列裁剪 | | SLCT | 选择（过滤） | WHERE 条件过滤 | | HAGR | 哈希聚合 | GROUP BY 操作 | | SORT | 排序 | ORDER BY / DISTINCT | | HJOIN | 哈希连接 | 大表连接 | | NLOOP | 嵌套循环 | 小数据集连接 | | MERGE JOIN | 归并连接 | 有序数据连接 |

反模式清单

| 反模式 | 症状 | 修正方案 | |--------|------|----------| | SELECT * | 返回不需要的列，浪费 IO 和网络 | 明确列出需要的列 | | WHERE 上函数运算 | 索引失效，全表扫描 | 改写为范围条件或创建函数索引 | | 逐行 COMMIT | 频繁日志刷盘，性能骤降 | 每 500-1000 行提交一次 | | 大事务不提交 | 锁持有时间长，UNDO 膨胀 | 分批处理，定期提交 | | 忽略统计信息 | 优化器选择次优计划 | 定期 DBMS_STATS.GATHER_TABLE_STATS | | 滥用 OR 条件 | 可能导致全表扫描 | 改写为 UNION ALL | | 子查询中 SELECT * | 子查询返回多余列 | 子查询只 SELECT 关联列 | | LIKE '%keyword' | 前缀通配符导致索引失效 | 使用全文索引或改写查询逻辑 |

关键参数速查

| 参数 | 说明 | 建议值 | |------|------|--------| | BUFFER | 数据缓冲区大小 | 物理内存的 60%-70% | | MAX_BUFFER | 最大缓冲区 | 与 BUFFER 相同或略大 | | SORT_BUF_SIZE | 排序缓冲区 | 10-50MB（根据排序量调整） | | HJ_BUF_GLOBAL_SIZE | 哈希连接全局缓冲 | 物理内存的 10%-20% | | USE_PLN_POOL | 计划缓存 | 1 或 2（开启） | | MAX_SESSIONS | 最大并发会话数 | 根据业务并发量设置 | | UNDO_RETENTION | UNDO 保留时间 | 根据长事务需要设置 |

详见 dm-parameter-tuning.md

参考文件索引

| 文件 | 内容 | |------|------| | dm-hint-reference.md | HINT 完整语法：INDEX/NO_INDEX、USE_HASH/USE_NL/USE_MERGE、ORDERED、PARALLEL 等 | | dm-index-guide.md | 索引类型选择、组合索引最左前缀、位图索引、函数索引、索引重建 | | dm-batch-operations.md | dmldr 工具、批量 INSERT 模板、NOLOGGING、DIRECT INSERT | | dm-execution-plan.md | 执行计划节点详解、代价解读、常见执行计划问题诊断 | | dm-parameter-tuning.md | 关键参数说明、内存配置、并发参数、统计信息维护 |