MYSQL优化 Analyze Table

Analyze Table(分析表)

MySQL 的Optimizer（优化元件）在优化SQL语句时，首先需要收集一些相关信息，其中就包括表的cardinality（散列程度），它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。

我们可以使用SHOW INDEX语句来查看索引的散列程度：
SHOW INDEX FROM TABLE;

索引信息中的列的信息说明：
Table :表的名称。
Non_unique:如果索引不能包括重复词，则为0。如果可以，则为1。
Key_name:索引的名称。
Seq_in_index:索引中的列序列号，从1开始。
Column_name:列名称。
Collation:列以什么方式存储在索引中。在MySQLSHOW INDEX语法中，有值’A’（升序）或NULL（无分类）。
Cardinality:索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
Sub_part:如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed:指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null:如果列含有NULL，则含有YES。如果没有，则为空。
Index_type：存储索引数据结构方法（BTREE, FULLTEXT, HASH, RTREE）

下面我们通过Analyze Table语句来修复索引：
ANALYZE TABLE TABLE;
SHOW INDEX FROM TABLE;

使用ANALYZE TABLE分析表的过程中，数据库系统会对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。 ${\color{red}{\text{ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表。}}}$

需要注意的是，如果开启了binlog，那么Analyze Table的结果也会写入binlog，我们可以在analyze和table之间添加关键字local取消写入。

Check Table(检查表)

CHECK TABLE 表名1 [,表名2…] [option] ;
其中，option参数有5个参数，分别是QUICK、FAST、CHANGED、MEDIUM和EXTENDED。这5个参数的执行效率依次降低。CHECK TABLE语句在执行过程中也会给表加上只读锁。

option有一下几个选项：
UPGRADE：用来测试在更早版本的MySQL中建立的表是否与当前版本兼容。
QUICK：速度最快的选项，在检查各列的数据时，不会检查链接（link）的正确与否，如果没有遇到什么问题，可以使用这个选项。
FAST：只检查表是否正常关闭，如果在系统掉电之后没有遇到严重问题，可以使用这个选项。
CHANGED：只检查上次检查时间之后更新的数据。
MEDIUM：默认的选项，会检查索引文件和数据文件之间的链接正确性。
EXTENDED：最慢的选项，会进行全面的检查。

${\color{red}{\text{option选项只对MyISAM类型的表有效，对InnoDB类型的表无效。}}}$

Optimize Table(优化表)

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
与ANALYZE TABLE一样，OPTIMIZE TABLE也可以使用local来取消写入binlog。

如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。【当您的库中删除了大量的数据后，您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。】

${\color{red}{\text{OPTIMIZE TABLE只对MyISAM, BDB和InnoDB类型的表起作用。}}}$
对于BDB表，OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。

${\color{red}{\text{注意：在OPTIMIZE TABLE运行过程中，MySQL会锁定表。}}}$

${\color{blue}{\text{对于myisam可以直接使用 optimize table table.name, }}}$ 当是InnoDB引擎时，会报“Table does not support optimize, doing recreate + analyze instead”，一般情况下，由myisam转成innodb，会用alter table table.name engine='innodb'进行转换，优化也可以用这个。 ${\color{blue}{\text{所以当是InnoDB引擎时，我们就用alter table table.name engine='innodb'来代替optimize做优化就可以。}}}$

查看前后效果可以使用show table status命令,例如show table status from [database] like '[table_name]';返回结果中的data_free即为空洞所占据的存储空间。

Repair Table(修复表)

repair table 表名 [option]
与Analyze Table一样，Repair Table也可以使用local来取消写入binlog。

option有一下几个选项：
QUICK：最快的选项，只修复索引树。
EXTENDED：最慢的选项，需要逐行重建索引。
USE_FRM：用在.MYI文件丢失或者头部受到破坏的情况下。利用.frm的定义来重建索引

多数情况下,简单得用repair table tablename不加选项就可以搞定问题。但是当.MYI文件丢失或者头部受到破坏时,这样的方式不管用。
${\color{red}{\text{只对MyISAM和ARCHIVE类型的表有效。}}}$

https://www.cnblogs.com/huojing/articles/3971107.html
https://blog.csdn.net/chenpeng19910926/article/details/79739722
https://blog.csdn.net/ZYC88888/article/details/85054064

MYSQL优化 Analyze Table

Analyze Table(分析表)

需要注意的是，如果开启了binlog，那么Analyze Table的结果也会写入binlog，我们可以在analyze和table之间添加关键字local取消写入。

Check Table(检查表)

Optimize Table(优化表)

Repair Table(修复表)

需要注意的是，如果开启了binlog，那么`Analyze Table`的结果也会写入binlog，我们可以在analyze和table之间添加关键字local取消写入。