数据库定期自动碎片优化提速定时执行脚本实战

知识分享

2026-07-24 20:00

23 阅读

数据库用久了，频繁增删改操作会让表空间产生大量碎片。
碎片多了，查询变慢，备份文件也膨胀。
这篇教程就是教你写一个数据库定期自动碎片优化提速定时执行脚本，让服务器在没人干预的情况下，自动把碎片清理掉，保持查询丝滑。

为什么需要自动碎片清理

MySQL 和 MariaDB 的 InnoDB 引擎在删除或更新数据时，不会立即把磁盘空间归拢，而是留下很多“空洞”。
这些空洞就是碎片。
碎片最直接的后果是：全表扫描变慢，索引利用率下降。
如果数据库每天都有大量数据变动，每周甚至每天执行一次碎片优化会很有帮助。

动手前的确认清单

在写脚本之前，先确认三个条件，避免执行时报错：

数据库账号权限：执行 OPTIMIZE TABLE 需要 ALTER 和 INSERT 权限，建议单独创建一个专用账号，只赋予需要优化库的 ALTER、SELECT 权限。
表引擎：碎片优化只对 InnoDB 和 MyISAM 表有效，如果你用的是其他引擎（如 Aria），请先查文档。
空闲时间窗口：优化操作会短暂锁定表（InnoDB 允许并发读写但有限制），建议选在业务低谷，比如凌晨 3 点。

编写碎片优化脚本（两种方式）

方式一：命令行脚本（适用所有 Linux 服务器）

创建一个文件 /root/optimize_db.sh，内容如下：

#!/bin/bash
# 自动碎片优化脚本
# 请将 DB_USER、DB_PASS、DB_HOST 替换为实际值

DB_USER="your_user"
DB_PASS="your_password"
DB_HOST="localhost"
DB_NAME="your_database"

mysql -u$DB_USER -p$DB_PASS -h$DB_HOST -e "SELECT CONCAT('OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND ENGINE IN ('InnoDB','MyISAM') AND DATA_FREE > 0;" | grep OPTIMIZE > /tmp/optimize_commands.sql

if [ -s /tmp/optimize_commands.sql ]; then
    mysql -u$DB_USER -p$DB_PASS -h$DB_HOST < /tmp/optimize_commands.sql
    echo "$(date)：碎片优化完成" >> /var/log/optimize_db.log
else
    echo "$(date)：无需优化" >> /var/log/optimize_db.log
fi

给脚本执行权限：

chmod +x /root/optimize_db.sh

注意：这段脚本先扫描指定库中所有有碎片的 InnoDB/MyISAM 表，再逐条执行 OPTIMIZE TABLE。
如果你要优化多个库，可以改成循环。

方式二：适用于宝塔面板

宝塔面板用户可以用计划任务的“Shell 脚本”功能，把上面脚本内容粘贴进去，省去登录服务器的麻烦。
路径：宝塔面板 → 计划任务 → 添加任务 → 任务类型选“Shell 脚本” → 执行周期设置每天 03:00。

设置定时执行

Linux 系统原生 crontab

crontab -e

加入一行：

0 3 * * * /root/optimize_db.sh >/dev/null 2>&1

表示每天凌晨 3 点执行。
保存后重启 cron：

systemctl restart crond

宝塔面板计划任务

在添加任务时，选择“每天”，时间填 03:00，脚本内容直接复制方式一的脚本（记得提前替换变量）。
宝塔会自动管理执行日志。

避坑与高频问题

问题1：脚本执行后没有效果怎么办？

先手动跑一条命令测试：

mysql -uroot -p -e "OPTIMIZE TABLE your_table;"

如果报权限错误，说明账号权限不足；
如果表特别大，可能出现超时，可以加参数 --quick 或增加 innodb_lock_wait_timeout。

问题2：碎片优化后磁盘空间没释放？

InnoDB 的碎片优化不会把空间还给操作系统，但会返还给表空间内部，后续插入可以重用。
如果需要真正回收磁盘，需要重建表（ALTER TABLE table_name ENGINE=InnoDB）。

问题3：担心影响业务？

建议先对从库或测试库跑一轮，或者在业务低峰手动执行。
如果表超过 100GB，优化耗时可能很长，可以拆成多个脚本分批处理。

如何验证优化已经生效

SELECT TABLE_NAME, ROUND(DATA_FREE/1024/1024,2) AS MB_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA='your_database';

DATA_FREE 代表表内碎片大小（单位 MB）。
优化后这个值应该明显下降，甚至变为 0。
同时观察你的慢查询日志，如果之前因碎片导致的慢 SQL 变快了，说明优化有效。

最后

数据库定期自动碎片优化提速定时执行脚本设置好后，基本就告别了手动维护。
建议先在测试环境跑一周，确认无异常再部署到生产。
如果你同时有多个数据库或需要更精细的优化策略（如只优化碎片超过 100MB 的表），可以在此基础上继续扩展脚本逻辑。

闲置住宅算力出租AI中转副业完整运营教程

AIOps智能故障自愈服务器自动处理各类告警