企业官网建设流程全解析

简介：这份zzu数据库实验报告书面向计算机科学与技术、软件工程专业的本科生，配合《数据库系统原理》课程的实验环节使用，帮助读者把概念模型、关系模型和SQL操作落实到可提交的实验文档中。内容以openGauss为平台，依次记录认识DBMS系统、创建数据库表与索引、交互式SQL语句、创建视图、完整性控制、安全性控制、事务与并发控制、备份与恢复、JDBC连接数据库九个实验；每个实验都包含目的、内容、类型、学时与仪器材料说明，并配有gsql常用命令、环境搭建过程与操作截图，可供对照撰写实验步骤、问题分析与结果讨论。压缩包内只有1个docx文件，体积约3.23MB，结构完整、排版规范，便于打印或二次编辑。目前已有1527人学习，可作为课程实验的参考范本和排错对照材料。

1. gsql 不是数据库本体：一条 \l 命令背后的客户端/服务端分层

你敲\l列出数据库列表时，以为读的是某个文件。其实是 gsql 把一条查询发给一个叫 postgres 的常驻进程，进程查完系统表再把结果序列化回来。这个分层决定了后面所有实验的现象：为什么\c db_school切换数据库之后原来的表还在，为什么端口号必须显式指定。gsql 只是客户端工具，数据库本体是后台那个一直在等请求的进程。理解这一点，才能解释实验二里建表语句在哪台机器上执行都一样生效，而数据文件落在哪里反而不重要。这也是为什么实验一强调安装和启动：先让服务跑起来，才有后面九次操作的全部前提。

2. 从表空间到索引：五层存储结构各自在解决什么问题

创建数据库那一刻，数据库系统并没有在磁盘上新造一个“文件夹”，而是往pg_database里插了一行记录，然后在磁盘上分配一个目录，把路径记到pg_tablespace里。这件事看起来像文件管理，实际是一套分层命名系统在起作用。表空间、数据库、Schema、表、索引，这五层不是随便叠出来的，层与层之间解决的是不同性质的问题：物理分布、隔离边界、命名冲突、数据组织、检索加速。把这几层各自负责什么搞清楚，后面建表加约束、建索引不生效、权限怎么落到具体的表上，才有解释的锚点。

2.1 表空间、数据库、Schema：为什么这不是“文件夹套文件夹”

表空间是物理层映射。创建tpcds_local时，relative location指定的目录是相对于数据库主数据目录的一个子路径，这一层决定的是数据文件落在哪块磁盘上。在同一个数据库里，不同的表可以分布到不同的表空间，目的是把 IO 压力分散开，而不是为了逻辑分组。换句话说，表空间是给运维看的，不是给业务逻辑看的。

-- 创建表空间：指定物理存储的相对路径 CREATE TABLESPACE tpcds_local RELATIVE LOCATION 'tablespace/tablespace_1'; -- 创建数据库并绑定默认表空间：该库后续新建的对象默认落在这个表空间 CREATE DATABASE db_sc WITH TABLESPACE = tpcds_local; -- 数据库改名：修改的是系统目录里的元数据，不移动磁盘上的物理文件 ALTER DATABASE db_sc RENAME TO db_school;

数据库这一层是隔离边界。db_school和postgres是两个独立的库，不能直接跨库做 join，连接时必须显式切换到目标库。这跟表空间是正交的：同一个表空间可以被多个数据库共用，一个数据库也可以使用多个表空间。隔离边界决定了连接串里必须带数据库名，也决定了备份、权限、字符集这一层各自独立。熟悉用法的开发者容易把数据库当成“最外层的容器”，其实它只是账号和对象归属的边界，权限和系统表是分库维护的。

Schema 是数据库内部的命名空间隔离。一个数据库里可以有多个 schema，比如book_eg和public，两个 schema 下的同名表互不冲突。系统表pg_namespace里每一行对应一个 schema，pg_class里的relnamespace字段指向它。物理存储这一层并不为 schema 单独分配文件目录，schema 只影响对象名的解析路径。这正是为什么CREATE SCHEMA book_eg;之后建表要写成book_eg.Teachers，而search_path参数又可以让某些 schema 下的对象被省略前缀找到。

