数据库开发技术核心要点：从ER模型到索引与事务优化

简介：一份配套南京大学中国大学MOOC《数据库开发技术》2023年课程的课后章节答案与期末考试题库，面向选课学生、数据库初学者及备考者，可用于考前自测、知识点查漏补缺和重点复盘。题库以选择题形式覆盖索引管理、SQL查询与数据类型、多表查询与优化、并发控制与MVCC、性能调优和数据库架构等核心模块，具体涉及MyISAM不支持hash索引、性别等低基数列宜用位图索引、CAST与concat函数细节、LEFT JOIN补查缺失数据、多表查询需为关联表合理建索引、高并发下幻读/脏读/丢失更新问题，以及Oracle与MySQL隔离级别差异、软解析/硬解析、死锁、ORM工具差异等考点，并附参考答案。这些题目不仅帮助记忆结论，还能纠正存储引擎选型、范式打破、分布式部署等方面的常见误解。压缩包内为1个docx文档，体积仅15KB，内容精炼、便于考前集中浏览；文档按知识点汇总，在版式上接近题库速览，适合快速刷题和核对关键结论。已有125人学习下载，适合需要集中突击数据库开发技术课程期末考试的学习者。

1. 数据库开发技术这门课到底在练什么

很多人把“数据库开发”等同于“会写增删改查”，但真正把课后题和期末考试题库做一遍就会发现，卡住自己的从来不是SQL语法，而是表结构怎么设计、索引为什么失效、两个事务到底怎么互相影响。这门课的价值不在于背会某道题的答案，而在于让你在本地把每个知识点变成可运行、可验证的实验。本文以南京大学在中国大学MOOC平台开设的《数据库开发技术》课程大纲为参照，沿着设计、查询、事务、存储过程、题库自测这条线，把数据库开发里最容易被忽略的边界和参数讲清楚，每个章节都有能直接执行的SQL和命令。

2. 从ER模型到建表语句：把业务翻译成关系

2.1 实体-联系模型的关键取舍

写建表语句之前，先把业务翻译成ER图，这一步决定了后续所有SQL的性能边界。实体是名词，联系是动词，属性是实体或联系上的限定描述。常见误区是恨不得把所有属性都塞进一张表，结果数据冗余、更新异常。比如“学生选课”这个场景，学生有姓名、邮箱，课程有名称、学分，学生和课程之间是多对多联系，联系本身还有“学期”“成绩”属性，就必须拆成三张表。

如果直接从某道MOOC课后题里拿到一段文字描述，第一件事是圈出名词和动词，再判断基数。一门课可以有多名学生修读，一名学生也可以修多门课，所以学生和课程之间是“多对多”，中间联系表的主键通常是“学生ID+课程ID+学期”。判断错了，后面的外键和查询都会带着设计缺陷走。

2.2 用标准SQL实现三范式表结构

三范式不是理论装饰，而是用来消除更新异常的检查清单。第一范式要求列不可再分，第二范式要求非主属性完全依赖于主键，第三范式要求非主属性不传递依赖于主键。实际开发里最常见的是违反第二范式：把课程名称直接放进选课表，一旦课程改名，就要UPDATE多行，还可能漏改。

