数据库开发技术核心要点：从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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; 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到MVCC4.1 ACID在数据库内核里的落地数据库开发技术课程到事务这里难度突然上升因为要理解的不再是语句而是数据库如何保证一致性。ACID四个性质不是抽象口号原子性靠undolog实现持久性靠redolog隔离性靠锁和MVCC一致性则是前三者协同后的结果。考试题库里常考“事务提交后关机数据是否还在”答案是持久性由redolog保证redolog先于数据页落盘。需要特别注意隐式提交。在MySQL里DDL语句、SET AUTOCOMMIT1时的普通语句都会隐式提交事务。练习中容易观察到一个现象明明两个会话都执行了BEGIN但其中一个会话DDL后数据就出现了。这不是事务没生效而是隐式提交把前序事务提交了。4.2 四种隔离级别能解决什么问题隔离级别解决的问题可以用三个词概括脏读、不可重复读、幻读。脏读是读到其他事务未提交的数据不可重复读是同一查询在不同时间返回不同行数据幻读是同一查询在不同时间返回不同行数。四种隔离级别与这些现象的关系如下隔离级别脏读不可重复读幻读READ UNCOMMITTED可能可能可能READ COMMITTED不可能可能可能REPEATABLE READ不可能不可能可能InnoDB下对某些场景已解决SERIALIZABLE不可能不可能不可能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命令可以把题目里出现的表结构一次性导入MySQLmysql -u test_user -p -h 127.0.0.1 test_db schema.sql其中schema.sql是手动整理的建表语句。导入后对每个查询题先写下自己的SQL再执行EXPLAIN对比预期扫描行数。如果一道题涉及事务就开两个终端模拟并发观察锁等待和隔离级别的影响。遇到不一致的“答案”不要直接否定题库先检查自己的建表语句是否缺少外键或唯一约束。一个更进阶的练习方式是把章节题按知识点打散做成一张自测表每道设计题补充至少一个反例每个反例对应一次WHERE条件变化或约束变化然后在本地执行验证。比如“联合索引最左前缀”的题目就用3列联合索引分别测试只用第2列、只用第3列的情况记录type列从ref变成ALL的变化。这样做的收益是题库里的答案变成了你的实验日志数据库系统本身的报错和执行计划才是最终参考答案。期末题库里的综合题往往涉及学生、课程、选课、教师、学院多张表先用CTECommon Table Expression重写复杂的子查询再对比两个版本的执行计划。MySQL 8.0和PostgreSQL都支持WITH子句这是比“套模板”更值得掌握的技巧。最后把每个实验阶段的建表语句、测试数据和EXPLAIN结果保存成一个markdown文件下次遇到同类问题直接查自己的笔记比翻题库更快。本文还有配套的精品资源点击获取

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

相关推荐

高校学生管理系统课程设计：Delphi+SQL Server实战解析

MMDetection 中的 DetectoRS：递归特征金字塔 RFP 与可切换空洞卷积 SAC 完整解析与实战指南

subfinder免费数据源深度盘点：不注册不付费，快速枚举海量子域名

更多资讯

Babel Compat-Data 深度指南：支撑 @babel/preset-env 插件决策的兼容性数据包

人脸篡改检测三重增强：频域结构+篡改感知数据+时序特征

邮箱验证的正确姿势：从RFC 5322语法到实战代码

一人工作室微信小游戏开发：AI辅助编程实战与效率提升指南

deepseek-harness Web Seam 契约精简实录：一次针对“无人消费字段“的接口裁剪决策

wlanapi.dll 丢失损坏怎么办？安全修复与数字签名验证全指南

今日更新

校园网认证计费模式详解：AAA与代拨架构、原理及排障

Spring Boot+Vue垃圾分类毕设：分层、权限与排错实战

把 Cline 的 Base URL 改到 TaoToken 之后，DeepSeek 仍能修调试配置

本周精选

AI驱动的自动化专利分析平台技术解析

LeetCode-Go 题解：1252. Cells with Odd Values in a Matrix（奇数值单元格计数）双解法剖析

在线答疑系统Java毕设实战：Spring Boot前后端分离与状态流转

本月热门

基于YOLO的焊缝缺陷检测：从数据标注到工业部署全流程实践

分布式服务的决策记录

创业产品的首版范围

读完文章，想聊聊您的网站？