从线上一个偶发慢查询开始说吧。有个订单查询接口业务高峰期每隔几分钟就要卡一下应用日志显示SQL本身执行了3秒多但这条SQL连的也就是一张10万行的小表where条件里用的还是主键。当时第一反应是“索引出问题了”结果一查索引在执行计划也正常。折腾了半天最后定位到的问题压根不在SQL本身而是连接线程打满、MDL锁排队把一条本该0.1毫秒返回的查询堵到了3秒。这事的教训很直接MySQL调优如果只看“SQL写得好不好”你永远只能看到浮在水面上的那一层。真正把性能瓶颈吃透必须从头到尾搞明白一条SQL在MySQL内部到底是怎么被执行的——连接怎么建立、解析器怎么读懂你的语句、优化器为什么选了这条路而不是那条路、执行器最终怎么把数据返回来。这个执行链路才是所有调优动作的根。这篇文章我就把MySQL执行原理从头到尾拆一遍每一层对应什么调优手段全说清楚。1. 一条SELECT的完整旅程从连接器到执行器很多人一提到MySQL调优脑子里蹦出来的就是“加索引”“改配置”但索引只是执行链路上的一环。在你按下“执行”之后SQL先要穿过好几个关卡才能见到数据。我把这些关卡按顺序捋一遍你就知道每个环节各自能优化什么。1.1 连接器你的SQL是从这里排队进入的客户端连上MySQL的第一步是TCP握手加身份认证这个环节由连接器负责。连接器做的事情很纯粹校验用户名密码获取该用户的权限列表建立一条专用连接后续所有SQL跑在这个连接上这里有三个跟调优强相关的细节。第一权限是连接建立时一次性加载的。你执行了GRANT语句已经建立的旧连接不会立刻感知到新权限必须断开重连才生效。这个很多人都没注意导致改完权限发现“怎么还报没权限”。第二每个连接在MySQL里都对应一个线程。线程不是免费的每个线程占内存、要参与调度、切换有开销。max_connections默认只有151但生产库经常被人为调到两三千。连接数越高MySQL花在线程上下文切换上的CPU就越多单条SQL的响应反而变慢。所以“连接池越大性能越好”是个经典的误区连接池大小一般压到CPU核数的2到4倍附近收益最高。第三连接建立后如果一直空闲会占用资源不释放。默认wait_timeout是8小时对长连接池来说太宽松了。建议应用侧连接池把空闲回收时间压在5到10分钟MySQL侧可以把wait_timeout调成600秒避免一堆僵尸连接占着线程资源。1.2 解析器MySQL是怎么“读懂”你的SQL连接建立后SQL文本进入解析器。解析器做两件事词法分析把SQL字符串拆成一个个token语法分析把这些token按SQL语法规则拼成一棵语法树。这一步只校验“你写的SQL语法对不对”不做任何表名、字段名校验。也就是说你写一个“SELECT non_exist_col FROM non_exist_table”如果语法结构没问题解析器照样能生成语法树等到预处理器阶段才报“Unknown column”。解析器层面的调优空间其实不大但有几点能帮你规避CPU浪费同一类SQL尽量用绑定参数别在SQL里拼一大长串文本。解析器对文本长度敏感一个几KB的动态SQL反复解析CPU消耗肉眼可见。避免写“一句话做所有事”的超级大SQL几千行的存储过程或嵌套视图展开后语法树会膨胀得很厉害解析和优化成本都会上升。1.3 优化器代价模型下的“黑盒决策”语法树生成后真正的重头戏来了——优化器。它的任务是为这条SQL生成若干候选执行计划然后按“代价”选一个最便宜的。需要注意MySQL优化器里的“代价”不是我们平时说的毫秒耗时而是一个估算值主要由两层叠加CPU代价估算要读取多少行、对多少行做条件判断、做多少排序和临时表操作IO代价估算要读多少个数据页、多少次顺序读和随机读估算的基础是统计信息而统计信息来源于information_schema里的表数据采样后面会专门说统计信息失真。所以优化器不是真的跑一遍SQL再选计划它是在“猜”哪条路最便宜。这个猜测对不对直接决定你有没有踩到“有索引不用”的坑。一条典型SQL可能会被优化器做这些事选择用哪个索引、决定表的连接顺序、把子查询改写为半连接、把IN改成临时表。8.0里还做了很多额外改写比如“条件常量传播”“外连接转内连接”。1.4 执行器最后一公里的逐行博弈执行计划确定后执行器开始真正干活。它的工作方式是调用存储引擎接口逐行取数据在server层做条件判断再把满足条件的行返回。这里有个长期存在的经典问题——server层与InnoDB层的行数不一致。InnoDB支持一种叫“索引条件下推”Index Condition PushdownICP的优化把WHERE里的部分条件直接下推到存储引擎层过滤减少返回给server层的行数。没有ICP的时候InnoDB要把所有满足索引条件的行全抛给server层再由server层逐行做剩余条件过滤IO和CPU都白白浪费。执行器阶段你能做的调优让查询尽可能走覆盖索引减少回表次数等于减少执行器与存储引擎之间的数据搬运查看explain里的rows字段和实际影响行数的差距判断执行器是不是在做无用功关注慢日志里的Rows_examined这个值如果远大于实际返回行数说明有大量行被扫描后被丢弃典型场景就是“隐式转换导致索引失效后全表扫描”这条链走完之后结果才通过网络返回客户端。整个链路每一层都有自己的开销调优就是逐层找短板。2. 存储引擎层的执行真相Buffer Pool、redo log 与 MVCC上面说到的连接器、解析器、优化器、执行器都算server层但真正决定一条SQL快不快的还有半边天——InnoDB存储引擎层。如果你不理解InnoDB底层怎么存数据、怎么刷盘、怎么处理并发版本很多调优参数你根本不知道为什么这样设。2.1 InnoDB的数据组织聚簇索引、二级索引与回表InnoDB的表数据不是“一行行平铺在文件里”而是按B树组织的。这个B树的叶子节点存的是完整数据行所以它也叫聚簇索引。表里每行的主键值决定了这一行在B树的什么位置。当你给某列建了一个索引InnoDB会再建一棵B树——二级索引。二级索引的叶子节点不存完整数据行只存索引列的值 主键值。于是就有个关键问题如果你的SQL在二级索引上找到了符合条件的行还需要拿着主键再去聚簇索引里查一次完整数据行这个过程叫回表。回表意味着多一次B树的随机访问。数据量小感觉不出来数据量大、回表行数多的时候性能直接崩。举个例子SELECT * FROM orders WHERE user_id 100;orders表有几百万行user_id上有普通索引。这条SQL会在user_id的二级索引里找到所有符合条件的行然后每个主键都回聚簇索引取全字段。如果user_id100这个用户有10万行订单那就意味着10万次回表速度可想而知。解决办法就是覆盖索引——把要查的列全部放进索引里SELECT user_id, order_id, amount FROM orders WHERE user_id 100;如果给(user_id, order_id, amount)建一个联合索引这时的查询根本不回表直接在二级索引的叶子节点里就把所有字段取完了Extra列会显示Using index。这就是“索引覆盖”的魅力它省掉的是整个回表过程。2.2 Buffer Pool数据库的“内存缓存工厂”InnoDB在内存里维护了一个巨大的缓存区域——Buffer Pool。所有数据页的读写都先经过它读的时候先看Buffer Pool里有没有没有才去磁盘读写的时候先改Buffer Pool里的页然后由后台线程慢慢刷回磁盘这个设计很好理解磁盘随机IO慢到令人发指内存随机读比磁盘快好几个数量级。Buffer Pool命中率越高SQL执行越快。你可以直接看状态变量SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;命中率 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests。生产环境上长期低于99%就要注意了说明要么Buffer Pool太小要么SQL扫描的数据量超出了内存承受范围。调优上最核心的参数就是innodb_buffer_pool_size默认只有128MB这简直是对现代服务器的浪费。通用经验值物理内存建议Buffer Pool16GB8GB ~ 10GB32GB16GB ~ 22GB64GB32GB ~ 44GB128GB64GB ~ 96GB原则是留出操作系统和其他进程的余量别一股脑全给MySQL。再有就是8.0开始支持多个buffer pool实例用innodb_buffer_pool_instances参数拆分可以降低并发访问的锁竞争。2.3 redo log 与 binlog每一次提交背后的刷盘博弈只要一想“为什么MySQL不能直接把数据写进磁盘”你就理解redo log的意义了。真实的数据页在磁盘上是随机分布的每次更新都去随机写磁盘整个数据库会被拖垮。InnoDB的答案是先写日志后写数据。日志是追加写的顺序IO比随机IO快得多。这个策略叫WALWrite-Ahead Logging。数据页在Buffer Pool里被修改后并不马上落盘而是先记一条redo log等将来某个时机后台线程再把脏页合并刷回磁盘。与redo log配套的还有binlog它是server层的逻辑日志。一个事务提交时要同时保证redo和binlog都能对上InnoDB用了两阶段提交先写redo prepare再写binlog最后把redo改为commit状态。这样可以防止“binlog有了但redo没有”导致主从数据不一致。调优的关键参数是innodb_flush_log_at_trx_commit 1每次事务提交都强制fsync redo log到磁盘。最安全但每次提交都有一次磁盘刷盘性能最差innodb_flush_log_at_trx_commit 0每秒刷一次性能最优但MySQL进程崩溃时会丢最多1秒事务innodb_flush_log_at_trx_commit 2提交时写入操作系统缓存每秒刷盘性能介于两者之间操作系统断电才会丢数据生产环境默认保持1这是数据安全底线。如果你做的是可以容忍小概率丢数据的业务并且追求极致吞吐才考虑改成2。至于0一般不建议。2.4 undo log 与 MVCC快照读为何“无锁”执行原理中容易忽略的一环是MVCC多版本并发控制。它允许你在不加锁的情况下读取数据的历史版本这就是“快照读”。每一行数据都藏了两个隐藏列DB_TRX_ID最近修改这一行的事务ID和DB_ROLL_PTR回滚指针。当一个事务对某行做了修改老版本不会立刻消失而是通过undo log串成一条历史版本链。新事务做快照读时会根据read view判断当前这行哪个版本对它是可见的。举个例子事务A正在改一行数据还没提交事务B执行普通SELECT。在RR可重复读隔离级别下B拿到的是事务A修改前的旧版数据完全不需要等待A释放锁。这就是为什么在并发量很大的系统上普通查询不会互相阻塞。这个机制引出的调优点是避免长事务。事务一直不提交undo log就不断累积历史版本链越来越长查询要做更深的版本回放速度越来越慢。同时长事务会让Buffer Pool里被修改过的旧版本页迟迟不能复用间接影响缓存效率。监控里如果看到秒级事务很少、分钟级事务很多就是报警信号。3. 优化器为什么总选错索引统计信息、隐式转换与 optimizer trace前面说过优化器是“猜”着选执行计划的。猜的依据是统计信息。统计信息准不准决定了优化器是不是靠谱。大多数人调优遇到“我明明建了索引它就是不鸟我”时往往就是统计信息或优化器判断出了问题。这一章我专门拆它。3.1 统计信息失真优化器“眼瞎”的根因InnoDB统计信息不是实时精确的它基于采样。默认情况下InnoDB从每个索引上随机抽取一部分数据页来估算“这个索引有多少个不同值”“每个值大概多少行”。这个采样量有限一旦表数据分布是偏斜的估算值就会离谱。经典场景订单表里status字段99%的行都是“已支付”只有1%是“待支付”。你在status上建了索引查询“WHERE status待支付”时优化器可能觉得“走这个索引要扫描的行数也不少不如全表扫”于是放弃索引换了全表扫描跑出几十倍的性能差距。处理办法很直接ANALYZE TABLE orders;强制重新采集统计信息。8.0还支持为列建直方图ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;直方图能给优化器提供更精确的分布信息特别适合这种严重偏斜的列。但注意直方图不会改变索引的“物理真实”它只是让优化器在估算代价时更接近真相。3.2 optimizer trace让优化器把决策过程全盘托出碰到优化器“脑回路”奇怪的SQL我强烈建议直接开optimizer trace看看它到底在盘算什么。操作很简单SET optimizer_trace enabledon; -- 执行你的那条SQL SELECT ... FROM orders WHERE ...; SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET optimizer_trace enabledoff;返回结果里能看到优化器对比了哪些候选索引、每一步的cost估算值甚至看到它为什么排除了某个方案。我遇到过最典型的场景优化器认为某个二级索引的“回表成本”过高宁可全表扫也不走索引但真实执行时回表行数并不多。看完trace才知道是统计信息给出的重复值估算偏大害得成本被高估了。这种问题靠人力猜是猜不出来的trace才是直接证据。3.3 索引失效的三种“假象”函数、隐式转换与字符集网上讲索引失效的文章一大堆但多数只列现象不讲原因。这里我说三个最常见的“假失效”帮你从原理上理解第一对索引列使用函数或计算。比如WHERE DATE(create_time) 2025-01-01索引存的是原始字段值不是函数计算结果MySQL没法对任意函数的输出做范围匹配只能全索引扫描。正确写法是WHERE create_time 2025-01-01 00:00:00 AND create_time 2025-01-02第二隐式类型转换。字段phone是VARCHAR你写phone 13800138000数字和字符串一比对MySQL会把VARCHAR转成数字再做比较索引直接失效。拿到explain一看typeALL就是它。解决办法是把条件写成字符串形式保持两边类型一致。第三字符集不一致导致join失效。两个表的关联字段一个是utf8mb4一个是utf8mb3join时MySQL必须做字符串转换索引匹配不了。建表时统一字符集、统一排序规则是最容易被忽略的“底层规范”。3.4 force index 与直方图给优化器“上手段”当统计信息修了、trace看了、问题还在最后的兜底方案是force index强制让优化器走指定索引SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id 100;但我要说一句force index是“物理干预”不是常规首选。它的问题在于写死了索引路径将来数据分布变化、更好的索引出现时这个“强制”反而成了枷锁。我习惯的优先级是analyze table → 直方图 → 改写SQL → 最后才是force index。前面三个都是“让优化器自己变聪明”force index则是“不信任优化器”属于备选中的备选。4. 用慢查询日志、explain 和 profiling 定位执行瓶颈前面讲完了MySQL内部是怎么工作的接下来要进入实操环节当你发现系统变慢怎么把“最拖后腿的SQL”找出来并且精确定位到它在执行链路的哪一段耗时最高。4.1 慢查询日志划定嫌疑范围的第一步MySQL默认不开启慢查询日志但生产环境强烈建议打开。配置一般在my.cnf里slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ON我的建议是long_query_time设成1秒这能筛出大多数有问题的业务SQL又不会产生太多日志。如果系统很忙还可以把log_output设置成TABLE让慢查询直接写进mysql.slow_log表方便SQL查询统计。反正别把慢日志全关了否则出了性能事故你连从哪里查起都不知道。慢日志里核心看两个指标Query_time是总耗时Rows_examined和Rows_sent的差值是扫描但未返回的行数。这两个数差得越离谱越说明SQL在做低效扫描。4.2 explain 输出字段的实战读法拿到嫌疑SQL后的第一动作永远是explain。给你一条比较典型的慢SQLEXPLAIN SELECT * FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.status 1 ORDER BY o.create_time DESC LIMIT 20;输出的关键列我在下面列一下真正影响调优判断的字段字段含义调优关注点type访问类型从好到差排序const eq_ref ref range index ALL。出现ALL请立刻警觉key实际使用的索引NULL表示没走索引key_len索引使用的字节长度可以判断联合索引哪几个列被真正用上了rows优化器估算的扫描行数和真实行数差异过大说明统计信息失真filtered返回行数占扫描行数的百分比过低说明大量行被过滤索引选择不精准Extra附加信息Using filesort、Using temporary、Using join buffer都是性能警告type列从ALL到ref的提升是最常见的优化空间。ALL是全表扫描index是扫了整棵索引树ref是走了非唯一索引的等值匹配const是通过主键或唯一索引精确定位单行。一条SQL能从ALL优化到ref提升往往就是数量级。Extra里出现Using filesort时要特别警惕。它意味着MySQL不得不额外做一次排序常见于ORDER BY的列不在索引中。解决思路是设计联合索引时把排序字段带上让索引天然有序排序步骤直接省略。4.3 profiling 与 performance_schema计时器级的拆解explain能看到计划但看不到“时间花在哪”。想要精确定位执行链路里的耗时大头用profilingSET profiling 1; -- 执行你的SQL SELECT ...; SHOW PROFILES; SHOW PROFILE FOR QUERY 1;SHOW PROFILE的输出会列出这条SQL各阶段的耗时包括connecting、parsing、optimizing、executing、Sending data等。我见过的典型情况是Sending data占比极高这个看似笼统的阶段实际上包含了存储引擎取数和server层处理行的全过程占大头通常意味着扫描行数实在太多必须回到索引优化这条路。更系统化的方案是看performance_schema。它记录了语句维度的统计信息SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS total_ms, ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY total_ms DESC LIMIT 10;这能直接列出“哪个SQL累计消耗时间最多”对于快速圈定线上TOP慢SQL非常有效比翻慢日志再肉眼统计高效得多。4.4 sys 库性能视图里的“速效救心丸”MySQL的sys库把performance_schema的数据封装成了好读的视图。如果你不想写一长串statistics表查询直接用现成的SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10; SELECT * FROM sys.host_summary ORDER BY statements DESC LIMIT 10;statement_analysis直接给出每条SQL的响应时间、扫描行数、返回行数、执行次数。另一个我很常用的SELECT * FROM sys.innodb_lock_waits\G一张视图把“谁在等锁、谁拿着锁、哪个事务阻塞了哪个事务”全列出来排查锁等待比手查information_schema三张表快得多。5. 执行原理落地调优五个真实案例与解决手法原理讲再多最后还是得回到业务SQL上验证。这里整理五个我实际处理过的案例每一个都能映射回前面某一段执行原理。5.1 深分页的limit大偏移回表之外的隐藏成本典型问题SQLSELECT * FROM orders ORDER BY id LIMIT 1000000, 20;这条SQL看着简单实际执行时MySQL要先把前100万行全找出来再跳过它们只回最后20行。排序和回表的浪费都在“无用功”上。一个常用改写是延迟关联先在索引上完成分页定位再回表取完整行SELECT o.* FROM orders o JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t ON o.id t.id;子查询只在索引上扫描索引覆盖了主键和排序字段不回表扫描成本大幅下降。如果业务允许按游标翻页用“书签法”更狠记住上一次查询的最大id下一次直接WHERE id 上一次id LIMIT 20完全绕开大偏移量。5.2 order by排序filesort的两种活法MySQL的排序有两种路径走索引有序性Extra里看不到filesort因为B树天然有序filesort把数据读出来在sort_buffer里自己排filesort又分两种执行方式。当排序字段的字节数少或者一行数据量小时MySQL会采用“全字段排序”直接在内存里排完整行结果。当行数据超过max_length_for_sort_data5.7及以前的限制时就会退化到“rowid排序”——先排序需要的字段和主键排完之后再回表取整行。回表意味着大量随机IO性能一个天一个地。优化方向就两个ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);让查询“WHERE status1 ORDER BY create_time”直接走联合索引排序步骤消失。再配合覆盖索引连回表都省了Extra里彻底干净。如果确实无法避免filesort再考虑调大sort_buffer_size让排序尽量在内存里完成而不是溢出到临时文件。5.3 count(*) 为什么这么慢从存储引擎猜答案InnoDB没有保存表的精确行数这是MVCC带来的天然代价——不同事务看到的不同版本行数都不一样MySQL没法维护一个“对所有人都准确”的计数器。所以每次执行COUNT()都得现场扫描扫描多少行就数多少行。网上经常看到“用count(1)比count(*)快”的说法这是谣言。MySQL优化器对两者一视同仁实际都会转化为对最小可用索引或者全表的遍历。调优方向对超大表不要频繁执行count(*)改用一个单独的小表或Redis维护计数器如果需求是“大概多少行”查统计信息SELECT table_rows FROM information_schema.tables WHERE table_name orders;它是个估算值但快如闪电。业务能接受误差就用它不能接受还是上计数器方案。5.4 锁等待与死锁并发执行时的“隐形交通事故”普通SELECT走快照读不加锁但UPDATE、DELETE、SELECT ... FOR UPDATE这些当前读必须加锁。InnoDB当前读加的行级锁有几种记录锁锁住单条记录间隙锁锁住一个区间的空隙临键锁记录锁加间隙锁的组合是RR隔离级别下防幻读的默认方式间隙锁是死锁最常见的元凶。典型场景事务A更新了一个区间内的部分数据事务B同时往这个区间插入新值A和B互相等待对方释放间隙锁死锁就产生了。MySQL检测到死锁后会自动回滚代价较小的事务但你的业务层会报Deadlock found触发重试逻辑。排查锁等待时我通常按这个顺序操作-- 1. 看都有谁在跑事务 SELECT * FROM information_schema.innodb_trx\G -- 2. 看谁在等锁、谁持锁 SELECT * FROM sys.innodb_lock_waits\G -- 3. 如果遇到死锁看死锁现场 SHOW ENGINE INNODB STATUS\G重点看LATEST DETECTED DEADLOCK片段里面会打印两个事务各自执行的SQL和持有的锁信息死锁形成的原因一目了然。这种问题靠加索引往往解决了关键还是业务SQL的执行顺序要一致避免两个事务以不同顺序拿锁。5.5 join性能小表驱动大表背后的执行逻辑提到join就绕不开“小表驱动大表”这句口诀。从执行原理看是这样被驱动表如果能用上索引MySQL会对驱动表的每一行拿关联字段去被驱动表的索引里查——这是经典的Nested-Loop Join。驱动表越小循环次数越少性能越好。用explain看第一行是驱动表第二行是被驱动表。想要确认驱动关系对不对看第二行的type是不是ref或eq_ref如果是ALL就要小心说明被驱动表没走索引可能已经在退化使用join buffer做block nested loop了一次把多行加载进内存批量比对。join优化最核心的两招被驱动表的关联字段必须有索引否则每次匹配都是全表扫描尽量减少被驱动表的扫描行数让where条件先过滤掉一大半再进join如果join的是两张几百万行的大表再怎么优化也不如从业务上砍掉这个join——提前把关联数据查出来在应用层合并是很多高并发系统的真实选择。这些案例做下来你会发现没有一个调优手段是孤立的。索引设计影响到执行器的回表次数统计信息失真影响优化器的计划选择长事务拉长MVCC版本链影响快照读速度锁等待又直接卡住当前读。整条执行链路环环相扣你只有把每一环的原理都装进脑子里遇到性能问题才敢说“看一眼就能猜到大概是哪里出了问题”。我自己做MySQL调优这几年最大的体会是别一上来就怀疑参数先顺着执行路径把SQL的实际行为摸清楚参数是最后才动的扳手。