From a4fe077a231ed2ae0094af0fc6fa0735b764c862 Mon Sep 17 00:00:00 2001
From: 疯狂的狮子Li <15040126243@163.com>
Date: 星期五, 28 六月 2024 12:49:58 +0800
Subject: [PATCH] update 优化 大数据量下join卡顿问题 使用子查询提高性能
---
ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysMenuMapper.xml | 33 +++++++++------------------------
ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysRoleMapper.xml | 4 +---
ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/mapper/SysMenuMapper.java | 7 -------
ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysMenuServiceImpl.java | 2 +-
ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysRoleServiceImpl.java | 4 ++--
5 files changed, 13 insertions(+), 37 deletions(-)
diff --git a/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/mapper/SysMenuMapper.java b/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/mapper/SysMenuMapper.java
index b2be0e9..ac646c0 100644
--- a/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/mapper/SysMenuMapper.java
+++ b/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/mapper/SysMenuMapper.java
@@ -19,13 +19,6 @@
public interface SysMenuMapper extends BaseMapperPlus<SysMenu, SysMenuVo> {
/**
- * 鏍规嵁鐢ㄦ埛鎵�鏈夋潈闄�
- *
- * @return 鏉冮檺鍒楄〃
- */
- List<String> selectMenuPerms();
-
- /**
* 鏍规嵁鐢ㄦ埛鏌ヨ绯荤粺鑿滃崟鍒楄〃
*
* @param queryWrapper 鏌ヨ鏉′欢
diff --git a/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysMenuServiceImpl.java b/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysMenuServiceImpl.java
index 6e1765b..dd56ef6 100644
--- a/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysMenuServiceImpl.java
+++ b/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysMenuServiceImpl.java
@@ -75,7 +75,7 @@
.orderByAsc(SysMenu::getOrderNum));
} else {
QueryWrapper<SysMenu> wrapper = Wrappers.query();
- wrapper.eq("sur.user_id", userId)
+ wrapper.inSql("r.role_id", "select role_id from sys_user_role where user_id = " + userId)
.like(StringUtils.isNotBlank(menu.getMenuName()), "m.menu_name", menu.getMenuName())
.eq(StringUtils.isNotBlank(menu.getVisible()), "m.visible", menu.getVisible())
.eq(StringUtils.isNotBlank(menu.getStatus()), "m.status", menu.getStatus())
diff --git a/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysRoleServiceImpl.java b/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysRoleServiceImpl.java
index 6c89082..01fb564 100644
--- a/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysRoleServiceImpl.java
+++ b/ruoyi-modules/ruoyi-system/src/main/java/org/dromara/system/service/impl/SysRoleServiceImpl.java
@@ -103,7 +103,7 @@
*/
@Override
public List<SysRoleVo> selectRolesAuthByUserId(Long userId) {
- List<SysRoleVo> userRoles = baseMapper.selectRolePermissionByUserId(userId);
+ List<SysRoleVo> userRoles = baseMapper.selectRolesByUserId(userId);
List<SysRoleVo> roles = selectRoleAll();
// 浣跨敤HashSet鎻愰珮鏌ユ壘鏁堢巼
Set<Long> userRoleIds = userRoles.stream().map(SysRoleVo::getRoleId).collect(Collectors.toSet());
@@ -123,7 +123,7 @@
*/
@Override
public Set<String> selectRolePermissionByUserId(Long userId) {
- List<SysRoleVo> perms = baseMapper.selectRolePermissionByUserId(userId);
+ List<SysRoleVo> perms = baseMapper.selectRolesByUserId(userId);
Set<String> permsSet = new HashSet<>();
for (SysRoleVo perm : perms) {
if (ObjectUtil.isNotNull(perm)) {
diff --git a/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysMenuMapper.xml b/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysMenuMapper.xml
index fad1812..a9d4886 100644
--- a/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysMenuMapper.xml
+++ b/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysMenuMapper.xml
@@ -11,9 +11,8 @@
select distinct m.menu_id, m.parent_id, m.menu_name, m.path, m.component, m.query_param, m.visible, m.status,
m.perms, m.is_frame, m.is_cache, m.menu_type, m.icon, m.order_num, m.create_time
from sys_menu m
- left join sys_role_menu rm on m.menu_id = rm.menu_id
- left join sys_user_role sur on rm.role_id = sur.role_id
- left join sys_role ro on sur.role_id = ro.role_id
+ left join sys_role_menu rm on m.menu_id = rm.menu_id
+ left join sys_role r on sur.role_id = r.role_id
${ew.getCustomSqlSegment}
</select>
@@ -34,14 +33,10 @@
m.order_num,
m.create_time
from sys_menu m
- left join sys_role_menu rm on m.menu_id = rm.menu_id
- left join sys_user_role sur on rm.role_id = sur.role_id
- left join sys_role ro on sur.role_id = ro.role_id
- left join sys_user u on sur.user_id = u.user_id
- where u.user_id = #{userId}
- and m.menu_type in ('M', 'C')
- and m.status = '0'
- and ro.status = '0'
+ left join sys_role_menu rm on m.menu_id = rm.menu_id and m.status = '0'
+ left join sys_role r on rm.role_id = r.role_id and r.status = '0'
+ where m.menu_type in ('M', 'C')
+ and r.role_id in (select role_id from sys_user_role where user_id = #{userId})
order by m.parent_id, m.order_num
</select>
@@ -57,22 +52,12 @@
order by m.parent_id, m.order_num
</select>
- <select id="selectMenuPerms" resultType="String">
- select distinct m.perms
- from sys_menu m
- left join sys_role_menu rm on m.menu_id = rm.menu_id
- left join sys_user_role sur on rm.role_id = sur.role_id
- </select>
-
<select id="selectMenuPermsByUserId" parameterType="Long" resultType="String">
select distinct m.perms
from sys_menu m
- left join sys_role_menu rm on m.menu_id = rm.menu_id
- left join sys_user_role sur on rm.role_id = sur.role_id
- left join sys_role r on r.role_id = sur.role_id
- where m.status = '0'
- and r.status = '0'
- and sur.user_id = #{userId}
+ left join sys_role_menu rm on m.menu_id = rm.menu_id and m.status = '0'
+ left join sys_role r on r.role_id = rm.role_id and r.status = '0'
+ where r.role_id in (select role_id from sys_user_role where user_id = #{userId})
</select>
<select id="selectMenuPermsByRoleId" parameterType="Long" resultType="String">
diff --git a/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysRoleMapper.xml b/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysRoleMapper.xml
index 7b8bba7..4ef7b1e 100644
--- a/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysRoleMapper.xml
+++ b/ruoyi-modules/ruoyi-system/src/main/resources/mapper/system/SysRoleMapper.xml
@@ -48,9 +48,7 @@
r.data_scope,
r.status
from sys_role r
- left join sys_user_role sur on sur.role_id = r.role_id
- left join sys_user u on u.user_id = sur.user_id
- WHERE r.del_flag = '0' and sur.user_id = #{userId}
+ WHERE r.del_flag = '0' and r.role_id in (select role_id from sys_user_role where user_id = #{userId})
</select>
<select id="selectRoleById" resultMap="SysRoleResult">
--
Gitblit v1.9.3