达梦数据库性能分析及SQL优化

---

排查思路

数据库问题的排查应遵循“由硬到软，自底向上”的排查思路进行检查和定位。硬件资源对于数据库的影响非常大，是软件正常运行的基础，当硬件资源达到瓶颈或出现故障的时候会多数据库造成致命性的问题，比如网络不稳定丢包，存储设备故障导致IO异常等都会造成不可预估的异常现象。一套数据库的运行依赖各种数据库服务，对于较为复杂的集群架构，服务器会运行多个数据库相关的后台服务，多个服务之间协同工作实现整个数据库集群的正常工作，如果出现服务中断，异常挂起都会造成影响。
硬件资源、服务等在部署之初就已经确定，出现故障虽然影响大，但一般属于小概率问题。绝大部的数据库问题都是由于不正确的使用造成的，比如不合理的数据库规划、复杂且高度耦合的业务逻辑、索引的不正确使用等。

分析排查步骤

服务器资源检查

内存使用情况

free -h
vmstat 1

cpu使用情况

top

查询占用cpu最多的线程

ps -eLo pcpu,pmem,pid,tid,psr,wchan:14,comm|grep 25403 |sort

[!info]
25403 是通过上面top命令查询到占用cpu最高的线程

参数内容说明

#CPU使用率 | 内存使用率 | 进程号 | 线程号 | 运行在哪个核上 | 进程休眠的内核函数地址，运行的任务显示为‘-’ | 进程可执行文件名

线程介绍

线程名	功能
dm_quit_thd	用于执行正常关闭数据库的线程
dm_io_thd	IO线程，由IO_THR_GROUPS参数控制，默认为2个线程
dm_rsyswrk_thd	异步归档线程，属于归档线程，主要负责将任务队列中的任务，按照归档类型进行相应的归档处理，一般有日志flushh线程触发
dm_chkpnt_thd	检查点线程，主要负责CKPT_LSN的管理
dm_redolog_thd	日志flush线程，负责日志刷盘，当事物提交或者发生检查点是触发
dm_hio_thd	IO线程，主要处理HFS相关的IO读取操作，比如HUGE表的IO读取就有该线程负责完成
dm_sqllog_thd	sql执行日志记录线程
dm_purge_thd	purge线程。主要负责回滚段清理
dm_tskwrk_thd	任务线程，由参数TASK_THREADS控制，取值范围为1-1000，默认为16，主要负责完成服务端SQL的解析运行等任务
dm_trctsk_thd	日志信息记录线程，主要负责数据库告警跟踪信息写入告警日志文件中
dm_wrkgrp_thd	工作线程，由参数WORKER_THREADS控制，取值范围为1-64，默认值为16，主要负责所有实际的数据相关操作
dm_audit_thd	审计线程，主要负责审计日志记录与更新
dm_sched_thd	调度线程，每秒钟轮询一次，主要负责接管数据库内部所有需要定时调度的任务，调度线程具备唤醒工作线程、向任务队列中添加任务队列、动态缓冲区检查、SQL缓存清理等权限
dm_lsnr_thd	监听线程。主要负责数据库服务器端口监听，处理客户端请求，并将连接请求加入到工作线程的任务队列，由工作线程完成任务处理。监听线程在数据库服务启动完成之后才启动，关闭数据库时首先被关闭
dm_sql_thd	用户线程。一般通过客户端连接的线程都是属于这个类别

相关动态视图

名称	说明
V$LATCHES	记录当前正在等待的线程信息
V$THREADS	记录当前系统中活动线程的信息
V$PROCESS	记录服务器进程信息

查询线程对应的SQL语句

select * from v$sessions where thrd_id='10640';

[!info]
10640 是通过上面命令查询到占用cpu或内存最高的pid的数值

数据库内存使用情况分析

达梦数据库的内存池包括共享内存池和一些运行时内存池，共享内存池是 DM Server 在启动时从操作系统申请一大片内存供系统运行时使用，避免运行期间频繁的进行系统调用降低系统运行效率，提供参数MEMORY_POOL设置大小和MEMORY_TARGET参数设置上限；运行时内存池为DM 的一些功能模块在运行时使用的自己运行时内存池，这些运行时内存池是从操作系统申请一片内存作为本功能模块的内存池来使用，如会话内存池、虚拟机内存池等。

查询占用内存高的SQL

SELECT
  A.CREATOR,
  B.SQL_TEXT,
  TRUNC(SUM(A.TOTAL_SIZE/1024/1024)) "分配大小(M)",
  TRUNC(SUM(A.DATA_SIZE /1024/1024)) "占用大小(M)"
FROM
  V$MEM_POOL A,
  V$SESSIONS B
WHERE
  A.CREATOR = B.THRD_ID
