SQL查询执行过慢，如何优化该Oracle查询语句？

1. 移除冗余的表连接 #

原查询中左连接了MSG_ACCOUNT_USERS、MSG_ACCOUNTS、MSG_TENANTS三张表，但SELECT和WHERE子句完全没有使用这些表的任何字段，这些连接只会产生重复的用户记录，增加数据库的计算量和IO开销，直接移除即可。

2. 消除无意义的聚合与分组 #

原查询使用MIN(UPPER(MSG_USERS.MSU_LOGIN_NAME))作为排序字段，同时GROUP BY包含了MSG_USERS.MSU_LOGIN_NAME——对于同一个用户（按MSU_USER_ID分组），UPPER(MSU_LOGIN_NAME)是唯一值，MIN聚合完全多余。移除GROUP BY和MIN聚合后，不仅简化逻辑，还能避免分组带来的性能损耗。

3. 简化多层嵌套与冗余排序 #

原查询嵌套了5层SELECT，多次重复排序和ROWNUM过滤，大部分是冗余操作。比如连续的正序→取前6→倒序→取前6→正序，最终结果等价于直接取前6条正序数据，完全可以大幅简化嵌套层级。

4. 添加针对性索引 #

针对WHERE条件的MSU_ROLE_NAME和排序字段UPPER(MSU_LOGIN_NAME)、MSU_USER_ID，创建复合索引，让数据库可以直接通过索引过滤数据并完成排序，避免全表扫描和内存排序：

CREATE INDEX idx_msg_users_role_login ON MSG_USERS(MSU_ROLE_NAME, UPPER(MSU_LOGIN_NAME), MSU_USER_ID);

优化后的SQL #

场景1：原逻辑等价的简化版本（取前6条正序数据） #

SELECT
    UPPER(MSU_LOGIN_NAME) AS MSU_LOGIN_NAME_SORT,
    MSU_USER_ID AS ID,
    MSU_LAST_LOGIN_TIME,
    MSU_CREATED_DATE,
    MSU_LOGIN_NAME,
    MSU_UUID,
    MSU_FIRST_NAME,
    MSU_LAST_NAME,
    MSU_LAST_UPDATED,
    MSU_CUSTOMER_UID
FROM MSG_USERS
WHERE MSU_ROLE_NAME NOT IN (
    'CUSTOMER_INTEGRATION', 
    'CUSTOMER_EXTERNAL_INTEGRATION', 
    'INTEGRATION_SUPERUSER', 
    'OAUTH2_CLIENT'
)
ORDER BY MSU_LOGIN_NAME_SORT ASC, ID ASC
FETCH FIRST 6 ROWS ONLY; -- Oracle 12c+可用，低版本替换为WHERE ROWNUM < 7

场景2：若原需求是获取最后6条数据（正序的末尾6条） #

如果原查询的多层嵌套是想获取排序后的最后6条，可调整为：

SELECT *
FROM (
    SELECT
        UPPER(MSU_LOGIN_NAME) AS MSU_LOGIN_NAME_SORT,
        MSU_USER_ID AS ID,
        MSU_LAST_LOGIN_TIME,
        MSU_CREATED_DATE,
        MSU_LOGIN_NAME,
        MSU_UUID,
        MSU_FIRST_NAME,
        MSU_LAST_NAME,
        MSU_LAST_UPDATED,
        MSU_CUSTOMER_UID
    FROM MSG_USERS
    WHERE MSU_ROLE_NAME NOT IN (
        'CUSTOMER_INTEGRATION', 
        'CUSTOMER_EXTERNAL_INTEGRATION', 
        'INTEGRATION_SUPERUSER', 
        'OAUTH2_CLIENT'
    )
    ORDER BY MSU_LOGIN_NAME_SORT DESC, ID DESC
    FETCH FIRST 6 ROWS ONLY
)
ORDER BY MSU_LOGIN_NAME_SORT ASC, ID ASC;

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