首页 > mysql > mysql单表删除千万条记录

mysql单表删除千万条记录

2019年11月21日 发表评论 阅读评论

当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。

扫码联系我

加我微信

除非注明,本站文章均为: nginx.cn原创,转载请注明本文地址: http://www.nginx.cn/5286.html

--完--

分类: mysql 标签:
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.