你往 MySQL 丢了一条 SQL表面上它只回给你一个结果集但在这几毫秒到几十毫秒里它至少穿过了连接器、分析器、优化器、执行器最后才和存储引擎打交道。这些年我处理过太多“加了索引也没用”“同样的 SQL 一会儿快一会儿慢”的案例追到底都是同一个问题大家并不清楚 MySQL 内部是怎么给这条 SQL 做决策的。这篇文章就拿一条再常见不过的 SELECT 查询把从客户端到返回结果的每一站拆开讲清楚顺手把优化器为什么选这个索引、为什么不选那个索引的底层逻辑也一并说透。适合刚入门想搞懂执行原理的同学也适合已经写过不少 SQL、但一直对执行计划半懂不懂的老手。1. 进入大门之前先把身份验清楚连接器的那些小事1.1 客户端和 MySQL 的第一次握手很多人以为 SQL 执行是从解析开始的其实不对。在 MySQL 收到你的 SQL 文本之前它首先要确认“你是谁”。客户端连接数据库走的是 MySQL 自有的协议默认端口 3306。这个过程包括 TCP 三次握手、协议版本协商以及最重要的认证步骤——MySQL 会拿着你的用户名、客户端 IP、密码去mysql.user等授权表里核对身份。MySQL 8.0 默认的认证插件是caching_sha2_password这个名字有点长你只需要记住一点如果程序连接 8.0 数据库报认证失败大概率是驱动版本太老、不支持这个新插件。我见过不少生产事故升级数据库到 8.0 之后应用程序集体连不上报Authentication plugin caching_sha2_password cannot be loaded最后都是换驱动或调整认证插件解决的。身份验证通过之后连接器还会顺便记录一些状态信息比如连接时间、当前用户、连接 ID。这个连接 ID 就是你在processlist里看到的那个数字排查问题的时候非常有用后面我们排查慢 SQL 时会用到。1.2 连接数量和线程模型为什么“连接数太高”也是一种故障MySQL 的每个客户端连接在服务端都会对应一个线程。传统模型下这几乎是 1:1 的关系。所以当你看到Threads_connected飙到几百上千的时候即使这些连接什么都不干也会占用线程栈、内存等资源。这里有个常见的坑应用层用了连接池但连接池的最小连接数设置得很大加上每个连接又长期不释放MySQL 的max_connections默认才 151稍微一冲就爆了报错Too many connections。遇到这个问题不要急着把max_connections调到几千那个治标不治本。先看wait_timeout和interactive_timeout把空闲连接断掉再检查应用连接池的maxActive是否合理。实际环境中核心业务系统通常几百个连接就很紧张了优化到合理范围远比盲目调上限靠谱。1.3 查询缓存一个已经在 MySQL 8.0 里消失的老朋友MySQL 5.7 及更早版本在分析器之前还有一层查询缓存。它会根据 SQL 文本做精确匹配如果完全一样且缓存有效直接返回结果连解析和优化都省了。听起来很美好但它的失效机制是表级粒度的只要这张表有任何数据变更所有和该表相关的查询缓存全部失效。这就导致一个极其蛋疼的局面——写操作频繁的表查询缓存几乎一直在被打断命中率低得可怜。而且维护缓存本身还有锁开销反而拖累了并发。所以 MySQL 8.0 直接把查询缓存整个干掉了社区几乎没人反对。我自己的经验是与其指望查询缓存兜底不如老老实实把索引和 SQL 写好业务上真需要实时性高的缓存交给 Redis 那类专门做缓存的东西别让数据库承担这个职责。2. 从一段文本变成一棵树分析器和预处理到底在干什么2.1 词法分析和语法分析把 SQL 切碎再组装过了连接器MySQL 开始真正处理你的 SQL 文本。第一步是词法分析把 SQL 拆成一个个“单词”和符号第二步是语法分析根据 MySQL 定义的语法规则把这些单词组装成一颗语法树。拿一条具体的 SQL 来看SELECT id, name, age, city_id FROM user WHERE age 30 AND city_id 100 ORDER BY id LIMIT 20;词法分析阶段SELECT、id、FROM、user、WHERE这些会被识别为不同类别的 token、、AND会被识别为运算符。然后语法分析器根据语法规则确定这是一条查询语句user是表名id/name/age/city_id是目标列WHERE age 30 AND city_id 100是过滤条件ORDER BY id是排序LIMIT 20是行数限制。这一步如果出了问题你会看到经典的报错信息You have an error in your SQL syntax。大概率是少了括号、多了逗号、关键字拼错这类低级问题。很多新手以为这是“SQL 执行失败”严格来说它根本还没走到执行阶段在语法检查这一关就被打回去了。2.2 预处理阶段检查表和列的真实身份语法树构建完毕接下来做语义检查。这一步 MySQL 要确认你写的表名存在吗列名存在吗这个函数存在吗有没有语法上合理但实际上不存在的标识符比如上面这条 SQL如果user表里根本没有city_id这个列预处理阶段就会报Unknown column city_id in where clause。这个地方你有没有想过为什么报错不是“找不到表”而是“未知的列”因为 MySQL 要先去查表结构把名字映射到实际的表字段编号上。预处理阶段还会做权限检查看看你有没有这张表的SELECT权限、某些列的访问权限。表级权限、列级权限、存储过程权限这些都是在这一层校验的。我之前遇到过一个数据安全问题一个业务账号能够查询某张表的所有数据但业务上只想让它查部分列。解决方式就是回收表级SELECT只授予需要的列权限这就是依赖预处理阶段的权限机制来实现的。2.3 视图和别名在预处理阶段就完成的“语法糖”这里再补一个多数人不注意的细节如果你查询的是视图预处理阶段会把视图的定义展开和你的 SQL 合并成一条新的 SQL。所以视图执行慢的时候别只在视图定义上加索引要去看展开之后的实际语句访问了哪些基础表索引加在基础表上才有意义。还有别名解析。比如SELECT u.name FROM user u这个u到user的映射关系是在预处理阶段确定的。后面优化器计算成本的每一步都会引用这个映射关系不再去重新解析表名。3. 优化器整条 SQL 往哪条路走是它说了算3.1 逻辑优化和物理优化两件事不是一件事解析和预处理做完SQL 已经变成一棵结构化的语法树。但 MySQL 不会按你写的字面顺序去执行。下一步是优化器的工作它要做两件事逻辑优化和物理优化。逻辑优化是在不改变语义的前提下把 SQL 变得更“好算”。比如把不必要的子查询转换成连接JOIN把EXISTS转成半连接把恒真恒假的条件下推把DISTINCT在某些情况下转成GROUP BY把ORDER BY使用的列如果已经有索引就尝试避免排序。这些变换同时会让执行计划更容易选择到好的访问路径。物理优化则是真正决定“用哪个索引、按什么顺序连接多张表、用哪种 join 算法”。这一步的核心是成本模型。MySQL 会估算各个候选方案的“代价”然后挑一个总代价最低的执行方案执行。3.2 成本模型MySQL 为什么有时候宁可全表扫描也不用索引这是整篇文章最值得关注的一点。很多开发者的困惑是“我明明创建了索引为什么 EXPLAIN 显示 type 是 ALL全表扫描”关键在于成本估算。MySQL 的成本模型会综合考虑 IO、CPU、扫描的行数、回表的次数、排序的代价等。我给你举个例子SELECT id, name, age, city_id FROM user WHERE age 30 ORDER BY id LIMIT 20;如果age 30这个条件能匹配到全表 80% 的数据用idx_age索引去匹配得到的是一大批主键接下来要回表读取 80% 的行这个代价比直接全表扫描还高。所以优化器最终会选择全表扫描再过滤。从EXPLAIN结果看就是type ALL但possible_keys里明明有idx_age。这里还有个参数叫eq_range_index_dive_limit默认值是 200。当IN里面的值数量超过这个阈值时优化器不再精确评估每个值的行数而是用更粗略的估算方式这会导致某些极端情况下选错索引。这也是“同一条 SQL 换个数据量就变慢”的常见诱因之一。再看这条 SQL 里的city_id 100如果它本身过滤性很好能过滤掉绝大部分数据优化器就会走idx_city这个索引。所以大家要记住优化器判断的不是“这个列有没有索引”而是“这个索引在这里的收益够不够大”。3.3 统计信息优化器做判断的依据也不总是可靠优化器在做成本估算时依赖表和索引的统计信息包括行的数量、索引基数cardinality、选择性等。InnoDB 的统计信息通过采样的方式获取并不是实时的精确值。如果表的数据发生大幅变化统计信息没有及时更新优化器就会用一份过期的“地图”去规划路线自然容易选错。解决方式是执行ANALYZE TABLE user;手动更新统计信息。在 MySQL 8.0 中还有个innodb_stats_auto_recalc参数默认开启会在表数据变更超过一定比例时自动重新计算。但自动计算有自己的触发时机和采样范围并不是每次更新都立即生效。我在线上遇到过几次性能诡异下降最后定位下来就是统计信息不准确执行一次ANALYZE之后执行计划立刻恢复正常查询从 8 秒降回 0.02 秒。排查慢 SQL 时如果执行计划明显不符合直觉记得把这个步骤走一遍。3.4 EXPLAIN优化器把它的“想法”摆给你看优化器的最终决策通过EXPLAIN展示出来。这是大家最常用的工具但很多人只会看图里的key和rows。EXPLAIN SELECT id, name, age, city_id FROM user WHERE age 30 AND city_id 100 ORDER BY id LIMIT 20;输出大概是idselect_typetabletypepossible_keyskeyrowsfilteredExtra1SIMPLEuserrefidx_age,idx_cityidx_city2010.0Using where; Using index condition; Using filesorttype是访问类型从好到差大致是system const eq_ref ref range index ALL。type ref表示走了非唯一索引等值查询还行如果是ALL那就是全表扫描了。rows是优化器预估需要读取的行数filtered是经过WHERE过滤后剩余的比例。Extra里的Using filesort表示排序没有走索引额外做了一次排序。Using index condition表示启用了索引条件下推这个我们后面在 InnoDB 执行阶段详细说。读懂EXPLAIN的价值在于你能知道优化器是怎么想的而不是只知道“它选了哪个索引”。下一步才能判断它是选对了还是选错了。4. 从执行器到 InnoDB数据到底是怎么被“摸”到的4.1 Server 层和存储引擎层各管一段路MySQL 的架构是分层的。上面我们说的解析器、优化器都在 Server 层而真正存储数据、管理事务、处理锁的地方是存储引擎层。平时我们用的默认引擎是 InnoDB这篇文章就只讲 InnoDB。执行器拿到优化器生成的执行计划之后通过调用存储引擎的接口去读写数据。对执行器来说它不关心 InnoDB 到底是 B 树还是别的什么结构它只调用统一的 handler 接口比如index_read、read_first_row这些。这样设计的好处是换一个存储引擎Server 层逻辑不用改。在这里还会干一件事打开表同时加 MDL 锁元数据锁。MDL 锁是为了防止执行过程中表结构被ALTER TABLE改了。我以前遇到过ALTER TABLE一直卡住查processlist发现是前面有一个长事务拿着 MDL 锁不释放后面所有 DDL 都排队。这也是“一条 SQL 执行”链路里容易被忽略的锁问题。4.2 缓冲池InnoDB 断电式的内存加速InnoDB 把数据组织成页page默认每页 16KB。从磁盘读取一页数据是很慢的机械硬盘随机 IO 一次要好几毫秒SSD 也要几十微秒比起内存几个纳秒的访问延迟差了好几个数量级。所以 InnoDB 专门维护了一个缓冲池Buffer Pool把经常访问的数据页放在内存里。当你执行的查询需要读取某一行时InnoDB 会先看对应的数据页在不在缓冲池。如果在直接走内存这叫缓存命中如果不在需要从磁盘把整页读入缓冲池再返回结果。这个过程对执行器是透明的但你从性能上能感受到巨大差异。缓存命中的查询可能只要 0.1ms磁盘随机 IO 的场景就是 10ms 甚至更久。InnoDB 的缓存淘汰用的是改进版 LRU 算法并且有预读机制——读到一页时大概率把相邻的页也读进来。所以顺序扫描的时候性能会好很多。这里我分享一个经验很多慢查询的根因不是 SQL 写法问题而是 Buffer Pool 太小或者脏页刷新太频繁。遇到“同一个查询有规律地忽快忽慢”先看一眼缓冲池命中率再决定要不要优化 SQL。4.3 B 树、回表和覆盖索引三种读数据的方式InnoDB 的索引是 B 树结构。聚簇索引主键索引的叶子节点直接保存整行数据二级索引的叶子节点保存索引列的值和主键值。查询数据时有以下几种路径主键查询直接沿着聚簇索引的 B 树查找找到即返回不需要额外回表。二级索引等值/范围查询先在二级索引里找到匹配的主键值再到聚簇索引里回表取整行。覆盖索引如果查询的所有列都包含在二级索引里InnoDB 可以直接从二级索引返回结果完全不用回表。比如我们user表有idx_age二级索引包含age和主键id。如果查询是SELECT id, age FROM user WHERE age 30这个查询就能直接用二级索引返回不需要回表。但如果有SELECT name FROM user WHERE age 30那name不在索引里每次匹配到主键后都得回表拿name。回表次数一多执行时间就上去了。还有后文EXPLAIN里常见的Using index conditionICP索引条件下推MySQL 5.6 之后引入的优化把部分WHERE条件放到存储引擎层在回表之前先过滤掉不可能匹配的记录减少回表次数。这个特性对多条件查询很友好但在EXPLAIN里看到也别过度兴奋它只是说明执行器做了这个优化不代表整体执行计划是高效的。4.4 排序、临时表和 filesort慢 SQL 的三座大山ORDER BY、GROUP BY、DISTINCT这些操作并不总是能直接利用索引完成。当索引顺序和排序要求不一致或没有合适的索引时MySQL 会做一次排序操作。这个排序有个历史遗留的名字叫filesort但实际上不一定会用磁盘文件。数据量小的时候直接在内存排序区sort_buffer_size控制完成数据量超过阈值才会把中间结果落到磁盘临时文件做归并排序。sort_buffer_size不能无脑调大太大可能导致内存碎片和 OOM我一般建议从 256K 开始配合监控慢慢调。GROUP BY还经常伴随临时表。临时表有内存临时表和磁盘临时表两种。MySQL 8.0 里内存临时表默认用 TempTable 存储引擎超过tmp_table_size或max_heap_table_size的限制后转为磁盘临时表。磁盘临时表走磁盘 IO性能断崖式下跌。怎么判断看EXPLAIN的Extra列有没有Using temporary有的话就要注意了这通常是优化 SQL 的一个重要切入点。5. 结果返回与全链路视角一次查询完整走完需要几步5.1 结果集是怎么送到客户端的执行器拿到数据后还有一步是把它组装成结果集通过 MySQL 协议发给客户端。这里有几个细节MySQL 默认不是等所有行都查完才发结果而是边查边发。比如LIMIT 20查够 20 行就停止不会傻乎乎把全表都查完。这在LIMIT配合索引时能大幅节省执行时间。但有一个陷阱ORDER BYLIMIT如果排序依赖临时表或文件排序优化器通常要先把所有满足WHERE条件的数据都排完再取前 20 行。所以LIMIT对排序操作并不能起到剪枝作用这就是为什么你写LIMIT 20依然可能对一张大表做全表排序。判断方法是看执行计划里有没有Using filesort。客户端和 MySQL 之间还有网络缓冲区。如果net_buffer_length设置不合理或者客户端读取结果速度太慢也可能让查询卡在“发送数据”状态。我在processlist里看到State: Sending data时会先把这条 SQL 的执行计划和耗时拿到手再判断是数据库慢还是客户端接收慢这个经验对排查线上问题很关键。5.2 一座“时间线”看全一条 SELECT 的完整旅程为了把前面的内容串起来我给你按时间顺序列一遍客户端发起连接完成 TCP 握手、身份认证。如果是 MySQL 8.0查询缓存已移除直接进入下一步如果是老版本且命中了查询缓存直接返回缓存。分析器对 SQL 做词法和语法分析生成语法树。预处理阶段检查表名、列名、权限展开视图确定别名。优化器做逻辑优化和物理优化基于统计信息和成本模型生成执行计划。执行器按执行计划调用存储引擎接口。InnoDB 打开表并加 MDL 锁按照访问路径读取数据页涉及缓冲池、B 树查找、回表等操作。执行过程中遇到排序、分组、去重等操作时使用内存排序区或临时表。执行器逐行处理结果边处理边发送给客户端直到满足LIMIT或结果全部返回。释放临时表、锁等资源更新状态统计信息。5.3 一条 SQL 慢通常慢在哪几个环节这里我给你画个“怀疑清单”环节常见问题排查线索连接认证慢、连接数打满Threads_connected、Aborted_connects语法解析很少成为瓶颈避免在热路径上执行复杂动态字符串拼接优化器统计信息不准、选错索引EXPLAIN的key、rows明显不合理存储引擎读数据缓冲池命中率低、回表太多Handler_read_rnd_next、Key_reads排序/临时表磁盘排序、磁盘临时表EXPLAIN的Using filesort、Using temporary锁等待MDL 锁、行锁冲突processlist里State: Waiting for table metadata lock结果发送客户端读取太慢、网络往返多State: Sending data但执行计划很快这套清单是我排查慢 SQL 的默认起点。大多数时候问题出在优化器选错索引和存储引擎层做了太多无用功。6. 我平时排查慢 SQL 最常用的两个工具和一套思路6.1 EXPLAIN ANALYZE真正执行之后再告诉你时间花在哪MySQL 8.0.18 引入了EXPLAIN ANALYZE相比传统EXPLAIN只展示预估EXPLAIN ANALYZE会真实执行语句并输出每个步骤的实际耗时和行数。比如EXPLAIN ANALYZE SELECT id, name, age, city_id FROM user WHERE age 30 AND city_id 100 ORDER BY id LIMIT 20;输出片段大概长这样版本不同格式略有差异- Limit: 20 rows (actual time0.11..0.12 rows20 loops1) - Sort: user.id (actual time0.11..0.11 rows20 loops1) - Index lookup on user using idx_city (city_id100), with index condition: (user.age 30) (cost2.3 rows20) (actual time0.05..0.09 rows20 loops1)注意它会真实执行 SQL。在一个巨大的生产表上跑EXPLAIN ANALYZE要先想清楚别本来只是想看看执行计划结果让线上库多跑了一次大查询。这是这个工具唯一的坑千万记住。6.2 optimizer_trace偷看优化器的心路历程EXPLAIN ANALYZE告诉你“做完了做了什么”optimizer_trace则告诉你“当初为什么这么选”。它的开启方式SET optimizer_trace enabledon; SELECT ...; -- 你的目标 SQL SELECT * FROM information_schema.OPTIMIZER_TRACE;输出是一个 JSON里面有优化器考虑过的所有执行计划、每个计划的成本估算、最终选择哪个计划的决定性因素。排查“为什么不走我新加的索引”这类问题时这个 JSON 比EXPLAIN有用得多。你会在里面看到优化器比较了走idx_age、idx_city、全表扫描三条路径最终因为某个分支的 cost 更小选择了它。看完之后你就明白不是 MySQL“脑子进水”而是你提供的索引在成本模型里真的不划算。6.3 一次真实的排查过程从慢查询日志到执行计划顺着这套工具链我带你走一遍我上个月刚遇到的一个案例。业务反馈某列表页接口变慢了其中一条 SQL 在慢查询日志里稳定出现SELECT order_id, user_id, status, amount FROM orders WHERE create_time 2025-01-01 AND status 1 ORDER BY create_time LIMIT 50;表的规模大约 2000 万行create_time和status上分别都有索引。第一步EXPLAIN看到key用的是idx_create_time但rows预估有 120 万Extra里有Using filesort非常奇怪走create_time索引居然还filesort原因是WHERE create_time ...已经是从 2025 年 1 月 1 日开始的范围范围匹配的索引顺序本来就和ORDER BY create_time一致理论上不该出现排序。再往下看发现问题出在status 1这个过滤条件。优化器选择走idx_create_time拿到的结果集是 120 万行每一行再去过滤status 1过滤后剩下 8000 行左右其实status这列的区分度很高走idx_status只需要扫描 8000 行但是优化器预估时高估了status的选择性低或样本偏旧最终选了更差的路径。处理方式分两步先看统计信息发现订单表数据量近期增长很快确实统计信息滞后了。执行ANALYZE TABLE orders;之后重新EXPLAIN优化器已经改走idx_status耗时从 3.8 秒降到 0.15 秒。第二步是为了防止后续再选错我调整了查询写法把create_time的范围条件保留但提醒研发同学检查统计信息更新机制。这个案例典型的地方在于它没有涉及任何复杂的 SQL 改写根子就是统计信息不准导致优化器用了一个错误的成本估算。加索引解决不了它改 SQL 也解决不了它唯一正确的动作是让优化器手里的“地图”变准。6.4 关于执行计划必须承认它是个“活的”最后说句掏心窝的话。我见过太多团队把执行计划当成一劳永逸的东西今天优化了一条 SQL明天上线之后速度很好就再也不管了。可执行计划是基于统计信息动态生成的数据分布一变、索引一变、版本升级一变计划就可能变化。所以真正靠谱的做法是把慢查询日志和 explain 采集起来做成回归监控定期看看“原先很快的 SQL 执行计划是不是变了”。我在实际项目中多次因为这个习惯提前几天发现了索引失效和统计信息滞后的隐患而不是等用户先来投诉。一条 SQL 到底怎么被执行拆到最后其实就是三层博弈结构上是 B 树和聚簇/二级索引的取舍决策上是成本模型和统计信息的博弈工程上是缓冲池、排序区、临时表和锁的协同。把这三层想明白了面对任何慢 SQL你至少知道该往哪个环节看而不是被动地“加个索引再试试”。如果你从头读到这里我建议你立刻拿一条线上真实的慢查询按我这里的方法走一遍完整链路你的收获会比看十篇文章都大。