mysql删除超大表中的部分数据

日期: 2019-12-13 04:09 浏览次数 :

mysql删除大表的部分数据

mysql中删除一般表数据我们会使用delete 或者truncate来清空表数据,但是如果碰到超大表时你会发现此方法有点困难了,下面我以一个mysql删除超大表中的部分数据为示例给各位同学介绍介绍。

 

mysql普通删除表

好久没写博客。最近项目要上线。下班时间还得陪着老妈。实在没时间更新。

delete 语句的定义:

 

经常和数据库打交道的孩子们,删除数据的时候用的大多都是 delete 语句。现在让我们来看一下 delete语句的定义。

今天有人提了一个问题,  www.2cto.com  

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

 

[WHERE where_definition]

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

[ORDER BY ...]

当时看了一眼数据吓尿了,这么大的数据要删除到什么时候啊,最要命的锁表肿么办

[LIMIT row_count]

 

delete是不行了,加索引也别想。mysql上delete加low_priorty,quick,ignore估计也帮助不大

delete from friends where user_name = ‘simaopig’;

 

truncate 语句 TRUNCATE [TABLE] tbl_name

看到mysql文档有一种解决方案:

这里简单的给出个示例,我想删除 friends 表中所有的记录,bKjia.c0m可以使用如下语句:

 

truncate table friends;

 

但在我在删除超大表时发现 delete是不行了,加索引也别想。mysql上delete加low_priorty,quick,ignore估计也帮助不大

If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:

看到mysql文档有一种解决方案:

 

If you are deleting many rows from a large table, you may www.bKjia.c0m exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use Delete at all) might be helpful:

Select the rows not to be deleted into an empty table that has the same structure as the original table:

Select the rows not to be deleted into an empty table that has the same structure as the original table:

 

Insert INTO t_copy Select * FROM t Where ... ;
Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:

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

RENAME TABLE t TO t_old, t_copy TO t;
Drop the original table: