MySQL 的ANALYZE与 OPTIMIZE命令
MySQL 的ANALYZE与 OPTIMIZE命令
一、ANALYZE TABLE - 更新统计信息
1. 基本语法与功能
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...
作用：收集表统计信息用于优化器生成更优的执行计划，主要更新：
- 索引基数（cardinality）
- 数据分布直方图（MySQL 8.0+）
- 表的存储引擎统计信息
2. 使用场景
-- 单表分析
ANALYZE TABLE customers;-- 多表分析（适用于批量维护）
ANALYZE TABLE orders, order_items;-- 不写入二进制日志（主从复制环境）
ANALYZE NO_WRITE_TO_BINLOG TABLE large_table;
3. 执行效果验证
-- 查看索引统计信息
SHOW INDEX FROM customers;-- 查看直方图信息（MySQL 8.0+）
SELECT * FROM information_schema.column_statistics
WHERE table_name = 'customers';
4. 自动分析配置
-- 查看自动分析设置
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';-- 设置自动分析阈值（默认10%变化触发）
SET GLOBAL innodb_stats_persistent_sample_pages = 200;
ALTER TABLE customers STATS_SAMPLE_PAGES = 500;
二、OPTIMIZE TABLE - 表优化重组
1. 基本语法与功能
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLEtbl_name [, tbl_name] ...
作用（根据存储引擎不同）：
- InnoDB：重建表，整理碎片（实际是ALTER TABLE的包装）
- MyISAM：修复碎片、排序索引、更新统计
- ARCHIVE：重新压缩表数据
2. 使用场景
-- 单表优化
OPTIMIZE TABLE order_archive;-- 批量优化所有表
SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'mydb'
AND engine = 'InnoDB'
INTO OUTFILE '/tmp/optimize_tables.sql';
SOURCE /tmp/optimize_tables.sql;
3. 执行效果验证
-- 查看表碎片率（InnoDB）
SELECT table_name, data_free / (data_length + index_length) AS frag_ratio
FROM information_schema.tables
WHERE table_schema = 'mydb'
AND data_length > 0;-- 优化前后性能对比
EXPLAIN ANALYZE SELECT * FROM large_table WHERE create_time > '2023-01-01';
4. 替代方案（避免锁表）
-- 使用pt-online-schema-change工具（Percona Toolkit）
pt-online-schema-change --alter="ENGINE=InnoDB" D=mydb,t=large_table-- 使用gh-ost工具（GitHub）
gh-ost --alter="ENGINE=InnoDB" --database=mydb --table=large_table
三、核心区别对比
| 特性 | ANALYZE TABLE | OPTIMIZE TABLE |
|---|---|---|
| 主要目的 | 更新统计信息 | 物理重组表结构 |
| 锁级别 | 通常仅读锁 | 表锁（InnoDB为MDL锁） |
| 执行时间 | 通常较快 | 大表可能很慢 |
| 存储引擎影响 | 所有引擎都需要 | 不同引擎效果不同 |
| 空间回收 | 不会回收空间 | 可能回收空间 |
| 自动触发机制 | 有（innodb_stats_auto_recalc） | 无 |
四、最佳实践指南
1. 维护计划建议
-- 每周维护脚本示例
SET @db = 'mydb';
SET @threshold = 0.3; -- 碎片率阈值SELECT CONCAT('ANALYZE TABLE ', table_name, ';') AS analyze_cmd
FROM information_schema.tables
WHERE table_schema = @db
AND engine = 'InnoDB';SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';') AS optimize_cmd
FROM (SELECT table_name, data_free / (data_length + index_length) AS frag_ratioFROM information_schema.tablesWHERE table_schema = @dbAND engine = 'InnoDB'AND data_length > 0
) t WHERE frag_ratio > @threshold;
2. 生产环境注意事项
- 避开高峰期：在低负载时段执行OPTIMIZE
- 备份优先：执行前确保有有效备份
- 监控进度：
watch -n 1 "mysql -e 'SHOW PROCESSLIST' | grep -i optimize" - 考虑替代方案：
-- InnoDB碎片整理替代方案 ALTER TABLE large_table ENGINE=InnoDB;-- 使用Percona的pt-index-usage分析索引 pt-index-usage /var/lib/mysql/mysql-slow.log
3. 性能监控指标
-- 查询效率变化监控
SELECT * FROM sys.schema_table_statistics
WHERE table_schema = 'mydb';-- 碎片率监控视图
CREATE VIEW frag_monitor AS
SELECT table_schema, table_name, ROUND(data_free/(1024*1024),2) AS frag_mb,ROUND(data_free/(data_length+index_length)*100,2) AS frag_pct
FROM information_schema.tables
WHERE data_length > 0
ORDER BY frag_mb DESC;
五、常见问题解决方案
1. 长时间阻塞问题
-- 查看阻塞会话
SELECT * FROM performance_schema.threads
WHERE PROCESSLIST_COMMAND = 'Query'
AND PROCESSLIST_STATE LIKE '%optimize%';-- 安全终止优化操作
KILL [process_id];
2. 空间不足问题
# 检查磁盘空间
df -h /var/lib/mysql# 临时更改tmpdir（需要重启）
[mysqld]
tmpdir = /mnt/bigtmp
3. 复制环境处理
-- 从库延迟监控
SHOW SLAVE STATUS\G-- 使用NO_WRITE_TO_BINLOG
OPTIMIZE NO_WRITE_TO_BINLOG TABLE audit_log;
4. 大表优化策略
# 分块优化（使用pt-archiver）
pt-archiver --source h=localhost,D=mydb,t=large_table \--purge --where "1=1" --limit 1000 --commit-each
通过合理使用ANALYZE TABLE和OPTIMIZE TABLE，可以保持MySQL数据库性能稳定。对于关键业务表，建议建立定期的统计信息收集和碎片整理计划，同时结合现代监控工具持续跟踪表健康状况。