《数据库系统工程师》考试大纲概况（后续分段详写）

出门喝奶茶

已于 2025-01-16 17:08:06 修改

阅读量2.3k

点赞数 35

分类专栏：笔记文章标签：数据库 oracle

于 2024-11-18 16:07:12 首次发布

本文链接：https://blog.csdn.net/qq_46301912/article/details/143843910

版权

笔记专栏收录该内容

6 篇文章

订阅专栏

软考中级数据库（通常指的是《数据库系统工程师》）的考点大纲涵盖了数据库的设计、管理、优化、及相关技术等多个方面。以下是软考中级数据库的一些主要考点大纲：

1. 数据库基础概念

数据库的定义与基本特点
加粗样式数据、数据库、数据库管理系统（DBMS）、数据库系统（DBS）

数据模型：层次模型、网状模型、关系模型及其特点
数据库体系结构（一级、二级、三级模式架构）
数据独立性（逻辑数据独立性和物理数据独立性）
数据库标准与规范：
数据定义语言（DDL）、数据操纵语言（DML）、数据控制语言（DCL）
SQL语言的基本操作和语法（查询、插入、删除、更新）

数据库管理系统（DBMS）的功能与作用
数据模型（如层次模型、网络模型、关系模型、面向对象模型等）
数据库系统的组成部分
数据库管理系统的分类（如关系型、非关系型数据库）

2. 关系型数据库

关系数据库的基本概念（表、行、列、主键、外键、索引等）
关系代数与关系演算
关系数据库的范式（1NF、2NF、3NF、BCNF）

数据库安全性：

数据库访问控制（权限管理）
数据加密技术
用户管理与角色管理
数据审计和日志
数据库完整性：

实体完整性、参照完整性、自定义完整性约束
数据库恢复与并发控制：

恢复机制（日志、检查点、备份与恢复技术）
事务管理及ACID特性（原子性、一致性、隔离性、持久性）
并发控制技术：
锁机制、两段锁协议、多版本并发控制（MVCC）
数据库性能优化：

查询优化：索引、视图、存储过程的使用
数据库设计优化：分区、分片
数据库调优工具和分析方法

数据库设计的原则

关系数据库设计的步骤：

需求分析、概念设计、逻辑设计、物理设计
ER模型（实体-关系模型）及其与关系模型的转换

3. SQL语言

SQL的基本概念和分类（数据定义语言DDL、数据操作语言DML、数据控制语言DCL等）
数据查询（SELECT语句，WHERE、GROUP BY、HAVING、ORDER BY等子句）
多表查询（联接查询、子查询、集合运算）
数据操作（INSERT、UPDATE、DELETE等）
数据约束（主键、外键、唯一约束、检查约束等）
索引、视图、存储过程、触发器等高级SQL功能

4. 数据库设计

数据库设计的概述

数据库设计的目标
数据的集成性：消除数据冗余，减少数据的不一致性。
数据的独立性：应用程序与数据存储分离，便于维护和扩展。
数据的共享性：支持多用户同时访问。
数据的安全性和完整性：确保数据的保密性、合法性和一致性。
数据库设计的基本原则

结构化：遵循规范化理论，避免冗余。
易扩展性：设计应能方便适应需求的变化。
高效性：设计应考虑性能优化需求。
安全性：设计需考虑访问控制和数据加密。

数据库设计的主要阶段

需求分析
了解系统对数据的功能需求和性能需求。
概念设计
设计ER图，描述数据实体及其关系。
逻辑设计
将概念模型转换为关系模型，进行规范化处理。
物理设计
确定存储结构、索引、分区策略等。
数据库实施
建库及测试。
数据库运行与维护
持续优化数据库性能，维护数据的完整性和安全性。

数据库设计的详细步骤

1. 需求分析

目标：明确用户对数据库系统的需求。
任务：
收集和分析业务需求。
确定关键数据、处理流程、用户访问需求。
需求说明书、数据流程图。

2. 概念设计

目标：建立与具体DBMS无关的概念数据模型。
任务：
实体和属性的确定
找出业务中的实体（如“用户”、“订单”）和属性（如“姓名”、“订单编号”）。
实体间的关系设计
确定一对一 (1:1)、一对多 (1
)、多对多 (M
) 的关系。
ER图的绘制
使用ER模型绘制实体、属性及关系。
ER图（实体-关系模型）。

3. 逻辑设计

目标：将概念模型转换为逻辑模型（关系模型）。
任务：
实体转换为关系表
每个实体对应一个关系表。
属性为列。
主键用于唯一标识记录。
关系转换为表设计
一对多关系：在“多”的一端添加“1”端的主键作为外键。
多对多关系：创建中间表，包含双方主键作为复合主键。
应用规范化理论
第一范式（1NF）：消除非原子值，确保字段不可再分。
第二范式（2NF）：消除部分函数依赖（主属性对主键的依赖完整）。
第三范式（3NF）：消除传递函数依赖（属性不通过其他属性依赖主键）。
规范化后的关系模型。

