logo标签列表

为什么Entity Framework会生成嵌套的SQL查询？

c#mysqllinqentity-frameworkentity-framework-5
25

为什么Entity Framework会生成嵌套的SQL查询？

我有这段代码

    var db = new Context();
    var result = db.Network.Where(x => x.ServerID == serverId)
        .OrderBy(x=> x.StartTime)
        .Take(limit);

这将生成如下结果！（注意双重选择语句）

SELECT
`Project1`.`Id`, 
`Project1`.`ServerID`, 
`Project1`.`EventId`, 
`Project1`.`StartTime`
FROM (SELECT
`Extent1`.`Id`, 
`Extent1`.`ServerID`, 
`Extent1`.`EventId`, 
`Extent1`.`StartTime`
FROM `Networkes` AS `Extent1`
 WHERE `Extent1`.`ServerID` = @p__linq__0) AS `Project1`
 ORDER BY 
`Project1`.`StartTime` DESC LIMIT 5

我应该如何更改才能得到一个select语句？ 我正在使用MySQL和Entity Framework与Code First。

更新

无论传递给 OrderBy()方法的参数类型是什么，我都会得到相同的结果。

更新2：已计时

Total Time (hh:mm:ss.ms)    05:34:13.000
Average Time (hh:mm:ss.ms)  25:42.000
Max Time (hh:mm:ss.ms)  51:54.000
Count   13
First Seen  Nov 6, 12 19:48:19
Last Seen   Nov 6, 12 20:40:22

原始查询：

SELECT `Project?`.`Id`, `Project?`.`ServerID`, `Project?`.`EventId`, `Project?`.`StartTime` FROM (SELECT `Extent?`.`Id`, `Extent?`.`ServerID`, `Extent?`.`EventId`, `Extent?`.`StartTime`, FROM `Network` AS `Extent?` WHERE `Extent?`.`ServerID` = ?) AS `Project?` ORDER BY `Project?`.`Starttime` DESC LIMIT ?

我使用一个程序从当前 MySQL 进程中获取快照。

其他查询同时执行，但当我将其更改为仅一个 SELECT 语句时，它永远不会超过一秒钟。也许还有其他问题，因为我对数据库不是太熟悉...

更新三：解释语句

实体框架生成的。

'1', 'PRIMARY', '<derived2>', 'ALL', NULL, NULL, NULL, NULL, '46', 'Using filesort'
'2', 'DERIVED', 'Extent?', 'ref', 'serveridneventid,serverid', 'serveridneventid', '109', '', '45', 'Using where'

一句话简介

'1', 'SIMPLE', 'network', 'ref', 'serveridneventid,serverid', 'serveridneventid', '109', 'const', '45', 'Using where; Using filesort'

这是来自我的QA环境，所以我贴上面的时间与行数说明无关。我认为有大约500,000条记录匹配一个服务器ID。

解决方案

我从MySQL切换到SQL Server。我不想彻底重写应用程序层。

3
首先，LINQ To SQL 和 Entity Framework 是不同的东西。其次，你为什么认为这个查询不好？你做了性能分析或者至少运行了 explain 吗？ - Stilgar
1
是的，我手动使用纯SQL构建它们。对于2800万条记录，这意味着毫秒级别而不是分钟级别。 - Simon Edström
1
@SimonEdström - 这个查询没有任何问题，实际上这就是它应该做的。您不希望对 2800 万条记录进行排序，而是要选择由 where 子句约束的子集，然后对子集进行排序并应用限制。这会导致 order by 的工作集更小。如果您在这方面遇到了巨大的问题，那么很可能存在其他问题。Where 子句返回多少条记录？ - Erik Funkenbusch
1
现在我们等待比我们更懂数据库的人来解释这些explain语句的真正含义以及为什么MySQL会以这种方式生成它们。在我看来，第一个查询首先进行排序，然后再进行过滤，但是为什么会这样呢... - Stilgar
1
嵌套查询的性能问题可能是MySql特有的问题：https://dev59.com/9WrWa4cB1Zd3GeqP-WLj（还要阅读问题下面的评论）。 - Slauma
显示剩余9条评论
6个回答
7

这是从表达式树中逻辑构建查询的最简单方法。通常情况下，性能不应该成为问题。如果您遇到性能问题，可以尝试使用以下方法获取实体：

var results = db.ExecuteStoreQuery<Network>(
    "SELECT Id, ServerID, EventId, StartTime FROM Network WHERE ServerID = @ID", 
    serverId);

results = results.OrderBy(x=> x.StartTime).Take(limit);
2
我敢打赌这在SQL Server中不会发生 ;) 我想知道他们的执行计划构建例程有什么不同。我要去尝试复现。 - PeteGO
2
我也很好奇，但是太懒了不想自己查研究。 - Stilgar
如果您想在SQL中保持排序并使用take操作来减少查询返回的结果，则此解决方案无效。 - Piccio95
3

我的初步印象是，采用这种方式实际上更有效率，尽管在与MSSQL服务器的测试中，我得到了小于1秒的响应时间。

通过一个Select语句，它首先对所有记录进行排序（Order By），然后过滤到您想看到的集合（Where），最后选取前5个（Limit 5或者对于我来说是 Top 5）。在大型表上，排序占据了很大一部分时间。使用嵌套语句，它首先将记录筛选到子集上，然后再在其上执行昂贵的排序操作。

编辑：我确实进行了测试，但我意识到我的测试有误，因此无效了。测试结果已被删除。

