数据库的三级模式结构、E-R模型、关系代数、范式理论、SQL体系、事务管理、并发控制、恢复机制、新型数据库架构及查询优化

数据库的三级模式结构、E-R模型、关系代数、范式理论、SQL体系、事务管理、并发控制、恢复机制、新型数据库架构及查询优化是数据库系统核心知识体系。以下按问题顺序逐一详述一、数据库系统的三级模式结构ANSI/SPARC架构该结构实现数据抽象与逻辑/物理独立性由外模式、概念模式概念模型、内模式构成通过两级映射连接概念模型Conceptual Schema是数据库全局逻辑结构的抽象描述面向全体用户和DBA独立于硬件与具体DBMS。它定义实体、属性、联系及其约束如完整性、基数不涉及存储细节或访问路径。典型工具是E-R模型。其作用是统一数据语义、支持需求分析、作为模式设计基础。外模式External Schema / View是概念模式的逻辑子集面向特定用户或应用描述用户可见的数据视图View。一个数据库可有多个外模式如财务视图、人事视图。外模式屏蔽无关数据和安全敏感字段如SALARY对非HR用户不可见并通过外模式/概念模式映射保证逻辑独立性修改概念模式时若映射调整得当外模式可不变。内模式Internal Schema描述数据在存储介质上的物理组织方式包括存储结构B树、哈希、堆文件、索引策略、存取路径、压缩加密、块大小等。一个数据库仅有一个内模式。通过概念模式/内模式映射实现物理独立性更换存储设备或优化索引时概念模式无需改动。✅ 两级映射保障了逻辑独立性应用程序基于外模式编写概念模式变化不影响外模式只要映射重定义物理独立性内模式调整如加索引、换存储引擎不影响概念/外模式。二、实体-关系模型E-R Model与E-R图绘制E-R模型是概念建模最常用工具用于需求分析阶段构建信息世界抽象。基本元素元素图形表示说明实体Entity矩形框客观存在且可区分的对象如Student、Course。弱实体用双矩形依赖强实体存在如OrderItem依赖Order。属性Attribute椭圆描述实体/联系的特征。主键属性下划线如StuID多值属性用双椭圆如Phone复合属性用树状展开如Address {Street, City, ZIP}派生属性用虚线椭圆如Age ← derived from BirthDate。联系Relationship菱形实体间的关联如Enrolls联系Student与Course。联系本身可有属性如Grade属于Enrolls。基数约束Cardinality边上标注1, N, M:N等或Crow’s Foot符号描述联系中实体参与的数量限制•1:1一对一如Person–Passport•1:N一对多如Department–Employee•M:N多对多如Student–Course需转换为关联实体或新关系表。E-R图绘制步骤识别实体从需求文档提取名词性、可独立存在的对象定义属性为每个实体确定关键属性标出主键发现联系分析动词性关系“学生选修课程”→Enrolls明确联系类型与基数添加联系属性如“选课时间”“成绩”属于Enrolls处理多值/复合/派生属性标注弱实体与标识性联系用双线菱形消除冗余合并同义实体验证完整性所有实体是否被联系覆盖规范化检查避免属性跨实体重复如DeptName不应同时出现在Employee和Department中。✅ 示例片段Student(StuID, Name, BirthDate)——Enrolls(Grade, EnrollTime)——Course(CourseID, Title)其中Enrolls为M:N联系Grade为其属性StuID和CourseID为主键联系隐含复合主键(StuID, CourseID)。三、关系代数基本运算集合论专门关系运算关系代数是SQL的理论基础操作对象为关系表结果仍为关系闭合性。运算符号含义说明选择Selectionσ条件®从关系R中选取满足条件的元组行条件为原子谓词组合如σsubSalary5000 ∧ DeptCS/sub(Employee)不改变属性集。投影ProjectionπA₁,…,Aₖ®从R中提取指定属性列列自动去重如πsubName,Salary/sub(Employee)若需保留重复需注明标准RA默认去重。并UnionR ∪ S兼容关系R与S的元组合并去重R、S必须同目属性数相同且对应属性可比union-compatible。差DifferenceR − S属于R但不属于S的元组同union兼容性要求。笛卡尔积Cartesian ProductR × SR每行与S每行拼接成新元组结果属性数 R.attr# S.attr#易产生冗余常与选择联用即θ-连接。连接JoinR ⋈条件S笛卡尔积后按条件筛选的子集•θ-连接条件为一般比较R.A op S.B•等值连接op为•自然连接⋈自动匹配同名同域属性结果中重复列只保留一份隐含等值投影。除DivisionR ÷ S查询R中“包含S全部元素”的元组形式化R(A,B) ÷ S(B)返回所有a∈A使得{a}×S ⊆ R。常用于“查找选修了所有课程的学生”类查询。✅ 除法直观理解设SC(Sno,Cno)记录学生选课C(Cno)为所有课程。则πsubSno,Cno/sub(SC) ÷ πsubCno/sub(C) 选修了全部课程的学生学号。四、函数依赖与规范化理论范式函数依赖Functional Dependency, FD设关系模式R(U)X,Y⊆U。若对R任意两个元组t₁,t₂当t₁[X]t₂[X] ⇒ t₁[Y]t₂[Y]则称X函数决定Y记作X → Y。平凡FDY⊆X恒成立完全函数依赖X→Y且∀X’⊂X, X’↛Y部分函数依赖X→Y但存在X’⊂X使X’→Y传递函数依赖X→Y, Y↛X, Y→Z ⇒ X→ZZ∉XY。范式演进消除各类数据异常范式定义消除的问题示例问题1NF关系中每个属性都是不可再分的原子值域中元素表中无重复组、无数组/集合属性Student(Sno, Name, Courses{C1,C2})❌ → 拆为SC(Sno,Cno)✅2NF在1NF基础上所有非主属性完全函数依赖于任一候选键消除部分依赖导致的插入/删除异常SC(Sno,Cno,Grade,Dept,DeptHead)候选键(Sno,Cno)但Dept→DeptHead⇒DeptHead部分依赖于Sno❌应拆出Dept(Dept,DeptHead)。3NF在2NF基础上所有非主属性不传递依赖于候选键消除传递依赖导致的更新异常同上例Sno→Dept→DeptHead⇒DeptHead传递依赖于Sno❌3NF要求DeptHead仅依赖于Dept其所在关系的码。BCNFBoyce-Codd NF更强于3NF对所有FD X→YX必为超键即X包含候选键消除主属性对非码的依赖3NF未覆盖R(A,B,C), F{A→B, B→C}候选键A但B→C中B非超键 ⇒ 不满足BCNF应分解为R1(A,B), R2(B,C)。✅ 规范化过程1NF → 消除部分依赖→2NF → 消除传递依赖→3NF → 检查BCNF→必要时分解。五、SQL语言组成与复杂查询示例SQL是关系数据库的标准语言按功能分为类别全称功能典型语句说明DDLData Definition Language定义/修改数据库结构CREATE TABLE,ALTER TABLE,DROP INDEX,TRUNCATE操作对象模式、表、索引、视图隐式提交不可回滚。DMLData Manipulation Language操作数据增删改INSERT,UPDATE,DELETE需显式事务控制BEGIN/COMMIT/ROLLBACKUPDATE/DELETE可带WHERE子句。DQLData Query Language查询数据核心SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...SELECT是唯一DQL语句支持嵌套子查询、连接、集合运算UNION/INTERSECT/EXCEPT。DCLData Control Language控制访问权限GRANT SELECT ON emp TO user1,REVOKE UPDATE ON dept FROM user2管理用户权限SELECT/INSERT/UPDATE/DELETE/ALL PRIVILEGES。复杂查询示例MySQL语法-- 查询每个部门平均工资 5000 的部门名、员工数、平均工资并按平均工资降序SELECTd.DeptName,COUNT(e.EmpID)ASEmpCount,ROUND(AVG(e.Salary),2)ASAvgSalaryFROMDepartment dINNERJOINEmployee eONd.DeptIDe.DeptIDGROUPBYd.DeptID,d.DeptNameHAVINGAVG(e.Salary)5000ORDERBYAvgSalaryDESC;✅ 关键点JOIN连接多表GROUP BY分组聚合HAVING过滤分组结果WHERE过滤行HAVING过滤组ROUND()数值处理别名增强可读性。六、事务ACID特性事务Transaction是数据库执行的最小逻辑工作单元必须满足特性含义实现机制举例原子性Atomicity事务要么全执行要么全不执行不可分割通过日志undo log失败时回滚到事务开始前状态COMMIT前所有修改暂存。一致性Consistency事务执行前后数据库从一个有效状态转入另一个有效状态满足所有完整性约束由应用逻辑 DBMS约束主键、外键、CHECK共同保证ACID中唯一需应用参与的特性。隔离性Isolation并发执行的事务互不干扰效果如同串行执行通过并发控制机制封锁、MVCC实现不同隔离级别Read Uncommitted → Serializable。持久性Durability事务一旦提交其结果永久保存即使系统崩溃依赖日志redo log提交前日志已刷盘崩溃后通过重做日志恢复。✅ ACID是事务的本质属性而非实现目标现代系统如PostgreSQL通过WALWrite-Ahead Logging MVCC协同保障。七、并发控制封锁协议与两段锁协议2PL封锁Locking基本类型共享锁S锁 / Read Lock允许多事务同时读但阻止写排他锁X锁 / Write Lock独占阻止其他任何锁S/X意向锁Intention Locks表级锁表明事务将在某行加S/X锁如IS,IX避免全表扫描判断冲突。封锁协议一级封锁协议事务修改数据前加X锁事务结束commit/rollback释放 → 防止丢失修改二级封锁协议一级 读数据前加S锁读完立即释放 → 防止丢失修改 读脏数据三级封锁协议二级 读数据前加S锁事务结束才释放 → 防止丢失修改 读脏数据 不可重复读。两段锁协议Two-Phase Locking, 2PL增长阶段Growing Phase事务可获得锁但不能释放任何锁收缩阶段Shrinking Phase事务可释放锁但不能再申请新锁严格2PLX锁持续到事务结束防止级联回滚强2PL所有锁S/X均保持到事务结束支持可串行化调度。✅ 2PL是可串行化的充分条件非必要但可能导致死锁需检测/超时机制。八、数据库恢复技术日志与检查点日志文件Log File记录事务对数据库的所有修改操作格式T_id, operation, item, old_value, new_value作用▪事务故障恢复UNDO未完成事务利用old_value▪系统故障恢复REDO已提交但未写盘事务利用new_value▪介质故障恢复结合备份 日志重放。检查点Checkpoint机制定期在日志中插入checkpoint L记录其中L为当时活动事务列表同时将缓冲区中所有已提交事务的修改强制写入磁盘flush恢复时优化只需从最近检查点开始扫描日志而非日志开头对检查点前已提交事务无需REDO对检查点后活动事务需UNDO/REDO判断。✅ WAL原则日志必须在数据页写盘之前写入磁盘fsync确保崩溃后可重放。九、数据仓库DW与分布式数据库DDB对比传统数据库维度传统数据库OLTP数据仓库OLAP分布式数据库DDB目标支持日常业务操作增删改查支持复杂分析、决策支持提升扩展性、可用性、容错性数据特征当前值、细节数据、高度规范化3NF历史快照、汇总数据、星型/雪花模型反规范化数据物理分布于多节点逻辑统一查询模式简单、短事务、高并发、低延迟ms级复杂、长查询、低并发、高吞吐秒~分钟查询可能跨节点需分布式执行计划更新频率高频实时更新CRUD批量加载ETL/ELT极少更新支持分布式事务如2PC、TCC但性能折衷关键技术B树索引、行存储、ACID列存储、位图索引、物化视图、MPP架构分片Sharding、复制Replication、一致性协议Paxos/Raft、分布式事务协调器代表系统MySQL, PostgreSQL, OracleSnowflake, Redshift, ClickHouseTiDB, CockroachDB, YugabyteDB✅ 核心区别DW是主题导向、集成、时变、非易失的数据集合DDB是物理分布、逻辑集中、自治与协作并存的数据库系统。十、SQL查询优化与索引失效场景查询优化方法执行计划分析使用EXPLAINMySQL/EXPLAIN ANALYZEPostgreSQL查看索引使用、扫描行数、连接算法Nested Loop/Hash/Merge Join索引优化▪ 为WHERE、JOIN ON、ORDER BY、GROUP BY字段建索引▪ 使用复合索引遵循最左前缀原则INDEX(a,b,c)可加速WHERE a1 AND b2但无法加速WHERE b2▪ 考虑覆盖索引索引包含查询所有字段避免回表SQL重写▪ 用EXISTS替代IN尤其子查询返回NULL时▪ 避免SELECT *只取必要字段▪ 将OR条件转为UNION若各分支可走不同索引统计信息更新定期ANALYZE TABLE使优化器准确估算行数分区表按时间/地域分区减少扫描范围如PARTITION BY RANGE (order_date)。索引失效常见场景对索引列使用函数或表达式WHERE YEAR(create_time) 2023→ 应改用范围查询create_time BETWEEN 2023-01-01 AND 2023-12-31隐式类型转换WHERE phone 13812345678phone为VARCHAR→ 字符串转数字索引失效LIKE以通配符开头WHERE name LIKE %abc→ 无法使用B树索引abc%可OR连接非全索引字段WHERE a1 OR b2若只有a索引则b2部分全表扫描NOT、!、操作符通常导致索引失效除非是索引覆盖且优化器选择复合索引未用最左列INDEX(a,b)WHERE b2无法使用索引列参与计算WHERE price tax 100→ 应改写为WHERE price 100 - tax若tax固定或建函数索引。✅ 高级技巧MySQL 8.0支持函数索引CREATE INDEX idx_year ON t ((YEAR(create_time)))可解决场景1。