浅谈数据库性能优化

一、数据库性能问题的背景
随着业务数据规模持续增长并进入海量数据阶段，系统在瞬时高并发场景下对数据库造成明显冲击，当前数据库压力主要表现为 TPS、QPS 长期高位运行、日志写入频繁以及热块读突出，尤其是索引热块读问题较为严重。在日常数据库操作中，性能问题通常是由各种因素引起的。这些因素可能来自数据库的硬件资源限制，也可能与 SQL 查询的执行计划相关。以下是一些常见的数据库性能问题：
1、运行态问题
运行态问题通常与 SQL 执行计划相关。若数据库优化器生成了非最优的执行计划，SQL 请求可能会扫描大量数据，导致性能低下。此外，计划缓存命中率不高也会增加 SQL 执行的编译时间。运行态问题可以通过调整执行计划来解决。
2、阻塞态问题
数据库的工作线程在进行 CPU 计算或等待某些资源时，可能进入阻塞态。阻塞态表明系统的并发能力受限，无法通过增加资源提升吞吐量。解决阻塞态问题的首要步骤是识别阻塞点，并进行针对性优化。
二、数据库性能优化的目标
根据不同角色的需求，数据库优化的目标各有不同：

业务角度（用户）：减少用户页面响应时间，提升用户体验。
数据库角度（开发）：减少 SQL 响应时间，提高数据库查询效率。
数据库服务器角度（运维）：充分使用数据库服务器的物理资源，减少 CPU、IO、内存的使用，避免资源瓶颈。

三、数据库优化的关键指标
优化数据库性能时，以下几个关键指标是必须关注的：

SQL 平均响应时间：优化 SQL 执行，减少查询时间。
数据库服务器 CPU 占用率：降低 CPU 占用，避免过度消耗。
数据库服务器 IO 使用率：降低 IO 操作，避免磁盘成为瓶颈。
内存使用率：优化内存使用，避免过多内存消耗。

四、性能问题的定位
性能问题的定位需要收集大量的系统信息，包括硬件资源使用情况、数据库软件配置、SQL 执行状况等。

硬件信息收集
硬件系统资源使用情况可以通过 nmon、OSW（OSWatcher）等操作系统监控工具进行全面观测，能够快速定位系统层面的异常问题，例如网络丢包、内存持续增长（潜在内存泄漏）、进程或线程数异常暴增等，这类问题往往会直接或间接影响数据库性能。
在实际排查过程中，常见现象包括磁盘Busy长时间接近100%，IO 响应时间显著升高。需要注意的是，这种情况往往并非业务变慢的根本原因，而更可能是问题的“结果”。其背后原因通常包括：

存在执行效率不高的 SQL 语句，产生大量无效或重复的磁盘 IO；
单条或多条 SQL 导致 IOPS 超过磁盘或存储阵列的承载能力；
无效的顺序/随机IO占用了大量磁盘带宽，引发 IO 队列堆积和整体 IO 拥堵。
因此，当出现磁盘繁忙、IO 响应时间过长时，不能仅停留在硬件层面扩容或更换存储，而应进一步结合数据库层面的 SQL 执行计划、逻辑读/物理读情况以及业务访问模式进行综合分析，从源头上定位和解决性能瓶颈，避免“治标不治本”。

数据库软件信息收集
了解数据库的运行环境对于诊断性能问题至关重要。需要收集的关键信息包括：
数据库版本与架构配置（如单机、HA、主备、DSC、MPP 等）。
数据库的交易类型（OLTP、OLAP）。
会话数、事务数、热点表、热点 SQL 等信息。
用户信息收集
与数据库的使用者沟通，了解是否有新业务上线、硬件升级、用户增加等变动，以确认是否是环境变化导致的性能问题。

4.慢SQL和阻塞监控

（1）已执行超过2秒的活动 SQL
select  *  from
        (
                SELECT
                        user_name                               ,
                        clnt_ip                                 ,
                        sess_id                                 ,
                        sql_text                                ,
                        datediff(ss, last_send_time, sysdate) ss,
                        SF_GET_SESSION_SQL(SESS_ID) fullsql     
                FROM
                        V$SESSIONS
                WHERE
                        STATE='ACTIVE' and user_name!='SYSDBA'
        )
