MySQL查询优化实战：从执行计划到索引设计，彻底告别慢查询

MySQL 查询优化这事说难不难说简单也真不简单。我做过不少数据库应用开发和性能调优的活见过太多“代码能跑就行”的项目上线三个月后接口开始一个比一个慢一查慢查询日志全是些看起来挺正常、实际执行计划离谱的 SQL。真正吃透 MySQL 查询优化不是背几条“加索引、别用 SELECT *”的规则就完事而是要搞清楚一条 SQL 从客户端发出去到结果返回数据库内部到底经历了什么慢在哪个环节以及为什么优化器会选出一个糟糕的执行计划。这篇文章把原理、定位手段和实战场景串起来讲适合刚接触数据库优化的开发同学也适合正在被慢查询折磨的运维和全栈工程师读完之后你至少能自己动手排查 90% 的常见慢查询问题。1. 先把原理讲透为什么你的 SQL 会慢优化这件事最忌讳上来就乱加索引。你连一条查询为什么慢都不知道加再多索引也是碰运气。所以这一章我们先不谈技巧把 MySQL 内部的工作机制说清楚。1.1 一条查询在 MySQL 内部是怎么跑的一条 SQL 从发出到返回会经过连接器、分析器、优化器、执行器最后才到存储引擎。很多开发同学对“存储引擎”有误解以为 MySQL 就是一张大表其实 MySQL 是分层的上层负责解析和优化真正存数据、读数据的是 InnoDB 这类存储引擎。连接器负责校验账号密码和权限建立连接后分配一个线程来处理后续请求。分析器做词法分析和语法分析SQL 写错了它第一个报错。优化器是“最需要背锅”的角色它决定用哪个索引、表连接顺序怎么排、子查询怎么转换所有这些决策都会直接影响最终耗时。执行器拿到优化器生成的执行计划再去调用 InnoDB 的接口一行一行地把数据取出来。打个比方一条查询就像你去餐厅点菜连接器是前台接待分析器是帮你读菜单的服务员优化器是后厨里决定先切什么、后炒什么的配菜师傅执行器才是真正掌勺的而 InnoDB 是食材仓库。绝大多数慢查询问题都出在配菜师傅的决策上——他明明可以用冷藏室里的半成品偏要去仓库现杀一头猪。1.2 优化器怎么“算账”成本模型是关键MySQL 使用的是一种基于成本的优化器CBO它会根据表的统计信息给每个候选执行计划“算账”。算账主要看两方面一个是 IO 成本一个是 CPU 成本。全表扫描的成本就是扫描所有数据页的成本加处理每行数据的 CPU 成本走索引的成本则是访问索引页、回表读取数据页的成本再加上符合条件那部分数据的过滤成本。这里有个特别关键的细节InnoDB 的统计信息是采样估算出来的不是精确值。如果表的统计信息长时间不更新或者数据分布发生了变化优化器就可能拿着过期的“账本”做决策选出一个实际上很慢的执行计划。这也是为什么有时候你明明建了索引EXPLAIN 一看却是全表扫描——优化器可能觉得索引选择性太差回表成本太高不如全表扫一遍。所以遇到慢查询第一步不要急着骂 MySQL“不智能”先看统计信息是否准确必要时执行一下ANALYZE TABLE更新统计信息。在 MySQL 8.0.18 之后的版本你还可以用EXPLAIN FORMATTREE直接看到优化器估算的成本区间这对理解“为什么走这个索引”非常有帮助。2. 先定位病根慢查询诊断三板斧知道原理之后接下来要解决一个实际问题怎么把那条拖垮系统的 SQL 找出来。我一般按三步走开慢查询日志、用 EXPLAIN 看执行计划、用 profiling 或 performance_schema 做精确测量。这一套组合拳打下来慢查询基本无处遁形。2.1 慢查询日志成本最低的第一道筛子慢查询日志是排查慢 SQL 的第一入口它会把执行时间超过阈值的 SQL 原样记录到文件里。配置要点就几个参数slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1生产环境我一般把long_query_time设为 1 秒也就是超过 1 秒的查询都要记录下来。log_queries_not_using_indexes这个参数特别值得开它会把所有没走索引的查询也记下来很多“看起来快但迟早出问题”的 SQL 就是被它揪出来的。需要注意的是long_query_time是当着新连接生效的你改完全局参数已经存在的连接可能还是老阈值。排查的时候先用SHOW VARIABLES LIKE long_query_time确认一下当前会话到底用的哪个值。慢查询日志文件会越来越大建议配合mysqldumpslow做聚合汇总只看 TOP 10 的慢 SQL。如果慢查询量很大可以用pt-query-digest生成一份更可读的报告它会按总耗时、平均耗时、出现次数多个维度排序比对着原始日志一行行翻效率高得多。2.2 EXPLAIN 不是摆设每个字段都有用拿到一条慢 SQL 后立刻在它前面加EXPLAIN跑一遍看执行计划。有人觉得 EXPLAIN 只是看有没有用到索引其实每个字段都有信息量。我挑几个最关键的展开说。type字段表示访问类型从好到差大致是const、eq_ref、ref、range、index、ALL。ALL就是全表扫描基本是性能瓶颈的代名词index虽然会遍历整个索引但数据量通常比全表小range说明用上了索引做范围扫描算是合格线ref表示普通的非唯一索引等值匹配日常查询里比较理想的访问级别。key是优化器实际选中的索引key_len是使用到的索引字节数这个字段很多人忽略。同样的组合索引key_len越长说明用到的索引列越多优化越充分。rows是优化器估算的扫描行数不是精确值但可以作为“这次查询大概要碰多少行”的参考。Extra字段里藏着两个常见红灯Using filesort表示查询结果需要额外排序Using temporary表示用了临时表。这两种情况一旦出现查询基本就快不了后面我会专门讲怎么消除它们。下面是一条示例EXPLAIN SELECT id, order_no, amount FROM orders WHERE user_id 123 ORDER BY created_at DESC LIMIT 20;执行计划大致长这样----------------------------------------------------------------------------------------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ----------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | ref | idx_user_id | idx_user_id| 8 | const | 476 | Using filesort | -----------------------------------------------------------------------------------------------注意看这条 SQL 虽然用到了idx_user_id索引但Extra里出现了Using filesort说明在索引定位完 476 行数据之后MySQL 还要额外做一次排序才能返回前 20 条。如果订单量越来越大这个排序的开销会逐渐成为问题。2.3 精确测量profiling 与 performance_schemaEXPLAIN 看的是执行计划但有些问题不是计划错了而是某个阶段耗时异常。比如一条 SQL 锁等待了 3 秒执行计划再完美也没用。这时候需要精确测量各阶段耗时。MySQL 5.x 时代我习惯用SHOW PROFILESET profiling 1; SELECT ...; SHOW PROFILES; SHOW PROFILE FOR QUERY 2;不过 MySQL 8.0 已经明确标记SHOW PROFILE为废弃推荐用 performance_schema。先开启相关消费者UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %events_statements%;然后查历史语句的耗时明细SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT, LOCK_TIME, ROWS_EXAMINED FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE %orders% ORDER BY TIMER_WAIT DESC LIMIT 10;TIMER_WAIT的单位是皮秒看着数字很大除以 10 的 12 次方才是秒。LOCK_TIME是锁等待时间ROWS_EXAMINED是实际扫描行数。这个表能帮你判断到底是扫描行数太多、锁等待太久还是排序太耗资源。3. 索引设计与场景化优化最优解是怎么长出来的定位到慢查询之后绝大多数优化的落脚点都在索引上。但索引不是随便建几个就行建多了不但浪费磁盘空间还会拖慢写入速度。这一章我把索引设计的底层逻辑和实战操作一起讲透。3.1 B 树索引为什么快快在哪儿说到索引绕不开 B 树。很多人听到“B 树”就头大其实它没有想象中复杂。你可以把 InnoDB 的索引想象成一棵树形的目录最上层是根页往下是若干内节点页最底层是叶子页叶子页之间还用双向链表串了起来。为什么 B 树查询快关键在于树的层数矮。InnoDB 一个数据页默认 16KB假设主键是 8 字节的 bigint加上指向下一层页的指针大约 6 字节一个内节点页大概能存上千条索引项。三层 B 树就能覆盖上千万行数据也就是说你查任意一行数据都只需要走三次页访问根页、中间页、叶子页。这就好比你在一个千万人口的城市找一个人不需要挨家挨户敲门用城市地图先定位到区再定位到街道最后直接找到门牌号几步就到了。InnoDB 里有两个层次的索引概念必须分清。聚簇索引以主键为索引键叶子节点直接存放整行数据二级索引的叶子节点存放的是索引列的值加主键值。所以通过二级索引查询如果需要的列不在索引里还要拿着主键再去聚簇索引里捞一次数据这个过程叫回表。回表是随机 IO每回一次表就是一次页读取数据量大时会有明显的性能损耗。3.2 组合索引的最左前缀设计顺序有讲究单个字段的索引很多时候满足不了真实查询我们需要组合索引。组合索引的排序规则是先按第一个列排序第一列相同的再按第二列排序以此类推。这意味着查询条件里必须包含组合索引的最左列索引才能被用上这就是“最左前缀原则”。举个例子表里有组合索引(user_id, status, created_at)那么能用到完整索引的查询条件是WHERE user_id ? AND status ? AND created_at ?也能用到前两列的WHERE user_id ? AND status ?但如果只写WHERE status ?这个索引基本就废了。设计组合索引的顺序有几个经验法则等值查询的列放前面范围查询和排序的列放后面区分度高的列优先经常用于排序的列考虑放进索引以消除文件排序。我以一个订单表的分页查询为例说明SELECT id, order_no, amount, created_at FROM orders WHERE user_id 123 AND status 1 ORDER BY created_at DESC LIMIT 20;这里user_id和status都是等值条件created_at用于排序。组合索引设计成(user_id, status, created_at)最合适前两列用于快速定位第三列直接在索引里排好序查询结果不需要再额外 filesort。如果你把索引建成(created_at, user_id, status)等值过滤条件放后面性能会差很多。3.3 覆盖索引与索引失效两个高频坑覆盖索引是查询优化里性价比极高的一招。所谓覆盖索引就是查询所需的所有列都能从某个二级索引里拿到不需要回表。这种情况下 EXPLAIN 的Extra字段会显示Using index这意味着所有的数据读取都在索引页内完成速度非常快。还是上面的例子如果查询列只保留id, user_id, status那么索引(user_id, status)就能完全覆盖连回表都省了。但如果你习惯写SELECT *覆盖索引就很难生效因为你要取所有列索引里放不下只能回表。这也是“尽量别用 SELECT *”的核心原因之一不只是网络传输的问题更关键的是它摧毁了覆盖索引的优化空间。说完覆盖索引再说索引失效。以下几个场景是我在排查中遇到最多的WHERE条件里对索引列使用函数比如WHERE DATE(created_at) 2025-01-01索引会失效正确写法是WHERE created_at 2025-01-01 00:00:00 AND created_at 2025-01-02 00:00:00。隐式类型转换也常见比如phone是 varchar 类型你写WHERE phone 13800138000MySQL 会把字段转成数字再比较索引也会失效。还有LIKE %abc这种前导通配因为没法从索引树定位起点只能全索引扫描。4. 高频场景 SQL 优化实操从慢到快的完整过程原理和工具都讲完了这一章我们落到具体场景。我会按真实项目中最高频的几类问题给出前后对比的优化方案。4.1 深分页优化LIMIT 一百万行不卡才怪分页是最常见的查询形态也是翻车最严重的地方。很多人会写出这样的 SQLSELECT id, order_no, amount, created_at FROM orders ORDER BY created_at DESC LIMIT 1000000, 20;你以为是只取 20 条但 MySQL 的实际做法是先扫描前 1000020 行然后把前 100 万行丢掉只返回最后 20 行。越往后翻页扫描的行数越多接口响应自然越来越慢。解决思路有两种。第一种是延迟关联。先在索引上完成分页只取主键再回表查询完整数据SELECT o.id, o.order_no, o.amount, o.created_at FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 20 ) t ON o.id t.id;子查询只需要在索引上扫描不需要回表速度会快非常多。第二种是游标分页适合“下一页”形式的客户端SELECT id, order_no, amount, created_at FROM orders WHERE created_at 2025-01-01 00:00:00 ORDER BY created_at DESC LIMIT 20;游标分页的精髓是每次带上上一页最后一条记录的排序字段值用WHERE条件直接过滤掉前面所有的行从第一页到第一百页扫描的行数基本一致。缺点是不支持任意跳页但大多数真实的 C 端场景用户根本不会真的去点第 5 万页。4.2 JOIN、子查询与 EXISTS怎么选才不吃亏关联查询优化有一个核心原则小表驱动大表连接字段必须有索引。这里的“小表”不是指表的物理大小而是过滤条件应用之后参与关联的行数少的那张表。看一个典型例子SELECT u.id, u.nickname, o.order_no FROM users u INNER JOIN orders o ON o.user_id u.id WHERE u.status 1 ORDER BY o.created_at DESC LIMIT 20;这条 SQL 里优化器会先根据u.status 1过滤用户表再用过滤后的用户 ID 挨个去订单表里找订单。如果订单表的user_id没有索引每次关联都要全表扫一遍订单表后果就是灾难性的。所以关联字段orders.user_id必须建索引这是 JOIN 查询的第一条底线。至于 IN 子查询和 EXISTS我的经验是不要迷信“IN 一定慢、EXISTS 一定快”。现代 MySQL 对 IN 子查询做了半连接优化很多情况下 IN 和 JOIN 会被优化成同一种执行计划。真正要小心的场景是子查询作为“过滤器”时返回的集合大小如果子查询结果集很大用 EXISTS 或 JOIN 通常更稳如果结果集很小IN 的写法更清晰且性能不差。任何结论都要以 EXPLAIN 为准。4.3 ORDER BY、GROUP BY 的索引化改造ORDER BY慢的根源通常是文件排序。文件排序不一定是磁盘排序MySQL 会优先在内存的排序缓冲区里排序数据量超了才落盘但无论哪种都意味着额外的时间开销。消除文件排序的最佳方式是让索引的天然顺序直接满足排序要求。看这条 SQLSELECT id, user_id, amount FROM orders WHERE user_id 123 ORDER BY created_at DESC LIMIT 10;只要有组合索引(user_id, created_at)InnoDB 在扫描索引时已经按user_id等值过滤并且created_at在同一索引内是排好序的MySQL 直接用索引顺序返回结果Extra里就不会再出现Using filesort。GROUP BY 的优化思路类似核心是想办法用松散索引扫描代替临时表。例如SELECT user_id, COUNT(*), SUM(amount) FROM orders WHERE created_at 2025-01-01 GROUP BY user_id;这里的优化方向是让分组字段user_id成为组合索引的最左前缀这样 MySQL 可以直接在索引上按顺序扫描分组避免先建临时表再聚合。需要提醒的是MySQL 的分组默认会附带一个按分组字段排序的动作如果排序结果不是业务必须可以在 SQL 末尾加ORDER BY NULL让 MySQL 省掉一次排序开销。5. 常见问题排查与避坑实录这一章我把自己这些年踩过的坑和排查经验整理出来做成一个偏“速查”的章节遇到症状直接对号入座。5.1 索引没生效按这张表自查症状常见原因处理方式EXPLAIN 显示 typeALL隐式类型转换、函数包裹、前导通配改写 WHERE 条件保证索引列不被加工明明走了索引还是很慢回表次数太多或扫描区间过宽设计覆盖索引或者缩小范围条件rows 与实际行数严重不符统计信息过期执行ANALYZE TABLE更新统计信息两个表 JOIN 很慢连接字段字符集不一致统一两边字段的字符集和排序规则组合索引只用到一部分查询条件顺序不符合最左前缀调整 SQL 条件顺序或组合索引列顺序范围之后的条件没生效建索引时范围列放前面把等值列放索引前面范围列放后面字符集不一致导致的 JOIN 慢特别隐蔽。比如 A 表字段是utf8mb4B 表字段是utf8MySQL 做关联时要把其中一个字段隐式转换成另一个字符集才能比较转换一旦发生索引基本就废了。排查方式很简单看 EXPLAIN 里关联查询的两张表key是否为空。5.2 参数与架构层面的配合单条 SQL 优化之外有时候单条 SQL 已经优化到极限但系统整体还是慢。这时候要把视角放大到数据库参数和架构层面。innodb_buffer_pool_size是 InnoDB 最重要的内存参数它决定了数据页在内存里能缓存多少。一般建议设置为物理内存的 60% 到 70%设置太小会导致频繁的磁盘读设置太大又会挤占操作系统和其他进程的内存引发 swap。max_allowed_packet决定单次能接受的包大小做大批量数据写入或者查询结果集特别大时报错Packet too large要找它。sort_buffer_size和join_buffer_size是会话级参数不是越大越好每个连接都会分配自己的缓冲区调太大会造成内存浪费。连接池也是很多人容易搞错的地方。连接的合理数量不是越大越好每个连接背后都有一个线程线程一多上下文切换的代价反超收益。我一般建议连接池大小和 CPU 核心数挂钩起步用核数 * 2再加少量余量然后通过压测逐步调整。很多“数据库没挂、但应用卡死”的问题根子都是应用侧连接池建得过大把数据库线程池打满了。5.3 遇到慢查询后的完整排查顺序最后分享一套我遇到慢查询时的固定排查流程照着走一般不会漏先看慢查询日志里那条 SQL 的出现频率和耗时规律确认是不是偶发。偶发问题优先怀疑锁和资源竞争持续性问题再往下走。然后执行EXPLAIN看执行计划重点看type、key_len、rows和Extra。如果行数估算和实际差距大执行ANALYZE TABLE再查一次。接着排查表数据分布比如某个status值占比极高优化器可能因此放弃索引这是业务数据导致的正常现象需要用索引提示或改写 SQL 绕过。如果执行计划没问题但实际还是慢就要看锁等待和资源消耗。查performance_schema的events_statements_history_long看耗时是否集中在LOCK_TIME查sys.innodb_lock_waits确认有没有长时间持锁的事务卡住后续查询。整个排查过程中每改动一处都要用相同的数据和条件做前后对比最好记录执行时间避免“感觉快了”的主观判断。我个人在实际操作中体会最深的一点是查询优化不是一次性决战而是持续迭代。每次上线前我会把核心接口对应的 SQL 全部跑一遍 EXPLAIN把type低于range、Extra出现Using filesort或Using temporary的 SQL 单独记录下来能当场改就当场改。平时养成长开慢查询日志的习惯阈值设 1 秒每周抽空看一次 TOP 慢查询长期坚持下来系统出问题的概率会明显下降。最后再分享一个小技巧遇到一条 SQL 怎么调都慢先用EXPLAIN FORMATTREE看看成本再结合业务想想能不能换一种写法很多时候不是 MySQL 不行而是我们问问题的方式不对。