今天想聊一个看着很基础、实际上水很深的话题MySQL全表扫描的生命周期。很多人一说全表扫描就摇头觉得这是性能问题的代名词一看到 EXPLAIN 里 typeALL 就开始紧张。但真实生产环境里全表扫描有时候是优化器的理性选择有时候是索引失效的无奈后果还有时候是数据分布导致的最优解。我把一条SQL从发起、解析、优化、执行到返回的全过程完整拆开结合InnoDB的物理存储和锁机制讲清楚全表扫描到底在数据库内部经历了什么资源消耗在哪个环节爆炸又该怎么定位、怎么优化、怎么预防。这篇内容适合刚入门的开发也适合被慢查询折磨过的运维和DBA看完你能对慢这件事有更准确的判断。1. 先搞清楚什么才算全表扫描1.1 从执行计划看本质全表扫描的官方定义很简单扫描表中所有的数据页一行一行读出来在Server层逐行判断是否符合WHERE条件。在EXPLAIN里的标志就是type: ALL。但很多人容易忽略一个细节优化器看的是预估行数和成本不是真实行数。全表扫描意味着优化器认为直接读完整张表比走某个索引再回表的成本更低或者压根没有合适的索引可用。举个例子一张500万行的用户表SQL是SELECT * FROM users WHERE status 1。如果 status 上有个索引但status1的数据占到了全表的40%优化器一算走索引要查索引页再回表每一行都有一次随机IO成本反而比顺序读全表高。这种情况下typeALL其实是合理选择。如果把这类SQL当成故障处理盲目强制索引结果往往更惨。还有一个容易踩的误区全表扫描不只是扫聚簇索引主键索引这一种。对InnoDB来说每张表就是一个B树叶子节点存的是整行数据。没有可用索引时扫描的就是这个聚簇索引的所有叶子节点。所以说全表扫描本质是扫描主键索引的全部叶子节点而不是什么独立于索引之外的操作。这一认知很重要否则你在分析IO和缓冲池命中率时方向容易跑偏。1.2 为什么一提到全表扫描DBA就皱眉不是因为全表扫描本身有多可怕而是它带来的三个连锁效应第一是逻辑读放大一张表1个G的数据哪怕只查5行也要把相关数据页从磁盘捞出来第二是锁范围扩大扫描过程中符合条件的行会被加锁如果配合UPDATE或DELETE很容易锁住大量行引发阻塞和死锁第三是长事务风险全表扫描耗时越长事务持有资源的时间越长undo log 的保留时间也被拉长间接导致 history list 变长影响后面所有读操作的性能。我在实际工作中见过最典型的一个事故业务代码里有个定时任务每小时跑一次UPDATE orders SET status 2 WHERE order_no IS NULLorder_no 是允许为空的字段没有索引。第一年数据量小几百毫秒搞定第三年订单表到了千万级这个SQL每次要扫几百M的数据执行时间变成几十秒把主库的CPU和IO直接打满同库的其他业务查询全部遭殃。全表扫描不是不能出现而是不能被高频、热点、大量并发地触发。一旦出现它就变成一个放大器把原本微小的性能问题放大成系统级故障。2. 一条全表扫描SQL的完整生命周期拆解2.1 生命周期全视角从连接线程到数据返回为了描述方便我把全表扫描的生命周期分成六个阶段连接建立与SQL接收、语法语义解析、优化器成本决策、执行器与存储引擎交互、InnoDB层扫描与行过滤、Server层结果返回。这六个阶段里前两个阶段基本不产生明显的性能差异性能瓶颈主要出现在第三到第五阶段。下面每一段我都会说清楚什么时候发生、消耗什么、卡在哪里。要注意的是MySQL 8.0已经移除了查询缓存所以现在一条SELECT进来直接就进解析器不会再有命中缓存直接返回这个分支。很多人网上翻旧教程还能看到query cache相关配置实践时不要照着抄那套机制在8.0已经彻底废弃了。2.2 解析阶段全表扫描的起点并不特殊SQL进入MySQL后先由连接线程交给解析器。解析器做词法分析和语法分析生成解析树。MySQL 8.0还多了一步把解析树转换为内部表示形式并做权限检查。这个阶段的成本主要是CPU但占比通常不到一个查询总耗时的5%即使是大SQL也差不多。因此全表扫描的问题从来不出在解析阶段解析器也不会判断这个表有多大、要不要扫描。值得一提的是如果SQL写得太复杂比如几千行的JOIN和子查询嵌套解析器和后续的优化器确实要花费更多CPU时间。但在全表扫描场景里这属于次要矛盾真正的性能大头是存储引擎层的IO和执行器层的判断循环。2.3 优化器决策全表扫描是被算出来的这是生命周期里最有意思的一环。优化器基于表的统计信息行数、数据页数、索引基数等用成本模型估算各种执行计划的开销。全表扫描的成本主要由两部分组成IO成本和CPU成本。计算方式是全表扫描成本 ≈ 数据页数量 * 单页IO成本 行数 * 单行CPU成本。这里我补充一下关键参数在MySQL默认配置下mysql.server_cost和mysql.engine_cost两张成本表定义了各种操作的权重。通常单页IO成本约等于1.0单行CPU成本约等于0.2如果你没改过成本模型优化器就是这样算的。走索引的计划除了访问索引页还必须考虑回表次数。如果回表的行数占比很大随机IO成本会迅速超过顺序扫描优化器就会选择看起来笨的全表扫描。还有个容易被人忽略的因素统计信息不准确。innodb_stats_persistent开启时统计信息是持久化的不会每次查询实时统计。如果表数据大规模变化后没有执行 ANALYZE TABLE优化器拿到的行数是旧值可能做出明显错误的判断本来该走索引的结果选了全表扫描。遇到明明有索引EXPLAIN却显示ALL的情况先别急着骂优化器执行一下 ANALYZE TABLE 再说。2.4 执行器与InnoDB的交互每次一行绝不贪多优化器确定全表扫描后会生成对应的执行计划交给执行器。执行器和存储引擎之间通过handler接口交互。对于全表扫描执行器会循环调用存储引擎的ha_innobase::index_read或rnd_pos等接口一行一行取数据。注意这里的交互模式执行器每次获取一行就在Server层进行条件过滤where条件中不涉及索引列的部分、表达式计算、函数调用然后判断是否放进结果集。不是从引擎层加载一批数据再统一过滤而是拿一行、滤一行、放一行。这种传统模式的好处是内存占用小坏处是Server层和InnoDB层的函数调用次数非常多。一条500万行的全表扫描就有500万次行读取和过滤操作CPU消耗会被明显放大。MySQL 8.0引入了并行读特性但对于单条SELECT的全表扫描大部分场景仍然走传统逐行模式。你如果观察长查询的profile通常能看到User calls、Sending data占了大头其中Sending data这个状态就是执行器从存储引擎读取并处理行数据的阶段全表扫描的大部分时间都耗在这个状态里。2.5 InnoDB层扫描缓冲池、预读与MVCC在InnoDB层全表扫描真正做的事情是逐个读取聚簇索引的叶子节点数据页。每读一个页先看缓冲池Buffer Pool里有没有。没有就去磁盘加载。这里有个容易混淆的点全表扫描虽然逻辑上是一行行扫描但物理上是按页读取的每次至少读16KB。InnoDB还做了预读优化。顺序读取多个数据页时会触发线性预读把后面可能用到的页提前加载到缓冲池。所以全表扫描的物理读不一定是完全的随机IO大部分时候是顺序IO这也是为什么数据量不太大的全表扫描跑起来也没那么慢因为顺序读机械盘或SSD的速度都不低。MVCC对全表扫描也有显著影响。InnoDB默认隔离级别是REPEATABLE READ每个SELECT都会基于当前读视图read view判断行是否可见。对于 undo log 里的老版本数据判断链路会变长。如果一个长事务一直开着导致 purge 线程无法清理历史版本undo log 会越积越多全表扫描时每行的可见性判断都会变慢。反过来频繁的全表扫描也可能延长事务的活跃时间进一步加剧这个恶性循环。2.6 返回阶段结果集是如何从引擎到客户端的执行器过滤完符合条件的行后会先把结果集发送到服务端的一个临时缓冲区。如果数据量大超过net_buffer_length默认配置会分批发送到客户端。这个阶段的状态是Sending to client或Writing to net。很多开发对这个阶段有误解以为MySQL是将所有结果算完才一起返回。实际不是MySQL是边查边发的客户端每接收一批数据服务端才会继续读下一批。如果客户端迟迟不读取网络缓冲区服务端的线程状态会变成Sleep而实际上SQL还没跑完。这种现象在慢查询日志里会被记成执行时间很长但其实存储引擎早就扫描完了就是卡在网络传输上。排查全表扫描问题时如果发现查得快、传得慢要先检查客户端是不是在逐行处理结果集或者网络带宽是否被占满。3. 怎么精准定位一条正在执行的全表扫描3.1 先用 EXPLAIN 看三条核心信息定位全表扫描第一件事就是跑EXPLAIN。但不要光看 type重点看三样东西rows、filtered、Extra。rows是优化器预估扫描的行数filtered表示从这些行里过滤后剩余的比例Extra里如果看到Using where说明存储引擎返回的数据还需要在Server层过滤如果是全表扫描这几乎一定出现。我建议直接看EXPLAIN FORMATJSON版本的输出里面有个cost_info字段会明确写出read_cost、eval_cost和prefix_cost。这几个数值能帮你判断当前执行计划的总成本和备选索引的差距有多大。比如你看到一个全表扫描的 prefix_cost 是1000而某个索引方案的成本是900那说明优化器其实在边缘状态可能统计信息一刷新就换个计划。另外在MySQL 5.7及以上版本可以执行EXPLAIN ANALYZE它会真实执行SQL并返回每个算子的执行时间、扫描行数。对于全表扫描你能直接看到扫描了实际多少行、耗时多少这个数据比优化器的预估靠谱得多。注意生产环境大表慎用因为它真的会跑一遍。3.2 用 performance_schema 追踪会话与慢SQLEXPLAIN只能分析静态SQL。如果你的慢查询日志里已经有目标SQL了就可以用EXPLAIN。但如果问题还在线上实时发生你要靠performance_schema去抓正在跑的线程。核心语句是SELECT THREAD_ID, PROCESSLIST_ID, TIME, PROCESSLIST_INFO FROM performance_schema.threads WHERE PROCESSLIST_STATE Sending data AND PROCESSLIST_INFO LIKE SELECT%;状态为Sending data的线程大概率正在执行存储引擎取数循环是判断全表扫描是否在跑的典型信号。更进一步可以关联events_statements_current和events_stages_current两张表看到当前线程停留在哪个阶段比如stage/sql/reading from net还是stage/innodb/buffer pool load。如果生产库用的MySQL 5.7以上sys库里的sys.session也能直接看状态和当前SQL。比如SELECT * FROM sys.session WHERE state Sending data;简写但不失准确。3.3 从全局视角统计哪些SQL在频繁全表扫描单条SQL定位之后还需要做全局盘点。MySQL 自带的能力是events_statements_summary_by_digest聚合了归一化SQL的统计信息包含执行次数、总耗时、扫描行数、返回行数。通过对比扫描行数/返回行数这个比例能筛出严重的数据放大型查询。一条SQL扫描100万行只返回20行这个放大倍数就是5万倍是全表扫描里最需要优先优化的对象。SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, SUM_ROWS_EXAMINED / SUM_ROWS_SENT AS amplification FROM performance_schema.events_statements_summary_by_digest WHERE SUM_ROWS_SENT 0 ORDER BY amplification DESC LIMIT 20;这个SQL在真实环境里非常实用我建议每个MySQL实例有空都跑一遍你会惊讶地发现很多低频但超慢的SQL全都排在扫描放大比前列。3.4 监控指标组合拳除了查表实时监控指标也不能忽略。优先看四个指标缓冲池命中率Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests Innodb_buffer_pool_reads)、磁盘读速率Innodb_data_read的增长斜率、CPU使用率、活跃会话数。全表扫描大 SQL 执行时这几个指标会同时出现异常波动。特别是活跃会话数和CPU使用率同步攀升基本可以确定有语句在做大规模逐行扫描。这里给一个经验阈值如果一条SELECT扫描行数超过表总行数的25%并且返回行数少于总行数的5%无论如何都要列入优化清单不管它当时跑得有多快。数据量增长后这类SQL会从轻量慢查询直接变成生产事故。4. 全表扫描的代价到底贵在哪4.1 逻辑读、物理读和缓冲池的放大效应全表扫描最直接的代价不是每条SQL的执行时间而是它对共享资源的占用。逻辑读是CPU层面的操作物理读是磁盘IO层面的操作二者对数据库并发能力的影响完全不同。一条全表扫描假设表有400个数据页如果这些页全在缓冲池属于纯逻辑读400次逻辑读对CPU的消耗有限如果都在磁盘就是400次物理读假设一次物理读10ms单线程就要4秒还没算排队时间。缓冲池的作用是让热数据留在内存全表扫描会打破这个格局。扫描过的数据页会进入缓冲池的LRU链表如果表很大比如超过缓冲池的一半这些被扫描的冷数据会把真正的热数据挤出缓冲池。后果就是这条全表扫描本身跑了几秒但它导致的热数据被淘汰会影响后续所有查询缓冲池命中率大幅下降整体性能倒退。这就是为什么DBA对全表扫描这么敏感——它不是一次性的重活而是会污染整个实例的性能环境。4.2 锁范围与长事务风险在InnoDB默认的REPEATABLE READ隔离级别下普通的SELECT是快照读不加行锁。所以纯SELECT的全表扫描不会阻塞其他事务。但如果是SELECT ... FOR UPDATE或者UPDATE、DELETE带全表扫描情况就完全不同了。执行器每扫描到一行满足条件就会尝试加锁锁的粒度和数量可能非常大。我遇到过一个极端案例一条UPDATE语句没有走索引更新了一个600万行的表里几千行的数据。表面上只改几千行但优化器是全表扫描InnoDB在扫描过程中对每一行都要判断是否需要加锁。在RR隔离级别下为了防止幻读InnoDB会对扫描过的间隙加间隙锁gap lock导致大量不相关的插入操作被阻塞。这个案例最终的表现就是一条UPDATE把整个业务的写入都堵死了而非只堵了那几千行。如果确实无法避免全表扫描的写操作可以开启innodb_lock_wait_timeout缩短短锁等待时间但根本解法永远是让SQL走更多精确命中的索引缩小扫描范围。4.3 什么时候全表扫描反而是最优解说句公道话全表扫描不是原罪。以下三种场景全表扫描是理性选择硬加索引反而画蛇添足第一数据量很小。一张表只有几千行一个B树最多几十个页全表扫描的逻辑读次数和走索引的回表成本差不多甚至更低。第二查询要返回的结果占表的大部分比例比如查所有statusactive的行而active占了80%。这时候走索引至少需要一次回表成本高于直接全表扫描。第三统计类查询比如COUNT(*)、MAX(id)在特定条件下也需要扫描大量数据走了索引也可能只是把扫描从聚簇索引转移到二级索引无所谓优劣。所以一个成熟的做法不是禁用全表扫描而是识别和消除不必要的全表扫描。通过slow query log、性能监控、扫描放大比指标来定位真正有问题的SQL才是核心能力。5. 优化手段让该扫的扫得高效让不该扫的走索引5.1 索引选择的三条铁律防止不必要的全表扫描最有效的手段是设计好索引。下面三条铁律是我在实际工作中反复验证过的一是联合索引要符合最左前缀原则查询条件里如果用了联合索引的第一个字段索引生效的可能性就大。二是索引列不要做函数运算和隐式类型转换。比如WHERE phone 138xxxx而 phone 列是VARCHAR查询里用了整型MySQL会在比较时对列做类型转换索引直接失效。三是覆盖索引优先。如果查询只需要某几个字段把字段包含在联合索引中InnoDB可以直接从二级索引叶子节点拿到数据完全不需要回表扫描的成本比全表扫描低一个量级。5.2 延迟关联解决深翻页的全表扫描幻觉分页查询是另一个容易触发全表扫描的场景。ORDER BY create_time DESC LIMIT 10000, 20如果create_time上没有索引或者排序字段不在联合索引内优化器可能选择用filesort方式先把全表扫一遍排序后再offset掉前10000行。表现上这条SQL扫描了整张表的全部数据EXPLAIN里type可能显示ALLExtra里还有Using filesort。这类问题用延迟关联技术解决先在索引上拿到分页后的主键ID再通过主键ID回表取完整行。SQL写成下面这种形式SELECT * FROM orders JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 10000, 20) tmp ON orders.id tmp.id;内层查询只需要扫描联合索引(create_time, id)或者 id 上的索引数据量小很多外层再按20个主键去聚簇索引里精确查找效率立竿见影。5.3 统计信息更新与计划稳定性很多全表扫描问题根源在于统计信息滞后。特别是用了大批量导入或大片数据删除后表的统计信息没有刷新优化器还拿老的数据页数估算成本以为全表扫描很快。遇到这种情况执行ANALYZE TABLE即可。如果业务数据波动频繁考虑设置innodb_stats_auto_recalc为ON让MySQL在表数据变更超过10%时自动重新计算统计信息。但也要提醒一句不要频繁对线上大表执行 ANALYZE TABLE它本身也要扫描数据页在高峰期可能引发额外IO。更稳妥的做法是在业务低峰期集中处理。5.4 一些容易被忽略的参数调优会话级参数方面有一个技巧值得收藏max_seeks_for_key当优化器估算走索引需要扫描的索引页超过这个值时会更倾向于全表扫描。如果你明确某条SQL应该走索引但优化器没有可以临时把该值设得非常小比如1强制优化器改变判断。但这是权宜之计不建议全局设置生产环境最好还是通过改写SQL或调整索引来根治。Buffer Pool 的大小也会间接影响全表扫描的决策和实际速度。缓冲池过小全表扫描的物理读比例高执行时间更长缓冲池大一些扫描的数据页更容易命中内存慢查询的体感时间明显缩短。但也别指望靠缓冲池救全表扫描如果表有几十G再怎么调优物理读都无法避免。6. 实战复盘一次全表扫描引发的线上事故6.1 现场现象某个电商系统大促前一天主库突然出现大量慢查询活跃会话数从平时的20飙升到200CPU直接打满。监控面板上Innodb_data_reads和Innodb_buffer_pool_reads同时暴涨说明有大量冷页从磁盘读取。业务日志里开始出现数据库连接超时和死锁重试。急查SHOW PROCESSLIST看到多个SELECT处于Sending data状态涉及的SQL完全相同是在查询一张订单扩展表。6.2 排查链路我先从 processlist 里拿到两条完整SQL和线程ID再用 EXPLAIN 看执行计划发现typeALLrows580万ExtraUsing where。表上其实有索引但SQL里面在 where 条件中写了DATE_FORMAT(create_time, %Y-%m-%d) 2024-05-20这就是典型的函数包裹索引列索引失效。随后我查了events_statements_summary_by_digest确认这条SQL最近一天执行了30万次总耗时时长占整个实例的60%以上。每条单耗大概300ms看着不吓人但高并发下资源争用被急剧放大。再用EXPLAIN ANALYZE跑了一遍真实执行看到实际扫描了580万行返回34行扫描放大比接近17万倍。优化方向不言而喻。6.3 根因与解决方案根因是两层第一层是SQL写法问题DATE_FORMAT函数包裹了索引列导致无法使用create_time上的索引第二层是业务模式问题这个查询压测时数据量小没暴露到了数据量膨胀后被放大成了热点。解法分两步落地。第一步立即修复SQL函数从 column 挪到常量侧改成WHERE create_time 2024-05-20 00:00:00 AND create_time 2024-05-21 00:00:00。存量索引idx_create_time立即生效。第二步给(merchant_id, create_time)加联合索引支持商家维度的时间范围查询进一步减少扫描量。做好这两步后SQL执行时间从300ms降到3ms慢查询清空CPU回落。这个案例的教训是任何SQL上线前一定要用生产同量级的数据做EXPLAIN而不是只在小库上验证结果正确性。7. 常见问题速查为什么明明建了索引还是全表扫描7.1 索引失效的五种典型场景我把生产环境里最常碰到的索引在但执行计划不认索引的情况整理成速查表方便你直接对照场景典型SQL原因隐式类型转换WHERE phone 13800138000phone是VARCHAR列和常量类型不一致列被隐式转换函数包裹列WHERE DATE(create_time) 2024-05-20索引列参与函数运算前导模糊查询WHERE name LIKE %张%前缀不确定B树无法定位联合索引非最左WHERE b 1 AND c 2联合索引a,b,c缺少最左列aOR连接非索引列WHERE id 1 OR name 张三其中一个条件没法走索引整体计划退化遇到这五类SQL不要再去加索引先把SQL改成可优化的形式。改完SQL以后如果还是全表扫描再考虑别的因素。7.2 大表 COUNT(*) 为什么那么慢COUNT(*)在全表扫描里是个特殊存在。InnoDB不像MyISAM那样存储了表行数它必须通过扫描来计数。虽然MySQL 8.0对COUNT(*)做了一些内部优化比如优先扫描最小的二级索引而非聚簇索引但如果表上没有任何二级索引或者二级索引也不够小仍然要扫描大量数据页。优化思路是对于精确计数需求引入汇总表或Redis计数对于近似计数用SHOW TABLE STATUS里的rows字段虽然是估算值但很多报表场景够用对于按条件计数一定要保证条件列有索引同时尽量让条件划分成小范围扫描。7.3 如何用索引纵切减少无谓扫描防止全表扫描的最后一道防线是索引覆盖率。如果你的业务查询需要频繁访问某些行的部分字段设计一个覆盖这几个字段的联合索引执行计划即使还是扫二级索引但不需要回表扫描的数据量也从整行降为几个列。典型如用户列表页只展示nickname、avatar、status建(status, nickname, avatar)联合索引查询时从索引页直接取数据数据量比聚簇索引小好几倍行数相同但逻辑读少很多。这一点在很多开发同学的水平之上但确实是最容易忽视的优化方式。记住一句经验想要大幅降低全表扫描的实际代价优先考虑让扫描的列与查询需要的列完全重合也就是覆盖索引。7.4 全表扫描的有效生命周期该怎么理解这里我想额外解释一下热搜词里提到的有效生命周期在全表扫描语境下它有两个层面的意义。第一层是单条SQL从开始扫描到结束的时间窗口这个窗口内它占用CPU、内存、IO、锁资源第二层是优化器对某个执行计划信任的有效期依赖统计信息的时效性。统计信息过期了全表扫描这个计划的有效期就会被人为拉长原本应该走索引的查询也被带偏。所以做性能治理的时候我一般会把统计信息有效期当作一个主动管理项而不是被动等待。大表导入数据后低峰期定期ANALYZE例行巡检里加入对information_schema.tables中last_analyze_time的检查发现有超过一周没更新的活跃大表主动分析一下。这样可以把很多潜在全表扫描问题扼杀在摇篮里。8. 写在最后的实操心得这篇文章里讲的内容大部分不是只读文档能看来的是踩过坑、熬过夜、被业务方催过的那种经验。我最想表达的一点是全表扫描不是一个需要赶尽杀绝的东西你也不可能让所有查询都走索引真正要做的是建立一套判断体系知道哪些全表扫描可以接受哪些必须处理处理优先级怎么排。我在实际工作中习惯在每个MySQL实例上跑两个例行任务一是利用events_statements_summary_by_digest找出扫描放大比排名前20的SQL每周复盘一次二是关注慢查询日志里那些执行频率高、单次消耗中等的SQL这类SQL比偶尔出现的超慢查询更危险因为总量大、影响面广。另外再分享一个特别实用的小技巧如果你在排查阶段不确定某个查询是不是全表扫描可以直接用EXPLAIN FORMATJSON看cost_info比对一下prefix_cost和行数。如果优化器评估的行数远大于真实行数很可能是统计信息问题如果评估行数基本准确但还选了全表扫描那大概率是 SQL 写法或数据分布导致索引方案性价比低这时候要优先改写SQL而不是硬塞索引。希望这篇拆解能让你对 MySQL 全表扫描从害怕变成理解下次再遇到慢查询时能更快定位、更稳处理。