Mysql批量删除大量数据

一、Mysql批量删除大量数据

方案1

假设有一个表(syslogs)有1000万条记录，需要在业务不停止的情况下删除其中statusid=1的所有记录，差不多有600万条， 直接执行 DELETE FROM syslogs WHERE statusid=1 会发现删除失败，因为lock wait timeout exceed的错误。

因为这条语句所涉及的记录数太多，因此我们通过LIMIT参数分批删除，比如每10000条进行一次删除，那么我们可以利用 MySQL这样的语句来完成:

DELETE FROM syslogs WHERE status=1 ORDER BY statusid LIMIT 10000;

如果要用order by 必须要和 limit 联用，否则被优化掉。然后分多次执行就可以把这些记录成功删除。

注意：
执行大批量删除的时候注意要使用上limit。因为如果不用limit，删除大量数据很有可能造成死锁。
如果delete的where语句不在索引上，可以先找主键，然后根据主键删除数据库。
平时update和delete的时候最好也加上limit 1 来防止误操作。

方案2

1.删除大表的部分数据
一个表有1亿6000万的数据，有一个自增ID。最大值就是1亿6000万，需要删除大于250万以后的数据，有什么办法可以快速删除？

看到mysql文档有一种解决方案：http://dev.mysql.com/doc/refman/5.0/en/delete.html

If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:

    Select the rows not to be deleted into an empty table that has the same structure as the original table:
    INSERT INTO t_copy SELECT * FROM t WHERE ... ;

    Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:
    RENAME TABLE t TO t_old, t_copy TO t;

    Drop the original table:
    DROP TABLE t_old;

删除大表的多行数据时，会超出innod block table size的限制，最小化的减少锁表的时间的方案是：
1、选择不需要删除的数据，并把它们存在一张相同结构的空表里
2、重命名原始表，并给新表命名为原始表的原始表名
3、删掉原始表

方案3

在My SQL数据库使用中，有的表存储数据量比较大，达到每天三百万条记录左右，此表中建立了三个索引，这些索引都是必须的，其他程序要使用。由于要求此表中的数据只保留当天的数据，所以每当在凌晨的某一时刻当其他程序处理完其中的数据后要删除该表中昨天以及以前的数据，使用delete删除表中的上百万条记录时，MySQL删除速度非常缓慢，每一万条记录需要大概4分钟左右，这样删除所有无用数据要达到八个小时以上，这是难以接受的。

查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的，于是删除掉其中的两个索引后测试，发现此时删除速度相当快，一百万条记录在一分钟多一些，可是这两个索引其他模块在每天一次的数据整理中还要使用，于是想到了一个折中的办法：

在删除数据之前删除这两个索引，此时需要三分钟多一些，然后删除其中无用数据，此过程需要不到两分钟，删除完成后重新创建索引，因为此时数据库中的数据相对较少，约三四十万条记录(此表中的数据每小时会增加约十万条)，创建索引也非常快，约十分钟左右。这样整个删除过程只需要约15分钟。对比之前的八个小时，大大节省了时间。

方案四

异步批量删除，每次删除固定数量数据

List<Long> searchData;//查询出的要删除的数据
MarketSearchMapper marketSearchMapper;
Integer batchNums = 1000;//每个批次数量
List<List<Long>> idsList = ListUtils.partition(searchData，batchNums);
for(List<Long> ids : idsList){
  CompletableFuture.runAsync(
  	() -> marketSearchMapper.deleteByIds(ids);//marketSearchMapper的批量删除方法
  )
}

//或者利用流进行删除
idsList.stream().forEach(ids-> {
            marketSearchMapper.deleteByIds(ids);//marketSearchMapper的批量删除方法
        });

Java 使用线程池和CountDownLatch分批插入或者更新数据

/**
 * 分页操作数据
 */
@Slf4j
@Component
public class BatchDealDemo  {

    private void batchDeal(List data, int batchNum) throws InterruptedException {
        int totalNum = data.size();
        int pageNum = totalNum % batchNum == 0 ? totalNum / batchNum : totalNum / batchNum + 1;
        ExecutorService executor = Executors.newFixedThreadPool(pageNum);
        //或者用ThreadPoolExecutor线程池
        //private static ThreadPoolExecutor executor = new ThreadPoolExecutor(10, 10, 0L, TimeUnit.MILLISECONDS, new LinkedBlockingQueue<>());
        log.info("主线程开始执行 begin --- ");

        try {
            CountDownLatch countDownLatch = new CountDownLatch(pageNum);
            List subData = null;
            int fromIndex, toIndex;
            for (int i = 0; i < pageNum; i++) {
                fromIndex = i * batchNum;
                toIndex = Math.min(totalNum, fromIndex + batchNum);
                subData = data.subList(fromIndex, toIndex);
                ImportTask task = new ImportTask(subData, countDownLatch);
                executor.execute(task);
            }
            // 当前线程必须在执行完任务之后立即调用CountDownLatch.await()方法，
            // 这样主线程的操作就会被阻塞，直到全部子线程完成各自的任务。
            //  CountDownLatch类中计数器的值等于0时，主线程就能通过await()方法恢复执行自己的任务。
            countDownLatch.await();
            log.info("数据操作完成!可以在此开始主线程的其它业务");
            log.info("主线程执行完毕 end --- ");
        } finally {
            // 关闭线程池，释放资源
            executor.shutdown();
        }
    }

