在使用 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, 就是将查询条件中的判断语句, 提前在代码中判断完成, 而放到数据库 (如 SQL Server) 中执行时就是简单的, 可利用索引的 SQL 语句了, 在这个例子中, 判断 @userId 和 @menuId 是否为 null 的代码, 可能会长这个样子(如果是 Dapper):
- var sql = new StringBuilder();
- sql.Append("SELECT * FROM FoodOrder WHERE 1=1");
- if (userId != null)
- {
- sql.AppendLine("AND OrderUserId = @userId");
- }
- if (menuId != null)
- {
- sql.AppendLine("AND FoodMenuId = @menuId");
- }
- // ...
如果是 EF, 代码可能是这个样子:
- IQueryable<FoodOrder> 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 AND
- FoodMenuId = @menuId
运行的 set statistics 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
次), 然后执行计划如下:
显示进行了两次 Index Seek, 显然是走了索引, 显示查询开销只占 5%, 而之前的开销占 95%, 性能区别高达 20 倍以上.
总结
据说上次博客园出现性能问题, 就是因为 EF Core 3.0 有这个 bug, 会生成多余的 IS NOT NULL(链接: EF Core 3.0 Preview 9 的 2 个小坑), 这个 bug 已经确认最新的 EF Core 3.1 中解决.
就像文中所说的动态 SQL, 我认为理解数据库, 对写出高性能的应用程序至关重要 -- 这显而易见, 但其实又很容易忽略. 忽略的原因不仅是因为新手, 很多老手有时因为 "互联网" 思维和 "设计模式" 等原因, 也会有意忽略数据库的理解.
现在很多 "互联网" 应用思维认为, 数据库就是一个仓库, 它应该只负责其最 "擅长" 的增删改查功能即可, 其它的应该都交由缓存来解决. 有句话说得好, 就是命名和缓存失效, 是编程界最困难的两个问题. 缓存有缓存的问题, 不好好理解数据库, 就必须花大量时间好好理解缓存. 设计一个正确的缓存往往又比花大量时间设计数据库要复杂得多.
另外现在流行的 "领域驱动设计"(DDD)也主张应用应该先从业务逻辑开始抽象, 数据库和性能往往成为他们首先忽略的对象, 最后可能也得加个 "缓存" 来解决, 导致原来简单的系统急剧膨胀, 复杂不堪. 这种过度设计, 人云亦云的做法值得深思.
来源: https://www.cnblogs.com/sdflysha/p/20191221-why-dynamic-sql.html