MysqL碎片整理优化

MysqL碎片整理优化

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

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

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

公式:

数据总大小 = data_length + index_length

实际表空间文件大小 = rows X avg_row_length

碎片大小 = (数据总大小 — 实际表空间文件大小 )/1024/1024 = xxx MB

查看表碎片的方法

方法一（所有库的表）：

select table_schema db,table_name,data_free,engine from information_schema.tables
 where table_schema not in('information_schema', 'mysql')and data_free> 0 ORDER BY data_free DESC ;

方法二（单个表）：

SHOW TABLE STATUS LIKE '表名'

也可以观察Mysql的数据目录存储文件Data文件大小，如MYD

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

ALTER TABLE tablename ENGINE=InnoDB

方法三（单个表）：

select ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE from information_schema.TABLES where TABLE_SCHEMA='yibu_api' and TABLE_NAME='yibu_call_end' limit 1;

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

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

OPTIMIZE TABLE table_name