简介数据库系统原理实验是计算机科学与技术、软件工程专业的重要基础课程。这份zzu数据库实验报告书完整收录了九个循序渐进的实验从认识DBMS、创建数据库表与索引到交互式SQL、视图、完整性/安全性控制再到事务与并发、备份恢复及JDBC连接数据库覆盖openGauss环境下的核心数据库操作。报告以文档形式呈现含1个docx文件压缩包大小3.23MB当前已有1522人学习下载。每个实验均包含实验目的、操作过程、遇到的问题及解决方法并配有相应截图与代码示例可帮助读者快速理解数据库建模、SQL命令、权限管理和并发控制等关键知识点也可作为期末复习、课程设计或备考的实验参考模板。无论是初学者入门还是进阶巩固这份报告书都能提供系统而直观的实践指引。1. 九次实验隐藏的三条主线为什么入门要先认识 DBMS数据库原理实验最反直觉的一点九次实验里最容易被当成“走过场”的第一次恰恰决定了后面八次能不能真正看懂。第一次实验不写业务 SQL而是安装 DBMS、启动服务、用 gsql 连上默认数据库 postgres、敲几条 \l、\d 命令去翻系统表。为什么这样安排因为 SQL 只是数据库这棵大树的叶子真正的根是 DBMS 怎么管理存储、怎么控制权限、怎么保证提交了的事务不丢数据。这份实验报告书把这些问题拆成了九次上机对象管理、交互式 SQL、视图、完整性、安全性、事务与并发、备份恢复最后用 JDBC 把数据库接回应用层。搞懂这条主线数据库才会从“黑匣子”变成一台看得见机制的机器。2. 对象与存储数据库、schema、表、索引在磁盘上到底是什么2.1 从 CREATE TABLESPACE 看“目录—文件—表空间”的映射实验二第一件事不是建表而是建表空间CREATE TABLESPACE tpcds_local RELATIVE LOCATION tablespace/tablespace_1; CREATE DATABASE db_sc WITH TABLESPACE tpcds_local; \c db_school第一句在数据库主节点上划了一块物理目录第二句把数据库放进了这块目录第三句切换过去开始建表。表空间这个词听起来抽象实际对应文件系统里的一个目录。RELATIVE LOCATION指定的是相对数据目录的路径而且要求目标目录为空——因为数据库系统要在里面自己管理文件布局。一个表空间可以拥有多个数据文件一个数据文件只能属于一个表空间这种层级关系决定了数据最终落在哪块磁盘上。很多人建表时根本不指定表空间于是所有表都落到数据库默认关联的表空间里。实验里把数据库和表空间显式绑定就是要你看见这条映射链表空间指向物理目录数据库指向表空间表的数据文件再放进数据库所在的目录。表是逻辑对象但每一行数据最终都要落到某个文件偏移上这个“逻辑对象到物理文件”的映射就是 DBMS 与单纯的数据文件最本质的区别。2.2 schema 的双重含义逻辑命名空间与物理容器实验书里专门辨析了 schema 的两种含义。概念上的 schema 是一组 DDL 语句集描述整个数据库的结构也就是教材里的“逻辑模式”物理上的 schema 是数据库内的一个命名空间包含一组表、视图、存储过程等对象。学生在实验里常犯的错是把 schema 当成数据库的别名——它们其实是两级不同的命名空间一个数据库下可以创建多个 schema每个 schema 下再挂表和视图。CREATE SCHEMA book_eg; CREATE TABLE book_eg.Teachers(Tno CHAR(20) PRIMARY KEY); DROP SCHEMA book_eg CASCADE;最后一句CASCADE是重点。不带级联时如果 schema 里还有对象删除会直接报错加上CASCADE表示把 schema 里的表、视图、索引一起删掉。这个设计是为了防止你误删一个还有数据的命名空间。我在做实验时习惯先查\dn确认 schema 列表再动手因为DROP SCHEMA不像DROP TABLE那样有回收站删完没有后悔药。物理 schema 为什么值得单独存在因为它给了权限控制一个中间层。你可以把一组表放进book_eg然后只把这个 schema 的访问权授给某个用户不用逐个表授权。这比把对象全堆在公共命名空间里干净得多也是实验二要把模式单独立项的用意。2.3 主键、索引与系统目录为什么 \d 能看到这么多信息实验二建完表后用\d查表属性、用\di查索引这些元命令的结果来自系统目录表。openGauss 里有几张核心的目录表pg_database存数据库列表pg_tablespace存表空间pg_user存用户pg_roles存角色pg_authid存认证信息。执行\l本质上就是查询pg_database执行\db就是查pg_tablespace——元命令只是把这些查询包装成了更友好的输出。CREATE TABLE Departments ( Dno CHAR(20) PRIMARY KEY, Dname CHAR(20), Dheadno CHAR(20), FOREIGN KEY (Dheadno) REFERENCES Teachers(Tno) ); CREATE INDEX Student_Dept ON Students(Dno);注意外键Dheadno引用Teachers(Tno)但外键不会自动在主键列上建索引。实验里建了Student_Dept索引去加速按院系查学生本质是在 Students 表的 Dno 列上建一棵 B 树。索引是一个辅助数据结构它不影响表里的数据排列只提供一条快速定位记录的路径。主键列会由 DBMS 自动建唯一索引普通列要提速必须手动建索引这就是\di能同时看到“系统建的”和“自己建的”两类索引的原因。系统目录本身也是表这意味着你可以用 SQL 去查数据库的元数据。理解这一点才算真正看懂\d、\dt、\di这些命令——它们不是在黑匣子外面敲咒语而是在查询一组描述数据库自身的表。数据库为什么能“认识”自己就是靠这套自描述的目录体系。对象层级物理对应逻辑作用表空间数据目录下的一个目录决定数据文件存放位置数据库表空间中划分的逻辑空间隔离不同业务的全部数据schema数据库内的命名空间组织表、视图等对象可独立授权表磁盘上的数据文件存储具体行数据索引独立的辅助文件加速检索不改变数据本身3. SQL 执行链路一条查询在 openGauss 里是怎么跑完的3.1 gsql 会话从端口 26000 到后台进程实验一要求先启动服务再连接命令本身很简单但背后是一整套进程模型su - omm gs_om -t start gsql -d postgres -p 26000 -rsu - omm切换数据库安装用户gs_om -t start启动集群gsql -d postgres -p 26000连接默认数据库。-r开启行编辑。gsql 连上后主节点上的监听进程接受 TCP 连接为这个会话派生一个独立的数据库后端进程然后进入“读命令—执行—返回结果”的循环。每个客户端连接都对应一个独立进程而不是线程这是 PG 体系一脉相承的设计一个后端进程崩溃不会拖垮整个数据库隔离性更好代价是连接开销大。这个模型解释了为什么连接数不能无限上涨——每个会话都要消耗进程内存和文件描述符。实验里用gs_om -t start启动的是整个集群如果之前有残留进程没清理干净启动会报端口占用或状态不一致最常见的处理是先看状态再启动别跳过检查直接敲命令。3.2 解析、重写、优化、执行SQL 不是“直接跑”的一条交互式 SQL 从键盘敲进去到最后返回结果集中间要过四道工序。词法分析把字符串切成 token语法分析按 SQL 文法生成语法树语义分析校验表名、列名、类型是否合法如果是视图还要在这里做查询重写把视图展开成基表查询然后优化器根据统计信息生成执行计划最后执行器按计划访问存储引擎。实验三里大量练习增删改查却看不到这些内部过程——因为 DBMS 把复杂度藏起来了。理解这条链路最大的价值在于很多 SQL 性能问题不是“写错了”而是“优化器没选对路”。比如对一列建了索引但查询条件是WHERE Dname LIKE %计算机%前导通配符让 B 树无法定位起点优化器只能选顺序扫描。这不是索引没用而是用错了场景。验证这条链路最直接的手段是看执行计划EXPLAIN SELECT * FROM Students WHERE Sno 2021001;EXPLAIN不真正执行查询只输出优化器选中的计划节点。你会看到是顺序扫描还是索引扫描、预估行数、预估代价。代价估算依赖表的统计信息如果刚导入了大量数据却没有做统计更新优化器会基于过期的行数做判断选错计划。这就是为什么数据库管理员在大批量数据变更后要重新收集统计信息——优化器是“用数据做决策”的数据不准决策就偏。3.3 元命令的本质gsql 客户端做了什么实验一里大量使用了\l、\dt、\d、\di、\dn、\db、\h这些反斜杠命令。它们不是 SQL而是 gsql 客户端的元命令在客户端本地被解析再翻译成对系统目录的 SQL 查询发给服务器。\d tablename展示表的属性、索引、约束其实是对pg_attribute、pg_index、pg_constraint等目录表的关联查询。这解释了为什么\d系列命令能“看到”结构——不是服务器有专门的“看结构”接口而是结构信息本来就存在系统表里。\h列出所有支持的 SQL 语法本质是客户端内置的语法帮助文本。明白这一点后你就不会再把元命令和 SQL 混为一谈SQL 发给服务器执行元命令在客户端立即处理。遇到\d没反应时先检查是不是不小心进了 SQL 的字符串字面量里——元命令只有在 gsql 的“非查询输入状态”才被识别这是个很容易踩的小坑。4. 完整性与安全性约束、角色、视图如何在底层生效4.1 五类约束的存储与检查时机实验五的完整性控制把约束拆成五类NOT NULL、UNIQUE、PRIMARY KEY、CHECK、FOREIGN KEY。约束不是写在表定义里就完事DBMS 要把它们存进系统目录并在每次数据变更时强制检查。CREATE TABLE Students ( Sno CHAR(20) PRIMARY KEY, Sname CHAR(20) NOT NULL, Sex CHAR(20) CHECK(Sex男 OR Sex女), Birthday DATE, Dno CHAR(20), FOREIGN KEY (Dno) REFERENCES Departments(Dno) );PRIMARY KEY在目录里登记主键信息的同时自动创建一个唯一索引NOT NULL和CHECK在 INSERT 和 UPDATE 时逐行验证条件外键的检查发生在子表写入或主表被删除时。主表的行被引用着执行 DELETE 会触发参照完整性检查——要么拒绝要么级联取决于外键定义的ON DELETE动作。列级约束和表级约束的区别也在这一章体现。涉及单列的约束可以跟在列定义后面涉及多列的约束必须用表级写法因为约束的作用范围超出了单列。一个经典错误是以为加了约束数据库就永远“干净”了——实际上约束只能挡住不合规则的数据写入对于已经存在的脏数据添加约束时数据库要先做全表校验校验失败就整条语句报错。所以给大表加CHECK约束前最好先手动查一遍有没有存量违规数据。外键的另一个细节值得注意外键列本身不会自动建索引。实验里 Departments 的 Dno 是主键Students 的 Dno 是外键删除或更新 Departments 的一行时DBMS 要在 Students 里找所有引用它的行没有索引就只能扫全表。生产环境里外键列几乎总是需要手动补一个索引这是完整性控制实验不会直接讲、但真实系统一定会遇到的性能问题。4.2 用户、角色与权限为什么角色是权限的集合实验六的安全性控制把用户和角色分开处理。实验书里的辨析很到位用户是具体账户个体角色是某一类权限的集合。数据库里新建一个用户本质是在pg_authid里登记一条认证记录而角色对应一组权限可以分配给任意用户。CREATE USER student_user IDENTIFIED BY 密码占位; CREATE ROLE read_only_role; GRANT SELECT ON Students TO read_only_role; GRANT read_only_role TO student_user;这样做的好处是权限的变更成本降到最低新来一个学生直接把read_only_role授给他这个学生转岗了撤销角色即可不用逐表回收权限。底层实现上权限检查发生在执行器启动之前DBMS 先查角色的成员关系再判断当前用户是否有目标表的访问权两处都是系统目录查询加上内存缓存。权限的粒度是分层的连接数据库需要 CONNECT 权限查表需要 SELECT 权限创建表需要所在 schema 的 CREATE 权限。实验里只授SELECT不授INSERT用户能读不能写这就是安全性控制的核心思想——最小权限原则。另一个容易忽略的点GRANT只能授予你本身就拥有的权限普通用户不能转授自己都没有的权限这条规则在系统目录里同样有记录。4.3 视图的底层查询重写与安全边界实验四创建视图目的是理解数据抽象和查询复用。视图在 openGauss 里默认不物理存储数据它只是一条命了名的 SQL 语句存在系统目录里。查询视图时优化器在重写阶段把视图定义展开成基表查询之后走和直接查基表完全相同的优化和执行路径。CREATE VIEW student_sc AS SELECT S.Sno, Sname, Cname, Grade FROM SC JOIN Students S ON SC.Sno S.Sno JOIN Courses C ON SC.Cno C.Cno;视图能当安全边界用把基表里不该暴露的列挡住只把视图的 SELECT 权限授出去。比如 Students 表里有生日、院系等字段你不想让别人看到就把视图只包含学号和姓名然后授视图权限。这样别人能查视图却查不到基表的其他列——权限检查发生在访问视图时视图内部的基表访问由视图定义者的权限决定普通用户不需要直接拥有基表的权限。视图还有一个容易踩的认知误区以为视图能提升查询性能。实际上普通视图不会加速查询它只是简化书写每次查询视图底层照样执行一遍展开后的完整 SQL。真正能提速的是物化视图——把结果物理存下来——但实验四练的是普通视图重点应该放在理解“重写”这一步。看到一个视图的执行计划时你会发现计划节点里已经没有视图的名字只剩展开后的表扫描和连接操作。5. 事务、并发与恢复的排查为什么实验七、实验八最容易翻车5.1 事务的 ACID 与日志先行WAL实验七讲事务与并发控制。事务的四个性质——原子性、一致性、隔离性、持久性——不是口号它们对应不同的底层机制。原子性靠回滚日志或 MVCC 的旧版本数据实现持久性靠预写日志WAL实现事务提交前先把修改记录写到日志文件再写数据文件。这样即使系统在写数据文件时崩溃重启后可以从日志里重放修改不会丢已提交的事务。这就是为什么事务提交的耗时主要花在刷日志上而不是刷数据上。数据文件可以用后台进程慢慢写日志必须同步落盘。理解这一点后再看“事务提交了但数据没立刻出现在表文件里”就不慌了——数据可能还在缓冲池里但日志已经保证它不会丢。回滚则是另一套路径。事务执行中如果出错DBMS 要把已修改的行恢复成事务开始前的状态。MVCC 体系下更新不是直接覆盖旧值而是生成一个新版本的行旧版本保留在页面里。回滚时只需要把新版本标记为不可见旧版本自然恢复可见。这套设计的代价是旧版本需要清理如果长事务一直不结束旧版本堆积会让表膨胀。5.2 隔离级别与 MVCC读不会阻塞写并发控制最容易让人困惑的是隔离级别。实验里默认配置下两个会话同时更新同一行时后更新的人会卡住直到前一个事务提交或回滚。这是因为行级锁——写与写互相排斥。但读与写不互斥因为读走的是 MVCC 快照每个事务有自己可见的数据版本读不会被写阻塞。在 READ COMMITTED 级别下事务里每条语句获取一个新快照所以同一个事务内两次查询可能看到不同结果——第一次查到旧值第二次查到别人刚提交的新值。到了 REPEATABLE READ事务开始时的快照一直用到结束两次查询结果一致。实验七如果不切换隔离级别很容易被“同一条 SELECT 结果不一样”吓到以为是数据乱了其实是快照规则不同。排查并发问题时最该看的是锁等待SELECT * FROM pg_locks WHERE NOT granted;这条查询能列出所有拿不到锁、正在等待的会话。配合pg_stat_activity看谁在等谁基本能定位卡住的源头。死锁则是另一个极端两个事务互相持有对方需要的锁DBMS 检测到死锁后会中止其中一个事务并返回错误另一个才能继续。实验里遇到死锁第一反应不是骂数据库而是去看事务里获取资源的顺序是不是不一致——把两个事务里对表的加锁顺序调成一致死锁大概率消失。5.3 备份恢复一致性点与日志归档实验八的备份与恢复核心不是“备份”这个动作而是“恢复到哪个时间点”。物理备份是把数据目录连同表空间整体复制下来逻辑备份是用工具导出表结构和数据。两者都要解决同一个问题备份过程中数据库还在写入怎么保证备份出来的数据是一致性状态答案是把备份和日志归档配合起来。先做一次基础备份从那时起持续归档 WAL 日志恢复时先还原基础备份再重放日志到目标时间点。这样即使基础备份是凌晨做的中午磁盘坏了也能恢复到损坏前一刻。如果只有基础备份没有归档日志那丢失的就是从备份点到故障点的全部数据——这是实验里最容易暴露的认知缺口备份不是复制文件备份是一套从“基础快照”到“日志连续重放”的体系。5.4 踩坑记录实验七、实验八最常见的五种翻车现象原因解决UPDATE 执行后卡住不动另一会话持有同一行的行锁未提交查pg_locks找持锁会话COMMIT 或 ROLLBACK同一事务内两次 SELECT 结果不同READ COMMITTED 下每条语句取新快照需要一致性读时改用 REPEATABLE READ事务里 UPDATE 报约束冲突违反 CHECK 或主键唯一约束整个事务未提交先 ROLLBACK 再修正数据不要继续执行备份文件拷走了恢复后数据缺失备份期间有新写入未归档日志备份要和 WAL 归档配合不能只拷文件大批量删除后表文件没变小MVCC 旧版本还没清理执行清理后再统计大小表实际占用会下降第一条是实验里出现频率最高的“翻车现场”。两个窗口同时操作一个开会忘了提交另一个去改同一行界面就一直转圈。这时候不是数据库坏了是行锁在等前一个事务结束。看pg_locks里NOT granted的行找到持锁会话的进程号再决定是等它提交、还是直接终止它。从那以后我每次开事务都养成一个习惯语句执行完立刻看要不要提交绝不挂着事务去干别的事这个习惯帮我省下了大量排查时间。5.5 恢复的优先级先想清楚“丢多少数据可接受”备份与恢复实验最值得深挖的问题是恢复时间点。日志归档默认保留一定数量的 WAL 文件超出的会被清理或覆盖。如果归档周期和保留策略没配好恢复时就只能回到“归档里存在的最后一个时间点”而不是你想要的任意点。设计备份策略前先问自己两个阈值最多能接受丢多少数据最多能接受停多久。前者决定日志归档频率后者决定恢复演练的必要性。实验八只做到“备份—恢复”成功生产环境还要反复演练“恢复到指定时间点”因为恢复流程里最容易出错的是日志序列的拼接——缺了一段恢复就停在缺段之前。数据量越大越需要先验证归档日志的连续性再谈恢复速度。6. 从 JDBC 回看数据库设计连接协议里藏着哪些取舍6.1 JDBC 连接串与协议协商实验九用 JDBC 把应用和数据库连起来。连接串jdbc:postgresql://127.0.0.1:26000/db_school里的每个字段都有对应机制协议名决定驱动怎么解析报文地址端口决定连到哪个主节点数据库名决定之后 SQL 默认在哪个库执行。驱动加载后客户端与服务器先做协议握手协商认证方式、字符编码、参数设置然后才进入正常的查询循环。Class.forName(org.postgresql.Driver); Connection conn DriverManager.getConnection( jdbc:postgresql://127.0.0.1:26000/db_school, student_user, 密码占位); conn.setAutoCommit(false); PreparedStatement pstmt conn.prepareStatement( UPDATE SC SET Grade ? WHERE Sno ?); pstmt.setInt(1, 95); pstmt.setString(2, 2021001); pstmt.executeUpdate(); conn.commit();这段代码把实验七的事务和实验九的 JDBC 串起来了。setAutoCommit(false)意味着后面多条语句共享一个事务直到显式commit()。实验里有人漏掉 commit程序退出后回滚数据没写进去——这不是数据库丢数据是事务从未提交。6.2 三个设计取舍预编译、手动事务与连接池JDBC 里最值得琢磨的是预编译。prepareStatement会把 SQL 模板先发给服务器生成执行计划后续每次只传参数值不再重复解析和优化。这省掉了重复的解析开销也让 SQL 和参数分离注入无从下手。这个机制和第三章的执行链路呼应一次解析多次执行把最贵的优化过程摊销掉。第二个取舍是事务边界。JDBC 默认自动提交——每条语句一个事务。但业务里一个操作往往涉及多条 SQL全自动提交会把原子性破坏掉。所以驱动把事务控制权交给应用层setAutoCommit(false)是接手边界。取舍在于事务太长会持有锁太久太短又牺牲原子性。实验九让你手动提交真正的意图是让你感知“事务边界是自己画出来的不是数据库替你决定的”。第三个取舍是连接池。每次新建连接都要经过 TCP 握手、认证、派生后端进程代价远高于执行几条 SQL。连接池把连接复用起来本质是把建连接的固定成本分摊到多次请求上。回看实验一的进程模型——每个后端一个进程——就知道连接昂贵是 PG 系架构的固有属性连接池不是锦上添花是绕不开的基础设施。数据库这门课真正难的不是语法是每一个机制背后的取舍用进程隔离换连接开销用多版本换读写并发用预写日志换崩溃安全用预编译换解析成本。从那以后我每次拿到一段数据库代码都会先问一句“这条连接和这条事务在底层让哪些进程动了手、持了哪些锁、刷了哪段日志”把问题追到机制层再下手改。这份实验报告书刚好是这条追问路径的地图每一章对应一个机制照着走一遍你会发现自己看数据库的方式彻底变了。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/10/19 2:23:02 · 阅读 1.2k 从DBMS到JDBC：数据库原理实验九次上机技术主线解析
简介数据库系统原理实验是计算机科学与技术、软件工程专业的重要基础课程。这份zzu数据库实验报告书完整收录了九个循序渐进的实验从认识DBMS、创建数据库表与索引到交互式SQL、视图、完整性/安全性控制再到事务与并发、备份恢复及JDBC连接数据库覆盖openGauss环境下的核心数据库操作。报告以文档形式呈现含1个docx文件压缩包大小3.23MB当前已有1522人学习下载。每个实验均包含实验目的、操作过程、遇到的问题及解决方法并配有相应截图与代码示例可帮助读者快速理解数据库建模、SQL命令、权限管理和并发控制等关键知识点也可作为期末复习、课程设计或备考的实验参考模板。无论是初学者入门还是进阶巩固这份报告书都能提供系统而直观的实践指引。1. 九次实验隐藏的三条主线为什么入门要先认识 DBMS数据库原理实验最反直觉的一点九次实验里最容易被当成“走过场”的第一次恰恰决定了后面八次能不能真正看懂。第一次实验不写业务 SQL而是安装 DBMS、启动服务、用 gsql 连上默认数据库 postgres、敲几条 \l、\d 命令去翻系统表。为什么这样安排因为 SQL 只是数据库这棵大树的叶子真正的根是 DBMS 怎么管理存储、怎么控制权限、怎么保证提交了的事务不丢数据。这份实验报告书把这些问题拆成了九次上机对象管理、交互式 SQL、视图、完整性、安全性、事务与并发、备份恢复最后用 JDBC 把数据库接回应用层。搞懂这条主线数据库才会从“黑匣子”变成一台看得见机制的机器。2. 对象与存储数据库、schema、表、索引在磁盘上到底是什么2.1 从 CREATE TABLESPACE 看“目录—文件—表空间”的映射实验二第一件事不是建表而是建表空间CREATE TABLESPACE tpcds_local RELATIVE LOCATION tablespace/tablespace_1; CREATE DATABASE db_sc WITH TABLESPACE tpcds_local; \c db_school第一句在数据库主节点上划了一块物理目录第二句把数据库放进了这块目录第三句切换过去开始建表。表空间这个词听起来抽象实际对应文件系统里的一个目录。RELATIVE LOCATION指定的是相对数据目录的路径而且要求目标目录为空——因为数据库系统要在里面自己管理文件布局。一个表空间可以拥有多个数据文件一个数据文件只能属于一个表空间这种层级关系决定了数据最终落在哪块磁盘上。很多人建表时根本不指定表空间于是所有表都落到数据库默认关联的表空间里。实验里把数据库和表空间显式绑定就是要你看见这条映射链表空间指向物理目录数据库指向表空间表的数据文件再放进数据库所在的目录。表是逻辑对象但每一行数据最终都要落到某个文件偏移上这个“逻辑对象到物理文件”的映射就是 DBMS 与单纯的数据文件最本质的区别。2.2 schema 的双重含义逻辑命名空间与物理容器实验书里专门辨析了 schema 的两种含义。概念上的 schema 是一组 DDL 语句集描述整个数据库的结构也就是教材里的“逻辑模式”物理上的 schema 是数据库内的一个命名空间包含一组表、视图、存储过程等对象。学生在实验里常犯的错是把 schema 当成数据库的别名——它们其实是两级不同的命名空间一个数据库下可以创建多个 schema每个 schema 下再挂表和视图。CREATE SCHEMA book_eg; CREATE TABLE book_eg.Teachers(Tno CHAR(20) PRIMARY KEY); DROP SCHEMA book_eg CASCADE;最后一句CASCADE是重点。不带级联时如果 schema 里还有对象删除会直接报错加上CASCADE表示把 schema 里的表、视图、索引一起删掉。这个设计是为了防止你误删一个还有数据的命名空间。我在做实验时习惯先查\dn确认 schema 列表再动手因为DROP SCHEMA不像DROP TABLE那样有回收站删完没有后悔药。物理 schema 为什么值得单独存在因为它给了权限控制一个中间层。你可以把一组表放进book_eg然后只把这个 schema 的访问权授给某个用户不用逐个表授权。这比把对象全堆在公共命名空间里干净得多也是实验二要把模式单独立项的用意。2.3 主键、索引与系统目录为什么 \d 能看到这么多信息实验二建完表后用\d查表属性、用\di查索引这些元命令的结果来自系统目录表。openGauss 里有几张核心的目录表pg_database存数据库列表pg_tablespace存表空间pg_user存用户pg_roles存角色pg_authid存认证信息。执行\l本质上就是查询pg_database执行\db就是查pg_tablespace——元命令只是把这些查询包装成了更友好的输出。CREATE TABLE Departments ( Dno CHAR(20) PRIMARY KEY, Dname CHAR(20), Dheadno CHAR(20), FOREIGN KEY (Dheadno) REFERENCES Teachers(Tno) ); CREATE INDEX Student_Dept ON Students(Dno);注意外键Dheadno引用Teachers(Tno)但外键不会自动在主键列上建索引。实验里建了Student_Dept索引去加速按院系查学生本质是在 Students 表的 Dno 列上建一棵 B 树。索引是一个辅助数据结构它不影响表里的数据排列只提供一条快速定位记录的路径。主键列会由 DBMS 自动建唯一索引普通列要提速必须手动建索引这就是\di能同时看到“系统建的”和“自己建的”两类索引的原因。系统目录本身也是表这意味着你可以用 SQL 去查数据库的元数据。理解这一点才算真正看懂\d、\dt、\di这些命令——它们不是在黑匣子外面敲咒语而是在查询一组描述数据库自身的表。数据库为什么能“认识”自己就是靠这套自描述的目录体系。对象层级物理对应逻辑作用表空间数据目录下的一个目录决定数据文件存放位置数据库表空间中划分的逻辑空间隔离不同业务的全部数据schema数据库内的命名空间组织表、视图等对象可独立授权表磁盘上的数据文件存储具体行数据索引独立的辅助文件加速检索不改变数据本身3. SQL 执行链路一条查询在 openGauss 里是怎么跑完的3.1 gsql 会话从端口 26000 到后台进程实验一要求先启动服务再连接命令本身很简单但背后是一整套进程模型su - omm gs_om -t start gsql -d postgres -p 26000 -rsu - omm切换数据库安装用户gs_om -t start启动集群gsql -d postgres -p 26000连接默认数据库。-r开启行编辑。gsql 连上后主节点上的监听进程接受 TCP 连接为这个会话派生一个独立的数据库后端进程然后进入“读命令—执行—返回结果”的循环。每个客户端连接都对应一个独立进程而不是线程这是 PG 体系一脉相承的设计一个后端进程崩溃不会拖垮整个数据库隔离性更好代价是连接开销大。这个模型解释了为什么连接数不能无限上涨——每个会话都要消耗进程内存和文件描述符。实验里用gs_om -t start启动的是整个集群如果之前有残留进程没清理干净启动会报端口占用或状态不一致最常见的处理是先看状态再启动别跳过检查直接敲命令。3.2 解析、重写、优化、执行SQL 不是“直接跑”的一条交互式 SQL 从键盘敲进去到最后返回结果集中间要过四道工序。词法分析把字符串切成 token语法分析按 SQL 文法生成语法树语义分析校验表名、列名、类型是否合法如果是视图还要在这里做查询重写把视图展开成基表查询然后优化器根据统计信息生成执行计划最后执行器按计划访问存储引擎。实验三里大量练习增删改查却看不到这些内部过程——因为 DBMS 把复杂度藏起来了。理解这条链路最大的价值在于很多 SQL 性能问题不是“写错了”而是“优化器没选对路”。比如对一列建了索引但查询条件是WHERE Dname LIKE %计算机%前导通配符让 B 树无法定位起点优化器只能选顺序扫描。这不是索引没用而是用错了场景。验证这条链路最直接的手段是看执行计划EXPLAIN SELECT * FROM Students WHERE Sno 2021001;EXPLAIN不真正执行查询只输出优化器选中的计划节点。你会看到是顺序扫描还是索引扫描、预估行数、预估代价。代价估算依赖表的统计信息如果刚导入了大量数据却没有做统计更新优化器会基于过期的行数做判断选错计划。这就是为什么数据库管理员在大批量数据变更后要重新收集统计信息——优化器是“用数据做决策”的数据不准决策就偏。3.3 元命令的本质gsql 客户端做了什么实验一里大量使用了\l、\dt、\d、\di、\dn、\db、\h这些反斜杠命令。它们不是 SQL而是 gsql 客户端的元命令在客户端本地被解析再翻译成对系统目录的 SQL 查询发给服务器。\d tablename展示表的属性、索引、约束其实是对pg_attribute、pg_index、pg_constraint等目录表的关联查询。这解释了为什么\d系列命令能“看到”结构——不是服务器有专门的“看结构”接口而是结构信息本来就存在系统表里。\h列出所有支持的 SQL 语法本质是客户端内置的语法帮助文本。明白这一点后你就不会再把元命令和 SQL 混为一谈SQL 发给服务器执行元命令在客户端立即处理。遇到\d没反应时先检查是不是不小心进了 SQL 的字符串字面量里——元命令只有在 gsql 的“非查询输入状态”才被识别这是个很容易踩的小坑。4. 完整性与安全性约束、角色、视图如何在底层生效4.1 五类约束的存储与检查时机实验五的完整性控制把约束拆成五类NOT NULL、UNIQUE、PRIMARY KEY、CHECK、FOREIGN KEY。约束不是写在表定义里就完事DBMS 要把它们存进系统目录并在每次数据变更时强制检查。CREATE TABLE Students ( Sno CHAR(20) PRIMARY KEY, Sname CHAR(20) NOT NULL, Sex CHAR(20) CHECK(Sex男 OR Sex女), Birthday DATE, Dno CHAR(20), FOREIGN KEY (Dno) REFERENCES Departments(Dno) );PRIMARY KEY在目录里登记主键信息的同时自动创建一个唯一索引NOT NULL和CHECK在 INSERT 和 UPDATE 时逐行验证条件外键的检查发生在子表写入或主表被删除时。主表的行被引用着执行 DELETE 会触发参照完整性检查——要么拒绝要么级联取决于外键定义的ON DELETE动作。列级约束和表级约束的区别也在这一章体现。涉及单列的约束可以跟在列定义后面涉及多列的约束必须用表级写法因为约束的作用范围超出了单列。一个经典错误是以为加了约束数据库就永远“干净”了——实际上约束只能挡住不合规则的数据写入对于已经存在的脏数据添加约束时数据库要先做全表校验校验失败就整条语句报错。所以给大表加CHECK约束前最好先手动查一遍有没有存量违规数据。外键的另一个细节值得注意外键列本身不会自动建索引。实验里 Departments 的 Dno 是主键Students 的 Dno 是外键删除或更新 Departments 的一行时DBMS 要在 Students 里找所有引用它的行没有索引就只能扫全表。生产环境里外键列几乎总是需要手动补一个索引这是完整性控制实验不会直接讲、但真实系统一定会遇到的性能问题。4.2 用户、角色与权限为什么角色是权限的集合实验六的安全性控制把用户和角色分开处理。实验书里的辨析很到位用户是具体账户个体角色是某一类权限的集合。数据库里新建一个用户本质是在pg_authid里登记一条认证记录而角色对应一组权限可以分配给任意用户。CREATE USER student_user IDENTIFIED BY 密码占位; CREATE ROLE read_only_role; GRANT SELECT ON Students TO read_only_role; GRANT read_only_role TO student_user;这样做的好处是权限的变更成本降到最低新来一个学生直接把read_only_role授给他这个学生转岗了撤销角色即可不用逐表回收权限。底层实现上权限检查发生在执行器启动之前DBMS 先查角色的成员关系再判断当前用户是否有目标表的访问权两处都是系统目录查询加上内存缓存。权限的粒度是分层的连接数据库需要 CONNECT 权限查表需要 SELECT 权限创建表需要所在 schema 的 CREATE 权限。实验里只授SELECT不授INSERT用户能读不能写这就是安全性控制的核心思想——最小权限原则。另一个容易忽略的点GRANT只能授予你本身就拥有的权限普通用户不能转授自己都没有的权限这条规则在系统目录里同样有记录。4.3 视图的底层查询重写与安全边界实验四创建视图目的是理解数据抽象和查询复用。视图在 openGauss 里默认不物理存储数据它只是一条命了名的 SQL 语句存在系统目录里。查询视图时优化器在重写阶段把视图定义展开成基表查询之后走和直接查基表完全相同的优化和执行路径。CREATE VIEW student_sc AS SELECT S.Sno, Sname, Cname, Grade FROM SC JOIN Students S ON SC.Sno S.Sno JOIN Courses C ON SC.Cno C.Cno;视图能当安全边界用把基表里不该暴露的列挡住只把视图的 SELECT 权限授出去。比如 Students 表里有生日、院系等字段你不想让别人看到就把视图只包含学号和姓名然后授视图权限。这样别人能查视图却查不到基表的其他列——权限检查发生在访问视图时视图内部的基表访问由视图定义者的权限决定普通用户不需要直接拥有基表的权限。视图还有一个容易踩的认知误区以为视图能提升查询性能。实际上普通视图不会加速查询它只是简化书写每次查询视图底层照样执行一遍展开后的完整 SQL。真正能提速的是物化视图——把结果物理存下来——但实验四练的是普通视图重点应该放在理解“重写”这一步。看到一个视图的执行计划时你会发现计划节点里已经没有视图的名字只剩展开后的表扫描和连接操作。5. 事务、并发与恢复的排查为什么实验七、实验八最容易翻车5.1 事务的 ACID 与日志先行WAL实验七讲事务与并发控制。事务的四个性质——原子性、一致性、隔离性、持久性——不是口号它们对应不同的底层机制。原子性靠回滚日志或 MVCC 的旧版本数据实现持久性靠预写日志WAL实现事务提交前先把修改记录写到日志文件再写数据文件。这样即使系统在写数据文件时崩溃重启后可以从日志里重放修改不会丢已提交的事务。这就是为什么事务提交的耗时主要花在刷日志上而不是刷数据上。数据文件可以用后台进程慢慢写日志必须同步落盘。理解这一点后再看“事务提交了但数据没立刻出现在表文件里”就不慌了——数据可能还在缓冲池里但日志已经保证它不会丢。回滚则是另一套路径。事务执行中如果出错DBMS 要把已修改的行恢复成事务开始前的状态。MVCC 体系下更新不是直接覆盖旧值而是生成一个新版本的行旧版本保留在页面里。回滚时只需要把新版本标记为不可见旧版本自然恢复可见。这套设计的代价是旧版本需要清理如果长事务一直不结束旧版本堆积会让表膨胀。5.2 隔离级别与 MVCC读不会阻塞写并发控制最容易让人困惑的是隔离级别。实验里默认配置下两个会话同时更新同一行时后更新的人会卡住直到前一个事务提交或回滚。这是因为行级锁——写与写互相排斥。但读与写不互斥因为读走的是 MVCC 快照每个事务有自己可见的数据版本读不会被写阻塞。在 READ COMMITTED 级别下事务里每条语句获取一个新快照所以同一个事务内两次查询可能看到不同结果——第一次查到旧值第二次查到别人刚提交的新值。到了 REPEATABLE READ事务开始时的快照一直用到结束两次查询结果一致。实验七如果不切换隔离级别很容易被“同一条 SELECT 结果不一样”吓到以为是数据乱了其实是快照规则不同。排查并发问题时最该看的是锁等待SELECT * FROM pg_locks WHERE NOT granted;这条查询能列出所有拿不到锁、正在等待的会话。配合pg_stat_activity看谁在等谁基本能定位卡住的源头。死锁则是另一个极端两个事务互相持有对方需要的锁DBMS 检测到死锁后会中止其中一个事务并返回错误另一个才能继续。实验里遇到死锁第一反应不是骂数据库而是去看事务里获取资源的顺序是不是不一致——把两个事务里对表的加锁顺序调成一致死锁大概率消失。5.3 备份恢复一致性点与日志归档实验八的备份与恢复核心不是“备份”这个动作而是“恢复到哪个时间点”。物理备份是把数据目录连同表空间整体复制下来逻辑备份是用工具导出表结构和数据。两者都要解决同一个问题备份过程中数据库还在写入怎么保证备份出来的数据是一致性状态答案是把备份和日志归档配合起来。先做一次基础备份从那时起持续归档 WAL 日志恢复时先还原基础备份再重放日志到目标时间点。这样即使基础备份是凌晨做的中午磁盘坏了也能恢复到损坏前一刻。如果只有基础备份没有归档日志那丢失的就是从备份点到故障点的全部数据——这是实验里最容易暴露的认知缺口备份不是复制文件备份是一套从“基础快照”到“日志连续重放”的体系。5.4 踩坑记录实验七、实验八最常见的五种翻车现象原因解决UPDATE 执行后卡住不动另一会话持有同一行的行锁未提交查pg_locks找持锁会话COMMIT 或 ROLLBACK同一事务内两次 SELECT 结果不同READ COMMITTED 下每条语句取新快照需要一致性读时改用 REPEATABLE READ事务里 UPDATE 报约束冲突违反 CHECK 或主键唯一约束整个事务未提交先 ROLLBACK 再修正数据不要继续执行备份文件拷走了恢复后数据缺失备份期间有新写入未归档日志备份要和 WAL 归档配合不能只拷文件大批量删除后表文件没变小MVCC 旧版本还没清理执行清理后再统计大小表实际占用会下降第一条是实验里出现频率最高的“翻车现场”。两个窗口同时操作一个开会忘了提交另一个去改同一行界面就一直转圈。这时候不是数据库坏了是行锁在等前一个事务结束。看pg_locks里NOT granted的行找到持锁会话的进程号再决定是等它提交、还是直接终止它。从那以后我每次开事务都养成一个习惯语句执行完立刻看要不要提交绝不挂着事务去干别的事这个习惯帮我省下了大量排查时间。5.5 恢复的优先级先想清楚“丢多少数据可接受”备份与恢复实验最值得深挖的问题是恢复时间点。日志归档默认保留一定数量的 WAL 文件超出的会被清理或覆盖。如果归档周期和保留策略没配好恢复时就只能回到“归档里存在的最后一个时间点”而不是你想要的任意点。设计备份策略前先问自己两个阈值最多能接受丢多少数据最多能接受停多久。前者决定日志归档频率后者决定恢复演练的必要性。实验八只做到“备份—恢复”成功生产环境还要反复演练“恢复到指定时间点”因为恢复流程里最容易出错的是日志序列的拼接——缺了一段恢复就停在缺段之前。数据量越大越需要先验证归档日志的连续性再谈恢复速度。6. 从 JDBC 回看数据库设计连接协议里藏着哪些取舍6.1 JDBC 连接串与协议协商实验九用 JDBC 把应用和数据库连起来。连接串jdbc:postgresql://127.0.0.1:26000/db_school里的每个字段都有对应机制协议名决定驱动怎么解析报文地址端口决定连到哪个主节点数据库名决定之后 SQL 默认在哪个库执行。驱动加载后客户端与服务器先做协议握手协商认证方式、字符编码、参数设置然后才进入正常的查询循环。Class.forName(org.postgresql.Driver); Connection conn DriverManager.getConnection( jdbc:postgresql://127.0.0.1:26000/db_school, student_user, 密码占位); conn.setAutoCommit(false); PreparedStatement pstmt conn.prepareStatement( UPDATE SC SET Grade ? WHERE Sno ?); pstmt.setInt(1, 95); pstmt.setString(2, 2021001); pstmt.executeUpdate(); conn.commit();这段代码把实验七的事务和实验九的 JDBC 串起来了。setAutoCommit(false)意味着后面多条语句共享一个事务直到显式commit()。实验里有人漏掉 commit程序退出后回滚数据没写进去——这不是数据库丢数据是事务从未提交。6.2 三个设计取舍预编译、手动事务与连接池JDBC 里最值得琢磨的是预编译。prepareStatement会把 SQL 模板先发给服务器生成执行计划后续每次只传参数值不再重复解析和优化。这省掉了重复的解析开销也让 SQL 和参数分离注入无从下手。这个机制和第三章的执行链路呼应一次解析多次执行把最贵的优化过程摊销掉。第二个取舍是事务边界。JDBC 默认自动提交——每条语句一个事务。但业务里一个操作往往涉及多条 SQL全自动提交会把原子性破坏掉。所以驱动把事务控制权交给应用层setAutoCommit(false)是接手边界。取舍在于事务太长会持有锁太久太短又牺牲原子性。实验九让你手动提交真正的意图是让你感知“事务边界是自己画出来的不是数据库替你决定的”。第三个取舍是连接池。每次新建连接都要经过 TCP 握手、认证、派生后端进程代价远高于执行几条 SQL。连接池把连接复用起来本质是把建连接的固定成本分摊到多次请求上。回看实验一的进程模型——每个后端一个进程——就知道连接昂贵是 PG 系架构的固有属性连接池不是锦上添花是绕不开的基础设施。数据库这门课真正难的不是语法是每一个机制背后的取舍用进程隔离换连接开销用多版本换读写并发用预写日志换崩溃安全用预编译换解析成本。从那以后我每次拿到一段数据库代码都会先问一句“这条连接和这条事务在底层让哪些进程动了手、持了哪些锁、刷了哪段日志”把问题追到机制层再下手改。这份实验报告书刚好是这条追问路径的地图每一章对应一个机制照着走一遍你会发现自己看数据库的方式彻底变了。希望帮到你。本文还有配套的精品资源点击获取