为什么要碎片整理
- MySQL中使用varchar、text、blob等可变长度的文本数据类型之后需要做额外的MySQL数据表碎片整理。
- 当MySQL从表中删除一行内容,该段空间就会被留空。如果有大量的删除操作,会使留空空间变得比存储列表内容所使用的空间更大。
- MySQL进行数据扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作,MySQL将尝试利用这些留空的区域,但仍然无法将其彻底占用。
碎片查看及整理
1 | select table_name,engine,table_rows,data_length,index_length,DATA_FREE from information_schema.tables where TABLE_NAME='chat_msgs'; |
information_schema.tables
- show create table information_schema.tables;
1 | CREATE TEMPORARY TABLE `TABLES` ( |
- desc information_schema.tables; 存储了数据表的元数据信息
Field | Type | Null | Key | Default | Extra | 说明 |
---|---|---|---|---|---|---|
TABLE_CATALOG | varchar(512) | NO | ||||
TABLE_SCHEMA | varchar(64) | NO | DB名 | |||
TABLE_NAME | varchar(64) | NO | 表名 | |||
TABLE_TYPE | varchar(64) | NO | ||||
ENGINE | varchar(64) | YES | NULL | |||
VERSION | bigint(21) unsigned | YES | NULL | |||
ROW_FORMAT | varchar(10) | YES | NULL | 是否压缩 | ||
TABLE_ROWS | bigint(21) unsigned | YES | NULL | 表的粗略行统计 | ||
AVG_ROW_LENGTH | bigint(21) unsigned | YES | NULL | |||
DATA_LENGTH | bigint(21) unsigned | YES | NULL | 表的大小(单位字节) | ||
MAX_DATA_LENGTH | bigint(21) unsigned | YES | NULL | |||
INDEX_LENGTH | bigint(21) unsigned | YES | NULL | 索引大小 | ||
DATA_FREE | bigint(21) unsigned | YES | NULL | 碎片大小 | ||
AUTO_INCREMENT | bigint(21) unsigned | YES | NULL | |||
CREATE_TIME | datetime | YES | NULL | |||
UPDATE_TIME | datetime | YES | NULL | |||
CHECK_TIME | datetime | YES | NULL | |||
TABLE_COLLATION | varchar(32) | YES | NULL | |||
CHECKSUM | bigint(21) unsigned | YES | NULL | |||
CREATE_OPTIONS | varchar(255) | YES | NULL | |||
TABLE_COMMENT | varchar(2048) | NO |
- 查看所有db
1 | select distinct TABLE_SCHEMA from information_schema.tables; |
- 查看表大小
1 | select (sum(DATA_LENGTH) + sum(INDEX_LENGTH)) as size from tables where table_schema='mysql' and table_name like 'time_%'; |
- 查看数据库大小
1 | select (sum(DATA_LENGTH) + sum(INDEX_LENGTH)) as size from tables where table_schema='mysql'; |
Notice
- 官方建议碎片整理每周或每月一次即可。
- optimize table 只对MyISAM、BDB、InnoDB表起作用,尤其MyISAM最为明显。并不是所有表都需要碎片整理,一般只需要对包含可变长度的文本数据类型的表进行整理即可。
- optimize table 运行过程中会锁定表。
- 默认情况下,直接对InnoDB引擎的数据表使用optimize table可能会显示[Table dose not support optimize,doing recreate + analyze instead] 的提示信息。这时候可以用mysqld –skip-new 或者 mysqld –safe-mode 命令来重启MySQL,以便让其他引擎支持optimize table。
- 对于mysql和Infobright等数据库,information_schema数据库中的表都是只读的,不能进行更新、删除和插入等操作,也不能加触发器,因为它们实际只是一个视图,不是基本表,没有关联的文件。