很多把 MySQL 当核心存储的团队都会撞上一个看上去很魔幻的故障线业务量越大写入越频繁查询反而越慢。最难受的是慢的不是某一条 SQL而是整张表的范围查询、分页查询甚至等值查询都开始飘。遇到这种情况DBA 的第一反应通常是看慢日志、加索引、调参数但在“大量写入 索引碎片”同时出现的场景里这些常规招数往往只能缓解一时。索引碎片不是玄学它是 InnoDB B 树在随机写入、频繁删除和页分裂共同作用下的必然产物。这篇文章直接围绕“mysql 大量写入导致索引性能下降”这个痛点展开讲清楚索引碎片怎么产生、怎么判断、怎么整理以及写入侧如何从根上减少碎片。如果你正在维护一张几百万甚至上亿行、每天都在持续写入的表下面的内容大概率用得上。1. 大量写入为什么会让索引性能变差1.1 页分裂是怎么把索引弄“虚”的理解碎片先得知道 InnoDB 是怎么存放索引的。日常见到的 MySQL 表默认就是 InnoDB 引擎所有数据都在一棵 B 树里。主键索引也叫聚簇索引的叶子节点按主键从小到大排列每个叶子节点本质上是一个 16KB 的数据页里面除了用户记录还有页头、页尾和各类槽位。当新记录插入时InnoDB 先找到它应该落在哪个页再判断页里是否还有空闲空间。如果页已经满了而新记录必须插入到这个页的中间或末尾就会触发页分裂申请一个新页把一部分记录挪过去重新维护叶子双向链表和上层索引项。这个过程看着不复杂问题是页分裂一旦频繁发生原先紧凑的叶子链会被打断大量相邻记录被拆到相隔很远的物理位置。更直观的影响是一次分裂会把一个原本满的页变成两个半满页页空间利用率直接往下掉。大量写入的影响在于如果每秒有成百上千条记录进入页分裂会从偶尔发生变成高频发生。每次分裂都要做额外 IO、额外写 redo log还留下很多“半空页”。等查询过来时沿叶子链表做范围扫描发现同一个范围内的数据覆盖的页数明显增加逻辑读和物理读双双上涨。本来一个页能装下 100 条记录碎片化之后可能要访问 10 个页才能读完同样的数据索引的性能自然开始塌方。还有一个容易被忽略的点页分裂是连锁式的。B 树是平衡树叶子节点分裂后如果上层的索引页也满了还要继续向上一层分裂极端情况下会让树高度增加。几千万行的表树高从 3 变成 4意味着每次等值查询都要多访问一个层级。碎片越严重页利用率越低单位空间的索引密度越低整棵 B 树会变得越来越“虚”。1.2 主键选错等于每天给碎片“施肥”页分裂的触发概率和插入的主键是否有序强相关。如果表用自增 id新记录基本是追加到叶子链表尾部。尾部页满了直接建新页续上绝大多数情况下不涉及中间页的重组旧页也不会因为中间挤入而频繁搬家。这也是 MySQL 官方推荐自增主键的根本原因。很多团队为了分布式全局唯一习惯拿 UUID、随机字符串当主键。这种主键毫无次序插入一条记录时目标页大概率落在已有数据中间。如果目标页满了InnoDB 就只能把后半部分记录搬到新页再把新记录插进去。更麻烦的是二级索引的叶子节点里每一行都冗余了一份主键值主键是 32 位 UUID 时每个二级索引行都平白多出几十字节索引体积膨胀缓存命中率下降随机写的特征会传染给所有二级索引。曾经处理过一张用户流水表日写入量几百万主键用了 UUID结果每个二级索引的大小都比实际数据大两三倍插入性能越来越差范围查询经常要扫大量半空页。后来把表改成自增主键业务订单号用另一个唯一索引约束整张表的写入和查询都稳了下来。所以大量写入场景下判断碎片风险第一步先看主键类型。自增 id 不一定适合所有场景但如果当前没有分库分表需求自增主键依然是最省事的选择。就算要用雪花 ID也要保证写入端生成的值整体递增避免时钟回拨和批量补数把乱序写带进来。1.3 删除和更新留下的“逻辑空洞”只谈插入并不全面。生产环境里的“大量写入”往往伴随大量删除和更新。每一行被 DELETE 时InnoDB 并不会立即把空间还给操作系统而是在记录上打删除标记真正的物理清理交给后台 purge 线程异步完成。这个过程中索引页里能看到不少空出的槽位这就是逻辑空洞。逻辑空洞对查询的影响比很多人想象中大。范围查询虽然依靠叶子链和页目录跳过空槽但如果连续多个页都是稀疏页实际访问的页数就会膨胀。比如按时间统计某段数据理想状态扫十几个页就够空洞多时可能要扫二三十页。另一个隐蔽问题是删除之后马上继续插入InnoDB 倾向于优先复用回收链表上的空闲页叶子链上的物理分布会越来越没有规律逻辑顺序和物理顺序的错位日益严重。更新操作也类似。InnoDB 的更新策略是“先标记旧记录再插入新记录”如果更新列不涉及索引键影响还小一些一旦频繁更新二级索引列尤其把一个值从小改到大需要更换存储位置碎片积累的速度会非常快。所以我评估一张表的碎片风险时从来不会只看 insert 量而是把 delete、update 和大事务中的批量操作全部考虑进去。2. 如何确认索引碎片已经拖慢查询2.1 先查 information_schema用 Data_free 做初筛大量写入之后索引到底碎没碎先跑一条 SQL 看基础水位。最常用的方式是查信息库里的表空间信息SELECT table_name, engine, table_rows, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb, ROUND(data_free / 1024 / 1024, 2) AS free_mb FROM information_schema.TABLES WHERE table_schema your_db AND table_name your_table;data_free表示 InnoDB 表空间中已分配但尚未使用的字节数可以简单理解为“空洞”的水位线。如果这张表持续大量写入data_free从几十 MB 涨到几 GB那大概率已经有明显碎片。不过要注意data_free不能直接等同为“必须整理的碎片量”。InnoDB 内部会有预分配的页、purge 后待复用的页这些都会算进去。经验做法是先做一个初筛当data_free超过表总大小data_length index_length的 10%或者绝对值已经到 GB 级再进入下一步排查。实际使用中我更喜欢用一条聚合 SQL 把整个库的状态拉出来看SELECT table_schema, table_name, ROUND(data_free / 1024 / 1024, 2) AS free_mb, ROUND((data_length index_length) / 1024 / 1024, 2) AS total_mb, ROUND(data_free / (data_length index_length) * 100, 2) AS frag_pct FROM information_schema.TABLES WHERE engine InnoDB AND table_schema your_db AND data_free / (data_length index_length) 0.1 ORDER BY free_mb DESC;这套 SQL 适合作为索引碎片巡检的保留脚本。每次看到数据量排名靠前的表frag_pct超过 20%就该留意了。小表碎片影响可以忽略大表碎片到了这个比例慢查询大概率会找上门。2.2 用执行计划和状态变量确认“是不是在扫空页”光看data_free只能说明有碎片无法证明它影响了业务 SQL。想确认碎片是否有实际危害需要结合具体查询的执行计划和运行状态。先看一个典型慢查询EXPLAIN SELECT id, user_id, amount FROM order_main WHERE created_at BETWEEN 2024-01-01 AND 2024-03-01 AND status 1 ORDER BY id;如果执行计划走到了range但预估扫描行数明显高于实际返回行数比如预估扫 200 万行实际只返回 2 万行排除统计信息问题后就要警惕大量半空页导致的额外扫描。还可以用会话级状态变量观察实际读取行成本FLUSH STATUS; SELECT id, user_id, amount FROM order_main WHERE created_at BETWEEN 2024-01-01 AND 2024-03-01 AND status 1 ORDER BY id; SHOW SESSION STATUS LIKE Handler_read_next;Handler_read_next代表索引扫描时按顺序读取下一行的次数在返回结果集固定的情况下这个值越大说明扫描过程中经过的无效行或空槽越多。整理碎片后同样的 SQL这个计数通常会明显下降。MySQL 8.0.18 以后还可以用EXPLAIN ANALYZE看实际执行代价它会把每条 SQL 访问的页和行数都贴出来。对比整理前后的访问行数能很直观判断碎片对查询的影响。2.3 开启页分裂监控把碎片趋势变成数据如果想提前看到“碎片正在形成”而不是等问题爆发后再补救可以打开 InnoDB 的页分裂监控指标。MySQL 的information_schema.innodb_metrics里有现成的计数器SET GLOBAL innodb_monitor_enable index_page_splits; SET GLOBAL innodb_monitor_enable index_page_merge_successful;开启后可以查询SELECT NAME, COUNT, STATUS FROM information_schema.innodb_metrics WHERE NAME LIKE index_page%;index_page_splits记录页分裂次数index_page_merge_successful记录页合并成功次数。如果你发现某张表持续写入期间页分裂次数快速增长而合并次数跟不上说明碎片正在加速积累。把这组数据接到监控系统里设置一个日增长阈值能有效避免“某天突然全表慢查询”的被动局面。提示innodb_metrics里的状态变量在 MySQL 版本之间名称有差异生产环境可以先在测试实例上确认再启用。3. 碎片整理线上可以落地的几种做法3.1 OPTIMIZE TABLE最直接但要选好窗口确认碎片已经影响查询后最直观的操作就是OPTIMIZE TABLE order_main;这条命令对 InnoDB 来说本质是重建整张表并优化所有索引。它会按主键顺序重新组织数据把半空页尽量合并把散落在文件里的空洞清理掉最后再更新统计信息。很多慢查询就是在执行完 optimize 之后执行计划立刻变好。但别急着在业务高峰执行。OPTIMIZE TABLE 在 MySQL 5.6 及更早版本会锁表5.7 之后 InnoDB 支持在线 DDL执行期间大多数情况下允许并发 DML但整表重建会占用大量 CPU 和磁盘 IO。如果表很大几十 GB 甚至几百 GBoptimize 过程可能持续几十分钟到几小时期间磁盘写入压力会明显上涨可能拖慢同实例上的其他业务。执行前必须确认两点一是磁盘剩余空间要足够重建表需要大约等同于表大小甚至更多的临时空间二是在主从架构里optimize 产生的大量 binlog 会在从库重放如果从库性能本来就弱主从延迟会飙升。可以把它当成一次低频维护任务放在凌晨低峰期执行而不是当作日常 DDL 想跑就跑。3.2 ALTER TABLE FORCE 与单索引重建如果 OPTIMIZE TABLE 太重或者只想整理部分索引可以用更细粒度的手段。ALTER TABLE ... FORCE和OPTIMIZE TABLE语义接近同样会重建表ALTER TABLE order_main FORCE;写成ALTER TABLE order_main ENGINEInnoDB也有类似效果都是触发一次表重建让数据重新按主键顺序排列。如果碎片主要集中在某个二级索引而且不想整表重建可以单独删除再造该索引ALTER TABLE order_main DROP INDEX idx_created_at, ADD INDEX idx_created_at (created_at), ALGORITHMINPLACE, LOCKNONE;这种方式只重建一个二级索引对原表数据的 IO 压力比整表重建小。MySQL 5.6 以上支持ALGORITHMINPLACE且允许并发 DML但在索引删除重建的过程中该索引对查询是不可用的可能短暂影响业务。执行前最好先确认该索引不是当前热点查询的唯一可用路径。3.3 用在线 DDL 工具优雅整理pt-osc 与 gh-ost表特别大、业务又不能停时原生 OPTIMIZE TABLE 不一定是最优解。这时候可以用 Percona Toolkit 里的pt-online-schema-change或者 GitHub 开源的gh-ost把整理操作拆成可控的在线流程。pt-osc 的原理是创建一张新表按主键分批把旧表数据复制到新表同时在旧表上建触发器把复制期间的新写入同步到新表。全部完成后通过原子改名切换表。执行命令可以这样写pt-online-schema-change \ --host127.0.0.1 --port3306 \ --userdba --passwordyour_password \ --alterENGINEInnoDB \ --critical-loadThreads_running100 \ --max-loadThreads_running30 \ --chunk-size1000 \ --pause-file/tmp/pause_osc \ Dyour_db,torder_main \ --execute--max-load和--critical-load用来控制对线上负载的影响--pause-file可以在紧急时刻暂停任务。pt-osc 依赖触发器如果表上已经有很多触发器或者没有 SUPER 权限可能无法执行。gh-ost 走了另一条路不依赖触发器而是直接解析 binlog 获取增量数据对主库侵入更小。基本命令gh-ost \ --host127.0.0.1 --userdba --passwordyour_password \ --databaseyour_db --tableorder_main \ --alterENGINEInnoDB \ --allow-on-master --executegh-ost 要求开启 binlog 且格式为 ROW同时 binlog_row_image 必须是 FULL否则拿不到完整的前后镜像。两个工具都能通过加ENGINEInnoDB之类无害的 alter 语句达到碎片整理的目的比直接 optimize 平滑很多。3.4 超大数据量新建表加分批迁移可能是唯一答案如果某张表已经到几百 GB任何形式的在线 DDL 都不轻松哪怕用 pt-osc 也要跑很久。遇到这种情况我通常建议直接走“新建表 分批迁移 切换”的方案。思路是建一张同结构新表按主键范围把旧表数据分成多个批次并行迁移等数据量快追平时做一次短暂只读切换把后续增量和 schema 变更一次性处理完最后 RENAME TABLE 完成切换。批量迁移可以用类似这样的 SQLINSERT INTO order_main_new (id, order_no, user_id, amount, status, created_at) SELECT id, order_no, user_id, amount, status, created_at FROM order_main WHERE id ? AND id ?;每次搬运几万行到几十万行通过应用或任务调度脚本循环执行。迁移期间要持续记录旧表新增数据最后停机窗口内把增量补上再切换读写。这个方案不依赖 MySQL 的 online DDL数据校验、灰度切换都更可控代价是开发量更大。表大到一定程度短痛换长痛比硬撑 optimize 更稳妥。4. 源头治理如何让写入少产生碎片4.1 主键、唯一键与表结构调整前面强调过主键是否有序直接决定页分裂频率。如果现在有一张大表因为乱序主键导致碎片反复出现最彻底的优化是调整主键设计。生产环境直接改主键通常风险很高。一般做法是新建表把原表数据导入同时将主键改成自增 id原业务唯一键改成普通唯一索引CREATE TABLE order_main_new ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, ... PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB;自增主键写入时追加到尾部叶子页可以保持高利用率从写路径上就切断了大量碎片产生的根源。很多业务担心自增 id 会暴露数据量其实完全可以用一个随机字符串作为对外展示的业务号内部主键仍然用有序 id。对于无法立即重建表的场景至少要保证后续新表的 DDL 不再使用随机字符串做主键。索引设计上也要避免建大量冗余索引因为每多一个二级索引就多一棵会碎的小 B 树写入量越大维护成本越高。4.2 批量写入不要一条一条 insert大量写入场景下应用层的写入方式同样关键。一条一条 INSERT 并发写入每次都要走完整的事务提交和 fsync 流程表面看是慢了深层看还给页分裂创造了更频繁的触发机会。更推荐的做法是批量写入INSERT INTO order_main (order_no, user_id, amount, status, created_at) VALUES (A001, 1001, 99.00, 1, NOW()), (A002, 1002, 199.00, 1, NOW()), ...;单批次行数不必刻意追求大经验上每次 1000 到 5000 行比较合适。批次太大会导致单条 SQL 执行时间过长主从复制延迟也会被放大批次太小又起不到减少网络往返的作用。执行前确认max_allowed_packet足够大避免大 SQL 直接被拒。批量写入的另一个好处是如果业务侧能把数据按主键排好序再插入比如离线导入任务先排一次序再批量写InnoDB 在写入时几乎不会触发中间页分裂。数据导入场景下这个优化效果立竿见影。快照数据、回补数据、初始化数据都能先用临时表排序再导入。4.3 删除策略、归档与分区表大量删除是碎片的重要来源。业务上如果经常要清理历史数据可以考虑用软删除字段替代直接 DELETE。应用层默认只查status 1历史数据留着不动通过定期归档任务把过期数据搬走。如果数据天然有很强的时间属性比如日志、流水、审计记录更推荐按时间分区。分区表在删除旧数据时直接DROP PARTITION几乎不产生记录级碎片IO 成本远低于 DELETE 几百万行。比如按月分区ALTER TABLE order_main PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS(2024-02-01)), PARTITION p202402 VALUES LESS THAN (TO_DAYS(2024-03-01)), PARTITION p202403 VALUES LESS THAN (TO_DAYS(2024-04-01)) );分区不是银弹它更利于数据的批量生命周期管理并不能完全消除单分区内的页碎片。但配合定期维护可以让表长期保持在一个相对健康的状态。4.4 页合并阈值等参数的小幅调整InnoDB 提供了一个比较冷门但有效的参数innodb_merge_threshold默认值是 50。它表示索引页在删除操作后如果空间利用率低于该百分比就会尝试与相邻页合并。当大量删除造成页的空闲率普遍偏高时把这个值适当调大InnoDB 会更积极地把稀疏页合并释放空间减少碎片。可以针对当前会话或全局调整SET GLOBAL innodb_merge_threshold 70;不过这个参数影响所有表的页合并行为不能无脑调。页合并本身需要消耗 CPU 和 IO如果表持续高频写入页刚合并完又很快写满反而会增加额外分裂。更稳妥的方式是在低峰期打开监控观察调整前后index_page_merge_successful和index_page_splits两个计数器的变化用数据判断该不该继续调。写入侧还有一类容易被忽视的参数比如innodb_buffer_pool_size。缓冲池太小大量写入会导致脏页频繁刷盘页来不及合并就被写出去物理碎片更加严重。尽可能让热数据留在内存里对减少随机写的负面影响有帮助。5. 整理之后又慢这些坑要避开5.1 先看执行计划别把所有锅甩给碎片碎片整理不是万能的。遇到慢查询第一步永远是先看执行计划而不是急着 optimize。举个例子有一张用户操作记录表SQL 是SELECT id, user_id, action, created_at FROM user_action_log WHERE user_id 12345 ORDER BY id DESC LIMIT 20;执行计划显示走的是user_id二级索引但依然要回表读取 action、created_at 等字段每次回表都是一次随机 IO。如果单用户行为很多扫描的行数会很大这时即使碎片很少查询也会慢。解决方案不是整理碎片而是建一个覆盖索引ALTER TABLE user_action_log ADD INDEX idx_user_id_id (user_id, id);这种情况下索引已经覆盖了查询需要的 user_id、id 和排序条件优化器可以选择只扫索引页从而规避大量回表。索引碎片的影响被降到很低。所以先确认 SQL 本身有没有更好的索引路径再做碎片整理顺序别搞反。5.2 主从环境下整理操作的复制延迟很多团队在从库上执行 optimize 或ALTER TABLE ... FORCE时会突然发现主从延迟飙升。原因是 DDL 在主库执行后会作为语句记录到 binlog从库重放时同样需要重建表。如果从库的硬件配置比主库差或者从库上还在跑其他报表查询延迟会非常明显。这里有几种处理策略。如果确实需要整表整理优先考虑在低峰窗口执行同时把复制延迟监控拉出来观察。延迟超过阈值就暂停任务从库追平后再继续。如果用了 pt-osc 或 gh-ost在主库上执行时从库仍会收到一份重建表的 binlog同样有复制压力。一种常见做法是只在主库执行然后观察从库是否被拖慢如果从库延迟严重可以临时把从库上的流量切走等追平再切回。对于多个只读从节点的架构要错峰执行不要在同一个时间点让所有从库同时开始重放重建表的大事务。另外整理完大表后最好顺手执行一次ANALYZE TABLE更新统计信息。否则表数据重新排列了优化器手里的统计信息还是旧的可能继续选错索引出现“整理完了但还是慢”的错觉。5.3 巡检要常做别等上亿行才开始索引碎片最怕的事是等表涨到上亿行、写满几万个稀疏页后再集中治理。那时候一次整理要跑几小时风险很高。正确姿势是把碎片巡检纳入常规数据库运维用脚本定期发现问题。我常用的巡检思路是每天凌晨通过 information_schema 采集大表的data_free、index_page_splits等指标写入监控库当发现碎片率快速上升或绝对值超过阈值时自动出工单提醒由 DBA 决定是否在下一个维护窗口执行整理。这里给出一个最简 Shell 片段可以结合 cron 跑#!/bin/bash mysql -h127.0.0.1 -udba -p**** -e SELECT table_schema, table_name, ROUND(data_free/1024/1024,2) AS free_mb, ROUND((data_lengthindex_length)/1024/1024,2) AS total_mb, ROUND(data_free/(data_lengthindex_length)*100,2) AS frag_pct FROM information_schema.TABLES WHERE engineInnoDB AND table_schemaorder_db AND data_free/(data_lengthindex_length) 0.2 ORDER BY free_mb DESC; /tmp/frag_tables.txt脚本只负责找出高碎片风险表真正的 optimize 操作不要自动执行必须人工确认。因为表的大小、业务时段、主从部署都不一样完全自动 DDL 容易出事故。经验收尾最后分享一个我个人的判断经验索引碎片优化核心不是“一次整理得干不干净”而是找到一张表反复产生碎片的根因。如果主键乱序、删除量大、索引冗余这些问题不解决今天整理完下个月碎片又会卷土重来。反过来写路径健康的大表几年不 optimize查询也照样能保持稳定。处理它们的最佳时机其实是在设计表结构的那一天。希望这篇围绕大量写入与索引碎片的排查思路能让你少踩几个坑少熬几次夜。