案例：大表膨胀分析，回收碎片空间

场景背景

• 时间：2026-06-18
• 现象：orders 表 10.6 GB，但实际数据只有 8.1 GB，2.5 GB 是碎片
• 数据库：MySQL 8.0.32，业务库 jump
• 目标：分析表膨胀原因，安全回收碎片空间
• 约束：业务高峰期不能锁表，需要 Online 方案

生活化比喻：InnoDB 表就像一本活页笔记本。你不断插入新页（INSERT）、撕掉旧页（DELETE），但撕掉的页还在笔记本里占位置（碎片）。OPTIMIZE TABLE 就是重新整理笔记本，但整理时别人不能写。MySQL 8.0 的 Online DDL 像"一边用一边整理"——别人正常用笔记本，你在后台悄悄把空白页抽掉。

---

步骤 1：检测表膨胀（1 分钟）

dbskiter --database=jump diagnose bloat

输出：

表膨胀分析：
====================================

高膨胀表：
  1. logs
     实际数据: 2.1 GB
     表空间占用: 6.2 GB
     碎片率: 195%（大量删除后未回收空间）
     建议: OPTIMIZE TABLE 或 ALTER TABLE ENGINE=InnoDB

  2. user_actions
     实际数据: 1.8 GB
     表空间占用: 3.1 GB
     碎片率: 72%
     建议: 同上

  3. orders
     实际数据: 8.1 GB
     表空间占用: 10.6 GB
     碎片率: 31%
     建议: 同上

碎片率计算：(表空间 - 实际数据) / 实际数据 × 100% - logs 碎片率 195%：表空间是实际数据的 2.95 倍（接近 3 倍！） - 通常碎片率 > 30% 就建议优化

---

步骤 2：分析碎片来源（2 分钟）

查看表结构和历史操作

dbskiter --database=jump diagnose sql "
SHOW TABLE STATUS LIKE 'logs';
"

输出：

Name: logs
Engine: InnoDB
Rows: 8,900,000
Avg_row_length: 258
Data_length: 2,296,320,000 (2.1 GB)
Index_length: 838,860,800 (0.8 GB)
Data_free: 4,194,304,000 (3.9 GB碎片！)
Auto_increment: 9,123,456
Create_time: 2025-03-15

Data_free 就是 InnoDB 已分配但未使用的空间。logs 表有 3.9 GB 碎片，加上数据和索引 2.9 GB，总计 6.8 GB（接近报告的 6.2 GB）。

为什么 logs 表碎片这么多？

dbskiter --database=jump diagnose sql "
SELECT 
    event_name,
    count_star,
    sum_timer_wait
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%logs%'
ORDER BY count_star DESC
LIMIT 5;
"

典型场景分析：

操作类型          次数/天     说明
INSERT INTO logs  12,000     每天插入 1.2 万条日志
DELETE FROM logs  11,000     每天删除 90 天前的旧日志
UPDATE logs       200        更新日志状态（很少）

碎片原因：每天大量 DELETE，但 InnoDB 的删除是"标记删除"，不会立即释放空间。就像你撕掉笔记本里的纸，但笔记本的总页数没变，只是撕掉的那页标记为"可用"。插入新数据时 InnoDB 会优先用这些"可用页"，但长期高频率删除插入，会导致页利用率不均匀，产生碎片。

---

步骤 3：评估优化影响（2 分钟）

方案对比

方案	锁表时间	额外磁盘空间	适用场景	风险
OPTIMIZE TABLE	长锁表（分钟级）	需要 1x 表空间	小表、维护窗口	高峰期不可用
ALTER TABLE ... ENGINE=InnoDB	几乎不锁（Online DDL）	需要 1x 表空间	大表、业务在线	磁盘空间不足会失败
pt-online-schema-change	不锁表	需要 2x 表空间	超大表、MySQL 5.6	需要安装 percona-toolkit
重建表 + 主从切换	零影响	需要 2x 表空间	核心生产库	操作复杂，需要主从架构

MySQL 8.0 的 ALTER TABLE ... ENGINE=InnoDB 支持 Online DDL： - 重建表数据，回收碎片 - 执行期间读写正常（允许 DML） - 仅在最后"元数据锁"阶段短暂锁表（通常 < 1 秒）

检查 Online DDL 支持情况

dbskiter --database=jump diagnose sql "
SELECT 
    name,
    status
FROM information_schema.innodb_metrics
WHERE name LIKE '%ddl%';
"

MySQL 8.0 确认：innodb_rebuild_table 等 Online DDL 指标可用，说明支持 Online 重建。

---

步骤 4：执行优化（业务低峰期）

方案 A：ALTER TABLE Online DDL（推荐，MySQL 8.0）

# 先检查磁盘空间（需要至少 1x 表空间的额外空间）
dbskiter --database=jump diagnose space

确认可用空间 > 表大小（10.6 GB）：

磁盘总容量: 100 GB
磁盘已使用: 55 GB
可用空间: 45 GB
确认: 45 GB > 10.6 GB，可以进行 Online DDL

-- 在业务低峰期执行（凌晨 2-4 点）
-- 执行前通知业务方，虽然 Online DDL 允许读写，但会消耗额外 IO

ALTER TABLE logs ENGINE=InnoDB;

-- 或加上 ALGORITHM 和 LOCK 参数，更明确控制
ALTER TABLE logs ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

执行过程监控：

# 另一个终端监控进度
dbskiter --database=jump diagnose sql "
SELECT event_name, work_completed, work_estimated, 
       (work_completed/work_estimated*100) as pct
