Mysql数据库管理表的维护

2016/01/05 by Leave a Comment

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

 

 

一、目标

 

完成本课程后，你应该能够：

  • 认识不同类型的表维护操作
  • 执行维护表的SQL语句
  • 使用客户端和实用程序维护表
  • 根据具体的存储引擎维护表

 

二、实施表的维护

 

  • 表的维护操作对识别和解决数据库问题非常有用，例如：
  • 因服务器崩溃而损坏的表
  • 表上查询处理缓慢
  • 许多工具可以执行表的维护
  • MySQL Workbench（MySQL工作台）
  • MySQL Enterprise Monitor（MySQL企业版监视器）
  • SQL（DML）Maintenance statements（SQL <DML>维护语句）
  • 工具：
  • mysqlcheck
  • Myisamchk
  • Server auto-recovery（服务器自动恢复）

 

三、使用SQL进行表的维护操作

 

  • 有多条SQL语句可以执行表的维护：
  • ANALYZE TABLE : 更新索引的统计信息
  • CHECK TABLE : 检查表的完整性
  • CHECKSUM TABLE : 报告表数据的一致性检测结果
  • REPAIR TABLE : 修复表
  • OPTIMIZE TABLE : 优化表

 

  • 每条语句需要一个或多个表名和可选关键字。

 

  • 一条维护语句和输出的样例：

MYSQL_TABLE_MAIN1

 

备注：

在执行请求的操作之后，服务器返回操作的结果信息到客户端。

结果信息以四列格式显示：

  • Table : 标示执行操作的表
  • Op : 操作的名字（check,repair,analyze,或optimize）
  • Msg_type : 提供一个成功或失败的指示器
  • Msg_text : 提供额外的信息

 

如果想获取关于MySQL表维护的更多信息，可以参照文档：

MySQL Reference Manual

http://dev.mysql.com/doc/mysql/en/table-maintenance-sql.html

一、ANALYZE TABLE（分析表）语句

 

  • 分析并存储表的键值分布统计信息
  • 为执行查询提供更好的选择
  • 对使用InnoDB,NDB和MyISAM存储引擎的表生效
  • 支持分区表
  • ANALYZE TABLE选项：
  • NO_WRITE_TO_BINLOG或LOCAL : 不记录二进制日志

 

  • 一条ANALYZE TABLE语句结果的样例：

MYSQL_TABLE_MAIN2

 

备注：

当你执行一个除常数之外的连接时，MySQL使用存储的键值分布统计信息决定如何优化表连接的顺序。另外，键值分布决定了查询中具体的表MySQL使用什么索引。

你可以执行ANALYZE TABLE语句分析和存储统计信息，或者你可以配置InnoDB，在数据变化后或者你查询表或索引的元数据时，将会自动收集统计信息。

ANALYZE TABLE 的特点：

  • 在分析过程中，MySQL在使用InnoDB和MyISAM引擎的表上加上一个只读锁
  • 这条语句等价于使用mysql check –analyze语句
  • 需要有表的SELECT和INSERT权限
  • 支持分区表。你也可以使用ALTER TABLE …ANALYZE PARTITION来检查一个或多个分区。

如果表自从上次执行ANALYZE TABLE语句后没有发生过改变，MySQL将不分析该表。

默认情况下，MySQL将ANALYZE TABLE语句写入二进制日志并且将其复制到从库。

使用可选的关键字NO_WRITE_TO_BINLOG或它的别名LOCAL来设置不记录日志。

 

续：备注：

 

你可以使用以下选项控制MySQL如何收集和存储键值分布统计信息：

  • innodb_stats_persistent :

当该选项设置为ON，MySQL将在新建的表上启用STATS_PERSISTENT设置。你也可以在使用CREATE TABLE 或者 ALTER TABLE 语句时使用STATS_PERSISTENT。

默认情况下，MySQL不会持续将键值分布的统计信息写入磁盘，所以它们（统计信息）必须在某些时间（重新）生成，比如服务器重启。当（对某些表）启用STATS_PERSISTENT时，MySQL将存储这些表的键值分布统计信息至磁盘，这样这些表将不需要再频繁生成统计信息。这使得随着时间的推移，优化器可以创建更一致（稳定）的查询计划。

  • innodb_stats_persistent_sample_pages :

