优化表语句

OPTIMIZE TABLE 语句通过对表进行碎片整理来清理表。
它通过重建表并释放未使用的空间来对表进行碎片整理。
它在优化期间锁定表并更新索引统计信息。
它在永久的、完全填充的桌子上是最有益的。

优化表特性：

• 碎片整理涉及回收由删除和更新引起的未使用空间，以及合并已拆分和非连续存储的记录。
• 需要表的 SELECT 和 INSERT 权限
• 支持分区表。我们还可以使用 ALTER TABLE...OPTIMIZE PARTITION 来检查一个或者多个分区。

例如，我们可以在修改大量行后使用 OPTIMIZE TABLE 语句在 InnoDB 中重建 FULLTEXT 索引。
对于 InnoDB 表，OPTIMIZE TABLE 映射到 ALTER TABLE，它重建表以更新索引统计信息并释放聚集索引中未使用的空间。
InnoDB 不像其他存储引擎那样受到碎片化的影响，因此我们不需要经常使用 OPTIMIZE TABLE。

在使用 ARCHIVE 存储引擎压缩表的表上使用 OPTIMIZE TABLE。
SHOW TABLE STATUS 报告的 ARCHIVE 表中的行数总是准确的。
.ARN 文件可能会在优化操作期间出现。

以下 OPTIMIZE TABLE 语句优化了 mysql 数据库中的两个完全填充的表：

mysql> OPTIMIZE TABLE mysql.help_relation, mysql.help_topic; 
+---------------------+----------+----------+----------+
| Table               | Op       | Msg_type | Msg_text | 
+---------------------+----------+----------+----------+
| mysql.help_relation | optimize | status   | OK       | 
| mysql.help_topic    | optimize | status   | OK       | 
+---------------------+----------+----------+----------+ 
2 rows in set (0.00 sec)

对于 MyISAM 表，在表的大部分被删除或者对具有可变长度行的表（具有 VARCHAR、VARBINARY、BLOB 或者 TEXT 列的表）进行了许多更改后，使用 OPTIMIZE TABLE 语句。
删除的行保存在链表中，后续的 INSERT 操作会重用旧的行位置。
OPTIMIZE TABLE 在完全填充且变化不大的表上使用时效果最佳。
如果数据变化很大，优化的好处就会减少，你必须经常优化。

ANALYZE TABLE 语句

MySQL 使用存储的键分布统计信息来决定当我们对常量以外的其他内容执行连接时优化器连接表的顺序。
此外，键分布确定 MySQL 为查询中的特定表使用哪些索引。
我们可以执行 ANALYZE TABLE 语句来分析和存储统计信息，或者我们可以配置 InnoDB 在多次数据更改或者查询表或者索引元数据时自动收集统计信息。

分析表特征：

• 在分析过程中，MySQL 对 InnoDB 和 MyISAM 使用读锁锁定表。
• 此语句等效于使用 'mysqlcheck -analyze'。
• 需要表的 SELECT 和 INSERT 权限
• 支持分区表。我们还可以使用 ALTER TABLE...ANALYZE PARTITION 来检查一个或者多个分区。

一个好的 ANALYZE TABLE 结果示例：

mysql> ANALYZE LOCAL TABLE Country; 
+----------------------+--------+----------+----------+ 
| Table                | Op     | Msg_type | Msg_text | 
+----------------------+--------+----------+----------+ 
| world_innodb.Country | analyze| status   | OK       | 
+----------------------+--------+----------+----------+

如果自上次运行 ANALYZE TABLE 语句后表没有更改，MySQL 不会分析该表。
默认情况下，MySQL 将 ANALYZE TABLE 语句写入二进制日志并将它们复制到复制从属。
使用可选的 NO_WRITE_TO_BINLOG 关键字或者其别名 LOCAL 禁止日志记录。

我们可以使用以下选项控制 MySQL 收集和存储密钥分布统计信息的方式：

• innodb_stats_persistent ：当此选项为 ON 时，MySQL 会在新创建的表上启用 STATS_PERSISTENT 设置。使用 CREATE TABLE 或者 ALTER TABLE 语句时，我们还可以在表上设置 STATS_PERSISTENT。默认情况下，MySQL 不会将密钥分发统计信息持久化到磁盘，因此它们必须在服务器重启等时间生成。 MySQL 在磁盘上为启用了 STATS_PERSISTENT 的表存储密钥分布统计信息，因此它不需要为这些表频繁生成统计信息。这允许优化器随着时间的推移创建更一致的查询计划。
• innodb_stats_persistent_sample_pages ：MySQL 通过读取 STATS_PERSISTENT 表的索引页示例而不是整个表来重新计算统计信息。默认情况下，它读取 20 页的示例。增加此数字可提高生成的统计信息和查询计划的质量。减少此数字可降低生成统计信息的 I/O 成本。
• innodb_stats_transient_sample_pages ：此选项控制在没有 STATS_PERSISTENT 设置的表上采样的索引页数。

以下选项控制 MySQL 如何自动收集统计信息。

• innodb_stats_auto_recalc ：启用此选项后，当 STATS_PERSISTENT 表自上次重新计算后 10% 的行发生更改时，MySQL 会自动为该表生成统计信息。
• innodb_stats_on_metadata ：启用此选项以在执行元数据语句（例如 SHOW TABLE STATUS）或者查询 INFORMATION_SCHEMA.TABLES 时更新统计信息。默认情况下，此选项处于禁用状态。

CHECKSUM TABLE 语句

CHECKSUM TABLE 语句报告表校验和。
它用于验证备份、回滚或者其他操作前后表的内容是否相同。

