数据库管理系统课程实战指南：从索引设计到事务并发控制

简介：悉尼大学数据库管理系统课程资料包，对应COMP9120课程，面向需要系统学习数据库原理的本科生、自学者及备考者，可帮助补充课堂讲义与练习资源。资料以DBMS核心知识为框架，整理每周讲义、课堂作业与参考答案，内容覆盖概念建模、关系模型、关系代数与SQL、复杂查询、完整性约束、事务与并发控制、规范化、存储与索引、查询处理等主题，并附有综合复习资料。全包共65个文件，以39个PDF讲义和习题为主，另有7个SQL脚本用于建表与查询练习、2个PPTX课件及4个ZIP压缩包，整体大小17.04MB；目录按week1至week13分模块存放，便于按主题查找。目前已有192人浏览学习，下载后既可结合每周讲义梳理知识体系，也可借助作业答案和SQL脚本动手实践，针对ACID事务特性、范式分解、查询优化等重难点做专项巩固，适合期末复习与实验参考。

1. 一门 Database Management System 课程，为什么值得你花一整学期死磕

如果只是为了拿学分，这篇笔记完全没有必要看。但如果你是想在简历上写“懂数据库底层”而不是只会写 SELECT 的开发者，这门某海外高校开设的 Database Management System 课程可能是你整个学期里性价比最高的投入。很多人一开始把它当成 SQL 语法课，跟着学完才发现：真正的课纲里藏着存储引擎、索引结构、事务与并发控制、故障恢复这些区分“会用”和“懂原理”的内容。这篇笔记不谈书单，直接讲怎么把课程拆成可落地的学习路线、实验怎么做、参数怎么定、哪里最容易翻车。新手能照抄步骤，熟手也能在这里找到边界。

2. 课程主线拆解：从关系模型到事务管理的知识地图

2.1 别把数据库和 DBMS 混为一谈：这门课真正的研究对象

课程第一周最容易出现的理解偏差，就是把“数据库”和“数据库管理系统”当成同一个东西。数据库是结构化数据的集合，就好比硬盘上那堆数据文件；数据库管理系统才是你实际接触的那个软件系统，负责把读写请求转成对文件的操作，并处理并发、权限、崩溃恢复这些内务。

这门课真正的研究对象是后者，也就是 DBMS 的实现机制。上课时会反复提到关系模型、关系代数、元组、属性这些术语，它们不是单纯的概念背诵，而是用来描述“系统如何理解用户请求”的形式化工具。我第一遍学的时候跳过关系代数直接写 SQL，结果后面看执行计划完全看不懂，才回头补课。关系代数里那些 select、project、join 操作，其实就是 SQL 语句背后的运算模型。你只有先理解“要做什么”，才能理解优化器“怎么做”的取舍。

从实现者的角度去理解这门课还有个额外好处：很多面试题随便翻翻文档也能背，但一旦追问到“为什么这里要用 B+ 树而不是哈希索引”“为什么多版本并发控制能避免写锁阻塞”，没有系统性学过 DBMS 实现的人只能靠零碎博客拼凑。这门课把系统拆成引擎层、存取层、事务层三层，每层都有明确职责，后续所有实验和考试题基本都围绕这条主线展开。

所以我的建议是：不要跳到中间开始学。开学的头两周老老实实把关系模型和 SQL 的映射关系过一遍，这决定了你后面能不能自己推导执行计划，而不是只会看结论。

2.2 课程模块的轻重与连贯逻辑：存储、索引、事务、恢复

把一门 Database Management System 课程按周拆开，通常能看到这样几个模块，按连贯逻辑排列。

如果是期末闭卷考试，存储和索引往往占比最大，因为这两块贴近系统底层，书面考试最方便考察细节。事务和恢复次之，因为它们有一个前置要求：理解并发环境和故障场景下数据可能处在什么状态。

我在课程中期踩过一个坑：以为索引就是“多建几个就能快”。结果作业里某个查询建了五个单列索引，执行计划反而走全表扫描。后来才明白，优化器是否选择索引取决于数据分布、回表代价和查询条件的选择率。这个点课程里会专门用一个小实验来讲，实操价值很高。如果你跟的课纲里还没讲到这一节，提前预习不会亏。

