Detailed explanation of delete, truncate and drop principles in MySQL database

How to delete data ：

Three common deletion methods ： adopt delete、truncate、drop Keywords to delete , All three can be used to delete data , But the scene is different .

Execution speed ：

drop > truncate > delete

DELETE principle ：

1. DELETE Belongs to the database DML Operating language , Delete only the data, not the structure of the table , I can walk away from business , It will trigger trigger;
2. stay InnoDB in ,DELETE It doesn't really delete the data ,MySQL In fact, it just marks the deleted data as deleted , therefore delete When deleting data from a table , Table files will not take up less space on disk , Storage will not be released , Just make the deleted data rows invisible . Although no disk space has been released , But the next time you insert data , You can still reuse this space （ reusing → Cover ）.
3. DELETE Execution time , It will cache the deleted data to rollback segement in , Business commit Effective after ;
4. delete from table_name Delete all data from the table , about MyISAM Will immediately free up disk space ,InnoDB It won't free up disk space ;
5. about delete from table_name where xxx Conditional deletion , Whether it's InnoDB still MyISAM Will not free up disk space ;
6. delete Use after operation optimize table table_name Will immediately free up disk space . Whether it's InnoDB still MyISAM . So in order to free up disk space ,delete Later optimize table operation .

Syntax for viewing the amount of hard disk space occupied by the table （MB） as follows ：

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size 
from information_schema.tables 
where table_schema='test' AND table_name='aaa';

  Then execute the space optimization statement , And the table size change after execution ：

  And then look at the size of this table , Only the size of the table structure is left .

     delete The operation is to delete line by line , At the same time, the The deletion log is recorded in redo and undo Table space for rollback （rollback） And redo operations , The large amount of logs generated will also take up disk space .

truncate principle ：

Truncate table TABLE_NAME

1. truncate Belongs to the database DDL Define language , No business , The original data will not be put into rollback segment in , Operation does not trigger trigger, Take effect immediately , Unable to retrieve .
2. truncate table table_name Free up disk space immediately , Whether it's InnoDB and MyISAM .truncate table It's kind of like drop table then create, It's just this create table The process has been optimized , For example, the table structure file has already existed before, and so on . So the speed should be close to drop table The speed of .
3. truncate Can quickly clear a table . And reset auto_increment Value . But for different types of storage engines, what you need to pay attention to is ： about MyISAM,truncate Reset auto_increment（ Autogenous sequence ） The value of is 1. and delete The back table remains auto_increment. about InnoDB,truncate Reset auto_increment The value of is 1.delete The back table remains auto_increment. But doing delete Restart after the entire table MySQL Words , After the restart auto_increment Will be set to 1. in other words ,InnoDB The table itself cannot be persisted auto_increment.delete After the table auto_increment Still in memory , But it was lost after the restart , Only from 1 Start . Essentially after the restart auto_increment From SELECT 1+MAX(ai_col) FROM t Start .

drop principle ：

Drop table Tablename

1. drop： Belongs to the database DDL Define language , Same as Truncate; Take effect immediately , Unable to retrieve
2. drop table table_name Free up disk space immediately , Whether it's InnoDB and MyISAM;
3. drop Statement will delete the constraint that the structure of the table is dependent on (constrain)、 trigger (trigger)、 Indexes (index);  Stored procedures that depend on the table / The function will keep , But it became invalid state .