达梦数据库性能优化实战指南

达梦数据库性能优化实战指南

一、核心优化框架

优化优先级从高到低，优先解决“投入少、见效快”的问题，避免盲目调优：

  1. SQL 优化：最易落地，通常能解决80%的性能问题（如慢SQL、全表扫描）；

  2. 统计信息优化：CBO（基于代价的优化器）生成最优执行计划的核心，统计信息过期会导致执行计划跑偏；

  3. 索引优化：合理建索引、避免失效，减少查询开销；

  4. 参数优化：适配硬件（CPU、内存）和业务场景（OLTP/OLAP），微调即可见效；

  5. 架构优化：底层支撑，针对高并发、大数据量场景（如分区表、读写分离）。

二、第一步：快速定位性能问题

核心目标：精准找到“拖慢系统”的慢SQL、无效索引或参数瓶颈，不做无用功。

2.1 3种方式定位慢SQL（直接复制可用）

方式1：查询正在执行的慢SQL（实时排查）

-- 执行时间>1秒的活跃SQL（可调整阈值，单位：毫秒）
SELECT 
  SESS_ID 会话ID,
  USER_NAME 执行用户,
  SF_GET_SESSION_SQL(SESS_ID) AS SQL_TEXT 完整SQL,
  EXEC_TIME 执行时间(ms),
  STATE 会话状态
FROM V$SESSIONS 
WHERE STATE = 'ACTIVE' AND EXEC_TIME > 1000;

方式2：查询历史长执行SQL（事后排查）

-- 执行时间>5秒的历史SQL，按执行时间降序排列
SELECT 
  SQL_TEXT 完整SQL,
  EXEC_TIME 执行时间(ms),
  START_TIME 执行开始时间,
  USER_NAME 执行用户,
  CLIENT_IP 客户端IP
FROM V$LONG_EXEC_SQLS 
WHERE EXEC_TIME > 5000
ORDER BY EXEC_TIME DESC;

方式3：开启SQL日志（持久化记录，适合长期监控）

  1. 修改 sqllog.ini 配置（找到数据库安装目录下的 log 文件夹）：

    1. MIN_EXEC_TIME = 2000 （仅记录执行时间>2秒的SQL，可调整）；

    2. LOG_TYPE = 1 （记录完整SQL文本）；

    3. FILE_NAME = dmsql_实例名 （日志文件名，自动按日期拆分）。

  2. 日志路径：../log/dmsql_实例名_日期_时间.log，用记事本或DMLOG工具打开即可分析。

2.2 执行计划分析（核心：找瓶颈操作符）

拿到慢SQL后，第一步看执行计划，判断是“全表扫描”“无效索引”还是“排序开销大”。

实操代码（直接复制）

-- 1. 预估执行计划（快速查看，无实际执行）
EXPLAIN SELECT * FROM T_ORDER WHERE ORDER_DATE > '2026-01-01';

-- 2. 真实执行计划（含实际耗时、行数，精准分析）
SET AUTOTRACE TRACEONLY; -- 只显示执行计划，不返回结果集
SELECT * FROM T_ORDER WHERE ORDER_DATE > '2026-01-01';

关键操作符速查

操作符 含义 常见问题 快速优化建议
CSCN 全表扫描 查询无索引，或索引失效，扫描全表耗时久 1. 给过滤列建索引；2. 若返回数据>20%，索引无效，改SQL过滤条件
SSEK/CSEK 索引扫描（顺序/并发） 正常，但可能存在“回表次数过多”（索引未覆盖查询列） 创建覆盖索引（包含查询所需所有列），避免回表
HASH JOIN 哈希连接（多表关联常用） 内存不足时，哈希表溢出到磁盘，速度骤降 调大 HJ_BUF_SIZE 参数，或拆分大表关联
NEST LOOP 嵌套循环连接 大表驱动小表时，效率极低 换 HASH JOIN，或调整表关联顺序（小表驱动大表）
SORT 排序操作 不必要的 ORDER BY/GROUP BY，消耗CPU和内存 删除无用排序，或给排序列建索引（避免手动排序）

三、高频优化手段

每个优化点都配套「业务案例+问题SQL+优化方案+优化效果」，方便直接参考落地。

3.1 SQL 优化（最易见效，重点）

