数据库大量数据操作中事务优化方案

1、业务场景：

1）短时间内向数据库中插入大量数据；

2）大批量数据修改操作；

3）批量删除数据。

2、两个弊端方案

1、处理完毕后统一提交数据库；

2、每操作完一条记录，就提交更新。

3、优化方案:批量事务

1、增加计数标识，记录已处理数据的量；

2、设定每批处理量的大小；

3、自己控制事务，每达到一定数量，即可提交一次事务，更新至数据库。

4、优点

1、避免统一提交，对数据库造成瞬间压力，从而避免影响其他业务；

2、分批提交，有效减少了数据库连接次数，降低了数据库资源的过度消耗。

5、代码实现

package com.sinosig.job;

import com.sinosig.model.PrecisionPrice;
import com.sinosig.service.credit.CreditsMonthRecordService;
import com.sinosig.service.order.PrecisionPriceService;
import com.sinosig.service.user.UserCreditsService;
import org.apache.commons.lang3.time.DateUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.quartz.DisallowConcurrentExecution;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.quartz.PersistJobDataAfterExecution;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.quartz.QuartzJobBean;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;

/**
 * 每月删除一月前的无用报价信息
 * <p/>
 * 每月1号2点执行
 *
 * @author kou
 */
@PersistJobDataAfterExecution
@DisallowConcurrentExecution
public class ClearHisPricesInfoJob extends QuartzJobBean {

    private static final Logger log = LoggerFactory.getLogger(ClearHisPricesInfoJob.class);

    @Resource(name = "sessionFactory")
    private SessionFactory sessionFactory;

    @Resource
    CreditsMonthRecordService creditsMonthRecordService;

    @Resource
    UserCreditsService userCreditsService;

    @Resource
    private PrecisionPriceService precisionPriceService;

    @Override
    protected void executeInternal(JobExecutionContext context)
            throws JobExecutionException {

        log.info("每日清理历史不用报价信息,任务开始...");
        Date beginTime = DateUtils.addMonths(new Date(), -2);
        Date endTime = DateUtils.addMonths(new Date(), -1);

        List<PrecisionPrice> list = precisionPriceService.findInvalidData(true, beginTime, endTime);

        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        int totalNum = list.size();
//      1、删除无用险种
        for (int i = 0; i < totalNum; i++) {
            PrecisionPrice precisionPrice = list.get(i);

            String hql = "delete from com.sinosig.model.KindCode where precisionPriceId = ?";
            Query q = session.createQuery(hql);
            q.setParameter(0, precisionPrice.getId());
            q.executeUpdate();

            if (i % 40 == 0 || i == totalNum - 1) {
                session.flush();
                session.clear();
            }
        }

//      2、删除无用保险信息
        for (int i = 0; i < totalNum; i++) {
            PrecisionPrice precisionPrice = list.get(i);
            String sql = "delete from b_precision_price where id = ?";

            Query q = session.createSQLQuery(sql);
            q.setParameter(0, precisionPrice.getId());
            q.executeUpdate();

            if (i % 40 == 0 || i == totalNum-1) {
                session.flush();
                session.clear();
            }


        }

        tx.commit();
        session.close();
        log.info("每日清理历史不用报价信息,任务结束。");

    }


}