where ss>=2 order by 5 desc;         
（2）锁超时查询阻塞会话
WITH LOCKS
     AS (SELECT O.NAME,L.*,S.SESS_ID,S.SQL_TEXT,S.CLNT_IP,S.LAST_SEND_TIME
           FROM V$LOCK L, SYSOBJECTS O, V$SESSIONS S
          WHERE L.TABLE_ID = O.ID AND L.TRX_ID = S.TRX_ID),
     LOCK_TR
     AS (SELECT TRX_ID WT_TRXID, ROW_IDX BLK_TRXID
           FROM LOCKS
          WHERE BLOCKED = 1),
     RES
     AS (SELECT SYSDATE STATTIME,T1.NAME,T1.SESS_ID WT_SESSID,S.WT_TRXID,
                T2.SESS_ID BLK_SESSID,S.BLK_TRXID,T2.CLNT_IP,
                SF_GET_SESSION_SQL (T1.SESS_ID) FULSQL,
                DATEDIFF (SS, T1.LAST_SEND_TIME, SYSDATE) SS,
                T1.SQL_TEXT WT_SQL
           FROM LOCK_TR S, LOCKS T1, LOCKS T2
          WHERE     T1.LTYPE = 'OBJECT'
                AND T1.TABLE_ID <> 0
                AND T2.LTYPE = 'OBJECT'
                AND T2.TABLE_ID <> 0
                AND S.WT_TRXID = T1.TRX_ID
                AND S.BLK_TRXID = T2.TRX_ID)
SELECT DISTINCT WT_SQL,CLNT_IP,SS,WT_TRXID,BLK_TRXID
  FROM RES;
（3）查询死锁历史事务信息
select
        dh.trx_id ,
        sh.sess_id,
        wm_concat(top_sql_text)
from
        V$DEADLOCK_HISTORY dh,
        V$SQL_HISTORY sh
where
        dh.trx_id =sh.trx_id
    and dh.sess_id=sh.sess_id
group by
        dh.trx_id, sh.sess_id;
（4）开启SQL日志sqllog
--设置 SQL 过滤规则，只记录必要的 SQL，生产环境不要设成1
2 只记录 DML 语句    
3 只记录 DDL 语句   
22 记录绑定参数的语句
25 记录 SQL 语句和它的执行时间    
28 记录 SQL 语句绑定的参数信息
SELECT SF_SET_SYSTEM_PARA_VALUE('SQL_TRACE_MASK','2:3:22:25:28',0,1);

--同步日志会严重影响系统效率，生产环境必须设置为异步日志
SELECT SF_SET_SYSTEM_PARA_VALUE('SVR_LOG_ASYNC_FLUSH',1,0,1);

--下面这个语句设置只记录执行时间超过 200ms 的语句
SELECT SF_SET_SYSTEM_PARA_VALUE('SVR_LOG_MIN_EXEC_TIME',200,0,1);

--下面的语句查看设置是否生效
SELECT * FROM V$DM_INI where para_name='SVR_LOG_ASYNC_FLUSH';
SELECT * FROM V$DM_INI where para_name='SQL_TRACE_MASK';
SELECT * FROM V$DM_INI where para_name='SVR_LOG_MIN_EXEC_TIME';

--开启 SQL 日志：
SP_SET_PARA_VALUE(1, 'SVR_LOG', 1);

--关闭 SQL 日志：
SP_SET_PARA_VALUE(1, 'SVR_LOG', 0);

五、常见的性能优化方法

