MySQL怎样实现表维护
发布时间：2022-01-11 14:17:44 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍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 支持其他选项。请访问 如果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 是命令行程序，因此可以在执行计划维护的操作系统作业中轻松使用该程序。 （编辑：随州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
