什么时候用 ANALYZE TABLE ?
after large amounts of table data have changed大量表数据已更改后
when you want to update index distribution statistics当您要更新索引分布统计信息时
OPTIMIZE TABLE
Rebuild full text indexes by using OPTIMIZE TABLE. #用其重建全文索引。
By default, OPTIMIZE TABLE rebuilds the entire table.
The innodb_optimize_fulltext_only option ensures that OPTIMIZE TABLE rebuilds only the full text index.
使用方法:SET innodb_optimize_fulltext_only = 1; OPTIMIZE TABLE tablename;
OPTIMIZE TABLE还可以重新利用未使用的空间,并整理数据文件的碎片。
例如删除表中大量数据后,优化表后,释放通用表空间,系统表空间的磁盘占用。( 注意这会造成表锁。)
慎用这种操作,对不同引擎影响也不同,请参考:https://developer.aliyun.com/article/27856 https://www.cnblogs.com/shengdimaya/p/5920310.html
CHECK TABLE
1.to make sure a table has no structural problems
#.确保表格没有结构性问题
2.to make sure that no table indexes are corrupted
#.确保没有表索引被破坏
还有一个操作:
Can be more convenient than issuing SQL statements
• Works with InnoDB, MyISAM, and ARCHIVE tables
• Three levels of checking: 1. Table-specific 2.Database-specific 3.All databases
• Some mysqlcheck maintenance options:
• --analyze: Perform an ANALYZE TABLE.
• --check: Perform a CHECK TABLE (default).
• --optimize: Perform an OPTIMIZE TABLE.
• Examples
mysqlcheck -uroot -p employees employees salaries
mysqlcheck --login-path=admin --analyze --all-databases
以前文章里整理过类似的。。。
=============End,===============