GROUP BY
  A.CREATOR,
  B.SQL_TEXT
ORDER BY 3 DESC

数据库的内存参数优化

内存池 v$mem_pool
在 DM Server 的运行期间，经常会申请与释放小片内存，而直接向操作系统申请和释放内存时需要发出系统调用，此时可能会引起线程切换，降低系统运行效率。于是 DM 采用采用共享内存池的方式：一次向操作系统申请一片较大内存，作为共享内存池。当系统在运行过程中需要申请小片内存时，可在共享内存池内进行申请，当用完该内存时，再释放掉，即归还给共享内存池。

主要参数

参数名	含义	建议值
MEMORY_POOL	共享池大小的参数为 ，缺省大小为 500M	2000
MEMORY_EXTENT_SIZE	指定了共享内存池每次扩展的大小	一般默认即可
MEMORY_TARGET	指定了共享内存池扩展到超过该值后，空闲时会收缩到的大小	内存大小的10%

缓冲区 v$bufferpool
缓冲区包括数据缓冲区、日志缓冲区、字典缓冲区、SQL 缓冲区
数据缓冲区是数据页写入磁盘之前以及从磁盘上读取数据页之后，数据页所存储的地方，分为四种类别

缓冲区名	说明	调整参数	建议值
NORMAL	NORMAL 缓冲区主要是提供给系统处理的一些数据页，没有特定指定缓冲区的情况下，默认缓冲区为 NORMAL	BUFFER(默认：1000MB)	总内存的30%-40%
FAST	缓冲区根据用户指定的 大小由系统自动进行管理	FAST_POOL_PAGES(默认3000)	99999
RECYCLE	缓冲区供临时表空间使用	RECYCLE(默认300MB)	总内存的3%-5%
KEEP	对缓冲区中的数据页很少或几乎不怎么淘汰出去，主要针对用户的应用是否需要经常处在内存当中	KEEP(默认8MB)	一般默认即可

SQL优化

查看SQL执行计划

使用“达梦管理工具”查看非常简单，只要在SQL窗口按“F9”就可以显示执行计划

在DISQL窗口可以在语句前增加 EXPLAIN 就可以显示出语句的执行计划

EXPLAIN select * from t1 where NAME='AAA';

执行计划执行顺序讲解

• 缩进越深的越先执行。
• 同样缩进的上面的先执行，下面的后执行。
• 上下的优先级高于内外。

执行计划操作符讲解

NSET：收集结果集。用于结果集收集的操作符， 一般是查询计划的顶层节点
PRJT：投影。关系的“投影”(project)运算，用于选择表达式项的计算；广泛用于查询，排序，函数索引创建等
SLCT：选择。关系的“选择” 运算，用于查询条件的过滤
AAGR：简单聚集。用于没有group by的count，sum，age，max，min等聚集函数的计算
FAGR：快速聚集。用于没有过滤条件时从表或索引快速获取 MAX/MIN/COUNT值，DM数据库是世界上单表不带过滤条件下取COUNT值最快的数据库
HAGR：HASH分组聚集。用于分组列没有索引只能走全表扫描的分组聚集
SAGR：流分组聚集。用于分组列是有序的情况下可以使用流分组聚集，SAGR2性能优于HAGR2
BLKUP：二次扫描。先使用2级别索引定位，再根据表的主键、聚集索引、 rowid等信息定位数据行
CSCN：全表扫描。CSCN2是CLUSTER INDEX SCAN的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。在一个高并发的系统中应尽量避免全表扫描
SSEK：索引扫描。SSEK2是二级索引扫描即先扫描索引，再通过主键、聚集索引、ROWID等信息去扫描表
CSEK：索引扫描。CSEK2是聚集索引扫描只需要扫描索引，不需要扫描表
SSCN：索引扫描。SSCN2是索引全扫描，不需要扫描表

表连接详解

NEST LOOP INDEX JOIN2：嵌套循环连接。
两层嵌套循环结构，有驱动表和被驱动表之分。 选定一张表作为驱动表，遍历驱动表中的每一行，根据连接条件去匹配第二 张表中的行。驱动表的行数就是循环的次数，这个很大程度影响了执行效率。

需注意的问题:
选择小表作为驱动表。统计信息尽量准确，保证优化器选对驱动表。
大量的随机读。如果没有索引，随机读很致命，每次循环只能读一块， 不能读多块。使用索引可以解决这个问题。

HASH2 INNER JOIN HASH：哈希连接。
使用较小的Row source 作为Hash table和Bitmap， 而第二个row source被hashed，根据bitmap与第一个row source生成的hash table 相匹配，bitmap查找的速度极快。

MERGE JOIN：排序合并连接

索引优化

