企业官网建设流程全解析

最近有个同事跑来找我，说他们线上一条SQL查了十几秒，加了索引也没用，问我要不要考虑换机器。我一听就知道问题没这么简单，让他把执行计划发过来，果然又是典型的索引失效叠加排序失控。这类问题在业务系统里太常见了，根子不在机器性能上，而在你对MySQL到底怎么执行一条SQL这件事的理解上——不理解执行原理，调优就永远是打地鼠，今天换个参数，明天加个索引，到处救火却不清根。这篇内容我结合自己这几年处理过的线上事故，把MySQL的执行原理从连接建立到返回结果整个链路拆开讲一遍，每个环节跟性能调优的对应关系也一并说清楚，适合后端开发、运维、以及想进阶的DBA同学参考。

1. 从一条慢SQL说起：MySQL到底是怎么执行你的命令的

1.1 一条查询背后的完整链路

你在客户端敲下一句SELECT，到结果返回屏幕，这中间经历的动作比你想象的多得多。MySQL不是一个单进程程序闷头干活，它是一套分层协作的体系，大体可以分成三层：连接层、服务层、存储引擎层。

连接层负责跟你打交道，认证身份、维持会话；服务层负责“翻译”和理解你的SQL，做优化决策；存储引擎层才是真正去磁盘上翻数据、操作索引的地方。以一条最简单的SELECT * FROM users WHERE id = 100为例，完整路径是这样的：

1. 客户端通过TCP协议跟MySQL建立连接，连接器校验用户名密码、获取权限信息。
2. 你发出SQL文本，服务层先查查询缓存（8.0之前），命中就直接返回。
3. 未命中则进入解析器，做词法分析和语法分析，把SQL字符串变成内部结构（语法树）。
4. 预处理器进一步校验表名、列名是否存在，权限是否足够。
5. 优化器登场，分析这个语法树，决定用哪个索引、以什么顺序连接多张表，生成执行计划。
6. 执行器根据执行计划，调用存储引擎的接口逐行读取数据。
7. 存储引擎在内存缓冲池和磁盘之间做数据读取，返回满足条件的行。
8. 执行器对返回的行做后续处理（排序、分组、计算表达式等），最终结果返回客户端。

这八步里面，耗时大头往往不在第8步，而在第5步和第7步——优化器的决策质量决定了扫多少数据，存储引擎的访问方式决定了每一步有多快。理解了这个分工，你就会明白为什么很多调优手段是“围绕优化器和存储引擎做文章”。

1.2 分层架构给调优带来的两个核心洞见

第一层洞见是调优不能只盯着SQL本身。连接层有连接数上限、超时时间；服务层有排序缓冲、临时表策略；引擎层有缓冲池大小、刷盘策略。一条SQL慢，可能是被连接排队卡住了，也可能是排序缓冲区太小导致磁盘临时表，还可能是innodb_buffer_pool_size严重不足导致频繁读盘。盲人摸象式调优只会浪费时间。

第二层洞见是执行计划的代价模型决定一切。MySQL优化器本质上是个基于代价的决策器，它估算每种执行方式的CPU成本、IO成本、内存成本，选一个它认为最低的。但这个估算是基于统计信息的，统计信息不准，决策就偏。你就能理解为什么有时候明明有索引，优化器却选择了全表扫描——它从统计信息里算出“全表扫描比走索引更快”，而这是错的。理解了这层，调优就有了方向：要么让统计信息更准，要么用索引提示干预决策，要么重写SQL让优化器走正路。

2. 解析与优化的黑匣子：连接管理、查询缓存与代价模型

2.1 连接管理：看似不起眼，却常常是瓶颈

MySQL的通信协议是半双工的，意味着同一时刻只能一方在发数据，客户端发完SQL后必须等服务器响应。这个机制本身不难，但实际生产里我见过大量因为连接管理不当引发的性能事故。

典型问题是短连接风暴。PHP类应用如果每请求都新建连接，在高并发下握手认证的开销会迅速堆积。MySQL建立连接要做TCP三次握手、认证、权限读取，这一步的CPU开销比执行一条简单查询还大。调优方向上，服务端可以适当调大max_connections，但这不是根本解法——我习惯配合connection pooling在应用层做连接复用，把建连次数降下来，通常能直接消灭一波连接等待的告警。