CREATE TABLE student ( student_id INT NOT NULL, name VARCHAR(50) NOT NULL, email VARCHAR(100), enroll_year SMALLINT, PRIMARY KEY (student_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE course ( course_id INT NOT NULL, title VARCHAR(100) NOT NULL, credits TINYINT NOT NULL, PRIMARY KEY (course_id) ); CREATE TABLE enrollment ( student_id INT NOT NULL, course_id INT NOT NULL, term VARCHAR(20) NOT NULL, grade DECIMAL(3,1) NULL, PRIMARY KEY (student_id, course_id, term), CONSTRAINT fk_enr_student FOREIGN KEY (student_id) REFERENCES student(student_id), CONSTRAINT fk_enr_course FOREIGN KEY (course_id) REFERENCES course(course_id) );

这段建表代码有三个要点。主键用业务自然键还是代理键，这里选的是“学生ID+课程ID+学期”作为联合主键，因为同一个人在同一学期重复修同一门课在业务上不允许。外键约束必须显式命名，比如fk_enr_student，这样后续DROP或修改约束时不需要猜数据库自动生成的名字。DECIMAL(3,1)用来存成绩，允许NULL，表示还没出分。

2.3 外键约束与级联动作的坑

外键不是摆设，但在分库分表或高频写入场景下，外键约束会成为性能瓶颈。课程题目里经常问“删除学生时选课记录怎么办”，这对应ON DELETE的四个选项：CASCADE、SET NULL、RESTRICT、NO ACTION。如果学生注销后保留成绩审计，就不能CASCADE；如果只是临时禁用，可以加一个status字段来软删除，而不是物理DELETE。

实际练习时，可以在建表语句末尾追加“ON DELETE SET NULL”看效果，但前提是选课表里的student_id列要允许NULL。很多人在这里踩坑：定义外键时发现NOT NULL约束与SET NULL冲突，MySQL直接报错。所以设计外键之前先决定好业务语义，再决定列是否可为空，顺序不能反。

3. 索引与查询优化：SQL慢不是SQL的错

3.1 索引选择性与回表代价

索引是数据库开发里“知道和做对”差距最大的知识点。索引选择性的定义是：不重复的索引值数量除以总行数，越接近1越好。比如性别字段只有两个值，选择性是0.0001，建立索引后扫描范围仍然很大，优化器大概率放弃索引。而学生ID或邮箱这类字段选择性高，索引收益明显。

另一个常被忽略的概念是回表。二级索引保存的是主键值和索引列，查询列如果不在索引里，找到主键后还要再到主键索引树里取整行数据，这一步叫回表。回表次数多，性能可能比全表扫描更差。所以“只要WHERE里有列就建索引”是错误的，还要看SELECT的列是否都在索引内。

3.2 用EXPLAIN定位全表扫描

本地验证索引是否生效最直接的手段是EXPLAIN。以下语句针对选课场景查看查询计划：

EXPLAIN SELECT s.name, c.title FROM enrollment e JOIN student s ON e.student_id = s.student_id JOIN course c ON e.course_id = c.course_id WHERE e.term = '2023-2024-1';

重点关注type列。如果看到ALL，说明发生了全表扫描；看到ref或eq_ref，说明索引被有效使用；看到index，说明扫描的是索引树但也是全索引扫描。key列显示实际用到的索引名，rows列是优化器估计扫描的行数。这里如果term选择性不高，优化器可能选择先全表扫描enrollment，再逐行去关联两张表。

要解决这个查询的潜在问题，可以建立联合索引。注意联合索引的列顺序：等值查询放在前面，范围查询放后面。如果term常用于筛选，同时查询要回表取title和name，可以考虑覆盖索引：

CREATE INDEX idx_enr_term_student_course ON enrollment(term, student_id, course_id);

这个索引把term、student_id、course_id都放进去，查询时只需要扫描这个索引树就能拿到关联所需的主键，不需要再回表读enrollment整行。但代价是写入时需要维护更大的索引树，所以不是索引越多越好。

3.3 覆盖索引与最左前缀原则

覆盖索引指查询需要的所有列都包含在同一个索引中。上面这个索引对于“按term查学生和课程ID”的查询就是覆盖索引。最左前缀原则说的是：当联合索引包含三个列(a,b,c)时，查询条件只有b和c时无法使用该索引，因为索引从左到右排列，跳过a就无法定位b的区间。

MySQL 8.0支持跳跃扫描，部分场景下能绕过最左前缀，但不要依赖它。手写练习题时，验证方法很简单：用EXPLAIN看Extra列是否出现“Using index”，出现就表示覆盖索引生效，没有则说明回表了。Extra列如果出现“Using where; Using index”，表示索引用来过滤了WHERE条件，并且不需要回表，这是比较理想的状态。

4. 事务隔离级别与并发控制：从ACID到MVCC

4.1 ACID在数据库内核里的落地

数据库开发技术课程到事务这里，难度突然上升，因为要理解的不再是语句，而是数据库如何保证一致性。ACID四个性质不是抽象口号：原子性靠undolog实现，持久性靠redolog，隔离性靠锁和MVCC，一致性则是前三者协同后的结果。考试题库里常考“事务提交后关机，数据是否还在”，答案是持久性由redolog保证，redolog先于数据页落盘。

需要特别注意隐式提交。在MySQL里，DDL语句、SET AUTOCOMMIT=1时的普通语句都会隐式提交事务。练习中容易观察到一个现象：明明两个会话都执行了BEGIN，但其中一个会话DDL后，数据就出现了。这不是事务没生效，而是隐式提交把前序事务提交了。

4.2 四种隔离级别能解决什么问题

隔离级别解决的问题可以用三个词概括：脏读、不可重复读、幻读。脏读是读到其他事务未提交的数据，不可重复读是同一查询在不同时间返回不同行数据，幻读是同一查询在不同时间返回不同行数。四种隔离级别与这些现象的关系如下：

MySQL默认是REPEATABLE READ，这与其他数据库如PostgreSQL默认READ COMMITTED不同。原因在于InnoDB使用间隙锁在RR级别下解决了一部分幻读问题，但仍然存在“先快照后当前读”的场景，需要结合下一小节验证。

4.3 用代码复现脏读和幻读

两个终端窗口是验证隔离级别的最好工具。打开终端A和终端B，连接同一个MySQL实例，把隔离级别调成READ UNCOMMITTED：

-- 终端A SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; UPDATE course SET credits = 3 WHERE course_id = 1; -- 终端B（此时A未提交） SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; SELECT credits FROM course WHERE course_id = 1;

终端B如果读到3，而终端A还没有COMMIT，这就是脏读。把隔离级别改成READ COMMITTED后再执行同样的动作，B会读到旧值，因为READ COMMITTED每次SELECT都会生成新的快照，但不会读取未提交的数据。

幻读复现有一个关键点：使用当前读语句。在REPEATABLE READ下，普通SELECT是快照读，不会看到幻影行；但如果有另一个事务插入了新行，本事务再执行“SELECT … FOR UPDATE”，就会突然多一行。所以题目里说“RR解决了幻读”是不完整的，准确说法是InnoDB通过next-key lock解决了部分当前读的幻读，快照读下不会看到但并发插入之间可能产生死锁。

5. 存储过程与触发器：业务逻辑该不该放进数据库

5.1 存储过程的适用边界

存储过程在课程里往往作为重点考察，但实际开发中不少团队刻意少用。原因是存储过程把业务逻辑隐藏在数据库内部，版本管理困难，测试链路变长。不过对于强一致性要求、涉及多次SQL交互、且数据库是唯一状态源的场景，存储过程仍然有价值。比如学生选课要校验前置课程、判断学分上限、插入选课记录，这三步如果放在应用层，任何一步失败都可能留下脏数据。

适用边界很清楚：需要多条SQL保证原子性，且不适合在应用层开事务时，用存储过程。需要复杂批量统计、定期任务，用存储过程也比应用层逐个调用快。需要做权限控制的旧系统，存储过程能隐藏表结构。除此之外，优先在应用层用ORM或数据访问层写逻辑，把数据库留给数据和约束。

5.2 一个带事务的存储过程示例

下面这个存储过程处理“学生选课”业务，包含前置课程校验和插入动作，并返回状态码。注意要在MySQL中支持条件语句，需要先重定义分隔符：

DELIMITER // CREATE PROCEDURE enroll_student( IN p_student_id INT, IN p_course_id INT, IN p_term VARCHAR(20), OUT p_status INT ) BEGIN DECLARE v_prereq INT DEFAULT 0; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_status = -2; END; START TRANSACTION; SELECT COUNT(*) INTO v_prereq FROM enrollment WHERE student_id = p_student_id AND course_id = (SELECT prerequisite_id FROM course WHERE course_id = p_course_id) AND grade >= 60; IF v_prereq = 0 THEN ROLLBACK; SET p_status = -1; ELSE INSERT INTO enrollment(student_id, course_id, term, grade) VALUES (p_student_id, p_course_id, p_term, NULL); COMMIT; SET p_status = 0; END IF; END // DELIMITER ;

这段代码的要点是：使用局部变量v_prereq记录前置课程成绩合格的数量，为0说明没通过。EXIT HANDLER捕获任何SQL异常，自动回滚并返回-2。输出参数p_status用于应用层判断结果，比直接返回结果集更清晰。

需要注意的是，处理过程中如果子查询prerequisite_id为NULL，即该课程没有前置课，SELECT COUNT(*)的结果会是0，导致永远选不上课。实际业务里要先判断prerequisite_id是否为空，这里故意保留了这个小陷阱，用来提醒测试边界值。调用方式如下：

CALL enroll_student(1001, 2023, '2024-2025-1', @status); SELECT @status;

5.3 触发器与数据一致性

触发器适合强制一致性的简单规则，但不要在里面做复杂的查询或更新。常见的课后题是“插入选课记录时自动更新学生已选课程数”，可以用AFTER INSERT触发器实现：

CREATE TRIGGER trg_enrollment_after_insert AFTER INSERT ON enrollment FOR EACH ROW BEGIN UPDATE student SET course_count = course_count + 1 WHERE student_id = NEW.student_id; END;

这段代码的问题是：如果student表没有course_count字段，需要先ALTER TABLE加字段。而且AFTER INSERT触发器在数据已经写入enrollment后执行，如果UPDATE失败，整个事务会回滚，所以仍然能保证一致性。但要警惕递归触发：UPDATE student又触发其他触发器，导致连锁更新。

我一般建议把触发器当成“最后一道防线”，而不是主业务逻辑。因为它不可见，排错时很难从应用日志追踪到触发器内部的UPDATE。考试题库里常考“触发器和存储过程的区别”，最简洁的回答是：存储过程是显式调用，触发器是隐式触发；存储过程可以有参数和返回值，触发器没有；触发器与表绑定，删表即删触发器。

6. 用MOOC题库自测：把章节题变成本地练手靶场

拿到了课后章节答案和期末考试题库，如果只是背下来，遇到环境变化仍然不会。正确用法是把每一道设计题或查询题改写成可在本地执行的SQL脚本，再准备一个测试库反复验证。下面这个bash命令可以把题目里出现的表结构一次性导入MySQL：

mysql -u test_user -p -h 127.0.0.1 test_db < schema.sql

其中schema.sql是手动整理的建表语句。导入后，对每个查询题先写下自己的SQL，再执行EXPLAIN对比预期扫描行数。如果一道题涉及事务，就开两个终端模拟并发，观察锁等待和隔离级别的影响。遇到不一致的“答案”，不要直接否定题库，先检查自己的建表语句是否缺少外键或唯一约束。

一个更进阶的练习方式是把章节题按知识点打散，做成一张自测表：每道设计题补充至少一个反例，每个反例对应一次WHERE条件变化或约束变化，然后在本地执行验证。比如“联合索引最左前缀”的题目，就用3列联合索引分别测试只用第2列、只用第3列的情况，记录type列从ref变成ALL的变化。这样做的收益是，题库里的答案变成了你的实验日志，数据库系统本身的报错和执行计划才是最终参考答案。

期末题库里的综合题往往涉及学生、课程、选课、教师、学院多张表，先用CTE（Common Table Expression）重写复杂的子查询，再对比两个版本的执行计划。MySQL 8.0和PostgreSQL都支持WITH子句，这是比“套模板”更值得掌握的技巧。最后把每个实验阶段的建表语句、测试数据和EXPLAIN结果保存成一个markdown文件，下次遇到同类问题直接查自己的笔记，比翻题库更快。

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