文章目录

• • 需求
  • 需求分析
  • 分页问题说明
  • 分页问题原因
  • 一对多场景一
  • 一对多场景二
  • 性能优化
  • • 优化解决方案

需求

查询用户信息列表，其中包含用户对应角色信息，页面检索条件有根据角色名称查询用户列表；

需求分析

一个用户对应多个角色，用户信息和角色信息分表根据用户id关联存储，用户和角色一对多进行表连接查询，

创建对应表：

CREATE TABLE `sys_user` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',`name` varchar(50) DEFAULT NULL COMMENT '姓名',`age` int DEFAULT NULL COMMENT '年龄',PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4  COMMENT='用户信息表';CREATE TABLE `sys_role` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '角色ID',`role_name` varchar(30) NOT NULL COMMENT '角色名称',PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4  COMMENT='角色信息表';CREATE TABLE `sys_user_role` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',`user_id` bigint NOT NULL COMMENT '用户ID',`role_id` bigint NOT NULL COMMENT '角色ID',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  COMMENT='用户和角色关联表';INSERT INTO tsq.sys_user (name,age) VALUES('张三',18),('王二',19);INSERT INTO tsq.sys_role (role_name) VALUES('角色1'),('角色2'),('角色3'),('角色4');INSERT INTO tsq.sys_user_role (user_id,role_id) VALUES(1,1),(1,2),(1,3),(2,4);

对应实体类：

@Data
@ApiModel("用户信息表")
@TableName("sys_user")
public class User implements Serializable {private static final long serialVersionUID = 1L;@ApiModelProperty("用户id")private Long id;@ApiModelProperty("姓名")private String name;@ApiModelProperty("年龄")private Integer age;
}@Data
@ApiModel("角色信息表")
@TableName("sys_role")
public class Role implements Serializable {private static final long serialVersionUID = 1L;@ApiModelProperty("角色id")private Long id;@ApiModelProperty("角色名称")private String roleName;
}@Data
@ApiModel("用户信息表")
public class UserVo implements Serializable {private static final long serialVersionUID = 1L;@ApiModelProperty("用户id")private Long id;@ApiModelProperty("姓名")private String name;@ApiModelProperty("年龄")private Integer age;private List roleList;}

分页问题说明

在使用一对多连接查询并且分页时，发现返回的分页列表数据数量不对
比如这里查询用户对应角色列表，如果使用直接映射，那么 roleList 的每个 Role 对象都会算一条数据；比如查第一页，一个用户有三个角色每页三条数据，就会出现查出一个 User ，三个 Role 的这些情况，这它也算每页三条（其实就只查到一个用户）

分页问题原因

mybatis-plus一对多分页时，应该使用子查询的映射方式，使用直接映射就会出错
所以直接映射适用于一对一，子查询映射使用于一对多；

一对多场景一

查询用户表的内容，角色表不参与条件查询，用懒加载形式

// controller@GetMapping("/pageList")public Map pageList(@RequestParam(required = false, defaultValue = "0") int offset,@RequestParam(required = false, defaultValue = "10") int pagesize) {return userService.pageList(offset, pagesize);}// serviceimpl@Overridepublic Map pageList(int offset, int pagesize) {List pageList = userMapper.pageList(offset, pagesize);int totalCount = userMapper.pageListCount();Map result = new HashMap();result.put("pageList", pageList);result.put("totalCount", totalCount);return result;}// mapper.xmlSELECT *FROM sys_user_role urinner join sys_role r on ur.role_id = r.idwhere ur.user_id = #{userId}SELECT *FROM sys_userLIMIT #{offset}, #{pageSize}SELECT count(1)FROM sys_user

查询结果

一对多场景二

查询用户表的内容，角色表要作为查询条件参与查询，例如要根据角色名称查询出用户列表