还有一类坑是sleep连接堆积。应用层连接池配置不当，连接闲置也不释放，DBA看show processlist满屏都是Sleep，占用连接数逼近上限，新的正常查询进不来。这时候排查方向不是MySQL，而是应用层的连接池maxLifetime、idleTimeout这些参数。

注意：max_connections调大只是临时止血，真正要做的是控制应用侧的连接行为。我见过把max_connections从200调到2000的，结果MySQL线程数暴涨，上下文切换把CPU打满，反而更慢。

2.2 查询缓存：为什么MySQL 8.0干脆移除了它

如果你维护过MySQL 5.7及更早版本，应该对query cache不陌生。它的设计思路笨拙但直接：把SELECT结果以SQL文本为key缓存在内存里，下次同样的SQL直接返回结果，不再执行解析、优化、读取。

听起来很美，但实际效果往往适得其反。问题出在缓存失效机制上——对某张表的任何写操作，都会让这张表的所有查询缓存全部失效。在读写比高、写操作频繁的业务里，缓存刚建立就被清掉，不仅没命中，还要付出维护和失效的额外开销。MySQL官方在8.0版本直接移除了这个功能，算是承认这条路走不通。

这对调优的启示是：不要在数据库层面依赖缓存，需要缓存就上Redis等专用组件。反过来，如果你在8.0之前的版本还在用query cache，我的建议是直接关闭——query_cache_type=OFF，把省下的内存交给InnoDB缓冲池，效果几乎总是更好。

2.3 解析器与预处理器：慢SQL最初的门槛

很多开发者以为解析SQL是个瞬间动作，不关心这块。实际上解析器的性能直接受SQL文本复杂度影响——表多、子查询多、嵌套深、函数多的SQL，解析开销呈指数级上升。我实测过一条十几个JOIN加上多层子查询的报表SQL，仅解析耗时就能到几十毫秒，对比一条简单查询的不到0.1毫秒的解析时间，差距惊人。

预处理器的职责是语义检查：表存不存在、列存不存在、有没有权限。这个环节看起来平淡无奇，但诊断问题很有用——当一条SQL报“Unknown column”这类错误时，预处理阶段就会拦截，根本不会进入优化器。这提醒我们，排查问题时先把这类低级错误排除掉，别在优化器层面找不存在的复杂原因。

2.4 优化器与代价模型：决定SQL生死的核心决策者

优化器拿到语法树后，会生成多个执行计划，然后基于统计信息计算每个计划的代价。MySQL对一个执行计划的总代价，粗略可以理解为IO成本加上CPU成本，公式类似：总成本 = 行数 × 每行IO成本 + 行数 × 每行CPU成本。

这里有个关键点：行数是靠统计信息估出来的。InnoDB的统计信息来源于采样，不是精确值。当表数据快速增长、或者数据分布极度不均匀时，统计信息很容易失真。我就遇到过一次经典场景：某表有一个区分度极低的is_deleted字段，只有0和1两个值，95%的数据是0。业务SQL条件明明是is_deleted=0，优化器从统计信息估算“需要扫描大概50%的行”，放弃了这个字段上的索引，反而去全表扫。但实际上业务里一大批记录是刚插入的1，需要用索引快速定位——这就是统计信息失真导致优化器选错索引的典型case。

对这种问题，我有几种实操手法：

• 手动执行ANALYZE TABLE刷新统计信息，观察执行计划是否变化。
• 使用FORCE INDEX强制走索引，但要注意这是临时手段，需要持续观察。
• 更优雅的方式是改写SQL，比如把is_deleted=0改成is_deleted IN (0)这种写法骗过优化器的估算逻辑——某些版本下IN的估算区分度更高。

优化器还涉及一个重要的策略：什么时候放弃索引选择全表扫描。MySQL没有“索引合并”以外的复杂策略，它就是个简单的代价对比。当优化器算出全表扫描的代价更低，你再怎么加索引都没用。这时候你需要做的不是埋怨MySQL傻，而是思考为什么走索引反而更贵——常见原因包括回表次数太多、区分度太低、数据量本身太小。

2.5 优化器选错索引时的干预手段

选错索引这个问题，我处理过太多次。有次线上订单表，一个查询条件里有create_time和status两个字段，分别有各自的单列索引。优化器估算create_time索引能筛掉更多数据，选了它，结果该时间范围内数据量被低估，实际扫了几十万行，加上回表，直接把库拖垮。