    class ImportTask implements Runnable {
        private List list;
        private CountDownLatch countDownLatch;

        public ImportTask(List data, CountDownLatch countDownLatch) {
            this.list = data;
            this.countDownLatch = countDownLatch;
        }

        @Override
        public void run() {
            if (null != list) {
                // 业务逻辑，例如批量insert或者update
                log.info("现在操作的数据是{}", list);
            }
            // 发出子线程任务完成的信号
            countDownLatch.countDown();
        }
    }
}

线程池同步删除

 try {
            long start = System.currentTimeMillis();
            List<NameZ> list = createList();
            Integer batchNums = 1000;//每个批次数量
            List<List<NameZ>> lists = ListUtils.partition(searchData，batchNums);
            System.out.println("主线程开始");
            //创建线程池
            private static ThreadPoolExecutor executorService= new ThreadPoolExecutor(10, 10, 0L, TimeUnit.MILLISECONDS, new LinkedBlockingQueue<>());
            //线程同步围栏
            CountDownLatch cdl = new CountDownLatch(i);
            long end = System.currentTimeMillis();
            System.out.println((end - start)/1000 + "秒");
            long startList = System.currentTimeMillis();
            
            //线程池中取线程 执行集合
            lists.forEach((a)->
                executorService.submit(() -> {
                    dealWith(a);
                    cdl.countDown();
                }
            ));
            
            //主线程等待子线程执行完成再继续执行
            cdl.await();
            //关闭线程池
            executorService.shutdown();
            System.out.println("主线程结束");
            long endList = System.currentTimeMillis();
            System.out.println((endList - startList) + "秒");
        } catch (Exception e) {
            e.printStackTrace();
        }

多线程+List分段

public void threadMethod() {
    List<T> updateList = new ArrayList();
    // 初始化线程池, 参数一定要一定要一定要调好！！！！
    ThreadPoolExecutor threadPool = new ThreadPoolExecutor(20, 50,
            4, TimeUnit.SECONDS, new ArrayBlockingQueue(10), new ThreadPoolExecutor.AbortPolicy());
    // 大集合拆分成N个小集合, 这里集合的size可以稍微小一些（这里我用100刚刚好）, 以保证多线程异步执行, 过大容易回到单线程
    List<Long> searchData;//查询出的要删除的数据
MarketSearchMapper marketSearchMapper;
Integer batchNums = 1000;//每个批次数量
List<T> splitNList = ListUtils.partition(searchData，batchNums);
    // 记录单个任务的执行次数
    CountDownLatch countDownLatch = new CountDownLatch(splitNList.size());
    // 对拆分的集合进行批量处理, 先拆分的集合, 再多线程执行
    for (List<T> singleList : splitNList) {
        // 线程池执行
        threadPool.execute(new Thread(new Runnable(){
            @Override
            public void run() {
                for (Entity yangshiwen : singleList) {
                    // 将每一个对象进行数据封装, 并添加到一个用于存储更新数据的list
                    // ......
                    
                }
            }
        }));
        // 任务个数 - 1, 直至为0时唤醒await()
        countDownLatch.countDown();
    }
    try {
        // 让当前线程处于阻塞状态，直到锁存器计数为零
        countDownLatch.await();
    } catch (InterruptedException e) {
        throw new BusinessLogException(ResponseEnum.FAIL);
    }
    // 通过mybatis的批量插入的方式来进行数据的插入, 这一步还是要做判空
    if (GeneralUtil.listNotNull(updateList)) {
        batchUpdateEntity(updateList);
        LogUtil.info("xxxxxxxxxxxxxxx");
    }
}

二、delete和truncate区别

1、delete删除数据的原理:(delete属于DML语句)

表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
这种删除表的优点是：支持回滚，后悔了可以恢复数据，可以删除单条数据
缺点：删除效率比较低

delete from user;  //删除user表中的数据，但是这种删除数据的方式有点慢。

2、truncate删除数据的原理:(DDL)

效率比较高，表被一次截断，物理删除
优点：快速，不走事务，不会锁表，也不会产生大量日志写入日志文件
缺点：不支持回滚，只能删除表中所有数据，不能删单条数据
如果说公司项目里面有一张大表，数据非常多，几亿条记录：
删除的时候，使用delete，也许执行一个小时才能删除完，效率极其低；
可以选择使用truncate删除表中的数据。只需要不到1s的时间就能删除结束，效率较高。
但是使用truncate之前，必须仔细询问客户是否真的需要删除，并警告删除之后不可恢复！！！

truncate table user; //删除user表中的数据，快速。

3、删除表操作：

drop table 表名；// 删除表，不是删除表中的数据

truncate和delete是删除表中的数据，表还在。

参考文章
mysql批量删除大量数据
MySQL中快速删除表中的数据
Mysql 大批量删除数据（解决方案）
MySQL删除大批量数据
ListUtils.partition的用法
Java 使用线程池和CountDownLatch分批插入或者更新数据
Java批量更新太慢？多线程+List分段完美解决