【MySQL学生手册】表维护操作类型

本文地址:https://www.askmac.cn/archives/mysql-maintenance-type.html

 

第10章 表的维护

 

章节概述

本章介绍如何在MySQL中进行表的维护管理。你会了解:

  • 分辨表维护操作类型
  • 执行表维护SQL语句
  • 使用客户端及工具程序来进行表维护
  • 修理InnoDB表
  • 启用对MyISAM表的自动修复

 

10.1 表维护操作类型

一些表维护操作对于判定并修正数据库中的问题(例如,当一张表由于服务器奔溃而导致损坏后)或帮助MySQL优化表查询时非常有用。MySQL(根据存储引擎)可允许你执行几种类型的维护操作:

存储引擎名 MyISAM InnoDB
CHECK TABLE 完整检查更新索引统计信息 完成检查
REPAIR TABLE 修理讹误表 N/A
ANALYZE TABLE 更新索引统计信息 更新索引统计信息
OPTIMIZE TABLE 回收被浪费的空间表碎片整理索引页排序

更新索引统计信息

表重建(MySQL 5.7.4以后部分使用了online DDL的机制避免了表拷贝)

10.1.1 分析和优化

对于表分析和优化操作,你可能希望定期执行以保持你的表一直处于最佳状态:

  • 当MySQL对一张MyISAM或InnoDB表进行分析时,它会更新其索引统计信息。优化器之后在处理查询时会使用这些统计信息,以便对如何最佳地查询表记录、表连接时读取表的顺序等做出更佳的判定。
  • 当MySQL优化了一张MyISAM表,它会对数据文件中的碎片进行整理以回收未使用的空间,排序索引,并更新索引统计信息。定期进行表碎片整理有助于加快包含有非定常列(如VARCHAR, VARBINARY, BLOB或TEXT)的表的访问,在此类表中进行频繁地插入或删除会导致存留许多空间间隙。碎片整理能有效降低这些间隙。

 

对于那些已经被填入数据并在之后一时不会再被修改的表,表分析和优化操作无疑对之后那些对此表进行的查询有最大的好处。随着表持续被更新修改,这些好处将被逐渐削弱,因此你需要定期地重复进行这类分析和优化操作。

 

表维护清理所用的工具包括有SQL语句如CHECK TABLE和REPAIR TABLE,客户端程序如mysqlcheck, myisamchk等工具,以及服务端对于自动恢复的能力。之后我们对这些工具进行介绍。

 

10.2 用于表维护操作的SQL语句

MySQL有几种SQL语句来进行表维护:CHECK TABLE用于完整性检查,REPAIR TABLE用于表修理,ANALYZE TABLE进行分析,OPTIMIZE TABLE被用于优化。本节中我们会重点对这些SQL语句进行讲解。

 

每个语句,当被发出后,服务端就会根据所需的操作进行执行。其中语句会接受一个或多个表名,或者可选的关键字来对基本的操作进行调整。一个表前一个不指定数据名来隐式指明为当前库下的表,或以db_name.table_name形式指定所要操作的数据库下的表。例如,如果world为当前数据库,以下语句等同于指导服务端对world.City表进行检查:

mysql> CHECK TABLE City;
mysql> CHECK TABLE world.City;

在执行完所要求的操作后,服务端返回操作结果信息,信息结果行分四列:

  • Table: 被处理的表
  • Op: 被执行的操作(check, repair, analyze或optimize)
  • Msg_type: 指提示成功或失败的返回信息类别。
  • Msg_text: 提供额外信息。

 

10.2.1 CHECK TABLE

CHECK TABLE语句可对表结构和内容进行完整性检查。它可用于MyISAM表和InnoDB表。对于MyISAM表,它还会对索引统计信息进行更新。如果进行检查的对象是一张view,CHECK TABLE语法也会对此视图的定义进行验证。当CHECK TABLE对于表的检查返回了一些问题,那么这张表就需要进行修复。

 

例如,在进行表修复前,CHECK TABLE语句应该被用于探测硬件问题(如,ram坏损,磁盘坏道等)。

简单语法如下:

CHECK TABLE <table_name> [, <table_name>] ;

 

10.2.2 REPAIR TABLE

REPAIR TABLE语句被用于修复表中的讹误问题。它仅可用于MyISAM,ARCHIVE及CSV表。当然,你也可以让服务端来自动对MyISAM表进行修复。

 

例如,当一次非正常关机(断电)或MyISAM表存在讹误的行时,可以使用REPAIRE TABLE语句进行恢复。

简单语法:

REPAIR TABLE <table_name> [, <table_name>] ;

 

10.2.3 ANALYZE TABLE

ANALYZE TABLE语句会更新表中关于键值分布的统计信息。这些信息被优化器用来对执行计划做出更佳判定。语句可用于MyISAM和InnoDB表。

 

简单语法:

ANALYZE TABLE <table_name> [, <table_name>] ;

 

10.2.4 OPTIMIZE TABLE

OPTIMIZE TABLE语句可以对MyISAM表进行碎片清理。清理会涉及对由于删除、更新、合并记录而造成的存储不连续(空间空隙)进行整理,回收这些不被使用的空间碎片。OPTIMIZE TABLE 也会对无序的索引页进行排序并,更新索引统计信息。

例如,使用OPTIMIZE TABLE语句来对表进行整理,简单语法:

OPTIMIZE TABLE <table_name> [, <table_name>] ;

 

对于每种存储引擎的命令匹配:

OPTIMIZE TABLE可作用于InnoDB表,但是其本质是匹配使用ALTER TABLE语句来实现表的重建。优化命令也会更新索引统计信息并释放簇索引中的闲置空间。

 

通过之前例子中的Note: Table does not support optimize, doing recreate + analyze instead,我们可以将作用于InnoDB表的OPTIMIZE TABLE底层实现理解为:

mysql> alter table City force;
mysql> analyze table City;

或是

mysql> alter table City engine=InnoDB;
mysql> analyze table City;

 

OPTIMIZE TABLE也可以运作于其他引擎表,不过其实现是通过执行匹配的ALTER TABLE命令来完成的,且一般情况下OPTIMIZE TABLE并不支持其它存储引擎,你需要在MySQL服务端启动时启用--skip-new项来启用支持。

 


Posted

in

by

Tags:

Comments

One response to “【MySQL学生手册】表维护操作类型”

Leave a Reply

Your email address will not be published. Required fields are marked *