4. 物理设计

目标：优化数据库的存储结构，提升性能。
任务：
存储结构设计
索引设计：选择适合的索引类型（主键索引、唯一索引、普通索引、全文索引）。
分区设计：对大数据量表进行水平或垂直分区。
表设计：根据业务需求，选择合适的数据类型、约束条件（如NOT NULL、DEFAULT）。
事务管理和并发控制
确定事务隔离级别，避免并发引发的死锁问题。
安全性与备份策略
用户权限划分。
制定定期备份机制。
物理数据库设计方案。

5. 数据库实施

目标：将物理设计方案落实到实际数据库管理系统中。
任务：
数据库的创建（表结构、索引、视图、触发器等）。
导入初始数据。
数据库功能的测试（如性能测试、查询测试、事务测试）。

6. 运行与维护

目标：保障数据库的正常运行并持续优化。
任务：
性能监控
查询慢日志、锁等待时间、磁盘I/O等。
数据备份与恢复
实现全量备份、增量备份，制定恢复流程。
安全管理
持续更新权限管理。
定期优化
包括索引重建、表碎片清理等。

数据库设计相关工具

ER图设计工具：
PowerDesigner、ERWin、Visio、DB Designer。
数据库建模工具：
MySQL Workbench、Navicat、Oracle Data Modeler。

数据库设计的常见问题与优化

常见问题

冗余设计：未遵循规范化，导致数据重复。
查询效率低：未合理设计索引或字段类型。
事务并发冲突：事务隔离级别不当。
数据库膨胀：未定期清理历史数据。

数据库优化方法

索引优化：
选择合适的索引字段，避免过多索引。
分区优化：
对大表进行分区存储，减少单次I/O量。
查询优化：
避免全表扫描；重构复杂的嵌套查询。
缓存机制：
使用缓存（如Redis）减轻数据库压力。

重点知识与备考建议

高频考点：

关系模型与范式转换。
ER图设计及其到关系模型的映射。
数据库设计的各个阶段任务及目标。
索引、分区、规范化与反规范化。

备考建议：

熟悉理论知识，尤其是范式和设计步骤。
多练习ER图设计和SQL语句实现数据库设计。
掌握数据库优化技巧，理解其原理和适用场景。

5. 数据库管理

一、数据库管理的概述

数据库管理的目标
确保数据库的 完整性、可靠性、安全性。
提高数据库系统的 性能与可用性。
满足用户 多样化的数据需求。
数据库管理员（DBA）的职责
数据库的设计与部署：包括数据模型设计、表结构设计等。
数据库的性能优化：索引优化、查询优化、存储优化。
数据库的安全管理：权限分配、数据加密等。
数据库的备份与恢复：制定并执行备份策略，确保数据可恢复性。
日常维护：监控数据库运行状态，处理系统日志与异常情况。

二、数据库管理的主要任务

1. 数据库的安全管理

目标：保护数据库不被未授权访问或恶意篡改。
方法：
用户权限管理：
使用角色与用户分组分配权限。
最小权限原则：只分配用户所需的最低权限。
常见权限：SELECT、INSERT、UPDATE、DELETE、EXECUTE。
数据加密：
使用透明数据加密（TDE）保护静态数据。
数据传输过程中采用SSL/TLS协议。
安全审计：
定期审查访问记录和异常行为。
防SQL注入：
使用参数化查询和预编译语句，避免直接拼接SQL。
2. 数据库的备份与恢复
备份的类型：
全量备份：
备份整个数据库；适合定期备份。
增量备份：
仅备份上次备份后修改的数据；节省时间与空间。
差异备份：
备份上次全量备份后所有的变更数据。
日志备份：
备份事务日志，支持故障恢复到任意时间点。
恢复的策略：
完全恢复：
还原全量备份及所有增量/日志。
时间点恢复：
利用日志将数据库恢复到特定时间。
部分恢复：
只恢复特定表或文件。
备份的原则：
备份多样化（本地、远程、云备份）。
定期测试备份的有效性。
3. 数据库的性能管理
性能优化的目标：
提高查询效率，减少资源消耗。
确保高并发情况下的稳定性。
性能管理的任务：
索引优化：
创建合适的索引（如单列索引、复合索引、全文索引）。
避免过多索引，防止更新性能下降。
查询优化：
避免全表扫描，使用条件过滤、联接优化。
优化SQL语句（如减少嵌套查询）。
存储优化：
定期整理碎片，压缩大表数据。
分区管理：
水平分区（按行划分数据）和垂直分区（按列划分数据）。
缓存优化：
使用内存缓存（如Redis）减少数据库负载。
性能监控：
查询慢日志、锁等待、CPU/IO利用率。