三层分开看，关系就清楚了：表空间管磁盘分布，数据库管连接与权限边界，schema 管对象名冲突。系统表pg_tablespace、pg_database、pg_namespace各自维护一层元数据，在\l、\dn、\db这些元命令背后，gsql 只是把这些系统表查询包装成了可读格式。下次再看到“一个数据库可以有多个 schema，一个 schema 属于一个数据库”这种教材定义，可以把它翻译成：pg_namespace通过datdba和库的 OID 发生关联，而表空间通过reltablespace与具体表发生关联，层级之间是平级交叉引用，不是严格的树状嵌套。

2.2 约束在系统表里不是“注释”，而是可查询的记录

建表时写PRIMARY KEY、CHECK、FOREIGN KEY，看起来只是 SQL 语法里的附加说明，实际上每写一个约束，数据库都会在系统目录里留下一条可查的记录。主键约束在 openGauss 上的实现方式是自动建一个唯一索引，同时对相关列加NOT NULL约束；CHECK约束的表达式不会被存成纯文本，而是编译成内部节点树，记录在pg_constraint的conbin字段里；外键约束除了在pg_constraint里有一行contype = 'f'的记录之外，还会通过依赖关系追踪被引用表和引用表之间的绑定关系，所以删被引用表的时候会看到“被其他对象依赖”的报错。

-- 创建带有列级约束的表：PRIMARY KEY 隐含唯一且非空 CREATE TABLE Teachers ( Tno CHAR(20) PRIMARY KEY, Tname CHAR(20) NOT NULL, Sex CHAR(20) CHECK (Sex = '男' OR Sex = '女'), Birthday DATE, Dno CHAR(20) ); -- 通过系统表查看这个表上到底挂了哪些约束 SELECT conname, contype, conrelid::regclass AS table_name FROM pg_constraint WHERE conrelid = 'teachers'::regclass;

contype的取值是关键：p表示主键，u表示唯一约束，c表示 CHECK 约束，f表示外键。查询结果里每条约束都有一个系统生成的约束名，只有显式命名的约束才能按名字操作。常见的误区是认为列级约束和表级约束在系统表里有差别，其实数据库根本不在乎你在 SQL 里写在列后面还是写在表末尾，最终都归一到pg_constraint这一张表。区别只在语法层面：涉及多列的约束（比如复合主键、复合外键）必须写在表级，单列约束两种写法等价。实验报告里对“列级约束与表级约束”的辨析，落到系统表上看就一句话：位置不同，存储归一。

还有一点值得单独说：删除被引用的表时遇到的依赖报错，来自pg_depend里的记录，而不是外键定义本身。外键约束在pg_constraint里有confrelid指向被引用表，constr相关的依赖关系会决定DROP TABLE的可行性。所以有时候改约束、加约束，看到的是索引层面的变化，背后的机制是系统表之间的引用完整性在起作用。

2.3 索引默认 B-Tree：一次等值查询到底走了哪条路

建索引的直觉是“加快查询”，但数据库优化器是否选择走索引，取决于代价估算。CREATE INDEX student_dept ON Students(Dno);创建的是一个 B-Tree 索引，pg_class里会多出一行relkind = 'i'的记录，pg_index里记录索引对应的表、索引列和唯一性标记。主键在创建时已经自动建好了唯一索引，所以再手动为同一列建普通索引属于重复劳动。实验报告里写“DBMS 自动为主码建立索引”，从系统表看就是pg_index.indisprimary为true。

-- 在 Students 的 Dno 列上建一个普通索引 CREATE INDEX student_dept ON Students(Dno); -- 查看执行计划：对比带索引和不带索引时优化器的选择 EXPLAIN ANALYZE SELECT * FROM Students WHERE Dno = 'D01';

