分页删除和全表删除

分页删除

如果你要删除一个表里面的前10000行数据，有以下三种方法可以做到：

第一种，直接执行delete from T limit 10000;
第二种，在一个连接中循环执行20次 delete from T limit 500;
第三种，在20个连接中同时执行delete from T limit 500。

你会选择哪一种方法呢？为什么呢？

选择第二种方式，即：在一个连接中循环执行20次 delete from T limit 500。

第一种方式:单个语句占用时间长，锁的时间也比较长；而且长事务还会导致主从延迟。

第三种方式:会人为造成锁冲突,因为大家同时都要对前500行删除。如果可以加上特定条件，将这10000行天然分开，可以考虑第三种。实际上在操作的时候也建议尽量拿到ID再删除。

全表删除

全表删除建议直接使用truncate table tb_name。

delete删除数据索引不会删除

索引和表一样,都是数据库对象。 在删除大数据时，一定要先把索引删掉，可以大大提高效率.

delete属于DML操作，个人理解是逐条删除记录，同时会更新索引,注意不是删除索引，全表删除操作后索引本身还是存在的。delete不一定删除全表，因此从设计角度将不可能只考虑全表删除的情况，因此更新索引是合理的操作。

truncate属于DDL操作，应该是直接更新了数据库的元数据，不需要每条删除，所以速度较快。执行truncate table，除了rows会删除，index也会删除。

delete，表占用空间不变,表上索引占用空间也不会变。

truncate table tb_name reuse storage，表占用不变，表上索引占用也不会变。

truncate table tb_name，表与表上索引的空间占用均回到建立索引或表时的initial参数。

定期rebuild索引是一个好习惯，一是提高索引效率二是释放存贮区。

对于delete from table_name：

对于MyISAM 会立刻释放磁盘空间（应该是做了特别处理，也比较合理） InnoDB 不会释放磁盘空间

对于delete from table_name where xxx带条件的删除。不管是innodb还是MyISAM都不会释放磁盘空间

delete操作以后使用optimize table table_name 会立刻释放磁盘空间,在OPTIMIZETABLE运行过程中，MySQL会锁定表。因此，这个操作一定要在网站访问量较少的时间段进行。。

不管是innodb还是myisam，所以要想达到清理数据的目的，请delete以后执行optimize table 操作。

delete from表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

原文链接：blog.csdn.net/eve_z/artic…

数据页复用&&记录复用

记录的复用：只限于符合范围条件的数据。比如上面的这个例子，R4这条记录被删除后，如果插入一个ID是400的行，可以直接复用这个空间。但如果插入的是一个ID是800的行，就不能复用这个位置了。

数据页复用:当整个页从B+树里面摘掉以后，可以复用到任何位置。以图1为例，如果将数据页page A上的所有记录删除以后，page A会被标记为可复用。这时候如果要插入一条ID=50的记录需要使用新页的时候，page A是可以被复用的。

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

进一步地，如果我们用delete命令把整个表的数据删除呢？

结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

你现在知道了，delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

实际上，不止是删除数据会造成空洞，插入数据也会。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

假设图中page A已经满了，这时我要再插入一行数据，会怎样呢？

可以看到，由于page A满了，再插入一个ID是550的数据时，就不得不再申请一个新的页面page B来保存数据了。页分裂完成后，page A的末尾就留下了空洞（注意：实际上，可能不止1个记录的位置是空洞）。

另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。

也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

重建表 alter analyze optimize 区别

使用optimize table、analyze table和alter table这三种方式重建表的区别。

从MySQL 5.6版本开始

alter table t engine = InnoDB（也就是recreate）。

analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁。

optimize table t 等于recreate+analyze。

61.mysql优化之数据删除优化