核心：避免全表扫描、无用排序、低效关联，改写SQL逻辑，不改动底层架构。

案例1：WHERE 优先过滤，替代 HAVING 过滤

业务场景

某电商订单表 T_ORDER（100万行数据），查询2026年1月的订单平均金额，按订单类型分组，仅统计“实物订单”。

问题SQL（执行时间：8.6秒，执行计划含 CSCN 全表扫描）

-- 优化前：先分组，再过滤，全表扫描所有数据
SELECT 
  ORDER_TYPE 订单类型,
  AVG(ORDER_AMOUNT) 平均金额
FROM T_ORDER
GROUP BY ORDER_TYPE
HAVING ORDER_DATE > '2026-01-01' AND ORDER_TYPE = '实物订单';

优化方案

先通过 WHERE 过滤时间和订单类型，减少分组的数据量，避免全表扫描。

优化后SQL（执行时间：0.3秒，执行计划含 SSEK 索引扫描）

SELECT 
  ORDER_TYPE 订单类型,
  AVG(ORDER_AMOUNT) 平均金额
FROM T_ORDER
WHERE ORDER_DATE > '2026-01-01' AND ORDER_TYPE = '实物订单'
GROUP BY ORDER_TYPE;

优化关键点

HAVING 是“分组后过滤”，会扫描所有数据再分组；WHERE 是“分组前过滤”，提前筛选数据，减少分组开销。

案例2：EXISTS 替代 IN/DISTINCT，优化关联去重

业务场景

用户表 T_USER（50万行）、订单表 T_ORDER（100万行），查询“有订单记录的用户ID”，需去重。

问题SQL（执行时间：6.2秒，执行计划含 SORT 排序去重）

-- 优化前：DISTINCT 去重，多表关联后排序，开销大
SELECT DISTINCT U.USER_ID
FROM T_USER U, T_ORDER O
WHERE U.USER_ID = O.USER_ID;

优化方案

用 EXISTS 替代 IN/DISTINCT，无需排序去重，仅判断“存在关联记录”即可，效率更高。

优化后SQL（执行时间：0.5秒，无排序操作）

SELECT U.USER_ID
FROM T_USER U
WHERE EXISTS (
  SELECT 1 FROM T_ORDER O 
  WHERE O.USER_ID = U.USER_ID
);

优化关键点

IN 会先查询子查询结果，再匹配主表，若子查询结果量大，效率极低；EXISTS 是“半连接”，只要子查询有匹配记录，就返回主表数据，无需全量匹配。

案例3：UNION ALL 替代 UNION，避免无用排序

业务场景

查询“2026年1月订单”和“2026年2月订单”的用户ID，无重复数据（业务保证）。

问题SQL（执行时间：4.8秒，执行计划含 SORT 排序去重）

-- 优化前：UNION 自动去重，会对结果集排序，耗时久
SELECT USER_ID FROM T_ORDER WHERE ORDER_DATE BETWEEN '2026-01-01' AND '2026-01-31'
UNION
SELECT USER_ID FROM T_ORDER WHERE ORDER_DATE BETWEEN '2026-02-01' AND '2026-02-28';

优化方案

业务已保证无重复数据，用 UNION ALL 替代 UNION，取消排序去重操作。

优化后SQL（执行时间：0.2秒，无排序操作）

SELECT USER_ID FROM T_ORDER WHERE ORDER_DATE BETWEEN '2026-01-01' AND '2026-01-31'
UNION ALL
SELECT USER_ID FROM T_ORDER WHERE ORDER_DATE BETWEEN '2026-02-01' AND '2026-02-28';

优化关键点

UNION 的核心是“去重+合并”，会触发排序操作；UNION ALL 仅“合并”，不排序，适合无重复数据的场景，性能提升80%以上。

案例4：避免函数操作，防止索引失效

业务场景

T_ORDER 表已给 ORDER_DATE 列建索引，查询“2026年1月的订单”，用函数截取日期。

问题SQL（执行时间：7.3秒，执行计划含 CSCN 全表扫描，索引失效）

-- 优化前：条件列用函数，索引失效，全表扫描
SELECT * FROM T_ORDER
WHERE SUBSTR(ORDER_DATE, 1, 7) = '2026-01';

优化方案

改写 SQL，避免在条件列上使用函数，让索引生效。

