5.2 碎片整理与表维护

5.2 碎片整理与表维护

本节摘要：频繁增删改产生碎片，浪费空间拖慢查询。本节讲清楚碎片成因、检测、整理（OPTIMIZE/重建），让表保持紧凑高效。

碎片怎么产生

碎片：表数据页中有空闲空间未用，但未归还 OS。

成因：

• DELETE：删行留空位，页内空闲。
• UPDATE：变长列更新变长，原位置不够，移到新位置留空。
• 页分裂：B+树页满分裂，页内空闲。
• MVCC 旧版本（PG）：UPDATE 创建新版本，旧版本成死元组，VACUUM 前占空间。

危害：

• 空间浪费：表文件比实际数据大。
• 查询慢：扫描要扫空闲空间，IO 增加。
• 缓存效率低：缓冲池缓存空闲空间，浪费。

碎片检测

MySQL：

• information_schema.TABLES——DATA_FREE（碎片空间）。
• DATA_FREE / DATA_LENGTH 比例——比例高碎片多。
• 经验：DATA_FREE > 30% DATA_LENGTH 考虑整理。

PostgreSQL：

• pg_stat_user_tables——n_dead_tup（死元组）。
• pg_relsize vs pg_table_size——膨胀率。
• pgstattuple 扩展——详细页内空闲。

Oracle：

• DBA_TABLES——EMPTY_BLOCKS、CHAIN_CNT。
• DBMS_SPACE——空间使用详情。

MySQL 碎片整理

OPTIMIZE TABLE t：

• 重建表，回收碎片。
• InnoDB：拷贝数据到新表，替换旧表（Online DDL 8.0 支持在线）。
• 锁表——5.6 前 LOCK，5.6+ Online（仍占资源）。
• 大表慢——亿级表可能小时级。

ALTER TABLE t ENGINE=InnoDB：

• 等价 OPTIMIZE（重建）。
• 8.0 用 instant/online DDL，影响小。

pt-online-schema-change：

• Percona 工具，在线重建表。
• 创建影子表，拷贝数据，触发器同步，最后替换。
• 不锁表，但占资源（拷贝期间双倍空间）。

注意：

• 整理后统计可能变——重新 ANALYZE。
• 大表整理低峰期——占 IO/CPU。
• 频繁整理没必要——碎片自然产生，到阈值再整。

PostgreSQL 碎片整理

VACUUM：

• 清理死元组（MVCC 旧版本），标记可重用。
• 不归还 OS（空间留给未来插入）。
• 普通 VACUUM——不锁表，定期跑。

VACUUM FULL：

• 重建表，归还空间给 OS。
• 锁表（排他锁），大表慢。
• 谨慎用——影响业务。

pg_repack/pg_squeeze：

• 在线重建表（类似 pt-online-schema-change）。
• 不锁表，但占资源。
• 替代 VACUUM FULL。

CLUSTER：

• 按索引物理重排表，顺带回收碎片。
• 锁表，适合按某索引查询多的表。

注意：

• autovacuum 定期 VACUUM——防膨胀。
• VACUUM FULL/pg_repack 偶尔——回收严重膨胀。
• 避免长事务——阻塞 VACUUM 导致膨胀。

Oracle 碎片整理

ALTER TABLE t SHRINK SPACE：

• 在线收缩表（行迁移+收缩 HWM）。
• 不锁表（但需行移动 ENABLE ROW MOVEMENT）。
• 比 MOVE 好（MOVE 锁表）。

ALTER TABLE t MOVE：

• 重建表，锁表。
• 索引失效需重建。

DBMS_REDEFINITION：

• 在线重定义表，类似 pt-online-schema-change。

表维护实践

1. 监控碎片

• 定期检查 DATA_FREE/n_dead_tup。
• 设阈值告警——超 30% 提醒整理。

2. 定期整理

• 高频更新表——定期（如月度）整理。
• 低频表——按需整理。
• 低峰期操作——避免业务影响。

3. 在线工具

• 大表用在线工具（pt-online-schema-change/pg_repack/SHRINK）。
• 避免锁表影响业务。

4. 预防

• 合适填充因子（PG fillfactor）——留页内空间给 UPDATE，减少页分裂。
• 避免过度 UPDATE——批量更新替代循环单条。
• 及时归档删除老数据——减少表大小。

5. 索引维护

• 索引也碎片化——定期重建索引。
• MySQL：ALTER INDEX/pt-osc。
• PG：REINDEX/REINDEX CONCURRENTLY（在线）。
• Oracle：ALTER INDEX REBUILD ONLINE。

