简介：工作之中，遇百万级数据加载内存的需求，特此记录，与诸君参考！
框架：springboot2.7+mybatisPlus3.5.1+mariadb10.x
方案一：使用mybatis-plus原生加载服务类中的查询方法如下.
public void findAll(){
QueryWrapper queryWrapper=new QueryWrapper<>();
queryWrapper.select("check_date","hosp_id","yesterday_amt","recharge_amt","payment_amt");
long startDateTime=System.currentTimeMillis();
List list=list(queryWrapper);
long endDateTIme=System.currentTimeMillis();
log.info("【查询日金额表所有数据】消耗时间:{}",(endDateTIme-startDateTime));
}
执行结果：总数据217万5872条，查询时间：10299=10秒
方案二基于mybatis-plus，在xml中自定义SQL语句
@Mapper//mapper.java public interface DailyAmtMapper extends BaseMapper{ List findAll(); }
//DailyAmtService.java
public void findAll(){
long startDateTime=System.currentTimeMillis();
List list=dailyAmtMapper.findAll();;
long endDateTIme=System.currentTimeMillis();
log.info("【查询日金额表所有数据】消耗时间:{},list长度:{}",(endDateTIme-startDateTime),list.size());
}
执行以上代码耗时9989毫秒=9.9秒
方案三：使用JDBC自定义查询
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.sql.*;
import static com.softscenene.server.drugPurchase.config.jdbc.PropertiesInfo.DBINFO_MYSQL;
@Slf4j
@Component
public class JDBCConnection {
private Connection conn=null;
private Statement stmt=null;
private ResultSet rs=null;
private final int FETCH_SIZE=10000;
@Resource
@Qualifier("mysql")
private DBInfo dbInfoMysql;
@Resource
@Qualifier("oracle")
private DBInfo dBInfoOracle;
@SneakyThrows
public ResultSet connection(String sql,String dbName){
log.info("自定义JDBC——连接数据库——连接参数:{}",dbName);
DBInfo dbInfo=DBINFO_MYSQL.equals(dbName)?dbInfoMysql:dBInfoOracle;
if(dbInfo==null){
log.warn("JDBC连接数据库——获取配置信息失败，数据源名称不存在.dbinfo:{}",dbInfo);
return null;
}
//注册驱动
DriverManager.registerDriver((Driver) Class.forName(dbInfo.getDriverClassName()).newInstance());
//获取连接
conn= DriverManager.getConnection(dbInfo.getUrl(),dbInfo.getUsername(),dbInfo.getPassword());
//获取数据库操作对象
stmt=conn.createStatement();
stmt.setFetchSize(FETCH_SIZE);
rs=stmt.executeQuery(sql);//执行给定SQL语句，该语句返回单个ResultSet对象
return rs;
}
@SneakyThrows
public void close() {
rs.close();
stmt.close();
conn.close();
}
}
@Test
void test() throws SQLException {
long startDateTime=System.currentTimeMillis();
ArrayList arrayList=new ArrayList<>();
String sql="SELECT check_date,hosp_id,yesterday_amt,recharge_amt,payment_amt from hosp_daily_amt";
ResultSet resultSet=jdbcConnection.connection(sql,DBINFO_MYSQL);
while (resultSet.next()){
DailyAmt dailyAmt=new DailyAmt();
dailyAmt.setCheckDate(resultSet.getString(1));
dailyAmt.setHospId(resultSet.getString(2));
dailyAmt.setYesterdayAmt(new BigDecimal(resultSet.getString(3)));
dailyAmt.setRechargeAmt(new BigDecimal(resultSet.getString(4)));
dailyAmt.setPaymentAmt(new BigDecimal(resultSet.getString(5)));
arrayList.add(dailyAmt);
}
long endDateTIme=System.currentTimeMillis();
log.info("【查询日金额表所有数据】消耗时间:{},list长度:{}",(endDateTIme-startDateTime),arrayList.size());
}
自定义JDBC，耗时6秒
另外，连接参数也是影响性能的因素，附上mysql连接URL:
jdbc:mysql://8.0.0.158:3306/drug?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&rewriteBatchedStatements=true
总结：影响性能的主要因素主要有2个，fetchsize大小，以及其解析字段所消耗时间，字段越多，解析耗时越长。
