Mysql deletes unused table data

There are several situations where MySQL deletes data and whether disk space is freed:

1. Drop table table_name immediately frees up disk space, whether it's Innodb or MyISAM.

2. Truncate table table_name immediately frees up disk space, whether it's Innodb or MyISAM. Truncate table is actually a bit like drop table and then creat, except that the create table process is optimized, such as table structure files, and so on. So the speed should be close to the speed of drop table.

3. Delete from table_name deletes all data from the table. For MyISAM, disk space will be released immediately (it should be treated specially and reasonably), while InnoDB will not free disk space.

4. For conditional deletion of delete from table_name where xxx, neither innodb nor MyISAM will free disk space.

5. Using optimize table table_name after delete operation will release disk space immediately. Whether it's innodb or myisam. So to achieve the goal of freeing up disk space, delete performs optimize table operations later.

6. Although the delete from table does not release disk space in the future, it can still be used the next time the data is inserted.