2.3 作业项目的两种常见形态：从零写一个教学数据库 vs 基于成品调优

很多 Database Management System 课程配的实验不止是写 SQL，而是拿一个简化版的数据库工程让你动手。常见的形态有一整套“从零实现”型项目：给你一批页文件读写接口，要求你实现 B+ 树的插入分裂、事务的日志恢复，或者做一个支持并发控制的小型存储引擎。这种项目初期最费时间的地方不是算法，而是理解工程骨架的调用关系，经常要从数据库的一层追到另一层看数据怎么流转。

另一类常见形态是“调优型”项目：在真实 DBMS 里导入一批数据，通过慢查询日志找出性能瓶颈，再靠索引、物化视图、查询重写把一个场景优化到指定指标。这种形态更接近生产环境，适合那些不打算做数据库内核方向、但想加深对数据库使用边界的同学。

我的看法是：两轮作业最好都做。第一轮从零写能补实现细节，第二轮调优能补参数感知。很多人在第一轮写完就满足了，但第二轮才是面试里最容易被追问的亮点，比如“你怎么定位慢查询”“你用了什么隔离级别，为什么”。如果课纲里两类都有，时间分配上建议把从零实现型作业放在前段，调优型放在考试复习前，这样调优阶段学到的执行计划知识正好能覆盖期末重点。

3. 从零跟课：先把本地环境搭到能跑通最小命令

3.1 选型：课程用什么版本，你就用什么版本，别自作聪明

跟课最大的环境陷阱，就是拿到一个和你本地数据库版本差异很大的教学环境。我见过有同学用某最新版本的数据库软件跑课程自带的存储过程，结果语法报错，折腾两天发现是版本行为变化。课程页面如果没有明确指定版本，我一般会先看实验手册里使用的建表语法和事务控制语句，这能直接判断出大版本方向。

这里给出一个最常见的组合选择：如果课程基于 Linux 虚拟机或远程教学机，优先选 PostgreSQL。理由很简单：PostgreSQL 对 SQL 标准支持比较完整，默认提交行为和事务隔离级别的边界清晰，和课程讲义里讲到的实现机制能对得上。MySQL 也没问题，但注意它的默认存储引擎 InnoDB 和 SQL 模式的差异，有些隐藏行为容易干扰实验结论。

如果课程要求你自己实现存储层结构，SQLite 反而是最省心的底盘，因为它提供的是嵌入式库、没有独立服务端进程，你可以在事务回调里做断点观察。我以前在某个模拟项目X里就是基于 SQLite 的虚拟文件系统做页管理实验，逻辑清晰多了。

配环境前先记住关键参数：字符集、默认隔离级别、autocommit 开关。这三个值决定了你复现课程实验结果时的行为一致性，后面写作业时这些参数全都会用到。

3.2 搭环境：从安装到建库，跑通第一组教学命令

第一步在白板上确认可执行的命令集合。以下是我在一台全新 Linux 环境上跑通的最小步骤。

# 安装数据库服务端与客户端（常见发行版包管理器命名略有差异） sudo apt-get install postgresql postgresql-client # 启动服务并确认进程监听状态 sudo service postgresql start sudo pg_isready # 用系统用户切换到数据库超级用户，创建自己的库 sudo -u postgres createdb studydb # 进入交互式命令行 psql -U postgres -d studydb

逻辑说明：pg_isready用来检查服务是否接受连接，它返回accepting connections才说明服务端正常。createdb是建库命令，等价于在 psql 里执行CREATE DATABASE。很多人卡在登录认证上，是因为默认 peer 认证要求当前操作系统用户和数据库用户名一致，所以第一行用了sudo -u postgres。

参数说明：-U指定数据库用户名，-d指定数据库名。如果你在远程连接，还需要补-h主机地址和-p端口。初学阶段我强烈建议全部走本机 unix socket，不要急着配网络监听，省得把权限和防火墙问题混进来。

进入 psql 后再跑下面这段 SQL，确认数据库可用。

