达梦数据库（DM8）SQL优化技巧分享

在生产环境中，当单表数据量突破千万，简单的 SQL 可能会引发严重的 CPU 报警或 I/O 阻塞。本文将结合达梦数据库的特性，分享一些通过 SQL 重构、复合索引优化及物化视图提升查询效率的技巧。

一、实战背景

模拟一张警务或政务系统的核心表 T_ORDER_LOG（订单日志表），表结构如下。

核心字段： ID (PK), ORG_ID (单位 ID), OP_TYPE (操作类型), CREATE_TIME (创建时间), AMOUNT (金额)。

CREATE TABLE "SYSDBA"."T_ORDER_LOG"
(
    "ID"          BIGINT IDENTITY(1, 1) NOT NULL, -- 自增主键
    "ORG_ID"      VARCHAR(50)           NOT NULL, -- 机构/单位ID，用于复合索引的前缀
    "USER_ID"     BIGINT,                         -- 操作员ID
    "OP_TYPE"     INT                   NOT NULL, -- 操作类型枚举 (1:查询, 2:新增, 3:修改, 4:删除)
    "AMOUNT"      DECIMAL(18, 2)        DEFAULT 0, -- 金额
    "CREATE_TIME" DATETIME              DEFAULT NOW(), -- 记录时间，默认为当前时间
    "REMARK"      VARCHAR(500),                   -- 备注信息
    NOT CLUSTER PRIMARY KEY("ID")
)

模拟一张机构信息表 T_ORG，表结构如下。

核心字段： ORG_ID (机构唯一编码), ORG_NAME (机构名称), ORG_TYPE (机构类型), LEADER (负责人), STATUS (状态)。

CREATE TABLE "SYSDBA"."T_ORG"
(
    "ORG_ID"   VARCHAR(50) NOT NULL, -- 机构唯一编码（关联键）
    "ORG_NAME" VARCHAR(100) NOT NULL, -- 机构名称
    "ORG_TYPE" INT,                  -- 机构类型（1:省级, 2:市级, 3:区县级）
    "LEADER"   VARCHAR(50),           -- 负责人
    "STATUS"   TINYINT DEFAULT 1,     -- 状态（1:正常, 0:停用）
    NOT CLUSTER PRIMARY KEY("ORG_ID") 
) STORAGE(ON "MAIN", CLUSTERBTR);

二、 SQL 语句的“避坑”与重写

1. 禁用索引列上的函数运算

痛点场景： 很多开发者习惯使用 DATEDIFF 查询近一小时的数据。在单表查询时或许尚可接受，但一旦涉及多表关联，性能会变差。在小规模或缓存充足的实验环境下，函数运算的开销可能不明显；但当面对高并发并发、海量冷数据或者执行计划发生倾斜时，保持索引列的纯净就显得更加重要。

错误示例： 在达梦中，如果你对索引列使用函数，CBO（优化器）通常会放弃索引走全表扫描（TABLE SCAN）。

SELECT A.ORG_NAME, B.AMOUNT 
FROM T_ORG A
JOIN T_ORDER_LOG B ON A.ORG_ID = B.ORG_ID
WHERE DATEDIFF(SS, B.CREATE_TIME, SYSDATE) < 3600;

实操优化： 将计算移至等号右侧，保持索引列的“干净”。

SELECT A.ORG_NAME, B.AMOUNT 
FROM T_ORG A
JOIN T_ORDER_LOG B ON A.ORG_ID = B.ORG_ID
WHERE B.CREATE_TIME >= DATEADD(HH, -1, SYSDATE);

2. 深度分页的“游标”式优化

当执行 LIMIT 1000000, 10 时，数据库通过索引定位到第 1,000,001 条记录之前，需要将前 1,000,000 条记录的整行数据从磁盘加载到内存。即使这些记录最终被丢弃，数据库仍然会对每一行进行“回表”操作，以获取非索引列的数据。这产生了大量的随机 I/O，是性能瓶颈的根本原因。使用子查询定位只查询ID列，它直接存在于二级索引或聚簇索引的叶子节点中。只需扫描索引树，无需回表获取整行数据。

