做相同网站违法吗,网站美化的目标,注册免费微网站,seo搜索引擎优化内容文章目录 前言.千万级数据优化一. 直接上流式查询封装工具代码二. 传统分页导出查询三. 流式查询概念游标查询 前言.千万级数据优化 我们不妨先给大家讲一个概念#xff0c;利用此概念我们正好给大家介绍一个数据库优化的小技巧#xff1a; 需求如下#xff1a;将一个地市表… 文章目录 前言.千万级数据优化一. 直接上流式查询封装工具代码二. 传统分页导出查询三. 流式查询概念游标查询 前言.千万级数据优化 我们不妨先给大家讲一个概念利用此概念我们正好给大家介绍一个数据库优化的小技巧 需求如下将一个地市表的数据导出70万条。 如果你不假思索直接一条sql语句搞上去直接就会内存溢出因为mysql会将结果记录统一查询出来然后返还给内存那内存可能直接OOM!
Test
public void testQuery1() {// 1、定义资源Connection connection null;ResultSet resultSet null;PreparedStatement statement null;String sql select * from user;try {// 获取连接connection DBUtil.getConnection();// 获取使用预编译的statementstatement 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 {// 关闭资源DBUtil.closeAll(connection,statement,resultSet);}
}所以我们通常有如下几种解决方案
一. 直接上流式查询封装工具代码
使用2核4G云服务器 表格美化 CustomCellWeightStrategy.class
/*** 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 MapInteger, MapInteger, Integer CACHE new HashMap();Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, ListWriteCellData? cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {boolean needSetWidth isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {MapInteger, 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);}//设置单元格类型cell.setCellType(CellType.STRING);// 数据总长度int length cell.getStringCellValue().length();// 换行数int rows cell.getStringCellValue().split(\n).length;// 默认一行高为20cell.getRow().setHeightInPoints(rows * 20);}}}/*** 计算长度** param cellDataList* param cell* param isHead* return*/private Integer dataLength(ListWriteCellData? 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:// 换行符数据需要提前解析好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;}}}}
}封装工具类EasyExcelUtil.class
/*** 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;/*** 设置批量存储最大值,也影响sheet页数*/private static final Integer MAX_SHEET_DATA 50000;/*** 设置内存最大值*/private static final Integer MAX_MEMORY_DATA 1000;/*** 使用EasyExcel生成Excel xls** param response 响应对象* param fileNameParam 文件名* param sheetName 表格名* param clazz 导出实体类* param t 查库入参* param func 流式查询方法 CursorResultVo listOrders(Param(userName) String userName);*/public static T void writeExcelXls(HttpServletResponse response, String fileNameParam,String sheetName, Class? clazz, T t,FunctionT, Cursor? func) throws Exception {streamExportExcel(response, fileNameParam, sheetName, clazz, ExcelTypeEnum.XLS.getValue(), t, func);}/*** 使用EasyExcel生成Excel xlsx** param response 响应对象* param fileNameParam 文件名* param sheetName 表格名* param clazz 导出实体类* param t 查库入参* param func 流式查询方法 CursorResultVo listOrders(Param(userName) String userName);*/public static T void writeExcelXlsx(HttpServletResponse response, String fileNameParam,String sheetName, Class? clazz, T t,FunctionT, Cursor? func) throws Exception {streamExportExcel(response, fileNameParam, sheetName, clazz, ExcelTypeEnum.XLSX.getValue(), t, func);}/*** 流式导出 Excel** param response 响应对象* param fileNameParam 文件名* param sheetName 表格名* param clazz 导出实体类* param excelType 导出类型* param t 查库入参* param func 流式查询方法 CursorResultVo listOrders(Param(userName) String userName);* throws Exception 异常*/private static T void streamExportExcel(HttpServletResponse response, String fileNameParam,String sheetName, Class? clazz, String excelType,T t, FunctionT, 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();// 内容样式WriteCellStyle contentWriteCellStyle new WriteCellStyle();// 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置自动换行前提内容中需要加「\n」才有效contentWriteCellStyle.setWrapped(true);// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现HorizontalCellStyleStrategy horizontalCellStyleStrategy new HorizontalCellStyleStrategy(null, contentWriteCellStyle);Cursor? cursor;ListObject list new ArrayList();int page 0;WriteSheet writeSheet EasyExcel.writerSheet(page, sheetName page).registerWriteHandler(horizontalCellStyleStrategy).build();// 流式数据库查询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条数据if (count MAX_SHEET_DATA) {writeSheet EasyExcel.writerSheet(page, sheetName page).registerWriteHandler(horizontalCellStyleStrategy).build();}}}// 处理最后不足MAX_SHEET_DATA的数据if (list.size() 0) {writeSheet EasyExcel.writerSheet(page, sheetName page).registerWriteHandler(horizontalCellStyleStrategy).build();excelWriter.write(list, writeSheet);list.clear();}} catch (Exception e) {// 重置responseresponse.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();}}}/*** 导出文件时为Writer生成OutputStream** param finalName 文件名* param response 响应对象* param excelType 导出文件类型* 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-age0);return response.getOutputStream();}
}导出实体 Dto.class
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;
}测试用例 MeTestController.class Resourceprivate ExportMapper exportMapper;Resourceprivate HttpServletRequest request;Resourceprivate HttpServletResponse response;GetMapping(/export)Transactionalpublic void export() throws Exception {Long params 110101001000L;EasyExcelUtil.writeExcelXlsx(response,地市信息,地市区域,Dto.class,params,param - exportMapper.export(null));}测试Dao ExportMapper.class
public interface ExportMapper {Options(resultSetType ResultSetType.FORWARD_ONLY, fetchSize Integer.MIN_VALUE)ResultType(Dto.class)Select(select * from area_code_2023)CursorDto export(Param(params) Long params);
} 二. 传统分页导出查询 大表的深度分页性能很差,也受制于表设计的影响
Test
public void testQuery2() {// 1、定义资源Connection connection null;ResultSet resultSet null;PreparedStatement statement null;String sql select * from user limit ?,?;try {// 获取连接connection DBUtil.getConnection();// 获取使用预编译的statementstatement connection.prepareStatement(sql);// 获取结果集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 {// 关闭资源DBUtil.closeAll(connection,statement,resultSet);}
}三. 流式查询概念
采用传统的Stream流式思想将直接提供数据替换成提供获取数据的管道客户端读取数据时直接从管道中遍历获取整个读取的过程需要客户端保持和服务端的连接也很好理解它实际是一个管道管道得通着才能取数据。 采用传统的Stream流式思想将直接提供数据替换成提供获取数据的管道客户端读取数据时直接从管道中遍历获取整个读取的过程需要客户端保持和服务端的连接也很好理解它实际是一个管道管道得通着才能取数据。 流式查询有两种使用方式一种是用Cursor作为返回值对数据进行遍历操作一种是不设置返回值在入参中传入一个ResultHandler作为回调处理数据。本文将基于Mybatis具体介绍使用方法。 这两种返回值的使用方式是相似的唯一区别就是返回值不同。Mybatis查询有两种方式一种是基于注解加在Mapper接口上方一种是写在xml文件中主要需要设置以下几个属性
ResultSetType结果集读取方式FetchSizeMySQL服务端单次发送至客户端的数据条数ResultType这个眼熟吧设置返回实体类映射
ResultSetType有4种可选项
DEFAULT(-1),
FORWARD_ONLY(1003),
SCROLL_INSENSITIVE(1004),
SCROLL_SENSITIVE(1005);FORWARD_ONLY顾名思义只能向前即数据只能向前读取是不是就类似一个流水的管道读一条就相当于水流过去一些。也是我们需要选用的。 SCROLL_INSENSITIVE不敏感滚动和下面那个差不多都是可以向后读或向前读这意味着已读取过的数据不能丢掉要继续保存在内存中因为有可能会回去再次读取他们。 SCROLL_SENSITIVE敏感滚动和上面那个差不多。 这么一比较就看得出来当选的一定是FORWARD_ONLY我们亟需解决的就是大数据量对内存的影响再用后面两个还是会放在内存中。
FetchSize这个概念在许多服务中都有提及例如RabbitMQ中是消费者取过来预处理的消息数量但在MySQL中完全不是一个概念。MySQL的数据传输是基于C/S的阻塞机制即Client设置FetchSize 1000而Server查出来10000条数据按照常理应该是Server智能地使用分页策略1000条1000条取实际不是Server查出来多少就是多少他会放在自己特定的内存空间内只是会根据FetchSize的大小一点一点传送给Client——利用C/S的通讯阻塞发1000条、堵一下、发1000条、堵一下……。 JDBC官方给出的答案是设置为“Integer.MIN_VALUE”具体原因不清楚但我猜是为了和游标查询区分开因为一会你会发现流式查询和游标查询唯一的区别就是FetchSize的大小。
注解式
Options(resultSetType ResultSetType.FORWARD_ONLY, fetchSize Integer.MIN_VALUE)
ResultType(ResultVo.class)
Select(SELECT *, 0 orderType FROM table\n WHERE username #{userName})
CursorResultVo 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, ResultHandlerResultVo handler);使用Mybatis的注解在 Options 中指定查询配置参数在ResultType中指定返回值类型 在 Select中指定查询语句。最后用Cursor接收返回值Cursor是可遍历的所以直接Foreach遍历即可或者返回void 用ResultHandler处理数据回调在调用方式时传入new ResultHandler并写明处理逻辑。
xml式
select idlistOrders resultTypecom.vo.ResultVo resultSetTypeFORWARD_ONLY fetchSizeInteger.MIN_VALUESELECT *, 1 stuffCount, 1 orderType FROM tableWHERE username #{userName}
/select需要注意的是不可以注解 xml混合使用比如注解指定fetchSizexml只写查询语句这种只有xml语句会生效要不全用注解要不全用xml
流式查询由于需要保持客户端与服务端的连接而一般查询提交完连接就会关闭因此我们需要保持事务开启否则会报“A Cursor is already closed.”即Cursor已经关闭没法再读取了。最简单的方法就是在方法上加Transactional在查询完毕以前事务会一直持有这个数据库连接但我们在使用完毕后也要自行关闭连接显式调用Cursor.close()或者用try with resource语句。
游标查询和流式查询的区分是fetchSize Integer.MIN_VALUE
Cursor 还提供了三个方法
isOpen()用于在取数据之前判断 Cursor 对象是否是打开状态。只有当打开时 Cursor 才能取数据isConsumed()用于判断查询结果是否全部取完getCurrentIndex()返回已经获取了多少条数据。
try(Cursor cursor OrderMapper.listOrders()) {cursor.forEach(rowObject - {// ...});
}OrderMapper.listOrders2(queryWrapper,resultContext - {ResultVo result resultContext.getResultObject();//这边循环调用就可以实现业务了}游标查询
和流式查询类似fetchSize不设置为MIN_VALUE即可 JDBC查询默认是不支持FetchSize属性的需要在JDBC连接URL后面加上**“useCursorFetchtrue”。**
useCursorFetchtrue 是针对 MySQL 数据库的 JDBC 连接参数用于启用服务器端游标获取数据。在 MyBatis 中当使用流式查询例如分页查询、结果集处理和使用游标等时这个配置可以帮助逐行从服务器检索数据而不是一次性将所有数据加载到内存中从而降低内存占用。
当使用 MySQL 数据库时在 JDBC 连接字符串中加入 useCursorFetchtrue并结合设置合适的 fetchSize可以避免因一次性加载过多数据导致的内存溢出问题。注意此配置仅对 MySQL 数据库有效。 如果不设置 useCursorFetchtrue 这个配置仅使用之前提到的那些配置如设置 defaultFetchSize、分页查询、结果集处理和使用游标等在大多数情况下这些配置仍然可以有效地避免查询导致的内存溢出。
但需要注意的是对于 MySQL 数据库如果不启用服务器端游标获取数据这可能会影响到流式查询的效果。因为在默认情况下MySQL JDBC 驱动会一次性将所有数据加载到内存中。此时即使使用了其他配置也可能无法达到预期的内存优化效果。
总的来说在使用 MySQL 数据库时推荐在 JDBC 连接字符串中加入 useCursorFetchtrue 配置以更好地支持流式查询和降低内存占用。在其他数据库中可以根据实际需求和场景选择合适的配置和策略来避免查询导致的内存溢出。
还要知道如何判断自己是否使用了流式查询或游标查询下面是几个数据集的对应关系
普通分页ResultsetRowsStaticRowDataStatic查询方式结果集类型行数据类型流式查询ResultsetRowsStreamingRowDataDynamic游标查询ResultsetRowsCursorRowDataCursor
这3种查询方式常规非大数据模式下普通查询最快其次是流式查询最次是游标查询.
主要是由于游标查询需要和数据库进行多次网络交互Client处理完这部分后再拉取下一部分数据因此会比较慢。但是流式查询又会长时间占用同一个数据库连接因此要取舍一下是能接受连接一直持有但是可能会堵住导致响应慢还是可能占用较多连接数但单次响应快。当通过流式查询获取一个ResultSet后在你通过next迭代出所有元素之前或者调用close关闭它之前你不能使用同一个数据库连接去发起另外一个查询否者抛出异常第一次调用的正常第二次的抛出异常。
