Solution to saving mysql delete data space usage

Source: Internet
Author: User
Tags mysql delete

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.