校验和表特征：

• CHECHSUM TABLE 需要该表的 SELECT 权限。
• 对于不存在的表，CHECKSUM TABLE 返回 NULL 并生成警告。
• 如果使用 EXTENDED 选项，则将逐行读取整个表，并计算校验和。
• 如果使用 QUICK 选项，则报告可用的实时表校验和；否则报告 NULL。这非常快。它还通过在创建表时指定 CHECKSUM=1 表选项来启用 MyISAM 表的实时校验和。
• 如果 QUICK 和 EXTENDED 均未指定，则 MySQL 假定 EXTENDED，但 CHECKSUM=1 的 MyISAM 表除外。

校验和值取决于表行格式。
如果行格式发生变化，校验和也会发生变化。
例如，在 MySQL 4.1 之后，VARCHAR 的存储格式发生了变化，因此如果将 4.1 表升级到更高版本，如果表包含 VARCHAR 字段，则校验和值会发生变化。

维修表语句

维修台特点：

• 快速选项：尝试仅修复索引文件而不修复数据文件。这种类型的修复就像 myisamchk -recover -quick 所做的那样。
• EXTENDED 选项：MySQL 逐行创建索引，而不是通过排序一次创建一个索引。这种类型的修复就像 myisamchk -safe-recover 所做的那样。
• USE_FRM 选项不能用于分区表。
• 需要表的 SELECT 和 INSERT 权限
• 支持分区表。我们还可以使用 ALTER TABLE...REPAIR PARTITION 来检查一个或者多个分区。

最好在进行表修复操作之前对表进行备份；在某些情况下，该操作可能会导致数据丢失。
可能的原因包括（但不限于）文件系统错误。
如果服务器在 REPAIR TABLE 操作期间崩溃，我们应该在重新启动后立即执行另一个 REPAIR TABLE，然后再执行任何其他操作，以避免进一步损坏。
如果我们经常需要使用 REPAIR TABLE 从损坏的表中恢复，请尝试查找根本原因以防止此类损坏并消除使用 REPAIR TABLE 的需要。

REPAIR TABLE 语句示例：

mysql> REPAIR TABLE mysql.help_relation; 
+---------------------+--------+----------+----------+ 
| Table               | Op     | Msg_type | Msg_text | 
+---------------------+--------+----------+----------+ 
| mysql.help_relation | repair | status   | OK       | 
+---------------------+--------+----------+----------+ 
1 row in set (0.00 sec)

用于表维护操作的 SQL

执行表维护的 SQL 语句有多种：

• ANALYZE TABLE：更新索引统计信息
• 检查表：正确检查完整性
• 校验和表：正确检查完整性
• 维修表：维修
• 优化表：优化

每个语句采用一个或者多个表名和可选关键字。
以下是维护语句和输出的示例：

mysql> CHECK TABLE world_innodb.City; 
+-------------------+-------+----------+----------+ 
| Table             | Op    | Msg_type | Msg_text | 
+-------------------+-------+----------+----------+
| world_innodb.City | check | status   | OK       | 
+-------------------+-------+----------+----------+

服务器执行请求的操作后，向客户端返回有关操作结果的信息。
该信息采用具有四列的结果集的形式：

• Table ：表示执行操作的表
• Op ：命名操作（检查、修复、分析或者优化）
• Msg_type ：提供成功或者失败的指标
• Msg_text : 提供另外信息

检查表语句

CHECK Table 语句检查表结构和内容的完整性是否有错误。
检查表特征：

• 对于 MyISAM 表，关键统计信息也会更新。
• 还可以检查视图是否存在问题，例如视图定义中引用的表不再存在。
• 支持分区表。我们还可以使用 ALTER TABLE...CHECK PARTITION 来检查一个或者多个分区。

使用 FOR UPGRADE ，服务器检查每个表以确定表结构是否与当前版本的 MySQL 兼容。
由于数据类型的存储格式已更改或者其排序顺序已更改，因此可能会发生不兼容性。
如果存在可能的不兼容性，服务器将对表运行全面检查。
如果完整检查成功，服务器会使用当前 MySQL 版本号标记表的 .frm 文件。
标记 .frm 文件可确保将来使用相同版本的服务器快速检查表。

将 FOR UPGRADE 与 InnoDB、MyISAM 和 ARCHIVE 存储引擎一起使用。
对 InnoDB 和 MyISAM 表使用 QUICK。
MyISAM 支持其他选项。

一个好的 CHECK TABLE 结果示例：

mysql> CHECK TABLE Country; 
+----------------------+-------+----------+----------+ 
| Table                | Op    | Msg_type | Msg_text | 
+----------------------+-------+----------+----------+ 
| world_innodb.Country | check | status   | OK       | 
+----------------------+-------+----------+----------+

如果 CHECK TABLE 的输出表明某个表有问题，请修复该表。
例如，我们可以在修复表之前使用 CHECK TABLE 语句检测硬件问题（例如有故障的内存或者坏的磁盘扇区）。
Msg_text 输出列通常是可以的。
如果我们没有得到 OK 或者 Table 已经是最新的，请运行该表的修复。
如果表被标记为损坏或者未正确关闭，但 CHECK TABLE 未在表中发现任何问题，则将表标记为正常。

表维护操作对于识别和纠正数据库问题非常有用，例如：

• 由于服务器崩溃而损坏的表。

• 对表的查询处理缓慢。

许多工具可用于执行表维护：

• MySQL 工作台
• MySQL 企业监视器
• SQL (DML) 维护语句
• 实用程序：mysqlcheck 和 myisamchk
• 服务器自动恢复