This is because fragments will remain in the data file after the delete operation. DELETE only deletes data identification bits, but does not organize data files. When new data is inserted, these recording spaces set as deletion marks will be used again. In addition, in the actual operation process, it is also found that there are two cases of this problem.

(1) This problem occurs when DELETE is followed by a condition. For example:

Deletefromtable_namewhere condition

After deleting the data, the space occupied by the data table will not change.

(2) Delete directly when the conditions are not met. For example:

deletefromtable_name

When the data is cleared, the space of the data table will also become 0.

This is a problem, in the actual operation of the website. This kind of operation behavior of conditionally deleting data often occurs. Over time, it will waste a lot of space in the database. At this point, we should use the OPTIMIZETABLE instruction to optimize the table.

How to use optimization and when to use optimization instructions?

Command syntax: optimize [local | no _ write _ to _ binlog] tabletbl _ name [,TBL _ name]. ...

The simplest: optimizetablephpernote _ article;

If a large part of the table has been deleted, or many changes have been made to tables with variable-length rows (tables with VARCHAR, BLOB, or TEXT columns), you should use the.

The table can be optimized. The deleted records are saved in the linked list, and the old record locations will be reused in subsequent insertion operations. You can use optimization.

Table to reuse unused space and organize data files.

In most settings, you don't need to run OPTIMIZETABLE at all. Even if you make a lot of updates to the variable-length travel, you don't need to run it frequently, once a week or once a month, only for a specific table.

OPTIMIZETABLE only applies to MyISAM, BDB and InnoDB tables.

Note that MySQL locks the OPTIMIZETABLE when it runs. Therefore, this operation must be carried out during the period of less website visits.

shorten

Its grammatical 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:

truncatetablefriends

The effect of delete is a bit like deleting all the records in mysql table one by one. truncate is equivalent to retaining the structure of mysql table and recreating the table. All the states are equivalent to the new table, so the space is reduced.

Well, of course, it is impossible to empty our website with truncatetable, because all the data will be lost after that, so it is definitely unreasonable to empty it. We must use delete to delete it and then repair the optimization table.