MySQL Data sheet defragmentation

In MySQL, we often use variable-length text data types such as varchar, text, and blobs. However, when we use these data types, we have to do some extra work--mysql data table defragmentation.

So why do we need to defragment MySQL regularly after using these data types?

--Create demo table  id int unsigned, body text )  engine=myisam charset=utf8;  --Insert 5 test data  insert into demo values (1,  ' AAAAA ');  insert into demo  values (2,  ' bbbbb ');  insert into demo values (3,  ' CCCCC '); INSERT  Into demo values (4,  ' ddddd ');  insert into demo values (5,  ' EEEEE '); 

We then use the following INSERT INTO statement, which is based on the 5 test data, to repeatedly perform the replication inserts.

INSERT into demo SELECT ID, body from demo;

INSERT INTO statement multiple times to generate a total of about 2.62 million data

As we all know, the data of the MyISAM table in MySQL is stored as a file, and we can find the Demo.myd file in the database test directory in the folder where MySQL stores the data. At this point, we can see the demo. The size of the MyD file is approximately 50MB.

Demo. MyD file is about 50MB

At this point, if we need to delete all data in the demo table that are less than 3 (that is, 1 and 2), we execute the following SQL statement:

DELETE from DEMO WHERE ID < 3

At this point, we can see that the amount of data in the demo table is only the original 3/5:

After deletion, only 1.57 million records are left

The current amount of data in the demo table is only 3/5 of the original, supposedly, this time the demo. The size of the MyD file should also be about 3/5 of the original. However, we look at the demo again. MyD file, I was surprised to find that the size of the file did not change at all!

After deleting the data, demo. MyD file size does not change

So what is it all about? It turns out that in MySQL, if we delete a large amount of data from a table, or we make a lot of changes to a table with variable-length text data types (Varchar,text or BLOBs), the deleted data records remain in the MySQL link list. Therefore, the size of the data store file does not decrease as the data is deleted.

When we determine that the data needs to be erased, the data becomes useless data, but in terms of MySQL processing, the data will still occupy our disk space, resulting in a huge waste of resources. Not only that, too large data files can also cause MySQL to perform related data operations with more performance and time. Therefore, it is necessary to defragment some MySQL data tables.

The method of defragmenting MySQL is very simple, because MySQL has provided us with the corresponding SQL instruction, the SQL command is optimize TABLE, the complete syntax is as follows:

OPTIMIZE [LOCAL | No_write_to_binlog] TABLE table_name1 [, table_name2] ...

From the syntax described above, we can tell that OPTIMIZE table can defragment multiple tables at once, simply by appending multiple table names to the OPTIMIZE table and separating them with commas.

Additionally, the OPTIMIZE table statement has two optional keywords: local and no_write_to_binlog. By default, the OPTIMIZE table statement will be recorded in the binary log and will not be logged if we specify the local or No_write_to_binlog keyword. Of course, we don't have to focus on these two keywords in general.

Now we'll use the Optimize table statement to defragment the demo table just now.

Defragmenting the demo table

Then we'll check the demo again. MyD file, we will find the demo at this time. The size of the MyD file has been reduced to about 3/5 of the original.

Defragment the demo after the defragmentation. Size of the MyD file

Note: 1.MySQL It is recommended that you do not defragment regularly (hourly or daily), usually on a weekly or monthly basis, depending on the actual situation. The 2.OPTIMIZE table works only on MYISAM,BDB and InnoDB tables, especially in MyISAM tables. In addition, not all tables need to be defragmented, generally only the tables containing the variable-length text data types described above can be collated. 3. mysql locks the table during optimize table operation. 4. By default, the Optimize table is used directly on the InnoDB engine's datasheet, and the "table does not optimize, doing recreate + analyze instead" message may be displayed. At this point, we can restart MySQL with the mysqld--skip-new or the mysqld--safe-mode command to enable other engines to support optimize TABLE.

This article is from the "XFICC" blog, make sure to keep this source http://xficc.blog.51cto.com/1189288/1570296

MySQL Data sheet defragmentation