数据库定时优化碎片提升读写查询性能速度脚本
为什么你的数据库需要定时优化碎片
数据库在持续写入、更新和删除记录时，表文件内部会产生很多不连续的空隙，这就是碎片。
碎片太多会导致读取时扫描更多无用数据，写入变慢，查询响应时间增加。
通过定期执行碎片优化（OPTIMIZE TABLE）可以整理物理存储、压缩空间、重建索引，从而明显提升读写查询性能速度。
很多生产环境的数据库维护都会加入这样一个定时脚本。
准备工作：确认环境和备份
在部署脚本前请确认以下几点：
- 确保 MySQL/MariaDB 版本在 5.7 及以上（mysqlcheck 命令通用）。
- 记录数据库 root 密码或拥有所有库权限的用户名密码。
- 强烈建议在首次优化前全量备份数据库：
mysqldump -u root -p --all-databases > /backup/full_dump_$(date +%F).sql - 检查磁盘剩余空间至少为最大表文件大小的 1.5 倍，因为 OPTIMIZE 会创建临时副本。
一键碎片优化脚本：基于 mysqlcheck
mysqlcheck 是 MySQL 自带的维护工具，其中的 -o 参数可直接对表执行 OPTIMIZE。
下面的脚本可以优化所有数据库的所有表，并在执行前打印日志，方便后期排查。
#!/bin/bash
# 数据库连接信息
DB_USER="root"
DB_PASS="your_password"
DB_HOST="localhost"
LOG_FILE="/var/log/db_optimize.log"
echo "=== $(date) 开始优化数据库碎片 ===" >> $LOG_FILE
# 利用 mysqlcheck 优化所有库的所有表
mysqlcheck -u $DB_USER -p$DB_PASS -h $DB_HOST -o --all-databases --skip-lock-tables >> $LOG_FILE 2>&1
echo "=== $(date) 优化结束 ===" >> $LOG_FILE参数说明：-o表示 OPTIMIZE TABLE；--skip-lock-tables避免在运行期间锁定所有表（但建议在低峰期执行）；如果使用 InnoDB 引擎，这个参数能减少锁影响。
将脚本保存为 /usr/local/bin/db_optimize.sh，然后赋予执行权限：chmod +x /usr/local/bin/db_optimize.sh。
配置定时任务：crontab 示例
使用 crontab -e 编辑当前用户的定时任务，加入以下行：
# 每周日凌晨 2:30 执行数据库碎片优化
30 2 * * 0 /usr/local/bin/db_optimize.sh保存后生效。
crontab 时间格式为“分 时 日 月 周”，以上示例表示每周日凌晨 2 点 30 分执行。
如果担心优化耗时过长，可搭配 timeout 命令限制最长时间，例如：30 2 * * 0 timeout 3600 /usr/local/bin/db_optimize.sh（最多执行 1 小时）。
避坑指南与常见问题
Q1：优化过程中网站访问变慢怎么办？
优化操作会消耗 I/O 和 CPU，并对优化中的表施加写锁。建议在访客最少的时段执行，或先优化读多写少的库。如果用的是 InnoDB 引擎且版本较新，可考虑使用 ALTER TABLE ... ENGINE=InnoDB（会重建表，但锁粒度略好），不过脚本方案更简单。
Q2：执行时遇到“The total number of locks exceeds the lock table size”错误？
说明 innodb_buffer_pool_size 不够大或并发锁太多。可以在低峰期使用 --quick 参数（mysqlcheck -o --quick --all-databases）逐表处理，避免一次锁定过多。
Q3：脚本运行后没有任何输出？
检查日志文件路径是否有写入权限，以及数据库连接密码是否正确。可以手动执行一次脚本并观察终端输出。
Q4：优化后查询速度反而变慢？
可能是统计信息未更新，执行 ANALYZE TABLE 后观察，或重启 MySQL 服务。另外，如果表本身碎片极少，优化带来的收益不明显。建议每月执行 1-2 次即可。
效果验证：如何确认优化生效
执行完脚本后，可以通过以下两个方法直观验证：
- 查看表文件大小：登录 MySQL 执行
SELECT table_schema, table_name, ROUND((data_length+index_length)/1024/1024,2) AS total_mb, ROUND(data_free/1024/1024,2) AS free_mb FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql','performance_schema');，优化前后对比 free_mb 应大幅减少。 - 观察慢查询日志：开启 MySQL 慢查询日志，优化后同一查询的耗时若明显下降，则证明碎片整理有效。
如果你的数据库持续面临读写慢的问题，不妨先按照本文步骤部署这套数据库定时优化碎片脚本，再结合实际情况微调执行频率和参数。
遇到异常时优先查看日志文件 /var/log/db_optimize.log，大多问题都能在日志中找到线索。
