MySQL入门--表维护

来源：这里教程网时间：2026-03-01 12:03:29 作者：

表维护

为什么要做表维护操作，解决什么问题？

两种情况下需要做表维护操作，一是由于服务器崩溃而导致表损坏，二是对表的查询处理速度较慢的情况；

执行表维护工具主要有 MySQL Workbench 、 MySQL Enterprise Monitor 、 SQL (DML) 维护语句、 mysqlcheck 、 myisamchk ；下面就逐一介绍这些工具；

1.1. 表维护 SQL 语句

用于执行表维护的 SQL 语句有： ANALYZE TABLE （更新索引统计信息）、 CHECK TABLE （彻底检查完整性）、 CHECKSUM TABLE （彻底检查完整性）、 REPAIR TABLE （修复）、 OPTIMIZE TABLE （优化），每个语句均包含一个或多个表名称和可选的关键字。维护语句和输出的示例：

mysql> CHECK TABLE world_innodb.City;

+-------------------+-------+----------+----------+

+-------------------+-------+----------+----------+

+-------------------+-------+----------+----------+

执行所请求的操作之后，服务器将返回有关对客户机执行操作的结果的信息。该信息以四列结果集形式显示：

l Table ：指示对其执行操作的表

l Op ：指出操作（检查、修复、分析或优化）

l Msg_type ：指示成功或失败

l Msg_text ：提供其他信息

1.1.1. ANALYZE TABLE 语句

ANALYZE TABLE 语句分析并存储表的键分布统计信息，用于更好地进行查询执行选择，处理 InnoDB 、 NDB 和 MyISAM 表，支持分区表；

ANALYZE TABLE 选项： NO_WRITE_TO_BINLOG 或 LOCAL ：禁用二进制日志

ANALYZE TABLE 正常结果的示例：

mysql> ANALYZE LOCAL TABLE Country;

+----------------------+--------+----------+----------+

+----------------------+--------+----------+----------+

+----------------------+--------+----------+----------+

在对非常量对象执行联接操作时， MySQL 使用所存储的键分布统计信息来确定优化程序联接表的顺序。此外，键分布确定了 MySQL 用于查询中的特定表的索引。

您可以执行 ANALYZE TABLE 语句来分析并存储统计信息，或者配置 InnoDB ，以便在大量数据发生更改之后或者在查询表或索引元数据时自动收集统计信息。

ANALYZE TABLE 特征：

l 在分析过程中，对于 InnoDB 和 MyISAM ， MySQL 使用读取锁来锁定表。

l 此语句等效于使用 mysqlcheck --analyze 。

l 需要对表有 SELECT 和 INSERT 权限。

l 支持分区表。还可以使用 ALTER TABLE...ANALYZE PARTITION 检查一个或多个分区。

如果自从运行上一个 ANALYZE TABLE 语句后表未发生任何更改，则 MySQL 不会分析该表。默认情况下， MySQL 会将 ANALYZE TABLE 语句写入二进制日志并将这些语句复制到复制从属角色中。禁止使用可选的 NO_WRITE_TO_BINLOG 关键字或其别名 LOCAL 执行日志记录。

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

l innodb_stats_persistent ：此选项为 ON 时， MySQL 将对新创建的表启用 STATS_PERSISTENT 设置。使用 CREATE TABLE 或 ALTER TABLE 语句时，还可以对表设置 STATS_PERSISTENT 。默认情况下， MySQL 不会将键分布统计信息持久保留在磁盘上，因此有时必须生成这些信息（如服务器重新启动后）。对于启用了 STATS_PERSISTENT 的表， MySQL 会将其键分布统计信息存储在磁盘上，从而不需要频繁地为这些表生成统计信息。随着时间推移，通过此操作优化程序可以创建更一致的查询计划。

l innodb_stats_persistent_sample_pages ： MySQL 通过读取 STATS_PERSISTENT 表的索引页样例（而并非整个表）重新计算统计信息。默认情况下，将读取 20 页样例。增大此数字可提高所生成的统计信息和查询计划的质量。降低此数字可减少用于生成统计信息的 I/O 成本。

