简介面向学习数据库系统课程的高校学生与自学者这份资料对应悉尼大学 COMP9120 数据库管理系统课程聚焦从概念建模、关系模型与关系代数到 SQL 编写、事务与并发控制、规范化设计、存储索引和查询处理等核心主题适合课程同步学习、实验对照与期末复习。包内共 65 个文件以 39 份 PDF 讲义和习题解答、7 个 SQL 脚本、4 个代码压缩包及 2 份 PPT 为主整体约 17.04MB目录按 week1 至 week13 展开涵盖讲义、辅导与复习材料便于快速定位薄弱环节。讲义与 tutorial 习题答案逐周配套SQL 脚本可用于建库、插入数据并验证复杂查询代码压缩包内含 Java、Python 数据库应用练习能帮助读者把事务的 ACID 特性、完整性约束、三值逻辑等抽象概念落实到可运行代码中也便于复盘 ER 建模与规范化步骤。已有 193 人学习适合需要系统梳理课程知识、对照习题查漏补缺的读者。1. 一份建库脚本跑三遍才成功这套数据库管理系统课程资源到底给谁用数据库管理系统课程的资料最大的问题从来不是内容少而是散。课件、ER 图、几段孤立的 SQL、一份实验要求文档摊在三个文件夹里想跑通得自己拼。这套资源是按工程方式组织的一组能直接 source 的建库脚本一套带主外键约束和种子数据的表结构一批围绕选课场景写的查询、事务与索引实验外加一层很薄的应用代码把 SQL 从命令行搬到程序里。它的适用对象很明确——正在上数据库课、要交实验但不想在环境上耗掉一周的学生以及已经工作、想把范式、隔离级别、执行计划这些概念重新落地一遍的后端开发。整套东西不依赖任何商业组件一台装了 MySQL 的机器加一个 Python 解释器就能从头跑到尾中间不需要联网下载数据。2. 工程全景技术栈选型与目录结构逐层拆解2.1 技术栈组合MySQL 打底Python 收口这套工程的技术栈没有做炫技式堆叠三个组件各管一段。存储层用 MySQL 8.0理由很直接课程里要演示的东西它全有——InnoDB 的事务与四种隔离级别、行锁与间隙锁、存储过程与触发器、EXPLAIN执行计划、窗口函数。换成 SQLite并发控制基本等于没有锁实验做不出来换成 PostgreSQL 又容易把注意力从课程概念引到方言差异上。应用层用 Python 3 加pymysql依赖只有一条 pip 命令跨平台不用折腾 JDK 和驱动包。工程里另外附带了一份 JDBC 的连接片段留给课程要求必须用 Java 的场合但主线代码不依赖它。组件建议版本在这套工程里承担的职责MySQL8.0 及以上表结构、约束、事务、存储过程、索引Python3.10 及以上应用层脚本、DAO、批量灌数据pymysql与 Python 3 兼容的稳定版MySQL 驱动纯 Python 实现免编译DBeaver 或 MySQL Workbench任意看 ER 图、执行单条 SQL、观察锁等待用版本对照表解释一下为什么卡 8.08.0 才原生支持窗口函数和EXPLAIN ANALYZE而第 4 章的排名查询和 4.3 的执行计划验证都压在这两个特性上。如果你机器上是 5.7工程能跑起来但窗口函数那段会直接报语法错误得改写成自连接版本性能差一截。另外 8.0 默认字符集是utf8mb4中文列不用再手动指定编码这也是省事的一点。连接层不放 ORM 是刻意的。课程的训练目标是让你手写 SQL 并理解它落到执行引擎后发生了什么套一层 ORM 会把生成的 SQL 藏起来索引失效、隐式类型转换这类问题就看不见了。等你把原生 SQL 写顺了第 6 章会讲怎么平滑替换成 SQLAlchemy。2.2 目录结构从 sql/ 到 app/ 的一条单向依赖链工程的目录组织遵循一条原则脚本之间只允许单向依赖不允许回指。sql/里的文件按编号顺序执行后者只依赖前者产出的表app/只依赖sql/执行完之后的库docs/只描述前两者不参与运行。这条规则听起来像教条但它解决的是最常见的一类返工——改了表结构忘了同步种子数据脚本重跑一遍就崩在外键上。course-registry/ ├── sql/ │ ├── 01_schema.sql # 建库建表含主外键与注释 │ ├── 02_seed.sql # 种子数据固定 id 便于断言 │ ├── 03_views.sql # 视图屏蔽多表连接细节 │ ├── 04_procedures.sql # 存储过程与触发器 │ └── 05_indexes.sql # 索引单独放方便做前后对比实验 ├── app/ │ ├── config.py # 连接参数集中管理不硬编码在脚本里 │ ├── db.py # 连接工厂全局唯一的建连入口 │ ├── dao/ │ │ ├── student_dao.py │ │ └── enroll_dao.py │ └── cli.py # 命令行入口跑通链路用 ├── tests/ │ └── test_enroll.py # 针对选课事务的集成测试 ├── docs/ │ ├── er_diagram.png │ └── normalization.md # 范式推导过程答辩时直接用 └── README.md05_indexes.sql单独拎出来是有原因的。做索引实验时你需要一个「加索引前」的基线如果索引和建表写在一起每次对比都得先注释掉再重建库来回十几分钟就没了。单独成文件后加索引就是source一次删索引就是DROP INDEX一条命令做前后对比很顺手。02_seed.sql里的主键全部用固定值比如学号S0000001不用自增。自增看着方便但测试断言里没法写死期望值每次跑完还得先去查一遍 id 才能写断言这是纯粹给自己找事。2.3 环境搭建从零到跑通第一条查询先把库建出来。建库这步最容易翻车的地方是字符集MySQL 8.0 服务端的默认字符集虽然是utf8mb4但如果你本地的my.cnf是从旧机器拷过来的很可能还停在latin1后面中文列一律变问号。所以建库时显式指定不给它默认的机会。# 建库并显式锁定字符集与排序规则不依赖服务端默认值 mysql -u root -p -e CREATE DATABASE IF NOT EXISTS course_registry DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; # 建一个专用账号别用 root 跑应用层代码 mysql -u root -p -e CREATE USER IF NOT EXISTS course_app127.0.0.1 IDENTIFIED BY ChangeMe_2024; GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON course_registry.* TO course_app127.0.0.1; FLUSH PRIVILEGES;utf8mb4_0900_ai_ci里的ai是不区分重音、ci是不区分大小写做姓名、课程名这类模糊匹配时更符合直觉。专用账号只给增删改查和执行存储过程的权限不给DROP这样即使脚本写错也不会把库删掉——这个习惯我是吃过一次亏之后才养成的。脚本必须按编号顺序source顺序错了外键直接报 1215# 顺序执行任何一步失败就停下不要用 串成一行硬跑 for f in sql/01_schema.sql sql/02_seed.sql sql/03_views.sql \ sql/04_procedures.sql sql/05_indexes.sql; do echo $f mysql -u course_app -p course_registry $f || { echo 失败于 $f; exit 1; } done用for循环加|| exit 1而不是用连成一条长命令是因为长命令失败后你只能看到一个笼统的报错行号定位不到是哪个文件出的问题。逐步打印文件名出错的第一现场一眼就能看到。最后验证连接这段代码后面所有脚本都会复用# app/db.py —— 全局唯一的建连入口别在业务代码里到处 connect() import pymysql def get_conn(): return pymysql.connect( host127.0.0.1, port3306, usercourse_app, passwordChangeMe_2024, databasecourse_registry, charsetutf8mb4, # 必须和库表一致否则中文列读出乱码 autocommitFalse, # 事务由代码显式 commit避免隐式提交 cursorclasspymysql.cursors.DictCursor, # 返回 dict按列名取值 ) if __name__ __main__: with get_conn() as conn, conn.cursor() as cur: cur.execute(SELECT COUNT(*) AS n FROM student) print(cur.fetchone())autocommitFalse这一项最关键。Python 驱动默认就是关闭自动提交的但不少人从别的语言转过来下意识以为每次execute都已经落库结果测试里数据查不到排查半天。DictCursor是为了让下游代码按列名取值避免表结构加了一列之后所有按下标取值的代码全部错位。跑通这段输出学生总数环境就算通了。3. 数据模型落地从 ER 图到能直接 source 的建表脚本3.1 实体、关系与范式取舍这套工程用的是选课场景五个实体系、学生、课程、开课班、选课记录。核心关系有两处需要仔细想。第一处是「课程」和「开课班」必须拆开——一门课可以在不同学期、由不同老师、在不同教室反复开如果把学期、年份直接塞进课程表同一门课每开一次就要复制一条课程记录课程标题、学分这些属性跟着重复这是典型的部分依赖违反 2NF。拆成course和section之后section用(course_id, sec_id, semester, year)四列复合主键唯一确定一个班。第二处是选课记录表。严格按 3NF 来看takes表里存semester、year是冗余的因为这两个值可以通过(course_id, sec_id)反查section得到。但这里做了有意的反范式——takes的主键必须包含学期和年份才能唯一约束「同一个学生同一学期只能选同一个班一次」如果只存(student_id, course_id, sec_id)学生跨学期重修同一门课时就会主键冲突。这个取舍在docs/normalization.md里有完整推导答辩时直接引用就行。第三个决策是删除策略。系被删了下面的学生怎么办这里选ON DELETE SET NULL把学生的dept_id置空而不是连人一起删。反过来说学生被删了他的选课记录用ON DELETE CASCADE一起清掉因为孤儿选课记录没有任何查询价值留着只会让统计口径出现偏差。3.2 建表脚本逐段拆解先看头部删表顺序和建表顺序必须相反中间的FOREIGN_KEY_CHECKS开关是给重复执行准备的-- sql/01_schema.sql SET NAMES utf8mb4; -- 告诉服务端本次会话用什么编码解析脚本 SET FOREIGN_KEY_CHECKS 0; -- 暂时关掉外键检查允许乱序删表 DROP TABLE IF EXISTS takes; DROP TABLE IF EXISTS section; DROP TABLE IF EXISTS course; DROP TABLE IF EXISTS student; DROP TABLE IF EXISTS department; SET FOREIGN_KEY_CHECKS 1; -- 立刻打开后面的建表必须受约束保护SET NAMES utf8mb4这一句不能省。命令行客户端读取.sql文件时如果会话编码和服务端不一致文件里的中文注释和中文数据会在解析阶段就被转坏报错信息还很难懂。开关外键检查只在这五行删表语句期间有效紧接着就打开保证后面的建表和外键定义仍然受约束校验。父表先建字段类型必须和子表里的引用列严格一致否则外键创建失败CREATE TABLE department ( dept_id CHAR(4) NOT NULL COMMENT 系编号固定4位, dept_name VARCHAR(60) NOT NULL COMMENT 系名全库唯一, building VARCHAR(20) DEFAULT NULL, budget DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 用定点数不用FLOAT, PRIMARY KEY (dept_id), UNIQUE KEY uk_dept_name (dept_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT系; CREATE TABLE student ( student_id CHAR(8) NOT NULL, name VARCHAR(40) NOT NULL, dept_id CHAR(4) DEFAULT NULL, tot_cred SMALLINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (student_id), KEY idx_student_dept (dept_id), -- 外键列必须建索引否则删父表会锁全表 CONSTRAINT fk_student_dept FOREIGN KEY (dept_id) REFERENCES department (dept_id) ON UPDATE CASCADE ON DELETE SET NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生;budget用DECIMAL不用FLOAT是硬要求。浮点数存金额做汇总统计时会出现0.30000000000000004这种尾数学生答辩演示时被问到很尴尬。CHAR(8)存学号而不用VARCHAR是因为定长类型在 InnoDB 里占位固定做主键和连接时比较开销更小而且学号本来就长度固定。idx_student_dept这个索引容易被忽略。MySQL 建外键时会自动给子表的引用列建索引但如果你自己写了KEY它会复用。显式写出来的好处是可读——后面做慢查询优化时能一眼看出哪些列已经有索引不用去SHOW CREATE TABLE里翻。接着是开课班和选课记录复合主键和复合外键都在这里CREATE TABLE section ( course_id CHAR(8) NOT NULL, sec_id CHAR(4) NOT NULL COMMENT 同一课程同一学期的班号如01, semester ENUM(Fall,Spring,Summer) NOT NULL, year SMALLINT UNSIGNED NOT NULL, building VARCHAR(20) DEFAULT NULL, room VARCHAR(10) DEFAULT NULL, capacity SMALLINT UNSIGNED NOT NULL DEFAULT 60 COMMENT 容量上限事务实验要用, PRIMARY KEY (course_id, sec_id, semester, year), CONSTRAINT fk_section_course FOREIGN KEY (course_id) REFERENCES course (course_id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT开课班; CREATE TABLE takes ( student_id CHAR(8) NOT NULL, course_id CHAR(8) NOT NULL, sec_id CHAR(4) NOT NULL, semester ENUM(Fall,Spring,Summer) NOT NULL, year SMALLINT UNSIGNED NOT NULL, grade VARCHAR(2) DEFAULT NULL COMMENT NULL表示在修未出分, PRIMARY KEY (student_id, course_id, sec_id, semester, year), KEY idx_takes_section (course_id, sec_id, semester, year), CONSTRAINT fk_takes_student FOREIGN KEY (student_id) REFERENCES student (student_id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_takes_section FOREIGN KEY (course_id, sec_id, semester, year) REFERENCES section (course_id, sec_id, semester, year) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT选课记录;takes的主键顺序(student_id, ...)决定了两件事。InnoDB 的主键就是聚簇索引数据行按主键顺序物理排列「查某个学生选了哪些课」这类查询走的是顺序读非常快反过来「查某个班有哪些学生」用不上这个主键所以补了idx_takes_section做反向覆盖。这就是为什么两个索引都要有只留一个必然有一半的查询要全表扫。semester用ENUM而不是VARCHAR一方面省空间内部存 1 字节整数另一方面把非法值挡在存储层——写Autumn进去会直接报错不用在应用层再写一遍校验。代价是以后要加学期就得ALTER TABLE这个场景下学期的集合是稳定的所以可以接受。3.3 约束校验写完数据先跑几条自检 SQL种子数据灌完不要急着写业务查询先跑自检。最有价值的一条是校验学生表里的累计学分和选课记录算出来的学分是否一致——这是典型的冗余字段很容易写歪-- 自检 1累计学分与明细是否对得上返回空集才算通过 SELECT s.student_id, s.tot_cred, COALESCE(SUM(c.credits), 0) AS calc_cred FROM student s LEFT JOIN takes t ON t.student_id s.student_id AND t.grade IS NOT NULL AND t.grade F LEFT JOIN course c ON c.course_id t.course_id GROUP BY s.student_id, s.tot_cred HAVING calc_cred s.tot_cred;注意两个条件的位置。grade IS NOT NULL AND grade F必须写在ON里不能挪到WHERE。挪过去之后没出分的学生会因为LEFT JOIN产生的 NULL 被WHERE过滤掉整行消失聚合结果直接变成 0自检就失去意义了。这个坑在第 4 章还会再遇到一次属于写多表连接时最高频的错误。-- 自检 2有没有选课记录指向不存在的开课班外键正常时必为空 SELECT t.* FROM takes t LEFT JOIN section s ON s.course_id t.course_id AND s.sec_id t.sec_id AND s.semester t.semester AND s.year t.year WHERE s.course_id IS NULL;这两条返回空集说明数据模型和种子数据都对得上可以往下走。测试文件里可以把它们包成断言每次改完数据跑一遍比肉眼看强得多。4. 查询与事务把课程实验写成可复用的 SQL 与 Python 模块4.1 多表连接与聚合ON 和 WHERE 的位置差异课程实验里最常见的题型是「查每个学生已修学分」和「查每门课的选课人数排名」。第一个查询前面已经写过骨架这里重点讲第二种因为它同时用到分组和窗口函数-- 每个学期内按选课人数给各开课班排名人数相同并列 SELECT course_id, sec_id, semester, year, cnt, RANK() OVER (PARTITION BY semester, year ORDER BY cnt DESC) AS rk FROM ( SELECT course_id, sec_id, semester, year, COUNT(*) AS cnt FROM takes GROUP BY course_id, sec_id, semester, year ) AS agg ORDER BY semester, year, rk;内层子查询先做分组聚合把每个班的人数压成一行外层用RANK()在「学期 年份」这个分区内按人数倒序排名。用RANK而不是ROW_NUMBER是因为人数相同的班应该并列名次ROW_NUMBER会强行排出先后结果不具业务意义。PARTITION BY里的列顺序要和ORDER BY的筛选条件对齐否则跨学期的数据会被混在一个分区里排名。分组统计还有一个容易踩的点COUNT(*)和COUNT(列名)结果不同。统计「有多少学生选了这门课」用COUNT(*)统计「有多少学生出了成绩」用COUNT(grade)后者会自动跳过 NULL。实验报告里经常因为这两个写混导致数字对不上检查时先看这一处。4.2 事务与隔离级别容量校验为什么必须加 FOR UPDATE选课是这套工程里唯一一个真正需要事务的操作它同时满足三个条件先读后写、读的结果影响写的判断、并发下会互相干扰。如果只是「读一下容量再插一条记录」两个学生同时选最后一个名额时双方都读到剩余 1都判定可以选都插入成功班级超员。# app/dao/enroll_dao.py —— 选课事务带容量校验 import pymysql def enroll(conn, student_id, course_id, sec_id, semester, year): with conn.cursor() as cur: try: conn.begin() # 1) 加行锁读容量锁住这一行直到事务结束 cur.execute( SELECT capacity FROM section WHERE course_id%s AND sec_id%s AND semester%s AND year%s FOR UPDATE, (course_id, sec_id, semester, year), ) row cur.fetchone() if row is None: raise ValueError(开课班不存在) # 2) 在锁的保护下统计已选人数 cur.execute( SELECT COUNT(*) AS n FROM takes WHERE course_id%s AND sec_id%s AND semester%s AND year%s, (course_id, sec_id, semester, year), ) taken cur.fetchone()[n] if taken row[capacity]: raise ValueError(容量已满) # 3) 插入选课记录主键冲突由数据库兜底防重复选课 cur.execute( INSERT INTO takes(student_id, course_id, sec_id, semester, year) VALUES (%s, %s, %s, %s, %s), (student_id, course_id, sec_id, semester, year), ) conn.commit() return True except pymysql.err.IntegrityError: conn.rollback() # 主键冲突同一学生重复选同一个班 return False except Exception: conn.rollback() # 任何异常都回滚不留半截数据 raiseFOR UPDATE是关键。它给section里那一行加排他锁第二个并发事务执行到同一句时会阻塞直到第一个事务提交或回滚。没有它两个事务读到的是同一份快照在REPEATABLE READ隔离级别下彼此看不见对方的插入超员就成了必然。except分两层的理由是错误语义不同。IntegrityError是预期内的业务冲突重复选课吞掉并返回False让上层提示用户其他异常说明是代码或环境问题回滚后继续往外抛别让它静默失败。很多课程作业在这里图省事写一个裸except: pass结果事务回滚了但程序假装成功调试时完全找不到数据去哪了。隔离级别本身也值得在实验里跑一遍对比。用两个终端开两个会话都执行SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED然后一个会话改数据不提交另一个会话去读能清楚看到「不可重复读」换成REPEATABLE READ再跑一次同样的操作读到的值就不变了。用SELECT transaction_isolation确认当前级别别凭记忆猜。4.3 索引与执行计划用 EXPLAIN 判断改得对不对索引文件单独放着就是为了做前后对比。挑一条最典型的查询按学生查他的全部选课及课程名。-- 加索引前先看执行计划重点看 type 和 rows 两列 EXPLAIN ANALYZE SELECT t.course_id, c.title, t.semester, t.year, t.grade FROM takes t JOIN course c ON c.course_id t.course_id WHERE t.student_id S0000007;EXPLAIN ANALYZE是 8.0 才有的和传统EXPLAIN的区别是它真的执行一遍给出实际耗时和实际行数而EXPLAIN只给优化器的估算。估算和实际差得远通常意味着统计信息过期跑一次ANALYZE TABLE takes就恢复了。判断标准看三处。type列出现ALL说明全表扫ref或range才算走上索引。rows是预估扫描行数和表总行数接近就是没走索引。Extra里出现Using filesort说明排序没吃到索引数据量一大就慢。加索引就是针对这三处下药-- 覆盖索引把查询要的列都放进索引避免回表 CREATE INDEX idx_takes_student_cover ON takes (student_id, course_id, semester, year, grade);前导列必须是student_id这由最左前缀原则决定——索引按(student_id, course_id, ...)的顺序排列查询条件里没有student_id就用不上这个索引。这里把grade也放进索引是因为它是定长小字段加上之后这条查询完全可以从索引里取数不需要回表到聚簇索引Extra里会显示Using index。代价也要说清楚每个索引都是一份额外的 B 树插入和更新时要同步维护。takes表写入频繁索引加太多会让选课变慢。工程里的原则是只给真实出现过慢查询的语句加索引不给「以后可能会查」的列加。加完索引再跑一遍EXPLAIN ANALYZE对比实际耗时数字下去了才算有效凭感觉判断基本都是错的。5. 排查手册五类把课程实验卡到通宵的翻车现场5.1 现象source 脚本报 1215外键建不起来现象很具体执行01_schema.sql前面几张表都过了到takes或section时抛出ERROR 1215 (HY000): Cannot add foreign key constraint但报错信息里不告诉你是哪一列的问题。原因通常有三个。一是建表顺序反了子表先于父表创建父表还不存在。二是引用列和被引用列的类型不完全一致比如一边是CHAR(8)另一边是VARCHAR(8)或者一边带UNSIGNED另一边不带——字符长度和符号属性都算类型的一部分差一点就建不上。三是被引用的列上没有索引复合外键引用的列组合必须和父表的某个索引前缀完全匹配。解决方法是拿到准确错误。执行SHOW ENGINE INNODB STATUS\G翻到LATEST FOREIGN KEY ERROR那一段它会明确写出是哪张表的哪个约束、哪一列对不上。先按建表顺序核对一遍父表是否都在再逐列对比类型最后检查复合外键的列组合是不是和父表主键的列顺序一致。这三个查完基本就解决了。5.2 现象中文全变问号或乱码现象是插入时看着正常SELECT出来一堆???或者显示成æ ¡å·这种拉丁字母混排。原因分三层。第一层是库表字符集建库时没显式指定跟着服务端默认走了latin1。第二层是连接字符集Python 驱动建连时charset参数没写或者写成了utf8这是utf8mb3的别名存不下四字节字符。第三层是导入脚本时的会话编码source之前没有SET NAMES utf8mb4文件内容在解析阶段就被转坏了。解决要三层一起查。先SHOW CREATE DATABASE course_registry确认库字符集再SHOW CREATE TABLE student确认表字符集然后用SHOW VARIABLES LIKE character_set%看连接层的几个变量。应用层的charsetutf8mb4必须显式写死不能依赖默认值。三层统一之后重灌一遍数据问题基本绝迹。5.3 现象查出来的时间和本地差 8 小时现象是程序里datetime.now()写进去数据库里看是对的但用别的客户端查出来早了 8 小时或者反过来。原因是DATETIME和TIMESTAMP的语义完全不同。DATETIME存的是字面值不做时区转换你写什么读什么TIMESTAMP存的是 UTC 时间戳写入和读取时都会按会话的time_zone做一次转换。如果建表时随手用了TIMESTAMP而应用连接和客户端工具的time_zone设置不一样两边看到的就永远对不上。解决方法是先定语义再定类型。业务时间选课时间、出分时间如果需要跨时区正确展示用TIMESTAMP但必须在连接串里统一time_zone08:00如果只是记录一个本地时刻、不打算跨时区用DATETIME更省心不会有意外的转换。工程里统一用DATETIME并在db.py里执行SET time_zone 08:00把不确定性压到最低。5.4 现象明明有索引查询还是慢得离谱现象是EXPLAIN显示typeALL或者key列是 NULL但SHOW INDEX明明能看到那个索引。最常见的原因有两类。一类是隐式类型转换索引建在VARCHAR列上查询条件写成WHERE student_id 20240001不带引号MySQL 会把列值转成数字再比较索引直接失效。另一类是前导模糊匹配WHERE title LIKE %数据库%用不上索引因为 B 树只能从左往右匹配模式串开头是通配符就无从下手。解决办法是先对齐类型字符串条件一定要加引号参数化查询天然避免这个问题。模糊查询如果确实要中缀匹配考虑建全文索引或者把匹配交给专门的检索组件不要在LIKE上硬扛。改完之后用EXPLAIN ANALYZE对比实际耗时type从ALL变成ref才算真的改对了。5.5 现象事务看起来没生效异常了数据还在现象是代码里写了rollback但出错后去查库数据仍然在。第一个怀疑对象是表引擎。SHOW TABLE STATUS看Engine列如果是MyISAM它根本不支持事务rollback是空操作。历史遗留库或者从别处拷来的建表语句很容易带出这个问题。第二个怀疑对象是autocommit某些驱动默认开着自动提交begin()之前每条语句都已经独立提交了。第三个是存储过程里的COMMIT过程内部如果自己提交了外层的回滚也管不到。解决方法是先确认引擎全部是InnoDB这一点在建表脚本里显式写ENGINEInnoDB就能避免。然后确认连接的autocommitFalse并且所有事务都走同一套begin / commit / rollback封装不在业务代码里散着写。存储过程内部坚决不写COMMIT把事务边界留给调用方这个约定要在 README 里写清楚。6. 二次开发把这套课程工程改成自己的项目骨架课程工程跑通之后多数人下一步是拿它当自己项目的起点。改造有轻重之分先看三条最值得做的。第一条是加迁移管理。现在靠01_schema.sql全量重建数据一多就不能用了。换成 Alembic 或者自建的migrations/目录每个变更一个带时间戳的文件正向和回滚脚本成对写。判断标准很简单能不能在不丢数据的前提下把线上库从版本 A 升到版本 B能就对了。第二条是给takes加软删除和审计。课程作业里选课记录删了就没了真实业务需要保留「谁在什么时候退的课」。加status和deleted_at两列配上视图把软删的行过滤掉业务代码不用改。改造项改动成本适合的场景引入迁移脚本中要重写一遍建表流程表结构还会变软删除 审计列低加列加视图记录不允许物理删除DAO 换成 SQLAlchemy中查询要重写团队要快速迭代 CRUD补 pytest 集成测试低复用现有断言准备长期维护第三条是补集成测试。现在的tests/test_enroll.py是跑在真实库上的换机器就废。改成用 Docker 起一个临时 MySQL 容器测试开始时建库灌数据结束时销毁pytest一条命令跑完全部用例。第 3 章那两条自检 SQL 直接包成断言搬进去改完表结构跑一遍比手动核对靠谱得多。有一件事不建议做不要为了「看起来专业」上读写分离和分库分表。这套表的数据量在单机 MySQL 上跑几百万行毫无压力提前拆分只会让选课事务的跨库一致性问题变成新的坑收益为零。我自己的习惯是任何一次表结构变更先在一个干净的库上从头source一遍全部脚本再用EXPLAIN ANALYZE抽查三条最热的查询两步都过才允许提交。这个流程救过我好几次——有一次改字段类型忘了同步外键列干净库一跑就炸了本地那个「已经改过」的库反倒看不出来。希望帮到你。本文还有配套的精品资源点击获取