优化后SQL（执行时间：0.1秒，执行计划含 SSEK 索引扫描）

-- 优化后：条件列无函数，索引生效
SELECT * FROM T_ORDER
WHERE ORDER_DATE BETWEEN '2026-01-01' AND '2026-01-31 23:59:59';

优化关键点

达梦数据库中，条件列使用函数（如 SUBSTR、TO_DATE）会导致索引失效，优先改写条件，让索引正常触发。

3.2 索引优化（核心：建对、用对，不滥用）

索引是“查询加速器”，但建多了会影响 DML（插入/更新/删除）性能，核心是“精准建索引”。

核心建索引原则（必记）

  • ✅ 高频查询、排序、分组的列优先建（如 WHERE 过滤列、ORDER BY 列）；

  • ✅ 组合索引遵循「最左匹配原则」（如建索引 (A,B,C)，仅查 B+C 则索引失效）；

  • ✅ 唯一索引优先于普通索引（区分度高，查询效率更高）；

  • ❌ 避免过多索引（一张表索引不超过5个，DML操作会频繁维护索引，拖慢性能）；

  • ❌ 避免在函数/计算列上建索引（易失效）；

  • ❌ 避免给数据区分度低的列建索引（如性别、状态列，返回数据>20%，索引无效）。

案例：覆盖索引优化，避免回表开销

业务场景

T_ORDER 表（100万行），常用查询：根据 ORDER_DATE 过滤，查询 USER_ID、ORDER_AMOUNT、ORDER_STATUS 三个字段，已给 ORDER_DATE 建普通索引。

问题SQL（执行时间：1.8秒，执行计划含 SSEK+回表操作）

SELECT USER_ID, ORDER_AMOUNT, ORDER_STATUS
FROM T_ORDER
WHERE ORDER_DATE > '2026-01-01';

问题分析

普通索引仅包含 ORDER_DATE 列，查询时先通过索引找到行地址，再回表查询 USER_ID、ORDER_AMOUNT 等字段，回表次数多，耗时久。

优化方案

创建覆盖索引，将查询所需的所有列（过滤列+查询列）都包含在索引中，避免回表。

-- 创建覆盖索引（ORDER_DATE 为过滤列，其他为查询列）
CREATE INDEX IDX_ORDER_DATE_COVER ON T_ORDER (ORDER_DATE, USER_ID, ORDER_AMOUNT, ORDER_STATUS);

优化效果

执行时间从1.8秒降至0.15秒，执行计划无回表操作，直接通过索引获取所有所需数据。

索引失效快速排查清单（直接对照）

  1. 条件列使用函数/计算（如 SUBSTR(ORDER_DATE,1,7) = '2026-01'）；

  2. 组合索引未使用首列（如索引 (A,B)，查询 WHERE B=10）；

  3. 查询返回数据量>20%（索引失效，自动走全表扫描）；

  4. 使用 !=、NOT IN、IS NULL/IS NOT NULL（易导致索引失效，优先用其他方式替代）；

  5. 统计信息过期（索引被CBO忽略，需重新收集统计信息）。

3.3 统计信息优化

统计信息是CBO生成最优执行计划的基础，若统计信息过期（如数据大量插入/删除后未更新），CBO会选择错误的执行计划（如明明有索引，却走全表扫描）。

案例：统计信息过期导致的慢SQL优化

业务场景

T_ORDER 表新增50万行数据（总数据量150万），未更新统计信息，执行之前优化过的SQL，执行时间从0.3秒骤升至9.2秒。

问题排查

查看执行计划，发现原本的 SSEK 索引扫描，变成了 CSCN 全表扫描，原因是统计信息未更新，CBO认为“全表扫描比索引扫描更快”。

优化方案（重新收集统计信息）

-- 1. 手动收集单表+索引统计信息（业务低峰期执行，避免影响业务）
DBMS_STATS.GATHER_TABLE_STATS(
  OWNNAME => 'SYSDBA', -- 用户名
  TABNAME => 'T_ORDER', -- 表名
  ESTIMATE_PERCENT => 100, -- 100%采样，精准度最高
  CASCADE => TRUE -- 同时收集索引统计信息
);

