堕落不振功业废,勤耕不辍日月新

mysql单表删除千万条记录

MySQL hailen 52℃

当mysql表有大量数据要删除的时候,sql的执行时间会非常长,甚至会执行超时。

比较好的办法有两种,

  • 用存储过程,一点点删除
  • 用中间表,通过导出表到新表,然后truncate原表,再重命名新表、原表的名字。

存储过程伪过程

drop procedure if exists huge_delete;
delimiter //
create procedure huge_delete()
begin
 declare rows int;
 declare rows_deleted int;
 set rows = 1;
 set rows_deleted = 10000;
 while rows > 0
 do
    delete from db.tabA where predicate_col < curdate() - interval 90 day order by `id` limit 10000;
    set rows = row_count();
    set rows_deleted = rows_deleted + row_count();
    select rows_deleted as "Rows Deleted";
 end while;
end //
delimiter ;

中间表的方式,条件需要自己设置。

CREATE TABLE `tabA_copy` as select * from `tabA` where predicate_col > curdate() - interval 30 day;

tabA_copy 保留需要的数据

Rename table tabA to tabA_archive;
Rename table tabA_copy to tabA;

对于设置了GTID的mysql, 通过第二种方式执行建表语句时会报1786错误。

行建表语句时CREATE TABLE  aaa  AS select * FROM bbb;  报:  

错误代码: 1786
Statement violates GTID consistency: CREATE TABLE … SELECT.

解决:

  在my.cnf 中将

gtid_mode = ON
enforce_gtid_consistency = ON

改为

gtid_mode = OFF
enforce_gtid_consistency = OFF

这个参数不支持动态修改,需要修改配置文件后重启mysql。

转载请注明:我是IT » mysql单表删除千万条记录

喜欢 (0)or分享 (0)