mysql如何消除表空间碎片

MySQL 清除表空间碎片 碎片产生的原因:
（1）表的存储会出现碎片化，每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大；
（2）当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；
  (3）当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分；
例如：
一个表有1万行，每行10字节，会占用10万字节存储空间，执行删除操作，只留一行，实际内容只剩下10字节，但MySQL在读取时，
仍看做是10万字节的表进行处理，所以，碎片越多，就会越来越影响查询性能。

查看表碎片大小
（1）查看某个表的碎片大小
mysql> SHOW TABLE STATUS LIKE '表名';
结果中’Data_free’列的值就是碎片大小
（2）列出所有已经产生碎片的表
mysql> 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;

清除表碎片
（1）MyISAM表
mysql> optimize table 表名
（2）InnoDB表
mysql> alter table 表名 engine=InnoDB;

Engine不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引.OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.所以把 Optimize 命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响.比较好的方式就是 做个shell,定期检查mysql中information_schema.TABLES字段,查看 DATA_FREE 字段,大于0话,就表示有碎片。

 下面用一个案例说明如何来消除表空间碎片和回收独占表空间的空间大小？
查询lots库中t_ls_tranlog表的大小和碎片大小：
[lots]> SELECT TABLE_NAME,(DATA_LENGTH+INDEX_LENGTH)/1024/1024 size_mb,data_free/1024/1024 free_mb,TABLE_ROWS FROM information_schema.tables where table_schema='LOTS' 

and TABLE_NAME='t_ls_tranlog'  and data_free/1024/1024>=100 order by free_mb desc;
+--------------------------+---------------+---------------+------------+
| TABLE_NAME               | size_mb       | free_mb       | TABLE_ROWS |
+--------------------------+---------------+---------------+------------+
| t_ls_tranlog      | 1989.03125000 | 3830.00000000 |   12219798 |
+--------------------------+---------------+---------------+------------+
4 rows in set (0.00 sec)

查看表实际占用磁盘大小：
[apps@mvxl0782 lots]$ ls -al|grep t_ls_tranlog
-rw-r--r-- 1 apps apps        4777 May 20 11:08 t_ls_tranlog.frm
-rw-r--r-- 1 apps apps  6446645248 May 20 10:50 t_ls_tranlog.ibd

消除碎片操作：
[lots]> alter table t_ls_tranlog engine=InnoDB;
Query OK, 0 rows affected (3 min 14.11 sec)         
Records: 0  Duplicates: 0  Warnings: 0

再查询lots库中t_ls_tranlog表的大小和碎片大小：
[lots]> SELECT TABLE_NAME,(DATA_LENGTH+INDEX_LENGTH)/1024/1024 size_mb,data_free/1024/1024 free_mb,TABLE_ROWS FROM information_schema.tables where table_schema='LOTS' 

and TABLE_NAME='t_ls_tranlog' order by free_mb desc;
+---------------------+---------------+------------+------------+
| TABLE_NAME          | size_mb       | free_mb    | TABLE_ROWS |
+---------------------+---------------+------------+------------+
| t_ls_tranlog | 1541.00000000 | 0.00000000 |   18082744 |
+---------------------+---------------+------------+------------+
1 row in set (0.00 sec)
再查看表实际占用磁盘大小：
[apps@mvxl0782 lots]$ ls -al|grep t_ls_tranlog
-rw-rw---- 1 apps apps        4777 Jul 26 20:47 t_ls_tranlog.frm
-rw-rw---- 1 apps apps  1623195648 Jul 26 20:50 t_ls_tranlog.ibd

从上面可看到，对碎片进行整理后，表文件t_ls_tranlog.ibd由原来的6446645248 byte变成了1623195648 byte，空间释放了4823449600 byte(约4600MB)，同时，数据库性能得到很大提升。