MySQL表维护语句

最新推荐文章于 2024-02-27 12:14:17 发布

jesseyoung

最新推荐文章于 2024-02-27 12:14:17 发布

阅读量2.9k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据仓库-MySQL 文章标签： mysql 表维护

本文链接：https://blog.youkuaiyun.com/jesseyoung/article/details/38228385

数据仓库-MySQL 专栏收录该内容

41 篇文章

订阅专栏

本文介绍了MySQL中用于维护和管理数据库的语句，包括ANALYZE TABLE、CHECK TABLE、CHECKSUM TABLE、OPTIMIZE TABLE和REPAIR TABLE。这些语句帮助检查表的健康状况、修复损坏、优化性能。通过示例详细讲解了每个语句的语法和应用场景，对数据库管理员进行日常维护非常有用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 简介

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

这些语句包括：

ANALYZE TABLE
CHECK TABLE
CHECKSUM TABLE
OPTIMIZE TABLE
REPAIR TABLE

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

2 创建测试表

创建表并写入数据

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 常用语句

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

****************************************************************************************
原文地址：http://blog.youkuaiyun.com/jesseyoung/article/details/38228385

博客主页：http://blog.youkuaiyun.com/jesseyoung
****************************************************************************************