MySQL如何实现表维护

这篇文章主要介绍MySQL如何实现表维护，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

表维护

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

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

执行表维护工具主要有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;

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

| Table | Op | Msg_type | Msg_text |

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

| world_innodb.City | check | status | OK |

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

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

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;

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

| Table | Op | Msg_type | Msg_text |

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

| world_innodb.Country | analyze| status | OK |

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

在对非常量对象执行联接操作时，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;

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

| Table | Op | Msg_type | Msg_text |

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

| world_innodb.Country | check | status | OK |

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

如果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;

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

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

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;

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

| Table | Op | Msg_type | Msg_text |

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

| mysql.help_relation | repair | status | OK |

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

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 操作。

1.7.        ARCHIVE 表维护

ARCHIVE表在插入表行时将对其进行压缩，检索时，将根据需要对行进行解压缩。一些SELECT 语句可能会减弱压缩功能。使用OPTIMIZE TABLE 或REPAIR TABLE 可以实现更好的压缩，但只在未对表进行访问（读或写）时，OPTIMIZE TABLE有效。

以上是“MySQL如何实现表维护”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！