l innodb_stats_transient_sample_pages ：此选项用于控制对没有 STATS_PERSISTENT 设置的表的抽样索引页数量。

以下选项用于控制 MySQL 自动收集统计信息的方式。

l innodb_stats_auto_recalc ：启用此选项时，如果 STATS_PERSISTENT 表中 10% 的行自前一次重新计算后有所变化，则 MySQL 将自动为该表生成统计信息。

l innodb_stats_on_metadata ：启用此选项可在执行元数据语句（如 SHOW TABLE STATUS ）或查询 INFORMATION_SCHEMA.TABLES 时更新统计信息。默认情况下，此选项处于禁用状态。

1.1.2. CHECK TABLE 语句

ANALYZE TABLE 语句检查表结构的完整性，并检查内容中是否包含错误，验证视图定义，支持分区表，处理 InnoDB 、 CSV 、 MyISAM 和 ARCHIVE 表

CHECK TABLE 选项：

Ø FOR UPGRADE ：检查表是否适用于当前服务器。

Ø QUICK ：不扫描行来检查错误链接。

如果 CHECK TABLE 发现 InnoDB 表出现问题，则服务器将关闭，以防止错误扩散，同时 MySQL 会将错误写入错误日志；

CHECK TABLE 特征：

Ø 对于 MyISAM 表，还将更新键统计信息。

Ø 还可以检查视图是否出现问题，例如视图定义中引用的表不再存在。

Ø 支持分区表。还可以使用 ALTER TABLE...CHECK PARTITION 检查一个或多个分区。

使用 FOR UPGRADE 时，服务器将检查每个表以确定表结构是否与当前的 MySQL 版本兼容。可能会因为某种数据类型的存储格式或排序顺序发生变化而出现不兼容的情况。如果出现潜在的不兼容情况，则服务器将对表运行全面检查。如果全面检查成功，则服务器会使用当前的 MySQL 版本号标记表的 .frm 文件。对 .frm 文件进行标记可以确保以后对于与服务器版本相同的表进行检查的速度会加快。

建议对 InnoDB 、 MyISAM 和 ARCHIVE 存储引擎使用 FOR UPGRADE 。对 InnoDB 和 MyISAM 表使用 QUICK 。 MyISAM 支持其他选项。请访问

http://dev.mysql.com/doc/refman/5.6/en/check-table.htm

CHECK TABLE 语句

CHECK TABLE 正常结果的示例：

mysql> CHECK TABLE Country;

+----------------------+-------+----------+----------+

+----------------------+-------+----------+----------+

+----------------------+-------+----------+----------+

如果 CHECK TABLE 的输出表明某个表出现问题，请修复该表。例如，您可以先使用 CHECK TABLE 语句检测硬件问题（如内存故障或磁盘扇区损坏），然后再修复表。

Msg_text 输出列通常为 OK 。如果输出不是 OK 或 Table is already up to date ，请对该表运行修复。如果该表被标记为 corrupted 或 not closed properly ，但 CHECK TABLE 在表中未发现任何问题，则会将该表标记为 OK 。

1.1.3. CHECKSUM TABLE 语句

CHECKSUM TABLE 语句报告表 checksum ，用于验证表的内容在备份、回滚或其他操作前后是否相同；

CHECKSUM TABLE 语句逐行读取整个表以计算校验和

Ø 默认的 EXTENDED 选项提供了此行为。

Ø QUICK 选项对 MyISAM 表可用。

Ø 当包含 MyISAM CHECKSUM=1 设置时，此为默认选项。

CHECKSUM TABLE 语句的示例：

mysql> CHECKSUM TABLE City;

+-------------------+-----------+

| Table | Checksum |

+-------------------+-----------+

| world_innodb.City | 531416258 |

+-------------------+-----------+

CHECKSUM TABLE 特征：

Ø CHECKSUM TABLE 需要对表有 SELECT 权限。

Ø 对于不存在的表， CHECKSUM TABLE 将返回 NULL 并生成警告。

