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

知识分享

2026-07-31 05:40

15 阅读

数据库定期自动碎片优化提速定时脚本，就是利用Shell脚本和Linux的crontab定时任务，自动对MySQL/MariaDB中的表执行OPTIMIZE TABLE（或等效操作），从而回收碎片空间、重建索引，提升查询和写入性能。
本文面向零基础用户，从原理到实操，带你一步步完成配置，让数据库在无人值守的情况下也能保持高效运行。

碎片是什么？为什么需要定期优化

所谓数据库碎片，是指当数据频繁插入、更新、删除时，表物理存储变得不连续，导致查询时需扫描更多无效页，写入时也因页分裂而变慢。
MySQL的OPTIMIZE TABLE命令可以整理表空间、重建索引，释放碎片空间。
但对于InnoDB引擎，官方建议使用ALTER TABLE tablename ENGINE=InnoDB（会自动重建表）效果类似。
通过定时脚本自动执行，可以在业务低峰期完成优化，避免手工介入。

准备工作：环境与权限确认

在写脚本之前，先确认以下条件满足：

操作系统：本文以CentOS 7/8或Ubuntu 20.04为例，其他Linux发行版类似。
数据库：MySQL 5.7+或MariaDB 10.3+，需要root或拥有OPTIMIZE权限的用户。
工具：bash + mysql命令行客户端。
安全考虑：建议仅对用户业务库操作，排除mysql、sys、performance_schema等系统库。大表（超过10GB）优化耗时较长，需选择合适的时间窗口。

编写自动碎片优化Shell脚本

以下脚本实现了自动查找所有业务表并逐个执行OPTIMIZE TABLE：

#!/bin/bash
# db_optimize.sh - 数据库碎片优化脚本

DB_USER="root"
DB_PASS="your_password"
DB_HOST="localhost"
# 排除系统库（可根据需要增删）
EXCLUDE_DBS="mysql sys performance_schema information_schema"

# 获取所有用户数据库
databases=$(mysql -u"$DB_USER" -p"$DB_PASS" -h"$DB_HOST" -e "SHOW DATABASES;" | grep -v Database | grep -v -E "$EXCLUDE_DBS")

for db in $databases; do
    # 获取该库下所有非临时表
    tables=$(mysql -u"$DB_USER" -p"$DB_PASS" -h"$DB_HOST" -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$db' AND TABLE_TYPE='BASE TABLE';" | grep -v TABLE_NAME)
    for tbl in $tables; do
        echo "$(date) :: Optimizing $db.$tbl"
        mysql -u"$DB_USER" -p"$DB_PASS" -h"$DB_HOST" -e "OPTIMIZE TABLE $db.$tbl;" 2>&1 | grep -v 'Table does not support optimize'
    done
done

echo "$(date) :: Optimization completed."

注意：将脚本中的your_password替换为实际密码；更安全的方式是使用.my.cnf文件存放凭据，避免密码明文。对于InnoDB表，OPTIMIZE TABLE会重建表，效果等价于ALTER TABLE ... ENGINE=InnoDB，但会记录更多日志。

保存为/usr/local/bin/db_optimize.sh，并赋予执行权限：

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

配置crontab定时任务

使用crontab实现每周日凌晨3点自动执行：

crontab -e

添加以下行：

0 3 * * 0 /usr/local/bin/db_optimize.sh >> /var/log/db_optimize.log 2>&1

保存后，crontab会自动生效。
可通过crontab -l检查任务是否写入。

验证与避坑指南

如何验证执行成功

查看日志：tail -f /var/log/db_optimize.log，看到每张表的Optimizing记录和最后的completed。
检查状态：手动执行一次脚本，观察输出是否有OK或Table is already up to date。
对比优化前后表碎片率：查询information_schema.TABLES中Data_free字段，优化后应明显减小。

常见避坑点

大表优化时间过长：建议在脚本中加入单表超时限制，或跳过大于指定大小的表（如5GB）。
主从环境：优化操作会在主库上阻塞写入（对于MyISAM表甚至全表锁定），InnoDB虽然支持在线DDL但也会产生大量日志，建议在从库执行或先停止业务。
InnoDB表优化：MySQL 5.6后OPTIMIZE TABLE被映射为ALTER TABLE ... ENGINE=InnoDB，不需要重复执行。
脚本密码安全：强烈建议使用.my.cnf或mysql_config_editor存储凭据，不要在脚本中明文写密码。

常见问题解答

Q1：优化脚本执行时数据库会卡住吗？
如果全是InnoDB表，OPTIMIZE TABLE在MySQL 5.6+表现为在线DDL，只短暂请求元数据锁，不会长时间锁定全表。但建议仍安排在业务低峰期。

Q2：可以只优化指定数据库吗？
可以修改脚本中的数据库获取逻辑，例如硬编码数组databases=("mydb1" "mydb2")即可。

Q3：碎片优化多久执行一次合适？
一般每周一次即可；如果业务写入量极大（如每小时超过10万条），可增至每日一次。

Q4：脚本执行后磁盘空间会立刻释放吗？
对于InnoDB表，如果开启了innodb_file_per_table，空间会释放给操作系统；否则碎片空间只释放给表空间文件，不会缩小ibdata文件大小。建议为独立表空间模式。

大模型推理服务器散热降噪改造实操指南

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