FROM performance_schema.events_stages_current
WHERE thread_id IN (SELECT thread_id FROM performance_schema.threads 
                    WHERE processlist_command = 'Query');
"

预期输出：

事件: stage/innodb/alter table (read PK and internal sort)
已完成: 1,245,000 行
预计总数: 8,900,000 行
进度: 14.0%

对于 8.9 百万行的 logs 表，预计耗时 10-30 分钟（取决于磁盘 IO 和 CPU）。

方案 B：OPTIMIZE TABLE（小表或维护窗口期）

-- 仅适用于小表（< 1 GB）或确认维护窗口期无业务访问
OPTIMIZE TABLE logs;

注意：OPTIMIZE TABLE 对 InnoDB 实际就是 ALTER TABLE ... ENGINE=InnoDB + ANALYZE TABLE。但在 MySQL 8.0 中，它可能会锁表，不如直接用 ALTER TABLE 加 LOCK=NONE 安全。

---

步骤 5：验证优化效果

dbskiter --database=jump diagnose bloat

预期结果：

表名	优化前	优化后	变化
logs	6.2 GB (碎片 3.9 GB)	2.3 GB	-3.9 GB
user_actions	3.1 GB (碎片 1.3 GB)	1.9 GB	-1.2 GB
orders	10.6 GB (碎片 2.5 GB)	8.2 GB	-2.4 GB
合计释放			-7.5 GB

dbskiter --database=jump diagnose sql "
SHOW TABLE STATUS LIKE 'logs';
"

优化后：

Name: logs
Data_length: 2,296,320,000 (2.1 GB)
Index_length: 838,860,800 (0.8 GB)
Data_free: 2,097,152 (2 MB)  ← 碎片几乎为零！

---

步骤 6：长期预防碎片

1. 定期监控膨胀表

# 每月检查一次膨胀表
dbskiter --database=jump diagnose bloat

# 或加入 crontab（每月 1 号凌晨 3 点）
0 3 1 * * dbskiter --database=jump diagnose bloat > /var/log/dbskiter/bloat_$(date +%Y%m).log

2. 自动清理 + 重建策略

#!/bin/bash
# /opt/scripts/optimize_bloat_tables.sh

# 每月自动重建膨胀率 > 50% 的表
BLOAT_THRESHOLD=50

# 获取膨胀表列表（简化版，实际可用 dbskiter 输出解析）
# ... 解析 diagnose bloat 输出 ...

for table in logs user_actions; do
    echo "$(date): 重建表 ${table}..."
    mysql -u root -p -e "ALTER TABLE jump.${table} ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;"
    echo "$(date): 表 ${table} 重建完成"
done

3. 表分区避免碎片累积

-- 对于日志类高频删除表，使用分区，直接删除旧分区（秒级，不锁表）
ALTER TABLE logs PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p202405 VALUES LESS THAN (TO_DAYS('2026-06-01')),
    PARTITION p202406 VALUES LESS THAN (TO_DAYS('2026-07-01')),
    PARTITION p202407 VALUES LESS THAN (TO_DAYS('2026-08-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 每月直接删除过期分区（不产生碎片！）
ALTER TABLE logs DROP PARTITION p202405;
-- 相比 DELETE 后 OPTIMIZE，分区删除是 O(1) 操作，瞬间完成

4. 调整 InnoDB 页大小（谨慎）

# my.cnf
# 默认页大小 16KB，如果表以整行读取为主，可以不改
# 如果大量随机读写，可能需要调整
innodb_page_size = 16384

页大小通常在初始化数据库时设置，后期修改需要重建整个数据库。不建议在生产环境修改。

---

复盘要点

1. 表膨胀排查口诀

表空间大 → 查碎片率 → 分析原因（DELETE多？）→ 选方案（Online DDL/分区）→ 低峰期执行 → 验证效果

2. 碎片率判断标准

碎片率	状态	建议
< 10%	优秀	无需处理
10-30%	正常	观察，结合表大小判断
30-50%	需要关注	小表（< 1 GB）可 Online DDL，大表规划维护窗口
50-100%	建议优化	安排 Online DDL 或分区改造
> 100%	严重	立即处理，碎片超过实际数据

3. Online DDL 注意事项

注意点	说明
磁盘空间	需要至少 1x 表大小的额外空间
IO 压力	重建过程会大量读写磁盘，避开业务高峰
主从延迟	主库执行 Online DDL，从库也会应用，关注主从延迟
大表风险	超过 50 GB 的表，Online DDL 可能耗时数小时，考虑 pt-osc
监控进度	用 performance_schema.events_stages_current 查看进度

4. 常见误区

误区	正确做法
"DELETE 后空间自动释放"	InnoDB 是标记删除，空间不立即释放，需要 OPTIMIZE/ALTER
"OPTIMIZE TABLE 和 ALTER TABLE 一样"	MySQL 8.0 中 ALTER TABLE ... ENGINE=InnoDB, LOCK=NONE 更安全
"小表不用管碎片"	小表碎片影响小，但日志类小表高频删除，碎片累积快
"Online DDL 完全不锁表"	最后元数据提交阶段会短暂锁（通常 < 1s），不是绝对零锁
"重建表后一劳永逸"	持续 DELETE/INSERT 会再次产生碎片，需要定期维护
"分区表没有碎片"	分区表依然有碎片，但可以通过删除旧分区避免累积

---

延伸阅读

• 案例：磁盘空间告警
• 案例：预判磁盘爆满
• dbskiter 命令：diagnose bloat、diagnose space