干预手段我推荐按以下优先级排：

1. 先刷新统计信息，ANALYZE TABLE，可能直接就解决了。
2. 改造SQL，例如把条件里面的函数去掉、把OR改成UNION、调整条件顺序，让优化器看到的东西更清晰。
3. 使用FORCE INDEX明确指定索引，前提是你深刻理解这个索引在当前SQL下确实更优。
4. 实在不行就在应用层做缓存，降低这条SQL的调用频次。

贴个实际经验：不要一上来就FORCE INDEX。它的副作用是锁死了执行计划，如果未来索引变更或数据分布变化，这个强制指令反而会挡住优化器的正确选择，变成新的性能炸弹。能通过改造SQL解决的，优先改造SQL。

3. 执行器与存储引擎协作：回表、索引下推与MRR

3.1 执行器调用的引擎接口

优化器产出执行计划后，执行器开始接手。执行器跟存储引擎的交互方式，不是一次性把数据全部捞出来，而是迭代式地“取下一行”。这个设计很关键——MySQL的存储引擎接口层定义了一个类似游标的读取协议，执行器通过不断调用引擎的index_read、index_next这类接口逐行获取数据。

每取到一行，执行器会做两件事：一是判断这行是否满足where条件中引擎层无法过滤的部分，二是如果需要，把这行数据发送到服务器层做后续处理。注意了，索引条件下推（ICP）出现之前，所有行数据的过滤都要回传到服务器层做，这意味着即使索引能定位到大致范围，每一行数据的完整内容还是得从磁盘读出来再传上去，传输成本和内存占用都很大。

3.2 回表与覆盖索引：一个老生常谈但总被忽略的话题

InnoDB的索引结构是B+树。主键索引的叶子节点保存的是整行数据，叫做聚簇索引；二级索引的叶子节点保存的是索引列值加主键值。当你通过二级索引查找时，过程是：先搜索二级索引B+树找到主键值，再用主键值去聚簇索引里搜索整行数据，这个“二次搜索”就叫回表。

回表的代价有多大？取决于你命中了多少行。如果二级索引过滤后还剩1万行，就要回表1万次——即便每次回表都在缓冲池命中，也是一万次B+树搜索操作。这就是为什么我一直强调：设计索引时要有覆盖索引的意识。所谓覆盖索引，就是索引里已经包含了查询所需要的全部字段，InnoDB查到二级索引叶子节点后，发现所有列都在里面，直接返回，不需要回表。

举个具体例子，有个订单查询页面，SQL大概是：

SELECT order_id, amount, status FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 20;

如果只在status上建单列索引，那么每次根据status定位后都要拿主键去聚簇索引回表取amount和create_time，最后还要做排序。正确姿势是建一个联合索引：

ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);

这个索引同时覆盖了status等值过滤、create_time排序，而且字段里面已经包含了order_id和amount（如果amount没有，则还需要回表取amount）。索引设计合理的话，这条SQL可以在索引内部完成全部工作，极限情况下连回表都省掉，排序顺带也解决了。

注意：覆盖索引不是万能的。索引字段越多，B+树占用空间越大，写入成本越高。我一般只在“高频查询、结果集字段固定”的场景下设计覆盖索引，不会在每张表上无脑加。

3.3 索引条件下推：帮你省掉大量无效回表

ICP大概是MySQL 5.6之后引入的一个不起眼但效果极佳的特性。它的核心思想是：把where条件中某些无法用索引定位、但跟索引列相关的过滤条件下推到存储引擎层，让引擎在读取索引记录时提前判断，不满足条件的记录直接跳过。

举个例子，一个联合索引(a, b)，SQL是SELECT * FROM t WHERE a > 100 AND b LIKE 'abc%'。按B+树最左前缀原则，b的like条件无法用于定位，但如果开了ICP，存储引擎在扫描索引a>100的范围内每一条记录时，会先检查b是否满足like条件，不满足就不回表，大大减少了回表次数。

这个特性默认是开启的，但你可以在EXPLAIN输出的Extra列看到“Using index condition”字样来确认是否生效。当你看到某个SQL的回表量特别大时，检查一下是不是ICP没生效——比如版本过旧，或者条件里的字段不在索引列中。对于老项目升级MySQL，ICP往往是开销最低的免费午餐。