Ø 如果使用了 EXTENDED 选项，则将逐行读取整个表，并计算 checksum 。

Ø 如果使用了 QUICK 选项：将报告实时表 checksum （如果可用）；否则将报告 NULL 。此操作非常快。通过在创建表时指定 CHECKSUM=1 表选项，对 MyISAM 表启用了实时 checksum 。

Ø 如果既未指定 QUICK ，也未指定 EXTENDED ，则 MySQL 将假定为 EXTENDED （ CHECKSUM=1 的 MyISAM 表除外）。

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

注：如果两个表的 checksums 不同，则很可能这两个表存在某方面的差异。不过，因为 CHECKSUM TABLE 使用的散列函数无法保证不冲突，所以存在两个不同的表生成相同 checksum 的微弱可能性。

1.1.4. OPTIMIZE TABLE 语句

OPTIMIZE TABLE 语句通过对表进行碎片整理来清理表，即通过重新构建表并释放未使用的空间对表进行碎片整理； OPTIMIZE TABLE 语句在优化过程中锁定表，并更新索引统计信息，最适用于完全填充的永久表，支持处理 InnoDB 、 MyISAM 和 ARCHIVE 表，支持分区表

OPTIMIZE TABLE 选项： NO_WRITE_TO_BINLOG 或 LOCAL ：禁用二进制日志。

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 语句

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

mysql> OPTIMIZE TABLE mysql.help_relation, mysql.help_topic;

+---------------------+----------+----------+----------+

+---------------------+----------+----------+----------+

+---------------------+----------+----------+----------+

2 rows in set (0.00 sec)

对于 MyISAM 表，在删除表中大量内容或者对包含可变长度行的表（包含 VARCHAR 、 VARBINARY 、 BLOB 或 TEXT 列的表）进行多项更改之后，请使用 OPTIMIZE TABLE 语句。已删除的行将保留在链接的列表中，而后续的 INSERT 操作将重用之前行的位置。

OPTIMIZE TABLE 对完全填充的表使用时效果最佳并且不会发生很大更改。如果数据更改较多并经常需要优化，则优化的优势将会大大降低。

1.1.5. REPAIR TABLE 语句

REPAIR TABLE 语句修复可能已损坏的 MyISAM 或 ARCHIVE 表， 不支持 InnoDB ，但是支持分区表；

REPAIR TABLE 选项：

Ø QUICK ：仅修复索引树，尝试仅修复索引文件，而不修复数据文件。此类型的修复与 myisamchk --recover --quick 所执行的修复相似。

Ø EXTENDED ：逐行创建索引（而不是一次性创建有序索引）， MySQL 将逐行创建索引，而不是一次性创建有序索引。此类型的修复与 myisamchk --safe-recover 所执行的修复相似。

Ø USE_FRM ：使用 .FRM 文件重新创建 .MYI 文件，但是不能用于分区表。

Ø NO_WRITE_TO_BINLOG 或 LOCAL ：禁用二进制日志。

REPAIR TABLE 特征：

Ø QUICK 选项：尝试仅修复索引文件，而不修复数据文件。此类型的修复与 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 语句

REPAIR TABLE 语句的示例：

mysql> REPAIR TABLE mysql.help_relation;

+---------------------+--------+----------+----------+

+---------------------+--------+----------+----------+

+---------------------+--------+----------+----------+

1 row in set (0.00 sec)

1.2. mysqlcheck 客户机程序

mysqlcheck 是用于检查、修复、分析和优化表的命令行客户机；它比发出 SQL 语句更加方便，可以处理 InnoDB 、 MyISAM 和 ARCHIVE 表，并且支持三种检查级别：特定表、特定数据库、所有数据库

部分 mysqlcheck 维护选项：

Ø --analyze ：执行 ANALYZE TABLE 。

Ø --check ：执行 CHECK TABLE （默认）。

Ø --optimize ：执行 OPTIMIZE TABLE 。

Ø --repair ：执行 REPAIR TABLE 。