错误示例：

SELECT * FROM T_ORDER_LOG ORDER BY ID LIMIT 1000000, 10;

实操优化（子查询定位法）：

SELECT t1.* FROM T_ORDER_LOG t1
INNER JOIN (
    SELECT ID FROM T_ORDER_LOG ORDER BY ID LIMIT 1000000, 10
) t2 ON t1.ID = t2.ID;

注：达梦支持 TOP 和 LIMIT 语法，在大数据量下，优先利用主键索引定位 ID 是最快路径。

三、复合索引的极致设计

在达梦中，复合索引不仅要考虑 WHERE 条件，还要考虑 ORDER BY。创建索引的时候按照以下顺序来对索引列排序。

1. 索引顺序原则：等值 > 范围 > 排序

若业务需求是：查询某个单位（ORG_ID）下，某种类型（OP_TYPE）的操作，并按时间倒序排。

创建索引方案：

-- 务必注意顺序：ORG_ID 和 OP_TYPE 是等值匹配，放在前面；CREATE_TIME 用于排序，放在最后。
CREATE INDEX IDX_ORG_TYPE_TIME ON T_ORDER_LOG(ORG_ID, OP_TYPE, CREATE_TIME DESC);

注：使用 like '%1%' 通常无法命中常规索引，如果数据量较大，尽量时候后匹配的方式查询，如 like '1%' 。

2. 巧用“索引覆盖”

如果你的查询只涉及索引中的几个字段，可以直接从索引页返回数据（INDEX SCAN），而不需要回表（FETCH BY ROWID），这能减少 50% 以上的 I/O。

错误示例：

SELECT *
FROM T_ORDER_LOG 
WHERE ORG_ID = '110101' 
GROUP BY ORG_ID;

实操优化：

-- 这种查询直接命中索引，无需访问数据表原块
SELECT ORG_ID, COUNT(*) 
FROM T_ORDER_LOG 
WHERE ORG_ID = '110101' 
GROUP BY ORG_ID;

四、视图与物化视图（Materialized View）

普通视图在达梦中仅是“逻辑封装”，并不加速查询。而物化视图不仅存储了 SQL 语句，还物理地存储了 SQL 执行后的结果集，所以对于生产环境的大规模聚合运算，物化视图是利器。由于查询物化视图只需要筛选物化视图存储的结果集，而不需要对原表进行全表扫描，所以通常使用物化视图进行高频、大跨度、复杂聚合的统计查询。

1. 场景：每日金额汇总查询

实操代码：

-- 创建一个手动刷新的物化视图，存储预计算结果
CREATE MATERIALIZED VIEW MV_DAILY_AMOUNT
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT ORG_ID, TRUNC(CREATE_TIME) as OP_DATE, SUM(AMOUNT) as TOTAL
FROM T_ORDER_LOG
GROUP BY ORG_ID, TRUNC(CREATE_TIME);

-- 以后查询直接查视图，无需扫描拥有大量数据的原表
SELECT * FROM MV_DAILY_AMOUNT WHERE ORG_ID = '320100';

五、更新统计信息

很多开发者发现索引建了但不生效，在达梦中，这通常是因为统计信息陈旧，导致优化器选择了错误的执行路径。在数据发生了大量增删改后，统计信息来不及自动更新，就会出现这种问题。以下两行sql可以更新表和索引的统计信息，从而让优化器能够正确选择执行路径。

实操命令：

-- 更新表统计信息
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA', 'T_ORDER_LOG', NULL, 100);

-- 更新索引统计信息
DBMS_STATS.GATHER_INDEX_STATS('SYSDBA', 'IDX_ORG_TYPE_TIME');

总结

达梦数据库在海量数据下的调优逻辑是：先用复合索引减少扫描行数，再用索引覆盖减少回表次数，最后用物化视图解决聚合瓶颈。