使用SQL Server视图数据检索过慢，求问题排查与优化方案

我的项目基于C#与.NET框架，使用SQL Server数据库，包含certificate和logbook两张存储大量数据的表，其中存有多份Base64格式的图片副本。为后续生成PDF，我创建了名为UserLogbookView的视图汇总所有数据，视图中通过子查询将certificate和logbook数据生成为JSON格式存储。目前反序列化JSON仅需约4秒，但从该视图检索数据却耗时约7分钟。请帮忙指出问题所在，或提供高效检索数据的方案。

相关代码 #

视图创建SQL #

CREATE VIEW UserLogbookView AS
SELECT
    u.UserId,
    u.Name,
    u.Gender,
    u.CandidateNumber,
    u.PhoneNumber,
    u.Email,
    u.Address,
    u.ImageUrl,
    u.UserName,
    u.SupervisorName,
    u.TrainingCenter,
    ui.UserInformationId,
    ui.PerformanceForm,
    ui.InternStartDate,
    ui.InternEndDate,
    ui.Abstract,
    ui.Marksheet,
    ui.StudentSignature,
    ui.InternshipCertificate,
    c.CourseId,
    c.CourseName,
    c.CourseCode,
    c.ShortCode,
    c.Duration,
    (SELECT CertificateImage FROM Certificate WHERE UserId = u.UserId FOR JSON PATH) AS Certificates,
    (SELECT [Name], LogStartDate, LogEndDate, [Shift], [Department], Section, Task, TaskImages, TaskImagesDescriptions, Observation FROM Logbook WHERE UserId = u.UserId FOR JSON PATH) AS Logbooks
FROM
    [User] u
LEFT JOIN
    UserInformation ui ON u.UserId = ui.UserId
LEFT JOIN
    Course c ON ui.CourseId = c.CourseId;

视图实体类（UserLogbookView.cs） #

namespace LogBook.Service.Models.View_Model_Logbook
{
    public class UserLogbookView
    {
        public string UserId { get; set; }
        public string Name { get; set; }
        public string? Gender { get; set; }
        public string CandidateNumber { get; set; }
        public string PhoneNumber { get; set; }
        public string Email { get; set; }
        public string? Address { get; set; }
        public string? ImageUrl { get; set; }
        public string? UserName { get; set; }
        public string? SupervisorName { get; set; }
        public string? TrainingCenter { get; set; }
        public string? UserInformationId { get; set; }
        public string? PerformanceForm { get; set; }
        public DateTime InternStartDate { get; set; }
        public DateTime InternEndDate { get; set; }
        public string? Abstract { get; set; }
        public string? Marksheet { get; set; }
        public string? StudentSignature { get; set; }
        public string? InternshipCertificate { get; set; } 
        public int? CourseId { get; set; }
        public string? CourseName { get; set; }
        public string? CourseCode { get; set; }
        public string? ShortCode { get; set; }
        public string? Duration { get; set; }
        public string? Certificates { get; set; }
        public string? Logbooks { get; set; }
    }

    public class CertificateViews
    {
        public string? CertificateImage { get; set; }
    }

    public class LogbookViews
    {
        public string? Name { get; set; }
        public DateTime? LogStartDate { get; set; }
        public DateTime? LogEndDate { get; set; }
        public string? Shift { get; set; }
        public string? Department { get; set; }
        public string? Section { get; set; }
        public string? Task { get; set; }
        public string? TaskImages { get; set; }
        public string? TaskImagesDescriptions { get; set; }
        public string? Observation { get; set; }
    }

    public class UserLogbookViewModel
    {
        public UserLogbookView UserDetails { get; set; }
        public List<CertificateViews> Certificates { get; set; }
        public List<LogbookViews> Logbooks { get; set; }
    }
}

DbContext配置片段 #

modelBuilder.Entity<UserLogbookView>(a =>
{
    a.HasNoKey();
    a.ToView("UserLogbookView");
});

数据检索查询 #

var users = await _dbContext.UserLogbookView.AsNoTracking()
    .Where(a => a.UserId == userId)
    .ToListAsync(cancellationToken);

核心问题点 #

1. 视图子查询的重复计算：视图中对每个用户都执行两次相关子查询（Certificate和Logbook表），数据量大时会触发大量重复查询，再加上Base64图片体积大，JSON序列化会占用大量CPU和IO资源。
2. 缺失关键索引：如果Certificate.UserId和Logbook.UserId没有非聚集索引，SQL Server需要对这两张大表做全表扫描匹配数据，这是检索慢的核心原因之一。
3. 视图无过滤执行：虽然查询最后加了UserId过滤，但视图会先计算所有用户的完整数据（包括所有JSON序列化内容），再筛选目标用户，完全浪费了全量计算的资源。

优化方案 #

1. 移除视图，改用按需关联查询 #

放弃预生成JSON的视图，直接在代码中先查用户基础数据，再单独查询对应用户的证书和日志，最后在内存组装结构。这样只针对目标用户执行必要查询：