4. 数据库的并发控制

并发问题：

脏读：读取到未提交的事务数据。
不可重复读：同一事务中多次查询结果不一致。
幻读：同一事务中查询新增或删除的数据导致结果变化。
事务的ACID特性：
原子性（Atomicity）：事务不可分割，要么全部完成要么全部回滚。
一致性（Consistency）：数据在事务前后保持一致。
隔离性（Isolation）：不同事务之间相互独立。
持久性（Durability）：提交的事务数据永久保存。
隔离级别（从低到高）：
读未提交（Read Uncommitted）：允许脏读。
读已提交（Read Committed）：防止脏读，但可能出现不可重复读。
可重复读（Repeatable Read）：防止不可重复读，但可能出现幻读。
序列化（Serializable）：防止所有并发问题，但性能较低。
并发控制方法：
锁机制：
排他锁（写锁）、共享锁（读锁）。
多版本并发控制（MVCC）：
保留数据的多个版本供并发事务读取。
5. 数据库的故障管理
故障类型：
硬件故障（磁盘损坏、电源故障）。
软件故障（系统崩溃、程序异常）。
数据故障（误操作、数据丢失）。
恢复机制：
事务日志：
通过日志进行回滚或重做操作。
故障转移：
使用主备数据库或分布式架构实现高可用性。

三、数据库管理工具与技术

数据库监控工具

开源工具：
Prometheus、Zabbix（监控性能与资源使用）。
商业工具：
SolarWinds、Quest Spotlight（深入分析数据库运行情况）。

自动化管理技术

数据库脚本自动化：
使用脚本（如Python、Shell）实现日常任务的自动化。
自动备份与恢复：
配置自动化备份工具（如MySQLdump、Oracle RMAN）。

数据库安全工具
防火墙、入侵检测工具（如WAF）。
数据加密工具（如PGP、TDE）。

四、数据库管理常见问题与解决方案

常见问题：

性能问题：
查询速度慢，导致系统响应延迟。
高并发导致死锁。
数据丢失：
未及时备份。
数据误删或写入错误。
安全问题：
SQL注入攻击。
数据泄露。

解决方案：

定期进行性能分析，调整索引和优化查询。
实现容灾和冗余，增强数据可用性。
部署数据库防护措施（如防火墙和入侵检测系统）。

五、重点知识与备考建议

高频考点

权限管理与安全策略。
数据库备份与恢复的原理及策略。
事务的ACID特性与隔离级别。
数据库优化技术（索引、查询、分区、缓存等）。
数据库监控与故障恢复的流程。

6. 数据库优化

一、数据库优化概述

1. 数据库优化的目标

提高性能： 缩短查询时间，提升响应速度。
降低资源消耗： 减少CPU、内存、磁盘I/O占用。
增强系统稳定性： 适应高并发场景，减少死锁和崩溃概率。
改善用户体验： 确保系统在高负载下仍然快速响应。

2. 优化的层次

硬件层优化：
配置高性能的服务器和存储设备。
数据库结构优化：
优化数据库设计、表结构、索引等。
SQL语句优化：
改写SQL语句，避免低效查询。
应用层优化：
调整业务逻辑、优化应用程序和缓存机制。

二、数据库结构优化

1. 数据库表结构优化
范式设计与反范式设计：
范式设计： 提高数据的独立性，减少冗余。
反范式设计： 为了性能牺牲部分规范化（如适当冗余字段）。

考点：知道何时采用范式或反范式设计。
字段类型选择：
使用精确的数据类型，避免占用多余存储空间。
优先使用整型或枚举代替字符串（如性别字段用1/0代替'男'/'女'）。
垂直分区与水平分区：
垂直分区： 将一张表的字段拆分到多个表中（适合减少宽表的字段冗余）。
水平分区：按行拆分，分布到多个存储节点（适合大数据量场景）。
数据分区和分表：
针对大数据表，通过分区减少全表扫描。
使用分表（Sharding）提升分布式环境的性能。

2. 索引优化

索引的作用：
提高查询速度，但会降低写入性能。
常见索引类型：
单列索引：基于单一字段建立。
复合索引：基于多个字段联合建立。
全文索引：针对文本字段的关键词搜索。
唯一索引：确保字段值唯一。
索引优化原则：
优化查询中高频使用的字段。
避免冗余索引（多个索引字段重叠）。
不对频繁更新的字段创建索引。
使用场景：
WHERE、GROUP BY、ORDER BY、JOIN 中出现的字段适合索引。
前缀匹配适合B-Tree索引（如LIKE 'abc%'）。