CREATE TABLE course_note ( id SERIAL PRIMARY KEY, week INTEGER NOT NULL, content TEXT ); INSERT INTO course_note (week, content) VALUES (1, '理解 DBMS 分层架构'); SELECT * FROM course_note;

逻辑说明：SERIAL是自增整数类型，PostgreSQL 会自动创建一个序列；PRIMARY KEY会触发唯一索引的创建，这也意味着如果你后面做索引实验，这张表自带一个主键索引可以观察执行计划。整条链路跑通后，说明环境能完成最基本的 DDL、DML、DQL 操作。

3.3 第一个 mini 项目的起手式：用命令行 SQL 模拟一张订单表

课程大多会在第二周给一个模拟业务场景，让你设计表和查询。不要急着直接在 Workbench 或 DBeaver 里拖拖拽拽，先在命令行把表结构和关联关系敲出来，这样执行计划和报错信息都更直观。

-- 模拟客户表与订单表，注意外键和索引的落点 CREATE TABLE customers ( customer_id INT PRIMARY KEY, name TEXT NOT NULL, region TEXT ); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT REFERENCES customers(customer_id), order_amount NUMERIC(10,2), order_date DATE ); CREATE INDEX idx_orders_customer ON orders(customer_id); CREATE INDEX idx_orders_date ON orders(order_date); -- 关联查询：每个客户的订单总金额 SELECT c.customer_id, c.name, SUM(o.order_amount) AS total_amount FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.name HAVING SUM(o.order_amount) > 100;

逻辑说明：REFERENCES在 PostgreSQL 里默认会创建外键约束，而外键约束是否走索引取决于子表和父表的访问顺序。这里我额外在orders(customer_id)上手动建索引，是因为关联查询里被驱动表的连接列常常被优化器当作扫描重点。HAVING是在分组后做过滤，不能用WHERE代替，顺序上要注意先分组再聚合过滤。

参数说明：NUMERIC(10,2)表示十进制数保留两位小数，比FLOAT更适合金额场景。ORDER_DATE用DATE类型而不是字符串，主要原因是后续日期函数和索引范围查询对类型敏感。

做这个项目时养成一个习惯：每张表建完都立刻EXPLAIN看一次插入和查询计划。这个动作花不了几秒钟，但能让你清晰地看到"哪些地方走了索引、哪些地方在排序、哪些地方在回表"。我第一周就把这个姿势固定下来了，后面排查慢查询省了非常多时间。

4. 关键参数与核心操作：join、执行计划、事务隔离级别怎么落地

4.1 连表查询：join 类型的取舍与空值陷阱

连表查询是课程实验里最常见的失分点，主要坑在两个地方：一是忘记ON条件之外的过滤条件，二是空值参与连接导致结果行数异常。

-- 用 inner join 找出同时有订单的客户 SELECT c.name, o.order_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id; -- 用 left join 找出所有客户以及他们是否下过单 SELECT c.name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.order_date >= '2025-01-01';

逻辑说明：第二个查询特别容易写错。如果想把关联条件和过滤条件分开处理，AND o.order_date >= '2025-01-01'写在ON里可以保留没有订单的客户；如果把它挪到WHERE里，LEFT JOIN的效果会退化成INNER JOIN。这是因为WHERE子句在连接产生结果后才过滤，空值那一行会被过滤掉。

参数说明：连接顺序也是优化器决定的一部分，但如果你用LEFT JOIN，左表和右表的语义就固定了。实际实验里如果要强制连接顺序理解执行计划，可以用SET LOCAL enable_hashjoin = off;让优化器临时改走嵌套循环连接，只要别忘了在当前事务结束前重置。

做实验时我建议每次 join 都记录返回行数，和纸面上手工推导的行数做对比。行数对不上，八成不是 join 语法问题，而是空值或重复关联造成的。这是最划算的排错顺序。

4.2 索引设计：不读执行计划，调索引就是玄学

很多同学以为索引建得越多越心安。实际上，当你对一张几万行的表连续执行下面这种查询时，执行计划会告诉你真相。