MySQL通过对STATS_PERSISTENT表的索引页采样而非整张表重新生成统计信息。

默认情况下，它对样例的20页进行采样，增加页数可以提升生成的统计信息的准确性和生成更精准的查询计划。

减少页数可以减少生成统计信息时I/O的消耗。

  • innodb_stats_transient_sample_pages :

当STATS_PERSISTENT未设置的时候，该选项控制表上索引页的采样数量。

 

以下选项控制MySQL如何自动收集统计信息。

  • innodb_stats_auto_recalc :

启用该选项时，当STATS_PERSISTENT表自上次重新统计后，有10%行记录改变时，MySQL将自动收集统计信息。

  • innodb_stats_on_metadata :

当你执行SHOW TABLE STATUS或其他元数据语句？，或当你查询INFOMATION_SCHEMA.TABLES时，启用该选项将更新统计信息。

默认情况下，该选项是禁用的。

 

一、CHECK TABLE（检查表）语句

 

  • 检查表结构和内容的完整性
  • 验证视图定义
  • 支持分区表
  • 对使用InnoDB,CSV,MyISAM和ARCHIVE（存储引擎）的表生效
  • CHECK TABLE选项：
  • FOR UPGRADE : 检查当前服务器的表是否工作？
  • QUICK : 不扫描不正确链接的行
  • 如果CHECK TABLE发现使用InnoDB存储引擎的表有错误：
  • 服务器将会被关闭以防止错误传播
  • MySQL将会记录错误到错误日志

 

备注：

CHECK TABLE特点：

  • 对于使用MyISAM存储引擎的表，键值统计信息也会被更新。
  • 也可以检查视图的问题，例如表中引用的视图定义并不存在。
  • 支持分区表。你也可以使用ALTER TABLE…CHECK PARTITION来检查一个或多个分区。

对于FOR UPGRADE子句，服务器检查每个表以确认表结构是否与当前版本的MySQL兼容。数据类型的存储格式更改或者它的排序顺序改变都可能导致不兼容。如果这里可能存在不兼容，服务器将在表上运行一个完整的检查，如果完整检查成功，服务器将会在表的.frm文件上标记当前MySQL版本的数字。标记.frm文件可以确保该表未来做（与文件标记）同样版本服务器检查时快速检查。

在InnoDB,MyISAM和ARCHIVE存储引擎上可使用FOR UPGRADE。

使用InnoDB和MyISAM存储引擎的表上可使用QUICK。MyISAM支持更多选项。

查阅http://dev.mysql.com/doc/mysql/en/check-table.html可获得更多信息。

 

 

 

 

续：五、CHECK TABLE（检查表）语句

 

  • 一个好的CHECK TABLE结果样例：

MYSQL_TABLE_MAIN4

 

备注：

如果CHECK TABLE的输出表明表有问题，修复该表。

例如，你可以在修复表之前使用CHECK TABLE语句检测硬件问题（例如内存错误或磁盘扇区损坏）。Msg_text输出列正常显示为“OK”。如果得到的（结果）不是“OK”或“Table is already up to date”，对表执行修复。

如果表被标记为“corrupted”或“not closed properly”，但CHECK TABLE没有在表上发现任何错误，将会标记表为“OK”。

 

一、CHECKSUM TABLE（校验表）语句

 

  • 报告表的checksum
  • （该语句）用来验证表备份，回滚或者其他操作之前或之后内容是否一致
  • 读取整个表逐行进行校验
  • 默认选项EXTENDED提供这种行为
  • QUICK选项在MyISAM表上可用。
  • MyISAM 上默认选项为设置CHECKSUM=1。

 

  • 一条CHECKSUM TABLE语句的样例：

MYSQL_TABLE_MAIN5

 

备注：

CHECKSUM TABLE特点：

  • CHECKSUM TABLE需要SELECT表的权限
  • 对于不存在的表，CHECKSUM TABLE返回“NULL”并且生成一条警告
  • 如果使用了EXTENDED选项，逐行读取整张表，并且计算checksum的值。
  • 如果使用QUICK选项：
  • 如果表的在线checksum可用将会给出报告，否则报告（显示）为“NULL”，这操作将会很快速。
  • 你创建表时为MyISAM表指定CHECKSUM=1将会启用在线checksum。
  • 如果既没有指定QUICK又没有指定EXTENDED，MySQL设置选项为EXTENDED，除非MyISAM表CHECKSUM=1。

