Shulou(Shulou.com)05/31 Report--

This article mainly introduces how mysql solves the problem of deleting records without reducing database space. It has certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

1. Use OPTIMIZE

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

The simplest: optimize table phpernote_article;

OPTIMIZE TABLE should be used if you have deleted a large portion of the table, or if you have made many changes to a table with variable-length rows (tables with VARCHAR, BLOB, or TEXT columns). The deleted record is retained in the linked list, and subsequent INSERT operations reuse the old record location. You can use OPTIMIZE TABLE to reuse unused space and defragment data files.

Note that MySQL locks the table while OPTIMIZE TABLE is running. Therefore, this operation must be carried out during the time period when the website has less traffic.

A 37-gigabyte table takes 5 seconds, time is for reference only, and server performance.

2. Use the Alter command

This is equivalent to deleting old tables and creating new ones.

For example:

mysql> alter table tablename engine=innodb;

A 37-gigabyte table takes 7 seconds, time is for reference only, and server performance.

3. Export and import

Export data using mysqldump and then reimport data. This method will affect the normal use of the business and is not recommended.

Thank you for reading this article carefully. I hope that the article "mysql how to solve the problem of deleting records database space is not reduced" shared by Xiaobian will help everyone. At the same time, I hope everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.