Mysql在大表中删除大量数据的优化

假设有一个表有3000万条记录，需要在业务不停止的情况下删除其中status=1的所有记录，差不多有600万条。

如果直接使用delete from tab_name where status=1； 会触发lock wait timeout exceed的错误，因为这条语句涉及的记录数太多。

1、drop、truncate和delete的区别：

drop

truncate

delete

执行过程

DDL语句，删除整张表和表结构，以及表的索引、约束和触发器。

DDL语句，只删除表数据，表的结构、索引、约束等会被保留。

DML语句，删除表中数据

回滚

不可

不可

可以

事务

不走事务，不会锁表，也不会生产大量日志写入日志文件;

truncate table table_name 后立刻释放磁盘空间，并重置auto_increment的值。

事务会记录到日志，并且有行、表锁；

delete删除不释放磁盘空间，但后续insert会覆盖在之前删除的数据上。

执行效率：drop > truncate > delete

2、分批limit删除方案：

DELETE FROM tab_name WHERE status=1 ORDER BY status LIMIT 10000;

注意：当需要用到order by排序时，必须order by + limit联用，否则order by 就会被优化器优化掉，被认为无意义。

说明：如果delete的where语句不在索引上，可以先找主键，然后根据主键删除数据库。

1）加limit的的优点：

1. 降低写错SQL的代价，就算删错了，比如limit 500,那也就丢了500条数据，并不致命，通过binlog也可以很快恢复数据。
2. 避免了长事务，delete执行时MySQL会将所有涉及的行加写锁和Gap锁（间隙锁），所有DML语句执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。
3. delete数据量大时，不加limit容易把cpu打满，导致越删越慢。

针对上述第二点，前提是statusid上加了索引，大家都知道，加锁都是基于索引的，如果statusid字段没索引，就会扫描到主键索引上，那么就算statusid = 1 的只有一条记录，也会锁表。

2）单条删除、更新操作，使用limit1绝对是个好习惯：

单条更新和删除操作时，如果SQL中有limit 1；这时就return了，否则还会执行完全表扫描才return。效率不言而喻。

3、rename方案：

一个表有1亿6000万的数据，有一个自增ID。最大值就是1亿6000万，需要删除大于250万以后的数据，有什么办法可以快速删除？

看到mysql文档有一种解决方案：http://dev.mysql.com/doc/refman/5.0/en/delete.html

删除大表的多行数据时，会超出innod block table size的限制，最小化的减少锁表的时间的方案是：

1）选择不需要删除的数据，并把它们存在一张相同结构的空表里

INSERT INTO t_copy SELECT * FROM t WHERE ... ;

2）利用rename原子操作，重命名原始表和复制表

RENAME TABLE t TO t_old, t_copy TO t;

3）删掉原始表

DROP TABLE t_old;

4、删除不必要的索引后重建

在My SQL数据库使用中，有的表存储数据量比较大，达到每天三百万条记录左右，此表中建立了三个索引，这些索引都是必须的，其他程序要使用。由于要求此表中的数据只保留当天的数据，所以每当在凌晨的某一时刻当其他程序处理完其中的数据后要删除该表中昨天以及以前的数据，使用delete删除表中的上百万条记录时，MySQL删除速度非常缓慢，每一万条记录需要大概4分钟左右，这样删除所有无用数据要达到八个小时以上，这是难以接受的。

查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的（对于DML操作，如果有索引会更新索引信息，所以会比较慢），于是删除掉其中的两个索引后测试，发现此时删除速度相当快，一百万条记录在一分钟多一些，可是这两个索引其他模块在每天一次的数据整理中还要使用，于是想到了一个折中的办法：

1. 在删除数据之前删除这两个索引，此时需要三分钟多一些；
2. 然后删除其中无用数据，此过程需要不到两分钟；
3. 删除完成后重新创建索引，因为此时数据库中的数据相对较少，约三四十万条记录(此表中的数据每小时会增加约十万条)，创建索引也非常快，约十分钟左右。这样整个删除过程只需要约15分钟。对比之前的八个小时，大大节省了时间。