网站导航
> 技术文档 > MySQL 的ANALYZE与 OPTIMIZE命令_mysql analyze table

MySQL 的ANALYZE与 OPTIMIZE命令_mysql analyze table

网友: 技术文档 2025-09-03 04:25:09

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_statisticsWHERE 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] TABLE tbl_name [, tbl_name] ...

作用（根据存储引擎不同）：

  • InnoDB：重建表，整理碎片（实际是ALTER TABLE的包装）
  • MyISAM：修复碎片、排序索引、更新统计
  • ARCHIVE：重新压缩表数据

2. 使用场景

-- 单表优化OPTIMIZE TABLE order_archive;-- 批量优化所有表SELECT CONCAT(\'OPTIMIZE TABLE \', table_name, \';\')FROM information_schema.tablesWHERE 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_ratioFROM information_schema.tablesWHERE 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_cmdFROM information_schema.tablesWHERE table_schema = @dbAND engine = \'InnoDB\';SELECT CONCAT(\'OPTIMIZE TABLE \', table_name, \';\') AS optimize_cmdFROM ( SELECT table_name, data_free / (data_length + index_length) AS frag_ratio FROM information_schema.tables WHERE table_schema = @db AND engine = \'InnoDB\' AND data_length > 0) t WHERE frag_ratio > @threshold;

2. 生产环境注意事项

  1. 避开高峰期：在低负载时段执行OPTIMIZE
  2. 备份优先：执行前确保有有效备份
  3. 监控进度： 
    watch -n 1 \"mysql -e \'SHOW PROCESSLIST\' | grep -i optimize\"
  4. 考虑替代方案： 
    -- 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_statisticsWHERE table_schema = \'mydb\';-- 碎片率监控视图CREATE VIEW frag_monitor ASSELECT table_schema, table_name, ROUND(data_free/(1024*1024),2) AS frag_mb, ROUND(data_free/(data_length+index_length)*100,2) AS frag_pctFROM information_schema.tablesWHERE data_length > 0ORDER 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_BINLOGOPTIMIZE 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数据库性能稳定。对于关键业务表，建议建立定期的统计信息收集和碎片整理计划，同时结合现代监控工具持续跟踪表健康状况。

网络标签：

相关问题