三、SQL语句优化

1. 查询优化基本原则

避免全表扫描：
在查询条件字段上加索引。
使用WHERE条件限制返回数据范围。
减少子查询：
替换为JOIN操作，减少重复扫描。
示例：SELECT * FROM A WHERE A.id IN (SELECT id FROM B) 优化为 SELECT A.* FROM A JOIN B ON A.id = B.id。
避免SELECT ：
明确指定需要的字段，减少返回的数据量。
限制返回结果：
使用LIMIT或TOP限制返回的记录数量。

2. 高效SQL书写技巧

避免函数操作导致索引失效：
示例：WHERE YEAR(create_time) = 2023，应改为WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'。
避免隐式类型转换：
示例：WHERE id = '123'（id为整数）会导致全表扫描，应改为WHERE id = 123。
优化JOIN操作：
使用小表驱动大表（控制JOIN顺序）。
确保JOIN字段有索引。
利用分组与排序优化：
在GROUP BY或ORDER BY字段上建立索引。

3. 查询优化器与执行计划

查询优化器：
负责选择最优的查询执行路径。
考点：理解优化器的作用及优化方式。
执行计划分析：
使用EXPLAIN命令查看查询执行计划。
关注：
是否存在全表扫描（type = ALL）。
是否使用索引（key字段显示索引名称）。
读取行数（rows字段）是否较高。

四、性能优化技术

1. 缓存优化

缓存的作用：
减少数据库压力，加快响应速度。
实现方式：
内存缓存：使用Redis、Memcached等。
应用程序级缓存：缓存查询结果到应用层。
查询结果缓存：数据库本身支持的结果集缓存。
缓存失效策略：
基于时间（如TTL）。
数据更新时主动清除。
2. 分布式架构与负载均衡
分布式数据库：
将数据分布到多个节点存储，提升并发处理能力。
负载均衡：
利用代理工具（如MySQL Proxy）实现读写分离。
主从复制（Master-Slave）：主库写入，从库读取。
3. 数据压缩与归档
数据压缩：
对存储数据进行压缩（如MySQL的InnoDB压缩表）。
数据归档：
将历史数据移至归档库，减少主库数据量。
4. 性能监控与分析
性能监控指标：
查询执行时间。
CPU、内存、磁盘I/O的使用情况。
数据库连接池使用情况。
监控工具：
数据库自带工具（如MySQL Performance Schema）。
第三方工具（如Zabbix、Prometheus）。

五、事务优化

1. 控制事务范围
减少事务锁定时间，尽量缩小事务范围。
避免长时间未提交的事务。
2. 使用合适的隔离级别
根据实际需要选择隔离级别：
高性能需求：使用READ COMMITTED或REPEATABLE READ。
高一致性需求：使用SERIALIZABLE。
3. 避免死锁
控制锁的粒度（避免大范围锁定）。
遵循固定的资源访问顺序。

六、常见优化问题及解决方案

1. 常见问题
查询缓慢，执行时间过长。
数据库连接数不足。
系统资源耗尽（如高CPU、I/O等待）。
2. 解决方案

查询慢：
优化SQL语句，添加索引。
使用缓存减少重复查询。
连接数不足：
优化连接池配置（增加连接数上限）。
检查是否存在长时间未关闭的连接。
系统资源耗尽：
监控资源使用，调整硬件配置。
分布式架构扩展系统容量。

七、备考建议

1. 高频考点
索引优化（单列、复合索引的应用场景）。
SQL语句优化原则和具体方法。
分区和分表的设计思路。
性能监控与常见指标的分析方法。
2. 学习策略

理解核心原理：
理解索引失效的场景和解决方法。
熟悉缓存和分布式架构的优势。
实践操作：
在测试环境中使用EXPLAIN分析SQL语句的执行计划。
模拟大数据量表，尝试使用分区、分表优化查询性能。
总结问题场景：
结合实际案例，梳理常见性能问题的解决思路。

7. NoSQL数据库

一、NoSQL数据库概述

NoSQL的定义
NoSQL（Not Only SQL）是指非关系型数据库，不遵循传统的关系型数据库模型。
主要设计目标是解决关系型数据库在高并发、大数据量、灵活性和分布式扩展方面的不足。
NoSQL与关系型数据库的区别

特性	NoSQL数据库	关系型数据库
数据存储模型	键值对、文档、列族、图数据等	表、行、列
数据结构灵活性	高灵活性，无需固定模式	需要定义固定表结构
扩展性	水平扩展（通过增加节点扩展）	垂直扩展（升级硬件设备）
查询语言	无标准化查询语言，多样化API	SQL
一致性模型	最终一致性（CAP原则）	强一致性（ACID模型）