关键词表膨胀、数据库存储空间、InnoDB、碎片整理、数据库性能优化、磁盘空间不足大家好我是小耶写功课只是为了我踩过的坑你们别再踩了线上有这么一种故障比慢查询还让人头疼——磁盘空间报警。你打开数据库一看明明业务数据量增长不大但某个表的物理文件占了几十GB。索引文件也跟着膨胀Buffer Pool命中率下降查询从10ms变成了3秒。这就是数据库里最隐蔽的性能杀手表膨胀。今天把表膨胀这件事从头到尾讲清楚帮你找到那些消失的磁盘空间。先搞明白表膨胀到底是什么表膨胀不是数据变多了而是表文件里堆积了大量无效空间。无效空间从哪来来源一DELETE后的空洞InnoDB执行DELETE时并不是把数据从磁盘上擦掉而是把这条记录标记为已删除。这条记录占用的空间变成了空洞——虽然不再存数据但.ibd文件不会缩小磁盘空间不释放。-- 删了100万行数据 DELETE FROM orders WHERE status cancelled AND created_at 2025-01-01; -- 结果磁盘空间一点没少只是表里多了100万个空洞来源二UPDATE导致的行迁移InnoDB的行存储是固定页大小默认16KB。当UPDATE操作使某行数据变长当前页塞不下时数据库会把这行移到新页原位置留下空洞。这叫行迁移。来源三页分裂向有主键索引的表中INSERT数据时如果插入位置在两行中间而目标页已经满了数据库会把这一页劈成两半——各腾出一半空间。这个过程叫页分裂会产生大量碎片空间。插入前 Page A: [row1][row2][row3][row4][row5][FULL] 插入rowX到row3后面 → Page A满了 → 分裂 Page A: [row1][row2][row3][空][空] Page B: [空][空][空][row4][row5]为什么膨胀不会被自动回收InnoDB的后台Purge线程会清理undo日志中的旧版本但不会收缩.ibd文件。DELETE标记的无效空间可以被后续INSERT复用但如果业务写入量不大这些空洞就会一直空着。表膨胀的三大危害存储空间浪费一个实际有效数据只有10GB的表可能因为膨胀占用30GB甚至50GB的磁盘空间。索引也会跟着膨胀本来2GB的索引变成8GB。如果你的表每天DELETE几千条历史数据又只INSERT几百条新数据空洞会越积越多。查询性能下降InnoDB扫描数据页时会扫到空洞IO浪费在无效数据上。索引B树因为页分裂变得松散树高增加原来3层就能定位到的数据变成4层。Buffer Pool里缓存了大量空洞页真正有用的数据反而被挤出去了。运维风险暴增膨胀持续累积可能突然耗尽磁盘空间导致数据库写入失败、主从复制中断。而且膨胀越严重后续整理时的开销越大——VACUUM一个膨胀3倍的表可能比正常表多花2倍时间。快速识别膨胀表方法一对比数据长度和索引长度SELECT table_schema, table_name, ROUND(data_length / 1024 / 1024, 1) AS data_mb, ROUND(index_length / 1024 / 1024, 1) AS index_mb, ROUND((data_length index_length) / 1024 / 1024, 1) AS total_mb, table_rows FROM information_schema.tables WHERE table_schema your_db ORDER BY (data_length index_length) DESC LIMIT 20;方法二检查DELETE后空间是否释放-- 删除大量数据后查看表空间大小 SELECT table_name, ROUND(data_length / 1024 / 1024, 1) AS data_mb, ROUND(index_length / 1024 / 1024, 1) AS index_mb, table_rows, ROUND(data_length / NULLIF(table_rows, 0), 1) AS bytes_per_row FROM information_schema.tables WHERE table_schema your_db AND table_name your_table;如果table_rows大幅减少但data_length没怎么变说明有膨胀。方法三估算膨胀率-- 用avg_row_length估算理论大小和实际大小对比 SELECT table_name, table_rows, ROUND(data_length / 1024 / 1024, 1) AS actual_mb, ROUND((table_rows * avg_row_length) / 1024 / 1024, 1) AS estimated_mb, ROUND(data_length / NULLIF(table_rows * avg_row_length, 0) * 100, 1) AS bloat_pct FROM information_schema.tables WHERE table_schema your_db AND table_rows 1000;bloat_pct超过150%说明膨胀明显超过200%需要尽快处理。表膨胀的四大根因和解决方案根因一DELETE历史数据后不整理这是最常见的膨胀场景。定期DELETE过期数据但.ibd文件不会缩小。典型场景-- 每天定时清理30天前的日志 DELETE FROM access_log WHERE created_at DATE_SUB(NOW(), INTERVAL 30 DAY); -- 跑了半年日志表50GB实际有效数据只有5GB解决方案-- 方案一OPTIMIZE TABLE会锁表低峰期执行 OPTIMIZE TABLE access_log; -- 重建表回收空间优化索引 -- 方案二ALTER TABLE重建 ALTER TABLE access_log ENGINEInnoDB; -- 效果和OPTIMIZE一样但可以在某些版本中用pt-osc在线执行 -- 方案三分区表用DROP PARTITION代替DELETE ALTER TABLE access_log DROP PARTITION p202501; -- 直接丢弃整个分区文件瞬间释放空间不产生空洞根因二长事务阻塞清理InnoDB的Purge线程需要等所有可能引用旧版本的事务结束后才能清理undo日志。一个跑了几个小时的长事务会让整个数据库的undo日志持续堆积。排查方法-- 查看当前运行时间最长的事务 SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec, trx_query FROM information_schema.innodb_trx ORDER BY trx_started ASC LIMIT 10;解决方案-- 设置InnoDB事务超时 SET GLOBAL innodb_lock_wait_timeout 60; -- MySQL 8.0设置长事务超时 -- 在配置文件中设置 innodb_thread_sleep_delay 10000 max_execution_time 1800000 -- 30分钟超时 -- 监控长事务并告警 SELECT * FROM information_schema.innodb_trx WHERE TIMESTAMPDIFF(MINUTE, trx_started, NOW()) 30;根因三频繁UPDATE导致页分裂有些业务会频繁UPDATE状态字段每次更新如果数据变长就可能触发页分裂。典型场景-- 每次状态变更都UPDATE整行包含大字段 UPDATE orders SET status shipped, shipping_info {tracking:xxx,carrier:SF,address:...很长...}, updated_at NOW() WHERE order_id 12345; -- 高频执行shipping_info字段每次可能不一样长解决方案把频繁变更的小字段和不变的大字段分表存储状态变更用单独的流水表追加写入而不是UPDATE原表需要高频更新的计数器用Redis等缓存层-- 分表设计主表状态流水表 -- 主表只存当前状态 UPDATE order_status SET current_status shipped WHERE order_id 12345; -- 变更记录追加到流水表只有INSERT不产生UPDATE空洞 INSERT INTO order_status_log (order_id, from_status, to_status, changed_at) VALUES (12345, paid, shipped, NOW());根因四数据冷热不分热表里混入了大量已完结的、不会再被访问的历史记录。每次扫描都要经过这些冷数据的页。解决方案冷热数据分离-- 按月建表历史表归档后直接DROP CREATE TABLE orders_202601 LIKE orders; -- 月底将新数据写入对应月表 -- 需要查询时SELECT * FROM orders_current UNION ALL SELECT * FROM orders_202601 ... -- 归档完成后清理 DROP TABLE orders_202401; -- 整表删除磁盘空间瞬间释放膨胀后怎么回收空间发现表已经膨胀了有几种回收方式方案一OPTIMIZE TABLEOPTIMIZE TABLE your_table;重建表索引回收空间。会锁表建议在业务低峰期执行。大表可能需要很长时间。方案二在线DDLMySQL 5.6ALTER TABLE your_table ENGINEInnoDB, ALGORITHMINPLACE, LOCKNONE;不阻塞读写操作适合不能停机的场景。但会消耗额外IO和磁盘空间重建期间两份数据并存。方案三使用pt-online-schema-changept-online-schema-change --alter ENGINEInnoDB \ Dyour_db,tyour_table \ --executePercona Toolkit工具在线重建表自动处理触发器和数据迁移。方案四主从切换-- 在从库上重建表 STOP SLAVE; OPTIMIZE TABLE your_table; START SLAVE; -- 主从切换把重建好的从库提升为主库适合超大表几百GB级别避免在主库上直接操作。日常维护建议场景操作频率是否需要锁表DELETE后回收空间OPTIMIZE TABLE按需✅日常索引维护ANALYZE TABLE每周❌更新统计信息ANALYZE TABLE数据量变化10%后❌预防性重建ALTER TABLE ... ENGINEInnoDB季度低峰期可选冷热分离归档归档DROP旧分区按月❌避坑检查清单建库和日常运维过一遍这个清单✅ DELETE大量数据后立即安排OPTIMIZE TABLE低峰期✅ 设置事务超时参数防止长事务挂起✅ 大表使用分区设计用DROP PARTITION代替DELETE✅ 频繁变更的状态字段单独拆表✅ 定期监控表大小和行数的比值变化✅ 制定季度表重建计划业务低峰期✅ 磁盘空间告警阈值设到80%给膨胀留出缓冲✅ 数据迁移前检查源库和目标库的碎片情况表膨胀不是突发故障而是日积月累的慢性病。数据量没怎么涨磁盘先满了查询没怎么变复杂就是变慢了——遇到这些情况先查膨胀。小耶在手SQL不愁。还有什么想了解的欢迎留言小耶一定知无不言言无不尽……我们下次见~