-- 2. 配置自动收集（推荐，避免手动遗漏）
-- 开启表数据量监控
SP_SET_PARA_VALUE(1, 'AUTO_STAT_OBJ', 2);
-- 设置触发阈值：数据变化超15%，自动更新统计信息
DBMS_STATS.SET_TABLE_PREFS('SYSDBA', 'T_ORDER', 'STALE_PERCENT', 15);

优化效果

执行时间恢复至0.3秒，执行计划重新触发 SSEK 索引扫描，CBO选择正确的执行计划。

常用统计信息操作（直接复制）

-- 1. 查看表统计信息（判断是否过期）
DBMS_STATS.TABLE_STATS_SHOW('SYSDBA', 'T_ORDER');

-- 2. 查看索引统计信息
DBMS_STATS.INDEX_STATS_SHOW('SYSDBA', 'IDX_ORDER_DATE_COVER');

-- 3. 收集整个用户下的所有表统计信息
DBMS_STATS.GATHER_SCHEMA_STATS('SYSDBA', 100, TRUE);

-- 4. 删除过期统计信息（谨慎使用）
DBMS_STATS.DELETE_TABLE_STATS('SYSDBA', 'T_ORDER');

3.4 关键参数优化（适配硬件，微调见效）

参数优化无需盲目调大，核心是“适配硬件（CPU、内存）和业务场景（OLTP/OLAP）”，以下是最常用的关键参数，配套案例说明。

核心参数速查表（含推荐值）

参数名 核心作用 OLTP（高并发交易）推荐值 OLAP（海量数据分析）推荐值 修改方式
BUFFER 数据缓冲区，缓存数据块，减少磁盘IO 物理内存的 2/3（数据量>内存时） 物理内存的 3/4（分析场景需更多缓存） 动态修改（立即生效）
WORKER_THREADS 工作线程数，处理用户请求 CPU 核数或 2 倍（高并发场景） CPU 核数（分析场景无需过多线程） 动态修改（立即生效）
HJ_BUF_SIZE HASH 连接缓存，影响多表关联效率 512M ~ 1G 1G ~ 4G（关联场景多，需更大缓存） 会话级/动态修改
ENABLE_MONITOR 性能监控开关，影响监控数据采集 优化时设 3，运行时设 2 优化时设 3，运行时设 2 动态修改（立即生效）
RECYCLE 临时表、排序缓冲区，减少磁盘排序 2G ~ 4G（高并发排序场景） 4G ~ 8G（分析场景排序多） 静态修改（需重启数据库）

案例：调整 WORKER_THREADS 解决高并发卡顿

业务场景

某电商系统，数据库服务器 CPU 为 16 核，高峰时段并发请求达 500+，系统卡顿，查询响应时间超 3 秒，排查发现 WORKER_THREADS 默认为 8。

问题分析

WORKER_THREADS 为 8，无法处理 500+ 并发请求，导致请求排队，响应变慢。

优化方案（动态修改，立即生效）

-- 修改 WORKER_THREADS 为 32（16核CPU的2倍，适配高并发）
SP_SET_PARA_VALUE(1, 'WORKER_THREADS', 32);

-- 查看修改后的值
SELECT SF_GET_PARA_VALUE(1, 'WORKER_THREADS');

优化效果

高峰时段响应时间从 3+ 秒降至 0.5 秒内，并发请求无排队，系统流畅。

参数修改注意事项

  • 动态参数（如 WORKER_THREADS、BUFFER）：修改后立即生效，无需重启数据库；

  • 静态参数（如 RECYCLE）：修改后需重启数据库生效，建议在业务低峰期操作；

  • 会话级参数（如 HJ_BUF_SIZE）：仅对当前会话生效，适合临时调试，重启后失效。

3.5 架构优化（按需选择，底层支撑）

架构优化适合“高并发、大数据量”场景，无需盲目搭建，根据业务需求选择即可。