checksum的值取决于表的行记录的格式，如果行记录的格式改变，checksum也会改变。

比如，“VARCHAR”存储类型在MySQL4.1之后发生了改变，所以如果你升级一张4.1版本的表到更新的版本，包含VARCHAR列的表的checksum值将会改变。

 

 

续：备注：

注意：

如果两张表的checksums值不同，这可能说明它们可能在某些方面不同。

自从CHECKSUM TABLE使用哈希函数后，不能保证没有冲突，这将会有极小的可能导致两张不同的表得到相同的checksum值。

 

一、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和其他存储引擎产生碎片的方式不同，所以你不需要经常（在InnoDB上）使用OPTIMIZE TABLE。

在ARCHIVE存储引擎上使用OPTIMIZE TABLE可以压缩表。

使用SHOW TABLE STATUS语句查看ARCHIVE表行数产生的结果往往比较准确。在执行优化操作时会产生一个.ARN文件。

 

 

续：七、OPTIMIZE TABLE（优化表）语句

 

以下OPTIMIZE TABLE语句优化了两张mysql数据库中数据密集/完全填充？的表：

 

MYSQL_TABLE_MAIN6

 

备注：

对于MyISAM表，在对表的大部分数据删除或者在可变长度行记录（包含VARCHAR，VARBINARY，BLOB或TEXT列）做出修改后，使用OPTIMIZE TABLE语句。

被删除的行都保存在一个链接列表中，并且随后的INSERT（插入）操作重复使用旧的行的位置。

OPTIMIZE TABLE在完全填充且数据很少改变的表上有较好的优化小故宫。当数据变化比较多时优化效果变差，并且你不得不经常进行优化。

 

一、REPAIR TABLE（修复表）语句

 

  • 修复可能已经损坏的MyISAM或ARCHIVE表
  • 不支持InnoDB
  • 优化过程中会锁定表
  • 支持分区表
  • REPAIR TABLE选项：
  • QUICK : 仅仅修复索引树
  • EXTENDED : 逐行创建索引（替代以排序方式一次创建一个索引）
  • 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_TABLE_MAIN7

一、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建议（为什么不是Mysql建议⊙﹏⊙）先不加选项执行myqlcheck命令。如果需要修复再次执行它。
  • 一些mysqlcheck限制选项：
  • –repair –quick : 尝试执行一次快速修复
  • –repair : 常规修复（如果快速修复失败）
  • –repair –force : 强制执行一次修复
  • mysqlcheck样例：

MYSQL_TABLE_MAIN8

 

备注：

默认情况下，mysqlcheck将第一个非选项参数解读为数据库名，并且检查数据库中所有表。如果在数据库名参数后有其他参数，将被作为表名，并且仅仅检查这些表。

上图展示的mysqlcheck 样例说明了以下内容：

  • 第一条带选项的指令仅仅检查world_innodb数据库中的City和Country表。
  • 带有–database（或 -B）选项的样例中,mysqlcheck会将该参数作为数据库名，并且检查名为world_innodb和test数据库中的表。
  • 带有–all-database（或 -A）选项时，mysqlcheck检查所有数据库中的所有表。

 

一、myisamchk 工具

 

  • myisamchk 是一个检查MyISAM引擎表的非客户端实用工具
  • myisamchk 和mysqlcheck工具有以下差异：
  • 它可以启用和禁用索引
  • 它可以不通过服务器直接访问表文件
  • 这避免了并发表查询
  • 一些myisamchk 的选项：
  • –recover : 修复一张表
  • –safe-recover : 修复一张不能使用–recover选项修复的表？

 

  • myisamchk样例：

MYSQL_TABLE_MAIN9

 

备注：

从概念上看，myisamchk和mysqlcheck在功能上很相似。然而，myisamchk并不和MySQL服务器通信。相反的，它直接从文件访问表。

