MySQL这玩意平时写SQL一时爽上线跑起来慢如狗的时候摔键盘的心都有。尤其干了几年的后端谁还没被几个“看似正常但慢得要命”的查询教做人过。我调过不少库也排过不少慢查询的坑最后发现很多人卡在一个点上SQL写得挺溜但对数据库到底怎么把这条SQL跑出来的一无所知。于是出了问题只会加索引、改SQL跟瞎猫碰死耗子似的治标不治本。想真正把调优做到点子上绕不开的一件事就是搞懂MySQL执行原理。你知道了连接器怎么接待你、解析器怎么拆你的SQL、优化器怎么给你选方案、执行器怎么去存储引擎拉数据你才能明白一条慢SQL到底慢在哪一步索引用没上、用没用好、排序是不是在临时文件里做的这些都藏在执行细节里。这篇文章我就照着“MySQL执行原理”这个根把调优的思路从头到尾捋一遍重点是帮你建立一套自己的排查逻辑而不是背一堆调优口诀。我会从MySQL整体的执行链路开始讲把每个环节对性能的影响都摆出来再重点拆解优化器和索引选择的规则因为这是90%慢查询的病根所在接着拿实际案例走一遍调优过程把排序、临时表、回表这些细节全部摊开最后整理我这些年踩过的坑和排查套路做成速查表。不管你是刚入门的开发还是被慢SQL折磨了好一阵子的后端这篇文章应该能给你一套能直接用上的方法论。1. MySQL执行链路你的SQL到底经历了什么1.1 从客户端到存储引擎一条SQL的完整旅程很多人以为执行一条SQL就是“输入一行命令出来一个结果”中间没什么事。其实一条SQL在MySQL内部的旅程相当漫长而且每一站都可能成为性能瓶颈。理解这条链路是后面做任何调优的地基。整条链路大致是这样的客户端发送SQL - 连接器验证身份、获取权限- 查询缓存8.0之前有之后废弃- 解析器词法、语法分析生成语法树- 预处理器校验表、列是否存在处理权限- 优化器决定执行计划- 执行器调用存储引擎接口逐行读取返回- 存储引擎InnoDB、MyISAM等真正干脏活累活的地方。最后服务端把结果集返回给客户端。这里有个很关键的认知MySQL的Server层负责上面大部分环节而存储引擎层只负责数据的读写。也就是说优化器选的执行计划最终要通过执行器翻译成对存储引擎的API调用。你在InnoDB上建了索引优化器没选它存储引擎再牛也没用。所以调优的抓手一是让优化器做出正确选择二是让存储引擎干活更高效。1.2 连接管理与线程模型对性能的隐性影响连接器这关往往被忽略但连接数一旦打满再好的SQL也进不来。MySQL每接收一个连接就要分配一个线程来处理线程池模型在商业版和企业版有增强开源版基本还是一连接一线程。连接数过多会导致上下文切换开销暴增甚至OOM。我实际调过一台数据库服务器CPU没满内存没满但接口就是频繁超时。后来一看Threads_connected接近上限2000多个连接挂着大量连接其实在sleep。很多是应用侧连接池配置得太奔放maxPoolSize设100服务一扩容连接数直接爆炸。这时候不是SQL的问题是连接治理问题。我当时的做法是在应用侧把连接池上限调低加了一层空闲连接回收同时把MySQL的wait_timeout调短让僵尸连接尽快被清理。所以说执行原理不只是看一条SQL还包括整个访问链路的资源管理。另一个隐形坑是权限验证。连接建立时MySQL会读取用户权限表如果用户权限特别多、表特别多验证本身也是开销。不过这个影响相对小不至于成为主要矛盾但极端情况下也会拖慢连接建立速度。生产环境尽量用最小权限原则对调优和安全管理都有好处。1.3 查询缓存为什么被废弃一个调优层面的历史教训MySQL 8.0直接移除了查询缓存这是很多人没注意到的变革。老版本里有一个query cache如果两张表完全相同的SQL直接从缓存返回结果号称能加速。看上去很美但实际用起来发现只要表有任何更新对应的缓存条目全部失效。在写入频繁的系统里维护缓存的开销比直接查询还大反而成了瓶颈。早期我维护过一套5.7的系统默认开启了查询缓存压测一跑写多读少的业务QPS反而比关闭缓存还低。后来统一改成query_cache_typeOFF性能立刻回升。这就是为什么8.0彻底砍掉它的原因——它只适合读多写极少且表基本不变的环境现实中这种场景太少了。这个案例告诉我们看执行链路要看每个环节的真实代价而不是看某个特性的“纸面优化”。2. 解析器、优化器与执行器性能差的源头在优化器2.1 词法语法解析与预处理慢SQL在这里暴露问题SQL到达解析器后MySQL会把字符串拆成一个个token这叫词法分析。接着按语法规则组装成语法树这叫语法分析。如果SQL写得不规范比如关键字拼错、列名不存在在这个阶段就会报错。预处理器则进一步检查表和列是否存在、权限是否满足。这里对于调优有意义的地方在于SQL写得越复杂、嵌套子查询越多、JOIN层数越深解析和预处理的耗时就越长。虽然这类耗时通常只有零点几毫秒但架不住高并发放大。一条SQL解析花0.5ms每秒一万次请求就是5秒的CPU时间全部白白消耗。所以我不建议在业务里堆那种动辄七八层嵌套的“祖传大SQL”。能拆成两条简单查询在应用层做关联往往比数据库硬扛一条巨型SQL更划算。数据库擅长的是集合操作但不是无底线的。2.2 优化器到底在“想”什么成本模型与执行计划生成解析完成后进入优化器这是整条链路中最核心也最黑盒的部分。MySQL的优化器主要用基于成本的优化CBO它会给每个可能的执行方案估算代价选择“它认为”成本最小的那个。成本主要考虑的因素包括全表扫描的代价、索引范围扫描的代价、回表的代价、排序的代价、临时表的代价等等。优化器拿到语法树后会做逻辑变换比如子查询优化、等价谓词重写、外连接转内连接等。然后做物理优化也就是决定表的读取顺序、连接算法、索引选择。最终生成一棵执行计划树交给执行器。你可以用EXPLAIN看到这棵树的最终形态但看不到优化器的内部推演过程。这也是很多开发困惑的地方为什么明明有索引优化器就是不用说到底是优化器基于统计信息估算出“走索引比全表扫更慢”。这听起来反直觉但真实存在。比如你要查的数据占全表的30%以上InnoDB的索引扫描需要回表逐行取数据而全表扫描是顺序IO直接扫聚簇索引前者可能要随机IO很多次反而更慢。这就是优化器“弃用索引”的合理原因。所以看到一个慢查询没走索引先别骂优化器想想自己的数据分布是不是本身就适合全表扫。2.3 统计信息不准是慢查询的头号“内鬼”优化器的成本计算依赖统计信息主要包括表的行数、索引的区分度、数据页数量等。如果统计信息不准确优化器就是瞎子摸象。最典型的场景是一张表数据量从1万涨到1000万但统计信息还停留在旧状态优化器以为走索引只要扫100行实际上要扫几十万行于是选了个“自认为很快”实际慢到爆炸的计划。InnoDB的统计信息是通过采样页估算的不是精确值。会随着表的增删改自动更新但更新频率受参数影响比如innodb_stats_auto_recalc和采样页数innodb_stats_persistent_sample_pages。我遇到过一张大表删除了大量数据后优化器还是按老行数估算导致JOIN时选了错误的驱动表整个查询跑了30秒。后来手动执行ANALYZE TABLE刷新统计信息同样的SQL变成0.2秒。所以调优的时候别一上来就改SQL先看看统计信息是不是过期了。另外索引基数Cardinality是统计信息里非常关键的值。它表示索引列有多少个不同的值。区分度越低比如性别列只有男女索引价值越低。MySQL优化器对区分度低的索引往往直接放弃。这就是为什么我老跟人说别见了WHERE条件就建索引在区分度极低的列上建索引纯属自欺欺人。2.4 执行器怎么工作从执行计划到数据返回优化器生成执行计划后执行器开始调用存储引擎接口。对InnoDB来说执行器会按照执行计划指示的表读取顺序一行一行或一匹一匹地取数据。这里的关键是rows_examined——实际扫描行数。用慢查询日志或者EXPLAIN里的rows字段能看出执行器到底扫了多少行。很多时候优化器估算的行数和实际扫描的行数差距巨大这种SQL一定要重点优化。执行器还有一个重要的动作是判断Server层的过滤条件。存储引擎返回的行先在Server层根据WHERE条件做进一步过滤条件下推除外。这意味着如果WHERE条件里含有无法下推到存储引擎的函数运算、类型转换就会造成每一行都要在Server层做额外判断白白增加CPU开销和扫描代价。这也是为什么对索引列用函数会导致索引失效的底层原因——索引列的值经过函数变换后存储引擎无法直接用B树查找只能全量拉出来算完再过滤。3. 索引与执行计划调优实战中的核心战场3.1 EXPLAIN各字段到底该怎么看关键就在这些“暗号”里用EXPLAIN分析执行计划是调优的基本功。但很多人只看type和rows其他字段全忽略。我建议把一张执行计划当成一份诊断报告来读。type字段代表访问类型从好到差依次是system const eq_ref ref range index ALL。system和const是极少见的极端情况eq_ref常见于JOIN时被驱动表通过主键或唯一索引查找。ref是普通索引等值匹配range是索引范围扫描index是索引全扫描虽然用了索引但遍历了整个索引树ALL就是全表扫描。重点要说下type为index的时候索引并没有真正“命中”只是优化器选择扫描一颗完整的索引树来避免排序或者覆盖部分列扫描量依然很大不能因为看到用了索引就觉得没问题。key字段展示实际选用的索引possible_keys展示可能用到的索引。如果possible_keys有值但key为NULL说明优化器认为索引没用具体原因结合rows和filtered看。filtered表示经过存储引擎层返回后Server层过滤后剩余的比例。这个字段在MySQL 5.7之后才比较准确能帮你判断索引的过滤效果。Extra字段更是信息密集区出现Using filesort或者Using temporary基本可以断定这里有性能隐患。3.2 为什么明明有索引却还是全表扫描这个疑问我在无数地方被问过必须好好展开讲。第一种情况是索引区分度太低比如状态列只有0、1两个值优化器觉得用索引扫30%的数据还不如直接全表扫于是key为NULL。第二种情况是隐式类型转换。比如索引列是varchar但查询参数传的是数字MySQL会先把列值转成数字再比较导致索引列上发生函数运算索引失效。第三种情况是违反最左前缀法则比如复合索引(a,b,c)你直接查bxxa没带索引没法定位。第四种情况是LIKE通配符在前面比如LIKE %xxx%索引B树只能按前缀匹配前缀是通配符就没办法走索引。还有一种是OR条件。如果OR连接的条件里有一个不走索引整个查询很可能退化。举例来说WHERE namex OR age20如果age列没索引优化器只能扫描全表。这时候可以把OR拆成两个查询用UNION合并或者给两侧都建上合适的索引。3.3 覆盖索引与回表让查询只在一颗索引树上完成InnoDB的主键索引聚簇索引的叶子节点存的是整行数据二级索引的叶子节点存的是主键值。当你通过二级索引查询时如果查询需要的列在二级索引里都有MySQL直接返回不需要再拿着主键去聚簇索引里捞完整行这叫覆盖索引。如果二级索引列不够覆盖查询列就需要回表也就是根据主键值再去聚簇索引查一遍多了随机IO。我调优化时特别看重覆盖索引因为它往往能以很小的成本带来巨大的性能提升。比如一个订单表最频繁的查询是SELECT order_no, amount, status FROM orders WHERE user_id ?你建立(user_id, amount, status)的联合索引这个查询就可以完全在索引树上完成。Extra字段会显示Using index这就是覆盖索引的标志。如果一个查询频繁出现但响应时间长先看看它的列能不能全部塞进一个联合索引里。回表次数多不多可以看extra里有没有Using index condition。这个是索引条件下推不是覆盖索引它表示存储引擎在索引遍历过程中用索引条件过滤了一部分行减少了回表次数。它也算一种优化但不如覆盖索引来得彻底。3.4 看不出问题的时候就要怀疑排序和临时表Extra里出现Using filesort时很多人以为产生了磁盘临时文件。严格来说filesort指的是MySQL需要自己执行排序操作而排序可能在内存中完成也可能在磁盘上完成。当排序的数据量不超过sort_buffer_size时在内存排序超过后使用磁盘临时文件多路归并排序。无论哪种排序都会增加耗时。你希望看到的是Extra里没有filesort也就是数据天然按索引顺序读取省去排序步骤。这就是为什么ORDER BY和GROUP BY这些操作最好也能利用索引顺序。如果Extra里出现Using temporary说明查询创建了临时表。临时表可能在内存MEMORY引擎也可能在磁盘On-disk临时表。GROUP BY、DISTINCT、UNION、子查询、ORDER BY与GROUP BY同时出现但字段不一致时都可能导致临时表。我遇到过一个真实案例一条分组统计SQL数据量只有几万行却跑了5秒多EXPLAIN一看Using temporary临时表直接落到磁盘了。后来通过调整索引顺序让GROUP BY走索引顺序去掉了临时表耗时降到几十毫秒。排序和临时表是执行计划里最容易被忽视的“隐形杀手”下次做EXPLAIN看到这两个词就要问自己能不能靠索引避免要不要改SQL结构能不能减少排序的字段宽度3.5 索引选择失当的实操复盘一次联合索引顺序调整查询从2秒到10毫秒讲一个印象很深的案例。有张用户行为日志表表结构大概长这样id, user_id, action_type, create_time, biz_id。线上有一个统计需求要查某个用户在某个时间段内做了某类操作的记录数。原SQL大概是SELECT COUNT(*) FROM user_log WHERE user_id ? AND action_type ? AND create_time BETWEEN ? AND ?。刚开始在user_id、create_time上分别建了单列索引。实际执行时优化器在user_id索引和create_time索引之间二选一选了其中一个然后回表过滤另一个条件。数据量一大回表数量惊人查询稳定在2秒左右。我当时第一个念头是改成联合索引但顺序上犯了难。想的是(user_id, action_type, create_time)因为三个等值/范围条件都有。但实际跑下来优化器有时候走索引有时候不走很不稳定。后来分析数据分布发现action_type的区分度极低只有5种操作user_id区分度好。于是建立(user_id, create_time, action_type)把区分度最高的列放前面等值的user_id放最左范围列create_time放中间最后才放低区分度的action_type用于过滤。调整之后执行计划稳定走这个联合索引回表几乎为零查询耗时降到10毫秒。这个案例给我的教训是联合索引顺序第一考虑等值查询列第二考虑排序或范围列最后才是过滤用但区分度低的列。同时要结合业务数据分布不要机械套用“最左前缀”。4. 慢查询排参与调优实战把执行原理用在定位问题上4.1 打开慢查询日志让执行原理成为你的探针懂执行原理不代表着你能一眼看出线上所有问题你得借助工具。慢查询日志是排查SQL性能最基础的手段。开启方式很简单在MySQL配置文件里设置slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time这里我习惯设置成1秒也就是超过1秒的查询都会被记录。生产环境如果秒级查询太多可以先设置成2秒逐步收紧。log_queries_not_using_indexes会记录所有没走索引的查询这个开关在前期很有用但开启后日志量会变大不适合长期全开。我一般是在调优期间打开等稳定后关掉。慢查询日志里每一行记录包含了执行时间、锁等待时间、扫描行数、返回行数、SQL语句等信息。重点对比扫描行数和返回行数如果扫描了100万行只返回10行说明过滤性很差大概率索引没用好。利用mysqldumpslow工具可以对日志做聚合统计快速找出Top N慢SQL。另外performance_schema和sys库也是定位问题的好工具比如sys.statement_analysis能按总延迟排序查看高频高耗时语句。4.2 ORDER BY、GROUP BY、DISTINCT、JOIN的调优套路针对排序和分组的调优有比较固定的套路。ORDER BY优化最核心的就是让排序字段与索引顺序匹配。比如查询SELECT * FROM t WHERE a ? ORDER BY b建立(a, b)联合索引既满足等值查询又让b有序排序直接省略。如果你是按b DESC也OK索引可以反序扫描。但如果排序方向不一致一个升序一个降序8.0之前只能filesort8.0之后支持降序索引可以建立(a ASC, b DESC)这种混合排序索引。所以建索引之前一定先问清楚业务的排序方向。GROUP BY优化本质和ORDER BY相似MySQL通常先分组再排序最好让分组字段也走索引顺序。如果集团语句还涉及COUNT、SUM等聚合要注意聚合字段能不能从索引里直接取避免回表。DISTINCT去重也一样利用索引有序性可以快速跳过重复值。如果DISTINCT的列上有索引基本就是顺序扫描索引并跳过相同值性能很好。没有索引的DISTINCT只能通过临时表去重代价不小。JOIN优化比较复杂核心思想是让小表驱动大表。优化器一般会自动选择驱动顺序但统计信息不准时会选错。你可以通过STRAIGHT_JOIN强制指定驱动顺序但这是核弹级手段只能在确认优化器脑抽时用。JOIN的算法有Nested Loop JoinNLJ、Block Nested Loop JoinBNL8.0.20后改为hash join前者对被驱动表的连接列要求有索引否则每行都要全表扫被驱动表后者是把驱动表数据加载到join buffer里批量匹配减少被驱动表扫描次数。经验之谈join连接列必须建索引这是底线。被驱动表连接列的索引可以让NLJ每次查找都走索引代价低很多。4.3 避免回表乱翻车一条“覆盖索引优化”的完整改造过程再看一个回表改造的实操。业务上有一张订单扩展表结构大概是id, order_id, extend_key, extend_value, create_time查询需求是SELECT order_id, extend_value FROM order_extend WHERE order_id IN (批量订单ID)。原order_id上有单列索引但select里要extend_value所以每次命中索引后都要回表拿extend_value。回表次数取决于IN列表有多少订单ID如果一次查1000个订单就要回表1000次快不了。我的改造方案很直白把extend_value和order_id做成联合索引(order_id, extend_value)。这样查询直接覆盖extra为Using index回表次数为0。有时候甚至不需要拆表一个覆盖索引就能解决一类查询的性能问题。但要注意索引不是越多越好每多一个二级索引写入和更新时都要维护索引占用空间也是成本。所以在覆盖索引能救命的同时也要克制只为高频且列少的关键查询建立覆盖索引。4.4 常见问题与排查技巧一张表解决你的多数困境我在实际支持中总结过不少高频问题这里整理成一张速查表大家可以直接对照处理。现象可能原因排查方法解决方向查询突然变慢统计信息过期EXPLAIN看rows增长ANALYZE TABLE刷新统计信息有索引却不走区分度低 / 类型转换 / 函数运算查看索引基数和字段类型重建索引列或调整SQL写法Using filesort出现排序字段无索引 / 顺序不一致查看执行计划sort_key调整索引或排序方向Using temporary出现GROUP BY / DISTINCT / UNION 导致查看临时表落盘警告改SQL或利用索引顺序JOIN后性能崩溃被驱动表连接列无索引查看EXPLAIN的type在连接列加索引连接数打满连接池配置过大查看Threads_connected调小连接池回收空闲连接批量查询慢回表过多看Extra是否有Using index改造覆盖索引除了这些还有一个很隐蔽的坑MySQL 8.0默认使用utf8mb4字符集校验规则索引列上的字符串比较可能受字符集排序规则影响尤其是大小写不敏感规则。如果两张表JOIN时字符集不同可能导致索引失效因为MySQL需要对其中一列做隐式转换。我遇到过一次中文字段JOIN特别慢查了半天发现一张表是utf8mb4_general_ci另一张表是utf8mb4_0900_ai_ci连字符集都一样就是排序规则不同索引直接废掉。统一表字符集和排序规则问题立解。5. 调优工具与参数基于执行原理的全局优化5.1 从EXPLAIN ANALYZE读懂每一步的真实消耗MySQL 8.0.18引入了EXPLAIN ANALYZE这是调优执行计划的神器。它不仅给出执行计划还能实际执行SQL并输出每一步的耗时、扫描行数和返回行数。以往EXPLAIN只能看估算现在能看到真金白银的实测数据。比如输出会包含类似- Filter: (t.a 100) (cost1.23 rows10) (actual time0.123..0.456 rows9 loops1)这样的信息。actual time里的第一个数字是取第一行的耗时第二个是取所有行的总耗时如果这两个差距很大说明第一行很快但后面卡可能是数据分布不均。用EXPLAIN ANALYZE最大的好处是能验证优化器估算和实际执行之间的差距。比如EXPLAIN说rows10000实际扫了400000行就说明统计信息严重失准或者优化器对某些谓词的过滤性判断错误。这时候再谈调优就有的放矢了。不过注意它会真实执行SQL线上环境慎用最好在测试库或只读从库上跑。5.2 关键参数调优不是越多越好而是对症下药MySQL的参数非常多但调优时别贪多抓住几个直接影响执行原理的关键参数就够了。innodb_buffer_pool_size这是InnoDB用来缓存数据和索引的内存区域。建议设置为物理内存的60%~75%。如果太小查询频繁发生磁盘IO执行原理再漂亮也扛不住。我见过很多配置差的机器buffer pool才128MB跑个百万行表就天天慢查询。调大它往往立竿见影。sort_buffer_size这是会话级内存每个连接排序时都会分配。设得太大容易导致内存占用过高一般建议2MB~8MB。它不是越大越好因为每个连接都可能分配连接一多内存就爆。join_buffer_sizeJOIN时用来缓存驱动表数据的缓冲区。同样会话级默认256KB对于复杂JOIN可以适当调大但别设成几十MB那会引来OOM。tmp_table_size和max_heap_table_size决定内存临时表的最大体积超过就会落盘。想减少Using temporary导致的磁盘临时表可以适当调大这两个值。但两者实际生效取较小值所以要一起调。innodb_io_capacity和innodb_io_capacity_max这是刷脏页速率的参数。机械硬盘和SSD的IO能力差别巨大如果SSD还用默认200刷盘能力跟不上写入性能会受限。按你的磁盘类型调整能让InnoDB后台刷盘更积极减少前台阻塞。参数的调整必须结合业务和监控逐步做千万不能抄别人一份配置就全堆上去。每个库的数据量、负载、磁盘类型都不同适合别人的不一定适合你。5.3 分区表与读写分离执行原理之上的架构级优化当单表的量级到千万甚至上亿索引优化能做到的上限就摆在那了。这时候需要考虑架构层面的手段。分区表是一个选项MySQL支持RANGE、LIST、HASH、KEY分区。分区对应用透明但优化器在裁剪分区partition pruning时会有额外的复杂逻辑如果查询条件无法裁剪到特定分区反而会扫全部分区比单表还慢。我建议除非有明确的时间范围查询需求否则谨慎使用分区。很多时候按时间做历史归档表比分区更实用。读写分离则是更常见的扩展手段。利用主从复制把只读查询分发到从库主库专攻写入。但要注意从库可能存在复制延迟实时性要求高的读不能用从库。另外如果大量查询因为索引没建好而慢从库也照样慢读写分离不能解决执行计划错误的问题。所以架构手段和数据调优不是替代关系而是叠加关系。5.4 MySQL 8.0与5.7的执行差异老经验要在新版本里修正很多从5.7时代过来的调优经验在8.0里要打个问号。比如前面说的查询缓存被移除如果你还在按老思维开query_cache8.0直接报错。再比如8.0的hash join让被驱动表无索引的JOIN查询也变快了但并不意味着你可以不建索引它只是用内存hash来减少扫描次数如果数据量大到超过join_bufferhash join会落盘性能照样崩。8.0的降序索引可以解决混合排序的filesort问题这是5.7做不到的。8.0的窗口函数也让部分原本需要临时表自连接的SQL可以改写得更高效。另外8.0默认的事务隔离级别依然是REPEATABLE READ但8.0引入了更完善的锁机制比如对不可重复读的解决方式导致一些老方案要重新验证。我调过的项目中升级到8.0后很多旧SQL的计划都变了因为优化器模型重写了。所以做版本升级后一定要重新压测并分析慢查询日志不能假设“版本升级后性能只升不降”。6. 我踩过的那些坑和最终沉淀的调优思路6.1 几个能救命的实操心得头一个心得调优先从“核对事实”开始而不是先改。很多人拿到慢SQL立刻试图改写结果改完也没变快。正确顺序是先EXPLAIN再EXPLAIN ANALYZE看看优化器估算多少行、实际扫多少行、有没有filesort/temporary、索引用没用上。把事实摆清楚再谈改法。我见过太多被改得面目全非的SQL最后发现只是统计信息过期。第二个心得不要在索引列上做运算和函数。举例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。这样create_time列本身没有被加工B树可以直接二分定位。同样WHERE name LIKE %abc%也是函数式前缀无法匹配最好用全文索引或Es解决。第三个心得批量操作要控制粒度。很多人用一条INSERT插入几万行或者一条UPDATE更新全表导致锁竞争激烈。乐观锁也好悲观锁也好长事务都是大忌。尽量把大批量拆成小批次每批几百行并且保持事务短小而完整。执行原理里InnoDB的锁和MVCC都绑定事务事务越长undo log越多历史版本链越长查询可能要在版本链里翻很久这也是慢查询的一个隐性源头。第四个心得备份和恢复机制不能省。调优过程中如果误操作把数据搞坏没有备份你会哭。我习惯调优前先导出线上表结构最好是做一次逻辑备份或者至少确保有最近的全量binlog可以恢复。这条不算MySQL执行原理但绝对算调优课里的必修课。6.2 一套可复用的SQL调优操作清单这几轮经验下来我沉淀了一套固定的操作清单执行效率很高。第一步打开慢查询日志收集一天的数据第二步用mysqldumpslow提取Top N第三步对每条慢SQL做EXPLAIN标记type、key、rows、Extra第四步用EXPLAIN ANALYZE验证实际耗时和扫描行数第五步根据问题类型对症下药索引问题重建索引统计信息问题ANALYZE TABLESQL写法问题改写SQL参数问题调整配置第六步上线后观察一周慢查询日志确认问题消失并及时清理不再需要的临时索引。这套流程看起来很朴素但结合执行原理后每一步都有依据。比如看到rows巨大你会思考是统计信息不对还是过滤性差看到Using filesort你会考虑排序能否用索引替代看到typeALL你会询问为什么优化器放弃索引。所有优化都不是拍脑袋而是顺着执行链路一层层查原因。6.3 最后补充一个关于锁与事务的调优视角执行原理除了查询路径还包括写入路径和锁机制。很多人调优只关心SELECT但系统里往往还有大量UPDATE和DELETE在争抢行锁。InnoDB的行锁是建立在索引上的如果UPDATE的WHERE条件没走索引行锁就会升级为表锁级的扫描导致大量阻塞。所以在生产环境对高频更新的表一定要保证更新条件的列有合适索引。这一点我吃过苦头一个订单状态更新操作WHERE条件是order_no结果order_no没有建索引每次更新全表扫描高峰期一压所有事务互相等锁数据库直接卡死。事务隔离级别也影响锁行为。REPEATABLE READ下普通的快照读不会加锁但当前读包括UPDATE、DELETE、SELECT ... FOR UPDATE会加锁。理解了这个你才能解释为什么一个UPDATE会把其他查询堵住。长事务持有锁不释放是造成锁等待超时Lock wait timeout exceeded的常见原因。所以业务代码里事务里别做耗时太长的外部调用事务提交前也别在事务里执行重SQL。这些都是从执行原理里推导出来的常识。MySQL调优不是堆参数也不是背命令而是真正理解从客户端到存储引擎之间每一步发生了什么。你看得见优化器的选择看得见执行器的扫描看得见索引的回表成本你才有能力判断一条SQL是优化一下写法就能好还是必须从索引结构层面动刀。这篇文章里的每一个案例我都在实际工作中真刀真枪地踩过写下来也就是希望大家少走几趟弯路。下次再遇到慢SQL别急着上网搜“MySQL调优大法”先EXPLAIN跑一下顺着执行原理查一遍问题往往自己就浮出来了。