二、批量删除大量数据策略

二、批量删除大量策略

批量删除可能存在的问题：
    删除大量数据后，表的统计信息错误，并产生磁盘碎片
    MySQL 5.5的复制是串行进行的，如果发生大事务，会导致后面的事务被阻塞
        可能发生复制延迟的情况：
        1）大表DDL操作
        2）大事务（长时间没有提交）

解决方案：
    将大事务拆分成多个小事务，并且各个小事务不要太紧密，防止跑满系统资源。
        具体：删除大量数据的拆分方式，按照id分组，分多次执行，每次只删除少量数据

其他方式：
    分别在主从库上执行delete操作：通过sql_log_bin=0禁止产生binlog来阻止事务复制到从库，避免涉及复制延迟

操作步骤：
1、需求：清除2016-05-01之前的数据，确认该范围的最大id，通过id确定要删除的数量
    >SELECT MAX(id) FROM fmb_admin_log_bak2015 WHERE log_time <'2016-05-01 00:00:00' ;
    +---------+
    | MAX(id) |
    +---------+
    | 6007013 |
    +---------+
    >SELECT COUNT(id) FROM fmb_admin_log_bak2015 WHERE id<6007013;
    +-----------+
    | COUNT(id) |
    +-----------+
    |   1653789 |
    +-----------+
    确认需要清除的数据160w
    
2、常规删除一条delete语句就能实现，通过存储过程的方式改写成多个delete语句（策略：每次删除1w条，执行160次，每次间隔10s）
    原始语句：
        DELETE FROM fmb_admin_log WHERE id < 6007013;
    存储过程：
        DELIMITER $$  
        DROP PROCEDURE IF EXISTS proc_del_tb
        $$  
        CREATE PROCEDURE proc_del_tb()  
        BEGIN  
          DECLARE count INT;
          DECLARE i INT;  
          set count = 1;
          set i = 10000;
          while count <= 165 do
            delete from fmb_admin_log order by id asc limit i;
            select sleep(10);
            set count = count + 1;
            select count;
          end while;
        end $$
        delimiter ;
        
3、编辑脚本和定时任务
    注意：调用存储过程和alter整理表比较耗时    ；alter table 会锁表，需要在空闲时执行
    [root@DB ~]# cat /data/software/script/call_proc.sh
    #!/bin/bash
    SDT=`date +"%y-%m-%d %H:%M:%S"`
      echo "START :$SDT"
    /usr/local/mysql/bin/mysql -uroot -e "call proc_del_tb();" fmb_new                                        #调用存储过程
      echo "The Result is :$?"
    EDT=`date +"%y-%m-%d %H:%M:%S"`
      echo "END :$EDT"
    /usr/local/mysql/bin/mysql -uroot -e "alter table fmb_admin_log ENGINE=InnoDB;" fmb_new            #由于删除数据量较大，需要alter table重建表，整理碎片空间
      echo "The Alter Result is:$?"                                        #输出Alter语句是否执行成功
    EDT01=`date +"%y-%m-%d %H:%M:%S"`
      echo "END :$EDT01"
      
    [root@DB ~]# crontab -l                                                    #设置04-28 05:05:00 开始执行，并输出日志
    05 05 28 4 * /data/software/script/call_proc.sh >> /data/software/script/call_proc.log 2>&1