软件学院数据库系统实验做到第三个很多人一下就紧张起来了。前两次实验可能还在教你熟悉客户端、建库建表、跑通最基本的增删改查到了实验三画风通常就变了老师不再给一条一条的步骤而是甩给你一个场景让你自己从需求分析开始一路做到查询优化最后交上来一份能跑的数据库设计和演示。我第一次接触这个实验的时候最大的感受是它考的不是你会不会敲SQL而是你有没有一套完整的数据库工程思路。这篇文章就把实验三里最常见的通关路径、评分重点和那些坑从头到尾捋一遍。1. 实验到底在考什么先拆解实验三的隐藏要求1.1 实验三在整门课里的定位实验三往往处在课程的中段前面已经讲完了关系模型、SQL基础、约束和完整性后面要衔接的才是索引、事务、并发控制这些内容。所以老师在设计实验三的时候通常不会只考一个知识点而是把建库、建表、数据操纵、完整性约束、查询优化打包在一起让你体验一次“从零设计一个小型数据库系统”的完整流程。换句话说前两次实验是“你会不会用工具”实验三是“你会不会设计系统”。很多同学做实验三翻车不是SQL题不会做而是压根没有从系统角度去思考表结构怎么定、外键怎么关联、数据怎么生成、查询为什么会慢、事务能不能回滚这些问题环环相扣。1.2 评分点比想象中细我见过不少实验报告代码写得厚厚一叠最后分数却不理想问题就出在评分点分布上。实验三的评分通常不会只看“能不能跑出结果”还会看表设计是否规范、完整性约束是否合理、SQL是否考虑了边界情况、有没有索引优化意识。一份典型的实验三评分构成可以大致分成这样几块数据库设计占25%左右核心SQL实现占40%左右完整性约束和事务处理占20%左右查询优化和实验总结占15%左右。哪怕你的查询全部写对了只要建表时没有外键、没有CHECK约束、没有事务演示分数也很难上去。评审老师最反感的是“一张表打天下”——把所有字段塞进一张宽表既不分解关系也不设主外键。2. 建表不是把字段写上去就完事DDL细节决定后面能不能跑2.1 从ER图到关系模式先想清楚再动手很多同学一拿到实验题第一反应是打开客户端直接写CREATE TABLE这是最容易走弯路的地方。数据库系统实验三既然叫“系统实验”第一步一定是画出ER图理清实体、属性和联系。我用最经典的教学场景“选课管理系统”来举例这里有学生、教师、课程、选课记录四个核心实体。学生和课程之间是多对多的选课关系课程和教师之间是多对一的授课关系选课记录上还能挂一个“成绩”字段。有些同学会把成绩直接挂到课程表里这就错了成绩是学生选完之后才产生的它属于选课这个行为不属于课程本身。所以需要单独建一张选课关联表把学生ID、课程ID、学期、成绩都放进去用三个字段的联合主键来防止同一个人在同一学期重复选同一门课。二和三范式的问题也在这里体现。选课人数上限、学分、课程名这类信息只依赖课程ID如果把它们重复存到选课记录里就会出现更新异常某门课的学分改了得去几千条历史选课记录里同步修改稍一疏忽数据就前后矛盾。所以建表之前先在纸上把依赖关系画清楚比到代码里排错省力得多。2.2 建表DDL里最容易翻车的三个细节设计好关系模式之后建表这一步有大量细节决定你后面能不能顺利跑通。我先放一份可以直接参考的DDL后面再一个个解释关键点。CREATE TABLE student ( student_id VARCHAR(20) PRIMARY KEY, name VARCHAR(50) NOT NULL, gender CHAR(1) CHECK (gender IN (M, F)), birth_date DATE, enroll_year INT ); CREATE TABLE teacher ( teacher_id VARCHAR(20) PRIMARY KEY, name VARCHAR(50) NOT NULL, title VARCHAR(30) ); CREATE TABLE course ( course_id VARCHAR(20) PRIMARY KEY, course_name VARCHAR(100) NOT NULL, credit DECIMAL(3,1) CHECK (credit 0), teacher_id VARCHAR(20), capacity INT DEFAULT 60, FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ); CREATE TABLE enrollment ( student_id VARCHAR(20), course_id VARCHAR(20), semester VARCHAR(20), score DECIMAL(5,2), PRIMARY KEY (student_id, course_id, semester), FOREIGN KEY (student_id) REFERENCES student(student_id), FOREIGN KEY (course_id) REFERENCES course(course_id) );第一个细节是主外键字段类型必须完全一致。student_id如果用的是VARCHAR(20)enrollment表里的student_id也必须用VARCHAR(20)不能一个用INT一个用VARCHAR。很多同学的报错信息是“Cannot add foreign key constraint”排查半天发现两边类型长度差一位这种低级错误非常浪费时间。第二个细节是CHECK约束的兼容性问题。上面gender字段的CHECK在大部分关系型数据库里都能生效但某些历史版本的数据库默认不启用CHECK约束你要么在实验报告里明确写出验证过程要么在应用层再补一道校验。最稳妥的做法是建完表之后故意插入一条非法数据证明约束真的拦住了再把截图放进报告。第三个细节是字符集和排序规则。中文场景下建库建表最好在连接字符串里显式指定字符集否则后面跑INSERT的时候一旦遇到中文轻则乱码重则直接报错。这个坑我在第5章单独讲。3. 数据准备和完整性约束别让实验死在INSERT上3.1 用存储过程一次生成十万条数据建好表只是开始实验三需要跑复杂查询没有足够的测试数据根本体现不出性能差异。手写几十条INSERT不是不行但等到你做索引优化的时候就尴尬了几千行数据跑全表扫描也只要几毫秒优化前后的对比完全看不出来。所以合理的测试数据量应该是十万级起步。手动去拼几万条INSERT不现实最实用的方法是写一个存储过程批量造数。下面这段以MySQL语法为例生成指定数量的选课记录CREATE PROCEDURE seed_ssignment(IN total_count INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i total_count DO INSERT INTO enrollment(student_id, course_id, semester) VALUES ( CONCAT(S, LPAD(i, 5, 0)), CONCAT(C, LPAD(1 (i MOD 10), 4, 0)), 2024-2025-1 ); SET i i 1; END WHILE; END;这里有个小技巧造数的时候不要只生成“正常数据”还要故意插入一些边界数据比如超长课程名、重复的学期、NULL成绩这些在后面的查询和约束演示里都用得上。另外批量插入几万条数据时如果数据库默认开启了自动提交整个过程会很慢你可以在存储过程外面手动开启事务插完再一次性提交耗时能缩短一大截。3.2 触发器和事务让数据库自己守住业务规则实验三的完整性约束部分很多学校会要求做触发器最经典的题目是“选课人数不能超过课程容量”。每次往选课表里插入一条记录时数据库自动检查当前人数满了就直接报错。CREATE TRIGGER check_capacity BEFORE INSERT ON enrollment FOR EACH ROW BEGIN DECLARE current_count INT; DECLARE max_capacity INT; SELECT COUNT(*) INTO current_count FROM enrollment WHERE course_id NEW.course_id; SELECT capacity INTO max_capacity FROM course WHERE course_id NEW.course_id; IF current_count max_capacity THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Course is full; END IF; END;这个触发器的思路是用NEW.course_id拿到当前要选的课程然后统计该课程已有选课人数和容量比较。SIGNAL的作用是主动抛出一个异常让INSERT语句失败而不是默默放弃插入。这里提醒一句不同数据库语法有差异SQLite里写触发器就不太方便做这种子查询如果你用的不是MySQL可以先查一下官方文档或者把容量校验放到应用层只要在报告里说明理由即可。事务部分的关注点则是隔离级别。实验要求里常出现的脏读、不可重复读、幻读都可以用两个连接窗口现场演示。我的建议是准备一份“脚本化”的演示流程第1个窗口开启事务修改数据但不提交第2个窗口以READ UNCOMMITTED级别读到未提交的新数据然后回滚把两个窗口的截图拼在一起放进报告老师一眼就能看出你理解了隔离级别的意义。4. 查询分析与索引优化实验三最拉分的地方4.1 高频查询的SQL写法实验三的SQL部分通常会要求你完成若干条典型查询包括多表连接、子查询、聚合计算和分组过滤。不要只满足于“查出来”要会写会讲。这里列几个高频场景和对应写法。查“每个学生每个学期的平均成绩并排序”SELECT student_id, semester, AVG(score) AS avg_score FROM enrollment WHERE score IS NOT NULL GROUP BY student_id, semester ORDER BY avg_score DESC;查“从未选过课的学生”SELECT student_id, name FROM student WHERE student_id NOT IN ( SELECT DISTINCT student_id FROM enrollment );查“课程成绩排名”SELECT student_id, course_id, score, RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS rk FROM enrollment WHERE score IS NOT NULL;窗口函数这部分非常加分。如果你的实验环境支持RANK、ROW_NUMBER这类的窗口函数一定要用上并在报告里解释“PARTITION BY”和“GROUP BY”的差别。GROUP BY会让多行压成一行窗口函数则是在保留原始行的基础上附加一个排名值语义完全不同。视图和存储过程也建议加上。把“每门课的选课人数”这种高频查询写成视图报告里说明视图的好处是逻辑复用和权限控制这在后面的应用场景里很常见。4.2 用EXPLAIN看执行计划再建索引查询写出来只是第一步实验三里分值最高的往往是怎么证明你的查询性能是经过优化的。我以前带这个实验的时候多次看到同学直接说“我加了索引所以查询快”但问他加了什么索引、为什么加这个索引答不上来。正确顺序是先看执行计划再决定建什么索引。MySQL用EXPLAINSQLite用EXPLAIN QUERY PLAN都是告诉你数据库实际怎么执行这条SQL的。下面用一个典型的慢查询举例EXPLAIN QUERY PLAN SELECT s.student_id, s.name, c.course_name, e.score FROM enrollment e JOIN student s ON e.student_id s.student_id JOIN course c ON e.course_id c.course_id WHERE c.teacher_id T001;如果执行计划里出现了“SCAN enrollment”这类全表扫描的提示说明连接时每次都要把整张表翻一遍。这时候就要给enrollment表的外键列建索引。因为外键列不自动建索引两个大表做连接JOIN on course_id没有索引的话代价是灾难级的。我在一台普通机器上做过一组参考对比数据量是10万学生选课记录条件一样索引策略执行计划表现实测耗时参考完全不建索引SCAN全表逐条匹配2秒以上只建主键索引仍然SCAN关联表0.8秒左右给course_id加单列索引关联时能命中索引0.05秒左右联合索引覆盖查询字段索引覆盖避免回表0.02秒以下注意这个数字在不同环境下会变重要的是趋势。几十万行数据时有没有索引可以差出两个数量级。做实验的时候分别截图优化前后的EXPLAIN结果和耗时再和这个趋势对应起来报告会非常有说服力。建索引也不是越多越好。很多同学给表里每个字段都建索引结果插入和更新变慢还白白占空间。更合理的做法是高频连接字段、WHERE过滤字段、ORDER BY字段优先考虑区分度很低的字段比如性别建了索引也几乎没用。能够覆盖查询所需全部字段的联合索引可以让数据库直接从索引里拿数据不用回表这是性能最高的形态。4.3 事务隔离级别与并发控制事务和并发控制是实验三里理论和实践结合最紧密的部分。你需要能说清楚四个隔离级别的区别READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。用生活化的比喻就是两个人在同一张表上改东西级别越低越容易看到别人改到一半的中间状态级别越高互相的“隔断”越严但并发性能越差。实际操作时我建议至少演示两个场景。第一个是脏读连接A开启事务修改某条记录但还没提交连接B用READ UNCOMMITTED级别读这条记录读到了未提交的新值连接A回滚B读到的数据就成了“不存在过的脏数据”。第二个是不可重复读在同一个事务里连续两次读取同一条记录期间被另一个事务提交修改两次结果不一致。处理方式也很简单把隔离级别调高即可比如SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; SELECT * FROM enrollment WHERE student_id S00001; COMMIT;事务的另一个重点是理解提交和回滚的边界。不少同学的演示代码里有“数据没变化”的情况最后查出来是忘了COMMIT或者用了不支持跨表的存储引擎。这种实验台上的低级错误做完第5章的速查表你就明白了。5. 常见问题速查与踩坑实录5.1 建库的时候没注意字符集中文全部变成乱码这个是实验三出现频率最高的环境问题。中文乱码通常有三个层面数据库连接字符串没指定字符集、建表时表默认字符集不对、客户端显示字符集和库字符集不一致。最省心的做法是新建数据库时显式写好字符集连接时也指明字符集三步到位不要想着事后改。5.2 外键约束报错到底是谁的问题“Cannot add foreign key constraint”这个报错九成原因是类型不匹配另一成原因是两个表使用的字符集或排序规则不同。排查顺序有讲究先看两边字段类型长度是否完全一致再看是不是同一字符集最后检查引用表的主键是否真的存在。还有一个容易被忽略的点如果有一张引用表的数据已经是“孤儿数据”加外键约束也会失败。5.3 事务不生效你是MyISAM还是忘了BEGIN事务演示翻车的一大原因是表的存储引擎不支持事务。你可能用的是MyISAM而不是InnoDB建表时没指定引擎导致整个事务代码形同虚设。另外有些同学只写了一条UPDATE就以为在事务里实际上前面没有BEGIN连接又开着默认自动提交语句立刻落库ROLLBACK当然毫无效果。检查方法很简单在BEGIN之后执行SHOW VARIABLES LIKE autocommit;确认自己的连接状态。5.4 加了索引还是不加速索引失效的几个典型姿势索引失效的坑在实验报告里特别值得写因为老师知道你是真的遇过问题。常见情况有三个查询条件中对索引列用了函数比如WHERE YEAR(birth_date) 2000索引直接失效前导通配符比如LIKE %关键字索引也失效隐式类型转换比如索引列是VARCHAR你拿数字去查数据库可能要先把列转成数字索引就用不上。把这些写进总结比单纯堆一页“我建了XX索引”高级得多。为了方便你提交前自查我把上述问题整理成一张速查表现象大概率原因快速验证方法中文乱码连接或表字符集不对查看连接字符集参数外键加不上类型/字符集不匹配对比两表字段定义触发器不抛错语法有兼容问题先测一个简单验证语句事务回滚无效存储引擎非事务型或未BEGIN查autocommit和引擎加了索引变慢索引冗余或没覆盖EXPLAIN看是否走索引6. 提交前最后过一遍代码能不能一键重跑6.1 用一个脚本完成“建库-建表-造数-查询验证”实验三交作业前我的习惯是准备一个可以直接从头跑通的脚本顺序是先DROP掉旧的表再重新建表插入测试数据然后执行所有实验要求的查询最后做约束和事务演示。这个“一键重跑”的思路非常有用因为老师评阅时会直接在命令行里跑你的脚本脚本如果依赖你手动创建的库和临时数据评阅的时候大概率卡住。一个比较完整的顺序大概是这样关闭自动提交开启一个总事务DROP TABLE IF EXISTS所有表按依赖顺序删先删子表再删父表按依赖顺序建表先建父表再建子表插入学生、教师、课程基础数据调用存储过程批量生成选课数据执行查询并输出结果提交总事务全程保证脚本可重复执行。这样做还有个额外好处你不需要手动清理环境反复改代码时只要把脚本重新跑一遍就行不会因为上一次残留数据导致诡异报错。6.2 报告和演示要说清“为什么”文档和代码同样重要。老师看实验报告时最看重的是你有没有回答“为什么这样设计”。建表时为什么是这个主键为什么额外建了索引隔离级别为什么选REPEATABLE READ这些才是实验三的核心。我习惯的写法是每个关键SQL代码后面跟一小段“设计说明”讲清楚它解决了什么问题遇到哪些备选方案为什么选这个。就算方案不是最优的只要论证合理老师一般都会给分最怕的是报告里只有代码没有解释。最后再分享一个小技巧。实验三最容易被忽略的其实是“可重复执行”和“边界验证”。交作业之前把建表、造数据、跑查询的脚本整理成一条顺序跑之前先删掉旧表把整个流程用事务包起来断开连接再重连整套流程能从零跑通这份实验的分数基本就稳了。这是我见过太多同学吃亏之后总结出来的经验也是最简单又最管用的一条。
软件学院数据库系统实验做到第三个很多人一下就紧张起来了。前两次实验可能还在教你熟悉客户端、建库建表、跑通最基本的增删改查到了实验三画风通常就变了老师不再给一条一条的步骤而是甩给你一个场景让你自己从需求分析开始一路做到查询优化最后交上来一份能跑的数据库设计和演示。我第一次接触这个实验的时候最大的感受是它考的不是你会不会敲SQL而是你有没有一套完整的数据库工程思路。这篇文章就把实验三里最常见的通关路径、评分重点和那些坑从头到尾捋一遍。1. 实验到底在考什么先拆解实验三的隐藏要求1.1 实验三在整门课里的定位实验三往往处在课程的中段前面已经讲完了关系模型、SQL基础、约束和完整性后面要衔接的才是索引、事务、并发控制这些内容。所以老师在设计实验三的时候通常不会只考一个知识点而是把建库、建表、数据操纵、完整性约束、查询优化打包在一起让你体验一次“从零设计一个小型数据库系统”的完整流程。换句话说前两次实验是“你会不会用工具”实验三是“你会不会设计系统”。很多同学做实验三翻车不是SQL题不会做而是压根没有从系统角度去思考表结构怎么定、外键怎么关联、数据怎么生成、查询为什么会慢、事务能不能回滚这些问题环环相扣。1.2 评分点比想象中细我见过不少实验报告代码写得厚厚一叠最后分数却不理想问题就出在评分点分布上。实验三的评分通常不会只看“能不能跑出结果”还会看表设计是否规范、完整性约束是否合理、SQL是否考虑了边界情况、有没有索引优化意识。一份典型的实验三评分构成可以大致分成这样几块数据库设计占25%左右核心SQL实现占40%左右完整性约束和事务处理占20%左右查询优化和实验总结占15%左右。哪怕你的查询全部写对了只要建表时没有外键、没有CHECK约束、没有事务演示分数也很难上去。评审老师最反感的是“一张表打天下”——把所有字段塞进一张宽表既不分解关系也不设主外键。2. 建表不是把字段写上去就完事DDL细节决定后面能不能跑2.1 从ER图到关系模式先想清楚再动手很多同学一拿到实验题第一反应是打开客户端直接写CREATE TABLE这是最容易走弯路的地方。数据库系统实验三既然叫“系统实验”第一步一定是画出ER图理清实体、属性和联系。我用最经典的教学场景“选课管理系统”来举例这里有学生、教师、课程、选课记录四个核心实体。学生和课程之间是多对多的选课关系课程和教师之间是多对一的授课关系选课记录上还能挂一个“成绩”字段。有些同学会把成绩直接挂到课程表里这就错了成绩是学生选完之后才产生的它属于选课这个行为不属于课程本身。所以需要单独建一张选课关联表把学生ID、课程ID、学期、成绩都放进去用三个字段的联合主键来防止同一个人在同一学期重复选同一门课。二和三范式的问题也在这里体现。选课人数上限、学分、课程名这类信息只依赖课程ID如果把它们重复存到选课记录里就会出现更新异常某门课的学分改了得去几千条历史选课记录里同步修改稍一疏忽数据就前后矛盾。所以建表之前先在纸上把依赖关系画清楚比到代码里排错省力得多。2.2 建表DDL里最容易翻车的三个细节设计好关系模式之后建表这一步有大量细节决定你后面能不能顺利跑通。我先放一份可以直接参考的DDL后面再一个个解释关键点。CREATE TABLE student ( student_id VARCHAR(20) PRIMARY KEY, name VARCHAR(50) NOT NULL, gender CHAR(1) CHECK (gender IN (M, F)), birth_date DATE, enroll_year INT ); CREATE TABLE teacher ( teacher_id VARCHAR(20) PRIMARY KEY, name VARCHAR(50) NOT NULL, title VARCHAR(30) ); CREATE TABLE course ( course_id VARCHAR(20) PRIMARY KEY, course_name VARCHAR(100) NOT NULL, credit DECIMAL(3,1) CHECK (credit 0), teacher_id VARCHAR(20), capacity INT DEFAULT 60, FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ); CREATE TABLE enrollment ( student_id VARCHAR(20), course_id VARCHAR(20), semester VARCHAR(20), score DECIMAL(5,2), PRIMARY KEY (student_id, course_id, semester), FOREIGN KEY (student_id) REFERENCES student(student_id), FOREIGN KEY (course_id) REFERENCES course(course_id) );第一个细节是主外键字段类型必须完全一致。student_id如果用的是VARCHAR(20)enrollment表里的student_id也必须用VARCHAR(20)不能一个用INT一个用VARCHAR。很多同学的报错信息是“Cannot add foreign key constraint”排查半天发现两边类型长度差一位这种低级错误非常浪费时间。第二个细节是CHECK约束的兼容性问题。上面gender字段的CHECK在大部分关系型数据库里都能生效但某些历史版本的数据库默认不启用CHECK约束你要么在实验报告里明确写出验证过程要么在应用层再补一道校验。最稳妥的做法是建完表之后故意插入一条非法数据证明约束真的拦住了再把截图放进报告。第三个细节是字符集和排序规则。中文场景下建库建表最好在连接字符串里显式指定字符集否则后面跑INSERT的时候一旦遇到中文轻则乱码重则直接报错。这个坑我在第5章单独讲。3. 数据准备和完整性约束别让实验死在INSERT上3.1 用存储过程一次生成十万条数据建好表只是开始实验三需要跑复杂查询没有足够的测试数据根本体现不出性能差异。手写几十条INSERT不是不行但等到你做索引优化的时候就尴尬了几千行数据跑全表扫描也只要几毫秒优化前后的对比完全看不出来。所以合理的测试数据量应该是十万级起步。手动去拼几万条INSERT不现实最实用的方法是写一个存储过程批量造数。下面这段以MySQL语法为例生成指定数量的选课记录CREATE PROCEDURE seed_ssignment(IN total_count INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i total_count DO INSERT INTO enrollment(student_id, course_id, semester) VALUES ( CONCAT(S, LPAD(i, 5, 0)), CONCAT(C, LPAD(1 (i MOD 10), 4, 0)), 2024-2025-1 ); SET i i 1; END WHILE; END;这里有个小技巧造数的时候不要只生成“正常数据”还要故意插入一些边界数据比如超长课程名、重复的学期、NULL成绩这些在后面的查询和约束演示里都用得上。另外批量插入几万条数据时如果数据库默认开启了自动提交整个过程会很慢你可以在存储过程外面手动开启事务插完再一次性提交耗时能缩短一大截。3.2 触发器和事务让数据库自己守住业务规则实验三的完整性约束部分很多学校会要求做触发器最经典的题目是“选课人数不能超过课程容量”。每次往选课表里插入一条记录时数据库自动检查当前人数满了就直接报错。CREATE TRIGGER check_capacity BEFORE INSERT ON enrollment FOR EACH ROW BEGIN DECLARE current_count INT; DECLARE max_capacity INT; SELECT COUNT(*) INTO current_count FROM enrollment WHERE course_id NEW.course_id; SELECT capacity INTO max_capacity FROM course WHERE course_id NEW.course_id; IF current_count max_capacity THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Course is full; END IF; END;这个触发器的思路是用NEW.course_id拿到当前要选的课程然后统计该课程已有选课人数和容量比较。SIGNAL的作用是主动抛出一个异常让INSERT语句失败而不是默默放弃插入。这里提醒一句不同数据库语法有差异SQLite里写触发器就不太方便做这种子查询如果你用的不是MySQL可以先查一下官方文档或者把容量校验放到应用层只要在报告里说明理由即可。事务部分的关注点则是隔离级别。实验要求里常出现的脏读、不可重复读、幻读都可以用两个连接窗口现场演示。我的建议是准备一份“脚本化”的演示流程第1个窗口开启事务修改数据但不提交第2个窗口以READ UNCOMMITTED级别读到未提交的新数据然后回滚把两个窗口的截图拼在一起放进报告老师一眼就能看出你理解了隔离级别的意义。4. 查询分析与索引优化实验三最拉分的地方4.1 高频查询的SQL写法实验三的SQL部分通常会要求你完成若干条典型查询包括多表连接、子查询、聚合计算和分组过滤。不要只满足于“查出来”要会写会讲。这里列几个高频场景和对应写法。查“每个学生每个学期的平均成绩并排序”SELECT student_id, semester, AVG(score) AS avg_score FROM enrollment WHERE score IS NOT NULL GROUP BY student_id, semester ORDER BY avg_score DESC;查“从未选过课的学生”SELECT student_id, name FROM student WHERE student_id NOT IN ( SELECT DISTINCT student_id FROM enrollment );查“课程成绩排名”SELECT student_id, course_id, score, RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS rk FROM enrollment WHERE score IS NOT NULL;窗口函数这部分非常加分。如果你的实验环境支持RANK、ROW_NUMBER这类的窗口函数一定要用上并在报告里解释“PARTITION BY”和“GROUP BY”的差别。GROUP BY会让多行压成一行窗口函数则是在保留原始行的基础上附加一个排名值语义完全不同。视图和存储过程也建议加上。把“每门课的选课人数”这种高频查询写成视图报告里说明视图的好处是逻辑复用和权限控制这在后面的应用场景里很常见。4.2 用EXPLAIN看执行计划再建索引查询写出来只是第一步实验三里分值最高的往往是怎么证明你的查询性能是经过优化的。我以前带这个实验的时候多次看到同学直接说“我加了索引所以查询快”但问他加了什么索引、为什么加这个索引答不上来。正确顺序是先看执行计划再决定建什么索引。MySQL用EXPLAINSQLite用EXPLAIN QUERY PLAN都是告诉你数据库实际怎么执行这条SQL的。下面用一个典型的慢查询举例EXPLAIN QUERY PLAN SELECT s.student_id, s.name, c.course_name, e.score FROM enrollment e JOIN student s ON e.student_id s.student_id JOIN course c ON e.course_id c.course_id WHERE c.teacher_id T001;如果执行计划里出现了“SCAN enrollment”这类全表扫描的提示说明连接时每次都要把整张表翻一遍。这时候就要给enrollment表的外键列建索引。因为外键列不自动建索引两个大表做连接JOIN on course_id没有索引的话代价是灾难级的。我在一台普通机器上做过一组参考对比数据量是10万学生选课记录条件一样索引策略执行计划表现实测耗时参考完全不建索引SCAN全表逐条匹配2秒以上只建主键索引仍然SCAN关联表0.8秒左右给course_id加单列索引关联时能命中索引0.05秒左右联合索引覆盖查询字段索引覆盖避免回表0.02秒以下注意这个数字在不同环境下会变重要的是趋势。几十万行数据时有没有索引可以差出两个数量级。做实验的时候分别截图优化前后的EXPLAIN结果和耗时再和这个趋势对应起来报告会非常有说服力。建索引也不是越多越好。很多同学给表里每个字段都建索引结果插入和更新变慢还白白占空间。更合理的做法是高频连接字段、WHERE过滤字段、ORDER BY字段优先考虑区分度很低的字段比如性别建了索引也几乎没用。能够覆盖查询所需全部字段的联合索引可以让数据库直接从索引里拿数据不用回表这是性能最高的形态。4.3 事务隔离级别与并发控制事务和并发控制是实验三里理论和实践结合最紧密的部分。你需要能说清楚四个隔离级别的区别READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。用生活化的比喻就是两个人在同一张表上改东西级别越低越容易看到别人改到一半的中间状态级别越高互相的“隔断”越严但并发性能越差。实际操作时我建议至少演示两个场景。第一个是脏读连接A开启事务修改某条记录但还没提交连接B用READ UNCOMMITTED级别读这条记录读到了未提交的新值连接A回滚B读到的数据就成了“不存在过的脏数据”。第二个是不可重复读在同一个事务里连续两次读取同一条记录期间被另一个事务提交修改两次结果不一致。处理方式也很简单把隔离级别调高即可比如SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; SELECT * FROM enrollment WHERE student_id S00001; COMMIT;事务的另一个重点是理解提交和回滚的边界。不少同学的演示代码里有“数据没变化”的情况最后查出来是忘了COMMIT或者用了不支持跨表的存储引擎。这种实验台上的低级错误做完第5章的速查表你就明白了。5. 常见问题速查与踩坑实录5.1 建库的时候没注意字符集中文全部变成乱码这个是实验三出现频率最高的环境问题。中文乱码通常有三个层面数据库连接字符串没指定字符集、建表时表默认字符集不对、客户端显示字符集和库字符集不一致。最省心的做法是新建数据库时显式写好字符集连接时也指明字符集三步到位不要想着事后改。5.2 外键约束报错到底是谁的问题“Cannot add foreign key constraint”这个报错九成原因是类型不匹配另一成原因是两个表使用的字符集或排序规则不同。排查顺序有讲究先看两边字段类型长度是否完全一致再看是不是同一字符集最后检查引用表的主键是否真的存在。还有一个容易被忽略的点如果有一张引用表的数据已经是“孤儿数据”加外键约束也会失败。5.3 事务不生效你是MyISAM还是忘了BEGIN事务演示翻车的一大原因是表的存储引擎不支持事务。你可能用的是MyISAM而不是InnoDB建表时没指定引擎导致整个事务代码形同虚设。另外有些同学只写了一条UPDATE就以为在事务里实际上前面没有BEGIN连接又开着默认自动提交语句立刻落库ROLLBACK当然毫无效果。检查方法很简单在BEGIN之后执行SHOW VARIABLES LIKE autocommit;确认自己的连接状态。5.4 加了索引还是不加速索引失效的几个典型姿势索引失效的坑在实验报告里特别值得写因为老师知道你是真的遇过问题。常见情况有三个查询条件中对索引列用了函数比如WHERE YEAR(birth_date) 2000索引直接失效前导通配符比如LIKE %关键字索引也失效隐式类型转换比如索引列是VARCHAR你拿数字去查数据库可能要先把列转成数字索引就用不上。把这些写进总结比单纯堆一页“我建了XX索引”高级得多。为了方便你提交前自查我把上述问题整理成一张速查表现象大概率原因快速验证方法中文乱码连接或表字符集不对查看连接字符集参数外键加不上类型/字符集不匹配对比两表字段定义触发器不抛错语法有兼容问题先测一个简单验证语句事务回滚无效存储引擎非事务型或未BEGIN查autocommit和引擎加了索引变慢索引冗余或没覆盖EXPLAIN看是否走索引6. 提交前最后过一遍代码能不能一键重跑6.1 用一个脚本完成“建库-建表-造数-查询验证”实验三交作业前我的习惯是准备一个可以直接从头跑通的脚本顺序是先DROP掉旧的表再重新建表插入测试数据然后执行所有实验要求的查询最后做约束和事务演示。这个“一键重跑”的思路非常有用因为老师评阅时会直接在命令行里跑你的脚本脚本如果依赖你手动创建的库和临时数据评阅的时候大概率卡住。一个比较完整的顺序大概是这样关闭自动提交开启一个总事务DROP TABLE IF EXISTS所有表按依赖顺序删先删子表再删父表按依赖顺序建表先建父表再建子表插入学生、教师、课程基础数据调用存储过程批量生成选课数据执行查询并输出结果提交总事务全程保证脚本可重复执行。这样做还有个额外好处你不需要手动清理环境反复改代码时只要把脚本重新跑一遍就行不会因为上一次残留数据导致诡异报错。6.2 报告和演示要说清“为什么”文档和代码同样重要。老师看实验报告时最看重的是你有没有回答“为什么这样设计”。建表时为什么是这个主键为什么额外建了索引隔离级别为什么选REPEATABLE READ这些才是实验三的核心。我习惯的写法是每个关键SQL代码后面跟一小段“设计说明”讲清楚它解决了什么问题遇到哪些备选方案为什么选这个。就算方案不是最优的只要论证合理老师一般都会给分最怕的是报告里只有代码没有解释。最后再分享一个小技巧。实验三最容易被忽略的其实是“可重复执行”和“边界验证”。交作业之前把建表、造数据、跑查询的脚本整理成一条顺序跑之前先删掉旧表把整个流程用事务包起来断开连接再重连整套流程能从零跑通这份实验的分数基本就稳了。这是我见过太多同学吃亏之后总结出来的经验也是最简单又最管用的一条。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
