Xin Xing talks about mysql's data fragmentation and operations under MyISAM engine,

Xin Xing talks about mysql's data fragmentation and operations under MyISAM engine,

Data fragmentation in mysql is closely related to data deletion. When deleting data, it will inevitably lead to discontinuous blank space in the data file. for deletion of a small amount of data, it does not produce much space. If a large number of delete operations are performed within a period of time, this blank space will be larger than the space used to store the list content. Some may say that when we insert data into the database, will we insert data into these blank spaces? The answer is yes. However, it will cause a consequence: the data storage location is not consecutive, and the physical storage order is not the same as the theoretical storage order, which is more troublesome.

Data fragmentation can be divided into single-row data fragmentation and multi-row data fragmentation. In fact, it will not only produce data fragmentation, but also produce index fragmentation if an index is added, which will cause disorder of order. The internal implementation mechanisms of the MySQL engine are different in terms of data fragmentation processing.

For MyISAM, because its index, data, and table structure are stored in three files, optimize can sort and sort data files, in this way, the performance problems caused by data fragmentation will be reduced a lot. You can directly use [optimize table name]. However, you should also pay attention to some problems because this operation will lock the table, so we try to sort out the fragments on a regular basis to do this when the traffic is low. You can view the free_data field in the tables of the information_schema database. If this field is not 0, data fragments are generated. See the following operations:

Type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql> use information_schema; Database changedmysql> select data_free from tables; + ----------- + | data_free | + ----------- + | 0 | 0 |... omitted data | 0 | 72 | 0 | 0 | + ----------- + 162 rows in set (0.06 sec) mysql>

Here we view all the data fragments in the table. If we want to view the data fragments of a table separately, see the following operations:

Step 1: Create a database and a table, and insert four data records into the table:

mysql> create database xinxing;Query OK, 1 row affected (0.16 sec)mysql> use xinxing;Database changedmysql>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

Step 2: view the table information:

 

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: 484Max_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)

We found that there was no data fragmentation, and they were very strict.

Step 3: delete a piece of data:

 

mysql> delete from xin where c = 'xiaolin';Query OK, 1 row affected (0.00 sec)

 

Step 4: Check again and find that data fragments are generated:

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: 484Max_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)

 

We found that the preceding data_free field is 121, that is, a 121-byte blank space is generated.

Note that we only deleted one piece of data. If n pieces of data are deleted, these fragments will affect performance. As for the solution, we mentioned above, I will not mention it here. I'm Xin Xing and look forward to your attention.