Java SQL查询，数据批量新增更新优化

前言：

本文旨在记录博主在日常工作中对于，SQL查询，数据新增，修改的优化的一些见解。

一、查询SQL优化

1、索引添加

对于一个查询条件多的SQL而言，需要添加索引去优化查询的速度。

但是索引需要遵循最左前缀原则，所以显然不能将所有的索引设置为一个联合索引，这样在查询的时候往往会导致索引失效，最终走的全表检索。

例：如下为一个关联查询多，且动态查询条件多的SQL。

对于关联查询而言，若表与表之间所关联的字段为主键ID，则可以不添加索引，例如t8.id。

若关联的字段不为主键ID，则需要添加一下索引，例如 t1.rule_type,t6.code,t1.fence_type。

对于动态SQL查询条件而且，因为查询条件大部分情况是单独拼接的，所以对于t1.fence_name,

t1.fence_status这两个个字段而言，则建立两个索引。

t1.fence_name, t1.user_group不用建立索引，因为模糊查询会使索引失效。

2、索引总结

1、对于模糊查询的字段不用添加索引，模糊搜索会使索引失效。

2、对于单个出现的字段添加普通单列索引。

3、索引的个数不宜过多，一般是对于常用的查询条件才添加索引

4、对于查询多的表建立索引，修改新增删除多的表不建议添加索引。

3、语句索引分析

一般使用explain来分析SQL语句是否走了索引，在此不多赘述。

二、新增/修改 优化效率

对于新增/修改数据而言，批量处理数据如果涉及到需要查询其他表中的数据，且数据量很大，则会让接口不堪重负，所以需要做以下处理，提升接口效率。

例：现在需求从原来的系统中将账单数据迁移到新系统中保存，需要查询新系统中的用户ID号，然后批量插入。

因为旧系统中的用户数据有上万条，账单数据有几十万上百万条。所以不可能通过关联查询去检索数据，因为要获取新系统中的用户主键ID数据（accountId），所以也不可能通过账单去一条一条的SQL查询新用户数据，这样会有数数以百万计的IO操作，执行的时候长达数小时。所以我们应该如下操作。

1、一次IO操作将新系统与旧系统的用户数据查询出来，用stream流转为map，相当于存储在内存中等待调用。即oldUserAccountsMap ，newUserAccountsMap 。

2、使用stream()流去遍历账单数据（千万别用for循环啥的，速度非常慢），通过步骤1中存储到内存中的Map数据，去获取新系统用户信息数据。即newUserAccountsMap.get(accountNo)

3、将需要插入的数据存储到对象集合中【 即paymentOrderList.add(paymentOrder) 】，然后批量插入，一次IO操作批量插入数据（这个根据数据量灵活一点，如果数据量太大，可以分成数次批量插入，也能显著提升插入的效率）

public void processPaymentData() {

    List<PaymentOrderDO> paymentOrderList = new ArrayList<>();

    // 获取旧系统用户数据
    Map<Integer, String> oldUserAccountsMap = oldUserAccountsMapper.selectList().stream().collect(Collectors.toMap(OldUserAccountsDO::getId, OldUserAccountsDO::getAccountNo));

    // 获取新系统用户数据
    Map<String, NewUserAccountDO> newUserAccountsMap = newUserAccountMapper.selectList().stream().filter(o -> o.getAccountNo() != null).collect(Collectors.toMap(NewUserAccountDO::getAccountNo, o -> o));

    // 获取旧系统的数据
    List<OldPaymentLogsDO> oldPaymentLogsList = oldPaymentLogsMapper.selectList();

    oldPaymentLogsList.stream().forEach(oldPaymentLog -> {
        // 获取订单号
        String orderNo = oldPaymentLog.getOrderNo();
        // 获取支付金额
        BigDecimal orderMoney = oldPaymentLog.getOrderMoney;
        // 获取旧系统用户accountId
        Integer accountId = oldPaymentLog.getAccountId();
        // 获取用户户号
        String accountNo = oldUserAccountsMap.get(accountId);

        // 获取新系统用户信息数据
        NewUserAccountDO newUserAccount = newUserAccountsMap.get(accountNo);

        // 拼装新数据
        PaymentOrderDO paymentOrder = new PaymentOrderDO();
        paymentOrder.setOrderNo(orderNo);
        paymentOrder.setAccountId(newUserAccount.getId());
        paymentOrder.setAccountNo(newUserAccount.getAccountNo());
        paymentOrder.setAccountName(newUserAccount.getAccountName());
        paymentOrder.setOrderMoney(orderMoney);
        paymentOrderList.add(paymentOrder);
    });

    paymentOrderMapper.insertBatch(paymentOrderList);

    System.out.println("插入成功，数据总数为：" + paymentOrderList.size());
}