在某些情况下， mysqlcheck 比直接发出 SQL 语句更加方便。例如，如果提供数据库名称作为其参数，则 mysqlcheck 将确定该数据库所包含的表，并发出语句处理所有这些表。您不需要提供明确的表名称作为参数。此外，由于 mysqlcheck 是命令行程序，因此可以在执行计划维护的操作系统作业中轻松使用该程序。

mysqlcheck 客户机程序， Oracle 建议首先在不使用任何选项的情况下运行 mysqlcheck ，如果需要修复再重新运行。

部分 mysqlcheck 修改选项：

Ø --repair --quick ：尝试快速修复。

Ø --repair ：正常修复（如果快速修复失败）。

Ø --repair --force ：强制修复。

mysqlcheck 示例：

shell> mysqlcheck --login-path=admin world_innodb

shell> mysqlcheck -uroot -p mysql user --repair

shell> mysqlcheck -uroot -p --all-databases # 将检查所有数据库中的所有表

shell> mysqlcheck --login-path=admin --analyze --all-databases

默认情况下， mysqlcheck 将其第一个非选项参数解释为数据库名称，并检查该数据库中的所有表。如果数据库名称后面有其他任何参数，则会将这些参数视为表名称，从而只检查这些表。

1.3. myisamchk 实用程序

myisamchk 是用于检查 MyISAM 表的非客户机实用程序，与 mysqlcheck 类似，其差异是 myisamchk 可以启用或禁用索引，直接（而不是通过服务器）访问表文件，这可以避免并发表访问。

部分 myisamchk 选项：

Ø --recover ：修复表。

Ø --safe-recover ：修复 --recover 无法修复的表。

myisamchk 示例：

shell> myisamchk /var/lib/mysql/mysql/help_topic

shell> myisamchk help_category.MYI

shell> myisamchk --recover help_keyword

从理论上来看， myisamchk 与 mysqlcheck 具有相似的用途。但是， myisamchk 不与 MySQL 服务器通信，而是直接访问表文件。

如何在使用 myisamchk 执行表维护的同时避免并发表访问？

A. 确保服务器不会访问正在进行处理的表。一种实现方法是锁定表或停止服务器。

B. 在命令提示符中，将位置更改为表所在的数据库目录。这是服务器数据目录的子目录，该目录的名称与要检查的表所在的数据库名称相同。（更改位置是为了更加便于引用表文件。可以跳过此步骤，但 myisamchk 必须包含表所在的目录。）

C. 调用 myisamchk ，使用选项指示要执行的操作，后跟参数以指定 myisamchk 应对其执行操作的表。这些参数可以是表名称，也可以是表的索引文件的文件名。索引文件名与表名称相同，包含 .MYI 后缀。因此，可以通过 table_name 或 table_name.MYI 引用表。

D. 重新启动服务器。

注：请首先尝试 --recover ，因为 --safe-recover 比较慢。

mysqlcheck 和 myisamchk 的用于控制所执行的维护类型的选项：

mysqlcheck 和 myisamchk 均使用多个选项来控制所执行的表维护操作的类型。上表汇总了一些最常用的选项，其中大多数选项同时适用于两个程序。如果不是同时适用于两个程序，会记录在相关的选项说明中。

Ø --analyze ：分析表中键值的分布。通过加快基于索引的查找，这可以提高查询的性能。

Ø --auto-repair ：如果检查操作发现了问题，则自动修复出现问题的表。

Ø --check 或 -c ：检查表中是否存在问题。如果未指定其他任何操作，则为默认操作。

Ø --check-only-changed 或 -C ：跳过表检查（自上一次检查后已更改的表或未正常关闭的表除外）。如果服务器在表打开时崩溃，则会出现后一种情况。

Ø --fast 或 -F ：跳过表检查（未正常关闭的表除外）。

Ø --extended 、 --extend-check 或 -e ：运行扩展表检查。对于 mysqlcheck ，将此选项与修复选项结合使用时，将执行比单独使用修复选项时更彻底的修复。即， --repair --extended 执行的修复操作比 --repair 执行的操作更彻底。

