数据库定期自动碎片优化提速脚本编写与实战
为什么要给数据库做定期碎片优化
MySQL 数据库运行一段时间后，频繁的增删改操作会让表和索引内部产生大量碎片（不连续的数据页）。
这些碎片会增加磁盘 I/O，拖慢查询和写入速度，尤其在 InnoDB 引擎上表现明显。
手工执行 OPTIMIZE TABLE 能重建表并回收空间，但靠人脑记住每周做一次不现实。
本文会带你编写一个小脚本，配合系统定时任务，让数据库在业务低峰期自动完成碎片整理，省心又安全。
你需要准备什么
- 一台装有 MySQL 或 MariaDB 的服务器（可使用宝塔面板或命令行管理）
- 有权限操作
OPTIMIZE TABLE的数据库账号（建议用 root 或单独的管理账号） - 基本的命令行操作能力（会登录服务器、编辑文件）
- 如果使用宝塔面板，可以直接用它的计划任务功能代替 crontab
编写自定义碎片优化脚本
我们写一个 Shell 脚本，逻辑如下：
- 通过
mysql命令连接数据库。 - 从
information_schema.TABLES中选出碎片率（Data_free / Data_length）超过一定比例的表。 - 对符合条件的表依次执行
OPTIMIZE TABLE并记录结果到日志。 - 如果某张表碎片率很低则跳过，避免频繁重建锁定。
#!/bin/bash
# 数据库连接参数（请根据实际修改）
DB_USER="root"
DB_PASS="your_password"
DB_HOST="localhost"
DB_NAME="your_database"
LOG_FILE="/var/log/optimize_$(date +%Y%m%d_%H%M%S).log"
# 碎片率阈值（百分比），建议 25 以上才优化
THRESHOLD=25
# 查询需要优化的表
SQL="SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS t
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '$DB_NAME'
AND DATA_LENGTH > 0
AND (DATA_FREE / DATA_LENGTH) * 100 > $THRESHOLD
ORDER BY (DATA_FREE / DATA_LENGTH) DESC;"
# 执行查询并循环处理
mysql -u$DB_USER -p$DB_PASS -h$DB_HOST -e "$SQL" -N | while read table_name; do
echo "[$(date '+%Y-%m-%d %H:%M:%S')] Starting optimize $table_name" >> $LOG_FILE
mysql -u$DB_USER -p$DB_PASS -h$DB_HOST -e "OPTIMIZE TABLE $table_name;" >> $LOG_FILE 2>&1
echo "[$(date '+%Y-%m-%d %H:%M:%S')] Finished optimize $table_name" >> $LOG_FILE
done
echo "[$(date '+%Y-%m-%d %H:%M:%S')] All tasks completed." >> $LOG_FILE注意：脚本中密码明文写在脚本里有一定风险，生产环境建议使用.my.cnf配置文件或加密工具。示例中的-p$DB_PASS可替换为--defaults-file=/etc/my.cnf这种更安全的方式。
部署自动定时任务
方式一：crontab（纯命令行环境）
- 将上面的脚本保存为
/usr/local/bin/optimize_db.sh，并给予执行权限：
chmod +x /usr/local/bin/optimize_db.sh- 编辑 crontab：
crontab -e- 添加一行（每周日凌晨 3 点执行）：
0 3 * * 0 /usr/local/bin/optimize_db.sh- 保存退出。
方式二：宝塔面板计划任务
- 登录宝塔面板，点击左侧“计划任务”。
- 添加任务 – 任务类型选“Shell脚本”。
- 任务名称填“数据库碎片优化”，执行周期选“每周”，具体时间建议凌晨 2-4 点之间。
- 脚本内容粘贴上面的完整脚本（注意修改数据库账号密码）。
- 点击“执行”测试一次，确认日志目录能正常写入。
避坑指南（一定先看再执行）
- 不要在业务高峰期运行：
OPTIMIZE TABLE会锁表（InnoDB 为元数据锁），期间该表的所有读写都会阻塞。建议选在凌晨低峰期。 - 确保磁盘空间充足：优化过程需要临时空间（约原表大小 1.1 倍），空间不足会报错
Table rebuild failed。 - 大表可能会很久：几十 GB 的表优化可能需要几十分钟甚至几小时，crontab 里要预留足够时间窗口，或者只针对碎片率高的小表。
- 日志膨胀处理：每次执行都生成一个新日志文件，建议定期清理，比如 crontab 里再加一条
find /var/log/ -name 'optimize_*.log' -mtime +30 -delete。
如何验证优化效果
- 查看日志：执行
cat /var/log/optimize_*.log，看是否有“OK”状态。 - 检查碎片率是否下降：登录 MySQL 执行以下 SQL：
SELECT TABLE_NAME, ROUND((DATA_FREE/DATA_LENGTH)*100,2) AS fragmentation
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='your_database';- 对比查询速度：优化前用
EXPLAIN和SELECT COUNT(*)记录耗时，优化后跑同样的 SQL，通常会发现 I/O 等待时间下降。 - 检查表大小是否缩小：若碎片率原本很高，优化后
DATA_LENGTH一般会减少，说明空间已回收。
高频问题解答
Q：脚本一直报错“Access denied for user”，怎么办？
A：检查 MySQL 账号是否有 PROCESS、SELECT、OPTIMIZE 权限。可以用 root 先授权：GRANT PROCESS, SELECT, INSERT ON *.* TO 'your_user'@'localhost'; 然后刷新权限。
Q：一张表很大，碎片率只有 10%，要不要优化？
A：不建议。InnoDB 引擎的碎片整理很重，阈值设为 25%-30% 比较合理，避免频繁重建影响性能。
Q：宝塔计划任务执行后没有日志生成？
A：检查日志目录是否存在（例如 /var/log/），以及脚本中写的路径是否有写权限。可以把日志路径临时改为 /tmp/ 测试。
Q：脚本里所有表都处理，但我只想优化某个数据库下的几张核心表，怎么改？
A：把 SQL 中的 TABLE_SCHEMA = '$DB_NAME' 改成具体的库名，再加一个条件 AND TABLE_NAME IN ('table1','table2') 即可。
