MySQL Clear Table Space fragmentation

Causes of fragmentation

(1) The table storage will be fragmented, whenever a row of content is deleted, the space will be blank, be left blank, and over a period of time a large number of delete operations,

This will make the space that is left empty larger than the space used to store the list content;

(2) When performing an insert operation, MySQL tries to use white space, but if an empty space has not been occupied by the appropriate size data, it is still unable to occupy it completely, resulting in fragmentation;

(3) When MySQL scans the data, the object it scans is actually the upper limit of the capacity requirement of the list, which is the part of the peak in the area where the data is written;

For example:
A table with 10,000 rows, 10 bytes per row, consumes 100,000 bytes of storage space, performs a delete operation, leaves only one row, and the actual content is only 10 bytes, but when MySQL reads,

is still considered to be a 100,000-byte table for processing, so the more fragmentation, the more it will affect query performance.

View Table Fragmentation Size

(1) View the fragmentation size of a table

mysql> SHOW table STATUS like  ' table name ';    

• 1

The value of the ' data_free ' column in the result is the fragment size

(2) List all tables that have been fragmented

Mysql>select Table_schema DB, TABLE_NAME, Data_free, engine from Information_ Schema.tables where Table_schema not in ( ' Information_schema ',  ' MySQL ') and data_free > 0;    

• 1
• 2
• 3

Clear Table Fragmentation

(1) MyISAM table

table 表名

• 1

(2) InnoDB table

mysql> alter table 表名 engine=InnoDB

• 1

Engine different, OPTIMIZE operation is not the same, MyISAM because the index and data is separate, so OPTIMIZE can organize the data file, and rearrange the index.

The OPTIMIZE operation temporarily locks the table, and the larger the amount of data, the longer it takes, after all, it is not a simple query operation. So it's not proper to put the OPTIMIZE command in the program,

No matter how low the set hit rate, when the traffic increases, the overall hit ratio will rise, which will certainly have a significant impact on the efficiency of the program. The better way is to be a shell,

Check MySQL regularly information_schema . TABLES field, viewing the Data_free field, which is greater than 0 words, indicates fragmentation.

MySQL Clear Table Space fragmentation