WSO2 Identity Server 5.11.0密码授权查询慢、CPU过高求助

针对你升级后遇到的密码授权流程中MSSQL查询性能问题，结合大表数据量的情况，给出以下实用优化方案：

1. 优化UM_USER_ATTRIBUTE表的索引 #

当前查询的核心是从4500万条数据的UM_USER_ATTRIBUTE中快速筛选出符合条件的记录，现有索引大概率无法覆盖所有查询条件，导致全表扫描或低效索引扫描。建议创建复合覆盖索引：

CREATE NONCLUSTERED INDEX IX_UM_USER_ATTRIBUTE_Tenant_Profile_Attr ON UM_USER_ATTRIBUTE 
(
    UM_TENANT_ID,
    UM_PROFILE_ID,
    UM_ATTR_NAME,
    UM_ATTR_VALUE
)
INCLUDE (UM_USER_ID); -- 包含关联所需的UM_USER_ID，避免回表查询

这个索引能让数据库直接通过索引定位到符合租户、配置文件、属性名和属性值的记录，无需扫描全表，大幅降低CPU开销。

2. 移除不必要的DISTINCT #

先验证查询是否真的需要DISTINCT：执行以下语句检查是否存在重复的UM_USER_ID结果：

SELECT u.UM_USER_ID, COUNT(*) AS cnt
FROM UM_USER u
INNER JOIN UM_USER_ATTRIBUTE ua 
    ON u.UM_ID = ua.UM_USER_ID
WHERE ua.UM_TENANT_ID = -1234
  AND ua.UM_PROFILE_ID = 'DEFAULT'
  AND ua.UM_ATTR_NAME = 'uid'
  AND ua.UM_ATTR_VALUE = 'whatever@something.com'
  AND u.UM_TENANT_ID = -1234
GROUP BY u.UM_USER_ID
HAVING COUNT(*) > 1;

如果没有返回结果，说明每个用户对应的uid属性是唯一的，DISTINCT完全多余——它会强制数据库进行排序去重，这是高CPU占用的主要原因之一，直接去掉即可。

3. 调整查询逻辑，先过滤大表再关联 #

将查询拆分为子查询，先从大表UM_USER_ATTRIBUTE中筛选出目标UM_USER_ID，再关联小表UM_USER，减少关联的数据量：

SELECT u.UM_USER_ID
FROM UM_USER u
INNER JOIN (
    SELECT UM_USER_ID
    FROM UM_USER_ATTRIBUTE
    WHERE UM_TENANT_ID = -1234
      AND UM_PROFILE_ID = 'DEFAULT'
      AND UM_ATTR_NAME = 'uid'
      AND UM_ATTR_VALUE = 'whatever@something.com'
) ua ON u.UM_ID = ua.UM_USER_ID
WHERE u.UM_TENANT_ID = -1234;

这种写法让数据库优先处理大表的过滤，得到少量符合条件的UM_USER_ID后再关联UM_USER，避免大表全量关联带来的CPU消耗。

4. 检查MSSQL执行计划定位瓶颈 #

在SSMS中打开执行计划（快捷键Ctrl+M），运行原查询，查看是否存在以下问题：

• 全表扫描（Table Scan）：说明索引未生效，检查索引是否创建正确、统计信息是否过期（可执行UPDATE STATISTICS UM_USER_ATTRIBUTE更新统计）
• 排序操作（Sort）：大概率是DISTINCT导致，去掉后可消除
• 键查找/行标识符查找（Key Lookup/RID Lookup）：说明索引未包含所需字段，用INCLUDE子句补充即可

5. 开启WSO2 IS用户信息缓存 #

密码授权流程中如果频繁查询用户信息，开启缓存能大幅减少数据库请求。修改WSO2 IS的repository/conf/user-mgt.xml配置：

<UserStoreManager class="org.wso2.carbon.user.core.jdbc.JDBCUserStoreManager">
    <!-- 开启缓存 -->
    <Property name="CacheEnabled">true</Property>
    <!-- 调整缓存过期时间，根据实际业务设置 -->
    <Property name="CacheTimeout">1800</Property>
    <!-- 其他配置... -->
</UserStoreManager>

缓存会将用户信息存储在内存中，避免重复查询数据库，直接降低CPU负载。

内容的提问来源于stack exchange，提问作者user22872069