写MySQL排查写久了被问得最多的一个问题就是“一条SQL语句在MySQL内部到底是怎么跑的”很多人面试前一字不差地背过“连接器→分析器→优化器→执行器”可真到了线上一条慢SQL摆在面前时却不知道怎么顺着这条路去找问题。原因是大家只记住了名词没理解每个环节具体在做什么也没把这条链路和索引、日志、事务机制串起来。这篇文章我就把这条完整链路掰开揉碎结合我自己实际排查的经验来写。不管你是刚入门想搞懂MySQL基础架构的开发者还是写了好几年SQL想系统梳理一遍的老手跟着这条线走一遍以后再处理慢SQL、看执行计划、理解优化器行为思路会清晰非常多。1. 别急着背八股一条SQL的完整旅途在动手拆环节之前先在心里搭一个总框架。一条SQL从客户端发出到最终拿到结果要经过的节点大概是这样的客户端连接、查询缓存8.0之前才有、解析器、预处理器、优化器、执行器、存储引擎。这里面的关键点是MySQL是典型的分层架构——server层负责连接管理、解析、优化、执行调度存储引擎层才真正负责数据的读写。你用的到底是InnoDB还是MyISAM对上层几个环节来说是透明的。这种分层带来的好处很明显存储引擎可以替换上层逻辑不用跟着改。但副作用也藏在这里——比如你在执行计划里看到的“Using filesort”其实是server层干的活并不是某个存储引擎特有的逻辑。搞清楚每一层各自管什么排查问题的时候才能准确定位。我用一条比较典型的查询SQL来当主线后面每个环节都拿它举例SELECT u.id, u.name, o.order_no FROM user u JOIN orders o ON u.id o.user_id WHERE u.age 20 ORDER BY o.create_time DESC LIMIT 10;这条SQL涉及连表、条件过滤、排序、分页基本把server层几个核心模块都覆盖了。1.1 从客户端到服务端的第一次握手SQL到达MySQL的第一步不是解析而是先建立连接。这一步由连接器负责。客户端通过TCP握手连接到MySQL服务端服务端校验用户名密码然后读取该用户的权限信息加载到当前会话的内存中。注意权限校验通过后这个连接后续所有操作的权限判断都是基于连接建立那一刻加载进来的权限快照而不是每次都重新读权限表。这意味着如果你在连接建立之后改了用户权限这个连接在断开重连之前并不会感知到变化。实际运维中改完权限后要么等现有连接超时断开要么让业务方重连这是很常见的坑。连接建立之后MySQL会为这个会话分配一个线程。8.0默认的线程池模型下每个连接对应一个线程线程执行完SQL后不会立刻销毁而是复用避免频繁创建线程带来的上下文切换开销。你可以在performance_schema.threads表里看到这些线程的状态。曾经有一次线上连接数飙升我查SHOW PROCESSLIST发现大量连接处于Sleep状态就是业务侧连接池的最小连接数设置过大加上wait_timeout时间太长导致一堆空闲连接占着不释放。后来把连接池参数调到合理范围并把MySQL的wait_timeout从默认8小时改到1小时问题才缓解。这里顺带说一个容易被忽略的点max_connections限制的是同时连接的数量如果业务突发流量导致连接数超过上限新连接会直接报Too many connections错误而这个错误不会因为负载降下来就自动恢复。处理方式一般是先临时调大上限同时排查是连接泄漏还是峰值流量再决定长期方案。1.2 8.0里消失的查询缓存曾经是个大坑建立连接之后如果是查询语句MySQL会先去查询缓存里看看有没有现成结果。查询缓存的逻辑很简单以SQL文本为key把查询结果缓存起来下次遇到一模一样的SQL直接返回结果跳过解析、优化、执行全过程。听起来很美实际用起来却很坑。这个缓存的失效粒度是表级别的——只要缓存涉及的表有任何一条数据发生变化该表相关的所有查询缓存全部失效。对于写入频繁的业务表缓存命中率极低反而还要付出维护缓存的额外开销。我之前接手过一个老项目打开过查询缓存结果写入稍一频繁缓存就反复失效性能不升反降期间还出现过因为缓存空间碎片化导致的性能抖动。MySQL官方也意识到这个问题从5.7开始就默认关闭了查询缓存到了8.0直接把这个功能整个移除了。现在如果看到老资料里提到query_cache_type参数直接跳过就行不用再折腾。实际上8.0之后InnoDB的缓冲池承担了“缓存”的职责它缓存的是数据页而不是查询结果这才是更合理的设计——数据变了缓冲池里的页跟着刷新天然一致不需要复杂无效。2. 从SQL变成数据结构解析器与预处理器到底在干什么查询缓存没命中或者8.0压根没这个环节SQL才真正开始被“消化”。这一阶段的目标是把纯文本字符串变成MySQL内部能理解的数据结构。你可以把它理解为编译器的前端先分词再构建语法树。2.1 词法分析和语法分析SQL怎么变成一棵树词法分析做的事情是把SQL字符串拆成一个个“单词”。比如SELECT u.id FROM user u WHERE u.age 20会被拆成SELECT、u、.、id、FROM、user、u、WHERE、u、.、age、、20这些token。每个token都有类型是关键字、标识符、数字还是操作符。这一步如果SQL里写了根本不存在的关键字或者字符串引号没闭合词法分析阶段就会报错。语法分析是在token流的基础上按照MySQL定义的语法规则构建一棵解析树。这棵树的结构大致是顶层是一个查询块query block下面分出select列表、from子句、where条件、order by、limit这些节点。拿我们那条SQL来说解析树里会明确知道u.id是一个“表字段引用”u.age 20是一个“比较表达式”u.id o.user_id是一个“等值连接条件”。语法分析阶段如果SQL本身语法错误比如SELEC拼错了或者ORDER BY后面没跟排序键会直接在这里报You have an error in your SQL syntax错误。这个报错虽然看着吓人但实际上是最好解决的问题——基本都是SQL写错了仔细看near后面的内容就能定位到出错位置。2.2 预处理器与权限校验效率和安全的平衡解析树构建好了还不能直接交给优化器中间还夹着一个预处理器。预处理器主要做几件事检查表是否存在、检查列是否存在、把*展开成具体的列名列表、校验表名和列名的歧义。比如两张表都有id字段你不能只写SELECT id而不用表名限定预处理器会在这里报Column id in field list is ambiguous。权限校验也发生在这个阶段附近。MySQL会检查当前用户是否对这个表有对应的权限SELECT、INSERT、UPDATE、DELETE等。这里有个细节权限校验是在预处理阶段做的但它针对的是“语句级”权限不是“行级”权限。MySQL原生的权限机制最细只能控制到列控制不到行。如果业务上有“不同角色只能看不同数据行”的需求靠MySQL权限表做不了得在SQL层面加过滤条件或者用视图包一层。这个阶段我实际排查中踩过的一个坑是某条SQL在测试环境跑得好好的上了生产报Table xxx doesnt exist但表明明在。最后发现是生产库的lower_case_table_names参数设置和测试环境不一致导致表名大小写匹配不上。在Linux上MySQL默认区分表名大小写而Windows上默认不区分。这个参数必须在初始化时确定中途改动会有各种诡异问题所以跨环境迁移时一定要检查。3. 优化器你的SQL最后怎么走由它决定解析树和预处理都通过之后SQL就进入了优化器阶段。这是整个执行链路里最核心、也最让人头疼的一环。优化器的任务是从无数种可能的执行方式里挑一个它认为成本最低的方案。这个“成本”不是玄学而是有一套估算模型主要考虑的是要读取多少行数据、要访问多少个数据页、是否使用索引、排序和临时表的开销有多大。3.1 优化器到底在优化什么成本估算模型为了估算成本MySQL需要知道表里大概有多少行数据、某个字段的区分度怎么样、索引的基数是多少。这些信息从哪里来从统计信息来。InnoDB的统计信息是通过采样数据页计算出来的不是实时的精确值。这也解释了为什么有时候表数据量变了执行计划却没变——因为统计信息没更新优化器还在按旧数据估算成本。你可以用ANALYZE TABLE强制更新统计信息很多“SQL突然变慢但表结构和索引都没变”的问题根源就是统计信息太旧导致优化器选了一条实际很差的执行计划。更新统计信息之后再跑一遍执行计划可能会完全不同。我遇到过一条查询白天正常、晚上突然慢几十倍的情况排查到最后发现是晚上有大批量导入任务数据量翻了几倍但统计信息还是导入前的优化器按老数据估出一个全表扫描更优的结论实际跑起来就是灾难。成本估算还会考虑是否走二级索引以及回表次数。比如索引idx_age(age)能过滤出一批满足age 20的记录但如果满足条件的记录占比很高优化器可能觉得直接全表扫描比走索引再回表更划算。这个“临界点”没有固定值大致在20%到30%的选择率附近具体情况要结合表的实际分布来看。3.2 几种真实可见的优化策略优化器不止是“选索引”这么简单它还会对SQL本身做等价改写让执行方式更高效。常见的优化手段包括条件化简把WHERE 11 AND age 20化简为WHERE age 20把a 5 AND a 10合并成a 10。常量传递WHERE u.age 20 AND o.user_id u.id优化器能推断出o.user_id的值也是20然后在连接时使用这个常量去匹配。子查询优化把IN (SELECT ...)改写成半连接semi-join避免子查询逐行执行。这就是为什么很多人问“IN和EXISTS到底哪个快”在现代版本里优化器会统一处理纠结谁更快很多时候已经没意义了。ORDER BY和GROUP BY优化如果排序字段正好是索引列优化器可以直接利用索引的有序性扫描避免额外排序如果条件允许GROUP BY也可以借助索引完成分组统计。LIMIT优化当LIMIT数量很小且没有其他复杂操作时优化器可能选择“优先队列排序”只需要维护一个小根堆而不用把全部数据排序内存和CPU开销都会小很多。这些优化策略在5.7和8.0版本中越来越智能但它们不是万能的。很多优化器“不具备”的能力就需要靠我们手工改写SQL来配合了。3.3 看懂EXPLAIN输出跟执行计划打交道优化器做完决策之后产生的执行计划可以通过EXPLAIN看到。很多开发者对EXPLAIN的理解停留在“会看type是不是ALL、key是不是NULL”这个层面这样有点浪费因为执行计划里信息量非常大。拿我们那条SQL为例执行EXPLAIN SELECT ...会返回一行或多行记录多表连接会有多行。核心字段和我的判断习惯如下字段关注重点我的判断习惯type访问类型从好到差大致是systemconsteq_refrefrangeindexALL。实际开发中出现ALL全表扫描或者index全索引扫描就要警惕了除非表很小。key实际选中的索引为NULL说明没用到索引要结合type看是否全表扫描。rows预估扫描行数这是一个估算值但数量级很有参考价值。多表连接时rows的乘积直接影响查询总成本。filtered过滤后在连接中进一步过滤的比例数值越低说明存储引擎层返回的行里大部分在server层被过滤掉了这时候就该考虑是否能把条件推下去。Extra附加信息出现Using filesort、Using temporary就要重点优化出现Using index是好事覆盖索引出现Using index condition说明用上了索引条件下推ICP。我见过不少开发者在优化SQL时不先看执行计划就盲目加索引加完之后还是慢再一看索引根本没被用上。正确顺序应该是先EXPLAIN看执行计划判断瓶颈是全表扫描、排序还是临时表再针对瓶颈去调整索引或改写SQL。8.0.18版本之后还提供了EXPLAIN ANALYZE可以直接把SQL实际执行一遍输出每一步的真实耗时和行数比单纯看估算值更直接。排查慢SQL时这工具比单看EXPLAIN实用很多。4. 执行器与存储引擎数据真正被读写的最后一公里执行计划确定了接下来就进入执行阶段。这一步由执行器Executor负责它按照执行计划的步骤向存储引擎发起读取请求再把存储引擎返回的数据做进一步处理。理解这里的关键点在于server层和存储引擎层之间通过统一的“行格式”接口交互执行器不关心数据在磁盘上怎么存它只按行来操作。4.1 执行器如何调用存储引擎接口继续拿我们那条SQL举例。假设优化器最终选择先读user表过滤出age 20的记录再回表拿id和name然后去orders表用user_id做连接查询。那么执行器的操作大概是这样调用存储引擎接口读取user表的第一行判断age 20是否成立不成立就跳过成立就保留。对保留的每一行根据执行计划决定是否需要回主键索引取其他列。用这一行的id值去orders表匹配user_id 该id的记录。匹配到之后取出order_no把结果交给server层做排序和LIMIT截断。这里有个细节值得展开二级索引回表。如果user表上age字段有二级索引执行器可以通过idx_age快速定位满足age 20的主键值集合然后再逐行去主键索引里取name列。但如果idx_age的age筛选后结果集很大回表次数很多性能反而不如直接全表扫描。这也是为什么优化器要根据统计信息做成本权衡。8.0对这类场景有一个重要优化索引条件下推Index Condition PushdownICP。在ICP之前存储引擎只能根据索引本身的条件比如age 20过滤数据其他条件要等回表后由server层判断。有了ICP之后部分WHERE条件可以直接下推到存储引擎在读取索引记录时就完成过滤减少回表次数。你可以通过Extra里出现Using index condition来判断是否命中了这个优化。实际优化含多个条件查询时把区分度高的列放在联合索引前面配合ICP效果非常明显。还有一个优化叫MRRMulti-Range Read。当二级索引匹配到多个主键值需要回表时MySQL可以把这些主键值先排序再批量回表读取。这样做能把随机I/O变成相对顺序的I/O对机械硬盘时代尤其重要。SSD时代随机I/O快了很多MRR的收益没以前那么夸张但在数据量特别大的情况下依然有效。4.2 更新语句的另一面redo log、binlog与两阶段提交前面讲的都是查询语句但面试和实际工作中更新语句的执行流程同样是重点而且它比查询多了一条关键链路日志。一条UPDATE语句的执行流程大致是先从存储引擎读取目标行在内存中修改然后写入undo log用于回滚、redo log崩溃恢复、binlog主从复制和备份恢复。这里最经典的考点是两阶段提交。简单说InnoDB在事务提交时不会直接一路写到底而是先把redo log写入并标记为prepare状态然后写入binlog最后再把redo log标记为commit状态。这个设计是为了保证redo log和binlog两份日志的一致性。如果崩溃恰好发生在两个日志写了一半的时候MySQL可以通过对比日志状态决定是提交还是回滚从而避免主从数据不一致。我实际遇到过一个相关问题某业务在做大批量更新时经常出现主从延迟甚至主从数据不一致。排查发现是binlog_format设置成了STATEMENT一些非确定性的SQL比如带NOW()、UUID()的更新语句在从库重放时结果和主库不一样。后来改成了ROW格式虽然binlog文件膨胀了一些但主从数据一致性得到了根本保障。这个取舍在做日志和复制相关优化时一定要清楚。5. 慢SQL排查实战定位一条SQL到底卡在哪一步前面的原理如果都理解了排查慢SQL就变成了一件顺理成章的事。因为一条慢SQL无非就是在执行链路的某一环出了问题要么连接和权限阶段卡住要么解析阶段本身就很慢这种情况极少要么优化器选了烂执行计划要么执行器阶段I/O开销巨大。下面我按照实际排查顺序整理一套可复用的方法这套方法我自己用下来定位问题的效率很高。5.1 定位阶段慢查询日志与进程列表慢SQL的第一现场是慢查询日志。线上环境建议至少打开慢查询日志设置一个合理的阈值。配置方式如下slow_query_log ON long_query_time 1 slow_query_log_file /var/log/mysql/slow.log log_queries_not_using_indexes ON这里我把long_query_time设成1秒意思是执行时间超过1秒的SQL都会被记录。log_queries_not_using_indexes会额外记录那些没用索引的SQL对发现潜在问题很有帮助。注意这个参数在8.0里依然是有效的虽然它可能会记录一些“虽然没走索引但其实不慢”的查询产生的日志量会比较大但初期排查宁可多记录不要漏掉。拿到慢日志里的SQL之后不要急着改先EXPLAIN看执行计划。如果执行计划显示全表扫描且表数据量很大那基本可以判断瓶颈在“扫描行数太多”。如果执行计划显示走了索引但还是很慢那就要看是不是回表次数太多、是否触发filesort、是否产生临时表。除了慢日志SHOW PROCESSLIST也能在问题发生时快速捕捉到当前正在执行的SQL状态。比如State列显示Sending data时说明正在读取和发送数据通常和存储引擎I/O有关显示Waiting for table metadata lock时说明有另一个连接长时间持有表锁堵住了后续的DML操作。后者很常见我记得有一次simply跑一个ALTER TABLE因为忘记指定ALGORITHMINPLACE导致全程持有元数据锁业务写入全被堵住了。这个坑排查了很久最后用SHOW PROCESSLIST看到一堆Waiting for table metadata lock才定位到根因。5.2 执行阶段从执行计划反推问题下面直接看几个真实场景下的执行计划问题和对应的处理思路。第一个场景是隐式类型转换导致索引失效。表里有个user_id字段是VARCHAR类型但SQL里传的是数字比如WHERE user_id 123。MySQL会把字符串和数字比较时转换为数字类型导致该字段上的索引无法正常使用实际执行计划变成全表扫描。这种问题的排查方式就是仔细检查WHERE条件的字段类型和传入参数类型是否一致。另外一个高频场景是函数包裹索引列WHERE DATE(create_time) 2024-01-01这样即使create_time上有索引也走不了。改成范围比较WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00就能利用上索引了。这也是优化器的一个限制它对索引列做函数计算后的状态是无能为力的因为索引里存储的是原始值不是函数计算结果。第二个场景是排序导致的filesort。比如我们主线这条SQL里ORDER BY o.create_time DESC如果create_time上没有索引执行计划Extra里会出现Using filesort。这里的filesort并不是真的“文件排序”那么可怕——数据量小的时候其实是在内存里排序的但一旦超过sort_buffer_size的限制就会使用磁盘临时文件性能急剧下降。想让排序走索引的办法很简单排序字段要么本身有索引要么建一个包含排序字段的联合索引这样优化器可以直接按索引顺序扫描避免额外的排序动作。但要注意联合索引的字段顺序和排序方向需要匹配如果ORDER BY a ASC, b DESC这种混合方向索引就很难帮上忙了。第三个场景是深分页优化。很多后台列表页会写LIMIT 100000, 20随着页数越来越深MySQL需要扫描前100000行然后丢弃代价极大。改用延迟关联是常见的优化手段SELECT u.id, u.name, o.order_no FROM ( SELECT id FROM user WHERE age 20 ORDER BY id LIMIT 100000, 20 ) t JOIN user u ON u.id t.id LEFT JOIN orders o ON u.id o.user_id;思路很直接先在索引覆盖的小结果集里完成分页再回表取完整数据。联合索引如果覆盖了age, id这个子查询就不会触发回表分页的代价大幅降低。5.3 优化手段改写SQL、调整索引、调参数把上面这些场景梳理一下慢SQL优化的手段其实就三类改写SQL、调整索引、调整服务端参数。改写SQL的优先级最高因为它能直接改变优化器可选择的执行路径。比如拆开一条逻辑复杂的SQL避免用OR连接多个不相干条件OR常常导致索引失效可以换成UNION ALL把大事务拆成小批次等等。调整索引是第二优先的但不是“越多越好”每个索引都会带来写入和存储的开销我见过有些表索引建了十来个插入性能差到离谱。字段选择上优先考虑查询频率高、区分度好的列同时要覆盖排序、分组、连接字段的使用场景。第三类是参数调整比如调大sort_buffer_size、join_buffer_size、tmp_table_size这些会话级内存参数缓解排序和临时表的压力。这里要特别提醒一个原则参数调整是“堵漏”SQL改写和索引调整才是“治本”。不要一上来就调各种buffer很多问题的根源是执行计划本身就不好调参数只是给烂计划续命。而且像sort_buffer_size这类参数是每个连接都会分配的内存调得过大连接数一多内存就会申请过多反而引发OOM风险。6. 几个面试之外才用得上的经验如果你把整条链路理解透了其实你已经能回答一大半MySQL面试题了。但我想在最后说说几个面试题之外、实践经验里才真正体现价值的地方。第一执行流程里的“优化器”并不是万能的。它基于统计信息和成本模型做决策但统计信息可能陈旧成本模型也不可能覆盖所有真实场景。所以线上排查慢SQL时不要迷信“优化器会自动选最优计划”。定期ANALYZE TABLE维护统计信息必要时用FORCE INDEX先让SQL恢复正常再慢慢研究更合理的索引方案是更务实的处理方式。第二流程中的每一步都可能变成瓶颈。连接层会撑爆连接数预处理层可能卡在元数据锁上优化器可能选出烂执行计划执行器阶段可能因为I/O瓶颈让一条简单查询慢如蜗牛。排查问题时要按照这条链路去“分段排除”。我看到很多新手一遇到慢SQL就第一时间去优化索引而不先看看到底是哪一环出的问题往往事倍功半。第三如果有条件尽量在8.0版本上做新项目。查询缓存的移除是好事优化器能力更强新增的EXPLAIN ANALYZE、Hash Join、窗口函数这些能力对日常开发和排查都是实打实的帮助。如果是老项目在5.7上跑也要对版本差异心里有数别把8.0的行为套到5.7上更别把5.7的行为套到8.0上。我个人的体会是把某一条SQL从发出到返回的全过程走一遍胜过死记硬背十篇架构博客。下次再遇到慢SQL别急着把SQL扔进搜索引擎先EXPLAIN一下顺着执行计划看它到底干了什么你会发现那些优化手段不再是“因为别人这么说”而是你亲眼看到了问题出在了哪一步。
MySQL一条SQL的执行流程：从连接到存储引擎的完整链路
写MySQL排查写久了被问得最多的一个问题就是“一条SQL语句在MySQL内部到底是怎么跑的”很多人面试前一字不差地背过“连接器→分析器→优化器→执行器”可真到了线上一条慢SQL摆在面前时却不知道怎么顺着这条路去找问题。原因是大家只记住了名词没理解每个环节具体在做什么也没把这条链路和索引、日志、事务机制串起来。这篇文章我就把这条完整链路掰开揉碎结合我自己实际排查的经验来写。不管你是刚入门想搞懂MySQL基础架构的开发者还是写了好几年SQL想系统梳理一遍的老手跟着这条线走一遍以后再处理慢SQL、看执行计划、理解优化器行为思路会清晰非常多。1. 别急着背八股一条SQL的完整旅途在动手拆环节之前先在心里搭一个总框架。一条SQL从客户端发出到最终拿到结果要经过的节点大概是这样的客户端连接、查询缓存8.0之前才有、解析器、预处理器、优化器、执行器、存储引擎。这里面的关键点是MySQL是典型的分层架构——server层负责连接管理、解析、优化、执行调度存储引擎层才真正负责数据的读写。你用的到底是InnoDB还是MyISAM对上层几个环节来说是透明的。这种分层带来的好处很明显存储引擎可以替换上层逻辑不用跟着改。但副作用也藏在这里——比如你在执行计划里看到的“Using filesort”其实是server层干的活并不是某个存储引擎特有的逻辑。搞清楚每一层各自管什么排查问题的时候才能准确定位。我用一条比较典型的查询SQL来当主线后面每个环节都拿它举例SELECT u.id, u.name, o.order_no FROM user u JOIN orders o ON u.id o.user_id WHERE u.age 20 ORDER BY o.create_time DESC LIMIT 10;这条SQL涉及连表、条件过滤、排序、分页基本把server层几个核心模块都覆盖了。1.1 从客户端到服务端的第一次握手SQL到达MySQL的第一步不是解析而是先建立连接。这一步由连接器负责。客户端通过TCP握手连接到MySQL服务端服务端校验用户名密码然后读取该用户的权限信息加载到当前会话的内存中。注意权限校验通过后这个连接后续所有操作的权限判断都是基于连接建立那一刻加载进来的权限快照而不是每次都重新读权限表。这意味着如果你在连接建立之后改了用户权限这个连接在断开重连之前并不会感知到变化。实际运维中改完权限后要么等现有连接超时断开要么让业务方重连这是很常见的坑。连接建立之后MySQL会为这个会话分配一个线程。8.0默认的线程池模型下每个连接对应一个线程线程执行完SQL后不会立刻销毁而是复用避免频繁创建线程带来的上下文切换开销。你可以在performance_schema.threads表里看到这些线程的状态。曾经有一次线上连接数飙升我查SHOW PROCESSLIST发现大量连接处于Sleep状态就是业务侧连接池的最小连接数设置过大加上wait_timeout时间太长导致一堆空闲连接占着不释放。后来把连接池参数调到合理范围并把MySQL的wait_timeout从默认8小时改到1小时问题才缓解。这里顺带说一个容易被忽略的点max_connections限制的是同时连接的数量如果业务突发流量导致连接数超过上限新连接会直接报Too many connections错误而这个错误不会因为负载降下来就自动恢复。处理方式一般是先临时调大上限同时排查是连接泄漏还是峰值流量再决定长期方案。1.2 8.0里消失的查询缓存曾经是个大坑建立连接之后如果是查询语句MySQL会先去查询缓存里看看有没有现成结果。查询缓存的逻辑很简单以SQL文本为key把查询结果缓存起来下次遇到一模一样的SQL直接返回结果跳过解析、优化、执行全过程。听起来很美实际用起来却很坑。这个缓存的失效粒度是表级别的——只要缓存涉及的表有任何一条数据发生变化该表相关的所有查询缓存全部失效。对于写入频繁的业务表缓存命中率极低反而还要付出维护缓存的额外开销。我之前接手过一个老项目打开过查询缓存结果写入稍一频繁缓存就反复失效性能不升反降期间还出现过因为缓存空间碎片化导致的性能抖动。MySQL官方也意识到这个问题从5.7开始就默认关闭了查询缓存到了8.0直接把这个功能整个移除了。现在如果看到老资料里提到query_cache_type参数直接跳过就行不用再折腾。实际上8.0之后InnoDB的缓冲池承担了“缓存”的职责它缓存的是数据页而不是查询结果这才是更合理的设计——数据变了缓冲池里的页跟着刷新天然一致不需要复杂无效。2. 从SQL变成数据结构解析器与预处理器到底在干什么查询缓存没命中或者8.0压根没这个环节SQL才真正开始被“消化”。这一阶段的目标是把纯文本字符串变成MySQL内部能理解的数据结构。你可以把它理解为编译器的前端先分词再构建语法树。2.1 词法分析和语法分析SQL怎么变成一棵树词法分析做的事情是把SQL字符串拆成一个个“单词”。比如SELECT u.id FROM user u WHERE u.age 20会被拆成SELECT、u、.、id、FROM、user、u、WHERE、u、.、age、、20这些token。每个token都有类型是关键字、标识符、数字还是操作符。这一步如果SQL里写了根本不存在的关键字或者字符串引号没闭合词法分析阶段就会报错。语法分析是在token流的基础上按照MySQL定义的语法规则构建一棵解析树。这棵树的结构大致是顶层是一个查询块query block下面分出select列表、from子句、where条件、order by、limit这些节点。拿我们那条SQL来说解析树里会明确知道u.id是一个“表字段引用”u.age 20是一个“比较表达式”u.id o.user_id是一个“等值连接条件”。语法分析阶段如果SQL本身语法错误比如SELEC拼错了或者ORDER BY后面没跟排序键会直接在这里报You have an error in your SQL syntax错误。这个报错虽然看着吓人但实际上是最好解决的问题——基本都是SQL写错了仔细看near后面的内容就能定位到出错位置。2.2 预处理器与权限校验效率和安全的平衡解析树构建好了还不能直接交给优化器中间还夹着一个预处理器。预处理器主要做几件事检查表是否存在、检查列是否存在、把*展开成具体的列名列表、校验表名和列名的歧义。比如两张表都有id字段你不能只写SELECT id而不用表名限定预处理器会在这里报Column id in field list is ambiguous。权限校验也发生在这个阶段附近。MySQL会检查当前用户是否对这个表有对应的权限SELECT、INSERT、UPDATE、DELETE等。这里有个细节权限校验是在预处理阶段做的但它针对的是“语句级”权限不是“行级”权限。MySQL原生的权限机制最细只能控制到列控制不到行。如果业务上有“不同角色只能看不同数据行”的需求靠MySQL权限表做不了得在SQL层面加过滤条件或者用视图包一层。这个阶段我实际排查中踩过的一个坑是某条SQL在测试环境跑得好好的上了生产报Table xxx doesnt exist但表明明在。最后发现是生产库的lower_case_table_names参数设置和测试环境不一致导致表名大小写匹配不上。在Linux上MySQL默认区分表名大小写而Windows上默认不区分。这个参数必须在初始化时确定中途改动会有各种诡异问题所以跨环境迁移时一定要检查。3. 优化器你的SQL最后怎么走由它决定解析树和预处理都通过之后SQL就进入了优化器阶段。这是整个执行链路里最核心、也最让人头疼的一环。优化器的任务是从无数种可能的执行方式里挑一个它认为成本最低的方案。这个“成本”不是玄学而是有一套估算模型主要考虑的是要读取多少行数据、要访问多少个数据页、是否使用索引、排序和临时表的开销有多大。3.1 优化器到底在优化什么成本估算模型为了估算成本MySQL需要知道表里大概有多少行数据、某个字段的区分度怎么样、索引的基数是多少。这些信息从哪里来从统计信息来。InnoDB的统计信息是通过采样数据页计算出来的不是实时的精确值。这也解释了为什么有时候表数据量变了执行计划却没变——因为统计信息没更新优化器还在按旧数据估算成本。你可以用ANALYZE TABLE强制更新统计信息很多“SQL突然变慢但表结构和索引都没变”的问题根源就是统计信息太旧导致优化器选了一条实际很差的执行计划。更新统计信息之后再跑一遍执行计划可能会完全不同。我遇到过一条查询白天正常、晚上突然慢几十倍的情况排查到最后发现是晚上有大批量导入任务数据量翻了几倍但统计信息还是导入前的优化器按老数据估出一个全表扫描更优的结论实际跑起来就是灾难。成本估算还会考虑是否走二级索引以及回表次数。比如索引idx_age(age)能过滤出一批满足age 20的记录但如果满足条件的记录占比很高优化器可能觉得直接全表扫描比走索引再回表更划算。这个“临界点”没有固定值大致在20%到30%的选择率附近具体情况要结合表的实际分布来看。3.2 几种真实可见的优化策略优化器不止是“选索引”这么简单它还会对SQL本身做等价改写让执行方式更高效。常见的优化手段包括条件化简把WHERE 11 AND age 20化简为WHERE age 20把a 5 AND a 10合并成a 10。常量传递WHERE u.age 20 AND o.user_id u.id优化器能推断出o.user_id的值也是20然后在连接时使用这个常量去匹配。子查询优化把IN (SELECT ...)改写成半连接semi-join避免子查询逐行执行。这就是为什么很多人问“IN和EXISTS到底哪个快”在现代版本里优化器会统一处理纠结谁更快很多时候已经没意义了。ORDER BY和GROUP BY优化如果排序字段正好是索引列优化器可以直接利用索引的有序性扫描避免额外排序如果条件允许GROUP BY也可以借助索引完成分组统计。LIMIT优化当LIMIT数量很小且没有其他复杂操作时优化器可能选择“优先队列排序”只需要维护一个小根堆而不用把全部数据排序内存和CPU开销都会小很多。这些优化策略在5.7和8.0版本中越来越智能但它们不是万能的。很多优化器“不具备”的能力就需要靠我们手工改写SQL来配合了。3.3 看懂EXPLAIN输出跟执行计划打交道优化器做完决策之后产生的执行计划可以通过EXPLAIN看到。很多开发者对EXPLAIN的理解停留在“会看type是不是ALL、key是不是NULL”这个层面这样有点浪费因为执行计划里信息量非常大。拿我们那条SQL为例执行EXPLAIN SELECT ...会返回一行或多行记录多表连接会有多行。核心字段和我的判断习惯如下字段关注重点我的判断习惯type访问类型从好到差大致是systemconsteq_refrefrangeindexALL。实际开发中出现ALL全表扫描或者index全索引扫描就要警惕了除非表很小。key实际选中的索引为NULL说明没用到索引要结合type看是否全表扫描。rows预估扫描行数这是一个估算值但数量级很有参考价值。多表连接时rows的乘积直接影响查询总成本。filtered过滤后在连接中进一步过滤的比例数值越低说明存储引擎层返回的行里大部分在server层被过滤掉了这时候就该考虑是否能把条件推下去。Extra附加信息出现Using filesort、Using temporary就要重点优化出现Using index是好事覆盖索引出现Using index condition说明用上了索引条件下推ICP。我见过不少开发者在优化SQL时不先看执行计划就盲目加索引加完之后还是慢再一看索引根本没被用上。正确顺序应该是先EXPLAIN看执行计划判断瓶颈是全表扫描、排序还是临时表再针对瓶颈去调整索引或改写SQL。8.0.18版本之后还提供了EXPLAIN ANALYZE可以直接把SQL实际执行一遍输出每一步的真实耗时和行数比单纯看估算值更直接。排查慢SQL时这工具比单看EXPLAIN实用很多。4. 执行器与存储引擎数据真正被读写的最后一公里执行计划确定了接下来就进入执行阶段。这一步由执行器Executor负责它按照执行计划的步骤向存储引擎发起读取请求再把存储引擎返回的数据做进一步处理。理解这里的关键点在于server层和存储引擎层之间通过统一的“行格式”接口交互执行器不关心数据在磁盘上怎么存它只按行来操作。4.1 执行器如何调用存储引擎接口继续拿我们那条SQL举例。假设优化器最终选择先读user表过滤出age 20的记录再回表拿id和name然后去orders表用user_id做连接查询。那么执行器的操作大概是这样调用存储引擎接口读取user表的第一行判断age 20是否成立不成立就跳过成立就保留。对保留的每一行根据执行计划决定是否需要回主键索引取其他列。用这一行的id值去orders表匹配user_id 该id的记录。匹配到之后取出order_no把结果交给server层做排序和LIMIT截断。这里有个细节值得展开二级索引回表。如果user表上age字段有二级索引执行器可以通过idx_age快速定位满足age 20的主键值集合然后再逐行去主键索引里取name列。但如果idx_age的age筛选后结果集很大回表次数很多性能反而不如直接全表扫描。这也是为什么优化器要根据统计信息做成本权衡。8.0对这类场景有一个重要优化索引条件下推Index Condition PushdownICP。在ICP之前存储引擎只能根据索引本身的条件比如age 20过滤数据其他条件要等回表后由server层判断。有了ICP之后部分WHERE条件可以直接下推到存储引擎在读取索引记录时就完成过滤减少回表次数。你可以通过Extra里出现Using index condition来判断是否命中了这个优化。实际优化含多个条件查询时把区分度高的列放在联合索引前面配合ICP效果非常明显。还有一个优化叫MRRMulti-Range Read。当二级索引匹配到多个主键值需要回表时MySQL可以把这些主键值先排序再批量回表读取。这样做能把随机I/O变成相对顺序的I/O对机械硬盘时代尤其重要。SSD时代随机I/O快了很多MRR的收益没以前那么夸张但在数据量特别大的情况下依然有效。4.2 更新语句的另一面redo log、binlog与两阶段提交前面讲的都是查询语句但面试和实际工作中更新语句的执行流程同样是重点而且它比查询多了一条关键链路日志。一条UPDATE语句的执行流程大致是先从存储引擎读取目标行在内存中修改然后写入undo log用于回滚、redo log崩溃恢复、binlog主从复制和备份恢复。这里最经典的考点是两阶段提交。简单说InnoDB在事务提交时不会直接一路写到底而是先把redo log写入并标记为prepare状态然后写入binlog最后再把redo log标记为commit状态。这个设计是为了保证redo log和binlog两份日志的一致性。如果崩溃恰好发生在两个日志写了一半的时候MySQL可以通过对比日志状态决定是提交还是回滚从而避免主从数据不一致。我实际遇到过一个相关问题某业务在做大批量更新时经常出现主从延迟甚至主从数据不一致。排查发现是binlog_format设置成了STATEMENT一些非确定性的SQL比如带NOW()、UUID()的更新语句在从库重放时结果和主库不一样。后来改成了ROW格式虽然binlog文件膨胀了一些但主从数据一致性得到了根本保障。这个取舍在做日志和复制相关优化时一定要清楚。5. 慢SQL排查实战定位一条SQL到底卡在哪一步前面的原理如果都理解了排查慢SQL就变成了一件顺理成章的事。因为一条慢SQL无非就是在执行链路的某一环出了问题要么连接和权限阶段卡住要么解析阶段本身就很慢这种情况极少要么优化器选了烂执行计划要么执行器阶段I/O开销巨大。下面我按照实际排查顺序整理一套可复用的方法这套方法我自己用下来定位问题的效率很高。5.1 定位阶段慢查询日志与进程列表慢SQL的第一现场是慢查询日志。线上环境建议至少打开慢查询日志设置一个合理的阈值。配置方式如下slow_query_log ON long_query_time 1 slow_query_log_file /var/log/mysql/slow.log log_queries_not_using_indexes ON这里我把long_query_time设成1秒意思是执行时间超过1秒的SQL都会被记录。log_queries_not_using_indexes会额外记录那些没用索引的SQL对发现潜在问题很有帮助。注意这个参数在8.0里依然是有效的虽然它可能会记录一些“虽然没走索引但其实不慢”的查询产生的日志量会比较大但初期排查宁可多记录不要漏掉。拿到慢日志里的SQL之后不要急着改先EXPLAIN看执行计划。如果执行计划显示全表扫描且表数据量很大那基本可以判断瓶颈在“扫描行数太多”。如果执行计划显示走了索引但还是很慢那就要看是不是回表次数太多、是否触发filesort、是否产生临时表。除了慢日志SHOW PROCESSLIST也能在问题发生时快速捕捉到当前正在执行的SQL状态。比如State列显示Sending data时说明正在读取和发送数据通常和存储引擎I/O有关显示Waiting for table metadata lock时说明有另一个连接长时间持有表锁堵住了后续的DML操作。后者很常见我记得有一次simply跑一个ALTER TABLE因为忘记指定ALGORITHMINPLACE导致全程持有元数据锁业务写入全被堵住了。这个坑排查了很久最后用SHOW PROCESSLIST看到一堆Waiting for table metadata lock才定位到根因。5.2 执行阶段从执行计划反推问题下面直接看几个真实场景下的执行计划问题和对应的处理思路。第一个场景是隐式类型转换导致索引失效。表里有个user_id字段是VARCHAR类型但SQL里传的是数字比如WHERE user_id 123。MySQL会把字符串和数字比较时转换为数字类型导致该字段上的索引无法正常使用实际执行计划变成全表扫描。这种问题的排查方式就是仔细检查WHERE条件的字段类型和传入参数类型是否一致。另外一个高频场景是函数包裹索引列WHERE DATE(create_time) 2024-01-01这样即使create_time上有索引也走不了。改成范围比较WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00就能利用上索引了。这也是优化器的一个限制它对索引列做函数计算后的状态是无能为力的因为索引里存储的是原始值不是函数计算结果。第二个场景是排序导致的filesort。比如我们主线这条SQL里ORDER BY o.create_time DESC如果create_time上没有索引执行计划Extra里会出现Using filesort。这里的filesort并不是真的“文件排序”那么可怕——数据量小的时候其实是在内存里排序的但一旦超过sort_buffer_size的限制就会使用磁盘临时文件性能急剧下降。想让排序走索引的办法很简单排序字段要么本身有索引要么建一个包含排序字段的联合索引这样优化器可以直接按索引顺序扫描避免额外的排序动作。但要注意联合索引的字段顺序和排序方向需要匹配如果ORDER BY a ASC, b DESC这种混合方向索引就很难帮上忙了。第三个场景是深分页优化。很多后台列表页会写LIMIT 100000, 20随着页数越来越深MySQL需要扫描前100000行然后丢弃代价极大。改用延迟关联是常见的优化手段SELECT u.id, u.name, o.order_no FROM ( SELECT id FROM user WHERE age 20 ORDER BY id LIMIT 100000, 20 ) t JOIN user u ON u.id t.id LEFT JOIN orders o ON u.id o.user_id;思路很直接先在索引覆盖的小结果集里完成分页再回表取完整数据。联合索引如果覆盖了age, id这个子查询就不会触发回表分页的代价大幅降低。5.3 优化手段改写SQL、调整索引、调参数把上面这些场景梳理一下慢SQL优化的手段其实就三类改写SQL、调整索引、调整服务端参数。改写SQL的优先级最高因为它能直接改变优化器可选择的执行路径。比如拆开一条逻辑复杂的SQL避免用OR连接多个不相干条件OR常常导致索引失效可以换成UNION ALL把大事务拆成小批次等等。调整索引是第二优先的但不是“越多越好”每个索引都会带来写入和存储的开销我见过有些表索引建了十来个插入性能差到离谱。字段选择上优先考虑查询频率高、区分度好的列同时要覆盖排序、分组、连接字段的使用场景。第三类是参数调整比如调大sort_buffer_size、join_buffer_size、tmp_table_size这些会话级内存参数缓解排序和临时表的压力。这里要特别提醒一个原则参数调整是“堵漏”SQL改写和索引调整才是“治本”。不要一上来就调各种buffer很多问题的根源是执行计划本身就不好调参数只是给烂计划续命。而且像sort_buffer_size这类参数是每个连接都会分配的内存调得过大连接数一多内存就会申请过多反而引发OOM风险。6. 几个面试之外才用得上的经验如果你把整条链路理解透了其实你已经能回答一大半MySQL面试题了。但我想在最后说说几个面试题之外、实践经验里才真正体现价值的地方。第一执行流程里的“优化器”并不是万能的。它基于统计信息和成本模型做决策但统计信息可能陈旧成本模型也不可能覆盖所有真实场景。所以线上排查慢SQL时不要迷信“优化器会自动选最优计划”。定期ANALYZE TABLE维护统计信息必要时用FORCE INDEX先让SQL恢复正常再慢慢研究更合理的索引方案是更务实的处理方式。第二流程中的每一步都可能变成瓶颈。连接层会撑爆连接数预处理层可能卡在元数据锁上优化器可能选出烂执行计划执行器阶段可能因为I/O瓶颈让一条简单查询慢如蜗牛。排查问题时要按照这条链路去“分段排除”。我看到很多新手一遇到慢SQL就第一时间去优化索引而不先看看到底是哪一环出的问题往往事倍功半。第三如果有条件尽量在8.0版本上做新项目。查询缓存的移除是好事优化器能力更强新增的EXPLAIN ANALYZE、Hash Join、窗口函数这些能力对日常开发和排查都是实打实的帮助。如果是老项目在5.7上跑也要对版本差异心里有数别把8.0的行为套到5.7上更别把5.7的行为套到8.0上。我个人的体会是把某一条SQL从发出到返回的全过程走一遍胜过死记硬背十篇架构博客。下次再遇到慢SQL别急着把SQL扔进搜索引擎先EXPLAIN一下顺着执行计划看它到底干了什么你会发现那些优化手段不再是“因为别人这么说”而是你亲眼看到了问题出在了哪一步。
