1. 从30秒到1毫秒一次SQL优化实战全记录上周排查线上慢查询时意外发现一个执行时间长达30秒的SQL语句。经过系列优化后最终将执行时间压缩到1毫秒以内。这个案例非常典型涉及子查询优化、索引策略、执行计划分析等核心知识点特别记录下完整优化过程。2. 问题场景还原2.1 数据库环境与表结构我们使用MySQL 5.6版本涉及三张核心表课程表CourseCREATE TABLE Course( c_id INT PRIMARY KEY, name VARCHAR(10) ) ENGINEInnoDB;数据量100条课程记录学生表StudentCREATE TABLE Student( s_id INT PRIMARY KEY, name VARCHAR(10) ) ENGINEInnoDB;数据量7万条学生记录成绩表SCCREATE TABLE SC( sc_id INT PRIMARY KEY, s_id INT, c_id INT, score INT, FOREIGN KEY (s_id) REFERENCES Student(s_id), FOREIGN KEY (c_id) REFERENCES Course(c_id) ) ENGINEInnoDB;数据量70万条成绩记录2.2 原始SQL与性能问题业务需求是查询语文考试c_id0得100分的学生信息原始SQL如下SELECT s.* FROM Student s WHERE s.s_id IN ( SELECT s_id FROM SC WHERE c_id 0 AND score 100 );执行时间30248.271秒约8.4小时3. 第一轮优化基础索引策略3.1 执行计划分析使用EXPLAIN查看执行计划EXPLAIN SELECT s.* FROM Student s WHERE s.s_id IN ( SELECT s_id FROM SC WHERE c_id 0 AND score 100 );执行计划显示两张表都使用全表扫描typeALL没有使用任何索引预估扫描行数Student表7万行SC表70万行3.2 添加单列索引在WHERE条件字段上创建索引CREATE INDEX idx_sc_c_id ON SC(c_id); CREATE INDEX idx_sc_score ON SC(score);优化后执行时间1.054秒性能提升约3万倍但1秒的响应时间对于线上服务仍不理想。4. 第二轮优化子查询重构4.1 分析优化后的执行计划查看优化后的实际执行SQL通过SHOW WARNINGSSELECT s.s_id, s.name FROM Student s WHERE in_optimizer( s.s_id, EXISTS( SELECT 1 FROM SC sc WHERE (sc.c_id 0) AND (sc.score 100) AND (CACHE(s.s_id) sc.s_id) ) )问题分析MySQL将IN子查询转换为EXISTS相关子查询执行顺序变为先遍历Student表再循环查询SC表总操作次数7万学生× 8满足条件的成绩≈ 56万次4.2 改为连接查询重构为JOIN形式SELECT s.* FROM Student s INNER JOIN SC sc ON sc.s_id s.s_id WHERE sc.c_id 0 AND sc.score 100;执行时间0.057秒4.3 强制先过滤后连接通过子查询确保先过滤SC表SELECT s.* FROM ( SELECT s_id FROM SC WHERE c_id 0 AND score 100 ) t INNER JOIN Student s ON t.s_id s.s_id;执行时间0.054秒5. 第三轮优化联合索引策略5.1 数据量增长后的性能回退当SC表数据增长到300万条时之前的优化SQL执行时间回升到0.061秒。分析发现c_id81的记录70001条score84的记录39425条c_id81 AND score84的记录897条5.2 创建联合索引删除单列索引创建(c_id, score)联合索引ALTER TABLE SC DROP INDEX idx_sc_c_id; ALTER TABLE SC DROP INDEX idx_sc_score; CREATE INDEX idx_sc_cid_score ON SC(c_id, score);优化后执行时间0.007秒6. 执行计划深度解析6.1 最优执行计划解读------------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------ | 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 897 | NULL | | 1 | PRIMARY | s | eq_ref | PRIMARY | PRIMARY | 4 | t.s_id | 1 | NULL | | 2 | DERIVED | sc | ref | idx_sc_cid_score | idx_sc_cid_score | 9 | const,const | 897 | Using index | ------------------------------------------------------------------------------------------------------关键点首先利用联合索引快速定位SC表中符合条件的897条记录通过主键关联Student表获取学生信息整个过程完全使用索引没有全表扫描7. 索引优化进阶技巧7.1 多列索引的最左前缀原则对于索引(c_id, score, s_id)WHERE c_id0→ 可用WHERE c_id0 AND score100→ 可用WHERE score100→ 不可用7.2 索引覆盖优化查询只返回索引包含的字段时无需回表SELECT c_id, score FROM SC WHERE c_id0 AND score100;执行时间0.003秒7.3 索引选择性原则建立索引前评估字段区分度SELECT COUNT(DISTINCT c_id)/COUNT(*) AS c_id_selectivity, COUNT(DISTINCT score)/COUNT(*) AS score_selectivity FROM SC;8. 实战经验总结子查询陷阱MySQL 5.6对IN子查询的优化仍不完善优先考虑JOIN重构索引策略联合索引比多个单列索引更高效区分度高的字段应放在联合索引左侧执行计划分析关注type列ALL→index→range→ref→eq_ref→const检查是否出现Using filesort或Using temporary数据量影响小数据量时索引效果不明显数据增长后索引策略需要重新评估9. 生产环境索引规范建立索引WHERE条件中的高频字段JOIN关联字段ORDER BY/GROUP BY字段避免索引区分度低的字段如性别频繁更新的字段索引维护定期分析慢查询日志使用pt-index-usage工具监控索引使用情况这个案例让我深刻体会到同样的SQL在不同数据量下可能表现出完全不同的性能特征。建议每季度对核心表进行索引review确保索引策略始终适应当前数据规模
1. 从30秒到1毫秒一次SQL优化实战全记录上周排查线上慢查询时意外发现一个执行时间长达30秒的SQL语句。经过系列优化后最终将执行时间压缩到1毫秒以内。这个案例非常典型涉及子查询优化、索引策略、执行计划分析等核心知识点特别记录下完整优化过程。2. 问题场景还原2.1 数据库环境与表结构我们使用MySQL 5.6版本涉及三张核心表课程表CourseCREATE TABLE Course( c_id INT PRIMARY KEY, name VARCHAR(10) ) ENGINEInnoDB;数据量100条课程记录学生表StudentCREATE TABLE Student( s_id INT PRIMARY KEY, name VARCHAR(10) ) ENGINEInnoDB;数据量7万条学生记录成绩表SCCREATE TABLE SC( sc_id INT PRIMARY KEY, s_id INT, c_id INT, score INT, FOREIGN KEY (s_id) REFERENCES Student(s_id), FOREIGN KEY (c_id) REFERENCES Course(c_id) ) ENGINEInnoDB;数据量70万条成绩记录2.2 原始SQL与性能问题业务需求是查询语文考试c_id0得100分的学生信息原始SQL如下SELECT s.* FROM Student s WHERE s.s_id IN ( SELECT s_id FROM SC WHERE c_id 0 AND score 100 );执行时间30248.271秒约8.4小时3. 第一轮优化基础索引策略3.1 执行计划分析使用EXPLAIN查看执行计划EXPLAIN SELECT s.* FROM Student s WHERE s.s_id IN ( SELECT s_id FROM SC WHERE c_id 0 AND score 100 );执行计划显示两张表都使用全表扫描typeALL没有使用任何索引预估扫描行数Student表7万行SC表70万行3.2 添加单列索引在WHERE条件字段上创建索引CREATE INDEX idx_sc_c_id ON SC(c_id); CREATE INDEX idx_sc_score ON SC(score);优化后执行时间1.054秒性能提升约3万倍但1秒的响应时间对于线上服务仍不理想。4. 第二轮优化子查询重构4.1 分析优化后的执行计划查看优化后的实际执行SQL通过SHOW WARNINGSSELECT s.s_id, s.name FROM Student s WHERE in_optimizer( s.s_id, EXISTS( SELECT 1 FROM SC sc WHERE (sc.c_id 0) AND (sc.score 100) AND (CACHE(s.s_id) sc.s_id) ) )问题分析MySQL将IN子查询转换为EXISTS相关子查询执行顺序变为先遍历Student表再循环查询SC表总操作次数7万学生× 8满足条件的成绩≈ 56万次4.2 改为连接查询重构为JOIN形式SELECT s.* FROM Student s INNER JOIN SC sc ON sc.s_id s.s_id WHERE sc.c_id 0 AND sc.score 100;执行时间0.057秒4.3 强制先过滤后连接通过子查询确保先过滤SC表SELECT s.* FROM ( SELECT s_id FROM SC WHERE c_id 0 AND score 100 ) t INNER JOIN Student s ON t.s_id s.s_id;执行时间0.054秒5. 第三轮优化联合索引策略5.1 数据量增长后的性能回退当SC表数据增长到300万条时之前的优化SQL执行时间回升到0.061秒。分析发现c_id81的记录70001条score84的记录39425条c_id81 AND score84的记录897条5.2 创建联合索引删除单列索引创建(c_id, score)联合索引ALTER TABLE SC DROP INDEX idx_sc_c_id; ALTER TABLE SC DROP INDEX idx_sc_score; CREATE INDEX idx_sc_cid_score ON SC(c_id, score);优化后执行时间0.007秒6. 执行计划深度解析6.1 最优执行计划解读------------------------------------------------------------------------------------------------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ------------------------------------------------------------------------------------------------------ | 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 897 | NULL | | 1 | PRIMARY | s | eq_ref | PRIMARY | PRIMARY | 4 | t.s_id | 1 | NULL | | 2 | DERIVED | sc | ref | idx_sc_cid_score | idx_sc_cid_score | 9 | const,const | 897 | Using index | ------------------------------------------------------------------------------------------------------关键点首先利用联合索引快速定位SC表中符合条件的897条记录通过主键关联Student表获取学生信息整个过程完全使用索引没有全表扫描7. 索引优化进阶技巧7.1 多列索引的最左前缀原则对于索引(c_id, score, s_id)WHERE c_id0→ 可用WHERE c_id0 AND score100→ 可用WHERE score100→ 不可用7.2 索引覆盖优化查询只返回索引包含的字段时无需回表SELECT c_id, score FROM SC WHERE c_id0 AND score100;执行时间0.003秒7.3 索引选择性原则建立索引前评估字段区分度SELECT COUNT(DISTINCT c_id)/COUNT(*) AS c_id_selectivity, COUNT(DISTINCT score)/COUNT(*) AS score_selectivity FROM SC;8. 实战经验总结子查询陷阱MySQL 5.6对IN子查询的优化仍不完善优先考虑JOIN重构索引策略联合索引比多个单列索引更高效区分度高的字段应放在联合索引左侧执行计划分析关注type列ALL→index→range→ref→eq_ref→const检查是否出现Using filesort或Using temporary数据量影响小数据量时索引效果不明显数据增长后索引策略需要重新评估9. 生产环境索引规范建立索引WHERE条件中的高频字段JOIN关联字段ORDER BY/GROUP BY字段避免索引区分度低的字段如性别频繁更新的字段索引维护定期分析慢查询日志使用pt-index-usage工具监控索引使用情况这个案例让我深刻体会到同样的SQL在不同数据量下可能表现出完全不同的性能特征。建议每季度对核心表进行索引review确保索引策略始终适应当前数据规模