Ø --medium-check 或 -m ：运行中等表检查。

Ø --quick 或 -q ：对于 mysqlcheck ，不包含修复选项的 --quick 会导致只检查索引文件，而不检查数据文件。对于这两个程序，将 --quick 与修复选项结合使用都会导致程序只修复索引文件，而不修复数据文件。

Ø --repair 、 --recover 或 -r ：运行表修复操作。

1.4. InnoDB 表维护

出现故障之后， InnoDB 将自动恢复。使用 CHECK TABLE 或客户机程序可找出不一致、不兼容和其他问题。也可通过使用 mysqldump 对表进行转储来恢复该表：

shell> mysqldump <db_name> <table_name> > <dump_file>

然后，删除该表并从转储文件重新创建。

shell> mysql <db_name> < <dump_file>

要在崩溃后修复表，请使用 --innodb_force_recovery 选项重新启动服务器或者从备份中恢复表。使用 ALTER TABLE 进行优化时，将重构表并释放群集索引中未使用的空间。

如果表检查表明存在问题，请通过使用 mysqldump 转储该表、删除该表并从转储文件重新创建该表来将其恢复到一致状态。

如果 MySQL 服务器或其运行主机崩溃，则某些 InnoDB 表可能处于不一致状态。在 InnoDB 的启动序列中，会执行自动恢复。服务器很少因为自动恢复故障而无法启动。如果出现此情况，请使用以下过程：

A. 重新启动服务器，将 --innodb_force_recovery 选项的值设置为 1 到 6 之间的值。这些值表示增加警告级别以避免崩溃，以及针对已恢复的表中可能存在的不一致状况增加容错级别。最好从值 4 开始，该值可以阻止插入缓冲区合并操作。

B. 当在 --innodb_force_recovery 设置为非零值的情况下启动服务器时， InnoDB 将阻止 INSERT 、 UPDATE 或 DELETE 操作。因此，您应转储 InnoDB 表，然后在该选项生效时将这些表删除。再在不使用 --innodb_force_recovery 选项的情况下重新启动服务器。服务器启动之后，将从转储文件恢复 InnoDB 表。

C. 如果前述步骤失败，则从前一个备份恢复表。

访问 http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html 了解有关对损坏的数据库启动 InnoDB 的更多信息。

1.5. MyISAM 表维护

MyISAM 表维护对于动态格式表和静态格式表，默认的 CHECK TABLE 检查类型均为 MEDIUM 。如果将静态格式表类型设置为 CHANGED 或 FAST ，则默认选项为 QUICK 。对于 CHANGED 和 FAST ，将跳过行扫描，因为这些行很少损坏。如果表被标记为“已损坏”或“未正常关闭”，则 CHECK TABLE 将更改表。如果未在表中发现任何问题，则会将表的状态标记为“最新”。如果表已损坏，则问题最有可能存在于索引而不是数据中。

shell> myisamchk --medium-check <table_name>

设置服务器以运行检查并自动修复表。使用 --myisam-recover 选项启用自动修复。服务器将在启动之后第一次访问每个 MyISAM 表时进行检查，以确保这些表前一次正确关闭。

--myisam-recover 选项值可以包含以逗号分隔的值列表，由以下一个或多个值组成：

Ø DEFAULT ：默认检查。

Ø BACKUP ：指示服务器对必须进行更改的所有表进行备份。

Ø FORCE ：执行表恢复，即使可能导致多行数据丢失也是如此。

Ø QUICK ：执行快速恢复。恢复将跳过一些不包含因删除或更新而产生的行间隔（也称为“洞”）的表。

强制从 config 文件恢复 MyISAM 表情况。例如，要指示服务器对发现问题的 MyISAM 表执行强制恢复，但同时要备份其更改的所有表，请向选项文件中添加以下内容：

[mysqld]

myisam-recover=FORCE,BACKUP

1.6. MEMORY 表维护

使用 DELETE...WHERE 语句删除多个行时， MEMORY 表不会释放内存。要释放内存，必须执行空值 ALTER TABLE 操作。