// controller@GetMapping("/pageListByRoleName")public Map pageListByRoleName(@RequestParam(required = false, defaultValue = "0") int offset,@RequestParam(required = false, defaultValue = "10") int pagesize,@RequestParam String roleName) {return userService.pageListByRoleName(offset, pagesize, roleName);}// serviceimpl@Overridepublic Map pageListByRoleName(int offset, int pagesize,String roleName) {List pageList = userMapper.pageListByRoleName(offset, pagesize, roleName);int totalCount = userMapper.pageListCount();Map result = new HashMap();result.put("pageList", pageList);result.put("totalCount", totalCount);return result;}// mapper.xmlSELECT *FROM sys_user_role urinner join sys_role r on ur.role_id = r.idwhere ur.user_id = #{userId}and r.role_name LIKE concat('%', #{roleName}, '%')SELECT temp.* FROM (SELECT distinct u.*,#{roleName} as roleNameFROM sys_user uleft join sys_user_role ur on u.id = ur.user_idleft join sys_role r on r.id = ur.role_idr.role_name LIKE concat('%', #{roleName}, '%')) tempLIMIT #{offset}, #{pageSize}

查询结果

性能优化

原因：

场景一二中使用 select方式会触发多次子查询（SELECT *FROM sys_user_role ur inner join sys_role …），当数据量大时会使查询速度很慢。

场景二中查询时产生的sql日志如下：

--  ==>  
SELECTtemp.* 
FROM( SELECTdistinct u.*,'角色' as roleName FROMsys_user u left joinsys_user_role ur on u.id = ur.user_id left joinsys_role r on r.id = ur.role_id WHEREr.role_name LIKE concat('%', '角色', '%') ) temp LIMIT 0,10 --  ====>  
SELECT* 
FROMsys_user_role ur 
inner joinsys_role r on ur.role_id = r.id 
whereur.user_id = 1 and r.role_name LIKE concat('%', '角色', '%') --  ====>  
SELECT* 
FROMsys_user_role ur 
inner joinsys_role r on ur.role_id = r.id 
whereur.user_id = 2 and r.role_name LIKE concat('%', '角色', '%') --  ==>  
SELECTcount(1) 
FROMsys_user 

sql可见如果有100各用户就要执行一百次子查询，效率极低。

优化解决方案

sql中只查询sys_user相关信息并且做roleName 过滤，roleList在java代码中用stream关联role并赋值roleList；

// serviceimpl@Overridepublic Map pageListByRoleName(int offset, int pagesize,String roleName) {// List pageList = userMapper.pageListByRoleName(offset, pagesize, roleName);List pageList = userMapper.pageListByRoleName2(offset, pagesize, roleName);List userIds = pageList.stream().map(UserVo::getId).collect(Collectors.toList());List userRoleVos =  userMapper.getUserRoleByUserIds(userIds);Map> userRoleMap = userRoleVos.stream().collect(Collectors.groupingBy(UserRoleVo::getUserId, Collectors.toList()));pageList.forEach(u -> {List roleVos = userRoleMap.get(u.getId());List roles = BeanUtils.listCopy(roleVos, CopyOptions.create(), RoleVo.class);u.setRoleList(roles);});int totalCount = userMapper.pageListCount();Map result = new HashMap();result.put("pageList", pageList);result.put("totalCount", totalCount);return result;}// mapper.xml
SELECT distinct u.*FROM sys_user uleft join sys_user_role ur on u.id = ur.user_idleft join sys_role r on r.id = ur.role_idr.role_name LIKE concat('%', #{roleName}, '%')LIMIT #{offset}, #{pageSize}
  

查询结果

同场景二。

查询时产生的sql如下：

  --  ==>  SELECTdistinct u.* FROMsys_user u left joinsys_user_role ur on u.id = ur.user_id left joinsys_role r on r.id = ur.role_id WHEREr.role_name LIKE concat('%', '角色', '%') LIMIT 0, 10 --  ==>  SELECTur.user_id ,r.id roleId,r.role_name FROMsys_user_role ur inner joinsys_role r on ur.role_id = r.id --  ==>  SELECTcount(1) FROMsys_user 

由sql日志可见这种方式比纯sql方式效率高一些