--- title: "02-主页数据显示讲解" created: 2025-12-15 aliases: - 主页数据显示讲解 tags: - 项目 --- # 主页数据显示讲解 ## **一、整体架构图** ![[首页列表查询架构-8294a913.jpg]] - **第一层（高速查询层）**：请求首先打入 **Elasticsearch**。 - 利用倒排索引解决多条件（地区+分类）查询问题。 - 数据为“宽表”结构，包含展示所需的所有字段（时间、价格范围），无需二次查询。 - **第二层（兜底保障层）**：如果 ES 挂了或无数据，回退到 **Database (MySQL)**。 - **核心痛点**：数据库按 ID 分片，按地区/分类查询会导致“全路由扫描（读扩散）”。 - **优化手段**：MyBatis 动态拼接 `UNION ALL` + Java 内存组装，最大限度减少 DB 交互次数。 ## **二、核心问题分析** ### **1. 为什么常规方案不行？** ![[为什么常规方案不行-3ba90c7d.jpg]] - **分库分表限制**：`d_program` 表按主键 ID 分片。查询条件却是 `areaId`（地区）和 `parentCategoryId`（父分类）。 - **读扩散（Read Diffusion）**：由于查询条件不是分片键，数据库中间件必须向**所有**分片发送 SQL 请求，然后汇总结果。随着分片增加，性能线性下降。 - **路由表失效**： - 即便是建立 `area_id -> program_id` 和 `category_id -> program_id` 的路由表。 - **问题**：两个路由表是独立的，无法在数据库层面高效求“交集”。在应用层把两个大集合拉出来求交集，内存和网络开销巨大。 ### **2. Elasticsearch 解决方案** [[01-Elasticsearch 学习笔记|Elasticsearch 学习笔记]] ![[_Elasticsearch_解决方案-00b60456.jpg]] - **倒排索引**：ES 天生适合多维度筛选。它将 `areaId` 和 `categoryId` 建立索引，查询时直接取交集，时间复杂度极低。 - **宽表设计**：为了避免“查出 ID 后再去 DB 查详情”，ES 中的 Document 包含了 `minPrice`、`showTime` 等聚合数据（在数据初始化/写入时预计算好）。 - **高可用策略**：ES 作为主查询源，MySQL 作为兜底，保证系统健壮性。 ## **三、代码执行流程** ### **1. 主入口逻辑** **入参实体** 使用了jakarta.validation的注解来做参数验证，区域id不能为空，父节目类型id集合不能为空并且长度最大为4 ```java @Data @ApiModel(value="ProgramListDto", description ="主页节目列表") public class ProgramListDto { @ApiModelProperty(name ="areaId", dataType ="Long", value ="所在区域id") private Long areaId; @ApiModelProperty(name ="parentProgramCategoryIds", dataType ="Long[]", value ="父节目类型id集合",required = true) @NotNull @Size(max = 4) private List parentProgramCategoryIds; } ``` **控制层** com.damai.controller.ProgramController#selectHomeList ```java @ApiOperation(value = "查询主页列表") @PostMapping(value = "/home/list") public ApiResponse> selectHomeList(@Valid @RequestBody ProgramListDto programPageListDto) { return ApiResponse.ok(programService.selectHomeList(programPageListDto)); } ``` **实现：** **核心策略：ES 优先，DB 兜底** ```java public List selectHomeList(ProgramListDto programPageListDto) { // ==================== 策略1：优先从ES查询 (高速路) ==================== // ES 中存放了组装好的宽表数据，查询速度快，无读扩散问题 List programHomeVoList = programEs.selectHomeList(programPageListDto); if (CollectionUtil.isNotEmpty(programHomeVoList)) { return programHomeVoList; // 命中缓存/索引，直接返回 } // ==================== 策略2：ES没数据，数据库兜底 (应急车道) ==================== // 当 ES 服务不可用或数据同步延迟时，执行复杂的 DB 查询逻辑 return dbSelectHomeList(programPageListDto); } ``` ### **2. ES 查询详解** **核心逻辑：循环分类查询 + 聚合结果** ```java public List selectHomeList(ProgramListDto programPageListDto) { List programHomeVoList = new ArrayList<>(); try { // ============ 循环每个父节目类型（最多4个）============ // 业务需求是每个楼层（分类）显示前7个，ES一次Query很难实现“分组取TopN” // 所以在应用层循环 4 次，对 ES 压力很小 for (Long parentProgramCategoryId : programPageListDto.getParentProgramCategoryIds()) { List programEsQueryDto = new ArrayList<>(); // ---------- 构建查询条件 ---------- if (Objects.nonNull(programPageListDto.getAreaId())) { // 情况A：查特定地区（如北京） EsDataQueryDto areaIdQueryDto = new EsDataQueryDto(); areaIdQueryDto.setParamName(ProgramDocumentParamName.AREA_ID); areaIdQueryDto.setParamValue(programPageListDto.getAreaId()); programEsQueryDto.add(areaIdQueryDto); } else { // 情况B：查全国 → 只查“主要节目”(Prime)，避免同一巡演显示多个城市 EsDataQueryDto primeQueryDto = new EsDataQueryDto(); primeQueryDto.setParamName(ProgramDocumentParamName.PRIME); primeQueryDto.setParamValue(BusinessStatus.YES.getCode()); programEsQueryDto.add(primeQueryDto); } // 父节目类型条件 EsDataQueryDto parentCategoryDto = new EsDataQueryDto(); parentCategoryDto.setParamName(ProgramDocumentParamName.PARENT_PROGRAM_CATEGORY_ID); parentCategoryDto.setParamValue(parentProgramCategoryId); programEsQueryDto.add(parentCategoryDto); // ---------- 执行查询（每个类型取前7条）---------- // 这里的 queryPage 底层调用 ES 的 Search API PageInfo pageInfo = businessEsHandle.queryPage( indexName, indexType, programEsQueryDto, 1, // 第1页 7, // 取7条 ProgramListVo.class ); // ---------- 组装结果 ---------- if (!pageInfo.getList().isEmpty()) { ProgramHomeVo programHomeVo = new ProgramHomeVo(); // 设置楼层名称（如“演唱会”） programHomeVo.setCategoryName(pageInfo.getList().get(0).getParentProgramCategoryName()); programHomeVo.setCategoryId(pageInfo.getList().get(0).getParentProgramCategoryId()); programHomeVo.setProgramListVoList(pageInfo.getList()); programHomeVoList.add(programHomeVo); } } } catch (Exception e) { // 关键点：ES查询异常不抛出，而是记录日志，让流程继续向下走 DB 兜底 log.error("businessEsHandle.queryPage error", e); } return programHomeVoList; } ``` ![[ES 查询详解-c5c754ad.jpg]] ### **3. ES 中的数据结构** **特点：空间换时间，宽表存储** ```json { "_source": { // ========== 基本信息 (来自 d_program) ========== "id": 6, "title": "「万朵」—春日玫瑰2024新专辑巡演", "itemPicture": "https://xxx/pkYEDAI.webp", // ========== 地区信息 (来自 d_area 或 关联查询) ========== "areaId": 2, "areaName": "北京", // ========== 分类信息 (来自 d_program_category) ========== "programCategoryId": 1, "parentProgramCategoryName": "演唱会", // ========== 预计算字段 (核心优化) ========== // 原本分散在 d_program_show_time 和 d_ticket_category 表中 // 在数据同步到 ES 前，已经计算好最小值、最大值和首场时间 "showTime": 1720182600000, "showWeekTime": "周五", "minPrice": 156, "maxPrice": 299, // ========== 业务标识 ========== "prime": 1 // 1=主节目（用于全国列表去重） } } ``` ![[ES_预组装数据的优势-081f76de.jpg]] ### **4. 数据库兜底查询详解** **核心逻辑：批量查询 + 内存组装 (解决 N+1 问题)** ![[数据库兜底查询详解-00885554.jpg]] ```java private List dbSelectHomeList(ProgramListDto programPageListDto){ List programHomeVoList = new ArrayList<>(); //根据父节目类型id来查询节目类型map，key：节目类型id，value：节目类型名 Map programCategoryMap = selectProgramCategoryMap(programPageListDto.getParentProgramCategoryIds()); //查询节目列表 List programList = programMapper.selectHomeList(programPageListDto); //如果节目列表为空，那么直接返回空map if (CollectionUtil.isEmpty(programList)) { return programHomeVoList; } //节目id集合 List programIdList = programList.stream().map(Program::getId).collect(Collectors.toList()); //根据节目id集合查询节目演出时间集合 LambdaQueryWrapper programShowTimeLambdaQueryWrapper = Wrappers.lambdaQuery(ProgramShowTime.class) .in(ProgramShowTime::getProgramId, programIdList); List programShowTimeList = programShowTimeMapper.selectList(programShowTimeLambdaQueryWrapper); //将节目演出集合根据节目id进行分组成map，key：节目id，value：节目演出时间集合 Map> programShowTimeMap = programShowTimeList.stream().collect(Collectors.groupingBy(ProgramShowTime::getProgramId)); //根据节目id统计出票档的最低价和最高价的集合map, key：节目id，value：票档 Map ticketCategorieMap = selectTicketCategorieMap(programIdList); //将节目结合按照父节目类型id进行分组成map，key：父节目类型id，value：节目集合 Map> programMap = programList.stream() .collect(Collectors.groupingBy(Program::getParentProgramCategoryId)); //循环此map for (Entry> programEntry : programMap.entrySet()) { //父节目类型id Long key = programEntry.getKey(); //节目集合 List value = programEntry.getValue(); List programListVoList = new ArrayList<>(); //循环节目集合 for (Program program : value) { ProgramListVo programListVo = new ProgramListVo(); BeanUtil.copyProperties(program,programListVo); //演出时间 programListVo.setShowTime(Optional.ofNullable(programShowTimeMap.get(program.getId())) .filter(list -> !list.isEmpty()) .map(list -> list.get(0)) .map(ProgramShowTime::getShowTime) .orElse(null)); //演出时间(精确到天) programListVo.setShowDayTime(Optional.ofNullable(programShowTimeMap.get(program.getId())) .filter(list -> !list.isEmpty()) .map(list -> list.get(0)) .map(ProgramShowTime::getShowDayTime) .orElse(null)); //演出时间所在的星期 programListVo.setShowWeekTime(Optional.ofNullable(programShowTimeMap.get(program.getId())) .filter(list -> !list.isEmpty()) .map(list -> list.get(0)) .map(ProgramShowTime::getShowWeekTime) .orElse(null)); //节目最高价 programListVo.setMaxPrice(Optional.ofNullable(ticketCategorieMap.get(program.getId())) .map(TicketCategoryAggregate::getMaxPrice).orElse(null)); //节目最低价 programListVo.setMinPrice(Optional.ofNullable(ticketCategorieMap.get(program.getId())) .map(TicketCategoryAggregate::getMinPrice).orElse(null)); programListVoList.add(programListVo); } ProgramHomeVo programHomeVo = new ProgramHomeVo(); //节目类型名 programHomeVo.setCategoryName(programCategoryMap.get(key)); //节目类型id programHomeVo.setCategoryId(key); programHomeVo.setProgramListVoList(programListVoList); //节目列表 programHomeVoList.add(programHomeVo); } return programHomeVoList; } ``` 从数据库中查询没有办法像从elasticsearch中直接查询的数据都是全的，programCategoryName、showTime、showDayTime、showWeekTime、minPrice、maxPrice这些仍然要从其他表中获取的，这些查询中都带有分片键，所以并不需要担心全路由的问题 查询节目类型名字 ```java //根据父节目类型id来查询节目类型map，key：节目类型id，value：节目类型名 Map programCategoryMap = selectProgramCategoryMap(programPageListDto.getParentProgramCategoryIds()); ``` ```java /** * 根据节目类型id集合查询节目类型名字 * @param programCategoryIdList 节目类型id集合 * @return Map key：节目类型id value：节目类型名字 * */ public Map selectProgramCategoryMap(Collection programCategoryIdList){ LambdaQueryWrapper pcLambdaQueryWrapper = Wrappers.lambdaQuery(ProgramCategory.class) .in(ProgramCategory::getId, programCategoryIdList); List programCategoryList = programCategoryMapper.selectList(pcLambdaQueryWrapper); return programCategoryList .stream() .collect(Collectors.toMap(ProgramCategory::getId, ProgramCategory::getName, (v1, v2) -> v2)); } ``` 根据传入的parentProgramCategoryIds父节目类型id集合，获取相关数据 查询节目列表 ```java List programList = programMapper.selectHomeList(programPageListDto); ``` 这个查询的要求是查询根据每个parentProgramCategoryId查询出节目列表，并且取前7条，要用sql实现的话，就要这么做，前端传入参数以此为例： ```json { "areaId":"2", "parentProgramCategoryIds":["1","2"] } ``` 形成的sql为 ```sql select dp.id,dp.area_id,dp.program_category_id,dp.parent_program_category_id,dp.title,dp.actor, dp.place,dp.item_picture from d_program dp where dp.program_status = 1 and dp.area_id = 2 and dp.parent_program_category_id = 1 limit 7 ) as tmp union all select * from ( select dp.id,dp.area_id,dp.program_category_id,dp.parent_program_category_id,dp.title,dp.actor, dp.place,dp.item_picture from d_program dp where dp.program_status = 1 and dp.area_id = 2 and dp.parent_program_category_id = 2 limit 7 ) as tmp ``` 每个根据父节目id的查询都是一个个独立的sql，然后靠union all 来拼接起来，要想在代码中实现这种效果，就需要使用Mybatis来动态拼接sql，自定义写我们的sql查询逻辑 com.damai.mapper.ProgramMapper#selectHomeList ```text /** * 主页查询 * @param programListDto 参数 * @return 结果 * */ List selectHomeList(@Param("programListDto")ProgramListDto programListDto); ``` 对应的xml文件 ```sql ``` 使用了foreach循环标签，来循环parentProgramCategoryIds，并使用标签的separator属性，来将union all进行分隔，这样就能查询出节目列表了 接下来就是接着去别的表中查询需要的数据 查询节目演出时间 ```java //节目id集合 List programIdList = programList.stream().map(Program::getId).collect(Collectors.toList()); //根据节目id集合查询节目演出时间集合 LambdaQueryWrapper programShowTimeLambdaQueryWrapper = Wrappers.lambdaQuery(ProgramShowTime.class) .in(ProgramShowTime::getProgramId, programIdList); List programShowTimeList = programShowTimeMapper.selectList(programShowTimeLambdaQueryWrapper); //将节目演出集合根据节目id进行分组成map，key：节目id，value：节目演出时间集合 Map> programShowTimeMap = programShowTimeList.stream().collect(Collectors.groupingBy(ProgramShowTime::getProgramId)); ``` 根据节目id统计出票档的最低价和最高价的集合 ```java //根据节目id统计出票档的最低价和最高价的集合map, key：节目id，value：票档 Map ticketCategorieMap = selectTicketCategorieMap(programIdList); ``` ```java public Map selectTicketCategorieMap(List programIdList){ //根据节目id统计出票档的最低价和最高价的集合 List ticketCategorieList = ticketCategoryMapper.selectAggregateList(programIdList); //将集合转换为map，key：节目id，value：票档 return ticketCategorieList .stream() .collect(Collectors.toMap(TicketCategoryAggregate::getProgramId, ticketCategory -> ticketCategory, (v1, v2) -> v2)); } ``` selectAggregateList也是自定义的sql，来统计出每个节目对应的票档最低价和最高价 ```sql ``` 当这些数据都查询出来后，接下来就是进行组装了 组装主页列表需要的数据 ```java //将节目结合按照父节目类型id进行分组成map，key：父节目类型id，value：节目集合 Map> programMap = programList.stream() .collect(Collectors.groupingBy(Program::getParentProgramCategoryId)); //循环此map for (Entry> programEntry : programMap.entrySet()) { //父节目类型id Long key = programEntry.getKey(); //节目集合 List value = programEntry.getValue(); List programListVoList = new ArrayList<>(); //循环节目集合 for (Program program : value) { ProgramListVo programListVo = new ProgramListVo(); BeanUtil.copyProperties(program,programListVo); //演出时间 programListVo.setShowTime(Optional.ofNullable(programShowTimeMap.get(program.getId())) .filter(list -> !list.isEmpty()) .map(list -> list.get(0)) .map(ProgramShowTime::getShowTime) .orElse(null)); //演出时间(精确到天) programListVo.setShowDayTime(Optional.ofNullable(programShowTimeMap.get(program.getId())) .filter(list -> !list.isEmpty()) .map(list -> list.get(0)) .map(ProgramShowTime::getShowDayTime) .orElse(null)); //演出时间所在的星期 programListVo.setShowWeekTime(Optional.ofNullable(programShowTimeMap.get(program.getId())) .filter(list -> !list.isEmpty()) .map(list -> list.get(0)) .map(ProgramShowTime::getShowWeekTime) .orElse(null)); //节目最高价 programListVo.setMaxPrice(Optional.ofNullable(ticketCategorieMap.get(program.getId())) .map(TicketCategoryAggregate::getMaxPrice).orElse(null)); //节目最低价 programListVo.setMinPrice(Optional.ofNullable(ticketCategorieMap.get(program.getId())) .map(TicketCategoryAggregate::getMinPrice).orElse(null)); programListVoList.add(programListVo); } ProgramHomeVo programHomeVo = new ProgramHomeVo(); //节目类型名 programHomeVo.setCategoryName(programCategoryMap.get(key)); programHomeVo.setProgramListVoList(programListVoList); //节目列表 programHomeVoList.add(programHomeVo); } return programHomeVoList; ``` 将节目列表按照父节目id进行分组，组装成programMap，接着循环此map，再循环map的value的集合 在循环中，将这些数据组装上去，组装后放在programHomeVoList中，泛型为ProgramHomeVo ProgramHomeVo结构 ```java @Data @ApiModel(value="ProgramHomeVo", description ="节目主页列表") public class ProgramHomeVo implements Serializable { private static final long serialVersionUID = 1L; @ApiModelProperty(name ="categoryName", dataType ="String", value ="类型名字") private String categoryName; @ApiModelProperty(name ="categoryId", dataType ="Long", value ="类型id") private Long categoryId; @ApiModelProperty(name ="programListVoList", dataType ="array", value ="节目列表") private List programListVoList; } ``` 可以看一下组装后给前端返回的数据： ```json { "code": "0", "message": "", "data": [ { "categoryName": "演唱会", "categoryId": "1", "programListVoList": [ { "id": "4", "title": "葡萄不愤怒乐队十周年远走高飞 2024巡演", "actor": "葡萄不愤怒乐队", "place": "MAO Livehouse北京", "itemPicture": "https://s21.ax1x.com/2024/06/06/pkYENcD.webp", "areaId": "2", "areaName": "北京", "programCategoryId": "1", "programCategoryName": "演唱会", "parentProgramCategoryId": "1", "parentProgramCategoryName": "演唱会", "showTime": "2024-07-20 19:00:00", "showDayTime": "2024-07-20 00:00:00", "showWeekTime": "周六", "minPrice": "199", "maxPrice": "299" }, { "id": "10", "title": "【乐队的春天】超燃乐队现场滚烫开燥｜蹦迪开火车｜室内音乐节式LIVE｜ 乐队迷大聚会！HOT乐队歌单", "actor": "", "place": "CLUB HEAVEN (天堂超市世茂工三店)", "itemPicture": "https://s21.ax1x.com/2024/06/06/pkYE5En.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "1", "programCategoryName": "演唱会", "parentProgramCategoryId": "1", "parentProgramCategoryName": "演唱会", "showTime": "2024-08-08 20:00:00", "showDayTime": "2024-08-08 00:00:00", "showWeekTime": "周四", "minPrice": "159", "maxPrice": "199" }, { "id": "14", "title": "2024【真的爱你】致敬BEYOND·黄家驹演唱会重现91殿堂级演唱会", "actor": "", "place": "MAO Livehouse北京", "itemPicture": "https://s21.ax1x.com/2024/06/07/pktiAc4.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "1", "programCategoryName": "演唱会", "parentProgramCategoryId": "1", "parentProgramCategoryName": "演唱会", "showTime": "2024-07-20 18:30:00", "showDayTime": "2024-07-20 00:00:00", "showWeekTime": "周六", "minPrice": "139", "maxPrice": "189" }, { "id": "22", "title": "暗杠「走歌人2024」巡演", "actor": "", "place": "WeShow Live", "itemPicture": "https://s21.ax1x.com/2024/04/30/pkF5T54.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "1", "programCategoryName": "演唱会", "parentProgramCategoryId": "1", "parentProgramCategoryName": "演唱会", "showTime": "2024-07-24 20:00:00", "showDayTime": "2024-07-24 00:00:00", "showWeekTime": "周三", "minPrice": "499", "maxPrice": "599" }, { "id": "36", "title": "张学友60+巡回演唱会", "actor": "张学友", "place": "国家体育馆", "itemPicture": "https://s21.ax1x.com/2024/06/07/pkYz139.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "1", "programCategoryName": "演唱会", "parentProgramCategoryId": "1", "parentProgramCategoryName": "演唱会", "showTime": "2024-07-22 20:30:00", "showDayTime": "2024-07-22 00:00:00", "showWeekTime": "周一", "minPrice": "480", "maxPrice": "2280" }, { "id": "2", "title": "韦礼安「一直都在」音乐会", "actor": "韦礼安", "place": "JDG英特尔电子竞技中心", "itemPicture": "https://s21.ax1x.com/2024/06/06/pkYEY9K.png", "areaId": "2", "areaName": "北京", "programCategoryId": "1", "programCategoryName": "演唱会", "parentProgramCategoryId": "1", "parentProgramCategoryName": "演唱会", "showTime": "2024-08-02 20:00:00", "showDayTime": "2024-08-02 00:00:00", "showWeekTime": "周五", "minPrice": "288", "maxPrice": "488" }, { "id": "6", "title": "「万朵」—春日玫瑰2024新专辑巡演", "actor": "春日玫瑰", "place": "疆进酒·OMNI SPACE", "itemPicture": "https://s21.ax1x.com/2024/06/06/pkYEDAI.webp", "areaId": "2", "areaName": "北京", "programCategoryId": "1", "programCategoryName": "演唱会", "parentProgramCategoryId": "1", "parentProgramCategoryName": "演唱会", "showTime": "2024-08-05 20:30:00", "showDayTime": "2024-08-05 00:00:00", "showWeekTime": "周一", "minPrice": "156", "maxPrice": "299" } ] }, { "categoryName": "话剧歌剧", "categoryId": "2", "programListVoList": [ { "id": "15", "title": "俄罗斯芭蕾国家剧院《天鹅湖》", "actor": "", "place": "天桥剧场", "itemPicture": "https://s21.ax1x.com/2024/03/20/pFWozc9.webp", "areaId": "2", "areaName": "北京", "programCategoryId": "2", "programCategoryName": "话剧歌剧", "parentProgramCategoryId": "2", "parentProgramCategoryName": "话剧歌剧", "showTime": "2024-07-16 20:30:00", "showDayTime": "2024-07-16 00:00:00", "showWeekTime": "周二", "minPrice": "375", "maxPrice": "415" }, { "id": "17", "title": "泱泱国风 民族经典 中国歌剧舞剧院鸿篇巨制舞剧《李白》", "actor": "", "place": "天桥艺术中心-大剧场 ", "itemPicture": "https://s21.ax1x.com/2024/03/20/pFWoXhF.webp", "areaId": "2", "areaName": "北京", "programCategoryId": "2", "programCategoryName": "话剧歌剧", "parentProgramCategoryId": "2", "parentProgramCategoryName": "话剧歌剧", "showTime": "2024-07-20 18:30:00", "showDayTime": "2024-07-20 00:00:00", "showWeekTime": "周六", "minPrice": "158", "maxPrice": "358" }, { "id": "5", "title": "话剧《苏堤春晓》田沁鑫导演", "actor": "辛柏青", "place": "国家话剧院-剧场", "itemPicture": "https://s21.ax1x.com/2024/03/20/pFW69mQ.webp", "areaId": "2", "areaName": "北京", "programCategoryId": "2", "programCategoryName": "话剧歌剧", "parentProgramCategoryId": "2", "parentProgramCategoryName": "话剧歌剧", "showTime": "2024-08-02 20:00:00", "showDayTime": "2024-08-02 00:00:00", "showWeekTime": "周五", "minPrice": "258", "maxPrice": "458" }, { "id": "7", "title": "话剧《夜行者》", "actor": "李乃文&唐旭", "place": "首都剧场", "itemPicture": "https://s21.ax1x.com/2024/03/20/pFWRugx.webp", "areaId": "2", "areaName": "北京", "programCategoryId": "2", "programCategoryName": "话剧歌剧", "parentProgramCategoryId": "2", "parentProgramCategoryName": "话剧歌剧", "showTime": "2024-07-20 19:00:00", "showDayTime": "2024-07-20 00:00:00", "showWeekTime": "周六", "minPrice": "288", "maxPrice": "388" }, { "id": "9", "title": "杨丽萍导演作品 新东方美学舞剧《平潭映象》", "actor": "", "place": "北京展览馆剧场", "itemPicture": "https://s21.ax1x.com/2024/03/20/pFWRIZF.webp", "areaId": "2", "areaName": "北京", "programCategoryId": "2", "programCategoryName": "话剧歌剧", "parentProgramCategoryId": "2", "parentProgramCategoryName": "话剧歌剧", "showTime": "2024-08-05 20:30:00", "showDayTime": "2024-08-05 00:00:00", "showWeekTime": "周一", "minPrice": "258", "maxPrice": "458" }, { "id": "11", "title": "【沉浸式互动戏剧】新年大戏｜侦探悬疑喜剧《完美犯罪》环境式爱笑—法庭剧场演出", "actor": "", "place": "雍和宫喜剧剧场", "itemPicture": "https://s21.ax1x.com/2024/03/20/pFWRjsK.webp", "areaId": "2", "areaName": "北京", "programCategoryId": "2", "programCategoryName": "话剧歌剧", "parentProgramCategoryId": "2", "parentProgramCategoryName": "话剧歌剧", "showTime": "2024-07-26 18:30:00", "showDayTime": "2024-07-26 00:00:00", "showWeekTime": "周五", "minPrice": "580", "maxPrice": "780" }, { "id": "13", "title": "法语原版音乐剧《唐璜》", "actor": "", "place": "天桥艺术中心-大剧场", "itemPicture": "https://s21.ax1x.com/2024/03/20/pFWWPJA.webp", "areaId": "2", "areaName": "北京", "programCategoryId": "2", "programCategoryName": "话剧歌剧", "parentProgramCategoryId": "2", "parentProgramCategoryName": "话剧歌剧", "showTime": "2024-08-08 20:00:00", "showDayTime": "2024-08-08 00:00:00", "showWeekTime": "周四", "minPrice": "225", "maxPrice": "325" } ] }, { "categoryName": "体育", "categoryId": "3", "programListVoList": [ { "id": "41", "title": "利雅得胜利中国行【C罗来啦】", "actor": "", "place": "体育场", "itemPicture": "https://s21.ax1x.com/2024/05/21/pkMug76.png", "areaId": "2", "areaName": "北京", "programCategoryId": "3", "programCategoryName": "体育", "parentProgramCategoryId": "3", "parentProgramCategoryName": "体育", "showTime": "2024-07-20 20:00:00", "showDayTime": "2024-07-20 00:00:00", "showWeekTime": "周六", "minPrice": "480", "maxPrice": "880" }, { "id": "45", "title": "世界摩托车越野锦标赛", "actor": "", "place": "国际越野赛车场 ", "itemPicture": "https://s21.ax1x.com/2024/06/12/pkUIgv8.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "3", "programCategoryName": "体育", "parentProgramCategoryId": "3", "parentProgramCategoryName": "体育", "showTime": "2024-08-06 13:00:00", "showDayTime": "2024-08-06 00:00:00", "showWeekTime": "周二", "minPrice": "1500", "maxPrice": "1500" }, { "id": "21", "title": "乒乓球大赛", "actor": "", "place": "海淀体育场", "itemPicture": "https://s21.ax1x.com/2024/07/07/pkWN3Yn.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "3", "programCategoryName": "体育", "parentProgramCategoryId": "3", "parentProgramCategoryName": "体育", "showTime": "2024-07-21 19:30:00", "showDayTime": "2024-07-21 00:00:00", "showWeekTime": "周日", "minPrice": "300", "maxPrice": "300" }, { "id": "33", "title": "羽毛球大赛", "actor": "", "place": "北京体育馆", "itemPicture": "https://s21.ax1x.com/2024/07/07/pkWtUIA.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "3", "programCategoryName": "体育", "parentProgramCategoryId": "3", "parentProgramCategoryName": "体育", "showTime": "2024-08-01 20:00:00", "showDayTime": "2024-08-01 00:00:00", "showWeekTime": "周四", "minPrice": "700", "maxPrice": "700" }, { "id": "35", "title": "世界斯诺克大师赛", "actor": "", "place": "北京体育馆", "itemPicture": "https://s21.ax1x.com/2024/07/06/pkWSCEq.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "3", "programCategoryName": "体育", "parentProgramCategoryId": "3", "parentProgramCategoryName": "体育", "showTime": "2024-07-11 20:00:00", "showDayTime": "2024-07-11 00:00:00", "showWeekTime": "周四", "minPrice": "900", "maxPrice": "900" }, { "id": "37", "title": "中国男子五人制足球超级赛", "actor": "", "place": "体育场", "itemPicture": "https://s21.ax1x.com/2024/05/21/pkMCoXF.png", "areaId": "2", "areaName": "北京", "programCategoryId": "3", "programCategoryName": "体育", "parentProgramCategoryId": "3", "parentProgramCategoryName": "体育", "showTime": "2024-07-15 20:00:00", "showDayTime": "2024-07-15 00:00:00", "showWeekTime": "周一", "minPrice": "258", "maxPrice": "458" }, { "id": "39", "title": "2024中超联赛北京国安主场赛事", "actor": "", "place": "工人体育场", "itemPicture": "https://s21.ax1x.com/2024/05/21/pkMPC0H.png", "areaId": "2", "areaName": "北京", "programCategoryId": "3", "programCategoryName": "体育", "parentProgramCategoryId": "3", "parentProgramCategoryName": "体育", "showTime": "2024-07-16 20:00:00", "showDayTime": "2024-07-16 00:00:00", "showWeekTime": "周二", "minPrice": "580", "maxPrice": "780" } ] }, { "categoryName": "儿童亲子", "categoryId": "4", "programListVoList": [ { "id": "23", "title": "大型全景多媒体舞台剧《冰雪女王Ⅰ艾莎之星梦奇缘》", "actor": "", "place": "北京展览馆剧场", "itemPicture": "https://s21.ax1x.com/2024/03/21/pFfrfII.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "4", "programCategoryName": "儿童亲子", "parentProgramCategoryId": "4", "parentProgramCategoryName": "儿童亲子", "showTime": "2024-07-27 20:00:00", "showDayTime": "2024-07-27 00:00:00", "showWeekTime": "周六", "minPrice": "410", "maxPrice": "410" }, { "id": "27", "title": "百老汇正版互动科学剧《物理秀》", "actor": "", "place": "北京展览馆剧场", "itemPicture": "https://s21.ax1x.com/2024/03/21/pFfrdaR.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "4", "programCategoryName": "儿童亲子", "parentProgramCategoryId": "4", "parentProgramCategoryName": "儿童亲子", "showTime": "2024-07-29 19:30:00", "showDayTime": "2024-07-29 00:00:00", "showWeekTime": "周一", "minPrice": "350", "maxPrice": "350" }, { "id": "31", "title": "儿童音乐舞台剧《神探艾小坡》", "actor": "", "place": "未来剧院", "itemPicture": "https://s21.ax1x.com/2024/03/22/pFfTpqI.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "4", "programCategoryName": "儿童亲子", "parentProgramCategoryId": "4", "parentProgramCategoryName": "儿童亲子", "showTime": "2024-08-01 20:00:00", "showDayTime": "2024-08-01 00:00:00", "showWeekTime": "周四", "minPrice": "258", "maxPrice": "258" }, { "id": "25", "title": "沉浸互动益智儿童剧神探爆梗岛之【小小福尔摩斯】全景互动|悬疑推理", "actor": "", "place": "雍和宫喜剧剧场", "itemPicture": "https://s21.ax1x.com/2024/03/21/pFfrsxO.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "4", "programCategoryName": "儿童亲子", "parentProgramCategoryId": "4", "parentProgramCategoryName": "儿童亲子", "showTime": "2024-07-28 19:00:00", "showDayTime": "2024-07-28 00:00:00", "showWeekTime": "周日", "minPrice": "430", "maxPrice": "430" }, { "id": "43", "title": "童艺出品沉浸式体验小剧场儿童剧《丑小鸭》", "actor": "", "place": "海淀剧场", "itemPicture": "https://s21.ax1x.com/2024/07/06/pkRzf3D.jpg", "areaId": "2", "areaName": "北京", "programCategoryId": "4", "programCategoryName": "儿童亲子", "parentProgramCategoryId": "4", "parentProgramCategoryName": "儿童亲子", "showTime": "2024-07-21 15:00:00", "showDayTime": "2024-07-21 00:00:00", "showWeekTime": "周日", "minPrice": "2000", "maxPrice": "2000" }, { "id": "19", "title": "东映动漫大型舞台剧《屁屁侦探之怪盗U对怪盗U》", "actor": "", "place": "北京展览馆剧场", "itemPicture": "https://s21.ax1x.com/2024/03/20/pFWTFAK.webp", "areaId": "2", "areaName": "北京", "programCategoryId": "4", "programCategoryName": "儿童亲子", "parentProgramCategoryId": "4", "parentProgramCategoryName": "儿童亲子", "showTime": "2024-07-20 19:30:00", "showDayTime": "2024-07-20 00:00:00", "showWeekTime": "周六", "minPrice": "358", "maxPrice": "358" }, { "id": "29", "title": "小猪佩奇舞台剧《完美的下雨天》", "actor": "", "place": "未来剧院", "itemPicture": "https://s21.ax1x.com/2024/03/22/pFfTPdP.webp", "areaId": "2", "areaName": "北京", "programCategoryId": "4", "programCategoryName": "儿童亲子", "parentProgramCategoryId": "4", "parentProgramCategoryName": "儿童亲子", "showTime": "2024-07-30 20:00:00", "showDayTime": "2024-07-30 00:00:00", "showWeekTime": "周二", "minPrice": "230", "maxPrice": "230" } ] } ] } ``` ### **5. MyBatis 动态 SQL 实现** **核心技术：Union All 实现分组 TopN** ```sql ``` ![[UNION_ALL_分组限制技巧-dcbfb54e.jpg]] ## **五、设计要点总结** ![[设计要点总结-65033233.jpg]] - **读写分离与分层架构**： - **ES** 负责复杂的检索和聚合，承担绝大部分读流量。 - **DB** 负责事务性的写操作和 ES 失效时的兜底读取。 - **解决 N+1 问题的典范**： - 在 DB 兜底逻辑中，没有在循环 `Program` 列表时去查 `ShowTime` 和 `Price`。 - 而是先收集所有 `ID`，通过 **内存（Java Map）**中进行匹配。这是高性能代码的标配写法。 - **SQL 技巧**： - 使用 `UNION ALL` + `LIMIT` 子查询，巧妙解决了“在一个 SQL 中查询多个分组的 Top N 数据”的难题，避免了多次数据库网络 IO。 - **防御性编程**： - `try-catch` 包裹 ES 查询逻辑，确保搜索引擎挂了不影响主业务。 - `Optional` 处理数据组装，防止因脏数据导致的空指针异常。 --- **企业级项目导航**：⬅️ [[01-Elasticsearch 学习笔记|01-Elasticsearch 学习笔记]] | 02-主页数据显示讲解 | ➡️ [[00-VibeCoding|00-VibeCoding]]