-- 初始状态：没有任何辅助索引 EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE order_date BETWEEN '2025-02-01' AND '2025-02-28'; -- 观察到的计划可能是 Seq Scan on orders (cost=0.00..450.00 rows=5200 width=...)

执行计划里出现Seq Scan，说明数据库在做全表扫描。如果order_date上已经建了索引，优化器仍然可能选全表扫描，这通常是因为返回行数占表总行数的比例太高，走索引还要大量回表，代价反而更大。这是一个很容易被误解的参数：索引不是越大越快，而是选择性越高越值得建。

我一般会按这个顺序排查慢查询。第一步，看EXPLAIN的开销估算，尤其注意rows是否和实际值差别很大，如果相差几十倍，说明统计信息过期，先跑ANALYZE更新统计。第二步，看有没有Sort或Hash Join耗费大量内存，很多时候查询慢不是因为索引缺了，而是排序和临时文件在拖后腿。

-- 建一个真正符合查询条件的复合索引，再对比执行计划 CREATE INDEX idx_orders_date_region ON orders(order_date, region); EXPLAIN (ANALYZE) SELECT region, COUNT(*) FROM orders WHERE order_date BETWEEN '2025-02-01' AND '2025-02-28' GROUP BY region;

参数说明：复合索引的列顺序很重要。这里把order_date放前面，是因为查询里它是一个等值或范围过滤条件，region放后面刚好覆盖分组字段，能形成索引覆盖扫描，减少回表。把顺序反过来，索引对这条查询就没有正收益了。建完索引立刻重跑EXPLAIN，观察计划从Seq Scan变成Index Scan或Index Only Scan，这才是有效调优。

4.3 事务隔离级别：读已提交 vs 可重复读的行为差异

事务隔离级别是课程实验里最直观的并发控制演示。PostgreSQL 支持读已提交、可重复读、可串行化三种，而已提交读和可重复读是最常见的对比对象。

写实验时，用两个 psql 会话模拟并发行为最方便。会话 A 开启事务并查询，会话 B 修改并提交，然后回到会话 A 再查一次。

-- 会话 A BEGIN; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SELECT amount FROM accounts WHERE id = 1; -- 会话 B（另一个会话执行） UPDATE accounts SET amount = amount + 50 WHERE id = 1; COMMIT; -- 回会话 A 再次查询，会自动看到会话 B 提交的改动 SELECT amount FROM accounts WHERE id = 1; COMMIT;

逻辑说明：读已提交隔离级别下，事务内每一条语句都会获得新的快照，所以第二次查询能看到别人提交的改动。如果把隔离级别改成REPEATABLE READ，整个事务只取一次快照，第二次查询结果和第一次保持一致，这正是不可重复读和可重复读的差别所在。

参数说明：SET TRANSACTION必须在BEGIN之后、事务内第一条语句之前执行。PostgreSQL 里可重复读默认不会产生幻读，因为它用快照机制替代了传统数据库的锁行为。你在课程实验里观察到行为差异，不是配置写错，而是不同数据库的实现策略不一样。做实验时最好把两个会话的SHOW transaction_isolation;也记录下来，防止自己忘记当前上下文。

5. 避坑：跟这门课最常见的 5 个翻车现场与排查路径

5.1 现象：本地运行正常，一交到教学环境就报权限错误

原因：本地用的是超级用户，所有表、序列、函数都在自己的 schema 下，而课程教学环境通常使用受限账号，默认搜索路径和权限都不一样。常见报错是permission denied for table orders或relation "orders" does not exist。

解决：提交作业前，用课程环境提供的账号执行一次完整建表脚本，确认模式和权限。我习惯在脚本开头显式加CREATE SCHEMA IF NOT EXISTS course; SET search_path TO course;，把表和视图都放在同一个 schema 下，避免依赖用户默认的搜索路径。权限问题用GRANT ALL ON ALL TABLES IN SCHEMA course TO 你的账号;一次性处理。

5.2 现象：连接查询很慢，加了索引反而更快走全表扫描

原因：表数据量太小，统计信息显示全表扫描的开销小于索引扫描加回表开销。或者复合索引列顺序和查询条件不匹配，导致优化器认为走索引没有收益。