执行计划里需要关注的是扫描方式和代价数字。Seq Scan表示全表扫描，Index Scan表示走索引后回表，Index Only Scan表示只读索引就拿到了需要的列。这里有个容易翻车的点：建了索引却还是 Seq Scan，常见原因有两个，一是查询条件列的类型和索引列的类型不一致，导致隐式类型转换，B-Tree 索引对转换后的值无法直接使用；二是命中行数太多，优化器判断走索引回表的随机 IO 比顺序全表扫更贵。前者是类型设计问题，后者是索引选择率问题，两者都需要看EXPLAIN的实际输出才能定位，而不是靠“建了索引就应该快”这种直觉。

索引结构本身是 B-Tree 形式的多层页结构，叶子页之间通过指针相连，等值查询和范围查询都能在 O(log n) 的页访问次数内定位。代价在于每次插入和更新都需要维护索引页的分裂与合并，所以索引不是越多越好。对于Dno这种低基数列，建索引还可能因为选择性不足被优化器直接忽略。数据库原理课里强调“索引加快检索”，工程实践里要补一句：加索引之前先想清楚查询模式，建完之后用执行计划验证，不然容易白建。

3. 事务提交：到底哪一步之后数据才算“安全落盘”

一个事务从BEGIN到COMMIT看起来只是包裹了几条 SQL，但提交成功并不等于数据页已经写回磁盘。数据库采用 WAL 机制，修改先在内存缓冲中完成，同时把变更记录顺序写入 redo 日志，COMMIT触发的是日志刷盘，而不是数据页刷盘。数据页由后台检查点进程在合适时机批量写回，所以“提交”保证的是可恢复性，不是数据文件已经更新。把这一点想反了，就会出现“提交了但断电丢数据”的疑惑。实际上只要 WAL 落了盘，崩溃恢复时可以重放日志把数据补回来。这个设计在并发和性能之间做了取舍，也是后面事务隔离和锁机制的基础。

3.1 WAL：提交的时候真正刷下去的是什么

WAL 的全称是 Write-Ahead Logging，核心规则只有一条：任何数据页的修改，必须先写日志，后写数据页。事务执行过程中，针对元组的增删改会生成对应的 WAL 记录，记录里包含事务 ID、操作类型、涉及的页面和修改前后的值。COMMIT时数据库把日志缓冲区刷到磁盘，返回成功。此时数据页可能还留在共享缓冲区里，如果断电，重启时从最后一次检查点开始重放 WAL，把已提交但未落盘的变化重新应用到数据页上。

-- 开启事务，对一行数据做更新 BEGIN; UPDATE Students SET Sname = '张三' WHERE Sno = '2021001'; -- COMMIT 触发 WAL 刷盘，数据页不一定会立刻写回磁盘 COMMIT;

理解 WAL 之后就能解释两个现象。第一，为什么COMMIT返回很快：因为只需要把一段连续的日志写下去，不需要随机写数据页。第二，为什么数据库必须定期做检查点：检查点之前的日志在数据页全部刷盘后可以回收，否则 WAL 文件会无限增长。实验八的备份与恢复之所以要结合日志，原因也在这里：物理备份加上归档日志，可以在恢复时把数据库推进到任意时间点，而单纯拷贝数据文件只是拿到一个不一致的快照。

3.2 MVCC：读不阻塞写的底层代价是什么

多版本并发控制的核心思想是让读操作看到事务开始那一刻的数据快照，而不是当前最新数据。每个元组有两个隐藏字段，xmin记录插入该版本的事务 ID，xmax记录删除该版本的事务 ID。事务开始时取得一个快照，包含当时活跃事务的集合，读数据时根据xmin和xmax判断这个版本对当前事务是否可见。这样读操作不需要加锁，写操作也不需要等待读操作结束，读写之间不会互相阻塞。

-- 查看当前事务的快照信息，了解活跃事务 ID 的范围 SELECT txid_current_snapshot(); -- 查看当前连接中是否有长时间未结束的事务 SELECT pid, state, backend_start FROM pg_stat_activity WHERE state <> 'idle';