在执行表维护时使用myisamchk可以避免表的并发访问：

  1. 确保在服务器工作时不能访问表。保障的方法是锁定表或者停止服务器。
  2. 在命令提示符中，将当前目录更改为数据库中存放表的路径。 这是具有和数据库包含的将要被检查的表有同样名字的服务器的数据目录的子目录？（切换目录的原因是可以更方便的查看表文件）
  3. 使用选项调用myismchk指明将要执行的操作，跟在myismchk指令后的参数指定将要被操作的表名。每个参数都可以是表名或表上索引文件的名字。索引文件名和表名一致，并添加.MYI后缀。因此，一张表可以被写作table_name（表名）或MYI（该表的索引文件名）。
  4. 重启服务器。

注意：

先使用–recover，inwei–safe-recover会慢许多。

 

 

 

一、mysqlcheck和myisamchk的选项

 

控制维护执行类型的选项：

 

MYSQL_TABLE_MAIN10

 

 

备注：

myisamchk和mysqlcheck都有多个控制表维护操作类型的选项。

上图的表中列举了一些常用的选项，其中大部分可以在两个程序（mysqlcheck和myisamchk）中使用。除了这种情况外，（其余情况）将在相关选项说明中指出。？

  • –analyze :

分析表中键值分布。这可以提高基于索引查找的查询语句速度的性能。

  • –auto-repair :

如果检查操作发现问题，自动修复表。

  • –check 或 -c :

检查表的问题。当没有指定其他选项时，该选项为默认动作。

  • –check-only-changed 或 -c :

跳过表查询，除非表自从上次检查后发生了改变或者没有正确关闭。后者可能在表打开时服务器崩溃的情况下发生。

  • –fast 或 -F :

跳过表检查除非表没有被正确关闭。

  • –extended,–extend-check,或 -e :

运行扩展的表检查。

对于mysqlcheck，当该选项和repair（修复）选项一起使用时，将会比单独使用repair       （修复）选项时进行更全面彻底的修复。就是说，使用–repair –extended选项比使用     –repair选项执行更彻底的修复操作。

 

 

续：十一、mysqlcheck和myisamchk的选项

  • –extended,–extend-check,或 -e :

运行中级表检查

  • –quick或-q :

对mysqlcheck指令，使用–quick选项不使用repair（修复）选项，仅仅只有索引文件被检查，而不检查数据文件。对于两个程序（mysqlcheck和myisamchk），使用–quick选项和repair（修复）选项，仅仅只有索引文件被检查，而不检查数据文件。

  • –repair,–recover,或 -r :

运行一个表修复操作。

 

一、InnoDB表维护

 

  • InnoDB引擎表在故障发生后自动修复
  • 使用CHECK TABLE或者一个客户端程序找出不一致、不兼容和其他问题。
  • 使用mysqldump语句通过导出还原一张表：

MYSQL_TABLE_MAIN11

  • 然后删除它并且从导出的文件重新创建表。

MYSQL_TABLE_MAIN12

 

  • 为了在（服务器）崩溃后恢复表，使用–innodb_force_recovery选项启动服务器，或者从一个备份复原表。
  • 使用ALTER TABLE重建表和释放在群集索引中未使用的空间以进行优化

 

备注：

如果表检查显示有问题，使用mysqldump指令通过导出还原表到一致性状态，删除它，然后再从导出的文件重建表。

万一发生数据库运行的主机或者服务器崩溃，使用InnoDB存储引擎的表可能额处于不一致的状态。作为（服务器）启动的一个步骤，InnoDB存储引擎将执行自动恢复。

极少见的情况下，可能服务器由于错误的自动恢复不能启动。如果遇到这样的情况，可以尝试以下的过程：

  • 重启服务器时，将–innodb_force_recovery选项值设置为1 – 6。

这个值表明避免崩溃警告的增进级别和恢复表时宽限表可能不一致的增进级别。

建议值从4开始，可以防止insert buffer（插入缓冲区）合并操作发生。

  • 当服务器以参数–innodb_force_recovery选项设置为非零值启动时，InnoDB阻止INSERT,UPDATE或DELETE操作。因此，当该选项生效时你需要导出InnoDB表，然后删除表。然后，再去掉–innodb_force_recovery选项启动服务器，当服务器启动后，从导出的文件恢复表。
  • 如果前面描述的步骤都失败了，从以前的备份还原表。