解决：先ANALYZE更新统计信息，再EXPLAIN看执行计划。如果仍然全表扫描，可以调低随机页读取代价参数random_page_cost，从默认的4降到1.1左右，让优化器更愿意尝试索引路径。这个调整在实验里能直观反映代价模型如何影响最终计划。

5.3 现象：并发事务相互等待，最后报死锁

原因：两个事务各自持有一个锁，又同时请求对方持有的锁。常见场景是循环更新两条记录。比如事务 A 更新表 1 再更新表 2，事务 B 更新表 2 再更新表 1。

解决：统一所有并发事务的加锁顺序。在更新多条记录时，先按主键排序再更新，这样每个事务对资源的访问顺序一致，死锁概率会大幅下降。如果业务上无法统一顺序，设置lock_timeout参数让等待方快速失败，在实验里明确记录死锁重试的日志。

5.4 现象：做故障恢复实验后，数据行数莫名其妙变少

原因：混淆了崩溃恢复和回滚的概念。有些实验使用ROLLBACK模拟掉电恢复，但真实的 WAL 恢复会重放已提交日志，未提交事务的修改不一定保留。数据变少可能只是因为恢复时选择恢复到某一个检查点，丢弃了检查点之后的非持久化改动。

解决：先画一条时间轴，从事务开始、预写日志、提交、检查点这几个节点标出每个事务的状态，再看日志里哪些事务标记为已提交。只要提交标记在检查点之后，恢复时就应该能重放。排查命令可以查pg_walfile_name()或对应日志文件，结合pg_stat_activity确认当前实例的状态。

5.5 现象：讲义看得懂，但书面作业一写就漏条件

原因：书面作业要求手写关系代数表达式或推演 B+ 树操作步骤，和写 SQL 是两套能力。写 SQL 时数据库会替你优化，手写时得自己维护所有中间结果和边界条件。

解决：每周抽二十分钟做纸面推演。我会准备一个表格，左边写输入表的状态，右边写每一步关系代数操作后的输出表。这样能倒逼你把select、project、join的先后顺序理清楚。考前复习也只需要翻纸面推演表，比翻代码效率高得多。

6. 把课程资产转成本地可复习工程：一个每周复盘脚本的落地

课程结束之后最有价值的不是考试分数，而是你留下的一套可复现笔记。我习惯在每周实验做完之后跑一个小脚本，把心得和踩坑记录整理成固定结构的复习卡。

import pathlib import datetime notes_dir = pathlib.Path("dbms_weekly_notes") notes_dir.mkdir(exist_ok=True) week = 4 topic = "索引与执行计划" key_points = [ "EXPLAIN ANALYZE 看 rows 与实际行数偏差", "random_page_cost 调整对计划的影响", "复合索引列顺序优先左前缀" ] puzzle = "哈希索引为什么不适合范围查询" card = f"""主题：{topic} 日期：{datetime.date.today()} 本周要点： "" {pull_index} "" 待澄清：{puzzle} """ card_file = notes_dir / f"week_{week:02d}_{topic.replace(' ', '_')}.md" card_file.write_text(card, encoding="utf-8") print(f"written: {card_file}")

逻辑说明：脚本用pathlib自动创建目录，生成带序号和主题的文件。key_points里的每一项追加进卡片，避免只记录代码而丢失当时的思考过程。我的教训是，第一轮学习时只贴代码不写心得，两周后再看根本想不起来为什么这么调参数。

这个技巧带来的额外好处是：期末复习时，你不再需要从几百行 SQL 里回忆当时的实验目标，只要浏览卡片就能把每个模块的要点、坑点、突破口串起来。我会在每张卡片末尾留一个“待澄清”字段，遇到暂时卡住的问题先记下来，这周过去再回头验证，很多当时不明白的索引行为后来都在新实验里得到了解释。

真正让我把学到的 DBMS 知识留到今天的，不是最后一次突击复习，而是每周一次不到十分钟的复盘动作。希望这套方法也能帮你把课程内容沉淀成自己的工程资产。

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