在数据库中，索引是用于加速数据检索的一种结构。它类似于图书目录，通过对表中的特定列进行排序并创建指向数据的指针（也称为引用），从而实现快速查找所需信息的功能。当用户执行查询时，系统会使用这些指针来跳过扫描和过滤不必要的数据，以达到更快的数据访问速度。
在日常SQL优化过程中通过合理的创建索引可以解决80%以上的优化问题。

• 合理选择索引类型：根据应用场景及业务需求选择最适合的索引形式，如 B-Tree、Hash 等；
• 适当设置索引数量：过多或过少的索引都会对性能产生不良影响。因此，应定期监控和分析哪些列被用于查询以确定最合适的索引个数。同时考虑磁盘空间与读写速度的因素；
• 避免重复索引：若同一张表中有多处存在相同的列名，则不应该在每个地方都添加相应的索引来提高性能。这样做只会增加存储负担而不会明显提升访问效率；
• 定期分析和维护：利用达梦数据库自带的 SQL 语句（例如 DM_ANALYZE）来识别、调整或删除不常用的索引，确保数据的结构与实际操作需求相匹配。以最大化性能。

更新统计信息

对象统计信息描述了对象数据的分布特征。统计信息是优化器的代价计算的依据，可以帮助优化器较精确地估算成本，对执行计划的选择起着至关重要的作用。 统计信息的收集频率是一把双刃剑，频率太低导致统计信息滞后，频率太高又影响查询性能，因此，系统管理员需要根据实际情况，合理安排统计信息收集的频率。
当发现运行一直很快的SQL突然变慢了，建了索引的列执行计划不走索引，都可以考虑是不是统计信息太久没有更新，数据的分布特征已经发生了变化。

#对库上所有模式下的所有用户表以及表上的所有索引生成统计信息
CALL SP_DB_STAT_INIT ();

#更新用户的索引的统计信息
SP_INDEX_STAT_INIT(USER,'IDX_C1_T1');  

#更新某一个表上的指定索引
stat 100 on index EBASEINFO_UNISCID_INDEX;

#更新某一个表的某一列信息
stat 100 on  E_FR_EPBBASEINFO(ENTNAME);

#收集表的统计信息
DBMS_STATS.GATHER_TABLE_STATS('模式名','表名',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

#收集列的统计信息
SP_COL_STAT_INIT('模式名','表名','列名');

HINT

HINT是一种嵌入在 SQL 语句中的特殊注释，用于指导数据库优化器选择特定的执行计划。HINT不会改变 SQL 语句的逻辑，但会影响其执行方式。

HINT 的语法格式

SELECT /*+ HINT_NAME(参数) */ 列名 FROM 表名 WHERE 条件;

• /*+ ... */：HINT 的注释格式。
• HINT_NAME：HINT 的名称。
• 参数：HINT 的参数（可选）

常用的 HINT
强制使用指定的索引

SELECT /*+ INDEX(表名 索引名) */ 列名 FROM 表名 WHERE 条件;

强制对表进行全表扫描

SELECT /*+ FULL(表名) */ 列名 FROM 表名 WHERE 条件;

强制按照 FROM 子句中表的顺序进行连接

SELECT /*+ ORDERED */ 列名 FROM 表1, 表2 WHERE 表1.列 = 表2.列;

强制使用嵌套循环连接(Nested Loop Join)

SELECT /*+ USE_NL(表1 表2) */ 列名 FROM 表1, 表2 WHERE 表1.列 = 表2.列;

强制使用哈希连接(Hash Join)

SELECT /*+ USE_HASH(表1 表2) */ 列名 FROM 表1, 表2 WHERE 表1.列 = 表2.列;

强制使用排序合并连接(MERGE SORT)

SELECT /*+ use_merge(表1 表2) */ 列名 FROM 表1, 表2 WHERE 表1.列 = 表2.列;

启用并行查询

SELECT /*+ PARALLEL(表名, 并行度) */ 列名 FROM 表名 WHERE 条件;

HINT 的使用场景
(1) 优化慢查询
当某个查询执行效率较低时，可以通过 HINT 强制优化器选择更优的执行计划。
(2) 测试执行计划
在测试环境中，可以通过 HINT 验证不同执行计划的性能差异。
(3) 解决优化器错误
当优化器选择的执行计划不理想时，可以使用 HINT 进行干预。

[!注意事项]

1. 谨慎使用：HINT 是一种强制干预手段，使用不当可能导致性能下降。
2. 版本兼容性：不同版本的达梦数据库可能支持的 HINT 不同，需参考官方文档。
3. 测试验证：在生产环境中使用 HINT 前，建议在测试环境中充分验证。
4. 结合执行计划：使用 HINT 时，建议结合 EXPLAIN 或 EXPLAIN PLAN 分析执行计划。

达梦社区地址：https://eco.dameng.com