60. analyze table和optimize table 、check table 、checksum table、repair table

1.Analyze table 　　

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [, col_name] ...
        [WITH N BUCKETS]

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    DROP HISTOGRAM ON col_name [, col_name] ...

　　1.analyze table 通常生成表的统计信息

　　2.analyze table 支持innodb、NOB和myiasm表，但是它不支持视图。

　　3.如果参数innodb_read_only被启用了，analyze table可能会失败，因为它不能更新在数据目录中表的统计信息

　　4.对于使用了innodb的表，analyze table操作将会更新key distribution(秘钥分配),failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0

　　5.analyze table 支持分区表

　　6.在analyze table 期间，表会被加上一个read lock

　　7.analyze table 会移除在缓存中的表的定义，而且它要求一个flush lock，因此，如果有长事务在执行的过程中，这个操作将会被阻塞，另外说明一下，analyze table 本身是quicklyd

　　8.默认情况下，服务器将analyze table操作会记录到binary log中以便去给副本去复制

　　9.对于innodb表，analyze table 将会更新索引树并相应的更新索引基数，注意这个索引基数只是一个估算值

　　10.可以通过启用innodb_stats_persisent使得analyze table收集的统计信息更精确和稳定。这个参数主要是对索引列数据进行重大更改后运行analyze table(自动,但是有性能损耗)

　　11.mysql中在join优化中，如果join不是走正确的索引，可以尝试运行analyze table，也可以在查询中使用force index强制使用特定索引。或者设置max_seeks_for_key系统变量以确保mysql更喜欢索引查找而不是表扫描。

　　12.analyze table 将会清除information_schema.innodb_sys_tablestats表中的信息，并将status_initialized列设置为未初始化，下次访问该表时会再次收集统计信息。

2.Optimize table　　　

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

　　（1）optimize table 重新组织表中的数据和关联索引数据的物理存储，目的在于减少存储空间和提高访问表时的i/o效率(有磁盘碎片整理功能)

　　（2）对于独立的表空间(innodb_file_per_table)的表，当有大量的插入、更新、删除操作时，使用optimize table将会重新组织表和索引，并且回收磁盘空间供操作系统使用。

　　（3）对于innodb表，optimize table 映射到alter table...force，它会重建表以更新索引统计信息并释放聚集索引中未使用的空间

　　innodb detailes

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

　　optimize table对常规和分区innodb表使用在线DDL，这减少了并发DML操作的停机时间，optimize table触发的表的重建就地完成，排他锁仅在操作的准备阶段和提交阶段被短暂使用。在准备阶段，更新元数据并创建中间表。在提交阶段，将提交的表元数据进行更改。

　　optimize table 不支持对fulltext索引的innodb表进行online ddl，而是使用表复制方法(copy)

　　碎片化问题：

　　1）innodb表预计会有一定程度的碎片化，innodb仅填充93%页面，它会为更新留出空间，而无需拆分页面

　　2）删除操作可能会留下间隙，使得页面的填充量低于预期，因此可以使用optimize table来进行优化

　　3）当有足够的空间可用时，对行的更新通常会重写同一页的数据，并且还需要取决于数据类型和行格式

　　4）在高并发的负载中可能会在索引中留下间隙，因为innodb通过MVCC机制保留了相同数据的多个版本。

 　　myisam表

　　 对于myisam表，optimize table主要有这样的功能

　　1）对于已经删除的或者split rows，将会修复这个表

　　2）如果索引页没有被排序，将会排序它

　　3）如果表的统计信息不是最新的(并且无法通过对索引进行排序来完成修复)，将会被更新

3.check table　

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option: {
    FOR UPGRADE
  | QUICK
  | FAST
  | MEDIUM
  | EXTENDED
  | CHANGED
}

　　1）check table 检查表的错误，也可以检查视图的问题，比如说视图定义的引用的表不再存在。

　　2）check table 需要create table权限

　　3）check table 支持分区表，可以使用alter table...check partition 去检查一个或者更多的分区。

　　4）check table语句可能会为每个检查的表生成许多行信息，最后一行的Msg_type值为status,Msg_text通常应该是OK，表已经是最新的意味表的存储引擎不需要检查表

4.checksum table  

CHECKSUM TABLE tbl_name [, tbl_name] ... [QUICK | EXTENDED]

　 1）cheksum table 报告表内容的校验和，可以使用此语句来验证在备份、回滚或者将数据恢复到已知状态的其它操作之前和之后的内容是否相同。

　 2）该语句需要这个select权限

　 3）该语句不支持视图，如果你用它检测视图的话，它总是会返回Null

　 4）对于不存在的表，它也会返回Null

　 5）在执行checksum 操作时，这个表 is locked with a read lock for innodb and myisam

　 性能注意事项:

　 1）默认情况下，该语句会逐行读取整个表并计算校验和。对于大型表，这可能需要很长时间，因此偶尔执行

　 2）checksum 值取决于表的行格式，如果这个行模式被修改了，这个checksum 也会被修改。

    重要提示：

　 如果checksum 两张表的结果不同，几乎肯定的是这两张表在某些方便是不一样的，但是，由于checksum table使用的散列函数不能保证不会发生冲突，所以两个不相同的表可能会产生相同的校验和

5. repair table　

REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]

　 repair table 修复可能被损坏的表，但是仅使用某些存储引擎。

　 尽管可能永远会用不到repair table，但是如果发生了灾难，该语句可能会从myisam表中取回你的所有数据。

　 repair table 检查表以查看是否需要升级，如果要的话，它将执行升级，请参考check table ... for upgrade

　 import：

• 在执行表修复之前对表进行备份，在某些情况下，该操作可能会导致数据丢失。
• 如果在执行repair table操作期间，服务器异常退出了，则必须在重启后对它立即执行repair table语句
• 如果源上的表损坏了并且你在其上运行repaire table,则对原始表任何更改都不会传播到副本上
• repaire  table仅适用于Myisam、archive和csv表

参考：

　　https://dev.mysql.com/doc/refman/8.0/en/table-maintenance-statements.html