mysql如何消除表空间碎片

冷不防 2024-02-19 20:05 63阅读 0赞

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'

查看表实际占用磁盘大小：  
\[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)，同时，数据库性能得到很大提升。  
  
原文：https://blog.csdn.net/zengxuewen2045/article/details/52039728