MySQL database, delete record does not free storage space

In the MySQL database, when we delete a database record using a statement such as delete from table where the record is deleted, the record is not found with SELECT, but the storage footprint of the table is not reduced because the use of such a statement is performed by a tombstone, The database simply deletes the tag and does not actually erase the data from the physical disk.

Therefore, in order to completely delete, you need to use TRUNCATE TABLE or delete without the where condition, so that all the data can be physically deleted. But the actual often can not execute such a statement, the table all the data is emptied, then how to achieve only part of the data physical deletion?

1. Execute the delete from table where the statement

2. Tablespace defragmentation, OPTIMIZE table, when using this statement, the tables are locked, so always note that it is recommended to execute the statement once a week or every month before using it.

MySQL database, delete record does not free storage space