企业官网建设流程全解析

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;

优化分析：

1. 使用子查询先过滤出平均分高于85的学生，减少主查询处理的数据量
2. 使用JOIN替代WHERE连接，提高可读性和执行效率
3. 避免在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' );

优化技巧：

1. 使用EXISTS替代IN，当子查询结果集较大时性能更好
2. 确保elective表的(sno,cno)有复合索引
3. 对于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;

性能对比：

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 EXISTS：

SELECT 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;

深度分页优化方案：

1. 使用覆盖索引+延迟关联
2. 记录上一页最后一条记录的ID作为条件
3. 对于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.0+）：

CREATE 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);

索引使用原则：

1. 遵循最左前缀原则
2. 避免过度索引，写操作会变慢
3. 使用EXPLAIN验证索引使用情况
4. 定期使用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. 避免全表扫描的优化技巧

案例：查询特定时间段选课记录

低效写法：

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';

其他优化建议：

1. 避免在索引列上使用函数
2. 使用合适的比较运算符
3. 限制返回的列数，避免SELECT *
4. 合理使用FORCE INDEX提示

实际案例：一个未优化的复杂查询从8秒降到0.2秒，通过以下步骤：

1. 添加适当的复合索引
2. 重写SQL避免使用OR条件
3. 使用JOIN替代子查询
4. 限制返回字段数量

通过这10个高频考点的优化实践，可以显著提升学生选课系统的数据库性能。关键是要理解每种优化方法背后的原理，根据实际场景灵活应用。