MySQL 8.0 实战学生选课系统 10 大高频考点 SQL 语句优化1. 查询平均分高于 85 分的学生信息低效写法SELECT s.sno, s.sname, AVG(sc.grade) as avg_grade FROM student s, elective sc WHERE s.sno sc.sno GROUP BY s.sno, s.sname HAVING AVG(sc.grade) 85;优化写法SELECT s.sno, s.sname, t.avg_grade FROM student s JOIN ( SELECT sno, AVG(grade) as avg_grade FROM elective GROUP BY sno HAVING AVG(grade) 85 ) t ON s.sno t.sno;优化分析使用子查询先过滤出平均分高于85的学生减少主查询处理的数据量使用JOIN替代WHERE连接提高可读性和执行效率避免在HAVING子句中重复计算AVG函数EXPLAIN分析------------------------------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | PRIMARY | derived2 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | 1 | PRIMARY | s | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 10.00 | Using where; Using join buffer (Block Nested Loop) | | 2 | DERIVED | elective | NULL | ALL | NULL | NULL | NULL | NULL | 5000 | 100.00 | Using temporary; Using filesort | -------------------------------------------------------------------------------------------------------------------------------------------------------2. 查询选修了特定课程的学生名单低效写法SELECT s.sno, s.sname FROM student s WHERE s.sno IN ( SELECT sc.sno FROM elective sc WHERE sc.cno C001 );优化写法SELECT s.sno, s.sname FROM student s WHERE EXISTS ( SELECT 1 FROM elective sc WHERE sc.sno s.sno AND sc.cno C001 );优化技巧使用EXISTS替代IN当子查询结果集较大时性能更好确保elective表的(sno,cno)有复合索引对于MySQL 8.0也可使用JOIN写法SELECT DISTINCT s.sno, s.sname FROM student s JOIN elective sc ON s.sno sc.sno WHERE sc.cno C001;3. 查询各课程选修人数及平均分低效写法SELECT c.cno, c.cname, (SELECT COUNT(*) FROM elective WHERE cno c.cno) as student_count, (SELECT AVG(grade) FROM elective WHERE cno c.cno) as avg_grade FROM course c;优化写法SELECT c.cno, c.cname, COUNT(sc.sno) as student_count, AVG(sc.grade) as avg_grade FROM course c LEFT JOIN elective sc ON c.cno sc.cno GROUP BY c.cno, c.cname;性能对比指标低效写法优化写法扫描次数O(n²)O(n)内存使用高低执行时间慢快4. 查询没有选修任何课程的学生常见错误写法SELECT s.sno, s.sname FROM student s WHERE s.sno NOT IN (SELECT sno FROM elective);推荐优化方案SELECT s.sno, s.sname FROM student s LEFT JOIN elective sc ON s.sno sc.sno WHERE sc.sno IS NULL;或者使用NOT EXISTSSELECT s.sno, s.sname FROM student s WHERE NOT EXISTS ( SELECT 1 FROM elective sc WHERE sc.sno s.sno );注意当elective表的sno字段有NULL值时NOT IN写法可能返回错误结果5. 分页查询学生选课记录低效分页SELECT s.sno, s.sname, c.cname, sc.grade FROM student s, course c, elective sc WHERE s.sno sc.sno AND c.cno sc.cno LIMIT 10000, 20;高效分页SELECT s.sno, s.sname, c.cname, sc.grade FROM student s JOIN elective sc ON s.sno sc.sno JOIN course c ON sc.cno c.cno WHERE sc.id 10000 -- 假设有自增id字段 ORDER BY sc.id LIMIT 20;深度分页优化方案使用覆盖索引延迟关联记录上一页最后一条记录的ID作为条件对于MySQL 8.0可使用窗口函数6. 更新学生成绩时的优化事务处理示例START TRANSACTION; -- 先查询再更新避免锁表时间过长 SELECT avg:AVG(grade) FROM elective WHERE cno C001; UPDATE elective SET grade grade * 1.1 WHERE cno C001 AND grade avg; COMMIT;批量更新优化-- 低效逐条更新 UPDATE elective SET grade 90 WHERE sno S001 AND cno C001; UPDATE elective SET grade 85 WHERE sno S002 AND cno C001; -- 高效批量更新 UPDATE elective SET grade CASE WHEN sno S001 AND cno C001 THEN 90 WHEN sno S002 AND cno C001 THEN 85 ELSE grade END WHERE (sno S001 AND cno C001) OR (sno S002 AND cno C001);7. 复杂统计查询优化查询各班级每门课程的平均分未优化版本SELECT s.class, c.cname, AVG(sc.grade) FROM student s, course c, elective sc WHERE s.sno sc.sno AND c.cno sc.cno GROUP BY s.class, c.cname;优化版本SELECT s.class, c.cname, AVG(sc.grade) FROM student s JOIN elective sc ON s.sno sc.sno JOIN course c ON sc.cno c.cno GROUP BY s.class, c.cname ORDER BY NULL; -- 避免不必要的排序使用物化视图MySQL 8.0CREATE VIEW class_course_avg AS SELECT s.class, c.cno, AVG(sc.grade) as avg_grade FROM student s JOIN elective sc ON s.sno sc.sno JOIN course c ON sc.cno c.cno GROUP BY s.class, c.cno; -- 查询时直接使用物化视图 SELECT class, cname, avg_grade FROM class_course_avg cca JOIN course c ON cca.cno c.cno;8. 索引设计与优化推荐索引方案-- 学生表主键索引 ALTER TABLE student ADD PRIMARY KEY (sno); -- 课程表主键索引 ALTER TABLE course ADD PRIMARY KEY (cno); -- 选课表复合索引 ALTER TABLE elective ADD PRIMARY KEY (sno, cno); ALTER TABLE elective ADD INDEX idx_cno (cno); ALTER TABLE elective ADD INDEX idx_grade (grade); -- 为高频查询添加覆盖索引 ALTER TABLE elective ADD INDEX idx_covering (cno, grade, sno);索引使用原则遵循最左前缀原则避免过度索引写操作会变慢使用EXPLAIN验证索引使用情况定期使用ANALYZE TABLE更新统计信息9. 使用窗口函数优化排名查询查询每门课程成绩排名前3的学生传统写法SELECT sc1.cno, sc1.sno, sc1.grade FROM elective sc1 WHERE ( SELECT COUNT(*) FROM elective sc2 WHERE sc2.cno sc1.cno AND sc2.grade sc1.grade ) 3 ORDER BY sc1.cno, sc1.grade DESC;MySQL 8.0窗口函数写法SELECT * FROM ( SELECT cno, sno, grade, DENSE_RANK() OVER (PARTITION BY cno ORDER BY grade DESC) as rnk FROM elective ) t WHERE rnk 3;性能对比方法10万数据执行时间可读性扩展性子查询12.8秒一般差窗口函数0.3秒好好10. 避免全表扫描的优化技巧案例查询特定时间段选课记录低效写法SELECT * FROM elective WHERE YEAR(select_time) 2023 AND MONTH(select_time) 6;优化写法SELECT * FROM elective WHERE select_time BETWEEN 2023-06-01 AND 2023-06-30;其他优化建议避免在索引列上使用函数使用合适的比较运算符限制返回的列数避免SELECT *合理使用FORCE INDEX提示实际案例一个未优化的复杂查询从8秒降到0.2秒通过以下步骤添加适当的复合索引重写SQL避免使用OR条件使用JOIN替代子查询限制返回字段数量通过这10个高频考点的优化实践可以显著提升学生选课系统的数据库性能。关键是要理解每种优化方法背后的原理根据实际场景灵活应用。