3.4 MRR：多范围读优化的意义

MRR（Multi-Range Read）解决的是另一个回表痛点——当二级索引过滤出来的主键值是无序的，回表时聚簇索引的读取就变成随机IO。随机IO比顺序IO慢好几个数量级，这是磁盘介质的物理特性决定的。

MRR的思路是把主键值收集到缓冲区里，排序，再统一去聚簇索引回表，把随机IO转成近似顺序IO。这个优化对机械硬盘时代意义重大，SSD时代效果被削弱了，但也不是没用。调优时留意read_rnd_buffer_size参数会影响MRR的缓冲区大小，如果发现执行计划里出现“Using MRR”，并且伴随大量磁盘读，可以适当调大这个值观察效果。

4. 执行原理映射调优：排序、JOIN、锁与事务的真实影响

4.1 ORDER BY排序：filesort不是文件排序那么简单

执行计划里看到“Using filesort”时，很多新手会慌，以为在磁盘上排序了。其实filesort的意思是“需要额外排序”，排序可能在内存完成，也可能落盘。真正决定性能的是排序的数据量，以及它跟sort_buffer_size的匹配关系。

排序流程大致这样：执行器按条件把满足where的行取出来，把需要排序的字段和行指针（或者整行数据，取决于是否开启max_length_for_sort_data）放入排序缓冲区，在缓冲区里做快速排序；如果缓冲区放不下，就把排序好的部分数据写到磁盘临时文件，最后做多路归并。数据量大时，落盘归并的代价是数量级的增长。

我实测过一张200万行的表，对某个字段排序并取前20条，如果order by字段没有索引而where筛选又不强，排序可能耗时几百毫秒到几秒。加了正确的联合索引后，排序直接走索引有序性，耗时降到十几毫秒。这个提升不是排序算法优化出来的，而是直接从“先查出来再排序”变成了“索引天然有序，只要顺序读就行了”。

经验总结：有排序需求的SQL，优先考虑让排序字段进索引。排序字段是等值条件后面的第一个字段时，效果最好。但要注意，排序方向——如果索引是升序，而SQL要求降序，MySQL 8.0之前无法直接反向扫描索引，还是得filesort；8.0之后支持降序索引，这是一个实实在在的版本红利。

4.2 JOIN算法：从嵌套循环到哈希连接的演进

JOIN是SQL里最容易出性能问题的地方。MySQL长期以来的主力算法是NLJ（嵌套循环连接），也就是先查驱动表，拿驱动表的每行去匹配被驱动表的索引。这个算法下，被驱动表是否有高效索引至关重要——如果被驱动表的关联字段没有索引，每匹配一行就要做一次全表扫描，复杂度直接爆炸。

对NLJ场景的调优就是：被驱动表的JOIN字段必须建索引，同时要尽量让驱动表的行数少。驱动表的行数由where条件决定，通常是过滤性更强的那个条件所在表。你可以通过EXPLAIN里的type列来观察——遇到ALL全表扫描的，多半就是被驱动表没索引，或者表关联顺序选反了。

MySQL 8.0引入了hash join，专门解决被驱动表无索引时NLJ的低效问题。它先把小表加载到内存构建哈希表，再遍历大表逐行探测，复杂度从O(N*M)降到O(N+M)。这对等值JOIN、且数据量差异大的场景是颠覆性的提升。我在实际业务里见过一个三表关联的报表查询，从原来的10秒降到1秒以内，全靠8.0的hash join兜底。

注意：hash join不是万能的，它需要join_buffer_size足够大，否则会落盘。生产环境中如果发现hash join落盘，优先调大join_buffer_size，或者干脆改写SQL减少关联表的行数。

4.3 锁与隔离级别：并发写场景的执行原理

MySQL锁的分类网上文章一大把，但大多停留在名词解释。我从执行原理角度说说真正影响调优的点。InnoDB的行锁不是“锁住整行”，而是锁住索引记录。对，就是索引记录——如果你的SQL没有走索引，InnoDB为了锁住目标行，会在内部把所有扫描到的聚簇索引记录都加上锁。这有个特别坑的副作用：一个不带索引的UPDATE，可能锁住全表大量记录，虽然最终只修改几行，但其他事务的写操作全部被阻塞。