执行计划分析
执行计划是数据库性能优化的核心，优化器是否能够生成最优执行计划，直接决定了 SQL 的整体执行效率。仅依赖预估执行计划往往难以准确反映 SQL 在真实运行环境下的性能表现，因此需要结合真实执行计划和运行期分析手段，对 SQL 的实际执行情况进行量化评估。
达梦数据库提供了 ET（Execution Trace）机制，ET 是达梦内置的系统存储过程工具，能够对 SQL 执行过程进行精细化统计，按操作符维度记录每个执行步骤的时间消耗和执行情况。通过 ET 分析，可以准确定位 SQL 执行过程中耗时较高或效率较低的操作环节，例如全表扫描、索引访问、排序、连接等，从而识别真正的性能瓶颈。
基于 ET 的分析结果，可以有针对性地对 SQL 语句、索引结构或表设计进行优化，避免盲目调优，实现从“经验优化”向“数据驱动优化”的转变，为数据库性能提升提供可靠依据。

达梦ET默认未启用，设置启动以下三个参数可以启用ET（ENABLE_MONITOR、MONITOR_TIME和MONITOR_SQL_EXEC）；

其中，ENABLE_MONITOR和MONITOR_TIME默认已开启，如果未开启可以使用如下方法开启：
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);

MONITOR_SQL_EXEC为会话级动态参数，可以设置只针对当前会话开启：
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);
执行SQL语句，我们会看到一个执行号，直接点这个执行号，即可调用ET；
在知道执行号的情况下，CALL ET(124571);

表设计优化
表设计对数据库整体性能具有基础性影响，合理的表结构能够显著提升查询效率、降低系统资源消耗。常见的表设计优化措施包括：

合理选择表类型：根据业务访问特征选择合适的表类型，如行存储、列存储、HUGE 表等，以匹配不同的读写模式和数据规模场景。
HUGE 表并发 DML 优化：针对高并发插入、更新及查询场景，对 HUGE 表进行专项优化，降低锁冲突和资源争用，提升并发处理能力。
分区表设计优化：通过科学的分区策略，将数据合理拆分到不同分区，减少单次扫描数据量，提升查询效率和维护效率。
临时表使用优化：优化临时表的创建方式和使用频率，避免不必要的磁盘 I/O 和锁竞争，提升会话执行效率。
字段类型合理化设计：根据实际业务需求选择合适的数据类型和长度，减少存储空间占用，提高数据访问和处理效率。
外键及关联索引优化：合理设计外键约束及其关联索引，在保证数据一致性的同时，提升关联查询性能。
适度反范式设计：在保证数据一致性和可维护性的前提下，引入反范式设计，减少复杂关联查询，提升核心业务查询响应速度。

索引设计优化
索引是数据库查询性能优化的关键手段，合理的索引设计能够显著降低 SQL 执行成本、提升查询响应效率。在索引优化过程中，需要结合实际业务访问模式进行综合分析，重点关注查询条件、关联方式及排序方式，并评估索引的选择性与覆盖能力。同时，还需充分考虑索引带来的维护成本，避免因索引数量过多或设计不合理，增加数据写入开销和系统资源消耗，从而在查询性能提升与系统整体负载之间取得平衡
配置参数优化
数据库配置参数对整体性能具有直接影响，是性能优化的重要组成部分。通过合理调整配置参数，可以更充分地发挥硬件资源能力，提升系统整体运行效率。常见的配置优化措施包括：适当增加内存缓存相关参数，提升数据和索引的缓存命中率，减少磁盘 IO 访问；结合实际业务负载特征调整查询优化相关参数，使优化器生成的执行计划更加符合 OLTP、OLAP 或混合型业务场景。
收集统计信息
数据库统计信息是优化器生成执行计划的重要依据，其准确性直接影响 SQL 的执行效率。通过定期收集和维护统计信息，尤其是对热点表、频繁变更数据表及核心业务表的统计信息进行重点维护，可以有效避免因统计信息失真导致的执行计划偏差，确保优化器能够选择更合理的访问路径和执行策略，从而提升整体数据库性能与稳定性。

--全库级别
CALL SP_DB_STAT_INIT (); --对库上所有表及索引 生成统计信息
--收集shema统计信息
CALL DBMS_STATS.GATHER_SCHEMA_STATS('大写的用户名',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
--收集表的统计信息
CALL DBMS_STATS.GATHER_TABLE_STATS('大写的用户名', '大写的表名',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
--收集索引的统计信息
DBMS_STATS.GATHER_INDEX_STATS('username','IDX_T2_X');

作者