达梦数据库查询较慢的快速诊断及优化

目录

• 1 快速诊断：定位瓶颈所在
• 1.1 查看当前慢SQL
• 1.2 检查系统资源状态
• 2 SQL语句优化
• 2.1 分析执行计划
• 2.2 常见SQL优化技巧
• 3. 索引优化策略
• 3.1 检查缺失索引
• 3.2 创建合适的索引
• 4. 数据库参数优化
• 4.1 关键性能参数检查
• 5. 统计信息维护
• 5.1 更新统计信息
• 5.2 定期维护脚本
• 6. 架构和设计优化
• 6.1 分区表优化
• 6.2 物化视图
• 7. 紧急优化措施
• 总结

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 缓冲池大android小_G,
      sum(rat_hit) /count(*) 命中率
from
      v$bufferpooUPOtYvzMl
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_INI编程客栈T('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 )php 
	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语句，再调整索引，最后修改参数
• 测试环境验证：所有优化先在测试环境验证
• 监控效果：优化后持续监控性能变化
• 定期维护：建立定期的统计信息更新和索引重建任务

总结

到此这篇关于达梦数据库查询较慢的快速诊断及优化的文章就介绍到这了,更多相关达梦数据库查询较慢内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！