06.3.2.2 查询性能与原生 SQL
所属:全栈开发面试题库 / 06 后端框架与工程实践 / 06.3 .NET 后端 / 06.3.2 EF Core 与数据访问 / RPC 集成
本节要解决的问题
- N+1 与笛卡尔积如何识别和消除?
- 投影、拆分查询、批量操作如何选择?
- 原生 SQL 怎么写才安全,批量更新/删除怎么落地?
版本前提:.NET 8 / EF Core 8。
06.3.2.2-001 N+1 如何产生与识别?
一句话结论
N+1 是主集合查询 1 次后,遍历每条记录再查关联 N 次;识别靠 SQL 日志中重复查询、列表耗时随行数增长;消除用 Include 预加载、批量装配或投影。
概念解释
- 触发:循环访问未加载的导航属性(懒加载或手动逐条查询)。
- 表现:订单列表 1 条 SQL,每条订单 Items 各 1 条,共 1+N。
- 识别:开启 EF Core SQL 日志/慢速查询统计,观察同一句查询重复次数。
示例
// 反例:遍历触发懒加载
foreach (var order in orders)
Console.WriteLine(order.Items.Count); // 每条一次查询
// 正例:预加载
var orders = await db.Orders.Include(o => o.Items).ToListAsync(ct);
常见误区
- 以为 Include 一次就不会再有额外查询:未 Include 的导航访问仍会触发。
- 序列化 DTO 时忽略:导航在序列化阶段被访问,同样 N+1。
追问
- 如何在代码评审发现?警惕循环内访问导航/调用 Repository 单查。
- N+1 与笛卡尔积区别?N+1 是多次往返;笛卡尔积是一次 join 行数相乘。
06.3.2.2-002 Include 过深导致笛卡尔积?
一句话结论
同时 Include 多个集合导航时,join 使行数按各集合相乘,结果集膨胀;应控制集合 Include 数量,用投影或拆分查询避免。
概念解释
- 单个集合 Include:行数随该集合增长。
- 两个及以上集合 Include:join 结果为集合大小的乘积,重复数据被 materialize。
- 宽 join 还会放大网络传输与对象组装成本。
示例
// 风险:订单同时带 Items 和 Coupons 两个集合
db.Orders.Include(o => o.Items).Include(o => o.Coupons)
常见误区
- Include 层级越深越好:把所有关联拉回本地。
- 不检查生成 SQL 的 join 与行数。
追问
- 笛卡尔积如何在客户端去重?身份解析可去重实体,但传输膨胀仍存在。
- 哪些导航适合一起 Include?引用导航(ToOne)join 不放大行数;集合导航需谨慎。
06.3.2.2-003 AsSplitQuery 原理与适用?
一句话结论
拆分查询把包含多个集合 Include 的查询拆成多条 SQL,每个集合单独加载,在客户端装配;缓解笛卡尔积,适合多对多大集合场景;单实体查询不必拆分。
概念解释
- EF Core 默认单条 join 查询;
AsSplitQuery()为每个集合导航生成独立查询。 - 代价:多次数据库往返;装配期间数据一致性以各查询时点为准。
- 适用:多个大集合、join 行数乘积不可接受时。
示例
var orders = await db.Orders
.Include(o => o.Items)
.Include(o => o.Coupons)
.AsSplitQuery()
.ToListAsync(ct);
常见误区
- 所有查询都加拆分:单集合场景增加往返,无收益。
- 要求强一致快照:拆分查询各语句时点不同,期间数据可能变化。
追问
- 拆分查询支持哪些提供程序?关系型库支持;行为以 EF Core 文档为准。
- 拆分后排序还能跨集合保证吗?排序作用于主查询,集合按各自查询装配。
06.3.2.2-004 投影只取需要字段的收益?
一句话结论
投影 Select 在 SQL 层只取所需列,不加载完整实体与快照,降低传输、内存与跟踪开销;列表接口优先投影到 DTO。
概念解释
- 避免把不需要的列、导航、大字段(文本/二进制)拉回。
- 投影结果不被跟踪(除非显式跟踪实体)。
- 可在 SQL 层完成计算、聚合与组装 DTO。
常见误区
- 先查全实体再在内存映射 DTO:数据库与网络承担多余字段。
- 投影中引用未映射本地方法:翻译失败或客户端求值。
追问
- 大文本字段何时排除?列表投影排除,详情接口按需加载。
- 投影能减少 N+1 吗?能:直接取需要的关联字段,不触发导航懒加载。
06.3.2.2-005 拆分查询与单条查询取舍?
一句话结论
单条 join 往返少、快照一致,但多集合笛卡尔积膨胀;拆分查询避免乘积但往返多、时点不严格一致;按集合数量、数据量与一致性要求选择。
对比
| 维度 | 单条 join | 拆分查询 |
|---|---|---|
| 往返 | 1 次 | 多次 |
| 行数 | 集合乘积 | 各集合独立 |
| 一致性 | 同一查询时点 | 各语句时点 |
| 适用 | 引用导航/小集合 | 多集合大结果 |
常见误区
- 凭感觉默认拆分或默认 join:应看生成 SQL 与结果规模。
- 拆分查询忽略多次往返的延迟累积。
追问
- 分页场景用拆分查询注意什么?主查询分页后,集合查询按页内主键展开。
- 一致性要求高的报表怎么做?单事务/快照隔离下单条查询。
06.3.2.2-006 原生 SQL:FromSqlRaw/FromSqlInterpolated?
一句话结论
FromSqlRaw 拼接原始 SQL 字符串;FromSqlInterpolated 使用内插参数,参数化传递防注入;原生 SQL 必须与 LINQ 组合返回实体类型,复杂只读查询或性能瓶颈时使用。
概念解释
FromSqlRaw:SQL 文本 + 手动参数数组。FromSqlInterpolated:$"..."内插表达式转为参数,值不拼进 SQL 文本。- 限制:必须返回映射实体;可后续接 LINQ 组合(Where/OrderBy/分页)下推到 SQL。
示例
// 安全:内插参数化
var orders = await db.Orders
.FromSqlInterpolated($"SELECT * FROM Orders WHERE Status = {status}")
.ToListAsync(ct);
// Raw 需显式参数
db.Orders.FromSqlRaw("SELECT * FROM Orders WHERE Status = {0}", status)
常见误区
- Raw 中字符串拼接用户输入:SQL 注入。
- 原生 SQL 后接不可翻译的 LINQ:部分条件可能客户端求值。
追问
- 原生 SQL 能执行存储过程吗?可以,
FromSql*调过程;返回结果需映射实体。 - 非实体只读结果怎么查?
Database.SqlQuery<T>(EF Core 8 提供类型化 SQL 查询能力,版本细节以官方文档为准)。
06.3.2.2-007 原生 SQL 注入防护参数化?
一句话结论
用户输入一律作为参数绑定,不拼进 SQL 文本;表名/列名/排序方向等不能参数化的部分走白名单映射;禁止把请求字符串直接拼进 Raw SQL。
概念解释
- 参数:值由数据库驱动参数化,不参与语法解析。
- 标识符(表/列):校验白名单后拼接。
- 审计:代码扫描识别 Raw SQL 拼接;ORM 默认查询不暴露拼接入口。
示例
// 危险:字符串拼接
$"SELECT * FROM Orders WHERE No = '{input}'"
// 安全:参数
FromSqlInterpolated($"SELECT * FROM Orders WHERE No = {input}")
常见误区
- 以为转义单引号就安全:编码上下文与存储过程二次解析仍可能绕过。
- 排序字段直接用请求值拼
ORDER BY {sort}:必须白名单映射。
追问
- 动态 WHERE 条件怎么安全拼?用 LINQ 条件组合;原生 SQL 用参数集合或白名单片段。
- 存储过程能防注入吗?过程内仍需参数化;过程本身不免疫调用拼接。
06.3.2.2-008 哪些操作不能用原生 SQL 表达(导航 Include)?
一句话结论
FromSql* 返回实体后可接 LINQ 组合过滤/排序,但不能在原生 SQL 段直接 Include 导航;关联加载仍由后续 LINQ/Include 表达,或在投影中显式 join 取字段。
概念解释
- 原生 SQL 返回扁平实体结果。
- 导航 Include 是 EF 关系装配,需在 EF 查询管线内表达。
- 原生查询中写 join 取关联字段,再映射到 DTO。
常见误区
- 期望
FromSql结果自动带出导航:不会。 - 原生 SQL 列名与实体属性不匹配:materialize 失败,需别名对齐。
追问
- 复杂多表原生查询如何返回 DTO?定义只读类型,
SqlQuery<T>映射列;或查询后手工装配。 - 原生 SQL 能更新/删除吗?用
ExecuteUpdate/Delete或Database.ExecuteSqlRaw。
06.3.2.2-009 批量操作:ExecuteUpdate/ExecuteDelete?
一句话结论
EF Core 7+ 提供 ExecuteUpdateAsync/ExecuteDeleteAsync,直接在数据库端按条件批量更新/删除,不加载实体到上下文,避免逐条往返;返回受影响行数。
概念解释
- 表达式指定要更新的列与赋值;
ExecuteDelete按条件删除。 - 不经过变更跟踪器,不触发实体导航级联(按数据库约束行为)。
- 适合大批量状态变更;需要实体业务逻辑时仍走加载+SaveChanges。
示例
await db.Orders
.Where(o => o.Status == Status.Pending && o.CreatedAt < cutoff)
.ExecuteUpdateAsync(s => s.SetProperty(o => o.Status, Status.Expired), ct);
await db.Items.Where(i => i.OrderId == id)
.ExecuteDeleteAsync(ct);
常见误区
- 用批量更新替代需要领域规则校验的变更:批量操作不跑实体方法/事件。
- 忽略级联:直接删除父行前确认子表约束与清理策略。
追问
- 批量更新能在事务里吗?可以,与其他操作同事务。
- 与
SaveChanges逐条更新性能对比?大批量场景数据库端操作远优于往返逐条。
06.3.2.2-010 批量插入与 SaveChanges 批次?
一句话结论
批量插入可依赖 EF 批量提交(SaveChanges 分批)或原生批量插入;注意上下文跟踪实体数量、批次大小与单事务规模;超大批量应分批提交控制事务与内存。
概念解释
- EF 跟踪大量实体后一次 SaveChanges:跟踪器内存与单事务膨胀。
- 分批:每批独立上下文/事务,控制规模与失败回滚范围。
- 高性能批量:复制导入(Bulk Copy)或提供程序批量扩展。
常见误区
- 几万行塞进同一上下文:内存占用高,单事务 undo 膨胀。
- 分批之间不做幂等:重试导致重复插入。
追问
- 批次大小怎么定?按压测与数据库负载调整,兼顾往返次数与事务规模。
- 批量插入失败如何续跑?记录批次进度,按唯一键去重重试。
06.3.2.2-011 索引配置与查询覆盖?
一句话结论
按查询过滤/排序/连接字段配置索引;EF Fluent HasIndex 声明并通过迁移落库;慢查询用执行计划验证索引命中,避免冗余与低选择性索引。
概念解释
- 索引:等值过滤、排序、外键、唯一约束列。
- 组合索引顺序:高选择性/前缀列在前。
- 覆盖索引:包含查询所需列,避免回表。
示例
mb.Entity<Order>().HasIndex(o => new { o.UserId, o.Status });
mb.Entity<Order>().HasIndex(o => o.No).IsUnique();
常见误区
- 为每个字段建索引:写入开销与存储上升,优化器选择变差。
- 只看 LINQ 不看执行计划:索引未命中仍慢。
追问
- EF 索引与数据库现有索引冲突?迁移模型以 EF 配置为准,遗留索引需人工核对。
- 唯一索引与并发令牌关系?唯一约束防重复业务号,并发令牌防并发覆盖。
06.3.2.2-012 跟踪查询的快照开销?
一句话结论
跟踪查询为每个实体保存原始值快照,占用内存并参与变更比对;只读大结果集应 AsNoTracking 降低开销;跟踪开销随行数与属性数增长。
概念解释
- 快照用于 SaveChanges 识别修改;只读场景用不到。
- 无跟踪 materialize 后直接返回,不存原始值。
- 大列表/报表/导出优先无跟踪或投影。
常见误区
- 列表查询默认跟踪且实体含大文本属性:快照内存翻倍。
- 以为跟踪只是「多一个标记」:完整原始值都要保存。
追问
- 跟踪开销如何观测?上下文实体数、内存峰值;对比无跟踪耗时。
- 需要少量更新的混合场景?查询投影取展示,更新时按主键重新加载跟踪。
06.3.2.2-013 编译查询与缓存?
一句话结论
EF Core 缓存查询计划:同一表达式树重复执行复用编译结果;高频固定查询可使用编译查询(EF.CompileQuery)减少构建开销;参数化查询避免每次重新规划。
概念解释
- 查询编译:表达式树 → SQL 计划,默认有缓存。
- 编译查询:显式委托绑定查询,适合高频固定语句。
- 参数化:值走参数,避免不同字面量破坏计划缓存。
常见误区
- 每次拼接不同字面量条件:计划缓存命中率下降。
- 认为 EF 总是慢:未命中索引与 N+1 才是常见原因。
追问
- 编译查询支持异步吗?编译查询主要为同步委托;异步查询用默认缓存管线。
- 缓存键包含什么?表达式树结构与提供程序,参数不破坏缓存。
06.3.2.2-014 ToQueryString 与 SQL 日志?
一句话结论
ToQueryString() 查看 LINQ 翻译出的 SQL;运行时日志记录实际执行语句与参数;调优时先看生成 SQL,再看数据库执行计划。
概念解释
- 调试阶段:
query.ToQueryString()输出 SQL 文本。 - 运行阶段:EF Core 日志类别
Microsoft.EntityFrameworkCore.Database.Command记录命令。 - 慢查询:SQL → 执行计划 → 索引/投影/拆分优化。
示例
var sql = db.Orders.Where(o => o.UserId == uid).ToQueryString();
常见误区
- 只看 C# 代码不看生成 SQL:翻译结果与预期不符。
- 生产打开详细 SQL 日志:注意参数脱敏与日志量。
追问
- 日志参数是否明文?按日志配置输出;敏感参数需脱敏。
- 翻译失败报什么?表达式无法映射到提供程序方言,运行时抛异常。
06.3.2.2-015 分页查询:Skip/Take 与深分页问题?
一句话结论
Skip(n).Take(size) 实现页码分页;深分页跳过大量行成本高(数据库扫描 offset 行);大数据量用游标/键集分页(按上一页最后主键/排序键继续)。
概念解释
- 页码分页:
page计算 Skip,总条数可读;适合浅页。 - 键集分页:
WHERE key < lastKey ORDER BY key LIMIT size,避免扫描跳过行。 - 排序必须稳定(唯一键兜底),否则翻页漏/重。
示例
// 浅页
await db.Orders.OrderBy(o => o.Id).Skip((page-1)*size).Take(size).ToListAsync(ct);
// 游标:上一页最后 Id
await db.Orders.Where(o => o.Id < lastId).OrderByDescending(o => o.Id).Take(size).ToListAsync(ct);
常见误区
- 深 offset 翻几千页:数据库逐行扫描跳过。
- 排序字段不唯一:游标边界不稳定,出现重复/遗漏。
追问
- 总数还要不要?游标分页可不给总数;页码分页提供 total。
- 多条件排序键集分页怎么构造?排序组合键唯一可比较,作为游标条件。
06.3.2.2-016 数据库提供程序差异如何隔离?
一句话结论
LINQ 与配置尽量用可翻译子集;提供程序方言差异(函数、类型、分页)通过仓储/工厂隔离;原生 SQL 标注目标库,避免跨库直接复用。
概念解释
- 提供程序:SQL Server/PostgreSQL/SQLite/MySQL 等,函数与类型映射不同。
- 隔离:业务仓储接口 + 各库实现;方言相关逻辑下沉到数据层。
- 测试:用相近提供程序/Testcontainers 验证翻译与行为。
常见误区
- 在 LINQ 中调用仅某库支持的函数表达式:其他提供程序翻译失败。
- 原生 SQL 无库标注:换库后语法不兼容。
追问
- 跨库迁移注意什么?类型映射、大小写敏感、分页语法、自增/序列差异。
- 本地用 SQLite 测试生产 PostgreSQL?行为有差异,关键集成测试应对齐生产库。
06.3.2.2-017 全局查询过滤器(全局软删除)?
一句话结论
HasQueryFilter 为实体附加全局过滤条件(如 IsDeleted == false),所有查询自动套用;多租户可按租户过滤;注意关闭过滤器的场景与包含已删除数据的查询。
概念解释
- 软删除:实体标记删除,过滤器默认排除。
- 多租户:过滤器结合当前租户值过滤。
- 例外:
IgnoreQueryFilters()显式关闭,用于管理员/恢复场景。
示例
mb.Entity<Order>().HasQueryFilter(o => !o.IsDeleted);
常见误区
- 忘记过滤器对 Include/join 同样生效:关联已删除记录被排除。
- 多租户过滤器依赖的上下文取值为空:查询全部或报错。
追问
- 全局过滤器影响迁移/批量操作吗?批量操作按 EF 行为,部分场景需显式条件。
- 软删除与外键约束?已删除行仍占用唯一索引,业务号设计需考虑。
06.3.2.2-018 并发查询与上下文线程使用?
一句话结论
DbContext 不是线程安全:同一上下文不能被并发线程同时使用;并行处理应每线程/每任务独立上下文;异步查询用 await,不要在上下文上并发触发多个未完成查询。
概念解释
- 跟踪器与连接状态单线程使用。
- 一个上下文同一时刻一个执行查询;多操作串行 await。
- 并行数据处理:创建多个 scope/上下文分别执行。
常见误区
Task.WhenAll跑同一上下文多个查询:并发访问非线程安全对象。- 把上下文传给后台线程长期复用。
追问
- 上下文池化能解决并发吗?池化复用实例但每次仍只在一个作用域内使用。
- 并行批量导入怎么做?分区后每批独立上下文串行/受控并行。
06.3.2.2-019 慢查询排查步骤?
一句话结论
定位慢接口 → 看 EF 生成 SQL 与参数 → 数据库执行计划找全表扫描/缺失索引 → 检查 N+1 与返回行数 → 投影/索引/拆分/分页优化 → 复测分位延迟。
概念解释
- 指标/日志定位慢查询与调用入口。
- 打印 SQL,确认翻译结果与索引使用。
- 执行计划:扫描行数、连接类型、排序临时表。
- 检查关联是否 N+1、结果集是否过宽。
- 优化后对比 P95/P99 与数据库负载。
常见误区
- 直接加索引而不看计划:可能索引选错或查询写法未优化。
- 只优化单次 SQL,忽略 N+1 总往返次数。
追问
- 慢查询与慢接口区别?接口慢可能来自下游、序列化,不只 SQL。
- 数据库侧诊断工具?执行计划、慢查询日志、索引使用统计。
06.3.2.2-020 表达式树被翻译失败常见原因?
一句话结论
翻译失败多因调用了提供程序无法映射的本地方法、不支持的类型转换/函数、客户端求值后又拼接条件;解决方式是改用可翻译成员、投影到内存后再处理,或落原生 SQL。
概念解释
- 不可翻译:自定义静态方法、部分 BCL 函数、动态反射访问。
- 混合:先
ToList再过滤会变内存查询;需要下推的条件必须留在 EF 管线内。 - 报错信息通常指出无法翻译的成员/表达式。
常见误区
- 在 Where 里调领域服务方法:SQL 无法翻译该调用。
- 投影中用本地日期/格式化函数:翻译到 SQL 失败。
追问
- 如何让复杂计算在数据库完成?用可翻译的属性/函数;不支持则投影基础列到内存计算。
- 客户端求值有什么风险?大数据量拉回内存,性能与一致性问题。
本节自检
- [ ] 能识别 N+1 与笛卡尔积并选择 Include/投影/拆分查询
- [ ] 能正确使用 FromSqlInterpolated 并守住参数化防线
- [ ] 能用 ExecuteUpdate/ExecuteDelete 做数据库端批量操作
- [ ] 能设计索引并通过执行计划验证命中
- [ ] 能说出深分页问题与游标分页方案、上下文线程约束