MySQL慢查询优化实战教程：200万数据从3秒优化到50ms（EXPLAIN + 索引设计 + 延迟关联）

手把手带你用 EXPLAIN 索引优化 SQL 改写把一条 3 秒的慢查询干到50ms 以内。背景最近在做一个电商项目的订单列表查询页面加载巨慢。打开 Chrome DevTools 一看一个接口响应 3.2 秒。排查下来罪魁祸首是一条 SQL。这篇文章记录完整的排查和优化过程希望对你有帮助。第一步定位慢查询1.1 开启慢查询日志先确认 MySQL 慢查询日志是否开启SHOWVARIABLESLIKEslow_query%;SHOWVARIABLESLIKElong_query_time;如果没开临时开启SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 超过1秒就记录SETGLOBALslow_query_log_file/var/log/mysql/slow.log;生产环境建议写到my.cnf里持久化[mysqld] slow_query_log 1 long_query_time 1 slow_query_log_file /var/log/mysql/slow.log1.2 找到问题 SQL用mysqldumpslow快速分析慢查询日志mysqldumpslow-sat-t10/var/log/mysql/slow.log定位到这条 SQLSELECTo.id,o.order_no,o.total_amount,o.status,o.created_at,u.nickname,u.phoneFROMorders oLEFTJOINusers uONo.user_idu.idWHEREo.status2ANDo.created_atBETWEEN2026-01-01AND2026-03-01ORDERBYo.created_atDESCLIMIT20OFFSET0;看起来很普通但在 200 万订单数据量下跑了 3.2 秒。第二步用 EXPLAIN 分析执行计划EXPLAINSELECTo.id,o.order_no,o.total_amount,o.status,o.created_at,u.nickname,u.phoneFROMorders oLEFTJOINusers uONo.user_idu.idWHEREo.status2ANDo.created_atBETWEEN2026-01-01AND2026-03-01ORDERBYo.created_atDESCLIMIT20OFFSET0;结果idselect_typetabletypepossible_keyskeyrowsExtra1SIMPLEoALLNULLNULL2034567Using where; Using filesort1SIMPLEueq_refPRIMARYPRIMARY1NULL三个致命问题一眼看出来type ALL— 全表扫描200 万行一行行找key NULL— 没命中任何索引Using filesort— 排序走的磁盘临时文件不是索引排序第三步优化方案3.1 创建合适的联合索引分析 WHERE 和 ORDER BY 的字段WHERE status 2— 等值查询WHERE created_at BETWEEN ...— 范围查询ORDER BY created_at DESC— 排序联合索引的设计原则等值条件放前面范围/排序字段放后面。ALTERTABLEordersADDINDEXidx_status_created(status,created_at);为什么这样设计status是等值查询放第一位先大幅缩小数据范围created_at既是范围查询又是排序字段放第二位索引天然有序可以避免 filesort3.2 再次 EXPLAIN 验证加完索引后再跑一次 EXPLAINidselect_typetabletypepossible_keyskeyrowsExtra1SIMPLEorangeidx_status_createdidx_status_created8543Using index condition1SIMPLEueq_refPRIMARYPRIMARY1NULL变化很明显type: ALL → range— 从全表扫描变成索引范围扫描rows: 2034567 → 8543— 扫描行数降了 200 多倍filesort 消失了— 排序直接走索引此时查询耗时3.2s → 120ms。好了很多但还能更快。3.3 使用覆盖索引进一步优化当前 SQL 还需要回表查order_no、total_amount等字段。如果查询的字段都在索引里就不用回表了覆盖索引。但订单表字段太多全放索引不现实。换个思路——先查 ID再关联取数据SELECTo.id,o.order_no,o.total_amount,o.status,o.created_at,u.nickname,u.phoneFROMorders oINNERJOIN(SELECTidFROMordersWHEREstatus2ANDcreated_atBETWEEN2026-01-01AND2026-03-01ORDERBYcreated_atDESCLIMIT20OFFSET0)tONo.idt.idLEFTJOINusers uONo.user_idu.id;这就是经典的延迟关联Deferred Join技巧子查询只查id完全走覆盖索引不回表外层用id精确取 20 条数据最终耗时120ms → 45ms。效果对比指标优化前第一次优化最终效果扫描行数2,034,5678,54320是否 filesort是否否是否回表是全表是8543次是20次响应时间3,200ms120ms45ms踩坑记录坑 1联合索引字段顺序搞反了一开始建了INDEX(created_at, status)结果 EXPLAIN 显示还是全表扫描。原因created_at是范围查询放在前面会导致status无法利用索引。联合索引遵循最左前缀原则范围查询会“截断”后面的字段。正确的顺序是等值字段在前范围字段在后。坑 2OFFSET 过大导致深分页变慢当用户翻到第 1000 页时LIMIT 20 OFFSET 20000即使有索引也会很慢因为 MySQL 会扫描前 20000 条再丢掉。解决方案——游标分页用上一页最后一条的created_at和id做条件SELECTidFROMordersWHEREstatus2ANDcreated_at2026-02-15 13:20:00ANDid12345ORDERBYcreated_atDESC,idDESCLIMIT20;这样无论翻到第几页性能都是稳定的。坑 3时间字段用字符串比较-- 错误写法对索引列做了隐式类型转换索引失效WHEREDATE(created_at)2026-01-01-- 正确写法保持索引列原样WHEREcreated_at2026-01-01 00:00:00ANDcreated_at2026-01-02 00:00:00优化速查表遇到慢查询时按这个顺序排查步骤动作关注点1EXPLAIN看执行计划type 是否 ALLkey 是否 NULL2检查索引WHERE/ORDER BY 字段有没有合适的索引3检查索引顺序等值在前范围在后4检查是否回表能否用覆盖索引或延迟关联5检查分页方式OFFSET 大不大能否改游标分页6检查隐式转换索引列上有没有函数调用或类型转换总结这次优化的核心就三步加联合索引(status, created_at)— 等值在前范围在后延迟关联— 先查 ID 再取数据减少回表避免深分页— 用游标分页替代 OFFSET200 万数据量从 3.2 秒干到 45 毫秒70 倍提升。索引设计是后端的基本功但很多人只知道“加索引”不知道怎么加对。希望这篇文章能帮你建立系统的排查思路。关于作者后端开发工程师坐标北京专注高性能后端架构与数据库优化。欢迎交流有技术需求也可以私信我。