快照机制的代价是旧版本元组会堆积。一次更新实际上是标记旧元组的xmax并插入一个新版本，旧版本不会立刻物理删除，需要后台清理进程扫描并回收。如果有一个长事务一直不提交，它持有的快照会让旧版本无法被清理，表就会膨胀。这就是为什么实验文档里强调“事务要尽量短”，不是因为道德要求，而是因为长事务在 MVCC 机制下会卡住清理路径，影响存储和扫描效率。HOT 更新和可见性映射这些优化，本质上也是在缓解旧版本堆积带来的压力。

3.3 行锁与死锁：pg_locks 里每一行在说什么

写操作之间仍然需要互斥，行级锁在更新元组时自动加在最新版本上。两个事务如果各持有一把行锁，又同时请求对方持有的锁，就会形成循环等待，触发死锁。openGauss 有死锁检测机制，默认在检测到死锁后中止其中一个事务，返回deadlock detected错误，让另一个事务继续执行。

-- 会话 A：锁定一行并保持事务不提交 BEGIN; SELECT * FROM Students WHERE Sno = '2021001' FOR UPDATE; -- 会话 B：同样锁定同一行，会进入锁等待 BEGIN; SELECT * FROM Students WHERE Sno = '2021001' FOR UPDATE; -- 在另一个会话中查看锁等待关系 SELECT locktype, relation::regclass, pid, mode, granted FROM pg_locks;

pg_locks里每一行描述一把锁：locktype是锁的对象类型，relation是表名，pid是持有或等待锁的进程号，mode是锁模式，granted表示是否已经获得。排查锁等待时，把pg_locks和pg_stat_activity关联起来，就能看到“谁在等谁”。死锁不会让数据库崩溃，它是对并发访问的一种保护机制，代价是牺牲其中一个事务。工程上减少死锁的常用做法是统一访问顺序，让所有事务按相同顺序加锁，避免交叉等待。

4. 权限体系：GRANT 的改动是怎么进入系统目录的

安全性控制这一层，表面看是“能不能查某张表”，底层其实是每次访问都要走一遍的权限决策链。执行GRANT或REVOKE时，数据库修改的不只是一张权限表，还会牵动对象与角色之间的依赖关系、角色成员的递归展开、以及后续所有访问检查的路径。实验六要求创建用户并分配权限，如果只记 SQL 语法，看到的只是“给或不给”；理解系统表之后，才能解释为什么一个用户明明没有直接授权，却因为属于某个角色而查到了数据，或者为什么回收了权限还会因为继承关系继续生效。

4.1 用户、角色、权限：角色是权限的模板而不是账号

用户和角色在内部是同一种数据结构，区别只在于用户带有LOGIN属性，可以直接连接数据库，而角色不带LOGIN，只能作为权限集合被分配给用户。CREATE ROLE teacher_role;创建的是一个没有登录能力的角色，CREATE USER zhangsan WITH PASSWORD '...';创建的是可以登录的用户。把角色赋给用户之后，用户就拥有了角色所带的所有权限。

-- 创建角色（无 LOGIN 属性，不能直接连接数据库） CREATE ROLE teacher_role; -- 创建用户并设置密码 CREATE USER zhangsan WITH PASSWORD 'Abcd_1234'; -- 把角色赋给用户：用户由此继承角色的权限 GRANT teacher_role TO zhangsan; -- 单独授予某张表的查询权限 GRANT SELECT ON Students TO zhangsan;

角色的用途是批量管理权限。假如有多个用户都需要读同一组表，逐个授权不仅繁琐，回收权限时也容易遗漏。把它们聚合成一个角色，授权和回收都只操作角色这一层，用户和权限之间就解耦了。这里隐藏着一个设计取舍：角色成员关系是可以继承的，默认情况下用户自动继承角色的权限，所以回收用户角色时，用户之前通过角色获得的权限也会被一并收回，不需要额外处理。

4.2 一条 GRANT 语句在系统表里落下了什么

对象的权限信息记录在pg_class.relacl字段里，是一个aclitem数组，每个元素描述了“谁在什么操作上拥有权限，由谁授予”。当执行GRANT SELECT ON Students TO zhangsan;时，这个数组里会追加一项，表示zhangsan在该表上有SELECT权限。角色成员的归属关系记录在pg_auth_members里，一条记录包含角色 ID、成员 ID 和授权者 ID。

