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及其后续版本取消,不再罗列。
****************************************************************************************