晚上十一点半线上订单库的 CPU 突然飙到 80%一条 select 把整张订单表从第一个数据页一路啃到最后一个数据页慢查询日志里红得刺眼。这种场面我接过不少每次排查到最后原因往往不在 SQL 语法上而在于没把这条 SQL 的生命周期给看透。MySQL 里一次全表扫描并不是“扫全表”三个字就能概括的它从触发到执行再到收尾中间要过优化器、存储引擎、Buffer Pool、排序临时文件好几道关任何一道环节出了问题结果都是大量无用 IO 和 CPU 空转。今天这篇就把全表扫描的四段生命周期——触发、决策、执行、收尾——一节一节切开讲清楚每一步 MySQL 内部到底在做什么、为什么这么做、哪些环节最容易翻车。适合刚接触 MySQL 性能调优的开发者、被慢查询反复折磨的运维以及所有想知道“一条 select 到底经历了什么”的人。1. 哪些 SQL 会把 MySQL 逼成全表扫描六大高发场景全表扫描不是随机发生的绝大多数时候是优化器在“没得选”或者“不想选”的情况下做出的决定。我整理了自己在生产环境里见过的高发场景按出现频率排个序你对照自己的慢查询日志看基本能对上。1.1 条件列没有索引最常见也最直白这是最基础的情况。where条件里的列既不在主键上也没有普通索引优化器想走索引都找不到路只能从聚簇索引的第一个叶子节点开始把整棵 B 树的所有叶子节点全量读一遍。-- user_phone 列没有索引 SELECT * FROM users WHERE user_phone 13800138000;这种 SQL 执行的时候InnoDB 不知道哪一页里有这个手机号只能把整张表的所有数据页都翻一遍期间Handler_read_rnd_next这个状态变量会飙升它统计的就是“随机读下一页”的次数在全表扫描场景下基本等于表里的行数。1.2 隐式类型转换导致索引失效这个是我见过最冤枉的坑。表的字段是 varchar但你传入的参数是数字MySQL 会把字段列本身转成数字再比较函数一作用在列上索引就废了。-- mobile 列是 varchar(20)索引建得好好的 SELECT * FROM users WHERE mobile 13912345678;MySQL 的隐式转换规则里字符串和数字比较时会把字符串转成浮点数相当于执行了CAST(mobile AS DOUBLE)。一旦索引列被函数包裹优化器就知道这个索引没法用了老老实实全表扫。想要验证很简单EXPLAIN一眼就能看出来type 从ref变成ALL同时extra里可能出现Using where。1.3 条件列上套了函数或计算这个比隐式转换更明显但也经常有人忽略SELECT * FROM orders WHERE DATE(created_at) 2024-06-01;created_at上就算有索引也用不上因为优化器无法对这个表达式做范围推导。标准的解法是改成范围查询created_at 2024-06-01 AND created_at 2024-06-02这样既保留索引起始点又能利用索引的范围扫描能力。1.4 LIKE 前置通配符%开头的模糊查询like abc%还能走索引因为前缀是确定的B 树可以根据前三个字符定位。但like %abc%没有任何前缀信息索引树的节点顺序帮不上忙优化器只能认为全表扫描更划算。1.5 优化器判断“索引不如全表扫”小表场景很多刚入门的同学看到EXPLAIN结果是ALL就慌其实不用。如果一张表只有 200 行InnoDB 读完整张表也就三五个数据页还是顺序读。这种情况下要走索引反而要多一次回表操作CPU 成本和随机 IO 成本全上去了优化器不傻它会果断选择全表。MySQL 的这个判断依据是成本模型8.0 里SERVER和ENGINE两套成本表都可以自定义默认配置下顺序读一个数据页的成本是 1.0随机读是 4.0memory_block_read_cost为 0.25。当表特别小而索引回表代价高时全表扫描的估算成本反而最低。1.6 OR 连接条件且其中一个分支无索引SELECT * FROM orders WHERE status 1 OR amount 10000;如果status有索引而amount没有优化器没法对两条分支分别走索引再合并结果因为amount 10000这个分支只能全表扫。最终 MySQL 会直接选择全表扫描整张表因为无论如何都要涉及全量判断。这种 SQL 改造思路是把OR拆成两个查询再UNION写起来麻烦但效果立竿见影。2. 优化器是怎么“拍板”选全表扫描的成本模型与估算逻辑很多人以为“全表扫描”是执行的时候临时决定的其实不是。真正拍板的是优化器它在解析完 SQL 之后、执行之前就用一套成本模型把所有执行方案都算了一遍然后挑一个“算下来最便宜”的。2.1 成本模型的两个关键角色IO 成本和 CPU 成本MySQL 8.0 里成本估算被拆成两部分。IO 成本指的是读取数据页的开销CPU 成本指的是每行数据做条件判断、投影操作时消耗的 CPU 周期。两张成本字典表server_cost和engine_cost存在mysql库下我一般会查一遍确认生产环境的成本参数是不是默认值SELECT * FROM mysql.engine_cost; SELECT * FROM mysql.server_cost;默认情况下InnoDB 里顺序读取一个数据页的成本是 1.0随机读取是 4.0而每处理一行数据的 CPU 成本是 0.1 左右。全表扫描走的是聚簇索引的叶子节点链本质上是顺序读所以它的 IO 成本约等于“总页数 × 1.0”这个数字在各种执行方案里通常是最稳定的。2.2 行数估算一切决策的地基却可能虚高全表扫描的成本估算依赖于一个关键输入表里大概有多少行、涉及多少个数据页。听起来简单实际很坑。InnoDB 对这些行数的统计来自统计采样information_schema.tables里的TABLE_ROWS是预估值不是精确值。它通过随机抽取几个 B 树索引页除以采样比例推算出来的误差在 10% 到 20% 很正常。更麻烦的是如果这张表的大多数行已经被删除但空间没被回收统计信息里记录的页面数量不会立刻减少优化器会认为这张表还有很多页要读从而在多个执行方案里把全表扫描估得更贵。反过来也有问题如果一张表的统计信息很久没更新实际上已经膨胀了几倍优化器却按老数据估算误以为全表扫描很便宜照样选全表。所以运维上有个习惯我一直保留对频繁批量删除、大批量导入的表定期执行ANALYZE TABLE刷新统计信息避免优化器拿着过期的“地图”做路线规划。2.3 eq_range_index_dive_limit一个参数如何影响索引选择MySQL 在估算“等值查询能命中多少行”时有两种方式。当where条件里的等值数量小于等于eq_range_index_dive_limit默认 200时优化器会实际去索引里“潜水”统计每个等值对应的记录数如果大于 200就改用索引的基数估算。这个细节对全表扫描的决策影响很大。曾经有同事在张大表上跑一条IN (几百个值)的查询优化器因为等值数量超过阈值改用基数估算把某个索引的选择性估得过高反倒选择了全表扫描其他条件列。排查时调大eq_range_index_dive_limit再重新跑执行计划立刻变成走索引。2.4 优化器追踪让决策过程无所遁形光知道结论不够我还想看看优化器到底是怎么算的这时候就用optimizer_trace。开启之后MySQL 会把优化器考虑过的每个方案、每一笔成本都记录成 JSONSET SESSION optimizer_traceenabledon; EXPLAIN SELECT * FROM orders WHERE status 1; SELECT * FROM information_schema.OPTIMIZER_TRACE\G输出里有rows_estimation和considered_execution_plans能看到“为什么选了全表扫描”的完整账本。有一次排查一条明明有条件却全表扫的 SQL就是这个 trace 告诉我优化器在该条件列的统计信息里看到“超过一半的行都满足这个条件”算下来走索引回表的成本比全表还高这才放下了心。3. InnoDB 执行扫描时到底在忙什么预读、快照读与锁的纠缠优化器拍板之后执行器开始调用 InnoDB 的接口正式干活。这一阶段是生命周期里最核心的一段也是 CPU 和 IO 压力真正起来的地方。很多人以为全表扫描就是把数据页挨个读一遍实际没这么简单。3.1 扫描起点聚簇索引叶子节点链InnoDB 的表本身是一棵以主键为 key 的 B 树叶子节点存整行数据。全表扫描就是从这棵树的最左叶子节点开始顺着叶子节点之间的双向链表一路往右读读到最右端再结束。如果你建的是普通索引情况会复杂一些。比如select * from t where name like %xxx%优化器如果选择扫普通索引的 B 树虽然每个叶子节点里存的不是整行而是索引列 主键值扫描的数据量更小但因为查的是*每条记录都要根据主键回聚簇索引取完整行——这个“回表”操作可能引起大量随机 IO。所以很多情况下优化器宁可直接扫聚簇索引省掉回表环节。3.2 预读机制MySQL 提前把“还没用到”的页搬进 Buffer Pool顺序扫描叶子节点时InnoDB 不会一个页一个页地读它有一个很聪明的预读机制。MySQL 8.0 里默认开启线性预读innodb_read_ahead_threshold默认是 56意思是如果 InnoDB 检测到正在顺序读取某个数据文件中的连续 56 个页就会异步发起额外 IO把后续的更多页提前加载到 Buffer Pool 里。这个机制让全表扫描的“顺序读”变得非常高效但也带来一个副作用Buffer Pool 会被扫描进来的页大量占用把业务经常访问的热点数据页挤出去。我见过一次性select *跑完把整个 Buffer Pool 的命中率从 99% 砸到 70% 的案例。所以大表扫描之后show status like Innodb_buffer_pool_read_hit_rate这个指标务必盯一眼掉得太快就该考虑给扫描任务加限流或者把 Buffer Pool 里的 LRU 链表按 young 和 old 区域的比例调一下。3.3 一致性快照读扫描过程中为什么看不到别的事务的修改全表扫描期间别的会话可能正在 insert、update、delete但扫描看到的却是一个“冻结在某一时刻”的数据视图。这靠的是 MVCC多版本并发控制和 undo log 的配合。当这条 select 开启事务并执行第一次读时InnoDB 会生成一个 ReadView里面记录了当前活跃事务的 ID。扫描过程中每读到一行数据InnoDB 会比较该行记录上的事务 ID 和 ReadView 里的快照信息如果该行的最新版本事务 ID 在 ReadView 的活跃事务列表里说明这行正在被别的事务修改InnoDB 会顺着 undo log 往前找到该行在快照时点的旧版本返回旧值如果修改事务已经提交而且提交时刻比快照更晚同样需要从 undo log 里取旧版本。这就是一个长期跑着的全表扫描为什么会导致 undo log 膨胀的原因——它一直占着旧版本其他事务提交后更新产生的 undo 信息不能及时清理undo tablespace越占越大甚至把磁盘塞满。扫描时间越长这个风险越大。3.4 扫描会不会锁住整张表锁粒度带来的错觉全表扫描不等于全表加锁。默认的REPEATABLE READ隔离级别下这条 select 走的是一致性非锁定读它通过 MVCC 读快照不需要对扫描过的行加共享锁。所以你跑一条大扫描并不能阻止别人更新数据。但如果这条 select 写成了select ... for update或者lock in share mode事情就不一样了。InnoDB 会在扫描过程中对每一行加锁而且在REPEATABLE READ下为了避免幻读它还会对扫描范围内的间隙加 gap lock导致区间内其他事务的插入被阻塞。更糟的是MySQL 的加锁行为是按扫描过程逐行锁定的扫描到哪一行锁就加到哪一行并不是一次性锁全表但最终效果上其他事务往这张表插入数据的动作会大面积受阻。这就是为什么我在生产环境特别忌讳对线上大表直接执行select * from ... for update去“导出数据”看起来只读实际会对后续写入造成强烈的锁竞争。4. 扫描完成后数据去了哪里临时表、排序与结果返回全表扫描把行捞出来之后生命周期并没有结束。如果需要排序、去重、分组MySQL 还得在内存或磁盘上做二次加工然后才通过 MySQL 协议把结果集推送给客户端。这一段的损耗往往被低估。4.1 排序的三层路径内存排序到磁盘归并order by是全表扫描伴侣。扫描出来的数据往往不是最终顺序MySQL 需要排序。排序优先使用sort_buffer_size默认 256KB这块内存。如果待排序数据量不大直接在内存里做快速排序一条 SQL 就跑完了。一旦数据量超过 sort buffer 的容量MySQL 就把数据分块每排好一块就写到磁盘临时文件最后再把多个有序分块做归并排序。归并过程会额外读写临时文件IO 影响比想象中大得多。遇到大结果集的order by我习惯观察SHOW STATUS LIKE Sort_merge_passes这个值如果大于 20说明排序大量走了磁盘临时文件光排序这一项就把 SQL 拖垮了。4.2 Filesort 还是索引排序优化器怎么选如果order by的字段正好是索引列InnoDB 扫描索引本身就是有序的根本不需要额外排序这就是Using index优化。但如果排序字段不在索引上或者order by和where条件用了不同的索引优化器只能在扫描完之后再做 filesort。这里有个典型的决策场景where status 1 order by created_at。如果 status 选择性差走 status 索引扫描大量行再按 created_at 文件排序可能比直接全表扫描 文件排序更慢。优化器会把两条路径的成本都算一遍最终选便宜的那个。从这个角度说某些“全表扫描 filesort”的执行计划其实是优化器在两害相权之后的理性选择。4.3 临时表分组和去重的隐形成本group by、distinct、union这类操作往往都会涉及临时表。老版本的 MySQL 会把临时表建在磁盘的 tmpdir 上磁盘临时表没有索引数据量一大查询只能反复全表扫临时表速度雪崩。MySQL 8.0 有个重要改进临时表统一使用 TempTable 引擎先在内存里维护内存占用超过temptable_max_ram默认 1GB以后才转磁盘。但这不意味着你可以无限制地在内存里跑大分组转磁盘依赖tmpdir所在文件系统的 IO 性能如果 tmpdir 和业务数据在同一块普通磁盘上竞争会非常明显。我一般会把 tmpdir 指向 tmpfs但前提是确认服务器内存足够别把系统挤爆。4.4 把结果发给客户端网络 IO 也可能是瓶颈全部加工完之后MySQL 通过协议把结果集的每一行发送给客户端这一阶段消耗的网络 IO 也是全表扫描生命周期的一部分。Net_send相关状态变量统计了发送等待时间如果客户端接收速度慢MySQL 的服务线程会一直挂着。这就是为什么我一直强调select *全表扫描加limit也不能盲目乐观。limit 10看起来只返回 10 行但如果前 10 行要等扫描到表尾才能确定——比如order by一个非索引字段——那前面所有数据还是得扫完算完limit只影响“发送多少行”不影响“内部扫描多少行”。5. 一次完整全表扫描的现场还原从 EXPLAIN 到状态变量把前面几段串起来我用一条典型 SQL 走一遍全流程。假设有一张 500 万行的订单表结构大致是这样CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT, status TINYINT, amount DECIMAL(10,2), created_at DATETIME, KEY idx_user_id (user_id) ) ENGINEInnoDB;业务端报了一个慢查询SELECT COUNT(*) FROM orders WHERE status 1;status 没有索引。我的排查链路是这样一步步展开的。第一步看执行计划EXPLAIN SELECT COUNT(*) FROM orders WHERE status 1;结果里type ALL、rows 520万左右、extra是Using where。这说明优化器选择了全表扫描扫描过程中对每一行做 status 条件过滤只计数符合条件的数据。第二步查实际执行代价。通过 optimizer trace 能看到全表扫描的成本组成500 万行数据大约占了 8 万个数据页IO 成本 80000 × 1.0CPU 成本 500万 × 0.1全表扫描总成本估算比“扫 idx_user_id 索引再回表”低了几个量级因为 status 条件没有可用的二级索引。这就彻底解释了优化器的选择它没有“犯错”是结构上就没有优化空间。第三步观测执行期的状态变化。开一个会话不断采样SHOW GLOBAL STATUS LIKE Innodb_pages_read; SHOW GLOBAL STATUS LIKE Handler_read_rnd_next; SHOW GLOBAL STATUS LIKE Select_scan;运行期间Handler_read_rnd_next从几万涨到几百万Innodb_pages_read也在持续攀升说明确实在逐页读数据。SQL 跑完后这几个数字的增长量基本可以估算出本次扫描的页数与行数分析慢查询时很有用。第四步看收尾阶段的状态。如果 SQL 里带排序或分组重点关注Sort_merge_passes、Created_tmp_disk_tables这些计数。这条COUNT(*)没有排序需求所以这些指标没有变化说明它的生命周期止步于“扫描 过滤计数”没有再往下游流转。整个还原下来这条 SQL 的瓶颈清晰可见status没有索引优化器只能全表。要解决它方案是在status上建索引让优化器可以走二级索引扫描。但注意如果 status 分布很集中比如 99% 的行都是 status 1优化器建了索引也可能仍然选全表因为扫二级索引再回表取数据确实没有全表顺序读划算。到时候你EXPLAIN看到的还是一个ALL别慌这是正常行为加索引的意义在于等值查询能精准定位到那一小撮不同的值。6. 常见问题速查这几种“全表扫描”根本不用管排查多了之后你会发现并不是所有全表扫描都需要治理。区分“该治”和“不该治”的边界比见一个杀一个重要得多。6.1 明确不该治理的三种情况第一单表数据量只有几千行且查询条件返回结果集很大。比如一张配置表本来就 300 行全表扫描的成本和走索引回表的成本差距可以忽略甚至全表扫描更优。这种情况下EXPLAIN里的ALL不值得花时间。第二OLAP 场景里的统计报表比如每天凌晨执行的汇总查询本来就要扫描几百万行做聚合。建索引对这类查询没有意义它的核心诉求是让扫描尽量顺、尽量少占用业务高峰期资源。第三select count(*) from t这类无过滤条件的计数查询。InnoDB 8.0 仍然需要逐行数因为 MVCC 导致每一行对每个事务的可见性可能不同它不可能像 MyISAM 那样存一个计数器直接返回。所以别看不起它它扫描全表是生存需要不是优化器偷懒。6.2 全表扫描类慢查询的排查清单遇到真的需要治理的全表扫描我一般按这个顺序排查先确认条件列有没有索引show index from table一眼的事。再确认有没有隐式类型转换。用EXPLAIN看type和key再看表结构字段类型与传参类型是否一致。用optimizer trace确认优化器不是“被迫”选择全表扫描。如果是统计信息不准执行ANALYZE TABLE刷新。看扫描时有没有把 Buffer Pool 冲垮。扫描后抽查Innodb_buffer_pool_read_hit_rate如果明显低于 95%建议要么给扫描任务错峰要么考虑用备份库跑分析查询。看排序、分组、临时表环节是否额外放大损耗。Sort_merge_passes和Created_tmp_disk_tables是重点检查对象。6.3 一个容易忽略的坑全表扫描在 binlog 和主从复制下的后果全表扫描的 SQL 本身不改数据但它可能引发后续的数据变更操作放慢间接影响复制。前面讲过长事务占着旧版本的 undo log 不释放从库的复制线程如果也跑了一个长查询主库的 binlog 在从库执行时会因为锁等待而滞后主从延迟就是这么拖出来的。所以生产环境我有一条铁律大查询要么走只读从库或分析库要么在低峰期执行。线上主库的 Buffer Pool 和 undo 资源经不起长扫描反复蹂躏。6.4 工具与命令速查调试和复盘时这几条命令是我最常用的直接贴出来# 查看当前正在执行的 SQL重点看 Time 列 SELECT * FROM information_schema.processlist WHERE command ! Sleep; # 统计 SQL 语句维度的扫描行数和耗时 SELECT digest_text, sum_rows_examined, sum_rows_sent, count_star FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_rows_examined DESC LIMIT 20; # 全表扫描冲突的锁等待来源 SELECT * FROM sys.innodb_lock_waits;用 performance_schema 的 digest 表定位高频全表扫描比翻慢查询日志更快因为慢日志只记录超过阈值的个别语句而 digest 表能按模板聚合直接排出“最费行”的 TOP20 语句。7. 一点私货我对全表扫描的重新认识早几年我有一个很暴力的执念慢查询日志里出现ALL等于事故必须先加索引再说。后来看多了才发现全表扫描不只是“性能事故”它也是一种能力。MySQL 在数据量不大的场景下用顺序读高效地解决问题在无法用索引的情况下保证查询仍然能执行这套机制本身设计得并不差。真正值得注意的是那些因为“设计缺陷”而被迫全表扫描的查询——索引失效、统计信息过期、SQL 写法不规范。每一次这种扫描都是对存储引擎资源的一次无差别暴力读取短期看是某一条 SQL 慢了长期看它挤占 Buffer Pool、膨胀 undo log、拖累主从同步是系统性风险的积累。我个人这几年把排查慢 SQL 的习惯固定成了三层第一层看执行计划第二层看 optimizer trace 的账本第三层看状态变量和 performance_schema。每一条异常的全表扫描都会走一遍这三步找到它从触发到执行再到收尾的完整生命周期问题基本就能定死。我希望这套方法论对你也有用下次再被慢查询喊起来的时候至少你手上有一把解剖刀能像庖丁解牛那样顺着纹理下刀而不是一刀剁下去。
MySQL全表扫描生命周期解析：从触发到收尾的完整路径
晚上十一点半线上订单库的 CPU 突然飙到 80%一条 select 把整张订单表从第一个数据页一路啃到最后一个数据页慢查询日志里红得刺眼。这种场面我接过不少每次排查到最后原因往往不在 SQL 语法上而在于没把这条 SQL 的生命周期给看透。MySQL 里一次全表扫描并不是“扫全表”三个字就能概括的它从触发到执行再到收尾中间要过优化器、存储引擎、Buffer Pool、排序临时文件好几道关任何一道环节出了问题结果都是大量无用 IO 和 CPU 空转。今天这篇就把全表扫描的四段生命周期——触发、决策、执行、收尾——一节一节切开讲清楚每一步 MySQL 内部到底在做什么、为什么这么做、哪些环节最容易翻车。适合刚接触 MySQL 性能调优的开发者、被慢查询反复折磨的运维以及所有想知道“一条 select 到底经历了什么”的人。1. 哪些 SQL 会把 MySQL 逼成全表扫描六大高发场景全表扫描不是随机发生的绝大多数时候是优化器在“没得选”或者“不想选”的情况下做出的决定。我整理了自己在生产环境里见过的高发场景按出现频率排个序你对照自己的慢查询日志看基本能对上。1.1 条件列没有索引最常见也最直白这是最基础的情况。where条件里的列既不在主键上也没有普通索引优化器想走索引都找不到路只能从聚簇索引的第一个叶子节点开始把整棵 B 树的所有叶子节点全量读一遍。-- user_phone 列没有索引 SELECT * FROM users WHERE user_phone 13800138000;这种 SQL 执行的时候InnoDB 不知道哪一页里有这个手机号只能把整张表的所有数据页都翻一遍期间Handler_read_rnd_next这个状态变量会飙升它统计的就是“随机读下一页”的次数在全表扫描场景下基本等于表里的行数。1.2 隐式类型转换导致索引失效这个是我见过最冤枉的坑。表的字段是 varchar但你传入的参数是数字MySQL 会把字段列本身转成数字再比较函数一作用在列上索引就废了。-- mobile 列是 varchar(20)索引建得好好的 SELECT * FROM users WHERE mobile 13912345678;MySQL 的隐式转换规则里字符串和数字比较时会把字符串转成浮点数相当于执行了CAST(mobile AS DOUBLE)。一旦索引列被函数包裹优化器就知道这个索引没法用了老老实实全表扫。想要验证很简单EXPLAIN一眼就能看出来type 从ref变成ALL同时extra里可能出现Using where。1.3 条件列上套了函数或计算这个比隐式转换更明显但也经常有人忽略SELECT * FROM orders WHERE DATE(created_at) 2024-06-01;created_at上就算有索引也用不上因为优化器无法对这个表达式做范围推导。标准的解法是改成范围查询created_at 2024-06-01 AND created_at 2024-06-02这样既保留索引起始点又能利用索引的范围扫描能力。1.4 LIKE 前置通配符%开头的模糊查询like abc%还能走索引因为前缀是确定的B 树可以根据前三个字符定位。但like %abc%没有任何前缀信息索引树的节点顺序帮不上忙优化器只能认为全表扫描更划算。1.5 优化器判断“索引不如全表扫”小表场景很多刚入门的同学看到EXPLAIN结果是ALL就慌其实不用。如果一张表只有 200 行InnoDB 读完整张表也就三五个数据页还是顺序读。这种情况下要走索引反而要多一次回表操作CPU 成本和随机 IO 成本全上去了优化器不傻它会果断选择全表。MySQL 的这个判断依据是成本模型8.0 里SERVER和ENGINE两套成本表都可以自定义默认配置下顺序读一个数据页的成本是 1.0随机读是 4.0memory_block_read_cost为 0.25。当表特别小而索引回表代价高时全表扫描的估算成本反而最低。1.6 OR 连接条件且其中一个分支无索引SELECT * FROM orders WHERE status 1 OR amount 10000;如果status有索引而amount没有优化器没法对两条分支分别走索引再合并结果因为amount 10000这个分支只能全表扫。最终 MySQL 会直接选择全表扫描整张表因为无论如何都要涉及全量判断。这种 SQL 改造思路是把OR拆成两个查询再UNION写起来麻烦但效果立竿见影。2. 优化器是怎么“拍板”选全表扫描的成本模型与估算逻辑很多人以为“全表扫描”是执行的时候临时决定的其实不是。真正拍板的是优化器它在解析完 SQL 之后、执行之前就用一套成本模型把所有执行方案都算了一遍然后挑一个“算下来最便宜”的。2.1 成本模型的两个关键角色IO 成本和 CPU 成本MySQL 8.0 里成本估算被拆成两部分。IO 成本指的是读取数据页的开销CPU 成本指的是每行数据做条件判断、投影操作时消耗的 CPU 周期。两张成本字典表server_cost和engine_cost存在mysql库下我一般会查一遍确认生产环境的成本参数是不是默认值SELECT * FROM mysql.engine_cost; SELECT * FROM mysql.server_cost;默认情况下InnoDB 里顺序读取一个数据页的成本是 1.0随机读取是 4.0而每处理一行数据的 CPU 成本是 0.1 左右。全表扫描走的是聚簇索引的叶子节点链本质上是顺序读所以它的 IO 成本约等于“总页数 × 1.0”这个数字在各种执行方案里通常是最稳定的。2.2 行数估算一切决策的地基却可能虚高全表扫描的成本估算依赖于一个关键输入表里大概有多少行、涉及多少个数据页。听起来简单实际很坑。InnoDB 对这些行数的统计来自统计采样information_schema.tables里的TABLE_ROWS是预估值不是精确值。它通过随机抽取几个 B 树索引页除以采样比例推算出来的误差在 10% 到 20% 很正常。更麻烦的是如果这张表的大多数行已经被删除但空间没被回收统计信息里记录的页面数量不会立刻减少优化器会认为这张表还有很多页要读从而在多个执行方案里把全表扫描估得更贵。反过来也有问题如果一张表的统计信息很久没更新实际上已经膨胀了几倍优化器却按老数据估算误以为全表扫描很便宜照样选全表。所以运维上有个习惯我一直保留对频繁批量删除、大批量导入的表定期执行ANALYZE TABLE刷新统计信息避免优化器拿着过期的“地图”做路线规划。2.3 eq_range_index_dive_limit一个参数如何影响索引选择MySQL 在估算“等值查询能命中多少行”时有两种方式。当where条件里的等值数量小于等于eq_range_index_dive_limit默认 200时优化器会实际去索引里“潜水”统计每个等值对应的记录数如果大于 200就改用索引的基数估算。这个细节对全表扫描的决策影响很大。曾经有同事在张大表上跑一条IN (几百个值)的查询优化器因为等值数量超过阈值改用基数估算把某个索引的选择性估得过高反倒选择了全表扫描其他条件列。排查时调大eq_range_index_dive_limit再重新跑执行计划立刻变成走索引。2.4 优化器追踪让决策过程无所遁形光知道结论不够我还想看看优化器到底是怎么算的这时候就用optimizer_trace。开启之后MySQL 会把优化器考虑过的每个方案、每一笔成本都记录成 JSONSET SESSION optimizer_traceenabledon; EXPLAIN SELECT * FROM orders WHERE status 1; SELECT * FROM information_schema.OPTIMIZER_TRACE\G输出里有rows_estimation和considered_execution_plans能看到“为什么选了全表扫描”的完整账本。有一次排查一条明明有条件却全表扫的 SQL就是这个 trace 告诉我优化器在该条件列的统计信息里看到“超过一半的行都满足这个条件”算下来走索引回表的成本比全表还高这才放下了心。3. InnoDB 执行扫描时到底在忙什么预读、快照读与锁的纠缠优化器拍板之后执行器开始调用 InnoDB 的接口正式干活。这一阶段是生命周期里最核心的一段也是 CPU 和 IO 压力真正起来的地方。很多人以为全表扫描就是把数据页挨个读一遍实际没这么简单。3.1 扫描起点聚簇索引叶子节点链InnoDB 的表本身是一棵以主键为 key 的 B 树叶子节点存整行数据。全表扫描就是从这棵树的最左叶子节点开始顺着叶子节点之间的双向链表一路往右读读到最右端再结束。如果你建的是普通索引情况会复杂一些。比如select * from t where name like %xxx%优化器如果选择扫普通索引的 B 树虽然每个叶子节点里存的不是整行而是索引列 主键值扫描的数据量更小但因为查的是*每条记录都要根据主键回聚簇索引取完整行——这个“回表”操作可能引起大量随机 IO。所以很多情况下优化器宁可直接扫聚簇索引省掉回表环节。3.2 预读机制MySQL 提前把“还没用到”的页搬进 Buffer Pool顺序扫描叶子节点时InnoDB 不会一个页一个页地读它有一个很聪明的预读机制。MySQL 8.0 里默认开启线性预读innodb_read_ahead_threshold默认是 56意思是如果 InnoDB 检测到正在顺序读取某个数据文件中的连续 56 个页就会异步发起额外 IO把后续的更多页提前加载到 Buffer Pool 里。这个机制让全表扫描的“顺序读”变得非常高效但也带来一个副作用Buffer Pool 会被扫描进来的页大量占用把业务经常访问的热点数据页挤出去。我见过一次性select *跑完把整个 Buffer Pool 的命中率从 99% 砸到 70% 的案例。所以大表扫描之后show status like Innodb_buffer_pool_read_hit_rate这个指标务必盯一眼掉得太快就该考虑给扫描任务加限流或者把 Buffer Pool 里的 LRU 链表按 young 和 old 区域的比例调一下。3.3 一致性快照读扫描过程中为什么看不到别的事务的修改全表扫描期间别的会话可能正在 insert、update、delete但扫描看到的却是一个“冻结在某一时刻”的数据视图。这靠的是 MVCC多版本并发控制和 undo log 的配合。当这条 select 开启事务并执行第一次读时InnoDB 会生成一个 ReadView里面记录了当前活跃事务的 ID。扫描过程中每读到一行数据InnoDB 会比较该行记录上的事务 ID 和 ReadView 里的快照信息如果该行的最新版本事务 ID 在 ReadView 的活跃事务列表里说明这行正在被别的事务修改InnoDB 会顺着 undo log 往前找到该行在快照时点的旧版本返回旧值如果修改事务已经提交而且提交时刻比快照更晚同样需要从 undo log 里取旧版本。这就是一个长期跑着的全表扫描为什么会导致 undo log 膨胀的原因——它一直占着旧版本其他事务提交后更新产生的 undo 信息不能及时清理undo tablespace越占越大甚至把磁盘塞满。扫描时间越长这个风险越大。3.4 扫描会不会锁住整张表锁粒度带来的错觉全表扫描不等于全表加锁。默认的REPEATABLE READ隔离级别下这条 select 走的是一致性非锁定读它通过 MVCC 读快照不需要对扫描过的行加共享锁。所以你跑一条大扫描并不能阻止别人更新数据。但如果这条 select 写成了select ... for update或者lock in share mode事情就不一样了。InnoDB 会在扫描过程中对每一行加锁而且在REPEATABLE READ下为了避免幻读它还会对扫描范围内的间隙加 gap lock导致区间内其他事务的插入被阻塞。更糟的是MySQL 的加锁行为是按扫描过程逐行锁定的扫描到哪一行锁就加到哪一行并不是一次性锁全表但最终效果上其他事务往这张表插入数据的动作会大面积受阻。这就是为什么我在生产环境特别忌讳对线上大表直接执行select * from ... for update去“导出数据”看起来只读实际会对后续写入造成强烈的锁竞争。4. 扫描完成后数据去了哪里临时表、排序与结果返回全表扫描把行捞出来之后生命周期并没有结束。如果需要排序、去重、分组MySQL 还得在内存或磁盘上做二次加工然后才通过 MySQL 协议把结果集推送给客户端。这一段的损耗往往被低估。4.1 排序的三层路径内存排序到磁盘归并order by是全表扫描伴侣。扫描出来的数据往往不是最终顺序MySQL 需要排序。排序优先使用sort_buffer_size默认 256KB这块内存。如果待排序数据量不大直接在内存里做快速排序一条 SQL 就跑完了。一旦数据量超过 sort buffer 的容量MySQL 就把数据分块每排好一块就写到磁盘临时文件最后再把多个有序分块做归并排序。归并过程会额外读写临时文件IO 影响比想象中大得多。遇到大结果集的order by我习惯观察SHOW STATUS LIKE Sort_merge_passes这个值如果大于 20说明排序大量走了磁盘临时文件光排序这一项就把 SQL 拖垮了。4.2 Filesort 还是索引排序优化器怎么选如果order by的字段正好是索引列InnoDB 扫描索引本身就是有序的根本不需要额外排序这就是Using index优化。但如果排序字段不在索引上或者order by和where条件用了不同的索引优化器只能在扫描完之后再做 filesort。这里有个典型的决策场景where status 1 order by created_at。如果 status 选择性差走 status 索引扫描大量行再按 created_at 文件排序可能比直接全表扫描 文件排序更慢。优化器会把两条路径的成本都算一遍最终选便宜的那个。从这个角度说某些“全表扫描 filesort”的执行计划其实是优化器在两害相权之后的理性选择。4.3 临时表分组和去重的隐形成本group by、distinct、union这类操作往往都会涉及临时表。老版本的 MySQL 会把临时表建在磁盘的 tmpdir 上磁盘临时表没有索引数据量一大查询只能反复全表扫临时表速度雪崩。MySQL 8.0 有个重要改进临时表统一使用 TempTable 引擎先在内存里维护内存占用超过temptable_max_ram默认 1GB以后才转磁盘。但这不意味着你可以无限制地在内存里跑大分组转磁盘依赖tmpdir所在文件系统的 IO 性能如果 tmpdir 和业务数据在同一块普通磁盘上竞争会非常明显。我一般会把 tmpdir 指向 tmpfs但前提是确认服务器内存足够别把系统挤爆。4.4 把结果发给客户端网络 IO 也可能是瓶颈全部加工完之后MySQL 通过协议把结果集的每一行发送给客户端这一阶段消耗的网络 IO 也是全表扫描生命周期的一部分。Net_send相关状态变量统计了发送等待时间如果客户端接收速度慢MySQL 的服务线程会一直挂着。这就是为什么我一直强调select *全表扫描加limit也不能盲目乐观。limit 10看起来只返回 10 行但如果前 10 行要等扫描到表尾才能确定——比如order by一个非索引字段——那前面所有数据还是得扫完算完limit只影响“发送多少行”不影响“内部扫描多少行”。5. 一次完整全表扫描的现场还原从 EXPLAIN 到状态变量把前面几段串起来我用一条典型 SQL 走一遍全流程。假设有一张 500 万行的订单表结构大致是这样CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id INT, status TINYINT, amount DECIMAL(10,2), created_at DATETIME, KEY idx_user_id (user_id) ) ENGINEInnoDB;业务端报了一个慢查询SELECT COUNT(*) FROM orders WHERE status 1;status 没有索引。我的排查链路是这样一步步展开的。第一步看执行计划EXPLAIN SELECT COUNT(*) FROM orders WHERE status 1;结果里type ALL、rows 520万左右、extra是Using where。这说明优化器选择了全表扫描扫描过程中对每一行做 status 条件过滤只计数符合条件的数据。第二步查实际执行代价。通过 optimizer trace 能看到全表扫描的成本组成500 万行数据大约占了 8 万个数据页IO 成本 80000 × 1.0CPU 成本 500万 × 0.1全表扫描总成本估算比“扫 idx_user_id 索引再回表”低了几个量级因为 status 条件没有可用的二级索引。这就彻底解释了优化器的选择它没有“犯错”是结构上就没有优化空间。第三步观测执行期的状态变化。开一个会话不断采样SHOW GLOBAL STATUS LIKE Innodb_pages_read; SHOW GLOBAL STATUS LIKE Handler_read_rnd_next; SHOW GLOBAL STATUS LIKE Select_scan;运行期间Handler_read_rnd_next从几万涨到几百万Innodb_pages_read也在持续攀升说明确实在逐页读数据。SQL 跑完后这几个数字的增长量基本可以估算出本次扫描的页数与行数分析慢查询时很有用。第四步看收尾阶段的状态。如果 SQL 里带排序或分组重点关注Sort_merge_passes、Created_tmp_disk_tables这些计数。这条COUNT(*)没有排序需求所以这些指标没有变化说明它的生命周期止步于“扫描 过滤计数”没有再往下游流转。整个还原下来这条 SQL 的瓶颈清晰可见status没有索引优化器只能全表。要解决它方案是在status上建索引让优化器可以走二级索引扫描。但注意如果 status 分布很集中比如 99% 的行都是 status 1优化器建了索引也可能仍然选全表因为扫二级索引再回表取数据确实没有全表顺序读划算。到时候你EXPLAIN看到的还是一个ALL别慌这是正常行为加索引的意义在于等值查询能精准定位到那一小撮不同的值。6. 常见问题速查这几种“全表扫描”根本不用管排查多了之后你会发现并不是所有全表扫描都需要治理。区分“该治”和“不该治”的边界比见一个杀一个重要得多。6.1 明确不该治理的三种情况第一单表数据量只有几千行且查询条件返回结果集很大。比如一张配置表本来就 300 行全表扫描的成本和走索引回表的成本差距可以忽略甚至全表扫描更优。这种情况下EXPLAIN里的ALL不值得花时间。第二OLAP 场景里的统计报表比如每天凌晨执行的汇总查询本来就要扫描几百万行做聚合。建索引对这类查询没有意义它的核心诉求是让扫描尽量顺、尽量少占用业务高峰期资源。第三select count(*) from t这类无过滤条件的计数查询。InnoDB 8.0 仍然需要逐行数因为 MVCC 导致每一行对每个事务的可见性可能不同它不可能像 MyISAM 那样存一个计数器直接返回。所以别看不起它它扫描全表是生存需要不是优化器偷懒。6.2 全表扫描类慢查询的排查清单遇到真的需要治理的全表扫描我一般按这个顺序排查先确认条件列有没有索引show index from table一眼的事。再确认有没有隐式类型转换。用EXPLAIN看type和key再看表结构字段类型与传参类型是否一致。用optimizer trace确认优化器不是“被迫”选择全表扫描。如果是统计信息不准执行ANALYZE TABLE刷新。看扫描时有没有把 Buffer Pool 冲垮。扫描后抽查Innodb_buffer_pool_read_hit_rate如果明显低于 95%建议要么给扫描任务错峰要么考虑用备份库跑分析查询。看排序、分组、临时表环节是否额外放大损耗。Sort_merge_passes和Created_tmp_disk_tables是重点检查对象。6.3 一个容易忽略的坑全表扫描在 binlog 和主从复制下的后果全表扫描的 SQL 本身不改数据但它可能引发后续的数据变更操作放慢间接影响复制。前面讲过长事务占着旧版本的 undo log 不释放从库的复制线程如果也跑了一个长查询主库的 binlog 在从库执行时会因为锁等待而滞后主从延迟就是这么拖出来的。所以生产环境我有一条铁律大查询要么走只读从库或分析库要么在低峰期执行。线上主库的 Buffer Pool 和 undo 资源经不起长扫描反复蹂躏。6.4 工具与命令速查调试和复盘时这几条命令是我最常用的直接贴出来# 查看当前正在执行的 SQL重点看 Time 列 SELECT * FROM information_schema.processlist WHERE command ! Sleep; # 统计 SQL 语句维度的扫描行数和耗时 SELECT digest_text, sum_rows_examined, sum_rows_sent, count_star FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_rows_examined DESC LIMIT 20; # 全表扫描冲突的锁等待来源 SELECT * FROM sys.innodb_lock_waits;用 performance_schema 的 digest 表定位高频全表扫描比翻慢查询日志更快因为慢日志只记录超过阈值的个别语句而 digest 表能按模板聚合直接排出“最费行”的 TOP20 语句。7. 一点私货我对全表扫描的重新认识早几年我有一个很暴力的执念慢查询日志里出现ALL等于事故必须先加索引再说。后来看多了才发现全表扫描不只是“性能事故”它也是一种能力。MySQL 在数据量不大的场景下用顺序读高效地解决问题在无法用索引的情况下保证查询仍然能执行这套机制本身设计得并不差。真正值得注意的是那些因为“设计缺陷”而被迫全表扫描的查询——索引失效、统计信息过期、SQL 写法不规范。每一次这种扫描都是对存储引擎资源的一次无差别暴力读取短期看是某一条 SQL 慢了长期看它挤占 Buffer Pool、膨胀 undo log、拖累主从同步是系统性风险的积累。我个人这几年把排查慢 SQL 的习惯固定成了三层第一层看执行计划第二层看 optimizer trace 的账本第三层看状态变量和 performance_schema。每一条异常的全表扫描都会走一遍这三步找到它从触发到执行再到收尾的完整生命周期问题基本就能定死。我希望这套方法论对你也有用下次再被慢查询喊起来的时候至少你手上有一把解剖刀能像庖丁解牛那样顺着纹理下刀而不是一刀剁下去。