-- 查看表上当前的权限数组 SELECT relname, relacl FROM pg_class WHERE relname = 'students'; -- 查看角色与成员之间的对应关系 SELECT r.rolname AS role, m.rolname AS member FROM pg_auth_members am JOIN pg_roles r ON r.oid = am.roleid JOIN pg_roles m ON m.oid = am.member;

权限检查的逻辑是按需展开的：访问一张表时，数据库先看当前用户自己的relacl里有没有对应权限，如果没有，再看用户所属角色是否有权限，角色还可以继续继承其他角色，一直递归到没有更多成员关系为止。这意味着权限查询的代价和角色层级有关，深层的角色继承会带来额外的目录查询开销。REVOKE操作修改的也是同一个aclitem数组，移除对应项之后，如果某个用户是通过角色获得的权限，并且没有其他路径再赋予这个权限，访问就会失败。

4.3 视图作为安全边界：为什么“只给视图权限”比“靠在表上限制列”更稳

直接对表做列级授权虽然也能解决问题，但如果要控制的列很多，维护起来会很麻烦。视图提供了一种更干净的边界：把允许暴露的列和行封装到一个视图里，把底层表的直接访问权限回收，只给视图权限。用户查询视图时，系统会先检查视图本身的权限，然后在视图定义中展开底层表访问。这样底层表的敏感列不会出现在用户权限能碰到的对象里。

-- 只暴露学生编号、姓名和院系，隐藏生日等敏感字段 CREATE VIEW student_public AS SELECT Sno, Sname, Dno FROM Students; -- 收回用户对底层表的直接访问权限 REVOKE ALL ON Students FROM zhangsan; -- 只授予视图查询权限 GRANT SELECT ON student_public TO zhangsan;

这个做法之所以稳，是因为权限边界设置在视图对象上，而不是依赖底层表的列级配置。即便后来Students表新增了敏感列，视图没有引用它，用户也就看不到。需要注意视图默认以视图定义者的权限执行，如果定义者拥有底层表的访问权，用户通过视图能查到的数据范围由视图的WHERE条件和列选择决定，而不是由用户自己的底层权限决定。实验四创建视图时强调“数据的抽象化”，背后就是这层权限隔离机制在起作用。

5. 排查：那些在实验报告里被写成“环境问题”的坑

数据库实验里大部分“环境问题”，回溯到根因都是操作顺序、类型匹配或者对系统表的状态理解不到位。错误提示往往只说结果不说原因，如果只看表面报错，很容易误判成安装或配置出了故障。下面五类现象在实验过程中出现频率最高，处理思路都是先看提示信息，再定位到具体的系统表或配置项。

5.1 敲完命令按回车没反应：分号缺失还是客户端卡住了

现象：在 gsql 里输入SELECT * FROM Students后按回车，光标换行继续等待，但没有执行结果，看起来像卡死。

原因：gsql 以分号作为语句结束符，没有遇到分号时它认为输入尚未结束，会持续接收多行输入。续行时提示符会从db_school=#变成db_school-#，这个变化是判断依据。

解决：补一个分号再回车即可执行；如果已经输入了很多行，敲分号后回车也一样生效。养成看提示符状态的习惯，主提示符和续行提示符的差异是排查这类问题的第一线索，而不是直接怀疑连接断了。

5.2 插入外键数据时报表不存在：主表数据必须先行的顺序

现象：向 SC 表插入选课记录时，报错提示违反外键约束，但确认过 Sno 和 Cno 在各自表里确实存在。

原因：外键约束要求从表引用的值在主表里必须已经存在，这里的“存在”是提交状态。如果插入顺序颠倒，或者主表数据还在未提交的事务里，从表的检查就会失败。批量导入时如果先导入从表也会触发这个错误。

解决：按依赖顺序插入，先主表后从表；在同一个事务里操作时，保证主表的插入在从表之前。批量导入场景，先导主表、再导从表，不要试图在会话级别临时关闭外键检查，那是掩盖问题而不是解决。