旁白：我真希望要求记录所有搜索的人实际上查看了那些数据。目前，它只有检测黑客脚本和垃圾邮件机器人的作用，因为它们搜索无意义的查询。 - Bobson
1
你的第一个查询中根本没有where子句。虽然我不是MySQL专家，但我期望任何体面的数据库引擎都会首先执行where子句并生成查询计划。 - James Gaunt
@JamesGaunt - 哎呀，你说得对。我在测试时复制粘贴出错了。证据无效化了。 - Bobson
@Bobson，你应该更新你的答案并告诉人们你是用 MS SQL Server 进行测试的。这很令人困惑！ - Simon Edström
@SimonEdström - 说得好。当我有测试结果时，我把它放在那里，但后来我把它编辑掉了。 - Bobson
3
为什么Entity Framework会产生嵌套查询？简单的答案是因为Entity Framework将查询表达式分解成表达式树，然后使用该表达式树来构建查询。树自然地生成嵌套查询表达式（即子节点生成一个查询，父节点在该查询上生成一个查询）。
为什么Entity Framework不简化查询并按照您的方式编写它？简单的答案是查询生成引擎可以处理的工作量有限，虽然现在比早期版本要好，但它并不完美，也永远不会完美。
尽管如此，在这种情况下，您手动编写的查询和EF生成的查询之间不应该存在任何显著的速度差异。数据库足够聪明，可以生成首先应用WHERE子句的执行计划。
看一下执行计划。不同语句之间有巨大的性能差异。 - Simon Edström
我没看到那个。两个查询计划都在第一时间执行 WHERE 子句。如果你看到非常不同的运行时间，那么说明还有其他问题存在。或者这是 MySQL 的一个“特性”，但 MySQL 是广泛使用且备受尊敬的数据库引擎，我无法相信它会犯如此基本的错误。你是否使用相同的参数调用了这两个测试？ - James Gaunt
啊！刚才在问题的评论里看到@Slauma的留言！哇。如果是这样的话，停止使用MySQL直到他们解决问题为止。子查询不是临时表！ - James Gaunt
是的，这听起来像是一种替代方案。 - Simon Edström
1

我刚刚偶然发现了这篇文章，因为我也遇到了同样的问题。我已经花了几天时间来追踪它，结果发现只是mysql中查询生成的问题。

我已经在mysql.com上提交了一个错误报告http://bugs.mysql.com/bug.php?id=75272

总结一下问题：

这个简单的查询

context.products
    .Include(x => x.category)
    .Take(10)
    .ToList();

被翻译成

SELECT
`Limit1`.`C1`, 
`Limit1`.`id`, 
`Limit1`.`name`, 
`Limit1`.`category_id`, 
`Limit1`.`id1`, 
`Limit1`.`name1`
FROM (SELECT
`Extent1`.`id`, 
`Extent1`.`name`, 
`Extent1`.`category_id`, 
`Extent2`.`id` AS `id1`, 
`Extent2`.`name` AS `name1`, 
1 AS `C1`
FROM `products` AS `Extent1` INNER JOIN `categories` AS `Extent2` ON `Extent1`.`category_id` = `Extent2`.`id` LIMIT 10) AS `Limit1`

并且表现相当不错。无论如何，外部查询是几乎没有用处的。如果我添加一个OrderBy

context.products
    .Include(x => x.category)
    .OrderBy(x => x.id)
    .Take(10)
    .ToList();

查询变更为

SELECT
`Project1`.`C1`, 
`Project1`.`id`, 
`Project1`.`name`, 
`Project1`.`category_id`, 
`Project1`.`id1`, 
`Project1`.`name1`
FROM (SELECT
`Extent1`.`id`, 
`Extent1`.`name`, 
`Extent1`.`category_id`, 
`Extent2`.`id` AS `id1`, 
`Extent2`.`name` AS `name1`, 
1 AS `C1`
FROM `products` AS `Extent1` INNER JOIN `categories` AS `Extent2` ON `Extent1`.`category_id` = `Extent2`.`id`) AS `Project1`
 ORDER BY 
`Project1`.`id` ASC LIMIT 10

这是不好的，因为order by在外部查询中。这意味着MySQL必须拉取每个记录以执行orderby，从而导致using filesort

我验证了SQL Server（至少是Comapact）不会为相同的代码生成嵌套查询

SELECT TOP (10) 
[Extent1].[id] AS [id], 
[Extent1].[name] AS [name], 
[Extent1].[category_id] AS [category_id], 
[Extent2].[id] AS [id1], 
[Extent2].[name] AS [name1], 
FROM  [products] AS [Extent1]
LEFT OUTER JOIN [categories] AS [Extent2] ON [Extent1].[category_id] = [Extent2].[id]
ORDER BY [Extent1].[id] ASC
1

如果您想让EF在生成查询时不使用子查询，请在查询中使用常量而非变量。

我之前创建了自己的.Where和所有其他LINQ方法，首先遍历表达式树并将所有变量、方法调用等转换为Expression.Constant。这是因为在Entity Framework中存在这个问题...

当EF需要执行查询时，它会将表达式树编译成数据库命令。对于复杂的查询来说，这是非常昂贵的。如果您将变量更改为常量，EF将无法重用缓存的已编译内容，并且需要重新编译。对于复杂的查询来说，这是极其影响性能的。 - MBoros
-2

实际上，Entity Framework 生成的查询语句有点丑陋，比 LINQ 2 SQL 少一些，但仍然不太美观。

然而，很可能你的数据库引擎会生成所需的执行计划，并且查询将顺利运行。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接