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