架构类型 核心优势 适用场景 实战案例
读写分离（DMRWC） 读请求自动分流到备库，主库专注写操作，提升并发能力 读多写少（如OA系统、报表查询、电商商品查询） 某OA系统，读请求占比85%，搭建读写分离后，主库CPU使用率从80%降至30%，查询响应时间缩短60%
数据守护（DMDataWatch） 实时数据同步，故障秒级切换，保障高可用，支持灾备 核心业务（如金融交易、电商订单），不允许停机 某银行核心系统，搭建双机守护，主库故障时，1秒切换至备库，无数据丢失，业务无感知
分区表 按时间/范围拆分大表，减少查询扫描范围，支持分区级操作（删除/ truncate） 单表数据量>1000万（如订单表、日志表） 某电商订单表（5000万行），按月份分区，查询2026年1月订单，扫描范围从5000万行降至400万行，执行时间从12秒降至0.8秒
列存储表（HUGE） 按列存储，压缩率高（可达10:1），适合海量数据分析，查询效率提升明显 OLAP场景（如数据仓库、报表分析、用户行为分析） 某数据仓库，用户行为表（1亿行），改为列存储后，存储占用从500G降至50G，分析查询时间从30秒降至3秒

四、应急优化流程（5步搞定，快速止血）

适用于“系统卡顿、慢SQL爆发”的紧急场景，按步骤操作，快速恢复系统性能。

  1. 定位慢SQL（1分钟）：用 V$LONG_EXEC_SQLS 或 SQL 日志，筛选出执行时间>5秒的SQL，按执行时间降序排列，优先处理Top10；

  2. 分析执行计划（2分钟）：对慢SQL执行 EXPLAIN，查看是否有 CSCN（全表扫描）、SORT（无用排序）、HASH JOIN 溢出，定位瓶颈；

  3. 紧急优化（5分钟）：

    1. 若全表扫描：临时建索引（优先覆盖索引）；

    2. 若索引失效：改写SQL，避免函数操作，或重新收集统计信息；

    3. 若排序/关联开销大：改写SQL（如 UNION ALL 替代 UNION、EXISTS 替代 IN）；

    4. 若内存不足：临时调大 HJ_BUF_SIZE、BUFFER 参数。

  4. 验证效果（2分钟）：执行优化后的SQL，查看执行时间是否下降，执行计划是否优化；

  5. 后续优化（非紧急）：清理无效索引、优化表结构（如分区表）、配置自动统计信息，避免问题复发。

五、日常运维 Checklist（避免问题复发）

5.1 DBA 日常运维（每日/每周/每月）

  • 每日：查询 V$LONG_EXEC_SQLS，清理执行超5秒的慢SQL，记录优化情况；

  • 每周：收集核心表（订单表、用户表）的统计信息，检查无效索引（无使用记录的索引），清理冗余索引；

  • 每月：调整参数适配业务变化（如并发提升、数据量增长），检查分区表数据分布，清理过期分区数据。

5.2 开发人员规范（从源头避免性能问题）

  1. SQL 编写规范：

    1. 避免 SELECT *，明确指定所需列（减少数据传输，便于建覆盖索引）；

    2. 优先用 EXISTS 替代 IN/DISTINCT，UNION ALL 替代 UNION；

    3. WHERE 过滤优先于 HAVING，减少分组数据量；

    4. 避免在条件列上使用函数，避免 !=、NOT IN 等易导致索引失效的语法；

    5. 批量操作（如批量插入/更新）使用 FORALL 或 BATCH 方式，减少事务提交次数。

  2. 索引设计规范：

    1. 高频查询列、过滤列优先建索引，组合索引遵循最左匹配；

    2. 新增索引前，先查看该列的查询频率，避免盲目建索引；

    3. 定期检查索引使用情况，清理无使用记录的无效索引。

  3. 表结构设计规范：

    1. 单表数据量超1000万，考虑分区表（按时间/业务维度分区）；

    2. 复杂查询（多表关联、统计分析）使用临时表存储中间结果，减少重复计算；

    3. OLTP 场景用行存储表，OLAP 场景用列存储表，适配业务需求。

六、总结

达梦数据库性能优化，核心是“先定位、后优化、再落地”：

  1. 优先解决慢SQL，通过执行计划找瓶颈，SQL改写和索引优化是最易见效的手段；

  2. 统计信息是基础，一定要定期收集，避免CBO选择错误的执行计划；

  3. 参数优化按需微调，适配硬件和业务，不盲目调大；

  4. 架构优化适合大数据量、高并发场景，按需选择，不过度设计；

  5. 日常运维是关键，定期排查、规范开发，才能从源头避免性能问题。

posted on 2025-11-09 20:27  二月无雨  阅读(228)  评论(0)    收藏  举报

博客园  ©  2004-2026
浙公网安备 33010602011771号 浙ICP备2021040463号-3