| 時間 | 更新內(nèi)容 |
|---|---|
| 2023/09/23 | fix: 每個sheet大小和存儲內(nèi)存條數(shù)一致的bug update: 增大一個sheet的默認(rèn)容量 |
前言.萬級數(shù)據(jù)優(yōu)化
我們不妨先給大家講一個概念，利用此概念我們正好給大家介紹一個數(shù)據(jù)庫優(yōu)化的小技巧： 需求如下：將一個地市表的數(shù)據(jù)導(dǎo)出70萬條。
如果你不假思索，直接一條sql語句搞上去，直接就會內(nèi)存溢出，因為mysql會將結(jié)果記錄統(tǒng)一查詢出來然后返還給內(nèi)存：那內(nèi)存可能直接OOM!
@Test
public void testQuery1() {
// 1、定義資源
Connection connection = null;
ResultSet resultSet = null;
PreparedStatement statement = null;
String sql = "select * from user";
try {
// 獲取連接
connection = DBUtil.getConnection();
// 獲取使用預(yù)編譯的statement
statement = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
resultSet = statement.executeQuery();
while (resultSet.next()){
System.out.println("name---->" + resultSet.getString("nick_name") );
}
long end = System.currentTimeMillis();
System.out.println(end -start);
} catch (SQLException e){
e.printStackTrace();
} finally {
// 關(guān)閉資源
DBUtil.closeAll(connection,statement,resultSet);
}
}
所以我們通常有如下幾種解決方案：
一. 直接上流式查詢封裝工具代碼
使用2核4G云服務(wù)器 下載速度在40-50s之間波動. 本機(jī)或大水管.我只能說更快了
/**
* @author YuanJie
* @projectName vector-server
* @package com.vector.common.utils.easyexcel
* @className com.vector.common.utils.easyexcel.CustomCellWeightStrategy
* @copyright Copyright 2020 vector, Inc All rights reserved.
* @date 2023/8/28 17:58
*/
public class CustomCellWeightStrategy extends AbstractColumnWidthStyleStrategy {
private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
int columnWidth = this.dataLength(cellDataList, cell, isHead)+8;
if (columnWidth >= 0) {
if (columnWidth > 254) {
columnWidth = 254;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 200);
}
//設(shè)置單元格類型
cell.setCellType(CellType.STRING);
// 數(shù)據(jù)總長度
int length = cell.getStringCellValue().length();
// 換行數(shù)
int rows = cell.getStringCellValue().split("\n").length;
// 默認(rèn)一行高為20
cell.getRow().setHeightInPoints(rows * 20);
}
}
}
/**
* 計算長度
*
* @param cellDataList
* @param cell
* @param isHead
* @return
*/
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData<?> cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
// 換行符（數(shù)據(jù)需要提前解析好）
int index = cellData.getStringValue().indexOf("\n");
return index != -1 ?
cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
/**
* @author YuanJie
* @projectName vector-server
* @package com.vector.common.utils
* @className com.vector.common.utils.easyexcel.EasyExcelUtil
* @copyright Copyright 2020 vector, Inc All rights reserved.
* @date 2023/8/26 1:17
*/
@Slf4j
public class EasyExcelUtil {
private static final String DATE_FORMAT = "yyyy-MM-dd";
/**
* 設(shè)置批量存儲最大值,也影響sheet頁數(shù)
*/
private static final Integer MAX_SHEET_DATA = 100000;
/**
* 設(shè)置內(nèi)存最大值
*/
private static final Integer MAX_MEMORY_DATA = 1000;
/**
* 使用EasyExcel生成Excel xls
*
* @param response 響應(yīng)對象
* @param fileNameParam 文件名
* @param sheetName 表格名
* @param clazz 導(dǎo)出實體類
* @param t 查庫入?yún)? * @param func 流式查詢方法 Cursor<ResultVo> listOrders(@Param("userName") String userName);
*/
public static <T> void writeExcelXls(HttpServletResponse response, String fileNameParam,
String sheetName, Class<?> clazz, T t,
Function<T, Cursor<?>> func) throws Exception {
streamExportExcel(response, fileNameParam, sheetName, clazz, ExcelTypeEnum.XLS.getValue(), t, func);
}
/**
* 使用EasyExcel生成Excel xlsx
*
* @param response 響應(yīng)對象
* @param fileNameParam 文件名
* @param sheetName 表格名
* @param clazz 導(dǎo)出實體類
* @param t 查庫入?yún)? * @param func 流式查詢方法 Cursor<ResultVo> listOrders(@Param("userName") String userName);
*/
public static <T> void writeExcelXlsx(HttpServletResponse response, String fileNameParam,
String sheetName, Class<?> clazz, T t,
Function<T, Cursor<?>> func) throws Exception {
streamExportExcel(response, fileNameParam, sheetName, clazz, ExcelTypeEnum.XLSX.getValue(), t, func);
}
/**
* 流式導(dǎo)出 Excel
*
* @param response 響應(yīng)對象
* @param fileNameParam 文件名
* @param sheetName 表格名
* @param clazz 導(dǎo)出實體類
* @param excelType 導(dǎo)出類型
* @param t 查庫入?yún)? * @param func 流式查詢方法 Cursor<ResultVo> listOrders(@Param("userName") String userName);
* @throws Exception 異常
*/
private static <T> void streamExportExcel(HttpServletResponse response, String fileNameParam,
String sheetName, Class<?> clazz, String excelType,
T t, Function<T, Cursor<?>> func) throws Exception {
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(DATE_FORMAT);
String fileName = fileNameParam + dateTimeFormatter.format(LocalDateTime.now()) + excelType;
ExcelWriter excelWriter = EasyExcel
.write(getOutputStream(fileName, response, excelType), clazz)
.registerWriteHandler(new CustomCellWeightStrategy())
.build();
// 內(nèi)容樣式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 設(shè)置自動換行，前提內(nèi)容中需要加「\n」才有效
contentWriteCellStyle.setWrapped(true);
// 這個策略是 頭是頭的樣式 內(nèi)容是內(nèi)容的樣式 其他的策略可以自己實現(xiàn)
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(null, contentWriteCellStyle);
Cursor<?> cursor;
List<Object> list = new ArrayList<>();
int page = 0;
WriteSheet writeSheet = EasyExcel
.writerSheet(++page, sheetName + page)
.registerWriteHandler(horizontalCellStyleStrategy)
.build();
// 流式數(shù)據(jù)庫查詢
cursor = func.apply(t);
int count = 0;
try {
for (Object o : cursor) {
list.add(o);
if(list.size() == MAX_MEMORY_DATA){
count += list.size();
excelWriter.write(list, writeSheet);
list.clear();
// 每個sheet頁最大存儲MAX_SHEET_DATA條數(shù)據(jù)
if (count >= MAX_SHEET_DATA) {
writeSheet = EasyExcel
.writerSheet(++page, sheetName + page)
.registerWriteHandler(horizontalCellStyleStrategy)
.build();
count = 0;
}
}
}
// 處理最后不足MAX_SHEET_DATA的數(shù)據(jù)
if (list.size() > 0) {
writeSheet = EasyExcel
.writerSheet(++page, sheetName + page)
.registerWriteHandler(horizontalCellStyleStrategy)
.build();
excelWriter.write(list, writeSheet);
list.clear();
}
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
String json = JacksonInstance.toJson(R.errorResult(EnumHttpCode.SYSTEM_ERROR, "下載文件失敗" + e.getMessage()));
response.getWriter().println(json);
} finally {
if (cursor != null) {
cursor.close();
}
if (excelWriter != null) {
excelWriter.finish();
}
}
}
/**
* 導(dǎo)出文件時為Writer生成OutputStream
*
* @param finalName 文件名
* @param response 響應(yīng)對象
* @param excelType 導(dǎo)出文件類型
* @return OutputStream
*/
private static OutputStream getOutputStream(String finalName, HttpServletResponse response, String excelType) throws Exception {
response.reset();
finalName = URLEncoder.encode(finalName, StandardCharsets.UTF_8);
if (ExcelTypeEnum.XLS.getValue().equals(excelType)) {
response.setContentType("application/vnd.ms-excel");
} else if (ExcelTypeEnum.XLSX.getValue().equals(excelType)) {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment; filename=" + finalName);
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
}
}
@Data
public class Dto {
@NumberFormat("#")
@ExcelProperty(value = "地市編碼", index = 0)
Long code;
@ExcelProperty(value = "地市名稱", index = 1)
String name;
@NumberFormat("#")
@ExcelProperty(value = "地市級別", index = 2)
Integer level;
@NumberFormat("#")
@ExcelProperty(value = "地市父編碼", index = 3)
Long pcode;
@NumberFormat("#")
@ExcelProperty(value = "地市父名稱", index = 4)
Integer category;
}
@Resource
private ExportMapper exportMapper;
@Resource
private HttpServletRequest request;
@Resource
private HttpServletResponse response;
@GetMapping("/export")
@Transactional
public void export() throws Exception {
Long params = 110101001000L;
EasyExcelUtil.writeExcelXlsx(
response,
"地市信息",
"地市區(qū)域",
Dto.class,
params,
param -> exportMapper.export(null));
}
public interface ExportMapper {
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
@ResultType(Dto.class)
@Select("select * from area_code_2023")
Cursor<Dto> export(@Param("params") Long params);
}
二. 傳統(tǒng)分頁導(dǎo)出查詢
大表的深度分頁性能很差,也受制于表設(shè)計的影響@Test
public void testQuery2() {
// 1、定義資源
Connection connection = null;
ResultSet resultSet = null;
PreparedStatement statement = null;
String sql = "select * from user limit ?,?";
try {
// 獲取連接
connection = DBUtil.getConnection();
// 獲取使用預(yù)編譯的statement
statement = connection.prepareStatement(sql);
// 獲取結(jié)果集
long start = System.currentTimeMillis();
long begin = 0L, offset = 10000L;
while (true){
statement.setLong(1,begin);
statement.setLong(2,offset);
begin += offset;
resultSet = statement.executeQuery();
boolean flag = resultSet.next();
if(!flag) break;
while (flag){
System.out.println("name---->" + resultSet.getString("nick_name") );
flag = resultSet.next();
}
}
long end = System.currentTimeMillis();
System.out.println(end -start);
} catch (SQLException e){
e.printStackTrace();
} finally {
// 關(guān)閉資源
DBUtil.closeAll(connection,statement,resultSet);
}
}
三. 流式查詢概念
采用傳統(tǒng)的Stream流式思想，將直接提供數(shù)據(jù)替換成提供獲取數(shù)據(jù)的管道，客戶端讀取數(shù)據(jù)時直接從管道中遍歷獲??；整個讀取的過程需要客戶端保持和服務(wù)端的連接，也很好理解，它實際是一個管道，管道得通著才能取數(shù)據(jù)。 流式查詢有兩種使用方式，一種是用Cursor作為返回值，對數(shù)據(jù)進(jìn)行遍歷操作；一種是不設(shè)置返回值，在入?yún)⒅袀魅胍粋€ResultHandler作為回調(diào)處理數(shù)據(jù)。本文將基于Mybatis具體介紹使用方法。 這兩種返回值的使用方式是相似的，唯一區(qū)別就是返回值不同。Mybatis查詢有兩種方式，一種是基于注解加在Mapper接口上方，一種是寫在xml文件中，主要需要設(shè)置以下幾個屬性：| ResultSetType | 結(jié)果集讀取方式 |
|---|---|
| FetchSize | MySQL服務(wù)端單次發(fā)送至客戶端的數(shù)據(jù)條數(shù) |
| ResultType | 這個眼熟吧，設(shè)置返回實體類映射 |
ResultSetType有4種可選項
DEFAULT(-1),
FORWARD_ONLY(1003),
SCROLL_INSENSITIVE(1004),
SCROLL_SENSITIVE(1005);
FORWARD_ONLY，顧名思義只能向前，即數(shù)據(jù)只能向前讀取，是不是就類似一個流水的管道，讀一條就相當(dāng)于水流過去一些。也是我們需要選用的。
SCROLL_INSENSITIVE，不敏感滾動，和下面那個差不多，都是可以向后讀或向前讀；這意味著已讀取過的數(shù)據(jù)不能丟掉，要繼續(xù)保存在內(nèi)存中，因為有可能會回去再次讀取他們。
SCROLL_SENSITIVE，敏感滾動，和上面那個差不多。
這么一比較就看得出來，當(dāng)選的一定是FORWARD_ONLY，我們亟需解決的就是大數(shù)據(jù)量對內(nèi)存的影響，再用后面兩個還是會放在內(nèi)存中。
FetchSize，這個概念在許多服務(wù)中都有提及，例如RabbitMQ中是消費(fèi)者取過來預(yù)處理的消息數(shù)量，但在MySQL中完全不是一個概念。MySQL的數(shù)據(jù)傳輸是基于C/S的阻塞機(jī)制，即Client設(shè)置FetchSize = 1000，而Server查出來10000條數(shù)據(jù)，按照常理應(yīng)該是Server智能地使用分頁策略1000條1000條取；實際不是，Server查出來多少就是多少，他會放在自己特定的內(nèi)存空間內(nèi)，只是會根據(jù)FetchSize的大小一點(diǎn)一點(diǎn)傳送給Client——利用C/S的通訊阻塞，發(fā)1000條、堵一下、發(fā)1000條、堵一下……。
JDBC官方給出的答案是設(shè)置為“Integer.MIN_VALUE”，具體原因不清楚，但我猜是為了和游標(biāo)查詢區(qū)分開，因為一會你會發(fā)現(xiàn)流式查詢和游標(biāo)查詢唯一的區(qū)別就是FetchSize的大小。注解式
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
@ResultType(ResultVo.class)
@Select("SELECT *, 0 orderType FROM `table`\n" +
" WHERE username = #{userName}")
Cursor<ResultVo> listOrders(@Param("userName") String userName);
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
@ResultType(ResultVo.class)
@Select("SELECT *, 0 orderType FROM `table`\n" +
" WHERE username = #{userName}")
void listOrders2(@Param("userName") String userName, ResultHandler<ResultVo> handler);
使用Mybatis的注解，在 @Options 中指定查詢配置參數(shù)，在@ResultType中指定返回值類型 ，在 @Select中指定查詢語句。最后用Cursor接收返回值，Cursor是可遍歷的，所以直接Foreach遍歷即可；或者返回void 用ResultHandler處理數(shù)據(jù)回調(diào)，在調(diào)用方式時傳入new ResultHandler并寫明處理邏輯。
xml式
<select id="listOrders" resultType="com.vo.ResultVo" resultSetType="FORWARD_ONLY" fetchSize="Integer.MIN_VALUE">
SELECT *, 1 stuffCount, 1 orderType FROM `table`
WHERE username = #{userName}
</select>
需要注意的是，不可以注解 + xml混合使用，比如注解指定fetchSize，xml只寫查詢語句，這種只有xml語句會生效?。。∫蝗米⒔?，要不全用xml?。?！
流式查詢由于需要保持客戶端與服務(wù)端的連接，而一般查詢提交完連接就會關(guān)閉；因此我們需要保持事務(wù)開啟，否則會報“A Cursor is already closed.”，即Cursor已經(jīng)關(guān)閉，沒法再讀取了。最簡單的方法就是在方法上加@Transactional，在查詢完畢以前事務(wù)會一直持有這個數(shù)據(jù)庫連接，但我們在使用完畢后也要自行關(guān)閉連接，顯式調(diào)用Cursor.close()，或者用try with resource語句。
游標(biāo)查詢和流式查詢的區(qū)分是fetchSize = Integer.MIN_VALUE
Cursor 還提供了三個方法：
-
isOpen()：用于在取數(shù)據(jù)之前判斷 Cursor 對象是否是打開狀態(tài)。只有當(dāng)打開時 Cursor 才能取數(shù)據(jù)； -
isConsumed()：用于判斷查詢結(jié)果是否全部取完； -
getCurrentIndex()：返回已經(jīng)獲取了多少條數(shù)據(jù)。
try(Cursor cursor = OrderMapper.listOrders()) {
cursor.forEach(rowObject -> {
// ...
});
}
OrderMapper.listOrders2(queryWrapper,resultContext -> {
ResultVo result = resultContext.getResultObject();
//這邊循環(huán)調(diào)用就可以實現(xiàn)業(yè)務(wù)了
}
游標(biāo)查詢
和流式查詢類似fetchSize不設(shè)置為MIN_VALUE即可
JDBC查詢默認(rèn)是不支持FetchSize屬性的，需要在JDBC連接URL后面加上**“useCursorFetch=true”。**
useCursorFetch=true 是針對 MySQL 數(shù)據(jù)庫的 JDBC 連接參數(shù)，用于啟用服務(wù)器端游標(biāo)獲取數(shù)據(jù)。在 MyBatis 中，當(dāng)使用流式查詢（例如：分頁查詢、結(jié)果集處理和使用游標(biāo)等）時，這個配置可以幫助逐行從服務(wù)器檢索數(shù)據(jù)，而不是一次性將所有數(shù)據(jù)加載到內(nèi)存中，從而降低內(nèi)存占用。
當(dāng)使用 MySQL 數(shù)據(jù)庫時，在 JDBC 連接字符串中加入 useCursorFetch=true，并結(jié)合設(shè)置合適的 fetchSize，可以避免因一次性加載過多數(shù)據(jù)導(dǎo)致的內(nèi)存溢出問題。注意，此配置僅對 MySQL 數(shù)據(jù)庫有效。 如果不設(shè)置 useCursorFetch=true 這個配置，僅使用之前提到的那些配置（如設(shè)置 defaultFetchSize、分頁查詢、結(jié)果集處理和使用游標(biāo)等），在大多數(shù)情況下，這些配置仍然可以有效地避免查詢導(dǎo)致的內(nèi)存溢出。
但需要注意的是，對于 MySQL 數(shù)據(jù)庫，如果不啟用服務(wù)器端游標(biāo)獲取數(shù)據(jù)，這可能會影響到流式查詢的效果。因為在默認(rèn)情況下，MySQL JDBC 驅(qū)動會一次性將所有數(shù)據(jù)加載到內(nèi)存中。此時，即使使用了其他配置，也可能無法達(dá)到預(yù)期的內(nèi)存優(yōu)化效果。
總的來說，在使用 MySQL 數(shù)據(jù)庫時，推薦在 JDBC 連接字符串中加入 useCursorFetch=true 配置，以更好地支持流式查詢和降低內(nèi)存占用。在其他數(shù)據(jù)庫中，可以根據(jù)實際需求和場景選擇合適的配置和策略來避免查詢導(dǎo)致的內(nèi)存溢出。
還要知道如何判斷自己是否使用了流式查詢或游標(biāo)查詢，下面是幾個數(shù)據(jù)集的對應(yīng)關(guān)系
| 普通分頁 | ResultsetRowsStatic | RowDataStatic |
|---|---|---|
| 查詢方式 | 結(jié)果集類型 | 行數(shù)據(jù)類型 |
| 流式查詢 | ResultsetRowsStreaming | RowDataDynamic |
| 游標(biāo)查詢 | ResultsetRowsCursor | RowDataCursor |
這3種查詢方式，常規(guī)非大數(shù)據(jù)模式下普通查詢最快，其次是流式查詢，最次是游標(biāo)查詢.文章來源：http://www.zghlxwxcb.cn/news/detail-681534.html
主要是由于游標(biāo)查詢需要和數(shù)據(jù)庫進(jìn)行多次網(wǎng)絡(luò)交互，Client處理完這部分后再拉取下一部分?jǐn)?shù)據(jù)，因此會比較慢。但是流式查詢又會長時間占用同一個數(shù)據(jù)庫連接，因此要取舍一下是能接受連接一直持有但是可能會堵住導(dǎo)致響應(yīng)慢，還是可能占用較多連接數(shù)但單次響應(yīng)快。當(dāng)通過流式查詢獲取一個ResultSet后，在你通過next迭代出所有元素之前或者調(diào)用close關(guān)閉它之前，你不能使用同一個數(shù)據(jù)庫連接去發(fā)起另外一個查詢，否者拋出異常（第一次調(diào)用的正常，第二次的拋出異常）。文章來源地址http://www.zghlxwxcb.cn/news/detail-681534.html
到了這里，關(guān)于萬級數(shù)據(jù)優(yōu)化EasyExcel+mybatis流式查詢導(dǎo)出封裝的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
