Solution to saving mysql delete data space usage

Solution to saving mysql delete data space usage

Today, the Space Provider told me that the database space is full. After checking, I found that the user behavior record data table on the website actually occupied more than 20 MB. After six months of accumulation, it is time to delete and release the space. After the decisive delete operation, we found that the database space was not small, although the number of data records was zero.

It turns out that this is because fragments are left in the data file after the delete operation. DELETE only deletes data identifiers and does not organize data files. When new data is inserted, the deleted identifiers are used again. In addition, this problem still exists in two cases during actual operation.

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

Delete from table_name where Condition

After the data is deleted, the space occupied by the data table does not change.

(2) When the condition is not directly deleted. For example:

Delete from table_name

The data is cleared, and the data table space is changed to 0.

This poses a problem during the actual operation of the website. Such operations include conditional deletion. Does it waste a lot of space in the database for a long time. At this time, we should use the optimize table command to OPTIMIZE the TABLE.

How to Use OPTIMIZE and when to use the OPTIMIZE command?
 
Command syntax: OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]...

Simplest: optimize table phpernote_article;
 
If you have deleted a majority of the tables, or if you have made many changes to the tables that contain variable-length rows (tables that contain VARCHAR, BLOB, or TEXT columns, optimize table should be used. The deleted records are kept in the Link List. Subsequent INSERT operations will reuse the old record location. You can use optimize table to reuse unused space and organize data file fragments.
 
In most settings, you do not need to run optimize table. Even if you have made 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, and only run on a specific table.
 
Optimize table only applies to MyISAM, BDB, and InnoDB tables.
 
Note: During the optimize table operation, MySQL locks the TABLE. Therefore, this operation must be performed in a time period with a low Website access volume.

Articles you may be interested in

• Learn to set five common MySQL database Parameters
• MySQL replace function replacement string statement usage
• Steps for synchronizing and backing up mysql Databases in windows
• Improves the query speed of millions of data records for MySQL
• Mysql database cache function analysis, debugging, and performance summary
• Rational use of MySQL database indexes to make the database run efficiently
• Methods for resetting the auto-increment start value of various MySQL Data Table types
• Case sensitivity of mysql Databases