问题描述:
1、大表导出时,SQL语句没用使用
Order by
,出现数据重复。
2、当添加Order by
时,由于数据表太大查询慢。
解决方案:
-
在大数据表中添加计数字段,即:类似自增主键,分页查询时,使用此字段当作条件。(目前使用此方法解决)
-
使用
Mybatis
中的Cursor
功能,只能单线程,将fetchSize
设置为 10000,性能还可以接受。<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.export.dao.BigTableMapper"> <select id="getBigTableCursor" resultType="map" resultOrdered="true" fetchSize="10000"> select * from BigTable_JBXX </select> </mapper>
@Mapper public interface BigTableMapper { Cursor<Map<String,Object>> getBigTableCursor(); }
@Transactional
public String exportAllData() {
Cursor<Map<String, Object>> bitTableCursor = bitTableMapper.getDyJbxxCursor();
int startpage = 1;
Iterator<Map<String, Object>> iterator = bitTableCursor.iterator();
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(10000);
int num = 0;
long startTime = System.currentTimeMillis();
while (iterator.hasNext()) {
num++;
list.add(iterator.next());
if (num == 10000) {
long endTime = System.currentTimeMillis();
// System.out.println("pagenum:" + startpage + "耗时:" + (endTime - startTime) / 1000 + " s");
// startTime = endTime;
// startpage++;
List<Map<String, Object>> tempList = new ArrayList<Map<String, Object>>(10000);
tempList.addAll(list);
dataToDiskService.saveDatoToDisk(tempList, startpage);
list.clear();
num = 0;
}
}
long endTime = System.currentTimeMillis();
dataToDiskService.saveDatoToDisk(list, startpage);
System.out.println("pagenum:" + startpage + "耗时:" + (endTime - startTime) / 1000 + " s");
return "SUCCESS";
}