达梦数据库SQL优化案例学习
一、基础表结构
-- 订单日志表（模拟警务/政务系统核心表）
CREATE TABLE "T_ORDER_LOG" (
"ID" BIGINT IDENTITY(1, 1) NOT NULL,
"ORG_ID" VARCHAR(50) NOT NULL,
"USER_ID" BIGINT,
"OP_TYPE" INT NOT NULL, -- 1:查询 2:新增 3:修改 4:删除
"AMOUNT" DECIMAL(18, 2) DEFAULT 0,
"CREATE_TIME" DATETIME DEFAULT NOW(),
"REMARK" VARCHAR(500),
"STATUS" TINYINT DEFAULT 1,
NOT CLUSTER PRIMARY KEY("ID")
);
-- 机构信息表
CREATE TABLE "T_ORG" (
"ORG_ID" VARCHAR(50) NOT NULL,
"ORG_NAME" VARCHAR(100) NOT NULL,
"ORG_TYPE" INT,
"LEADER" VARCHAR(50),
"STATUS" TINYINT DEFAULT 1,
NOT CLUSTER PRIMARY KEY("ORG_ID")
);
-- 订单明细表
CREATE TABLE "T_ORDER_DETAIL" (
"DETAIL_ID" BIGINT IDENTITY(1, 1) NOT NULL,
"ORDER_ID" BIGINT NOT NULL,
"PRODUCT_NAME" VARCHAR(200),
"QUANTITY" INT DEFAULT 1,
"UNIT_PRICE" DECIMAL(18, 2),
"DISCOUNT" DECIMAL(5, 2) DEFAULT 0,
NOT CLUSTER PRIMARY KEY("DETAIL_ID")
);
-- 操作日志归档表
CREATE TABLE "T_ORDER_LOG_ARCHIVE" (
"ID" BIGINT NOT NULL,
"ORG_ID" VARCHAR(50) NOT NULL,
"USER_ID" BIGINT,
"OP_TYPE" INT NOT NULL,
"AMOUNT" DECIMAL(18, 2) DEFAULT 0,
"CREATE_TIME" DATETIME,
"ARCHIVE_TIME" DATETIME DEFAULT NOW(),
NOT CLUSTER PRIMARY KEY("ID")
);
二、测试数据插入
-- 插入机构数据（100条）
INSERT INTO T_ORG(ORG_ID, ORG_NAME, ORG_TYPE, LEADER, STATUS)
SELECT
'ORG' || LPAD(LEVEL, 4, '0'),
'机构名称' || LEVEL,
MOD(LEVEL, 3) + 1,
'负责人' || LEVEL,
CASE WHEN MOD(LEVEL, 10) = 0 THEN 0 ELSE 1 END
FROM DUAL CONNECT BY LEVEL <= 100;
-- 插入订单日志数据（约50万条）
INSERT INTO T_ORDER_LOG(ORG_ID, USER_ID, OP_TYPE, AMOUNT, CREATE_TIME, REMARK, STATUS)
SELECT
'ORG' || LPAD(MOD(LEVEL, 100) + 1, 4, '0'),
MOD(LEVEL, 1000) + 1,
MOD(LEVEL, 4) + 1,
ROUND(DBMS_RANDOM.VALUE(1, 10000), 2),
SYSDATE - DBMS_RANDOM.VALUE(1, 365),
'备注信息' || LEVEL,
CASE WHEN MOD(LEVEL, 20) = 0 THEN 0 ELSE 1 END
FROM DUAL CONNECT BY LEVEL <= 500000;
-- 插入订单明细数据（约200万条）
INSERT INTO T_ORDER_DETAIL(ORDER_ID, PRODUCT_NAME, QUANTITY, UNIT_PRICE, DISCOUNT)
SELECT
MOD(LEVEL, 500000) + 1,
'产品' || MOD(LEVEL, 100),
MOD(LEVEL, 10) + 1,
ROUND(DBMS_RANDOM.VALUE(10, 1000), 2),
ROUND(DBMS_RANDOM.VALUE(0, 30), 2)
FROM DUAL CONNECT BY LEVEL <= 2000000;
-- 插入归档数据（约100万条，模拟历史数据迁移）
INSERT INTO T_ORDER_LOG_ARCHIVE(ID, ORG_ID, USER_ID, OP_TYPE, AMOUNT, CREATE_TIME, ARCHIVE_TIME)
SELECT
LEVEL,
'ORG' || LPAD(MOD(LEVEL, 100) + 1, 4, '0'),
MOD(LEVEL, 1000) + 1,
MOD(LEVEL, 4) + 1,
ROUND(DBMS_RANDOM.VALUE(1, 10000), 2),
SYSDATE - DBMS_RANDOM.VALUE(365, 730),
SYSDATE - DBMS_RANDOM.VALUE(1, 30)
FROM DUAL CONNECT BY LEVEL <= 1000000;
COMMIT;
-- 收集统计信息（优化前必须做）
CALL SP_TAB_STAT_INIT('SYSDBA', 'T_ORDER_LOG');
CALL SP_TAB_STAT_INIT('SYSDBA', 'T_ORG');
CALL SP_TAB_STAT_INIT('SYSDBA', 'T_ORDER_DETAIL');
CALL SP_TAB_STAT_INIT('SYSDBA', 'T_ORDER_LOG_ARCHIVE');
三、10个需要优化的SQL（题目）
题目1：索引列使用函数导致全表扫描
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;
问题：对CREATE_TIME使用了DATEDIFF函数，导致无法使用索引
答案：避免索引列函数运算
-- 优化前：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);
-- 配合索引
CREATE INDEX IDX_ORDER_LOG_CREATE_TIME ON T_ORDER_LOG(CREATE_TIME);
原理：保持索引列"干净"，让优化器能够使用索引扫描
题目2：OR条件导致索引失效
SELECT * FROM T_ORDER_LOG
WHERE ORG_ID = 'ORG0001'
OR USER_ID = 100
OR AMOUNT > 5000;
问题：OR条件中多个字段无法有效利用组合索引
答案：OR改写为UNION ALL
-- 优化前：OR条件无法有效利用索引
-- 优化后：拆分为UNION ALL
SELECT * FROM T_ORDER_LOG WHERE ORG_ID = 'ORG0001'
UNION ALL
SELECT * FROM T_ORDER_LOG WHERE USER_ID = 100 AND ORG_ID != 'ORG0001'
UNION ALL
SELECT * FROM T_ORDER_LOG WHERE AMOUNT > 5000
AND ORG_ID != 'ORG0001' AND USER_ID != 100;
-- 配合索引
CREATE INDEX IDX_ORDER_LOG_ORG_ID ON T_ORDER_LOG(ORG_ID);
CREATE INDEX IDX_ORDER_LOG_USER_ID ON T_ORDER_LOG(USER_ID);
CREATE INDEX IDX_ORDER_LOG_AMOUNT ON T_ORDER_LOG(AMOUNT);
原理：UNION ALL每个分支可利用各自索引，避免全表扫描
题目3：深度分页性能差
SELECT * FROM T_ORDER_LOG
ORDER BY ID
LIMIT 100000, 20;
问题：深度分页需要跳过大量数据，产生大量回表操作
答案：游标式分页（记录上次位置）
-- 优化前：LIMIT 100000, 20 需要跳过大量数据
-- 优化后：使用上一页最后一条记录的ID
SELECT * FROM T_ORDER_LOG
WHERE ID > 100000 -- 上一页最后的ID
ORDER BY ID
LIMIT 20;
-- 配合索引（主键自动索引）
原理：利用主键索引直接定位，避免大量回表。
题目4：隐式类型转换
SELECT * FROM T_ORDER_LOG
WHERE ORG_ID = 1;
问题：ORG_ID是VARCHAR类型，传入数字导致隐式转换，索引失效。
答案：保持数据类型一致
-- 优化前：WHERE ORG_ID = 1 （隐式转换）
-- 优化后：字符串匹配字符串
SELECT * FROM T_ORDER_LOG
WHERE ORG_ID = '1';
-- 配合索引
CREATE INDEX IDX_ORDER_LOG_ORG_ID ON T_ORDER_LOG(ORG_ID);
原理：避免隐式类型转换导致索引失效
题目5：NOT IN子查询性能差
SELECT * FROM T_ORDER_LOG
WHERE ID NOT IN (SELECT ID FROM T_ORDER_LOG_ARCHIVE);
问题：NOT IN子查询在大数据量下效率极低
答案：NOT EXISTS替代NOT IN
-- 优化前：NOT IN子查询
-- 优化后：NOT EXISTS
SELECT * FROM T_ORDER_LOG A
WHERE NOT EXISTS (
SELECT 1 FROM T_ORDER_LOG_ARCHIVE B
WHERE B.ID = A.ID
);
-- 配合索引
CREATE INDEX IDX_ARCHIVE_ID ON T_ORDER_LOG_ARCHIVE(ID);
原理：NOT EXISTS对NULL值处理更友好，执行计划更优
题目6：LIKE前置模糊查询
SELECT * FROM T_ORDER_LOG
WHERE REMARK LIKE '%紧急%';
问题：LIKE以%开头无法使用B树索引
答案：使用全文索引或反转索引
-- 方案1：如果业务允许，改为后缀匹配
SELECT * FROM T_ORDER_LOG
WHERE REMARK LIKE '紧急%';
-- 方案2：使用达梦全文索引
CREATE CONTEXT INDEX IDX_ORDER_LOG_REMARK ON T_ORDER_LOG(REMARK);
SELECT * FROM T_ORDER_LOG
WHERE CONTAINS(REMARK, '紧急');
-- 方案3：使用反转索引（适合后缀模糊）
CREATE INDEX IDX_ORDER_LOG_REMARK_REV ON T_ORDER_LOG(REVERSE(REMARK));
SELECT * FROM T_ORDER_LOG
WHERE REVERSE(REMARK) LIKE REVERSE('%紧急');
原理：LIKE前置%无法使用B树索引，需换用全文索引或反转索引
题目7：标量子查询导致循环访问
SELECT A.ID, A.ORG_ID, A.AMOUNT,
(SELECT ORG_NAME FROM T_ORG B WHERE B.ORG_ID = A.ORG_ID) AS ORG_NAME
FROM T_ORDER_LOG A
WHERE A.CREATE_TIME >= DATEADD(DAY, -7, SYSDATE);
问题：标量子查询每行都执行一次，产生循环依赖。
答案：JOIN替代标量子查询
-- 优化前：标量子查询每行执行一次
-- 优化后：一次性JOIN
SELECT A.ID, A.ORG_ID, A.AMOUNT, B.ORG_NAME
FROM T_ORDER_LOG A
LEFT JOIN T_ORG B ON B.ORG_ID = A.ORG_ID
WHERE A.CREATE_TIME >= DATEADD(DAY, -7, SYSDATE);
-- 配合索引
CREATE INDEX IDX_ORDER_LOG_ORG_ID_CREATE ON T_ORDER_LOG(ORG_ID, CREATE_TIME);
CREATE INDEX IDX_ORG_ORG_ID ON T_ORG(ORG_ID);
原理：JOIN一次性完成关联，避免逐行子查询
题目8：缺少合适的组合索引
SELECT * FROM T_ORDER_LOG
WHERE ORG_ID = 'ORG0020'
AND OP_TYPE = 2
AND CREATE_TIME >= DATEADD(DAY, -30, SYSDATE)
ORDER BY CREATE_TIME DESC;
问题：查询条件涉及多个字段，单列索引无法满足，需要回表
8答案：创建组合索引 + 覆盖索引
-- 优化前：单列索引需要回表
-- 优化后：组合索引覆盖查询字段（避免回表）
CREATE INDEX IDX_ORDER_LOG_ORG_OP_TIME ON T_ORDER_LOG(ORG_ID, OP_TYPE, CREATE_TIME DESC);
-- 如果查询字段不多，可以建覆盖索引
CREATE INDEX IDX_ORDER_LOG_COVER ON T_ORDER_LOG(ORG_ID, OP_TYPE, CREATE_TIME DESC, AMOUNT, STATUS);
原理：组合索引按最左前缀原则，排序字段放最后并指定DESC
题目9：GROUP BY未利用索引排序
SELECT ORG_ID, OP_TYPE, COUNT(*), SUM(AMOUNT)
FROM T_ORDER_LOG
WHERE CREATE_TIME >= DATEADD(DAY, -90, SYSDATE)
GROUP BY ORG_ID, OP_TYPE
ORDER BY ORG_ID, OP_TYPE;
问题：GROUP BY + ORDER BY无法利用索引有序性，导致额外排序
9答案：利用索引避免排序
-- 优化前：GROUP BY需要额外排序
-- 优化后：创建组合索引让数据天然有序
CREATE INDEX IDX_ORDER_LOG_GROUP ON T_ORDER_LOG(ORG_ID, OP_TYPE, CREATE_TIME);
-- 如果只需要聚合结果，可考虑物化视图
CREATE MATERIALIZED VIEW MV_ORDER_STATS
REFRESH COMPLETE ON DEMAND
AS
SELECT ORG_ID, OP_TYPE, COUNT(*) AS CNT, SUM(AMOUNT) AS SUM_AMOUNT
FROM T_ORDER_LOG
WHERE CREATE_TIME >= DATEADD(DAY, -90, SYSDATE)
GROUP BY ORG_ID, OP_TYPE;
如果必须要排序，就走强制索引 /*+index(T_ORDER_LOG,idx_ORDER_LOG_ORG_ID_OP_TYPE)*/
原理：索引有序性可消除SORT操作符
题目10：多表关联驱动表选择不当
SELECT /*+ ORDERED */
A.ORG_NAME, B.AMOUNT, C.PRODUCT_NAME, C.QUANTITY
FROM T_ORG A
JOIN T_ORDER_LOG B ON A.ORG_ID = B.ORG_ID
JOIN T_ORDER_DETAIL C ON B.ID = C.ORDER_ID
WHERE B.CREATE_TIME >= DATEADD(DAY, -1, SYSDATE)
AND C.UNIT_PRICE > 500;
问题：强制使用ORDERED提示但驱动表选择错误，导致大量关联。
10答案：调整驱动表 + 统计信息
-- 优化前：错误驱动表导致大量关联
-- 优化后：用小结果集做驱动表，并收集统计信息
-- 1. 先收集统计信息
CALL SP_TAB_STAT_INIT('SYSDBA', 'T_ORDER_LOG');
CALL SP_TAB_STAT_INIT('SYSDBA', 'T_ORDER_DETAIL');
CALL SP_TAB_STAT_INIT('SYSDBA', 'T_ORG');
-- 2. 移除ORDERED提示，让优化器自动选择
SELECT A.ORG_NAME, B.AMOUNT, C.PRODUCT_NAME, C.QUANTITY
FROM T_ORDER_LOG B
JOIN T_ORG A ON A.ORG_ID = B.ORG_ID
JOIN T_ORDER_DETAIL C ON B.ID = C.ORDER_ID
WHERE B.CREATE_TIME >= DATEADD(DAY, -1, SYSDATE)
AND C.UNIT_PRICE > 500;
-- 3. 配合索引
CREATE INDEX IDX_ORDER_LOG_CREATE ON T_ORDER_LOG(CREATE_TIME);
CREATE INDEX IDX_ORDER_DETAIL_ORDER_PRICE ON T_ORDER_DETAIL(ORDER_ID, UNIT_PRICE);
优化器会生成备用的执行计划，导致SQL执行变慢
使用/*+ ADAPTIVE_NPLN_FLAG(0)*/关闭备用执行计划会更优
发现不合理的优化，欢迎大家留言讨论
浙公网安备 33010602011771号