查阅http://dev.mysql.com/doc/mysql/en/forcing-innodb-recovery.html可以获得更多从崩溃的数据库启动InnoDB的信息。

 

 

一、MyISAM表维护

 

  • 使用CHECK TABLE…MEDIUM（该选项为默认选项）
  • 使用客户端程序运行myisamchk：

MYSQL_TABLE_MAIN14

MYSQL_TABLE_MAIN13

 

  • 表损坏使用通常发生在索引中，标记这些检查将会很有用。
  • 当表损坏时，该工具修复表。
  • 设置服务器运行检查并且自动修复表。
  • 使用 –myisam-recover选项启用自动修复
  • 服务器在启动后第一次访问是检查每一张MyISAM表，以确保上一次是正常关闭。
  • 根据“config”文件强制恢复MyISAM表。

 

备注：

默认的CHECK TABLE（检查表）检查类型是为动态与静态格式的表执行MEDIUM（中级）检查。

如果一个静态格式表类型设置为CHANGED或FAST，则默认值为QUICK。

对于CHANGED和FAST值将会跳过行扫描，因为这些行很少会出现损坏。

如果表被标记为“corrupted”或“not closed ”，CHECK TABLE将修改表。

如果没有发现表上有任何问题，将会被标记为“up to date”。

如果表有损坏，问题多数出现在索引而不是数据上。

选项 –myisam-recover可以包含以下一个或多个以逗号分割的列表：

  • DEFAULT : 默认检查
  • BACKUP : 告诉服务器对必须改变的表进行备份
  • FORCE : 即使丢失超过一行数据，仍然执行表恢复
  • QUICK :

执行一次快速恢复。恢复跳过删除或更新的行没有间隔（known as“holes”？）的表。

 

 

续：备注：

例如，告知服务器对发现有问题的MyISAM表进行强制恢复，但要对表改变的部分做一个备份，添加以下内容到你的选项文件：

 

[mysqld]
Myisam-recover=FORCE,BACKUP

 

 

 

一、内存表维护

 

  • 当使用一条..WHERE语句删除行记录时，MEMORY（内存）表不释放内存。
  • 为了释放内存，你必须执行一条空ALTER TABLE操作。
  • 为了查看MEMORY（内存）表的存储要求，执行SHOW TABLE STATUS并且添加Data_length和Index_length列。
  • 如果删除行没有重复值，Data_free列为非零。

 

备注：

以下是一条SHOW TABLE STATUS语句在world_innodb数据库City表副本CityCopy表上执行的输出结果。

CityCopy表使用MEMORY存储引擎：

 

mysql> SHOW TABLE STATUS LIKE 'CityCopy'\G

*************************** 1. row ***************************
Name: CityCopy
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 4079
Avg_row_length: 67
Data_length: 383072
Max_data_length: 10808373
Index_length: 253984
Data_free: 0
...:
1 row in set (0.00 sec)
The total memory the CityCopy table occupies can be calculated as follows:
Data_length (383072) + Index_length (253984) = 637056 bytes

 

二、ARCHIVE表维护

 

  • ARCHIVE压缩问题：
  • 数据插入时表的行记录被压缩。
  • 进行检索时？，行记录可以根据需要不压缩、
  • 一些SELECT语句可能导致压缩恶化？
  • 使用OPTIMIZE TABLE或REPAIR TABLE可以实现更好的压缩。
  • OPTIMIZE TABLE在表没有被访问（读或写）时生效。

 

三、总结

 

在本课程中，你已经学习到如何：

  • 认识表维护操作的类型
  • 执行SQL语句维护表
  • 使用客户端和工具程序维护表
  • 根据具体的存储引擎维护表

 

实践13-1概览：

测验 – 维护表

 

在本测验中，你需要回答关于MySQL表维护的问题。

 

实践13-2概览：

使用维护表的SQL语句

在本测验中，你使用SQL语句检查和优化表。

 

实践13-3概览：

使用表维护工具

在本测验中，你使用MySQL命令行工具检查并修复一张表。

 

 

Filed Under: MYSQL

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号