5.3 删除模式报错：DROP SCHEMA book_eg;为什么不能执行

现象：执行DROP SCHEMA book_eg;时提示无法删除，报错信息提到 schema 不为空或者存在依赖对象。

原因：schema 里如果有表、视图或其他对象，默认行为是拒绝删除，避免误删数据。这是保护性设计，不是语法问题。

解决：先确认 schema 里的对象确实不需要保留，再使用DROP SCHEMA book_eg CASCADE;级联删除，它会连带删除 schema 下的所有对象。如果只需要删除 schema 本身，先把里面的对象逐个DROP，再删 schema。级联删除之前先查一下pg_class里relnamespace指向该 schema 的对象列表，确认范围。

5.4 建了索引但查询还是全表扫描：类型不匹配或选择率过高

现象：在Students.Dno上执行CREATE INDEX student_dept ON Students(Dno);之后，查询WHERE Dno = 'D01'还是走 Seq Scan，索引似乎没被使用。

原因：如果Dno是CHAR(20)类型，而查询条件里的值被当作VARCHAR处理，比较时会发生隐式类型转换，B-Tree 索引无法直接利用转换后的值做定位。另一种情况是查询命中的行数占全表比例很高，优化器估算走索引再回表的代价高于顺序扫描，主动放弃索引。

解决：用EXPLAIN ANALYZE看实际执行计划，先确认是哪种情况。类型不一致时在查询条件里显式转换，或者建表时统一类型；选择率过低时考虑组合索引或者重新评估查询条件，单列索引对低选择性字段帮助有限。不要反复REINDEX，那解决不了类型问题。

5.5 两个会话互相锁住：死锁不是数据库挂了，是它在帮你踩刹车

现象：两个终端同时在操作同一张表，其中一个突然报deadlock detected，事务被回滚，另一个则正常完成。

原因：两个事务各持有一把行锁，同时请求对方持有另一把锁，形成循环等待。数据库的死锁检测机制发现循环后，主动中止其中一个事务来打破僵局，保证系统整体可继续运行。

解决：业务逻辑上统一加锁顺序，比如两个事务都按主键升序更新，不要交叉顺序。长事务会拉长锁的持有时间，事务里不要包含用户交互或耗时操作。排查已经发生的死锁时，可以查pg_locks和pg_stat_activity的组合，定位到具体卡在哪张表的哪一行上，而不是盯着报错本身。

6. 备份与恢复：为什么“把数据目录拷一份”从来不是有效备份

物理备份和逻辑备份的取舍，本质上是速度与可移植性的权衡。物理备份直接复制数据文件，速度快，恢复时要求目标环境的数据库版本和架构一致；逻辑备份导出 SQL 或自定义格式文件，速度慢，但可以跨版本恢复，也可以只恢复某张表。只拷数据目录之所以不算有效备份，是因为拷贝过程中数据库仍在运行，不同数据页可能处于不同的写入阶段，拷出来的文件版本不一致，恢复时会遇到数据损坏。

WAL 归档补上了这个缺口。全量物理备份加归档日志，可以在恢复时重放到任意时间点，代价是需要规划日志存储和恢复流程。逻辑备份更简单，但恢复时间随数据量线性增长，而且不适合大规模数据库。我的经验是：小规模开发环境用逻辑备份足够，生产环境至少要有全量物理备份加日志归档两条路径，并且必须定期做恢复演练——备份文件能不能恢复，只有真正恢复过一次才知道。

验证备份有效性有一个笨但可靠的办法：从备份恢复到一个隔离环境，跑一遍关键查询，确认数据完整。不要用“备份命令执行成功”当作结论，那只是流程走完了，不代表数据可用。从那以后我每次做数据变更之前，都强制先验证一次备份的可恢复性，不是把文件拷出来就完事。这份实验报告里把每个实验的现象、原因和结论都按可复现的方式记了下来，下载下来对着系统表和实践步骤走一遍，比只看定义更容易建立底层直觉。希望帮到你。

本文还有配套的精品资源，点击获取