MySQL数据库定时优化，OPTIMIZE表脚本
MySQL数据库在长期运行后，表数据频繁增删改会产生碎片，导致查询变慢、存储空间浪费。
定期执行OPTIMIZE TABLE可以重新组织表数据和索引，回收未使用空间。
本文面向零基础用户，提供一套可定时执行的优化脚本，并说明配置、避坑和验证方法。
优化前需要确认的环境条件
OPTIMIZE TABLE并非所有存储引擎都支持。
InnoDB引擎在MySQL 5.6.17及以上版本支持在线DDL，但执行时仍会锁表，建议在业务低峰期进行。
MyISAM引擎会全程锁表，需格外谨慎。
操作前，请通过以下命令确认MySQL版本和存储引擎：
SELECT VERSION();
SHOW TABLE STATUS FROM 你的数据库名;重点检查Data_free列，它表示碎片占用的字节数。
如果该值较大（例如超过100MB），说明优化收益明显。
另外，确保执行脚本的MySQL账号具有SELECT、OPTIMIZE权限，并且服务器已安装crontab（Linux）或计划任务（Windows）。
编写OPTIMIZE表脚本
我们不建议直接对所有表执行OPTIMIZE，因为大表可能耗时过长并阻塞业务。
推荐只优化碎片率高的表。
以下是一个Shell脚本示例，保存为/root/optimize_mysql.sh：
#!/bin/bash
# MySQL连接信息
DB_USER="root"
DB_PASS="你的密码"
DB_NAME="你的数据库名"
# 排除不需要优化的表，用空格分隔
EXCLUDE_TABLES="logs temp_data"
# 获取碎片大于100MB的表
TABLES=$(mysql -u$DB_USER -p$DB_PASS -N -e "SELECT table_name FROM information_schema.tables WHERE table_schema='$DB_NAME' AND data_free > 104857600 AND engine='InnoDB'")
for TABLE in $TABLES; do
# 跳过排除表
if echo "$EXCLUDE_TABLES" | grep -qw "$TABLE"; then
continue
fi
echo "Optimizing $TABLE ..."
mysql -u$DB_USER -p$DB_PASS -e "OPTIMIZE TABLE \`$DB_NAME\`.\`$TABLE\`"
done给脚本添加执行权限：chmod +x /root/optimize_mysql.sh。
配置crontab定时任务
使用crontab -e编辑当前用户的定时任务，加入一行，表示每周日凌晨3点执行：
0 3 * * 0 /root/optimize_mysql.sh >> /var/log/mysql_optimize.log 2>&1保存后，通过crontab -l查看是否生效。
日志文件/var/log/mysql_optimize.log会记录每次优化的表名和结果。
如果使用宝塔面板，可以在“计划任务”中添加Shell脚本，执行周期选择“每周”或“每天”，脚本内容填入上述脚本路径。
避坑指南：这些情况不要用OPTIMIZE
大表在业务高峰期执行会锁表，导致请求堆积。
务必在低峰期运行，或使用pt-online-schema-change等在线工具。
MyISAM表优化时会全程锁定，如果表很大，可能造成长时间不可用。
建议先转换为InnoDB。
不要对系统表执行OPTIMIZE，例如mysql库中的表。
脚本中应明确指定业务数据库。
如果表数据量小但碎片多，优化很快完成；
反之，大表可能耗时数小时。
建议先在测试环境评估时间。
密码明文写在脚本中存在安全风险，可以改用~/.my.cnf配置文件，并设置权限600。
如何验证优化效果
执行脚本后，再次查询information_schema.tables中的Data_free，观察是否显著下降。
同时，可以用SHOW TABLE STATUS LIKE '表名'查看Data_free和Data_length的变化。
业务层面，关注慢查询日志中相关表的查询时间是否缩短。
如果优化后性能没有改善，可能碎片不是瓶颈，需要检查索引或SQL语句。
定期检查日志文件/var/log/mysql_optimize.log，确认没有报错。
如果出现“Table does not support optimize”提示，说明存储引擎不支持，需要调整脚本过滤条件。
常见疑问
OPTIMIZE TABLE和ALTER TABLE ENGINE=InnoDB有什么区别？
两者都能整理碎片，但OPTIMIZE更直接，且会更新统计信息。对于InnoDB，OPTIMIZE实际上会重建表，效果类似。
优化期间数据库会中断吗？
InnoDB在MySQL 5.6.17后支持在线DDL，但仍有短暂锁表；MyISAM会全程锁表。建议在维护窗口执行。
多久执行一次比较合适？
根据业务写入频率决定。通常每月或每季度一次。如果表每天大量删除，可以每周一次，但需监控执行时间。
脚本执行后没有输出？
检查MySQL账号权限、密码是否正确，以及information_schema查询是否返回结果。可以手动运行脚本排查。
