【mysql磁盤碎片】MySql磁盤碎片
2021-09-28 16:48:26

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

在查看表的status的時候，會顯示出來這個信息：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ù)之后進行刪除【不要一次性全部刪除，只刪除一部分】，那么data_free字段會增加，而且隨著刪除條目越來越多，這個字段也越來越大。

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

那么多次插入刪除之后，這個空間就是浪費了。因為雖然給分配了空間，卻沒使用到。

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

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

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

對于innodb引擎，可使用下列方法：

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

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

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

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

OPTIMIZE?可以整理數(shù)據(jù)文件,并重排索引。【據(jù)說僅針對myisam有效】