辛星浅谈mysql中的数据碎片以及引擎为MyISAM下的操作

时间：2023-11-23 18:44:26

对于mysql中的数据碎片，事实上和我们删除数据是息息相关的，删除数据的时候必定会在数据文件里造成不连续的空白空间，对于少量的数据的删除，并不会产生多少的空白空间。假设在一段时间内的大量的删除操作，会使得这样的留空的空间变得比存储列表内容所使用的空间更大。

可能有人会说。我们向数据库中插入数据的时候，会不会在这些空白空间中插入数据呢？答案是会的。

可是，它会造成一个后果。那就是数据的存储位置不连续，以及物理存储顺序与理论上的存储顺序不同样，这就比較麻烦了。

依照数据碎片的大小来分，能够分为单行数据碎片和多行数据碎片。

事实上不只会产生数据碎片。假设加了索引，还会产生索引碎片，这样会造成顺序的紊乱。因为MySQL的引擎的内部实现机制不同，在数据碎片的处理上也会不同。

对于MyISAM来说，由于它的索引和数据以及表结构分为三个文件来存储，因此optimize能够整理数据文件，而且又一次排序。这样由于数据碎片产生的性能问题会降低非常多，直接使用【optimize table 表名】就可以，可是此时也应该注意一些问题。由于该操作会锁住表。所以我们尽量定期整理一下碎片，在訪问量小的时候来做这件事。

我们能够查看information_schema数据库下的tables中的free_data字段就可以，假设该字段不为0，则产生了数据碎片，看以下操作：

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use information_schema;

Database changed

mysql> select data_free from tables;

+-----------+

| data_free |

+-----------+

|         0 |

|         0 |

...中间省略部分数据

|         0 |

|        72 |

|         0 |

|         0 |

+-----------+

162 rows in set (0.06 sec)

mysql>

这里我们是查看全部的表中的数据碎片。假设我们想单独看某一个表的数据碎片，看以下操作：

第一步，我们先建立一个库和一个表，并向当中插入四条数据：

mysql> create database xinxing;

Query OK, 1 row affected (0.16 sec)

mysql> use xinxing;

Database changed

mysql>

mysql> create table xin (c char(40)) engine = myisam;

Query OK, 0 rows affected (0.06 sec)

mysql>

mysql> insert into xin values('xiaohei'),('xiaoqian'),

    -> ('xiaolin'),('xiaonan');

Query OK, 4 rows affected (0.03 sec)

Records: 4  Duplicates: 0  Warnings: 0

第二步，我们查看这个表的信息：

mysql> show table status from xinxing \G

*************************** 1. row ***************************

           Name: xin

         Engine: MyISAM

        Version: 10

     Row_format: Fixed

           Rows: 4

 Avg_row_length: 121

    Data_length: 484

Max_data_length: 34058472181989375

   Index_length: 1024

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2014-08-25 13:19:02

    Update_time: 2014-08-25 13:19:35

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)

我们发现这里面并没有数据碎片。都是很严密的。

第三步，我们删除一条数据：

mysql> delete from xin where c = 'xiaolin';

Query OK, 1 row affected (0.00 sec)

第四步。我们再次查看，发现数据碎片产生了:

mysql> show table status from xinxing \G

*************************** 1. row ***************************

           Name: xin

         Engine: MyISAM

        Version: 10

     Row_format: Fixed

           Rows: 3

 Avg_row_length: 121

    Data_length: 484

Max_data_length: 34058472181989375

   Index_length: 1024

      Data_free: 121

 Auto_increment: NULL

    Create_time: 2014-08-25 13:19:02

    Update_time: 2014-08-25 13:20:15

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)

我们发现上面的data_free字段为121，也就是产生了121字节的空白空间。

大家注意，我们这里仅仅是删除了一条数据。假设删除n条数据。那这些碎片就非常影响性能了，至于它的解决的方法，我们上面也提到了，这里就不再提了。

我是辛星。期待您的关注。

相关文章