一、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
相关推荐
mysqlcheck -u root -p --password=<your_password> --repair database_name table_name ``` 在实际操作中,务必谨慎使用`mysqlcheck.exe`,因为它可能会修改数据,所以在执行修复或优化之前,最好先备份重要的...
通过执行SQL语句,如`CHECK TABLE`, `REPAIR TABLE`, `ANALYZE TABLE` 和 `OPTIMIZE TABLE`,`mysqlcheck`简化了对数据库的维护。 ### mysqlcheck简介 `mysqlcheck`的基本用途是在不关闭MySQL服务器的情况下检查表...
这个工具可以执行多种操作,如检查(check)、修复(repair)、分析(analyze)和优化(optimize)表,帮助数据库管理员确保数据的完整性和性能。在本文中,我们将深入探讨`mysqlcheck`的使用方法,特别是针对InnoDB...
* 暂不支持ANALYZE/CHECK/CHECKSUM/OPTIMIZE/REPAIR TABLE,需要用透传语 * 暂不支持CACHE IN * 暂不支持FLUSH * 暂不支持KILL(非跨城版本数据库支持) * 暂不支持LOAD INDEX INTO CACHE * 暂不支持RESET * 暂不...
myisamchk -r /path/to/table/data ``` 在使用`myisamchk`时,要确保没有其他进程在访问该表。 **二、表分析** 分析表有助于更新索引统计信息,从而提高查询性能。以下是分析表的几种方法: 1. **使用`ANALYZE...
这里我们将深入探讨几个常用的MySQL维护命令,包括`ANALYZE TABLE`、`CHECKSUM TABLE`、`OPTIMIZE TABLE`、`CHECK TABLE`以及`REPAIR TABLE`。 1. **ANALYZE TABLE**: `ANALYZE TABLE`命令用于收集和更新表统计...
2. mysqlcheck或SQL语句修复:如果mysqld正在运行,可以使用mysqlcheck工具或内置的`CHECK TABLE`、`REPAIR TABLE`、`ANALYZE TABLE`和`OPTIMIZE TABLE` SQL语句进行修复。根据具体情况选择适合的方法。 在修复前,...
6. **表分析与优化操作**:如`ANALYZE TABLE`、`CACHE INDEX`、`CHECK TABLE`、`FLUSH LOAD INDEX INTO CACHE`、`OPTIMIZE TABLE`、`REPAIR TABLE`、`RESET`等命令也会触发隐式提交。 7. **复制相关命令**:如`START...
MySQL的分区表(Partitioned Table)功能是一种高级的数据库设计技术,旨在提升数据库性能和简化数据管理。分区的原理是将一个大表物理地划分为更小、更易管理的部分,这样可以加速特定的SQL查询,尤其是那些涉及...
- **表维护语句**:如`ANALYZE TABLE`用于分析表的统计信息,优化查询性能;`BACKUP TABLE`用于备份表;`CHECK TABLE`用于检查表的物理完整性和索引;`CHECKSUM TABLE`用于计算表的校验和;`OPTIMIZE TABLE`用于整理...
CHAPTER 1 Introduction to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.2 Database, ...