主页数据显示讲解
一、整体架构图
- 第一层（高速查询层）：请求首先打入 Elasticsearch。
- 利用倒排索引解决多条件（地区+分类）查询问题。
- 数据为“宽表”结构，包含展示所需的所有字段（时间、价格范围），无需二次查询。
- 第二层（兜底保障层）：如果 ES 挂了或无数据，回退到 Database (MySQL)。
- 核心痛点：数据库按 ID 分片，按地区/分类查询会导致“全路由扫描（读扩散）”。
- 优化手段：MyBatis 动态拼接
UNION ALL+ Java 内存组装，最大限度减少 DB 交互次数。
二、核心问题分析
1. 为什么常规方案不行？
- 分库分表限制：
d_program表按主键 ID 分片。查询条件却是areaId（地区）和parentCategoryId（父分类）。 - 读扩散（Read Diffusion）：由于查询条件不是分片键，数据库中间件必须向所有分片发送 SQL 请求，然后汇总结果。随着分片增加，性能线性下降。
- 路由表失效：
- 即便是建立
area_id -> program_id和category_id -> program_id的路由表。 - 问题：两个路由表是独立的，无法在数据库层面高效求“交集”。在应用层把两个大集合拉出来求交集，内存和网络开销巨大。
- 即便是建立
2. Elasticsearch 解决方案
- 倒排索引：ES 天生适合多维度筛选。它将
areaId和categoryId建立索引，查询时直接取交集，时间复杂度极低。 - 宽表设计：为了避免“查出 ID 后再去 DB 查详情”，ES 中的 Document 包含了
minPrice、showTime等聚合数据（在数据初始化/写入时预计算好）。 - 高可用策略：ES 作为主查询源，MySQL 作为兜底，保证系统健壮性。
三、代码执行流程
1. 主入口逻辑
入参实体
使用了jakarta.validation的注解来做参数验证，区域id不能为空，父节目类型id集合不能为空并且长度最大为4
@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<Long> parentProgramCategoryIds;
}
控制层 com.damai.controller.ProgramController#selectHomeList
@ApiOperation(value = "查询主页列表")
@PostMapping(value = "/home/list")
public ApiResponse<List<ProgramHomeVo>> selectHomeList(@Valid @RequestBody ProgramListDto programPageListDto) {
return ApiResponse.ok(programService.selectHomeList(programPageListDto));
}
实现：
核心策略：ES 优先，DB 兜底
public List<ProgramHomeVo> selectHomeList(ProgramListDto programPageListDto) {
// ==================== 策略1：优先从ES查询 (高速路) ====================
// ES 中存放了组装好的宽表数据，查询速度快，无读扩散问题
List<ProgramHomeVo> programHomeVoList = programEs.selectHomeList(programPageListDto);
if (CollectionUtil.isNotEmpty(programHomeVoList)) {
return programHomeVoList; // 命中缓存/索引，直接返回
}
// ==================== 策略2：ES没数据，数据库兜底 (应急车道) ====================
// 当 ES 服务不可用或数据同步延迟时，执行复杂的 DB 查询逻辑
return dbSelectHomeList(programPageListDto);
}
2. ES 查询详解
核心逻辑：循环分类查询 + 聚合结果
public List<ProgramHomeVo> selectHomeList(ProgramListDto programPageListDto) {
List<ProgramHomeVo> programHomeVoList = new ArrayList<>();
try {
// ============ 循环每个父节目类型（最多4个）============
// 业务需求是每个楼层（分类）显示前7个，ES一次Query很难实现“分组取TopN”
// 所以在应用层循环 4 次，对 ES 压力很小
for (Long parentProgramCategoryId : programPageListDto.getParentProgramCategoryIds()) {
List<EsDataQueryDto> 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<ProgramListVo> 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;
}
3. ES 中的数据结构
特点：空间换时间，宽表存储
{
"_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=主节目（用于全国列表去重）
}
}
4. 数据库兜底查询详解
核心逻辑：批量查询 + 内存组装 (解决 N+1 问题)
private List<ProgramHomeVo> dbSelectHomeList(ProgramListDto programPageListDto){
List<ProgramHomeVo> programHomeVoList = new ArrayList<>();
//根据父节目类型id来查询节目类型map，key：节目类型id，value：节目类型名
Map<Long, String> programCategoryMap = selectProgramCategoryMap(programPageListDto.getParentProgramCategoryIds());
//查询节目列表
List<Program> programList = programMapper.selectHomeList(programPageListDto);
//如果节目列表为空，那么直接返回空map
if (CollectionUtil.isEmpty(programList)) {
return programHomeVoList;
}
//节目id集合
List<Long> programIdList = programList.stream().map(Program::getId).collect(Collectors.toList());
//根据节目id集合查询节目演出时间集合
LambdaQueryWrapper<ProgramShowTime> programShowTimeLambdaQueryWrapper = Wrappers.lambdaQuery(ProgramShowTime.class)
.in(ProgramShowTime::getProgramId, programIdList);
List<ProgramShowTime> programShowTimeList = programShowTimeMapper.selectList(programShowTimeLambdaQueryWrapper);
//将节目演出集合根据节目id进行分组成map，key：节目id，value：节目演出时间集合
Map<Long, List<ProgramShowTime>> programShowTimeMap =
programShowTimeList.stream().collect(Collectors.groupingBy(ProgramShowTime::getProgramId));
//根据节目id统计出票档的最低价和最高价的集合map, key：节目id，value：票档
Map<Long, TicketCategoryAggregate> ticketCategorieMap = selectTicketCategorieMap(programIdList);
//将节目结合按照父节目类型id进行分组成map，key：父节目类型id，value：节目集合
Map<Long, List<Program>> programMap = programList.stream()
.collect(Collectors.groupingBy(Program::getParentProgramCategoryId));
//循环此map
for (Entry<Long, List<Program>> programEntry : programMap.entrySet()) {
//父节目类型id
Long key = programEntry.getKey();
//节目集合
List<Program> value = programEntry.getValue();
List<ProgramListVo> 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这些仍然要从其他表中获取的，这些查询中都带有分片键，所以并不需要担心全路由的问题
查询节目类型名字
//根据父节目类型id来查询节目类型map，key：节目类型id，value：节目类型名
Map<Long, String> programCategoryMap = selectProgramCategoryMap(programPageListDto.getParentProgramCategoryIds());
/**
* 根据节目类型id集合查询节目类型名字
* @param programCategoryIdList 节目类型id集合
* @return Map<Long, String> key：节目类型id value：节目类型名字
* */
public Map<Long, String> selectProgramCategoryMap(Collection<Long> programCategoryIdList){
LambdaQueryWrapper<ProgramCategory> pcLambdaQueryWrapper = Wrappers.lambdaQuery(ProgramCategory.class)
.in(ProgramCategory::getId, programCategoryIdList);
List<ProgramCategory> programCategoryList = programCategoryMapper.selectList(pcLambdaQueryWrapper);
return programCategoryList
.stream()
.collect(Collectors.toMap(ProgramCategory::getId, ProgramCategory::getName, (v1, v2) -> v2));
}
根据传入的parentProgramCategoryIds父节目类型id集合，获取相关数据
查询节目列表
List<Program> programList = programMapper.selectHomeList(programPageListDto);
这个查询的要求是查询根据每个parentProgramCategoryId查询出节目列表，并且取前7条，要用sql实现的话，就要这么做，前端传入参数以此为例：
{
"areaId":"2",
"parentProgramCategoryIds":["1","2"]
}
形成的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
/**
* 主页查询
* @param programListDto 参数
* @return 结果
* */
List<Program> selectHomeList(@Param("programListDto")ProgramListDto programListDto);
对应的xml文件
<select id="selectHomeList" parameterType="com.damai.dto.ProgramListDto" resultType="com.damai.entity.Program">
<if test = 'programListDto.parentProgramCategoryIds != null and programListDto.parentProgramCategoryIds.size>0'>
<foreach collection='programListDto.parentProgramCategoryIds' item='parentProgramCategoryId' index='index' separator=' 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
<if test ='programListDto.areaId == null'>
and dp.prime = 1
</if>
<if test ='programListDto.areaId != null'>
and dp.area_id = #{programListDto.areaId,jdbcType=BIGINT}
</if>
and
dp.parent_program_category_id = #{parentProgramCategoryId,jdbcType=BIGINT} limit 7
) as tmp
</foreach>
</if>
</select>
使用了foreach循环标签，来循环parentProgramCategoryIds，并使用标签的separator属性，来将union all进行分隔，这样就能查询出节目列表了
接下来就是接着去别的表中查询需要的数据
查询节目演出时间
//节目id集合
List<Long> programIdList = programList.stream().map(Program::getId).collect(Collectors.toList());
//根据节目id集合查询节目演出时间集合
LambdaQueryWrapper<ProgramShowTime> programShowTimeLambdaQueryWrapper = Wrappers.lambdaQuery(ProgramShowTime.class)
.in(ProgramShowTime::getProgramId, programIdList);
List<ProgramShowTime> programShowTimeList = programShowTimeMapper.selectList(programShowTimeLambdaQueryWrapper);
//将节目演出集合根据节目id进行分组成map，key：节目id，value：节目演出时间集合
Map<Long, List<ProgramShowTime>> programShowTimeMap =
programShowTimeList.stream().collect(Collectors.groupingBy(ProgramShowTime::getProgramId));
根据节目id统计出票档的最低价和最高价的集合
//根据节目id统计出票档的最低价和最高价的集合map, key：节目id，value：票档
Map<Long, TicketCategoryAggregate> ticketCategorieMap = selectTicketCategorieMap(programIdList);
public Map<Long, TicketCategoryAggregate> selectTicketCategorieMap(List<Long> programIdList){
//根据节目id统计出票档的最低价和最高价的集合
List<TicketCategoryAggregate> ticketCategorieList = ticketCategoryMapper.selectAggregateList(programIdList);
//将集合转换为map，key：节目id，value：票档
return ticketCategorieList
.stream()
.collect(Collectors.toMap(TicketCategoryAggregate::getProgramId, ticketCategory -> ticketCategory, (v1, v2) -> v2));
}
selectAggregateList也是自定义的sql，来统计出每个节目对应的票档最低价和最高价
<select id="selectAggregateList" resultType="com.damai.entity.TicketCategoryAggregate">
select
program_id,min(price) as min_price,max(price) as max_price
from d_ticket_category
where program_id in
<foreach collection='programIdList' item='programId' index='index' open='(' close=')' separator=','>
#{programId,jdbcType=BIGINT}
</foreach>
group by program_id
</select>
当这些数据都查询出来后，接下来就是进行组装了
组装主页列表需要的数据
//将节目结合按照父节目类型id进行分组成map，key：父节目类型id，value：节目集合
Map<Long, List<Program>> programMap = programList.stream()
.collect(Collectors.groupingBy(Program::getParentProgramCategoryId));
//循环此map
for (Entry<Long, List<Program>> programEntry : programMap.entrySet()) {
//父节目类型id
Long key = programEntry.getKey();
//节目集合
List<Program> value = programEntry.getValue();
List<ProgramListVo> 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结构
@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<ProgramListVo> programListVoList;
}
可以看一下组装后给前端返回的数据：
{
"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
<select id="selectHomeList" resultType="com.damai.entity.Program">
<!-- 循环父节目类型ID集合 -->
<foreach collection='programListDto.parentProgramCategoryIds'
item='parentProgramCategoryId'
separator=' 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
<!-- 地区条件 -->
<if test='programListDto.areaId == null'>
and dp.prime = 1 <!-- 全部地区只查主节目 -->
</if>
<if test='programListDto.areaId != null'>
and dp.area_id = #{programListDto.areaId}
</if>
and dp.parent_program_category_id = #{parentProgramCategoryId}
limit 7
) as tmp
</foreach>
</select>
五、设计要点总结
- 读写分离与分层架构：
- ES 负责复杂的检索和聚合，承担绝大部分读流量。
- DB 负责事务性的写操作和 ES 失效时的兜底读取。
- 解决 N+1 问题的典范：
- 在 DB 兜底逻辑中，没有在循环
Program列表时去查ShowTime和Price。 - 而是先收集所有
ID，通过 **内存（Java Map）**中进行匹配。这是高性能代码的标配写法。
- 在 DB 兜底逻辑中，没有在循环
- SQL 技巧：
- 使用
UNION ALL+LIMIT子查询，巧妙解决了“在一个 SQL 中查询多个分组的 Top N 数据”的难题，避免了多次数据库网络 IO。
- 使用
- 防御性编程：
try-catch包裹 ES 查询逻辑，确保搜索引擎挂了不影响主业务。Optional处理数据组装，防止因脏数据导致的空指针异常。
企业级项目导航：⬅️ 01-Elasticsearch 学习笔记 | 02-主页数据显示讲解 | ➡️ 00-VibeCoding
💬 评论