MySQL中的分析表、检查表与优化表如何操作？

MySQL提供了分析表、检查表和优化表的语句。分析表主要是分析关键字的分布，检查表主要是检查表是否存在错误，优化表主要是消除删除或者更新造成的空间浪费。

【1】分析表

MySQL中提供了 ANALYZE TABLE 语句分析表，analyze table 语句的基本语法如下：

analyze [local | no_write_to_binlog] table table_name[,tbl_name]....

默认的，MySQL服务会将analyze table语句写到binlog中，以便在主从架构中，从服务能够同步数据。可以添加参数 local 或者 no_write_to_binlog 取消将语句写到binlog中。

使用 analyze table 分析表的过程中，数据库系统会自动对表加一个 只读锁。 在分析期间，只能读取表中的记录，不能更新和插入记录。analyze table 语句能够分析 InnoDB和MyISAM类型的表，但是不能作用于视图。

analyze table分析后的统计结果会反映到 cardinality 的值，该值统计了表中某一键所在的列不重复的值的个数。 该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。也就是索引列的 cardinality 的值与表中数据的总条数差距越大，即使查询的时候使用了该索引作为查询条件，存储引擎实际查询的时候使用的概率就越小。

下面通过例子来验证一下，cardinality 可以通过 show index from table_name 查看。

CREATE TABLE `user1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb3;

创建表user1，并插入1000条数据，其中name值均为jane 。此时我们执行 SHOW INDEX FROM user1;

如上图所示，Cardinality 列 对于id 是12（其实应该是1000），对于name是1（实际就是1）。我们使用分析表语句 后再次查看：

上面结果显示的信息说明如下：

• Table：表示分析的表的名称
• Op：表示执行的操作。analyze表示进行分析操作
• Msg_type：表示信息类型，其值通常是状态(status)、信息（info）、注意（note）、警告（warning）和错误（error）之一。
• Msg_text：显示信息。

此时再次查看user表的 Cardinality，如下所示：

SHOW INDEX FROM user1;

可以看到id主键的Cardinality已经修正，和数据总量一致，此时索引已经修复，查询效率大大提高。这个值只要大致相同就表示索引被优化器使用的概率就越大。

---

我们修改id=2的name值，然后再次分析查看，可以看到Cardinality列已经从 1 变成了 2：

# 修改值
update user1 set name='janus' where id=2;
# 查看索引
SHOW INDEX FROM user1;
# 分析表
analyze table user1;
# 查看索引
SHOW INDEX FROM user1;

我们查看下面SQL的执行计划：

explain select * from user1 where name='jane'

结果如下所示，可以看到虽然name字段上面有索引，但是执行计划中显示type为’ALL’，表示并没有使用到索引。

我们再看下面SQL的执行计划：

explain select * from user1 where name='janus'

结果如下所示，可以看到确实是用到了索引，检索的行数rows为1。

这两个SQL也说明了：

• 如果取值离散度太小，那么不太适合创建索引
• 如果创建了索引，那么查询“索引取值少量数据”时才会用到索引

【2】检查表

MySQL中可以使用 CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。 CHECK TABLE语句在执行过程中也会给表加上只读锁。

对于MyISAM类型的表，CHECK TABLE 语句还会更新关键字统计数据。而且 CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。

该语句的基本语法如下：

CHECK TABLE table_name[, table_name] ....[option]...
option={QUICK | FAST | MEDIUM | EXTENDED |CHANGED }

option各个选项的意义分别是：

• QUICK：不扫描行，不检查错误的连接
• FAST：只检查没有被正确关闭的表
• CHANGED：只检查上次检查后被更改的表和没有被正确关闭的表
• MEDIUM：扫描行，以验证被删除的连接是有效的。也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点。
• EXTENDED：对每行的所有关键字进行一个全面的关键字查找。这可以确保表时100%一致的，但是花的时间较长。

option只对MyISAM类型的表有效，对InnoDB类型的表无效。比如：

check table user1

该语句对于检查的表可能会产生多行信息。最后一行有一个状态的Msg_type值 ，Msg_text通常为OK。如果得到的不是OK，通常要对其进行修复；是OK说明表已经是最新的了。表已经是最新的，意味着存储引擎对这张表不必进行检查。

【3】优化表

① OPTIMIZE TABLE

MySQL中使用optimize table语句来优化表。但是，optimize table语句只能优化表中的varchar、blob或text类型的字段。一个表使用了这些字段的数据类型，若已经删除了表的一大部分数据，或者已经对含有可变长度行的表（含有varchar、blob或text列的表）进行了很多更新，则应使用optimize table来重新利用未使用的空间，并整理数据文件的碎片。

optimize table语句对InnoDB和MyISAM类型的表都有效。该语句在执行过程中也会给表加上只读锁。optimize table语句的基本语法如下：

optimize [local | no_write_to_binlog] table table_name [,table_name]...

local | no_write_to_binlog 关键字的意义和分析表相同，都是指定不写入二进制日志。

如下所示，我们在user1中插入十万条数据，占用空间如下：

---

如下所示，我们删除一半数据，再次查看该文件，发现占用大小并未改变

delete from user1 where id>50000

---

优化表之后再次查看，发现文件占用空间变小：

optimize table user1;

上图（优化结果示意图）是正常的，针对MySQL的InnoDB引擎，optimize结果就是那样的（官网有说明）。在MyISAM中，是先分析这张表，然后会整理相关的MySQL datafile，之后回收未使用的空间。在InnoDB中，回收空间是简单通过alter table 进行整理空间。在优化期间，MySQL会创建一个临时表，优化完成之后会删除原始表，然后会将临时表rename成为原始表。

注意，在多数的设置中，根本不需要运行 optimize table。即使对可变长度的行进行了大量的更新，也不需要经常运行，每周一次或每月一次即可。并且只需要对特定的表运行。

② mysqlcheck

mysqlcheck -o DatabaseName tableName -uroot -p******

mysqlcheck是Linux中的rompt，-o是代表Optimize。

举例：优化所有表：

mysqlcheck -o DatabaseName -u root -p****
# 或
mysqlcheck -o --all-databases -u root -p****