数据库定时碎片优化脚本自动维护数据库性能

数据库使用久了，频繁的插入、更新和删除操作会让表文件内部产生大量碎片。
碎片不会直接让数据丢失，但会让表占用更多磁盘空间，扫描行数变多，查询速度逐渐变慢。
本文要解决的就是这个场景：用一段可自动执行的脚本，定期对数据库表做碎片整理，让数据库长时间保持在一个相对健康的状态。
全文以 MySQL 为例，其余数据库思路类似，零基础用户可以跟着命令直接操作。

先把数据库碎片情况摸清楚

在动手写脚本前，先确认你的数据库当前碎片有多严重，同时判断是否有必要做定时优化。
登录 MySQL 后执行下面这条查询，可以看到指定数据库下每张表的数据大小、索引大小和数据碎片大小：

SELECT table_name, data_length, index_length, data_free 
FROM information_schema.tables 
WHERE table_schema = 'your_db' 
ORDER BY data_free DESC;

data_free 字段的单位是字节，数值越大说明碎片越多。
一般建议在 data_free 超过表总大小 30% 或持续增长时，再考虑加入定时优化。
如果每张表都很小，碎片量也不大，就没必要天天跑脚本，避免白白消耗服务器资源。

另外确认一下 MySQL 是否安装了 mysqlcheck 工具，它是 MySQL 自带的维护工具，通常安装 MySQL 后就有。
可以用 which mysqlcheck 检查，没有的话用系统自带的包管理器补装即可。

写一个安全的碎片优化脚本

脚本的核心逻辑并不复杂：先用 mysqlcheck 检查所有表的碎片情况，再对需要优化的表执行 OPTIMIZE TABLE。
为了不让大表优化卡死业务，建议拆成两步，并且每次处理一张表。

下面是一个适合放入 crontab 的完整脚本示例，保存为 /usr/local/bin/db_optimize.sh：

#!/bin/bash
DB_USER='root'
DB_PASS='你的密码'
DB_NAME='your_db'
LOG_FILE='/var/log/db_optimize.log'

# 获取所有表名
TABLES=$(mysql -u"$DB_USER" -p"$DB_PASS" -N -e "SHOW TABLES FROM $DB_NAME" 2>/dev/null)

for TABLE in $TABLES; do
    # 获取该表的碎片大小
    FREE=$(mysql -u"$DB_USER" -p"$DB_PASS" -N -e "SELECT data_free FROM information_schema.tables WHERE table_schema='$DB_NAME' AND table_name='$TABLE'" 2>/dev/null)
    # 碎片超过 100MB（约 104857600 字节）才优化
    if [ "$FREE" -gt 104857600 ]; then
        echo "$(date '+%Y-%m-%d %H:%M:%S') 优化 $TABLE，碎片 $FREE 字节" >> "$LOG_FILE"
        mysqlcheck -u"$DB_USER" -p"$DB_PASS" --optimize "$DB_NAME" "$TABLE" >> "$LOG_FILE" 2>&1
    fi
    sleep 1
 done

注意把 DB_USER、DB_PASS、DB_NAME 换成你自己的信息。
脚本里设置了一个 100MB 的碎片阈值，避免频繁对微小碎片执行优化。sleep 1 是让每张表之间留出间隔，降低数据库压力。

给脚本添加可执行权限并先手动跑一遍：

chmod +x /usr/local/bin/db_optimize.sh
/usr/local/bin/db_optimize.sh

然后查看日志文件 /var/log/db_optimize.log，确认没有报错，并且能看到哪些表被优化过。
如果日志为空，说明当前没有超过阈值的表，脚本在按预期工作。

用 crontab 让它每天自动执行

脚本手动跑通后，就可以交给计划任务了。
执行 crontab -e，加入一行：

0 3 * * * /usr/local/bin/db_optimize.sh

这行配置的含义是每天凌晨 3 点运行一次优化脚本。
选择凌晨是为了避开业务高峰，避免 OPTIMIZE TABLE 在运行期间占用过多 IO 影响线上访问。
如果你的数据库是主从架构，建议在从库上执行优化，减少对主库写入性能的干扰。

保存后可以用 crontab -l 确认任务已生效。
注意 crontab 中的环境变量和手动执行时不一样，如果脚本里依赖 mysql 命令的完整路径，建议在脚本开头加上 export PATH=/usr/local/mysql/bin:$PATH 或使用绝对路径。

这些坑一定要避开

不要在业务高峰期跑优化。 OPTIMIZE TABLE 会锁表，数据量大的表优化时间可能很长，期间相关表的写入和查询都会被阻塞。
建议固定到凌晨低峰执行。

不要对所有表一刀切。 有些表只有几万行，碎片再大也不值得优化。
设置阈值能减少无意义的锁表操作。
同时，含有大字段或者经常全文检索的表，优化时间会更久，需要单独评估。

不要把数据库密码直接写死在明文脚本里。 如果脚本文件被其他用户读取，密码就会泄露。
建议使用 ~/.my.cnf 配置文件存放凭证，并设置 chmod 600 ~/.my.cnf。

注意磁盘空间是否充足。 优化过程中，MySQL 会重建表，可能需要临时占用等于该表大小的额外磁盘空间。
如果磁盘本身已经快满，先清理日志或扩容，再执行优化。

如何验证优化是否真的生效

优化结束后，重新执行开头那条查询 data_free 的 SQL，对比各表碎片值。
你会发现超过阈值的表 data_free 明显下降，甚至变成 0。
同时表数据文件大小也会缩减。

更直观的验证是在优化前后各执行一次 SHOW TABLE STATUS LIKE '你的表名'\G，重点看 Data_free 字段。
另外，如果你之前发现某个慢查询和碎片相关，优化后重新跑一遍同一条 SQL，观察执行时间有没有下降。

日志会持续记录每次自动优化的内容，遇到异常时先看 /var/log/db_optimize.log 里的错误信息，再根据报错关键词去排查。
建议每隔一段时间检查一次脚本是否还在正常运行，因为服务器重启、MySQL 版本升级或密码变更都可能导致计划任务失效。

数据库定时碎片优化脚本自动维护数据库性能，本身就是一个长期工作。
按本文把脚本和 crontab 配置好之后，每天凌晨会自动完成碎片检查与整理，你可以省下大量手工维护时间。
初次运行时多观察几次日志，确认稳定运行后再放心交给计划任务。
遇到异常时，优先回看避坑部分，大部分问题都出在锁表、路径和磁盘空间这三处。