MySQL optimize/analyze/check/repair table

一、optimize table

官方文档： http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html

语法：

 

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...

NO_WRITE_TO_BINLOG | LOCAL 标示是否记录到binlog ，在主从备份的场景下，加上该参数则不会在从库执行optimize命令

 

一般执行完以下命令： REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE 最好

执行一下FLUSH TABLES清空缓存表。

 

作用：优化表主要作用是消除删除或者更新造成的空间浪费。可以回收空间、减少碎片、提高I/O

官方文档写的：Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. （整理数据表以及相关索引的物理存储数据，减少空间大小并且提高访问表的I/0效率）

 

 

如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。

 

提要：

1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。

2.OPTIMIZE TABLE只对MyISAM，ARCHIVE和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。

3.在5.6.17版本之前的InnoDB或其它类型表， OPTIMIZE TABLE运行过程中，MySQL会锁定表；MySQL 5.6.17之后，使用online DDL (ALGORITHM=INPLACE) 只在一个短暂的时间间隔进行锁定。（原文：As of 5.6.17, OPTIMIZE TABLE uses online DDL (ALGORITHM=INPLACE) for both regular and partitioned InnoDB tables. The table rebuild, triggered by OPTIMIZE TABLE and performed under the cover by ALTER TABLE ... FORCE, is now performed using online DDL (ALGORITHM=INPLACE) and only locks the table for a brief interval, which reduces downtime for concurrent DML operations.）

在对数据量小的表操作时，optimze table是挺快的，但是对一张有海量数据的表进行optimze table操作时，就很悲剧了，因为不但执行时间会很长，而且会锁表。这个时候就应该考虑使用一些运维手段避免现网的服务受到影响。

 

对于MyISAM表，OPTIMIZE TABLE按如下方式操作：

如果表已经删除或分解了行，则修复表。

如果未对索引页进行分类，则进行分类。

如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新。

 

mysql> OPTIMIZE table a;
+--------+----------+----------+-----------------------------+
| Table  | Op       | Msg_type | Msg_text                    |
+--------+----------+----------+-----------------------------+
| test.a | optimize | status   | Table is already up to date | 
+--------+----------+----------+-----------------------------+
1 row in set (0.00 sec)

 

 

对于InnoDB表的优化方法：

1. 默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。

重启之后执行

OPTIMIZE TABLE a;

(4990 row(s) affected)
Execution Time : 00:00:01:779
Transfer Time  : 00:00:00:000
Total Time     : 00:00:01:779

 

 2. 执行命令：

ALTER TABLE table_name ENGINE=INNODB; 

 该方法会对旧表以复制的方式新建一个新表，然后删除旧表。虽然这个过程是安全的，但是在进行操作时还是先进行备份为好。

 

怎样查看优化后的数据变化：

SHOW TABLE STATUS LIKE 'table_name ';

-- show table status 官方文档： http://dev.mysql.com/doc/refman/5.6/en/show-table-status.html 

返回的结果，其中Data_free 表示 被整序，但是未使用的字节的数目。这个数据对于分区表只是估算值，如果要获取准确数据，可执行下面的查询：

SELECT    SUM(DATA_FREE)
    FROM  INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA = 'db_name'
    AND   TABLE_NAME   = 'table_name';

 

还有Data_free的数据并不一定表示当前表存在这些多余分配空间未利用，需要查看当前数据库是否独享表空间：

 SHOW VARIABLES LIKE 'innodb_file_per_table';

 返回值ON表示独享表空间，OFF表示共享。

如果是共享表空间，那么Data_free的数据就是所有表的未利用空间总和。

 

选择需要优化的表，正确地执行了optimize table 后，Data_free的值应该会为0

 

-------------------------------------------------分界线---------------------------------------------------------------------------

 

二、ANALYZE TABLE

 

用于收集优化器统计信息、和tuning相关、

这个命令对 MyISAM、BDB、InnoDB 存储引擎的表有作用

如果不想记录到binlog、也可加关键字local或者另外一个NO_WRITE_TO_BINLOG

 

MySQL中使用ANALYZE TABLE语句来分析表，该语句的基本语法如下：

 

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]

 

本语句用于分析和存储表的关键字分布。在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM, BDB和InnoDB表有作用。对于MyISAM表，本语句与使用myisamchk -a相当。

 MySQL使用已存储的关键字分布来决定，当您对除常数以外的对象执行联合时，表按什么顺序进行联合。 

 

Analyze 用来分析和存储表的关键字的分布，使得系统获得准确的统计信息，影响 SQL 的执行计划的生成。对于数据基本没有发生变化的表，是不需要经常进行表分析的。但是如果表的数据量变化很明显，用户感觉实际的执行计划和预期的执行计划不 同的时候，执行一次表分析可能有助于产生预期的执行计划。

 

【示例18-8】 下面使用ANALYZE TABLE语句分析score表，分析结果如下：

 

mysql> ANALYZE TABLE score; 

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

| Table    | Op     | Msg_type | Msg_text  | 

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

| test.score | analyze | status    | OK       | 

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

1 row in set (0.05 sec)

上面结果显示了4列信息，详细介绍如下：

 

Table：表示表的名称；

 

Op：表示执行的操作。analyze表示进行分析操作。check表示进行检查查找。optimize表示进行优化操作；

 

Msg_type：表示信息类型，其显示的值通常是状态、警告、错误和信息这四者之一；

 

Msg_text：显示信息。

 

检查表和优化表之后也会出现这4列信息。

 

 

-------------------------------------------------分界线---------------------------------------------------------------------------

 

三、CHECK TABLE

MySQL中使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。而且，该语句还可以检查视图是否存在错误。该语句的基本语法如下：

 

CHECK TABLE 表名1 [,表名2…] [option] ;

其中，option参数有5个参数，分别是QUICK、FAST、CHANGED、MEDIUM和EXTENDED。这5个参数的执行效率依次降低。option选项只对MyISAM类型的表有效，对InnoDB类型的表无效。CHECK TABLE语句在执行过程中也会给表加上只读锁。

 

-------------------------------------------------分界线---------------------------------------------------------------------------

 

四、REPAIR TABLE

语法：

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]

 

用于修复被破坏的表。默认情况下，REPAIR TABLE与myisamchk --recovertbl_name具有相同的效果。REPAIR TABLE对MyISAM和ARCHIVE表起作用。

　　通常，您基本上不必运行此语句。但是，如果灾难发生，REPAIR TABLE很有可能从MyISAM表中找回所有数据。如果您的表经常被破坏，您应该尽力找到原因，以避免使用REPAIR TALBE。

 

 

 参考： http://www.cnblogs.com/littlehb/archive/2013/05/08/3067175.html

http://www.cnblogs.com/zqrferrari/archive/2011/06/29/2093675.html

http://www.cnblogs.com/ggjucheng/archive/2012/11/07/2758021.html