MySQL Delete delete record database space does not reduce problem solving methods

Today, the space business told me that the database space is full, checked, and found that the Web site user behavior Record data table accounted for more than 20 MB. Accumulated for six months, the deletion of the release of space. Decisive delete found that the database space unexpectedly is not small, although the number of data records is zero.

This is due to the fragmentation of the data file after the delete operation. Delete simply deletes the data identifier and does not defragment the data file, and once the new data is inserted, the record space that is placed as the deletion identity is reused. In addition, it is also found that there are two kinds of cases in the actual operation process.

(1) This problem occurs when the delete is followed by the condition. Such as:

Delete from table_name WHERE condition

When you delete data, the amount of space that the datasheet occupies does not change.

(2) When the condition is not directly delete. Such as:

Delete from table_name

The data is cleared, and the space of the datasheet becomes 0.

This has a problem in the actual operation of the site. There is often such an act of deleting data with such conditions. Over time, this does not waste a lot of space in the database. This time we should use the OPTIMIZE table instruction to optimize the table.

How do you use OPTIMIZE and when should you use OPTIMIZE instructions?

Command syntax: OPTIMIZE [local | No_write_to_binlog] TABLE tbl_name [, Tbl_name] ...

Simplest: Optimize table phpernote_article;

If you have deleted a large portion of the table, or if you have made many changes to a table containing a variable-length row (a table that contains varchar, blobs, or text columns), you should use OPTIMIZE table. The deleted record is kept in the linked list, and subsequent inserts reuse the old record location. You can use the Optimize table to reuse unused space and defragment the data file.

In most settings, you do not need to run optimize TABLE at all. Even if you have a large number of updates to a variable-length row, you don't need to run it frequently, once a week or once a month, and only run on a specific table.

OPTIMIZE table works only for MyISAM, BDB, and InnoDB tables.

Note that MySQL locks the table while the optimize table is running. Therefore, this operation must be in the site less traffic time period.

TRUNCATE

Its grammatical structure is:

TRUNCATE [TABLE] Tbl_name

Here's a simple example,

I want to delete all the records in the Friends table, you can use the following statement:

TRUNCATE TABLE friends;

The effect of the delete is a bit like deleting all the records in the MySQL table to the end, and truncate is equivalent to keeping the MySQL table structure, recreating the table, and all the States are equivalent to the new table, so the space is reduced.

Well, of course for our site can not use TRUNCATE table to clear, because then all the data are lost, so it is certainly unreasonable to clear, we have to use Delete to delete, and then to fix the optimization table OH.