如何在SQL Server中优化SQL查询？及登录场景下获取用户信息与权限的高性能实现方案问询

嘿，我来帮你解答这两个SQL Server相关的问题，都是日常开发中很常见的优化场景：

这里分享几个实用且经过项目验证的优化技巧：

• 优先优化索引策略：给查询的过滤条件（WHERE子句）、连接字段（JOIN）、排序/分组字段创建非聚集索引；别在索引字段上套函数（比如WHERE YEAR(CreateTime) = 2024会直接让索引失效）；如果查询只需要特定字段，创建覆盖索引把这些字段都包含进去，避免额外的键查找。
• 拒绝SELECT *：只查询业务需要的字段，既减少数据传输量，也更容易命中覆盖索引，提升查询效率。
• 优化JOIN与子查询：尽量用INNER JOIN替代相关子查询，SQL Server的查询优化器对JOIN的执行计划优化更成熟；避免无意义的笛卡尔积，确保JOIN条件精准。
• 减少临时表的不必要使用：临时表会带来额外的IO开销，能用CTE（公共表表达式）或者直接关联查询的场景就别用临时表；如果必须用，记得给临时表加索引。
• 避免不必要的排序操作：ORDER BY、GROUP BY、DISTINCT都会触发排序，能省略就省略；如果必须排序，确保排序字段有索引支持。
• 善用执行计划：在SSMS里按Ctrl+M开启实际执行计划，看看有没有全表扫描、键查找这类低效操作，针对性优化。
• 参数化查询：用参数化语句或者sp_executesql替代动态SQL，提升查询计划的缓存命中率，避免重复编译。

先聊聊你现有写法的问题：
你的第一个临时表写法，假设UserName+Password是唯一的（正常登录场景应该是），TOP 1其实是多余的；而且创建临时表会带来额外的IO开销，反而不如直接关联高效。第二个写法重复查询了两次Users表，完全是资源浪费。

下面给你几个更优的实现方案，按推荐程度排序：

方案一：JOIN直接返回合并结果（最优） #

如果业务允许一次性返回用户信息和权限，这是最快的方式——只需要一次关联查询，避免多次IO：

SELECT 
    u.id, u.Code, u.Name, u.PostId,
    upa.PermetionId
FROM Users u
LEFT JOIN UserPostAccess upa ON upa.Id = u.PostId
WHERE u.UserName = 'myUser' AND u.Password = 'myPassword';

（注：用LEFT JOIN是为了避免用户没有岗位权限时返回空结果，如果你确定用户一定有权限，用INNER JOIN性能稍好一点）

方案二：用CTE避免重复查询 #

如果需要分开返回用户信息和权限，CTE是内存级的临时结果集，比临时表轻量得多：

WITH UserLoginCTE AS (
    SELECT id, Code, Name, PostId
    FROM Users
    WHERE UserName = 'myUser' AND Password = 'myPassword'
)
-- 返回用户信息
SELECT * FROM UserLoginCTE;
-- 返回对应的岗位权限
SELECT upa.PermetionId 
FROM UserPostAccess upa
JOIN UserLoginCTE u ON upa.Id = u.PostId;

方案三：用变量存储PostId #

如果必须分开查询，用变量存下PostId，避免重复扫描Users表：

DECLARE @PostId INT;

-- 获取用户信息并存储PostId
SELECT 
    @PostId = PostId,
    id, Code, Name
FROM Users
WHERE UserName = 'myUser' AND Password = 'myPassword';

-- 返回岗位权限
SELECT PermetionId FROM UserPostAccess WHERE Id = @PostId;

额外的重要建议： #

• 一定要给Users表的UserName（或者UserName+Password）创建联合索引，这会让登录查询的速度大幅提升。
• UserPostAccess表的Id字段应该设为主键（默认有聚集索引），这样关联查询时的查找效率最高。
• 绝对不要明文存储密码！要用SHA-256这类哈希算法加盐后存储，这是安全红线，也是生产环境的必备要求。

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