⚠️ 常见误读：以为"碎片要天天整理"。碎片自然产生，频繁整理浪费资源且影响业务。监控阈值（如 30%），到阈值低峰期整理。

💡 关键直觉：碎片（DELETE 留空/UPDATE 变长移位/页分裂/MVCC 旧版本死元组）致空间浪费查询慢缓存低。检测（MySQL DATA_FREE/PG n_dead_tup，>30% 整理）。MySQL 整理（OPTIMIZE TABLE 重建/ALTER ENGINE/pt-online-schema-change 在线不锁但占资源）。PG（VACUUM 清死元组不归还 OS/VACUUM FULL 重建归还锁表/pg_repack 在线/CLUSTER 按索引重排）。Oracle（SHRINK SPACE 在线/MOVE 锁表/DBMS_REDEFINITION）。实践：监控阈值告警、定期高频更新表月度、低峰期、在线工具避免锁、预防（fillfactor 留页空间/避免过度 UPDATE/及时归档）、索引也碎片定期重建（REINDEX CONCURRENTLY 在线）。不天天整理，到阈值低峰期。

维护要点清单

• 碎片成因：DELETE 留空位、UPDATE 变长移位留空、B+树页分裂留空、PG MVCC 旧版本死元组（VACUUM 前占空间）。危害空间浪费/查询慢扫空闲/缓存效率低。
• 检测：MySQL information_schema.TABLES DATA_FREE（DATA_FREE/DATA_LENGTH >30% 整理）、PG pg_stat_user_tables n_dead_tup + pgstattuple、Oracle DBA_TABLES EMPTY_BLOCKS/CHAIN_CNT + DBMS_SPACE。
• MySQL 整理：OPTIMIZE TABLE（重建拷贝替换，8.0 Online DDL）、ALTER TABLE ENGINE=InnoDB（等价重建）、pt-online-schema-change（影子表+触发器+替换，不锁表占双倍空间）。整理后重新 ANALYZE，大表低峰期。
• PG 整理：VACUUM（清死元组标记可重用不归还 OS，定期跑）、VACUUM FULL（重建归还 OS，锁表谨慎）、pg_repack/pg_squeeze（在线重建不锁）、CLUSTER（按索引物理重排回收碎片锁表）。autovacuum 防膨胀，避免长事务阻塞 VACUUM。
• Oracle 整理：SHRINK SPACE（在线行迁移+收缩 HWM，需 ENABLE ROW MOVEMENT）、MOVE（重建锁表索引失效）、DBMS_REDEFINITION（在线重定义）。
• 实践：监控碎片阈值告警（>30%）、定期整理（高频更新表月度/低频按需）、低峰期、在线工具避免锁、预防（PG fillfactor 留页空间减页分裂/避免过度 UPDATE/及时归档删老数据减表大小）、索引维护（定期重建，MySQL ALTER INDEX/PG REINDEX CONCURRENTLY 在线/Oracle REBUILD ONLINE）。
• 原则：不天天整理（自然产生频繁整理浪费资源影响业务），监控阈值低峰期整理。

重建的时机经济学

碎片整理不是保健操，是有代价的手术（重建期间锁或资源占用、磁盘空间翻倍、缓冲池被冲刷），所以要算时机经济学。触发条件的三个信号：碎片率超过阈值（比如三成）、扫描全表的逻辑读与物理读比例持续恶化、页填充率显著低于同侪表——三信号同时出现才动手，单一信号先观察。时机的三个考量：业务低峰窗口够不够长（大表重建以小时计）、剩余磁盘是否支撑在线重建的临时双份、主从延迟是否允许（重建产生的大量日志会冲击复制）。替代方案的优先级：能用在线索引重建就别重建表、能分区级处理就别整表动刀（第 3 章分区的又一红利）、历史数据多的先归档再重建（重建的对象小一半）。这套经济学的核心是克制——碎片率两成的表重建后扫描快了一成半，但重建窗口里业务受损的可能远超这一成半的收益；把重建留给"收益明确、窗口安全、方案最小"三条件齐备的场合，是成熟 DBA 的自我修养。

在线 DDL 的窗口工程

