mysql碎片整理 提速_MysqL碎片整理优化

先来说一下什么是碎片，怎么知道碎片有多大！

简单的说,删除数据必然会在数据文件中造成不连续的空白空间,而当插入数据时,这些空白空间则会被利用起来.于是造成了数据的存储位置不连续,以及物理存储顺序与理论上的排序顺序不同,这种是数据碎片.实际上数据碎片分为两种,一种是单行数据碎片,另一种是多行数据碎片.前者的意思就是一行数据,被分成N个片段,存储在N个位置.后者的就是多行数据并未按照逻辑上的顺序排列.当有大量的删除和插入操作时,必然会产生很多未使用的空白空间,这些空间就是多出来的额外空间.索引也是文件数据,所以也会产生索引碎片,理由同上,大概就是顺序紊乱的问题.Engine 不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引。这样不但会浪费空间，并且查询速度也更慢。

解决方案：(切记，一定要在夜里执行，表越大，越耗资源时间，不要频繁修复，可以几个月甚至一年修复一次，如果表频繁被更改，可以按周/月来整理。)

mysql> select

ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE

from TABLES

where

TABLE_SCHEMA='test_db' and TABLE_NAME='table_name'

limit 1;

image.png

通过OPTIMIZE TABLE　table_name 后再查询一下结果如下

image.png

例如:

use information_schema; -- 需要在这个数据库里面查询

select

ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE

from TABLES

where

TABLE_SCHEMA='zqkd-article' and TABLE_NAME='wx_feed'

limit 1;

一、Innodb存储引擎清理碎片方法

ALTER TABLE tablename ENGINE=InnoDB

自己测试过这个也可以使用

二、Myisam存储引擎清理碎片方法

OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.所以把 Optimize 命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响.比较好的方式就是做个 Script,定期检查mysql中 information_schema.TABLES字段,查看 DATA_FREE 字段,大于0话,就表示有碎片.脚本多长时间运行一次,可以根据实际情况来定,比如每周跑一次.

OPTIMIZE TABLE table_name

亲测两个方法都可以对Innodb引擎表进行碎片优化

MySQL 5.0 系列 (即5.0.x 版本)

如果你是这个版本，很遗憾所有alter table 操作都会进行temp table copy。以下是摘自官方文档的解释：

If you use any option to ALTER TABLE other than RENAME, MySQL always creates a temporary table, even if the data wouldn't strictly need to be copied (such as when you change the name of a column).

MySQL 5.1 系列 (即5.1.x 版本)

以下操作不会有copy temp table操作，即锁表时间较短。

1. ALTER TABLE tbl_name RENAME TO new_tbl_name

2. 不涉及数据修改的操作

2.1 列改名 (除了innodb)

2.2 修改默认值 (注意：必须使用 modify ，而不能使用change)

2.3 增加ENUM的枚举定义 (注意：仅当新增枚举在当前允许最大值內，例：1B 可存8个枚举，2B可存128个枚举)

3. 通过add partition 添加分区

4. 重命名索引

5. 添加删除索引 (仅 innodb plugin支持)

详见官方文档说明：

For ALTER TABLE *tbl_name* RENAME TO *new_tbl_name* without any other options, MySQL simply renames any files that correspond to the table tbl_name without making a copy. (You can also use the RENAME TABLEstatement to rename tables. See Section 13.1.33, “RENAME TABLE Syntax”.) Any privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually.

Alterations that modify only table metadata and not table data can be made immediately by altering the table's.frm file and not touching table contents. The following changes are fast alterations that can be made this way:

* Renaming a column, except for the [`InnoDB`](http://dev.mysql.com/doc/refman/5.1/en/innodb-storage-engine.html "14.6. The InnoDB Storage Engine") storage engine.

* Changing the default value of a column (except for [`NDB`](http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html "Chapter 17. MySQL Cluster NDB 6.1 - 7.1") tables; see [Limitations of ](http://dev.mysql.com/doc/refman/5.1/en/alter-table-online-operations.html#alter-table-online-limitations)[`NDBCLUSTER`](http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html "Chapter 17. MySQL Cluster NDB 6.1 - 7.1") online operations).

* Changing the definition of an [`ENUM`](http://dev.mysql.com/doc/refman/5.1/en/enum.html "11.4.4. The ENUM Type") or [`SET`](http://dev.mysql.com/doc/refman/5.1/en/set.html "11.4.5. The SET Type") column by adding new enumeration or set members to the *end* of the list of valid member values, as long as the storage side of the data type does not change. For example, adding a member to a [`SET`](http://dev.mysql.com/doc/refman/5.1/en/set.html "11.4.5. The SET Type") column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this will require a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.

ALTER TABLE ... ADD PARTITION creates no temporary table except when used with NDB tables. ADD or DROPoperations for RANGE or LIST partitions are immediate operations or nearly so. ADD or COALESCE operations forHASH or KEY partitions copy data between changed partitions; unless LINEAR HASH or LINEAR KEY was used, this is much the same as creating a new table (although the operation is done partition by partition). REORGANIZEoperations copy only changed partitions and do not touch unchanged ones.

Renaming an index, except for InnoDB.

Adding or dropping an index, for InnoDB (if InnoDB Plugin is used) and NDB.