对MySQL表维护_MySQL表维护语句

MySQL支持几条与维护和管理数据库相关的语句。例如：使用其中一条，可以修复一个损坏的表，使用另一条，可以检查一个表的索引是否正确。通常，数据库管理员而不是开发者使用这些语句。

这些语句包括：

ANALYZE TABLE

CHECK TABLE

CHECKSUM TABLE

OPTIMIZE TABLE

REPAIR TABLE

这些语句组合在一起被称为：表维护语句。

创建表并写入数据

create table maintain_table(

c1 int not null auto_increment,

c2 varchar(10),

c3 int(10),

primary key(c1)

);

insert into maintain_table values('', 'name1', 001);

insert into maintain_table values('', 'name2', 002);

insert into maintain_table values('', 'name3', 003);

insert into maintain_table values('', 'name4', 004);

insert into maintain_table values('', 'name5', 005);

3.1 analyze table语句

基本语法：

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE

tbl_name [, tbl_name] ...    实例：

mysql> analyze no_write_to_binlog table maintain_table;

mysql> analyze local table maintain_table;

mysql> analyze table maintain_table;

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

| Table | Op | Msg_type | Msg_text |

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

| test.maintain_table | analyze | status | OK |

+---------------------+---------+----------+----------+    注：上面三个语句打印结果相同，唯一区别是带上local或no_write_to_binlog这两个参数，命令是不被写入binlog的。

执行该语句的时候innodb及myisam表会加上读锁，停止数据更新。该语句支持innodb，myisam及ndb表，针对myisam表，该语句等同myisamchk --analyze。

在linux命令行下查看binlog

mysqlbinlog mysql-bin.000001

......

BINLOG '

GuLVUxMCAAAAOQAAANobAAAAAL8AAAAAAAEABHRlc3QADm1haW50YWluX3RhYmxlAAMDDwMCHgAG

GuLVUxcCAAAALAAAAAYcAAAAAL8AAAAAAAEAA//4BQAAAAVuYW1lNQUAAAA=

'/*!*/;

# at 7174

#140728 13:39:38 server id 2 end_log_pos 7201 Xid = 72001413

COMMIT/*!*/;

# at 7201

#140728 13:50:28 server id 2 end_log_pos 7292 Query thread_id=102 exec_time=0 error_code=0

SET TIMESTAMP=1406526628/*!*/;

analyze table maintain_table

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;    可见只有analyze table maintain_table;这一条语句写入了binlog。

3.2 checksum table语句

对于每个表，可以获得一个校验和，用于校验数据传输的完整性问题。对于myisam创建的表，校验和都存储在表中，叫做实时校验和(live checksum)，如果数据改变，实时校验和也会改变。

基本语法：

CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]    实例：

mysql> checksum table maintain_table;

mysql> checksum table maintain_table extended;

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

| Table | Checksum |

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

| test.maintain_table | 2338316854 |

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

mysql> checksum table maintain_table quick;

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

| Table | Checksum |

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

| test.maintain_table | NULL |

+---------------------+----------+    注：若不指定默认为extended。如果使用了extended，那么分析和计算最初的表，即便这个表是myisam表，也不使用实时校验和，而是重新计算。

若指定了quick，如果是myisam表，返回实时校验和，否则返回NULL。

3.3 check table语句

用来检查数据库表和索引是否损坏。如果mysql查询一个表后打印Incorrect key file for table '***'; try to repair it，则说明这张表坏了。

基本语法：

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

实例：

mysql> check table maintain_table;

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

| Table | Op | Msg_type | Msg_text |

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

| test.maintain_table | check | status | OK |

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

mysql> check table maintain_table for upgrade;

mysql> check table maintain_table quick;

mysql> check table maintain_table fast;

mysql> check table maintain_table medium;

mysql> check table maintain_table extended;

mysql> check table maintain_table changed;

注：for upgrade主要检查旧的数据库数据在新的数据库里的兼容性，quick是最快的选项，使用表中的行不会检查错误的连接。fast，medium，extened，changed主要针对myisam表，其中：

fast：检查表是否被正确的关闭了。

changed：可以和fast对比，不同点在于检查check语句之后发生变化的表。

medium：检查索引数据及表数据之间的连接是否正确。

extended：最综合最慢的选项。

3.4 optimize table语句

数据库不断地创建，删除，更新后会在磁盘上产生很多碎片文件，使数据在磁盘上比较分散，optimize table用来整理数据。

基本语法：

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE

tbl_name [, tbl_name] ...

实例：

mysql> optimize table maintain_table;

mysql> optimize local table maintain_table;

mysql> optimize no_write_to_binlog table maintain_table;

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

| Table | Op | Msg_type | Msg_text |

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

| test.maintain_table | optimize | note | Table does not support optimize, doing recreate + analyze instead |

| test.maintain_table | optimize | status | OK |

+---------------------+----------+----------+-------------------------------------------------------------------+    注：我们应该定期的在更新比较频繁的表上执行optimize table语句。

和analyze一样，默认optimize命令是写入binlog的，加上local或no_write_to_binlog这两个参数，命令是不被写入binlog的。

3.5  repair table语句

check table语句可以检查一个表中的的问题，若表或索引损坏，可以使用repair table语句尝试修正它。如果不起作用，可以使用myisamchk这样的工具。

注意：repair table只适用于myisam，archive引擎创建的表。

基本语法：

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE

tbl_name [, tbl_name] ...

[QUICK] [EXTENDED] [USE_FRM]

实例：

mysql> alter table maintain_table engine=myisam;

mysql> repair table maintain_table;

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

| Table | Op | Msg_type | Msg_text |

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

| test.maintain_table | repair | status | OK |

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

mysql> repair local table maintain_table;

mysql> repair local table maintain_table quick;

mysql> repair local table maintain_table extended;

mysql> repair local table maintain_table use_frm;

mysql> repair no_write_to_binlog table maintain_table use_frm;

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

| Table | Op | Msg_type | Msg_text |

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

| test.maintain_table | repair | warning | Number of rows changed from 0 to 5 |

| test.maintain_table | repair | status | OK |

+---------------------+--------+----------+------------------------------------+    注：和analyze一样，默认repair命令是写入binlog的，加上local或no_write_to_binlog这两个参数，命令是不被写入binlog的。

quick：这个选项是最快的，MySQL尝试只修改索引树。

extended：索引一行一行的构建，而不是一次性创建整个索引。

use_frm：如果MYI文件完全丢失或头部损坏，必须使用这一选项。那么整个索引一次性重新建立。

3.6 backup table / restore table

mysql5.5及其后续版本取消，不再罗列。

****************************************************************************************