MySQL 查询优化实战：从50题中提炼的5个索引设计与执行计划解读

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为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 = '张三' );

优化步骤：

1. 分析现有索引：

   • 确认teacher.t_name、course.t_id、score.c_id、score.s_id是否有索引

2. 优化索引设计：

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

3. 改写查询（使用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 = '张三';

优化前后执行计划对比：

3. 分组聚合查询优化：第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;

优化策略：

1. 覆盖索引设计：

   ALTER TABLE score ADD INDEX idx_cid_score (c_id, s_score);

2. 预聚合技术（针对大数据量）：

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

优化方案：

1. 使用EXISTS替代IN：

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

2. 索引优化：

   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;

优化手段：

1. 复合索引设计：

   ALTER TABLE score ADD INDEX idx_cid_score_desc (c_id, s_score DESC);

2. 使用索引提示：

   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优化精髓。