表维护的现代形态是在线 DDL（不锁或轻锁地改表结构），它的工程要点值得单独成节。第一，了解你的操作的"在线度"：加列、改默认值这类通常是秒级元数据操作；改列类型、加索引视引擎与版本可能是"允许并发读写但重建慢"或"长时间锁表"——上线前查该版本的官方矩阵，别拿生产验证运气。第二，大表重建的三件配套：磁盘余量核对（重建期间新旧并存）、主从延迟预案（重建日志量会冲击复制，考虑先从库后主库的滚动策略）、超时与失败点预演（中断后能否安全续跑）。第三，变更大表的替代路径：影子表方案（新建表逐步双写回填切换）在超大盘子上的可控性优于原生在线 DDL，虽然工程量大但每一步可回退。在线 DDL 把"改表结构"从停机窗口的噩梦变成常规操作，但"常规"的前提是把它当工程做——窗口、余量、预案三件套齐了才配叫在线。

维护窗口的编排艺术

碎片章收官谈维护窗口的编排——多个维护任务（重建、统计、备份、归档）共享有限低峰时的调度智慧。原则一，互斥识别：重建冲刷缓冲池、备份占 IO、统计耗 CPU——把同类资源占用的任务错开（重建与备份不同夜，统计避开重建后的当晚）。原则二，依赖排序：归档在重建前（重建对象更小）、统计在重建后（新结构要新统计）、备份在任何结构变更后（保住变更后的第一个全量）。原则三，时长预算：每个任务的历史时长进台账，编排时按预算排期、超预算告警——防止"重建跑进早高峰"的经典事故。原则四，日历化：季度维护日历提前公布（哪夜做什么、谁值守、怎么回滚），让维护从"抽空做"变成"按历走"。四原则合成的编排能力，是把维护从体力活变成指挥艺术的台阶——窗口有限而任务繁多，编排就是用顺序换吞吐。

表维护的月度体检单

碎片章收官给月度体检单的固定格式——十分钟看完一屏。行一，碎片排行：碎片率前十的表与环比——持续上升的进入治理候选。行二，页填充分布：核心表的页填充均值——低于经验阈值（第 3 章的六成）的标注。行三，自动统计与维护任务的健康：上月成功失败比、失败原因归类——静默失败是重点盯防对象。行四，大表增长榜：行数增长前十——与容量预测对表，超预测的查原因（是业务增长还是数据异常膨胀）。四行数据、十分钟、一张截图归档——表维护的全部日常就浓缩在这张体检单里。它的价值不在单月，在十二个月连起来看时——慢性病全都逃不过月度环比的眼睛，而慢性病正是维护章所有内容的假想敌。

最后补一个进阶动作：把体检单的历史数据（十二个月的碎片与填充趋势）在新人大学的第一课里展示——一张"时间维度的数据库健康史"比任何文档都更能让新人理解"维护是在对抗熵增"这个本质；这个教学小技巧的边际成本是一次截图，收益是团队维护文化的代际传承。

维护工具链的选型要点

碎片与表维护的工具链（厂商自带、开源脚本、商业平台三类）选型给三个要点。要点一，能力覆盖对齐：工具要覆盖你的全部对象类型（普通表、分区表、索引、大对象），只支持一半的工具会在另一半上留下盲区。要点二，窗口与速度：维护工具的执行速度直接决定窗口占用——选型时用你最大的表实测（而不是看厂商的演示库），小时级的差异很常见。要点三，安全性与可回退：维护操作本身要能中断、能续跑、能撤销——把"维护工具自己造成故障"的概率降到最低，这比快慢更重要。三个要点之外还有个隐性维度：输出的报告质量（维护前后的对比数据是否自动生成）——它决定你的台账是自动积累还是手工拼凑。工具链选对了，本章的所有流程都事半功倍；选错了，维护本身就成了新的故障源。

最后补一句给多云与多版本团队：不同版本或不同云上的库，维护工具链与参数语义有差异（重建的在线度、统计的算法、碎片度量的口径），维护流程要按"平台版本"分册管理——用一套流程管异构舰队，是维护事故的常见起点；分册的成本是文档翻倍，收益是每次维护都在正确的语境里执行。

收尾送一句维护哲学：碎片与统计的退化是数据库的"熵增税"，按时缴纳（定期维护）成本固定，逾期缴纳（故障后抢修）利息惊人——维护排期的本质是一份与熵增的分期协议，签了它，系统的老去就有了节奏；这份协议的履约记录，就是你作为数据库管理者的信用史。

再补一个零成本的动作作真正的收尾：翻出你的库里最老的那张表（按建表时间排序），看一眼它的碎片率与页填充——老表是维护欠账的集散地，这一个动作常能翻出三五个被遗忘的优化对象；维护的起点不必是宏大体系，可以从一张最老的表开始。