MySQL Delete data space consumption is not reduced to a workaround

Today the space quotient told me that the database space is full, checked, found that the site user behavior Record data table accounted for more than 20 MB. Accumulated for half a year, the deletion to free up space. Decisive Delete after the discovery of database space is not small, although the number of data records is zero.

This is due to a fragment left in the data file after the delete operation. Delete simply deletes the data identity bits and does not defragment the data file, and once the new data is inserted, the record space that is set to delete the identity is used again. In addition, there are two kinds of problems in the process of actual operation.

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

Delete from table_name WHERE condition

When you delete data, the size of the data table takes up no change.

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

Delete from table_name

The data is cleared, and the data table's space becomes 0.

There is a problem in the actual operation of the site. There are often such ancillary conditions for the operation of data deletion. Forever, this doesn't just waste a lot of space in the database. At this point we should use the OPTIMIZE table instruction to optimize the table.

How do I use OPTIMIZE and when should I use the OPTIMIZE directive?

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

Simplest: Optimize table phpernote_article;

If you have deleted a large part of the table, or if you have made many changes to a table with variable-length rows (a table with varchar, blob, or text columns), you should use OPTIMIZE table. The deleted records are kept in the linked list, and subsequent insert operations re-use the old record location. You can use the Optimize table to reuse unused space and defragment the data files.

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 do not need to run it frequently, once a week or once a month, only for specific tables.

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

Note that MySQL locks the table during the Optimize table run. Therefore, this must be done in a period of time when the site is visited less frequently.

Http://www.bkjia.com/Mysql/856394.html

MySQL Delete data space consumption is not reduced to a workaround