辛星浅谈mysql中的数据碎片以及引擎为MyISAM下的操作_MySQL
程序员文章站
2022-06-01 18:34:35
...
对于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条数据,那这些碎片就很影响性能了,至于它的解决办法,我们上面也提到了,这里就不再提了。我是辛星,期待您的关注。