锁机制给调优的启示：第一，UPDATE和DELETE的WHERE条件必须走索引，这是并发控制的核心前提；第二，锁粒度与隔离级别相关，RC（读已提交）级别下间隙锁被禁用，MySQL默认的RR（可重复读）级别下，范围查询会加间隙锁，可能引发更大范围的锁等待。如果业务对幻读不敏感，把隔离级别从RR降为RC，锁竞争通常会显著下降。

我在高并发订单系统里，把数据库隔离级别从RR调整到RC，配合每个写语句走主键或唯一索引，锁等待从频繁告警降到了基本消失。注意这个修改需要业务确认能接受RC级别下的语义差异，不是无脑推荐。

4.4 事务执行链：redo log、undo log与刷盘策略

理解事务执行原理，对调优innodb_flush_log_at_trx_commit这类参数至关重要。一个简单的事务提交，数据变更会先写入内存中的缓冲池，同时生成redo log写到日志缓冲区。真正提交时，根据刷盘策略，redo log可能立即fsync到磁盘，也可能交给后台线程批量处理。

innodb_flush_log_at_trx_commit=1是默认值，代表每次事务提交都必须把redo log刷到磁盘。这个设置保证即使数据库崩溃也不会丢事务，但带来的代价是每次都要求一次磁盘fsync，在机械硬盘上性能极差。如果设置为0或2，性能显著提升，但可能丢失最近1秒内的事务。

生产环境如何选？金融类、强一致业务，我不建议动这个参数；但对允许秒级数据丢失的日志、统计类业务，可以设置成2，实测并发写入性能提升好几倍。这是交易一致性跟吞吐量的经典权衡，没有绝对正确，只有业务场景说了算。

undo log则是支撑MVCC多版本控制的关键。每条记录被更新时，旧版本会挂到undo链上，其他事务通过版本链读取旧快照。这解释了为什么长事务是个隐形杀手——事务不结束，undo log不能清理，版本链越来越长，读取旧快照的性能随之劣化，还可能导致undo表空间膨胀。经验规律：业务里的长事务跟慢SQL一样危险，一定得监控起来。

4.5 批量调优：从单条SQL到整体负载的执行视角

调优到一定阶段你会发现，单条SQL的问题往往好解决，难的是整体负载。执行原理层面的角度是，要关注三类资源竞争：CPU（排序、哈希、函数计算）、IO（读盘、刷盘、临时表落盘）、锁（行锁等待、元数据锁阻塞）。

批量任务比如报表、批量更新，我习惯在SQL层面做切分和节奏控制。一个千万级数据的UPDATE，如果一条SQL跑完，会持锁极长时间，导致主从延迟和业务阻塞。拆成每次1000条的小批量加sleep，看似慢了，实际整体稳定性和吞吐反而更好。这个思路的背后逻辑就是：执行器逐行更新、行锁逐条释放，大批量SQL会造成锁资源长期占用，而小批量把锁竞争切割成许多短周期，让其他事务有机会插进来。

另外，批量导入时关注innodb_buffer_pool_size的命中率。目标是把热数据尽量留在内存，避免每读一行都触发磁盘IO。导入前临时调大缓冲池、导入完成后恢复，是运营大促场景的常规操作。

5. 执行计划诊断实战：EXPLAIN的正确食用方式

5.1 慢查询日志配置：先定位再调优

调优的第一步永远是定位问题SQL，而不是盲目改参数。MySQL的慢查询日志就是干这个的。常见配置：

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太多，可以先设成2秒，逐步收紧。log_queries_not_using_indexes我建议打开，这个配置能让你找到那些“没走索引但跑得不算慢”的潜在炸弹。

分析慢日志时，除了看语句本身，还要注意Rows_examined和Rows_sent的对比。如果Rows_examined是几十万，Rows_sent只有几十，说明查询扫描了大量行却没用到多少，这是索引设计失效的强信号。

5.2 EXPLAIN关键列：type、key、rows、Extra该怎么看

EXPLAIN是每个调优的人必须精通的工具。我按优先级讲几个最关键的字段。

type列是访问类型。从好到差大致是：system>const>eq_ref>ref>range>index>ALL。其中一旦出现ALL，就说明做了全表扫描，要么是SQL没走索引，要么是优化器判定走索引更贵。index代表扫描了整棵索引树，比ALL稍好，但也不理想。生产环境的核心查询，至少要达到range级别。

