数据库系统实验三实战：SQL复杂查询、索引优化与事务并发控制

数据库系统实验三在很多学校的人才培养方案里都是一道分水岭。某高校软件学院的这门实验题目看起来只是几张表、几条SQL但真正动手后才会发现它把关系代数、查询优化、事务控制这些课堂理论全部揉在了一起。这个实验能帮你打通从“会写SQL”到“会用数据库”的那一步尤其是复杂查询和性能分析能力是后续做应用开发的底层功。适合正在赶实验报告的同学也适合想系统巩固数据库实操的人。1. 实验全貌与设计思路拆解1.1 实验三到底在考查什么数据库系统实验三通常不会只让你做增删改查而是把复杂查询、索引、事务三个模块串起来。先说复杂查询一般会通过学生选课这种经典场景要求你完成多表连接、子查询、分组聚合、集合运算甚至要处理“选修了全部课程”这类关系代数除法问题。这部分考查的是你能否把自然语言描述的业务需求准确翻译成没有逻辑漏洞的SQL语句。索引模块是对查询性能的进一步要求。你不仅要会写查询还要能判断一条慢查询为什么慢并且通过建立索引、改写SQL来让它变快。这一步会涉及执行计划分析很多同学第一次看到EXPLAIN的输出会懵但实际上它只是把优化器的决策摊开给你看。事务与并发控制是实验三里最容易被忽视的部分因为不像是“写一条SQL出结果”那么直接。它需要你打开多个数据库会话去制造脏读、不可重复读、幻读再通过隔离级别或加锁来规避。这个过程看起来像在做实验演示但恰恰是理解数据库一致性的关键。设计者把这些环节放在实验三也是循序渐进的。前面实验要求你建表、插数据、写基础查询到实验三才开始模拟真实业务中的“统计、报表、并发更新”等场景。说白了前两次实验是教你说单词实验三是教你怎么写句子和段落。1.2 数据库选型与实验环境准备实验本身对数据库没有硬性限制但选对工具能省很多事。我个人建议使用MySQL 8.0原因是它语法通用、网上的资料和踩坑记录最多而且EXPLAIN输出清晰默认隔离级别是REPEATABLE READ很适合做事务实验。如果你学校机房预装的是SQL Server或PostgreSQL问题也不大核心SQL语法基本一致只有少量差异。安装MySQL时建议把默认字符集设置为utf8mb4避免后面插入中文数据出现乱码。到这一步很多同学会疑惑界面工具不是更直观吗我的态度是DataGrip、DBeaver这些工具可以用来看表结构和执行计划的图形化展示但实验报告里要求的是能直接运行的SQL文本。所以至少一半的调试过程要在命令行或代码编辑器里完成避免过度依赖自动补全。我在本机一般会同时准备两种环境一个MySQL 8.0实例用来跑查询一个Navicat或DBeaver连接作为辅助工具。要注意的是做事务实验时需要开多个会话窗口图形工具通常也支持多标签页不一定非要开多个命令行窗口。2. 核心细节解析与实操要点2.1 多表连接别让结果集失控多表连接最容易犯的错就是忘记写连接条件导致笛卡尔积。比如三张表学生表20条记录、课程表10条、选课表50条如果不加任何WHERE或ON条件直接JOIN结果集会有20乘以10乘以50等于一万行。我见过有同学统计“每门课的平均分”就是因为JOIN条件写错把每一个学生和每一门课都匹配了一遍最后平均值完全不对。写多表连接时要形成肌肉记忆先确认需要从哪些表取字段再画出表之间的关联键最后在ON子句里写等值条件。对于外连接要明确主表是哪一张。LEFT JOIN会保留左边表的全部行RIGHT JOIN反之。自连接是难点本质上是给同一张表起两个别名然后当成两张不同的表来操作。比如查询“年龄比某位同学大的所有学生”就可以把student表与自己连接。还有一个细节外连接加上WHERE条件时容易踩坑。举个例子你希望列出所有学生以及他们选修某门课的成绩如果用如下写法SELECT s.s_name, sc.score FROM student s LEFT JOIN sc ON s.s_id sc.s_id WHERE sc.c_id 1;表面看没问题但如果某个学生没有选任何课sc.c_id是NULLWHERE条件就把这行过滤掉了最终效果跟内连接一样。正确做法是把筛选放在子查询里或者直接作为一个派生表SELECT s.s_name, t.score FROM student s LEFT JOIN ( SELECT s_id, score FROM sc WHERE c_id 1 ) t ON s.s_id t.s_id;这种细节在实验报告里非常加分因为它体现的是对SQL逻辑的理解而不是只会调API。2.2 子查询与集合运算的易错点子查询可以出现在SELECT子句、FROM子句和WHERE子句每种位置都有各自规则。SELECT子句里的标量子查询必须返回一行一列否则直接报错FROM子句里的派生表必须有别名WHERE子句里的IN和EXISTS要重点辨析。很多人以为IN和EXISTS只是写法不同性能差不多但实验里要写出合理的查询就必须知道它们的差异。IN适合驱动表数据量小、子查询结果集也小的场景EXISTS更像循环匹配适合外表行数多、子查询走索引的场景。现代优化器有时候会互相改写但你不能依赖这一点。更关键的是NULL的三值逻辑陷阱。以“查询没有选修任何课程的学生”为例如果用NOT INSELECT s_id, s_name FROM student WHERE s_id NOT IN (SELECT s_id FROM sc);如果sc表中的s_id本身就是外键一般不会有NULL但如果子查询里混入了NULLNOT IN会返回空集。这是因为SQL中NULL既不是真也不是假而是未知导致最终结果无法确定为真。稳妥方案是用NOT EXISTSSELECT s_id, s_name FROM student s WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.s_id s.s_id );在实验报告里可以把这两种写法及其结果都展示出来加上一句“为避免NULL对三值逻辑的影响推荐使用NOT EXISTS”老师会知道你真正踩过这个坑。集合运算方面UNION会去重UNION ALL不会去重且性能更好。如果题目只是要求合并两个结果集且明确要包含重复项就必须用UNION ALL。还有一个容易忽略的点集合运算要求两个查询的列数相同对应列的数据类型也要兼容不然会报错。2.3 索引优化不要盲目建索引索引优化的核心不是“建了索引查询就会快”而是要理解B树索引能加速什么。简单来说B树可以让数据库快速定位到目标数据而不用整表扫。WHERE中的等值条件、范围条件、JOIN的关联字段、ORDER BY和GROUP BY的字段都是适合建索引的候选。但索引不是越多越好。每次插入、更新、删除时数据库都要同步维护索引索引太多写入性能就会变差。此外如果字段区分度太低比如性别只有“男”“女”建了索引也可能不被优化器使用因为它算下来全表扫描更划算。实验里通常要求你对比加索引前后的扫描行数和执行时间所以数据量要足够大。只插几十行的话性能差异根本看不出来。组合索引要遵循最左前缀原则。比如建立索引(a, b, c)它可以加速包含a条件、包含a和b条件、包含a和b和c条件的查询但不会加速只包含b或c的查询。这好比查字典你只能按照字母顺序从前到后翻跳过第一个字母直接查第二个字母是行不通的。EXPLAIN是最重要的调试工具。执行EXPLAIN SELECT ...后重点看type列从好到差大致是const、eq_ref、ref、range、index、ALL。还有key列表示实际用到的索引rows列表示预估扫描行数。实验报告里可以贴上优化前后的EXPLAIN输出再用表格对比比写一大段文字更直观。3. 实操过程与核心环节实现3.1 选课系统的建表与数据准备下面我们用一个可复现的模拟项目X来演示。表结构设计如下学生表student、课程表course、选课表sc。选课表以(s_id, c_id)作为复合主键并设置外键保证完整性。成绩字段score允许为空模拟“还没考试”的情况。CREATE DATABASE IF NOT EXISTS db_lab3 CHARACTER SET utf8mb4; USE db_lab3; CREATE TABLE student ( s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(50) NOT NULL, s_gender ENUM(M,F), s_age TINYINT, s_major VARCHAR(50) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE course ( c_id INT PRIMARY KEY AUTO_INCREMENT, c_name VARCHAR(100) NOT NULL, c_credit DECIMAL(3,1) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE sc ( s_id INT NOT NULL, c_id INT NOT NULL, score DECIMAL(4,1), PRIMARY KEY (s_id, c_id), CONSTRAINT fk_sc_student FOREIGN KEY (s_id) REFERENCES student(s_id), CONSTRAINT fk_sc_course FOREIGN KEY (c_id) REFERENCES course(c_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入示例数据时要注意外键顺序先插入student和course再插入sc。如果偷懒用工具可视化操作后面做外键级联时会很麻烦所以建议手工维护。INSERT INTO student (s_name, s_gender, s_age, s_major) VALUES (张同,M, 20, 软件工程), (李茜,F, 21, 数据科学), (王磊,M, 22, 人工智能), (赵欣,F, 20, 网络工程), (陈宇,M, 21, 软件工程); INSERT INTO course (c_name, c_credit) VALUES (数据库系统, 4.0), (操作系统, 3.5), (计算机网络, 3.0), (数据结构, 4.0), (软件工程, 2.5); INSERT INTO sc (s_id, c_id, score) VALUES (1, 1, 91.0), (1, 2, 82.5), (2, 1, 88.0), (2, 3, 95.0), (3, 1, 76.0), (3, 4, 68.5), (4, 5, NULL), (5, 1, 79.0), (5, 2, 86.0);实际实验时这些数据远远不够看索引效果。我当时写了一个存储过程往sc表里插入了上万条随机记录再把student和course也扩充到几百行这样EXPLAIN的输出才有说服力。存储过程不是实验重点但值得写在报告“附加工作”里。3.2 典型查询题的SQL实现与解释第一题查询每个学生的选课数和平均分。注意使用LEFT JOIN保证没选课的学生也能显示出来平均分如果NULL就显示为0SELECT s.s_id, s.s_name, COUNT(sc.c_id) AS course_num, ROUND(AVG(COALESCE(sc.score, 0)), 2) AS avg_score FROM student s LEFT JOIN sc ON s.s_id sc.s_id GROUP BY s.s_id, s.s_name;这里有一个细节COUNT(sc.c_id)只统计非NULL的选课记录所以没选课的学生course_num会是0而AVG(COALESCE(sc.score, 0))会把NULL当成0分计算。这个写法在业务上是否合理需要看题目要求但实验报告里可以解释清楚。第二题查询每门课的选课人数、最高分、最低分。同样用LEFT JOIN列出无人选的课程SELECT c.c_id, c.c_name, COUNT(sc.s_id) AS stu_num, MAX(sc.score) AS max_score, MIN(sc.score) AS min_score FROM course c LEFT JOIN sc ON c.c_id sc.c_id GROUP BY c.c_id, c.c_name;第三题“查询选修了数据库系统课程且成绩高于该课程平均分的学生”。我的第一版SQL是这样写的SELECT s.s_id, s.s_name, sc.score FROM student s JOIN sc ON s.s_id sc.s_id JOIN course c ON sc.c_id c.c_id WHERE c.c_name 数据库系统 AND sc.score ( SELECT AVG(sc2.score) FROM sc sc2 JOIN course c2 ON sc2.c_id c2.c_id WHERE c2.c_name 数据库系统 );这个查询会先子查询算出平均分再比较每条记录。如果数据量大可以考虑用窗口函数但实验要求基础SQL的话子查询版本完全够用。窗口函数版本是这样SELECT s.s_id, s.s_name, sc.score FROM ( SELECT s_id, c_id, score, AVG(score) OVER (PARTITION BY c_id) AS avg_score FROM sc JOIN course ON sc.c_id course.c_id WHERE course.c_name 数据库系统 ) t JOIN student s ON t.s_id s.s_id WHERE t.score t.avg_score;两个版本都能跑但是窗口函数可读性更好在MySQL 8.0中性能也不错。实验报告里我建议主推子查询版本并把窗口函数作为“进一步优化”提一下显得思考全面。第四题是“查询选修了全部课程的学生”。这是关系代数里的除法问题。一个常用解法是统计课程总数再按学生分组统计去重选课数两者相等SELECT s.s_id, s.s_name FROM student s JOIN sc ON s.s_id sc.s_id GROUP BY s.s_id, s.s_name HAVING COUNT(DISTINCT sc.c_id) (SELECT COUNT(*) FROM course);注意COUNT(DISTINCT sc.c_id)要去重防止同一个人重复选修同一门课导致计数虚高。还有另一种“双重NOT EXISTS”写法可以解决零除问题但做实验时这种不使用聚合的写法更经典SELECT s.s_id, s.s_name FROM student s WHERE NOT EXISTS ( SELECT c.c_id FROM course c WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.s_id s.s_id AND sc.c_id c.c_id ) );这个逻辑需要一点耐心理解内层NOT EXISTS选出“该学生没选的课程”外层NOT EXISTS则保证不存在这样的课程也就是学生选了所有课。3.3 索引优化的完整对比索引优化题通常会给一条慢查询要求你通过加索引或改写SQL来提升性能。我们可以拿这个查询做例子EXPLAIN SELECT * FROM sc WHERE score BETWEEN 70 AND 90;没有索引时EXPLAIN的type是ALLrows是全部数据行。加上普通索引ALTER TABLE sc ADD INDEX idx_score (score);再执行EXPLAINtype会变成rangerows会明显减少。但要注意如果数据量不大或者范围覆盖的数据占比太高优化器可能仍然选择全表扫描。这不代表索引没用而是优化器认为访问整表比走索引回表更快。所以在实验报告里“没有优化效果”也可能是一条有效结论关键是你能解释清楚为什么。还有一个常见操作在student表的s_major字段上建索引来优化“查询某专业所有学生的选课信息”这类查询。这里有个陷阱如果s_major的区分度不高索引效果可能不明显。我建议用组合索引比如(s_major, s_id)这样既能快速定位专业又能通过s_id再连接选课表。这个选择背后的原理就是尽量让索引覆盖查询条件减少回表次数。如果还想深入可以尝试EXPLAIN ANALYZEMySQL 8.0.18以后支持会输出实际执行时间。但在提交实验报告之前建议先确认老师是否接受这种高级工具因为有些评分标准只要EXPLAIN输出。3.4 事务与并发控制实验事务部分需要开多个会话窗口。先演示最基础的事务提交和回滚-- 会话A START TRANSACTION; UPDATE sc SET score 99 WHERE s_id 1 AND c_id 1; ROLLBACK; SELECT score FROM sc WHERE s_id 1 AND c_id 1;如果ROLLBACK生效查出来的分数应该还是原来的91.0。然后演示脏读。先把会话A的隔离级别设为READ UNCOMMITTED更新后不提交另一个会话也设为READ UNCOMMITTED就能读到未提交的修改。等会话A回滚那个会话再查就是旧值。这种先读到后消失的数据就是脏读。不可重复读需要在两个隔离级别下对比。把会话A设为READ COMMITTED然后在同一个事务里两次查询同一条记录中间让另一个会话修改并提交该记录第一次读和第二次读的值不一样这就是不可重复读。注意在MySQL默认的REPEATABLE READ下普通SELECT是快照读两次查询结果一致所以这个现象必须在READ COMMITTED下演示。幻读更复杂一些往往涉及范围查询。比如事务A按条件SELECT ... FOR UPDATE锁住一部分记录事务B插入一条满足条件的新记录并提交然后事务A再查发现自己锁住的范围内出现了本不存在的记录。这种问题通常靠间隙锁解决但这也是死锁容易发生的地方。实验报告里只需要记录现象和结论并不需要写太底层的东西。锁等待的实验相对简单会话A执行SELECT * FROM sc WHERE s_id 1 FOR UPDATE;不提交会话B执行同一行更新会一直等待直到超时。用SHOW ENGINE INNODB STATUS可以看到锁等待信息。这个是加分项显示出你真正操作过并发场景。4. 常见问题与排查技巧实录4.1 查询结果出现重复行统计类查询最怕结果翻倍。有一次我写“每个学生的选课数”用了三表连接结果学生选了两门课就出现两行COUNT数变成了2、4看起来完全不对。后来我单独对sc表和student表分组查询再对比JOIN后的明细才发现问题出在course表被错误引入。排查重复行的思路很简单先去掉非必要的表确定最小表集再逐步加表观察行数变化。如果最终确实需要多表再考虑使用DISTINCT或者子查询来消除重复但根本解法是理清业务关系和连接条件。4.2 GROUP BY报错 only_full_group_by很多同学在MySQL 5.7以上环境运行这样的SQL会报错SELECT s_name, COUNT(*) FROM sc JOIN student ON sc.s_id student.s_id GROUP BY student.s_id;因为s_name没有被包含在GROUP BY中。严格模式下选择列表里出现的非聚合列必须被GROUP BY子句包含或者被聚合函数包裹。如果确定s_name函数依赖于s_id即一个s_id只会有一个s_name可以加ANY_VALUE(s_name)绕过。但是实验报告不建议用这个手法因为容易掩盖逻辑问题。最稳妥的写法是把s_name也写进GROUP BY代价是多一点分组开销但语义清楚。4.3 NOT IN 永远查不出结果之前说过NOT IN和NULL的问题。实际排查时可以先运行子查询单独检查是否有NULLSELECT s_id FROM sc WHERE s_id IS NULL;如果有NULL就会导致整个NOT IN为假。最好的替代方案是NOT EXISTS。这个坑在很多生产环境的SQL里也会遇到实验里能自己发现并解决说明你对三值逻辑的理解是到位的。4.4 事务实验中的锁等待超时事务实验中最常见的问题是“Lock wait timeout exceeded”。遇到这种提示先别急着杀进程。用SHOW PROCESSLIST;看看哪个连接在等锁再用SHOW ENGINE INNODB STATUS;查看最近的死锁或锁等待详情。我遇到过一次死锁两个事务一个先更新student再更新sc另一个先更新sc再更新studentInnoDB检测到了死锁并回滚了其中一个事务。解决方式就是让所有事务按照相同的表顺序加锁保持事务短小不在事务里做不必要的外部查询。另外锁等待超时时间默认50秒可以在客户端设置成更短比如5秒这样实验演示时不用等那么久。命令是SET innodb_lock_wait_timeout 5;这个参数只对当前会话生效做演示足够了。4.5 字符集和排序规则导致的比较问题中文数据最容易踩字符集坑。两个表的字符集不一致连接时可能报错或者比较时不相等。建议建表时都明确指定CHARSETutf8mb4排序规则统一用utf8mb4_general_ci或utf8mb4_unicode_ci。另外字符串含有尾随空格也会造成比较结果异常用LIKE或者TRIM()处理。实验里如果遇到“明明数据一样却查不出来”优先检查字符集和空格。数据库系统实验三做完之后我个人最大的收获不是背会了多少SQL语法而是养成了一个习惯每次查询都先EXPLAIN每次结果不对都先怀疑连接条件。这个习惯让我在后来的很多项目里避免了不少线上事故。如果你正在做这个实验请一定自己亲手把慢查询调优一遍不要直接复制别人的答案。等你看到EXPLAIN里type从ALL变成ref的时候那种感觉比拿满分还爽。