【mysql磁盤(pán)碎片】MySql磁盤(pán)碎片
2021-09-28 16:48:26

mysql當(dāng)然也會(huì)產(chǎn)生磁盤(pán)碎片。

在查看表的status的時(shí)候，會(huì)顯示出來(lái)這個(gè)信息：Data_free字段

?? Data_free??

The number of allocated but unused bytes.

Beginning with MySQL 5.1.24, this information is also shown for???InnoDB???tables (previously, it was in the???Comment??value).???InnoDB???tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace. If you are using multiple tablespaces and the table has its own tablespace, the free space is for only that table. Free space means the number of completely free 1MB extents minus a safety margin. Even if free space displays as 0, it may be possible to insert rows as long as new extents need not be allocated.

For partitioned tables, this value is only an estimate and may not be absolutely correct. A more accurate method of obtaining this information in such cases is to query the???INFORMATION_SCHEMA.PARTITIONS???table, as shown in this example:

SELECT    SUM(DATA_FREE)
    FROM  INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_SCHEMA = 'mydb'
    AND   TABLE_NAME   = 'mytable';

參考一下?

?這篇文章??

在插入數(shù)據(jù)之后進(jìn)行刪除【不要一次性全部刪除，只刪除一部分】，那么data_free字段會(huì)增加，而且隨著刪除條目越來(lái)越多，這個(gè)字段也越來(lái)越大。

然而在delete * from tb_name之后，data_free變?yōu)?了。mysql應(yīng)該是在這里做了空間優(yōu)化。

那么多次插入刪除之后，這個(gè)空間就是浪費(fèi)了。因?yàn)殡m然給分配了空間，卻沒(méi)使用到。

此時(shí)，就需要優(yōu)化了。

-------------------------------------------------------------------------------------------------

對(duì)于myisam引擎的表，可以使用optimize table tb_name來(lái)進(jìn)行空間優(yōu)化。

對(duì)于innodb引擎，可使用下列方法：

1、innodb 的optimize table 是映射到alter table的，做大innodb表優(yōu)化前先drop掉所有的index,然后optimize 完了再rebuild index.速度要快很多。

2、對(duì)于使用獨(dú)立表空間的innodb表，表比較小的可以ALTER TABLE table_name ENGINE = Innodb;表比較大的話(huà)，新創(chuàng)建一個(gè)表，把數(shù)據(jù)倒進(jìn)去然后drop原來(lái)表。

-------------------------------------------------------------------------------------------------

ALTER TABLEtable_nameENGINE = Innodb;這其實(shí)是一個(gè)NULL操作,表面上看什么也不做,實(shí)際上重新整理碎片了。當(dāng)執(zhí)行優(yōu)化操作時(shí),實(shí)際執(zhí)行的是一個(gè)空的 ALTER 命令,但是這個(gè)命令也會(huì)起到優(yōu)化的作用,它會(huì)重建整個(gè)表,刪掉未使用的空白空間。

OPTIMIZE?可以整理數(shù)據(jù)文件,并重排索引?！緭?jù)說(shuō)僅針對(duì)myisam有效】