本文地址：https://www.askmac.cn/archives/mysql-maintenance-type.html

 

第10章 表的维护

 

章节概述

本章介绍如何在MySQL中进行表的维护管理。你会了解：

• 分辨表维护操作类型
• 执行表维护SQL语句
• 使用客户端及工具程序来进行表维护
• 修理InnoDB表
• 启用对MyISAM表的自动修复

 

10.1 表维护操作类型

一些表维护操作对于判定并修正数据库中的问题（例如，当一张表由于服务器奔溃而导致损坏后）或帮助MySQL优化表查询时非常有用。MySQL（根据存储引擎）可允许你执行几种类型的维护操作:

存储引擎名	MyISAM	InnoDB
CHECK TABLE	完整检查更新索引统计信息	完成检查
REPAIR TABLE	修理讹误表	N/A
ANALYZE TABLE	更新索引统计信息	更新索引统计信息
OPTIMIZE TABLE	回收被浪费的空间表碎片整理索引页排序 更新索引统计信息	表重建（MySQL 5.7.4以后部分使用了online DDL的机制避免了表拷贝）

10.1.1 分析和优化

对于表分析和优化操作，你可能希望定期执行以保持你的表一直处于最佳状态:

• 当MySQL对一张MyISAM或InnoDB表进行分析时，它会更新其索引统计信息。优化器之后在处理查询时会使用这些统计信息，以便对如何最佳地查询表记录、表连接时读取表的顺序等做出更佳的判定。
• 当MySQL优化了一张MyISAM表，它会对数据文件中的碎片进行整理以回收未使用的空间，排序索引，并更新索引统计信息。定期进行表碎片整理有助于加快包含有非定常列（如VARCHAR, VARBINARY, BLOB或TEXT）的表的访问，在此类表中进行频繁地插入或删除会导致存留许多空间间隙。碎片整理能有效降低这些间隙。

 

对于那些已经被填入数据并在之后一时不会再被修改的表，表分析和优化操作无疑对之后那些对此表进行的查询有最大的好处。随着表持续被更新修改，这些好处将被逐渐削弱，因此你需要定期地重复进行这类分析和优化操作。

 

表维护清理所用的工具包括有SQL语句如CHECK TABLE和REPAIR TABLE，客户端程序如mysqlcheck, myisamchk等工具，以及服务端对于自动恢复的能力。之后我们对这些工具进行介绍。

 

10.2 用于表维护操作的SQL语句

MySQL有几种SQL语句来进行表维护：CHECK TABLE用于完整性检查，REPAIR TABLE用于表修理，ANALYZE TABLE进行分析，OPTIMIZE TABLE被用于优化。本节中我们会重点对这些SQL语句进行讲解。

 

每个语句，当被发出后，服务端就会根据所需的操作进行执行。其中语句会接受一个或多个表名，或者可选的关键字来对基本的操作进行调整。一个表前一个不指定数据名来隐式指明为当前库下的表，或以db_name.table_name形式指定所要操作的数据库下的表。例如，如果world为当前数据库，以下语句等同于指导服务端对world.City表进行检查：

mysql> CHECK TABLE City;
mysql> CHECK TABLE world.City;

在执行完所要求的操作后，服务端返回操作结果信息，信息结果行分四列：

• Table: 被处理的表
• Op: 被执行的操作（check, repair, analyze或optimize）
• Msg_type: 指提示成功或失败的返回信息类别。
• Msg_text: 提供额外信息。

 

10.2.1 CHECK TABLE

CHECK TABLE语句可对表结构和内容进行完整性检查。它可用于MyISAM表和InnoDB表。对于MyISAM表，它还会对索引统计信息进行更新。如果进行检查的对象是一张view，CHECK TABLE语法也会对此视图的定义进行验证。当CHECK TABLE对于表的检查返回了一些问题，那么这张表就需要进行修复。

 

例如，在进行表修复前，CHECK TABLE语句应该被用于探测硬件问题（如，ram坏损，磁盘坏道等）。

简单语法如下：

CHECK TABLE <table_name> [, <table_name>] ;

 

10.2.2 REPAIR TABLE

REPAIR TABLE语句被用于修复表中的讹误问题。它仅可用于MyISAM，ARCHIVE及CSV表。当然，你也可以让服务端来自动对MyISAM表进行修复。

 

例如，当一次非正常关机（断电）或MyISAM表存在讹误的行时，可以使用REPAIRE TABLE语句进行恢复。

简单语法：

REPAIR TABLE <table_name> [, <table_name>] ;

 

10.2.3 ANALYZE TABLE

ANALYZE TABLE语句会更新表中关于键值分布的统计信息。这些信息被优化器用来对执行计划做出更佳判定。语句可用于MyISAM和InnoDB表。

 

简单语法：

ANALYZE TABLE <table_name> [, <table_name>] ;

 

10.2.4 OPTIMIZE TABLE

OPTIMIZE TABLE语句可以对MyISAM表进行碎片清理。清理会涉及对由于删除、更新、合并记录而造成的存储不连续（空间空隙）进行整理，回收这些不被使用的空间碎片。OPTIMIZE TABLE 也会对无序的索引页进行排序并，更新索引统计信息。

例如，使用OPTIMIZE TABLE语句来对表进行整理，简单语法：

OPTIMIZE TABLE <table_name> [, <table_name>] ;

 

对于每种存储引擎的命令匹配：

OPTIMIZE TABLE可作用于InnoDB表，但是其本质是匹配使用ALTER TABLE语句来实现表的重建。优化命令也会更新索引统计信息并释放簇索引中的闲置空间。

 

通过之前例子中的Note: Table does not support optimize, doing recreate + analyze instead，我们可以将作用于InnoDB表的OPTIMIZE TABLE底层实现理解为:

mysql> alter table City force;
mysql> analyze table City;

或是

mysql> alter table City engine=InnoDB;
mysql> analyze table City;

 

OPTIMIZE TABLE也可以运作于其他引擎表，不过其实现是通过执行匹配的ALTER TABLE命令来完成的，且一般情况下OPTIMIZE TABLE并不支持其它存储引擎，你需要在MySQL服务端启动时启用--skip-new项来启用支持。