Does mysql delete free up index space?

The index space is freed, but not the tablespace. After a large number of deletes, we recommend that you use optimize table to organize the tablespace, which can not only free up the tablespace, but also report the index space utilization.

1. Drop table table name immediately free up disk space, whether it is innodb or myisam

2. Truncate table table name immediately free up disk space, whether it is innodb or myisam. A truncate table is actually a bit like a drop table and then create

It's just that the process of creating table has been optimized, for example, the table structure file has been existing before, etc., so you don't need to do it again. So the speed should be close to the speed of the drop table.

The difference between physical deletion and tomb-deletion in SQL databases is as follows: physical deletion of records means that the data records in the database will be directly cleared (or deleted on disk), the physical space will be released, and the deleted data records can no longer be searched from the database; Tombstoning records does not directly delete the data in the database, but only shields the display of the tombstoned data in the foreground through some means, does not free up physical space, and can also query the data from the database. Extensions:

Tombstone is used to keep important data and make it easier to maintain data and other related business problems, and its related tombstone means can be to add table fields as filter conditions, migrate to another table, etc.!

More than 100 entries can be deleted in one second, and if there is an index, it will be slow.

Method 1: truncate table Your table name [This not only deletes all the data, but also relocates the self-incrementing fields].

The truncate command will restore the auto-increment field to start from 1, or you can try to clear table a, then cancel the auto-increment, save, and add back to the auto-increment, which is also the way to restore the auto-increment segment to 1.

Method 2: deletefrom your table name.

dbcc checkident(your table name, reseed, 0) [Relocate the self-incrementing field so that it starts at 1].

More than 100 entries can be deleted in one second, and if there is an index, it will be slow.

Whether or not an index will be used for MySQL deletion and modification operations depends on many conditions1. The WHERE condition of the delete or modify SQL statement contains fields that have been indexed2. The MySQL optimizer will use the index to optimize SQL according to the query conditions3.

4. In the printed EXPLAIN screen information, you can see whether the index is really used when SQL is executed.

Example: explain update table set name='zhangsan' where age=18 and id=2;

If it is a non-innodb table, it can be dropped after it, if it is an innodb and the exclusive tablespace is not enabled, then even if the drop is not enabled, then the exclusive tablespace can be enabled. This turn on is invalid if there is data.