normal boot是什么选项,normal boot是啥意思

2022-07-17 19:31:11 基金研究

　　概述关系型数据库如果把百万级别数据直接查询存储到列表，会导致呜。使用框架自带的分页查询逐页查询效率（数据越多，效率越低)如何快速读取关系型数据库数据并且导入其他数据库创建项目　　

　　引入依赖？可扩展标记语言版本='1.0 '编码='UTF八号'？项目xmlns=' http://maven。阿帕奇。org/POM/4。0 .0 ' xmlns : xsi=' http://www。w3。org/2001/XML schema-instance ' xsi :架构位置=' http://maven。阿帕奇。org/POM/4。0 .0 http://maven.apache.org/xsd/maven-4.0.0.xsd'模型版本4 .0 .0/模型版本父groupIdorg.springframework.boot/groupId artifactId spring-boot-starter-parent/artifactId版本2.7 　　

ion>8.0.13</version> </dependency> </dependencies> <build> <finalName>big_table_query</finalName> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build></project>创建表CREATE TABLE `user_info` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(100) DEFAULT NULL, `password` varchar(100) DEFAULT NULL, `mobile` varchar(11) DEFAULT NULL, `age` int(11) DEFAULT NULL, `nickname` varchar(100) DEFAULT NULL, `sex` int(1) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=311558 DEFAULT CHARSET=utf8mb4;编写基础Mapperpackage com.itdl.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.baomidou.mybatisplus.core.toolkit.Constants;import org.apache.ibatis.annotations.Param;import java.util.List;public interface MyBaseMapper<T> extends BaseMapper<T> { String COUNT_METHOD_NAME = "customCount"; String PAGE_METHOD_NAME = "customPage"; /** * 通用获取分页总数方法 * @param param 查询参数条件 */ Integer customCount(@Param(Constants.ENTITY) P param); /** * 通用获取分页方法 * @param param 查询参数条件 */ List customPage(@Param("maxId") Long maxId, @Param("pageSize") Integer pageSize, @Param(Constants.ENTITY) P param);}

编写mapper接口

