共计 10719 个字符，预计需要花费 27 分钟才能阅读完成。
这篇文章主要介绍 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
之间的值。这些值表示增加警告级别以避免崩溃，以及针对已恢复的表中可能存在的不一致状况增加容错级别。最好从值 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 如何实现表维护”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！
