EF查询百万级数据的性能测试--多表连接复杂查询
相关文章：EF查询百万级数据的性能测试--单表查询
一、起因
上次做的是EF百万级数据的单表查询，总结了一下，在200w以下的数据量的情况(Sql Server 2012)，EF是可以使用，但是由于查询条件过于简单，且是单表查询，EF只是负责生成Sql语句，对于一些简单的查询，生成Sql语句的时间可以基本忽略，所以不仅没有发挥出EF的优势，而且这样的性能瓶颈基本可以说是和数据库完全有关的，这个锅数据库得背(数据库：怪我了)。鉴于实际项目中多是多表的连接查询，还有其他复杂的查询，一向本着求真务实的思想的博主就趁此机会再次测试了一下EF的复杂的连接查询什么的。说实话，在测试之前我也不知道结果，只是为了自己以后用起来有个参考依据，也比总是听别人说EF性能很差，吓得都不敢用了要好。EF的性能到底有多差，或者说可以胜任什么样的场景，不吹不黑，我们就一起来看看，也好在以后的实际项目选型的时候参考一下。
二、关于很多ORM框架的对比测试
博主最近也看了不少关于ORM框架的测试，大多数都是增删改几千，几万条的数据，这样确实可以看出来性能的比较，但是实际项目中真的很少有这样的情况，一次增删改几千几万条数据的，我们做项目服务的都是用户，按用户的一次请求为一次数据库上下文的操作，同一个上下文在这样的一次请求中基本不可能同时提交这么多的数据操作，有人说那要是成千上万的用户同时呢，那就要考虑并发了，就不是本文所要讨论的问题了。所以这些测试能表明结果，但是不能表明实际问题。另外在大多数对于EF的测试中，很多人忽略了EF对于实体的跟踪，比如：
这些属性虽然我不全知道是什么的东西，但是既然可以设置Enabled，就说明是对性能有影响的，而且数据量越多，相信影响也越大，但其他多数ORM应该都没有这些功能或者设置（我不知道，哈哈），所以对于增删改的操作，我觉得当前情况下是完全够用的，所以不再探究增删改的性能（如果实在有朋友觉得必要，博主再找机会）。EF的初衷，也可以说是很多ORM应该具备的出发点，就是从以前的非常不OO的数据操作方式，变成现在的OO的方式，就是为了解放开发人员写Sql查询操作数据库的方式，就是要用面向对象的思想来操作数据库，结果倒好，有些人又要回到以前写Sql语句，又要去回到*，这就好比 面向过程编程 效率很高速度很快，但是为什么要提出面向对象编程，因为面向过程写起来累啊！不好维护啊！不好扩展啊！不方便啊，还有分层架构，不都是为了这吗，这些东西我们应该是发挥它的优势，知道他在什么情况下用，什么情况下不用，而不是一直死死的抓住他的缺点说不行。当然，有很多情况下是不追求生产效率，只追求性能的，那就不说了。
说了这么多，我也不是想证明什么，我只是想知道，我该什么情况下用EF，怎么用EF来发挥出他的优势，怎么能用好EF，应用到实际生产环境中。一句话，为什么我的眼里常含泪水，因为我对EF爱的深沉。（斜眼笑）
三、准备工作
那肯定是先建表结构和数据了，废话不多说，上图先。
1.关系图
这是数据库的关系图，只有User和Role是多对多关系，其他的是一对多，另外都加了导航属性，博主事先用的是Code First，已经添加了导航属性，为的是可以在后来的测试中使用导航属性（EF会自动根据导航属性生成连接查询，可以由此来做测试），这里借用了Database First来从数据库生成了模型图，为的是大家能够清楚的看表之间的关系。
简单说明一下：
一个User对应多个Order；
一个Order对应多个OrderDetail，对应一个City；
一个OrderDetail相当于一个产品，对应一个产品类型Category。
其中由于多对多的关系比较少见，且可以转化为两个 一对多的关系（Sql Server就是这么干的），所以这次暂时不做多对多的测试，应该和一对多差不多。
2.表数据
这里城市表 是现在项目中用的一个，因为之前就三个字段Id，Name，ParentId，然后要找其他数据就要递归查询，很浪费时间，后来想了想既然都是死数据，就一下给写进去，之后再用就不用查了。
附上City表的Sql文件，有需要的同学可以带走：dbo.City.Table.zip
在某东首页复制的商品类型数据。。
3.数据量
用户表，订单表，订单明细表都是100w的数据，其他两个表按实际情况来，类型表没有再细分，就这样吧。
四、开始测试
1.关于Sql语句生成的时间
由于大多数人都说EF的性能瓶颈在生成Sql的时间和质量上，引用一位朋友的回答如下：
上边这条评论的第二条说的应该就是质量的问题，关于EF生成Sql语句有什么规则，或者怎样才能生成高质量的Sql，这个内容也是一个很值得研究的问题，我们随后有时间研究。今天我们就只针对生成Sql语句的时间上加以探究。
在网上搜索了一些资料，关于怎么测试EF生成Sql的时间，博主没有见到过相关的测试，但是怎样获取到生成的Sql语句还是有办法的，所以，博主想了想，既然能获取到sql语句，那么这个获取的过程就可以作为生成Sql的时间，由于没有相关的资料说明，所以暂且用这样的方法来测，博主使用的两种比较笨的方法测试生成的时间，也希望园友们如果有更好的方法可以告诉博主。
1.ToString()方法
由于在IQueryable接口中重写了ToString()方法，所以博主试了一下，果真能获取到Sql语句，所以就用ToString()方法的执行时间当做生成Sql语句的时间。先来个简单的：
可以看出已经生成了Sql（注意：这里并没有去数据库查询，只是生成了Sql）涉及到了最简的两个表的链接，那我们接下来看生成所用的时间。
可以看出来，生成Sql的时间非常短，完全可以忽略不计，可能博友觉得Sql过于简单，没关系，我们再来几个复杂的
复杂语句一，涉及到了四个表的链接：
依旧很少时间，只是略比上一个Sql的时间长一点，毕竟复杂了一点。
复杂语句二，直接截图了，这里为了生成Sql语句的复杂，随便写了一些Linq，可能不是我们日常想要的结果，只是为了复杂而已：
时间明显变长，但是依旧不到1ms，附上生成的Sql语句，够复杂了吧。
1 SELECT 2 [Project7].[C1] AS [C1], 3 [Project7].[Work] AS [Work], 4 [Project7].[C2] AS [C2] 5 FROM ( SELECT 6 [Project6].[Work] AS [Work], 7 1 AS [C1], 8 [Project6].[C1] AS [C2] 9 FROM ( SELECT 10 [Project3].[Work] AS [Work], 11 (SELECT 12 MAX([Project5].[Amount]) AS [A1] 13 FROM ( SELECT 14 [Extent11].[Id] AS [Id], 15 [Extent11].[Amount] AS [Amount], 16 [Filter4].[UserId] AS [UserId] 17 FROM (SELECT [Project4].[UserId] AS [UserId], [Project4].[FullName] AS [FullName], [Project4].[UserName] AS [UserName1], [Extent10].[Work] AS [Work] 18 FROM (SELECT 19 [Extent6].[UserId] AS [UserId], 20 [Extent7].[FullName] AS [FullName], 21 [Extent8].[UserName] AS [UserName], 22 (SELECT 23 SUM([Extent9].[TotalPrice]) AS [A1] 24 FROM [dbo].[OrderDetail] AS [Extent9] 25 WHERE [Extent6].[Id] = [Extent9].[OrderId]) AS [C1] 26 FROM [dbo].[Order] AS [Extent6] 27 INNER JOIN [dbo].[City] AS [Extent7] ON [Extent6].[CityId] = [Extent7].[Id] 28 INNER JOIN [dbo].[User] AS [Extent8] ON [Extent6].[UserId] = [Extent8].[Id] ) AS [Project4] 29 LEFT OUTER JOIN [dbo].[User] AS [Extent10] ON [Project4].[UserId] = [Extent10].[Id] 30 WHERE [Project4].[C1] > cast(500 as decimal(18)) ) AS [Filter4] 31 LEFT OUTER JOIN [dbo].[User] AS [Extent11] ON [Filter4].[UserId] = [Extent11].[Id] 32 WHERE ([Filter4].[FullName] LIKE @p__linq__0 ESCAPE N'~') AND (([Filter4].[UserName1] = @p__linq__1) OR (([Filter4].[UserName1] IS NULL) AND (@p__linq__1 IS NULL))) AND (([Project3].[Work] = [Filter4].[Work]) OR (([Project3].[Work] IS NULL) AND ([Filter4].[Work] IS NULL))) 33 ) AS [Project5]) AS [C1] 34 FROM ( SELECT 35 [Distinct1].[Work] AS [Work] 36 FROM ( SELECT DISTINCT 37 [Extent5].[Work] AS [Work] 38 FROM (SELECT 39 [Extent1].[UserId] AS [UserId], 40 [Extent2].[FullName] AS [FullName], 41 [Extent3].[UserName] AS [UserName], 42 (SELECT 43 SUM([Extent4].[TotalPrice]) AS [A1] 44 FROM [dbo].[OrderDetail] AS [Extent4] 45 WHERE [Extent1].[Id] = [Extent4].[OrderId]) AS [C1] 46 FROM [dbo].[Order] AS [Extent1] 47 INNER JOIN [dbo].[City] AS [Extent2] ON [Extent1].[CityId] = [Extent2].[Id] 48 INNER JOIN [dbo].[User] AS [Extent3] ON [Extent1].[UserId] = [Extent3].[Id] ) AS [Project1] 49 LEFT OUTER JOIN [dbo].[User] AS [Extent5] ON [Project1].[UserId] = [Extent5].[Id] 50 WHERE ([Project1].[C1] > cast(500 as decimal(18))) AND ([Project1].[FullName] LIKE @p__linq__0 ESCAPE N'~') AND (([Project1].[UserName] = @p__linq__1) OR (([Project1].[UserName] IS NULL) AND (@p__linq__1 IS NULL))) 51 ) AS [Distinct1] 52 ) AS [Project3] 53 ) AS [Project6] 54 ) AS [Project7] 55 ORDER BY [Project7].[Work] ASC
复杂语句三，再来一个看看，用到了分页。
这次由于比较复杂，所以生成Sql也花费了一些时间，可以看出来已经到的4、5ms左右，但是生成的Sql确比上次的少。
SELECT
[Project3].[Id] AS [Id],
[Project3].[UserName] AS [UserName],
[Project3].[Name] AS [Name],
[Project3].[Amount] AS [Amount],
[Project3].[C1] AS [C1]
FROM ( SELECT
[Project2].[Id] AS [Id],
[Project2].[UserName] AS [UserName],
[Project2].[Amount] AS [Amount],
[Project2].[Name] AS [Name],
[Project2].[C1] AS [C1]
FROM ( SELECT
[Project1].[Id] AS [Id],
[Extent5].[UserName] AS [UserName],
[Extent5].[Amount] AS [Amount],
[Extent6].[Name] AS [Name],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[OrderDetail] AS [Extent7]
WHERE [Project1].[Id] = [Extent7].[OrderId]) AS [C1]
FROM (SELECT
[Extent1].[Id] AS [Id],
[Extent1].[UserId] AS [UserId],
[Extent1].[CityId] AS [CityId],
[Extent2].[FullName] AS [FullName],
[Extent3].[UserName] AS [UserName],
(SELECT
SUM([Extent4].[TotalPrice]) AS [A1]
FROM [dbo].[OrderDetail] AS [Extent4]
WHERE [Extent1].[Id] = [Extent4].[OrderId]) AS [C1]
FROM [dbo].[Order] AS [Extent1]
INNER JOIN [dbo].[City] AS [Extent2] ON [Extent1].[CityId] = [Extent2].[Id]
INNER JOIN [dbo].[User] AS [Extent3] ON [Extent1].[UserId] = [Extent3].[Id] ) AS [Project1]
LEFT OUTER JOIN [dbo].[User] AS [Extent5] ON [Project1].[UserId] = [Extent5].[Id]
LEFT OUTER JOIN [dbo].[City] AS [Extent6] ON [Project1].[CityId] = [Extent6].[Id]
WHERE ([Project1].[C1] > cast(500 as decimal(18))) AND ([Project1].[FullName] LIKE @p__linq__0 ESCAPE N'~') AND (([Project1].[UserName] = @p__linq__1) OR (([Project1].[UserName] IS NULL) AND (@p__linq__1 IS NULL)))
) AS [Project2]
WHERE ([Project2].[Amount] > cast(50 as decimal(18))) AND ([Project2].[Amount] < cast(500 as decimal(18)))
) AS [Project3]
ORDER BY [Project3].[Amount] DESC
OFFSET 28 ROWS FETCH NEXT 14 ROWS ONLY
2.和数据库的时间对比
这是博主又想到的一个笨方法，就是点击按钮的时候记下当前的时间，然后去数据库的Profile里边获取监视到的开始时间，因为这里考虑的网络传输Sql语句的时间，但是由于是本机传送，所以应该不会耗费很多时间，那么我们就来对比一下，也就可以大致估算出生成sql语句所用的时间了。如下图：
下面来看统计结果：
预期结果为差值大于后边的生成sql的时间(肯定的啊)，里边有两次时间为负，可能是其他原因导致的 客户端开始时间记录产生的误差，从这里可以看出 ，因为生成sql的时间必然要小于差值，所以生成sql的时间还是很短的。
再来看一张图：
从下边的结果可以看出，传输时间相对于生成sql的时间还是挺长的，这也再一次说明了，EF生成sql语句的时间很短，几乎可以忽略。所以EF的性能瓶颈可以排除在生成的sql语句时间长上。
2.查询数据
下面我们就根据实际的业务需要查询一波数据，看看结果到底怎么样。代码如下：
需求1：查询最近六个月下单的用户的部分信息（用户名，余额，下单日期），并按照下单日期排序进行分页（涉及到两个100w数据表的链接User表和Order表）
生成sql语句，中规中矩。
1 SELECT 2 [Project1].[UserId] AS [UserId], 3 [Project1].[UserName] AS [UserName], 4 [Project1].[Amount] AS [Amount], 5 [Project1].[OrderDate] AS [OrderDate] 6 FROM ( SELECT 7 [Extent1].[UserId] AS [UserId], 8 [Extent1].[OrderDate] AS [OrderDate], 9 [Extent2].[UserName] AS [UserName], 10 [Extent2].[Amount] AS [Amount] 11 FROM [dbo].[Order] AS [Extent1] 12 INNER JOIN [dbo].[User] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[Id] 13 WHERE [Extent1].[OrderDate] > @p__linq__0 14 ) AS [Project1] 15 ORDER BY [Project1].[OrderDate] DESC 16 OFFSET 2000 ROWS FETCH NEXT 20 ROWS ONLY
代码如下：
查询结果如下：
可以看出来表现很不错，时间大概在70ms左右，是非常可以接受的。至于这里为什么生成sql的时间长了，那是因为在生成sql的前边做了一次Count查询，所以这里的生成sql的时间是无效的。前边已经证明过生成sql的时间是可以忽略不计的。
需求2：查询最近六个月订单总金额大于1000的订单，获取用户和订单详情的部分信息，并按照下单日期排序进行分页（涉及到三个100w数据表的链接User表和Order表、OrderDetail表）
生成的sql:
1 SELECT 2 [Project4].[Id] AS [Id], 3 [Project4].[UserName] AS [UserName], 4 [Project4].[Amount] AS [Amount], 5 [Project4].[OrderDate] AS [OrderDate], 6 [Project4].[C1] AS [C1], 7 [Project4].[C2] AS [C2] 8 FROM ( SELECT 9 [Project3].[Id] AS [Id], 10 [Project3].[OrderDate] AS [OrderDate], 11 [Project3].[UserName] AS [UserName], 12 [Project3].[Amount] AS [Amount], 13 [Project3].[C1] AS [C1], 14 [Project3].[C2] AS [C2] 15 FROM ( SELECT 16 [Project2].[Id] AS [Id], 17 [Project2].[OrderDate] AS [OrderDate], 18 [Project2].[UserName] AS [UserName], 19 [Project2].[Amount] AS [Amount], 20 [Project2].[C1] AS [C1], 21 (SELECT 22 SUM([Extent5].[TotalPrice]) AS [A1] 23 FROM [dbo].[OrderDetail] AS [Extent5] 24 WHERE [Project2].[Id] = [Extent5].[OrderId]) AS [C2] 25 FROM ( SELECT 26 [Project1].[Id] AS [Id], 27 [Project1].[OrderDate] AS [OrderDate], 28 [Extent3].[UserName] AS [UserName], 29 [Extent3].[Amount] AS [Amount], 30 (SELECT 31 COUNT(1) AS [A1] 32 FROM [dbo].[OrderDetail] AS [Extent4] 33 WHERE [Project1].[Id] = [Extent4].[OrderId]) AS [C1] 34 FROM (SELECT 35 [Extent1].[Id] AS [Id], 36 [Extent1].[UserId] AS [UserId], 37 [Extent1].[OrderDate] AS [OrderDate], 38 (SELECT 39 SUM([Extent2].[TotalPrice]) AS [A1] 40 FROM [dbo].[OrderDetail] AS [Extent2] 41 WHERE [Extent1].[Id] = [Extent2].[OrderId]) AS [C1] 42 FROM [dbo].[Order] AS [Extent1] ) AS [Project1] 43 LEFT OUTER JOIN [dbo].[User] AS [Extent3] ON [Project1].[UserId] = [Extent3].[Id] 44 WHERE ([Project1].[OrderDate] > @p__linq__0) AND ([Project1].[C1] > cast(1000 as decimal(18))) 45 ) AS [Project2] 46 ) AS [Project3] 47 ) AS [Project4] 48 ORDER BY [Project4].[OrderDate] DESC 49 OFFSET 2080 ROWS FETCH NEXT 20 ROWS ONLY
查询结果：
查询用了330ms左右，还是可以接受的。
需求3：查询订单总价格大于1000的数据，并按时间降续排列，取前10000条的用户的部分信息，并且对着10000条按账户余额排序，再进行分页处理。
这里可以说是连接了四个表的（User表，Order表，OrderDetail表，City表），其中三个表都是100w的数据
查询了十次，我们来看查询时间
已经1s多的时间，可以说是有点慢了。（注意，这里在查询出来之前先是按日期排序再取10000条，这个排序是很耗费性能的，这里也是一个我们以后需要优化的地方）但是，对，说到但是了，于是乎，楼主把生成的sql语句复制到数据库中直接查询，结果也是很长的。
所以说，这应该是数据库方面的问题的，这里肯定不是EF生成sql语句的时间问题，前边已经说明过了，至于是不是EF生成的sql语句的质量问题，我就不知道了。
五、关于时间概念
我们做的产品或者项目都是服务于用户的，所以我们要以用户的角度看待问题，那就是用户的体验问题。
1.关于页面的响应时间，引用了网上的一点资料，百度的标准是3s以下，我们暂且定为2s以下，以Asp.Net Mvc为例 如果我们在控制器里拿数据并渲染到页面上，拿数据时间应该在1s（1000ms）以下才可以。
2.现在越来越流行单页面web应用，所以一般都是ajax请求异步拿数据，首先说明一点，拿数据最耗时的就是在数据库里的查询，传输时间也有，但是在现在这么高的带宽下，完全可以忽略不计，但是说也是白说，大家还是以实际中的体验来做标准吧
园子里的博客分页应该是异步加载，就以此为例看看。
1.700ms左右的体验：
2.300ms左右的体验：
3.200ms左右的体验：
4.100ms左右的体验：
具体体验大家可以亲自感受一下，谷歌浏览器调试工具可以设置当前网速，博主本着求真务实的思想，认为实际项目中如果不是非常非常注重用户的体验，我们的拿数据的时间可以控制在250ms以下也是可以接受的，100ms以下的时间已经是有点浪费了，在这里是给大家一个时间概念参考一下。
按着这个标准，我感觉EF在百万级的数据下还是非常可以接受的，毕竟博主测试的都是自己的电脑，实际项目运行在服务器上，服务器的配置肯定是相当高的，肯定也会提高不少性能。
六、总结
1.EF可以说是不存在生成sql语句时间长方面的瓶颈，至于生成sql语句的质量，可能真的有性能影响，但是这些东西也是开发人员写的，所以这个锅EF还是不能背，还应该是开发人员的锅。
2.对于简单的连接查询，EF生成的sql语句应该不存在质量问题，应该和开发人员写的差不多，但是对于复杂的查询，EF确实生成了一大堆的sql语句，但是开发人员面对这么复杂的查询，还不一定能写出来呢（反正我现在是写不出来），即使花费一上午写了出来，那么再花费一下午调试，一天过去了，这时候你对你们经理说，我考虑到性能问题，不想用自动生成的sql语句。那么你基本可以卷铺盖走人了。（哈哈），所以基于这个角度，我觉得还是乖乖用生成的sql查询吧。
3.对于百万级以上的数据，表连接最好控制在3个以内，我这里不是针对EF，是针对所有在座的数据库。（请自动脑补星爷电影里的桥段）
4.本文只做测试功能，可能会有一些偏差，大家用时还是请以实际项目为准。毕竟有博友几百万的数据连接查询也同样高效：
5.关于怎么用EF写出高效的查询，我相信这也是一个很值得研究的话题，以后有时间的话博主还会继续研究，关于这方面希望大家也踊跃为博主提供一些资料，也希望有做DBA的朋友提出一些sql语句方面的优化建议，毕竟博主也是只能一个个试来试去。
6.还是那句话，我只是想知道，我该什么情况下用EF，怎么用EF来发挥出他的优势，怎么能用好EF，应用到实际生产环境中。也为更多的喜欢EF的人和不了解EF的人提供一些帮助。
附：转载请注明出处，楼主一个一个测试也是很不容易，感谢大家的支持。