wordpress网站模版,jsp网站项目,南宁一站网网络技术有限公司,冯耀宗seo博客优化为什么需要动态SQL在使用 EF或者写 SQL语句时#xff0c;查询条件往往是这样一种非常常见的逻辑#xff1a;如果客户填了查询信息#xff0c;则查询该条件#xff1b;如果客户没填#xff0c;则返回所有数据。我常常看到很多人解决这类问题时使用了错误的静态 SQL的解决办… 为什么需要动态SQL在使用 EF或者写 SQL语句时查询条件往往是这样一种非常常见的逻辑如果客户填了查询信息则查询该条件如果客户没填则返回所有数据。我常常看到很多人解决这类问题时使用了错误的静态 SQL的解决办法使得数据库无法利用索引导致性能急剧下降。介绍数据这次我将使用我的某客户的真实数据来演示已确认不涉及信息安全????有一个订单表 FoodOrder结构如下 我在 Id、 FoodMenuId、 OrderUserId上创建了非聚集索引在 OrderTime上创建了聚集索引。该表有 51652条数据。静态SQL在这种逻辑中如果想用一条 SQL语句搞定所有查询那么代码可能长这个样子set statistics io on
declare userId int 506
declare menuId int 3176
select * from FoodOrder where (userId is null or OrderUserId userId) AND(menuId is null or FoodMenuId menuId)
这种写法虽然方便但基于其 SQL过于“复杂”甚至还使用了 IS NULL和 OR导致语句完全无法使用索尼运行 SET STATISTICS IO ON后显示信息如下(3 行受影响)
Table FoodOrder. Scan count 1, logical reads 337, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
显示其进行了一次表扫描并进行了 337次逻辑读输出数据只有 3行。然后看看实际的执行计划 如图显示了一个极其简单的执行计划确实进行了一次表扫描读取了 51652行数据并且完全没有走索引。动态SQL而动态 SQL就是将查询条件中的判断语句提前在代码中判断完成而放到数据库如 SQLServer中执行时就是简单的、可利用索引的 SQL语句了在这个例子中判断 userId和 menuId是否为 null的代码可能会长这个样子如果是 Dappervar sql new StringBuilder();
sql.Append(SELECT * FROM FoodOrder WHERE 11 );
if (userId ! null)
{sql.AppendLine(AND OrderUserId userId);
}
if (menuId ! null)
{sql.AppendLine(AND FoodMenuId menuId);
}
// ...
如果是 EF代码可能是这个样子IQueryableFoodOrder query db.FoodOrders;
if (userId ! null)
{query query.Where(x x.OrderUserId userId);
}
if (menuId ! null)
{query query.Where(x x.FoodMenuId menuId);
}
// ...
这样一来最终在数据中执行的 SQL语句就比较简单了如果客户确实传了 userId和 menuId两个参数 SQL就应该长这个样子select * from FoodOrder where OrderUserId userId ANDFoodMenuId menuId
运行的 setstatistics io on结果如下(3 行受影响)
Table FoodOrder. Scan count 2, logical reads 11, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
显然仅进行了 11次逻辑读相比静态 SQL的 337次然后执行计划如下 显示进行了两次 IndexSeek显然是走了索引显示查询开销只占 5%而之前的开销占 95%性能区别高达 20倍以上。总结据说上次博客园出现性能问题就是因为 EFCore3.0有这个 bug会生成多余的 IS NOT NULL链接EF Core 3.0 Preview 9 的2个小坑这个 bug已经确认最新的 EFCore3.1中解决。就像文中所说的动态 SQL我认为理解数据库、对写出高性能的应用程序至关重要——这显而易见但其实又很容易忽略。忽略的原因不仅是因为新手很多老手有时因为“互联网”思维和“设计模式”等原因也会有意忽略数据库的理解。现在很多“互联网”应用思维认为数据库就是一个仓库它应该只负责其最“擅长”的增删改查功能即可其它的应该都交由缓存来解决。有句话说得好就是命名和缓存失效是编程界最困难的两个问题。缓存有缓存的问题不好好理解数据库就必须花大量时间好好理解缓存。设计一个正确的缓存往往又比花大量时间设计数据库要复杂得多。另外现在流行的“领域驱动设计” DDD也主张应用应该先从业务逻辑开始抽象数据库和性能往往成为他们首先忽略的对象最后可能也得加个“缓存”来解决导致原来简单的系统急剧膨胀复杂不堪。这种过度设计、人云亦云的做法值得深思。喜欢的朋友 请关注我的微信公众号【DotNet骚操作】
