大表百万条以上记录非主键索引删除大量数据的操作方法

众所周知，上百万表数据类似上述写法，哪怕where条件命中索引，执行时间也不会低于10分钟；考虑对数据的操作会引起两表的排他锁，造成业务被暂停！从服务器资源的影响：IOPS升高，IO争用造成CPU等待（高CPU）；删除表存在的大量空间碎片，造成的空间浪费及性能低下

这种需求可以采用中间表rename方法，将要保留的数据写入中间表，写入完成后将原表删除，并将中间表改名为原始表；

具体操作步骤如下：

1.SHOW  CREATE  TABLE  ooo_scrm_customer;    2.创建同ooo_scrm_customer表结构一样的新表：_ooo_scrm_customer_new  REATE  TABLE  _ooo_scrm_customer_new ( `id`  int (11)  NOT  NULL  AUTO_INCREMENT,      .....      .....      ..... PRIMARY  KEY  (`id`) ) ENGINE=InnoDB  DEFAULT  CHARSET=utf8;   3.创建增删改触发器（主要用于主表向中间表写数据时，业务方请求修改的数据要一起同步到中间表） CREATE  TRIGGER  `ooo_scrm_customer_del`  AFTER  DELETE  ON  ooo_scrm_customer  FOR  EACH ROW  DELETE  IGNORE  FROM  _ooo_scrm_customer_new  WHERE  _ooo_scrm_customer_new.`id` <=> OLD.`id` CREATE  TRIGGER  `ooo_scrm_customer_upd`  AFTER  UPDATE  ON  ooo_scrm_customer  FOR  EACH ROW  REPLACE  INTO  _ooo_scrm_customer_new(`id`, `其他列`)  VALUES  (NEW.`id`, NEW.`其他列`) CREATE  TRIGGER  `ooo_scrm_customer_ins`  AFTER  INSERT  ON  ooo_scrm_customer  FOR  EACH ROW  REPLACE  INTO  _ooo_scrm_customer_new(`id`, `其他列`)  VALUES  (NEW.`id`, NEW.`其他列`)   4.将要保留的数据写入中间表 INSERT  LOW_PRIORITY  IGNORE  INTO  _ooo_scrm_customer_new (`id`, `其他列`)  SELECT  `id`, `其他列`  FROM  ooo_scrm_customer LOCK  IN  SHARE MODE # where  条件优化，按分批执行思想，稳步执行！   5.优化中间表 ANALYZE TABLE_ooo_scrm_customer_new    6.改名并删除操作记录 RENAME  TABLE  ooo_scrm_customer  TO  _ooo_scrm_customer_old, _ooo_scrm_customer_new  TO  ooo_scrm_customer DROP  TRIGGER  IF EXISTS `mysqlcheck`.`ooo_scrm_customer_del` DROP  TRIGGER  IF EXISTS `mysqlcheck`.`ooo_scrm_customer_upd` DROP  TRIGGER  IF EXISTS `mysqlcheck`.`ooo_scrm_customer_ins`

 

其他应用案例

       该思想起源facebook的mysql path，也是大名鼎鼎pt-online-schema-change工具的实现原理。

       可应用于在线改表结构，在线修改表数据，原则上来讲可以实现秒级别的表锁定，对业务的0伤害

 

       同学们在程序实现时还可灵活的使用其他方法：

               SET SESSION lock_wait_timeout=60；SET SESSION innodb_lock_wait_timeout=1；SET SESSION wait_timeout=10000

            （innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；）

 

 

如有类似需求，请将该思想运用到你们的项目中吧。

 

OVER!!

转载于:https://www.cnblogs.com/sungx/p/5332367.html