MySQL 5.7 中 OPTIMIZE TABLE 对 InnoDB 表本质是执行 ALTER TABLE ... ENGINE=InnoDB 重建表，仅对独立表空间（innodb_file_per_table=ON）有效，无法收缩共享表空间 ibdata1，且全程加锁阻塞 DML。
直接说结论：MySQL 5.7 中可以用存储过程批量调用 OPTIMIZE TABLE，但必须注意引擎差异、锁表影响和 InnoDB 的实际行为——它本质是重建表，不是“整理”，且不会收缩共享表空间 ibdata1。
为什么 OPTIMIZE TABLE 在 InnoDB 里会变成 ALTER TABLE ... ENGINE=InnoDB
MySQL 5.7 对 InnoDB 表执行 OPTIMIZE TABLE 时，内部会自动转换为 ALTER TABLE tbl_name ENGINE=InnoDB（带 ANALYZE），这是官方定义的行为。这意味着：
- 操作会重建整张表：生成新
.ibd文件，把数据按聚簇索引顺序重写，释放未用页，合并碎片 - 仅对启用独立表空间（
innodb_file_per_table=ON）的表有效；若用共享表空间，Data_free可能清零，但磁盘空间不会返还给操作系统 - 过程中表被加
EXCLUSIVE锁，DML 阻塞，线上大表慎用 - 执行后
information_schema.tables.Data_free通常变为 0，但需配合ANALYZE TABLE更新统计信息
存储过程里调用 OPTIMIZE TABLE 的关键陷阱
你看到的网上常见存储过程（比如循环查 information_schema.tables 后拼 OPTIMIZE）能跑通，但容易踩这些坑：
- 拼接 SQL 时没转义库名/表名，遇到中划线、数字开头或保留字（如
order）直接报错 —— 必须用反引号包裹：CONCAT('OPTIMIZE TABLE `', db_name, '`.`', @tb_name, '`') - 没检查
ENGINE类型，对MEMORY或COLUMNSTORE等不支持引擎执行会失败，应先过滤：AND engine IN ('InnoDB', 'MyISAM') - 没判断
data_free > 0就盲目优化，小表或刚清理过的表反复执行纯属浪费资源和锁表时间 - 没设超时或重试逻辑，某张表卡住（如正在被长事务占用）会导致整个存储过程中断，后续表全跳过
- 没加
FLUSH TABLES或FLUSH TABLES WITH READ LOCK前置动作，可能导致information_schema查询结果滞后（尤其在高并发 DDL 场景下）
一个更稳妥的存储过程模板（含防错与日志）
以下是一个生产可用的简化版，聚焦核心逻辑，去掉花哨包装：
DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_optimize_fragmented_tables`$$
CREATE PROCEDURE `sp_optimize_fragmented_tables`(
IN target_db VARCHAR(64),
IN min_data_free_mb INT DEFAULT 16
)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tb_name VARCHAR(64);
DECLARE tb_engine VARCHAR(16);
DECLARE tb_data_free BIGINT;
<p>-- 游标只查真正有碎片且支持优化的表
DECLARE cur CURSOR FOR
SELECT table_name, engine, data_free
FROM information_schema.tables
WHERE table_schema = target_db
AND engine IN ('InnoDB', 'MyISAM')
AND data_free > min_data_free_mb <em> 1024 </em> 1024
AND table_type = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;</p><p>OPEN cur;
read_loop: LOOP
FETCH cur INTO tb_name, tb_engine, tb_data_free;
IF done THEN
LEAVE read_loop;
END IF;</p><pre class='brush:php;toolbar:false;'>SET @sql = CONCAT('OPTIMIZE TABLE `', target_db, '`.`', tb_name, '`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 可选：记录日志到自建表，或用 SELECT 输出
SELECT CONCAT('Optimized: ', tb_name, ' (free=', ROUND(tb_data_free/1024/1024, 2), 'MB)') AS log;END LOOP; CLOSE cur; END$$ DELIMITER ;
调用方式：CALL sp_optimize_fragmented_tables('myapp', 32); —— 只处理碎片超过 32MB 的 InnoDB/MyISAM 表。
比存储过程更值得优先考虑的替代方案
存储过程看似自动化，但实际运维中往往不如以下方式省心：
-
mysqlcheck -o -u root -p mydb：命令行一键优化整个库，支持并行（-c）、跳过某些表（--ignore-table），还能结合cron定时跑，无需进 MySQL 执行 - 监控驱动：用脚本定期查
SELECT table_name, ROUND(data_free/1024/1024, 2) free_mb FROM information_schema.tables WHERE data_free > 100*1024*1024，只对真正需要的表发OPTIMIZE，避免无差别轮询 - 架构层规避：对高频删改的流水表，改用按时间分区（
PARTITION BY RANGE），清旧数据直接ALTER TABLE ... DROP PARTITION，秒级完成且不锁表、不产碎片
真正难的不是写存储过程，而是判断哪张表该什么时候优化——Data_free 大不代表一定慢，Rows 少但 Avg_row_length 波动大才更危险。别让自动化掩盖了对数据访问模式的理解。