key列显示实际用到的索引。注意这里要看实际选择而非可能选择，有时候possible_keys列有值，key列却是NULL，说明优化器把可选索引都否决了，这里要深挖为什么。

rows列是优化器估算的需要扫描的行数。这是跟实际执行耗时强相关的预估指标。我习惯把rows跟实际行数对比，如果差距数量级明显，优先考虑统计信息过期，执行ANALYZE TABLE。

Extra列最能体现执行细节。看到Using temporary要警觉，说明这条SQL用了临时表，常见于GROUP BY、DISTINCT、某些子查询，往往伴随性能问题。看到Using filesort前面说过了，额外排序。看到Using index是好消息，表示覆盖索引生效；Using index condition表示ICP生效；Using where表示存储引擎返回后又在服务器层过滤了部分行。

5.3 一个完整的EXPLAIN实战案例

说个真实案例。有个用户中心查询会员列表的接口，SQL简化如下：

SELECT id, name, phone, level FROM member WHERE create_time BETWEEN '2024-01-01' AND '2024-06-30' AND status = 1 ORDER BY id DESC LIMIT 20;

执行计划里type是ALL，rows估算25万行，Extra有Using where和Using filesort。显然，where条件里的create_time有索引，但优化器没走，因为status过滤后数据量还是大，它认为走create_time索引再回表排序的成本高于全表扫。但这个判断是错的——业务里大部分历史会员的status都已经是0，status=1的活跃会员在某个时间段内其实很少。

我给出的调整有两个。第一，建一个联合索引：

ALTER TABLE member ADD INDEX idx_status_time (status, create_time);

联合索引把等值条件status放在前面，范围条件create_time放后面，这样索引能精确定位status=1且时间范围内的数据，大大缩小扫描范围。第二，把ORDER BY id改成ORDER BY create_time DESC，或者干脆让联合索引里包含排序字段的顺序，避免filesort。

调整后执行计划type变成range，rows降到几百，Using filesort消失。接口从800ms降到30ms左右。这个案例的启示是：EXPLAIN里看到ALL和filesort时，不要急着想到加内存、扩机器，先想想索引结构是否能直接服务这个查询模式。

5.4 常见问题速查表

整理一个我在社群答疑时最常被问到的问题对照表，方便你直接对照排查：

这个表不能覆盖所有现场，但能覆盖我做技术支持这些年遇到的一半以上的调优问题。出问题时先对照，比无头苍蝇式重启好太多。

5.5 调优参数落地的优先级

最后一个实操建议，关于参数调优的优先级。很多人一上来就改几十个参数，结果根本不知道哪个生效了。我自己的落地顺序是：

1. 先改运行时状态，SET GLOBAL，观察效果，不重启，方便回滚。
2. 最重要的参数永远是innodb_buffer_pool_size，建议设为物理内存的60%-75%，这是InnoDB读写的核心缓存。
3. 其次是innodb_flush_log_at_trx_commit，根据业务对一致性的要求，从1往0或2降（如果允许）。
4. 然后是sort_buffer_size、join_buffer_size、read_buffer_size这类会话级缓冲，注意它们按连接分配，过高会导致内存消耗乘以连接数飙升，不能盲调。
5. 最后才是那些细枝末节的比如table_open_cache、max_connections之类，按监控数据精确调整。

参数调整查看效果的时间窗口，我一般建议至少观察一个业务高峰周期，不要改完立刻下结论。短时间的波动不能说明问题。

6. 执行原理指导下的几个实战调优案例

6.1 案例一：分页深翻页为什么越来越慢

刚工作时遇到一个分页SQL，第100页之后页面打开要好几秒。SQL长这样：

SELECT id, title, content FROM article WHERE author_id = 123 ORDER BY id DESC LIMIT 90000, 20;

执行原理角度分析：LIMIT 90000, 20，MySQL不是直接跳到第90000行取20行，而是把前面90000行全查出来，丢掉，再读20行。这个offset越大，扫描和丢弃的开销越大，线性增长。

解法不唯一，我用的是利用索引有序性做条件分页，记录上一页最后一条id，用WHERE id < 上页最后的id ORDER BY id DESC LIMIT 20。这个SQL直接通过主键定位，每次只要扫描20行。效果立竿见影，第1000页也是毫秒级。这是理解“LIMIT的执行本质是扫描加丢弃”带来的优化思路。

