MySQL 查询优化实战从50题中提炼的5个索引设计与执行计划解读在数据库性能优化领域索引设计和执行计划分析是DBA和开发者的核心技能。本文将基于经典的学生-课程-成绩模型通过5个典型复杂查询案例深入剖析如何通过索引优化提升查询效率。我们将从执行计划解读入手逐步揭示索引设计的底层逻辑并提供可直接落地的优化方案。1. 理解执行计划数据库的路线图执行计划是数据库优化器的决策结果它揭示了MySQL如何访问数据。通过EXPLAIN命令我们可以获取查询的执行计划。以下是一个典型的执行计划输出示例EXPLAIN SELECT s.s_name, c.c_name, sc.s_score FROM student s JOIN score sc ON s.s_id sc.s_id JOIN course c ON sc.c_id c.c_id WHERE sc.s_score 80;执行计划中的几个关键指标指标含义优化方向type访问类型从优到差system const eq_ref ref range index ALLkey实际使用的索引确保查询使用了最佳索引rows预估扫描行数数值越小越好Extra额外信息注意Using filesort、Using temporary等警告当发现type为ALL全表扫描或出现Using filesort时通常意味着需要优化。例如对于多表连接查询确保连接字段上有适当索引是基础优化手段。2. 多表连接查询优化第7题案例分析原始查询查找学过张三老师授课的学生信息SELECT s.* FROM student s WHERE s.s_id IN ( SELECT DISTINCT sc.s_id FROM score sc JOIN course c ON sc.c_id c.c_id JOIN teacher t ON c.t_id t.t_id WHERE t.t_name 张三 );优化步骤分析现有索引确认teacher.t_name、course.t_id、score.c_id、score.s_id是否有索引优化索引设计ALTER TABLE teacher ADD INDEX idx_tname (t_name); ALTER TABLE course ADD INDEX idx_tid (t_id); ALTER TABLE score ADD INDEX idx_cid_sid (c_id, s_id);改写查询使用JOIN替代子查询SELECT DISTINCT s.* FROM student s JOIN score sc ON s.s_id sc.s_id JOIN course c ON sc.c_id c.c_id JOIN teacher t ON c.t_id t.t_id WHERE t.t_name 张三;优化前后执行计划对比指标优化前优化后typeALL (student)eq_ref (student)keyNULLPRIMARYrows8 (student)1 (student)ExtraUsing whereUsing index3. 分组聚合查询优化第18题案例分析原始查询统计各科成绩分段人数SELECT c.c_id, c.c_name, SUM(CASE WHEN s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100-85], SUM(CASE WHEN s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85-70] FROM course c JOIN score sc ON c.c_id sc.c_id GROUP BY c.c_id;优化策略覆盖索引设计ALTER TABLE score ADD INDEX idx_cid_score (c_id, s_score);预聚合技术针对大数据量CREATE TABLE score_stats AS SELECT c_id, SUM(s_score 85) AS cnt_85_100, SUM(s_score BETWEEN 70 AND 85) AS cnt_70_85 FROM score GROUP BY c_id; -- 定期更新统计表关键优化点分组字段应作为复合索引的前导列考虑使用WITH ROLLUP获取小计和总计大数据量时物化视图或预计算是更优方案4. 子查询优化第13题案例分析原始查询查找与01号同学所学课程完全相同的其他同学SELECT * FROM student WHERE s_id IN ( SELECT s_id FROM score s JOIN (SELECT c_id FROM score WHERE s_id 1) t1 ON s.c_id t1.c_id WHERE s_id ! 1 GROUP BY s_id HAVING COUNT(*) (SELECT COUNT(*) FROM score WHERE s_id 1) );优化方案使用EXISTS替代INSELECT s.* FROM student s WHERE EXISTS ( SELECT 1 FROM ( SELECT c_id FROM score WHERE s_id 1 ) t1 LEFT JOIN score sc ON sc.c_id t1.c_id AND sc.s_id s.s_id GROUP BY s.s_id HAVING COUNT(t1.c_id) COUNT(sc.c_id) AND COUNT(sc.c_id) (SELECT COUNT(*) FROM score WHERE s_id 1) );索引优化ALTER TABLE score ADD INDEX idx_sid_cid (s_id, c_id);子查询优化原则将相关子查询转为连接操作使用半连接(semi-join)优化避免在WHERE子句中对字段使用函数操作5. 排序优化第16题案例分析原始查询01课程分数小于60按分数降序排列SELECT s.*, sc.s_score AS score_01 FROM student s JOIN score sc ON s.s_id sc.s_id WHERE sc.c_id 1 AND sc.s_score 60 ORDER BY sc.s_score DESC;优化手段复合索引设计ALTER TABLE score ADD INDEX idx_cid_score_desc (c_id, s_score DESC);使用索引提示SELECT s.*, sc.s_score AS score_01 FROM student s JOIN score sc USE INDEX (idx_cid_score_desc) ON s.s_id sc.s_id WHERE sc.c_id 1 AND sc.s_score 60 ORDER BY sc.s_score DESC;排序优化要点ORDER BY与WHERE条件中的列应组成复合索引注意排序方向一致性ASC/DESC避免ORDER BY与WHERE使用不同索引6. 综合优化检查清单根据50题分析总结出以下性能陷阱检查表索引设计检查项[ ] 所有主键和外键是否都有索引[ ] 高频查询的WHERE条件字段是否有索引[ ] 多列条件是否建立了复合索引[ ] 排序字段是否包含在索引中执行计划警告标志[ ] 出现Using filesort文件排序[ ] 出现Using temporary临时表[ ]type列为ALL全表扫描[ ]rows值异常大查询改写技巧[ ] 用JOIN替代子查询[ ] 避免SELECT *只查询必要字段[ ] 大数据量分页使用延迟关联[ ] 复杂查询考虑拆分为多个简单查询在实际项目中我们曾遇到一个分页查询性能问题当使用LIMIT 10000, 20时查询变得异常缓慢。通过改为JOIN形式先获取ID再查询明细性能提升了20倍。这种实战经验告诉我们理论结合实践才能真正掌握MySQL优化精髓。