三级数据库技术——运行维护与优化

数据库运行与维护一、日常维护数据库日常维护是确保系统稳定、高效运行的基础工作主要包括以下任务1. 备份与恢复概述数据库备份与恢复是保障数据安全与业务连续性的核心维护任务。其主要目的是在数据丢失、损坏或系统故障时能够快速、准确地恢复数据到可用状态。备份的重要性防止数据丢失应对硬件故障、人为误操作、软件缺陷或恶意攻击。满足合规要求许多行业法规要求定期备份并保留数据副本。支持业务连续性灾难发生时备份是恢复系统运行的基础。备份类型完全备份备份整个数据库。恢复简单但耗时耗空间。-- 创建完整备份 BACKUP DATABASE [YourDatabase] TO DISK ND:\Backup\YourDatabase_Full.bak WITH INIT, NAME NYourDatabase-Full Database Backup, DESCRIPTION NFull backup of YourDatabase, STATS 10; GO增量备份仅备份自上次备份完全或增量以来发生变化的数据。备份快恢复时需要按顺序应用所有增量备份。差异备份备份自上次完全备份以来发生变化的数据。恢复时只需最近一次完全备份和最后一次差异备份。-- 创建差异备份 BACKUP DATABASE [YourDatabase] TO DISK ND:\Backup\YourDatabase_Diff.bak WITH DIFFERENTIAL, INIT, NAME NYourDatabase-Differential Database Backup, DESCRIPTION NDifferential backup of YourDatabase, STATS 10; GO日志备份备份事务日志支持时间点恢复。-- 创建事务日志备份 BACKUP LOG [YourDatabase] TO DISK ND:\Backup\YourDatabase_Log.trn WITH INIT, NAME NYourDatabase-Transaction Log Backup, DESCRIPTION NTransaction log backup of YourDatabase, STATS 10; GO恢复策略完全恢复使用完全备份恢复整个数据库。-- 从完整备份恢复数据库 RESTORE DATABASE [YourDatabase] FROM DISK ND:\Backup\YourDatabase_Full.bak WITH REPLACE, RECOVERY, MOVE YourDatabase TO ND:\Data\YourDatabase.mdf, MOVE YourDatabase_log TO ND:\Data\YourDatabase_log.ldf, STATS 10; GO时间点恢复利用事务日志将数据库恢复到故障前的特定时间点。-- 时间点恢复示例 -- 1. 恢复完整备份但不恢复数据库 RESTORE DATABASE [YourDatabase] FROM DISK ND:\Backup\YourDatabase_Full.bak WITH NORECOVERY, MOVE YourDatabase TO ND:\Data\YourDatabase.mdf, MOVE YourDatabase_log TO ND:\Data\YourDatabase_log.ldf; -- 2. 恢复事务日志备份到特定时间点 RESTORE LOG [YourDatabase] FROM DISK ND:\Backup\YourDatabase_Log.trn WITH RECOVERY, STOPAT 2024-01-15 14:30:00; GO文件/页恢复仅恢复损坏的个别数据文件或页面。最佳实践制定并定期测试备份与恢复计划。遵循3-2-1原则至少 3 份副本2 种不同介质1 份异地存储。监控备份作业的成功与失败。定期验证备份文件的完整性与可恢复性。-- 创建每日完整备份作业 USE [msdb]; GO EXEC dbo.sp_add_job job_name NDaily Full Backup, enabled 1, description NDaily full backup of YourDatabase; EXEC dbo.sp_add_jobstep job_name NDaily Full Backup, step_name NBackup Database, subsystem NTSQL, command NBACKUP DATABASE [YourDatabase] TO DISK ND:\Backup\YourDatabase_Full_$(ESCAPE_NONE(DATE)).bak WITH INIT, COMPRESSION, STATS 10;, database_name Nmaster; EXEC dbo.sp_add_schedule schedule_name NDaily at 2:00 AM, freq_type 4, -- 每天 freq_interval 1, active_start_time 20000; -- 02:00:00 EXEC dbo.sp_attach_schedule job_name NDaily Full Backup, schedule_name NDaily at 2:00 AM; GO2. 完整性维护目标确保数据的一致性和准确性保证实体、参照、域完整性不被破坏实施数据验证和约束检查主键、外键、唯一约束、check 约束、非空约束。定期运行完整性检查脚本检测页损坏、索引损坏、逻辑数据错误。处理约束冲突修复非法数据处理外键断链。索引完整性校验检测索引碎片、索引损坏。修复逻辑损坏DBCC 检查、修复数据库页错误。sql-- SQL Server完整性检测示例 DBCC CHECKDB([YourDatabase]) WITH NO_INFOMSGS;3. 安全性维护目标保障数据库访问安全防止越权访问、数据泄露管理用户权限和访问控制创建登录名、数据库用户授予 / 回收角色权限最小权限原则。处理安全日志和异常访问登录失败、权限变更、高危操作审计。定期更新安全补丁和策略数据库版本补丁、操作系统补丁。账号管理清理过期账号禁用闲置账号强密码策略。数据保护敏感数据加密、审计配置。sql-- 回收权限示例 REVOKE SELECT ON Table1 FROM User1;4. 存储空间管理目标合理管理磁盘、数据文件、日志文件防止空间耗尽规划数据增长和存储扩展设置文件自动增长手动扩容数据文件。优化存储结构和文件组织合理划分数据文件组收缩日志文件谨慎收缩数据文件。清理无用数据归档历史冷数据删除过期业务数据。手动清理临时段扩容临时空间回收空间。二、系统监控与分析核心本质只做查看、采集、统计、分析只发现问题不执行修改、修复、调优操作。动词特征监控、查看、统计、采集、观测、分析、收集。 ❗只要动手改、修复、处理、调整就不属于本模块归入「日常维护」或「系统性能优化调整」。两大监控方向① 对数据库架构体系的监控存储 / 磁盘层面聚焦磁盘、表空间、数据文件、段、区间等存储资源状态查看空间基本信息统计空间使用率、剩余空间大小分析空间是否需要自动扩展分析哪些数据表的增长造成空间上涨统计段占用空间、区间extent数量监控临时段的使用情况做题区分查看表空间使用率 → ✅系统监控与分析手动扩容表空间、分配存储空间 → ✅日常维护动手操作② 对数据库性能的监控运行效率层面聚焦服务器资源、缓存、锁、SQL 执行运行状态监控服务器硬件资源CPU、内存、磁盘 I/O统计各类缓存命中率数据缓冲区命中率、库缓存、共享池状态监控锁信息、锁等待事件观察死锁现象仅查看查看回滚段运行状态统计 SQL 执行情况统计索引使用情况只看哪些索引被使用 / 闲置不增删索引监控并发访问量、各类等待事件做题区分观察死锁、查看锁等待 → ✅系统监控与分析杀掉会话、终止事务解除死锁 → ✅系统性能优化调整统计索引使用情况 → ✅系统监控与分析创建、删除索引 → ✅系统性能优化调整三、性能优化与调整核心定义经过监控分析发现性能瓶颈之后动手修改数据库对象、参数、SQL解决性能问题提升运行效率。 动词特征修改、调整、新建、删除、改写、解决、优化。 ⚠️区别系统监控与分析只是 “看问题”性能优化调整是 “动手解决问题”。主要工作内容1. 数据库系统参数调优调整内存参数缓冲区、共享池大小调整最大连接数、各类超时参数调整事务隔离级别2. 索引相关调整创建适合业务的索引删除无用、低效索引重建碎片化索引区分统计查看索引使用情况监控分析增删重建索引性能优化调整3. SQL 与视图、存储过程优化改写慢查询 SQL 语句修改、调整视图定义优化存储过程、触发器改写执行逻辑坑点 ①为了解决慢查询而调整视图 → 性能优化调整 ②新业务需求新增业务视图→ 系统进化4. 存储结构调整调整分区表策略修改表存储结构、文件组配置5. 并发、锁冲突处理调优终止死锁会话解除死锁优化长事务减少锁持有时间调整锁策略减少锁等待区分观察死锁、查看锁等待事件 →系统监控与分析只看杀掉会话、解除死锁冲突 →系统性能优化调整动手处理四、系统进化系统进化典型内容应用程序升级、数据库重组和重构、DBMS 升级、OS 操作系统升级全部归属于系统进化。逐条解析1.应用程序升级上层业务软件版本迭代业务功能更新。属于应用层的系统进化不属于 DBA 日常维护。2.数据库重组和重构重组调整表、索引存储结构重新组织数据存储重构修改表结构、调整 ER 模型调整数据库逻辑结构适配新业务。业务驱动的结构改造不是单纯解决碎片性能问题属于系统进化。区分单纯重建索引消除碎片、提升查询速度 →性能优化调整为适配新业务对整个数据库做重组、重构改造 →系统进化3.DBMS数据库管理系统版本升级例如 SQL Server2019 升级到 2022大版本更迭。✅系统进化。 补充安装小安全补丁、漏洞修复属于日常维护大版本升级属于系统进化。4.OS 操作系统升级数据库服务器操作系统版本升级更换操作系统平台。✅系统进化属于软硬件平台整体迭代改造。