Delete Delete Record database space size does not reduce the problem

I remember when I was in college, when I was learning computer, the teacher told me that the delete record only adds a delete ID to the record in the database, so that the database space is not reduced, then did not think so much, yesterday found a database using delete deleted capacity unchanged, Baidu later found the following a webmaster to share the file, write very good, tidy up for you to reference.
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.

TRUNCATE

Its syntax structure is:

TRUNCATE [TABLE] Tbl_name

Here's a simple example,

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

TRUNCATE TABLE friends;

The effect of delete is a bit like removing all records from a MySQL table to a delete, and truncate is equivalent to preserving the structure of the MySQL table, recreating the table, and all the States are equal to the new table, so that the space is reduced.

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

Delete Delete Record database space size does not reduce the problem