6.2 案例二：OR条件引发的全表扫描

有一次线上查询，SQL是：

SELECT * FROM orders WHERE user_id = 100 OR order_no = 'NO20240101';

user_id和order_no上有各自独立索引，但执行计划却是全表扫描。原因在于：优化器从两个索引分别取结果，然后做合并（index merge），这个操作的代价在MySQL 5.7的代价模型里被估算过高；或者两个索引的合并逻辑对优化器不友好，它直接放弃了。

我的改法是拆成两条SQL，用UNION连接：

SELECT * FROM orders WHERE user_id = 100 UNION SELECT * FROM orders WHERE order_no = 'NO20240101';

每条语句各自走自己的索引，再合并去重。这个改动让单次查询时间从几百毫秒降到十几毫秒。这个案例的深层价值是：当一条SQL的执行计划不对劲时，改用更简单清晰的SQL形态往往比跟优化器死磕更高效。

6.3 案例三：存储过程里的一句话更新走错索引

有一次维护一套老系统，一个存储过程里有一句UPDATE：

UPDATE account SET balance = balance - 100 WHERE account_no = input_no AND status = 1;

account_no有索引，但status没有。存储过程执行时，因为status条件加上了，优化器觉得使用account_no索引后还要去过滤status，不如全表扫（表小）。于是每个账户更新都全表扫描，累积下来把CPU打满。

处理方式：确认account_no本身就是唯一键，那status条件其实是多余的，直接去掉即可；如果account_no非唯一，那就建一个(account_no, status)联合索引。这也是一个典型教训：存储过程里的SQL，调优逻辑跟外部SQL完全一样，不要因为它在过程里就忽视执行计划分析。

6.4 案例四：临时表使用不当引发的Using temporary

有个统计需求，需要把两张表的数据合并后去重再聚合：

SELECT user_id, SUM(amount) FROM ( SELECT user_id, amount FROM order_2023 UNION ALL SELECT user_id, amount FROM order_2024 ) t GROUP BY user_id;

执行计划出现Using temporary，结果集大时直接落盘，跑了十几秒都没出结果。临时表是MySQL处理子查询、UNION、GROUP BY时常用的内部机制，当数据量超过tmp_table_size或max_heap_table_size时，内存临时表会转为磁盘临时表，性能断崖式下跌。

我的优化思路是：既然业务能接受近似结果，那就不需要全量聚合，可以在各个分表先做GROUP BY聚合，再合并：

SELECT user_id, SUM(total) FROM ( SELECT user_id, SUM(amount) AS total FROM order_2023 GROUP BY user_id UNION ALL SELECT user_id, SUM(amount) AS total FROM order_2024 GROUP BY user_id ) t GROUP BY user_id;

把聚合操作下推到每张表，底层扫描的数据量不变，但传输到临时表的数据量大幅减少，临时表压力骤降，查询从十几秒降到两秒左右。

7. 我对MySQL调优这件事的几个体会

写了这么多，最后说点个人体会。MySQL调优这事儿，工具和参数永远是表层的，最核心的还是你怎么理解“一条SQL从头到尾怎么被处理”这件事。你把执行原理吃透了，看到慢SQL脑子里自动会形成一个分层的排查框架——是连接层卡了，是优化器选错了，是引擎层扫了太多行，还是排序临时表压垮了内存？这个框架比任何参数大全都有价值。

我自己的习惯是，每逢线上有SQL性能问题，不管多急，一定会先花两分钟把EXPLAIN跑一遍再动手。很多新同事一上来就调buffer pool、刷参数，结果发现SQL本身写得不合理，根因没解决，参数倒是先乱了。我也见过不少看似无解的问题，最后就是一条索引的事。这不是玄学，是执行原理直接告诉你的答案。

最后再分享一个习惯：给自己维护的数据库建立一个“SQL性能基线”。记录核心接口对应的SQL，在正常负载下的耗时、扫描行数、执行计划。一旦发现偏离，对比基线就能快速定位是数据量变化、统计信息过期还是代码改动导致的。这个习惯帮我省了无数次排查事故的时间。调优没有银弹，但一套科学的方法论加扎实的执行原理功底，足以让你少踩掉大部分坑。