平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“达梦数据库查询较慢的快速诊断及优化”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。
1 更快诊断：定位瓶颈所在
1.1 查看当前慢SQL
— 最慢的 20 条 SQL 统计
SELECT TOP 20 START_TIME,TIME_USED/1000 TIME_USED,TOP_SQL_TEXT FROM V$SQL_HISTORY ORDER BY TIME_USED DESC;
SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;
—查询慢 SQL 及阻塞
SELECT
DS.SESS_ID "被阻塞的会话ID",
DS.SQL_TEXT "被阻塞的SQL",
DS.TRX_ID "被阻塞的事务ID",
(CASE L.LTYPE WHEN 'OBJECT' THEN '对象锁' WHEN 'TID' THEN '事务锁' END CASE ) "被阻塞的锁类型",
DS.CREATE_TIME "开始阻塞时间",
SS.SESS_ID "占用锁的会话ID",
SS.SQL_TEXT "占用锁的SQL",
SS.CLNT_IP "占用锁的IP",
L.TID "占用锁的事务ID"
FROM
V$LOCK L
LEFT JOIN V$SESSIONS DS
ON
DS.TRX_ID = L.TRX_ID
LEFT JOIN V$SESSIONS SS
ON
SS.TRX_ID = L.TID
WHERE
L.BLOCKED = 1;
1.2 检查系统资源状态
— 查看缓存命中率
结合项目来看，数据缓冲区是 DMSERVER 在将数据页写入磁盘之前以及从磁盘上读取数据页之后，数据页所存储的地方。
结合项目来看，数据缓冲区设定得太小，会导致缓冲页命中率低，磁盘 IO 频繁；将其设定得太大，又会导致操作系统内存本身不够用。
select
name 缓冲池名称,
sum(page_size)*sf_get_page_size 缓冲池大小_G,
sum(rat_hit) /count(*) 命中率
from
v$bufferpool
group by name;
2 SQL语句优化
2.1 分析执行计划
— 对慢SQL生成执行计划
EXPLAIN SELECT * FROM your_table WHERE your_conditions;
执行计划重点关注：
是否采用了合适的索引（INDEX SCAN）
避免全表扫描（CROSS INDEX SCAN）
注意排序操作（SORT）是否必要
2.2 常用SQL优化技巧
— 避免 SELECT *，只取需的字段
SELECT id, name FROM users WHERE status = 1; -- 好
SELECT * FROM users WHERE status = 1; -- 差
— 为查询条件字段添加索引
CREATE INDEX idx_users_status ON users(status);
— 避免在WHERE条件中对字段进行函数操作
SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m') = '2024-01'; -- 差
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01'; -- 好
— 采用EXISTS代替IN（大数据量时）
SELECT * FROM table1 WHERE EXISTS (
SELECT 1 FROM table2 WHERE table1.id = table2.id
);
3. 索引优化策略
3.1 检查缺失索引
— 查找可能需索引的表
- 从实现思路看，频繁用来查询条件的列所在的表：如果某个表的列经常出现在 WHERE 子句中作为查询条件，该表可能需索引。
- 主键或唯一性约束的表：达梦数据库会自动为这些列新建索引，但如果需额外的查询优化，能够手动添加其他索引。
- 结合项目来看，连接操作中采用的列所在的表：如果某个表经常在 JOIN 操作中采用，能够为连接列新建索引以提高性能。
- 数据量较大的表：对于数据量较大的表，适当的索引能够显著减少查询时间。
- 结合项目来看，排序或分组操作中采用的列所在的表：如果某个表的列经常用来 ORDER BY 或 GROUP BY 操作，能够考虑新建索引。
3.2 新建合适的索引
在以下场景下可考虑新建索引：
仅当要借助索引访问表中很少的一部分行（1%~20%）。
索引可覆盖查询所需的所有列，不需额外去访问表。
存在下列情况将导致无法采用索引：
组合索引中，条件列中没有组合索引的首列。
条件列带有函数或计算。
在这个场景下，索引排序是按照字段值进行排序的，字段值借助函数或计算后的值索引无法拿到。
索引过滤性能不好时。
4. 数据库参数优化
4.1 关键性能参数检查
内存相关
从实现思路看，BUFFER = 10000 # 缓冲区大小(MB)，建议系统内存的50-70%
MAX_BUFFER = 10000 # 最大缓冲区
SORT_BUF_SIZE = 200 # 排序缓冲区(MB)
HJ_BUF_SIZE = 200 # HASH连接缓冲区(MB)
#并行处理
PARALLEL_POLICY = 1 # 启用并行
PARALLEL_THREAD_NUM = 4 # 同时行线程数
#优化器相关
OPTIMIZER_MODE = 1 # 优化器模式
5. 统计信息维护
5.1 更新统计信息
— 更新表的统计信息
CALL SP_TAB_STAT_INIT('SCHEMA_NAME', 'TABLE_NAME');
— 更新整个模式的统计信息
CALL SP_DB_STAT_INIT('SCHEMA_NAME');
— 收集列统计信息
CALL SP_COL_STAT_INIT('SCHEMA_NAME', 'TABLE_NAME', 'COLUMN_NAME');
5.2 定期维护脚本
举例：
— 更新模式 DB_TEST 下的统计信息
DBMS_STATS.GATHER_SCHEMA_STATS('DB_TEST', 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
— 更新特定表的统计信息
DBMS_STATS.GATHER_TABLE_STATS('username', 'table_name', null, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO', cascade => true, degree => 8);
— 更新所有表和索引的统计信息并记录日志
drop table if exists my_tab_stats;
create table my_tab_stats(table_name varchar2(100),owner varchar2(100),stat_flag varchar2(5),begin_time timestamp,end_time timestamp);
insert into my_tab_stats(table_name,owner) select table_name,owner from dba_tables where owner in ('OA');
update my_tab_stats set stat_flag = 0;
commit;
begin
for tb in (select rowid,table_name,owner from my_tab_stats where stat_flag = 0 )
loop
update my_tab_stats set begin_time=sysdate where my_tab_stats.table_name=tb.table_name and my_tab_stats.owner=tb.owner;
dbms_stats.gather_table_stats(tb.owner,tb.table_name,null,100,true,'FOR ALL COLUMNS SIZE AUTO',cascade => true, degree => 8);
update my_tab_stats set stat_flag = 1,end_time=sysdate where my_tab_stats.table_name = tb.table_name and my_tab_stats.owner=tb.owner;
commit;
end loop;
end;
6. 架构和设计优化
6.1 分区表优化
– 对大表进行分区
CREATE TABLE sales (
sale_id INT,
sale_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE(sale_date)
(
PARTITION p2023 VALUES LESS THAN (‘2024-01-01'),
PARTITION p2024 VALUES LESS THAN (‘2025-01-01')
);
6.2 物化视图
— 为复杂查询新建物化视图
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT customer_id, SUM(amount) as total_amount,
COUNT(*) as order_count
FROM sales
GROUP BY customer_id;
7. 紧急优化措施
如果系统正在经历严重性能问题：
—立即终止阻塞会话：
SP_CLOSE_SESSION(session_id); — 谨慎采用！
—清理缓存：
—重启数据库服务（最后手段）
优化实施建议
- 从易到难：先优化SQL语句，再调整索引，最后修改参数
- 测试环境验证：所有优化先在测试环境验证
- 监控效果：优化后持续监控性能变化
- 定期维护：建立定期的统计信息更新和索引重建任务
总结
到此这篇关于达梦数据库查询较慢的更快诊断及优化的文章就介绍到这了,更多相关达梦数据库查询较慢内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多兼容脚本之家！
- 达梦数据库(DM数据库)的查询与操作指南