// 查询用户基础信息
var userDetails = await _dbContext.User
    .Join(_dbContext.UserInformation, u => u.UserId, ui => ui.UserId, (u, ui) => new { u, ui })
    .Join(_dbContext.Course, uu => uu.ui.CourseId, c => c.CourseId, (uu, c) => new UserLogbookView
    {
        UserId = uu.u.UserId,
        Name = uu.u.Name,
        Gender = uu.u.Gender,
        CandidateNumber = uu.u.CandidateNumber,
        PhoneNumber = uu.u.PhoneNumber,
        Email = uu.u.Email,
        Address = uu.u.Address,
        ImageUrl = uu.u.ImageUrl,
        UserName = uu.u.UserName,
        SupervisorName = uu.u.SupervisorName,
        TrainingCenter = uu.u.TrainingCenter,
        UserInformationId = uu.ui.UserInformationId,
        PerformanceForm = uu.ui.PerformanceForm,
        InternStartDate = uu.ui.InternStartDate,
        InternEndDate = uu.ui.InternEndDate,
        Abstract = uu.ui.Abstract,
        Marksheet = uu.ui.Marksheet,
        StudentSignature = uu.ui.StudentSignature,
        InternshipCertificate = uu.ui.InternshipCertificate,
        CourseId = c.CourseId,
        CourseName = c.CourseName,
        CourseCode = c.CourseCode,
        ShortCode = c.ShortCode,
        Duration = c.Duration
    })
    .Where(x => x.UserId == userId)
    .FirstOrDefaultAsync(cancellationToken);

if (userDetails == null) return null;

// 查询当前用户的证书和日志
var certificates = await _dbContext.Certificate
    .Where(c => c.UserId == userId)
    .Select(c => new CertificateViews { CertificateImage = c.CertificateImage })
    .ToListAsync(cancellationToken);

var logbooks = await _dbContext.Logbook
    .Where(l => l.UserId == userId)
    .Select(l => new LogbookViews
    {
        Name = l.Name,
        LogStartDate = l.LogStartDate,
        LogEndDate = l.LogEndDate,
        Shift = l.Shift,
        Department = l.Department,
        Section = l.Section,
        Task = l.Task,
        TaskImages = l.TaskImages,
        TaskImagesDescriptions = l.TaskImagesDescriptions,
        Observation = l.Observation
    })
    .ToListAsync(cancellationToken);

// 组装最终模型
var viewModel = new UserLogbookViewModel
{
    UserDetails = userDetails,
    Certificates = certificates,
    Logbooks = logbooks
};

2. 给关联字段添加索引 #

在Certificate和Logbook表的UserId字段创建非聚集索引，并包含查询所需字段，避免书签查找：

-- 给Certificate表创建索引
CREATE NONCLUSTERED INDEX IX_Certificate_UserId ON Certificate(UserId)
INCLUDE (CertificateImage);

-- 给Logbook表创建索引
CREATE NONCLUSTERED INDEX IX_Logbook_UserId ON Logbook(UserId)
INCLUDE ([Name], LogStartDate, LogEndDate, [Shift], [Department], Section, Task, TaskImages, TaskImagesDescriptions, Observation);

3. 改用表值函数替代视图（需保留JSON格式时） #

如果必须保留JSON输出，把视图改成带参数的表值函数，只针对单个用户生成数据：

CREATE FUNCTION GetUserLogbook(@UserId VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
SELECT
    u.UserId,
    u.Name,
    u.Gender,
    u.CandidateNumber,
    u.PhoneNumber,
    u.Email,
    u.Address,
    u.ImageUrl,
    u.UserName,
    u.SupervisorName,
    u.TrainingCenter,
    ui.UserInformationId,
    ui.PerformanceForm,
    ui.InternStartDate,
    ui.InternEndDate,
    ui.Abstract,
    ui.Marksheet,
    ui.StudentSignature,
    ui.InternshipCertificate,
    c.CourseId,
    c.CourseName,
    c.CourseCode,
    c.ShortCode,
    c.Duration,
    (SELECT CertificateImage FROM Certificate WHERE UserId = @UserId FOR JSON PATH) AS Certificates,
    (SELECT [Name], LogStartDate, LogEndDate, [Shift], [Department], Section, Task, TaskImages, TaskImagesDescriptions, Observation FROM Logbook WHERE UserId = @UserId FOR JSON PATH) AS Logbooks
FROM
    [User] u
LEFT JOIN
    UserInformation ui ON u.UserId = ui.UserId
LEFT JOIN
    Course c ON ui.CourseId = c.CourseId
WHERE u.UserId = @UserId;

在EF Core中映射该函数后，查询时直接传入userId：

var user = await _dbContext.GetUserLogbook(userId).FirstOrDefaultAsync(cancellationToken);

4. 分离图片存储（长期优化） #

Base64图片会大幅增加数据库体积和IO开销，建议将图片迁移到文件系统或对象存储（如Azure Blob、阿里云OSS），数据库只存储图片访问路径，可大幅减少查询数据传输量，提升检索速度。

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