package com.itdl.mapper;import com.baomidou.mybatisplus.core.toolkit.Constants;import com.itdl.entity.UserInfo;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import java.util.List;/** * * Mapper 接口 * * * @author itdl * @since 2022-06-23 */@Mapperpublic interface UserInfoMapper extends MyBaseMapper<UserInfo> { @Select("select ifnull(count(*), 0) from user_info") @Override Integer myCustomCount(@Param(Constants.ENTITY) UserInfo param); @Select("select * from user_info where id > #{maxId} limit #{pageSize}") @Override List<UserInfo> myCustomPage(@Param("maxId") Long maxId, @Param("pageSize") Integer pageSize, @Param(Constants.ENTITY) UserInfo param);}编写分页工具类（重要）package com.itdl.utils;import com.itdl.common.enums.RespCode;import com.itdl.exception.BizException;import com.itdl.mapper.MyBaseMapper;import lombok.extern.slf4j.Slf4j;import org.apache.commons.lang3.StringUtils;import org.springframework.util.StopWatch;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.util.Arrays;import java.util.List;@Slf4jpublic class PageUtil { /** * * @param mapper 分页查询Mapper * @param param 分页查询和统计数量查询条件参数 * @param pageSize 分页大小，每次查询的数量 * @param callBack 分页查询回调函数，因为分页数据可能过多，全部放在list会导致程序内存所占用过多最终导致OOM */ public static <M extends MyBaseMapper<T>, T, P extends T> void pageQuery(M mapper, P param, Integer pageSize, CustomerPageCallBack callBack){ pageQuery(mapper, MyBaseMapper.COUNT_METHOD_NAME, MyBaseMapper.PAGE_METHOD_NAME, param, pageSize, callBack); } /** * * @param mapper 分页查询Mapper * @param countMethodName 自定义统计条数方法 * @param pageMethodName 自定义分页查询方法 * @param param 分页查询和统计数量查询条件参数 * @param pageSize 分页大小，每次查询的数量 * @param callBack 分页查询回调函数，因为分页数据可能过多，全部放在list会导致程序内存所占用过多最终导致OOM */ @SuppressWarnings("unchecked") public static <M extends MyBaseMapper<T>, T, P extends T> void pageQuery(M mapper, String countMethodName, String pageMethodName, P param, Integer pageSize, CustomerPageCallBack callBack){ Method method = Arrays.stream(mapper.getClass().getDeclaredMethods()).filter(s -> StringUtils.equals(countMethodName, s.getName())).findFirst().orElse(null); if (method == null){ throw new BizException(RespCode.REQUEST_PARAM_ERROR.getCode(), "分页查询【获取总记录数】的方法名：" + countMethodName + "不存在或参数错误: "); } Integer count = null; try { count = (Integer) method.invoke(mapper, param); } catch (IllegalAccessException | InvocationTargetException e) { throw new BizException(RespCode.REQUEST_PARAM_ERROR.getCode(), "分页查询【获取总记录数】的方法" + countMethodName + "执行出错: "+ e.getMessage()); } // 为0或null表示没有记录 if (count == null || count == 0){ log.error("====>>>总记录数为空，无需分页查询"); return; } Method pageMethod = Arrays.stream(mapper.getClass().getDeclaredMethods()).filter(s -> StringUtils.equals(pageMethodName, s.getName())).findFirst().orElse(null); if (pageMethod == null){ throw new BizException(RespCode.REQUEST_PARAM_ERROR.getCode(), "分页查询【获取分页数据】的方法名：" + countMethodName + "不存在或参数错误: "); } // 计算能分几页 int pages = count % pageSize == 0 ? (count / pageSize) : (count / pageSize) + 1; // 循环查询 Long maxId = 0L; StopWatch stopWatchTotal = new StopWatch(); stopWatchTotal.start(); for (int i = 0; i < pages; i++) { // 此时需要注意：获取数量和获取分页结果的条件必须一致，不然会导致maxId不对 StopWatch stopWatch = new StopWatch(); stopWatch.start(); // 获取查询结果 List resultList; try { resultList = (List) pageMethod.invoke(mapper, maxId, pageSize, param); } catch (IllegalAccessException | InvocationTargetException e) { throw new BizException(RespCode.REQUEST_PARAM_ERROR.getCode(), "分页查询【获取分页数据】的方法" + countMethodName + "执行出错: "+ e.getMessage()); } stopWatch.stop(); log.info("====>>>自定义分页查询成功：查询到{}条记录，总页数：{}，当前页：{}，耗时：{}s", resultList.size(), pages, i + 1, stopWatch.getTotalTimeSeconds()); // 执行回调函数 log.info("====>>>执行回调函数开始"); stopWatch.start(); // 获取maxId maxId = callBack.getMaxId(resultList); // 执行回调逻辑 callBack.handle(resultList); stopWatch.stop(); // 清空List 释放内存 resultList.clear(); log.info("====>>>执行回调函数结束, 耗时：{}s", stopWatch.getTotalTimeSeconds()); } stopWatchTotal.stop(); log.info("====>>>分页查询整体耗时：{}s", stopWatchTotal.getTotalTimeSeconds()); } /** * 自定义分页回调函数 * @param <T>回调实体泛型 */ public interface CustomerPageCallBack<T>{ /** * @Description 功能描述：获取本次查询的最大ID，用于下一次查询的开始位置 * @Param {@link List<T>} resultList 上一次查询结果列表 **/ Long getMaxId(List<T> resultList); /** * @Description 功能描述：对每一次的分页查询结果进行回调处理 * @Param {@link List<T>} resultList 结果列表 **/ void handle(List<T> resultList); }}编写Mybatis-plus配置类package com.itdl.config;import com.baomidou.mybatisplus.annotation.DbType;import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;import org.apache.ibatis.session.SqlSessionFactory;import org.mybatis.spring.annotation.MapperScan;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;@Configuration@MapperScan(basePackages = "com.itdl", sqlSessionFactoryRef = "sqlSessionFactory")public class MybatisPlusConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor(){ MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor(); mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return mybatisPlusInterceptor; } @Bean("sqlSessionFactory") public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception { //使用 mybatis plus 配置 MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean(); mybatisSqlSessionFactoryBean.setDataSource(dataSource); return mybatisSqlSessionFactoryBean.getObject(); }}编写控制器生成百万数据插入mysqlpackage com.itdl.controller;import com.itdl.entity.UserInfo;import com.itdl.mapper.UserInfoMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;@RestController@RequestMapping("/users")public class UserInfoController { @Autowired private UserInfoMapper userInfoMapper; @GetMapping("/generatorData") public String generatorData(){ for (int i = 0; i < 1000000; i++) { UserInfo userInfo = new UserInfo(); userInfo.setUsername("user0" + (i+1)); userInfo.setPassword("123456"); userInfo.setMobile("18080000000"); userInfo.setAge(20); userInfo.setSex(i % 2); userInfo.setNickname("张小" + (i + 1)); userInfoMapper.insert(userInfo); } return "success"; }}编写入口程序package com.itdl;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplicationpublic class BigTableQueryApp { public static void main(String<> args) { SpringApplication.run(BigTableQueryApp.class, args); }}调用接口生成数据

准备好一百万数据

限制内存直接查询寻一百万数据

普通查询 @GetMapping("/normalPageQuery") public String normalPageQuery(){ // 使用普通的分页查询进行查询 // 一百万条数据一次查询一万,循环一千次，然后将一万条数据存储到redis StopWatch stopWatch = new StopWatch(); stopWatch.start(); for (int i = 0; i < 1000; i++) { Page<UserInfo> page = new Page<>((i + 1), 1000); StopWatch subStopWatch = new StopWatch(); subStopWatch.start(); page = userInfoMapper.selectPage(page, new LambdaQueryWrapper<>()); List<UserInfo> records = page.getRecords(); handleList(records); subStopWatch.stop(); log.info("=======>>>>普通的分页查询第{}次查询耗时：{}s", (i + 1) ,subStopWatch.getTotalTimeSeconds()); } stopWatch.stop(); log.info("=======>>>>普通的分页查询总耗时：{}s", stopWatch.getTotalTimeSeconds()); return "success"; }页面直接五百

控制台打印堆溢出

执行普通分页，逐页查询处理(越到后面效率越是低下)

自定义分页查询（重点） @GetMapping("/customPageQuery") public String customPageQuery(){ PageUtil.pageQuery(userInfoMapper, new UserInfo(), 1000, new PageUtil.CustomerPageCallBack<>() { @Override public Long getMaxId(List<UserInfo> resultList) { return resultList.stream().mapToLong(UserInfo::getId).max().orElse(0L); } @Override public void handle(List<UserInfo> resultList) { handleList(resultList); } }); return "success"; } private void handleList(List<UserInfo> records) { log.info("======>>>>处理数据：{}条", records.size()); }查询效率基本没有改变