easy-query暴打efcore(包括其他所有orm)，隐式Group看我如何在子查询做到极致的性能天花板
介绍
文档地址 https://www.easy-query.com/easy-query-doc/
GITHUB地址 https://github.com/dromara/easy-query
GITEE地址 https://gitee.com/dromara/easy-query
标题为什么这么自信，eq做了很多ORM这么多年都没有解决的子查询性能问题，算是子查询最后一块拼图。平时怎么写子查询现在还是怎么写，但是会将多个相同子查询比如where子查询 order by子查询或者 select子查询进行合并，这就是隐式Group 解决的不是性能问题，而是解决了阅读性和性能双重问题。
本次测试代码都在地址处点击
背景
几个月前其实我已经注意到了一篇帖子《从30秒到30毫秒：EF Core查询性能优化实战全记录》，感兴趣的小伙伴可以自行网上收一下，讲的是如何通过优化提高efcore的性能。
我们对其进行简化，简化部分需求，来讲解本次主题，为什么easy-query这么强,为什么可以这么智能
本次使用的是MYSQL 8本地环境 java8和efcore9
实体关系如下：
- 用户：每个用户有多篇帖子和多条评论
- 评论：每条评论属于一个用户并关联一篇帖子
- 分类：帖子按分类组织
- 帖子：每个帖子所属一个类目 并且有多个评论
挑战需求：查询过去7天在".NET"分类下评论最多的5位用户，
每位用户返回：
隐式Group
一种更加智能的sql生成，完美的融合了表达式的阅读性和性能，在复杂子查询嵌套和多子查询下合并有种非常高的性能提升，且表达式本身不需要任何变化，是一种非常智能的表达式转变。
先说结论
| 框架耗时 easy-query普通子查询 efcore子查询 easy-query隐式Group | | mysql8 | 11s | 11s | 2.7s |
创建实体
点击查看实体代码- @AllArgsConstructor @NoArgsConstructor @Builder @Data @EntityProxy @Table("t_user") public class User implements ProxyEntityAvailable { private String id; private String username; @Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {UserProxy.Fields.id}, targetProperty = {PostProxy.Fields.userId}) private List posts; @Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {UserProxy.Fields.id}, targetProperty = {CommentProxy.Fields.userId}) private List comments; } @AllArgsConstructor @NoArgsConstructor @Builder @Data @EntityProxy @Table("t_post") public class Post implements ProxyEntityAvailable { private String id; private String content; private String userId; private String categoryId; @Navigate(value = RelationTypeEnum.ManyToOne, selfProperty = {PostProxy.Fields.categoryId}, targetProperty = {CategoryProxy.Fields.id}) private Category category; @Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {PostProxy.Fields.id}, targetProperty = {CommentProxy.Fields.postId}) private List comments; } @AllArgsConstructor @NoArgsConstructor @Builder @Data @EntityProxy @Table("t_category") public class Category implements ProxyEntityAvailable { private String id; private String name; @Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {CategoryProxy.Fields.id}, targetProperty = {PostProxy.Fields.categoryId}) private List posts; } @AllArgsConstructor @NoArgsConstructor @Builder @Data @EntityProxy @Table("t_comment") public class Comment implements ProxyEntityAvailable { private String id; private String userId; private String postId; private String text; private LocalDateTime createAt; @Navigate(value = RelationTypeEnum.ManyToOne, selfProperty = {CommentProxy.Fields.postId}, targetProperty = {PostProxy.Fields.id}) private Post post; }
点击查看初始化代码- private static void initData() { DatabaseCodeFirst databaseCodeFirst = entityQuery.getDatabaseCodeFirst(); databaseCodeFirst.createDatabaseIfNotExists(); CodeFirstCommand codeFirstCommand1 = databaseCodeFirst.dropTableIfExistsCommand(Arrays.asList(User.class, Post.class, Category.class, Comment.class)); codeFirstCommand1.executeWithTransaction(s->s.commit()); CodeFirstCommand codeFirstCommand = databaseCodeFirst.syncTableCommand(Arrays.asList(User.class, Post.class, Category.class, Comment.class)); codeFirstCommand.executeWithTransaction(s -> { System.out.println(s.getSQL()); s.commit(); }); String[] categories = { ".NET", "Java", "Python", "PHP", "Go", "Ruby", "Swift", "Kotlin", "JavaScript" }; String[] users = { "张三", "李四", "王五", "赵六", "孙七", "周八", "吴九", "郑十", "小王", "小李", "小张" , "小赵", "小孙", "小周", "小吴", "小郑" }; // === 检查并插入分类 === boolean hasCategories = entityQuery.queryable(Category.class).any(); List insertCategories = new ArrayList<>(); if (!hasCategories) { for (String item : categories) { Category category = new Category(); category.setId(UUID.randomUUID().toString()); category.setName(item); insertCategories.add(category); } entityQuery.insertable(insertCategories).executeRows(); } else { insertCategories = entityQuery.queryable(Category.class).toList(); } // === 检查并插入用户 === boolean hasUsers = entityQuery.queryable(User.class).any(); List insertUsers = new ArrayList<>(); if (!hasUsers) { for (String item : users) { User user = new User(); user.setId(UUID.randomUUID().toString()); user.setUsername(item); insertUsers.add(user); } entityQuery.insertable(insertUsers).executeRows(); } else { insertUsers = entityQuery.queryable(User.class).toList(); } // === 生成帖子 === int postCount = 150_000; List posts = new ArrayList<>(postCount); Random random = new Random(); for (int i = 0; i < postCount; i++) { Post post = new Post(); post.setId(UUID.randomUUID().toString()); post.setContent("这是第 " + (i + 1) + " 篇帖子，主题是关于 " + categories[random.nextInt(categories.length)]); post.setUserId(insertUsers.get(random.nextInt(insertUsers.size())).getId()); post.setCategoryId(insertCategories.get(random.nextInt(insertCategories.size())).getId()); posts.add(post); } // === 生成评论 === int commentCount = 1000_000; LocalDateTime now = LocalDateTime.now(); List comments = new ArrayList<>(commentCount); for (int i = 0; i < commentCount; i++) { Comment comment = new Comment(); comment.setId(UUID.randomUUID().toString()); comment.setPostId(posts.get(random.nextInt(posts.size())).getId()); comment.setUserId(insertUsers.get(random.nextInt(insertUsers.size())).getId()); comment.setText("评论内容 " + (i + 1)); // 生成近30天内随机时间（包含随机天数、小时、分钟、秒） long randomSeconds = random.nextInt(30 * 24 * 3600); // 30天内的随机秒数 comment.setCreateAt(now.minusSeconds(randomSeconds)); comments.add(comment); } System.out.println("✅ 数据初始化完成：用户=" + insertUsers.size() + " 分类=" + insertCategories.size() + " 帖子=" + posts.size() + " 评论=" + comments.size()); entityQuery.insertable(posts).batch().executeRows(); entityQuery.insertable(comments).batch().executeRows(); }
普通子查询- LocalDateTime dateTime = LocalDateTime.now().plusDays(-7); List list = entityQuery.queryable(User.class) .where(u -> { u.comments().any(); }) .orderBy(u -> { u.comments().where(c -> { c.createAt().isAfter(dateTime); c.post().category().name().eq(".NET"); }).count().desc(); }) .limit(5).toList();
- SELECT t.`id`, t.`username` FROM `t_user` t WHERE EXISTS (SELECT 1 FROM `t_comment` t1 WHERE t1.`user_id` = t.`id` LIMIT 1) ORDER BY (SELECT COUNT(*) FROM `t_comment` t2 LEFT JOIN `t_post` t3 ON t3.`id` = t2.`post_id` LEFT JOIN `t_category` t4 ON t4.`id` = t3.`category_id` WHERE t2.`user_id` = t.`id` AND t2.`create_at` > '2025-10-19 22:28:18.469' AND t4.`name` = '.NET') DESC LIMIT 5
efcore
点击查看实体代码- public class User { public string Id { get; set; } public string Username { get; set; } = null!; public ICollection Posts { get; set; } = new List(); public ICollection Comments { get; set; } = new List(); } public class Post { public string Id { get; set; } public string Content { get; set; } = null!; public string UserId { get; set; } public User User { get; set; } = null!; public string CategoryId { get; set; } public Category Category { get; set; } = null!; public ICollection Comments { get; set; } = new List(); } public class Category { public string Id { get; set; } public string Name { get; set; } = null!; public ICollection Posts { get; set; } = new List(); } public class Comment { public string Id { get; set; } public string UserId { get; set; } public User User { get; set; } = null!; public string PostId { get; set; } public Post Post { get; set; } = null!; public string Text { get; set; } = null!; public DateTime CreatedAt { get; set; } }
- var dateTime = DateTime.Now.AddDays(-7); var users = context.Set() .AsNoTracking() .Where(u =>u.Comments.Any()) .OrderByDescending(u => u.Comments .Count(c => c.CreatedAt >= dateTime && c.Post.Category.Name ==".NET") ) .Take(5) .ToList();
- Executed DbCommand (11,016ms) [Parameters=[@__dateTime_0='2025-10-19T22:32:06.7108910+08:00' (DbType = DateTime), @__p_1='5'], CommandType='Text', CommandTimeout='30'] SELECT `t`.`id`, `t`.`username` FROM `t_user` AS `t` WHERE EXISTS ( SELECT 1 FROM `t_comment` AS `t0` WHERE `t`.`id` = `t0`.`user_id`) ORDER BY ( SELECT COUNT(*) FROM `t_comment` AS `t1` INNER JOIN `t_post` AS `t2` ON `t1`.`post_id` = `t2`.`id` INNER JOIN `t_category` AS `t3` ON `t2`.`category_id` = `t3`.`id` WHERE (`t`.`id` = `t1`.`user_id`) AND ((`t1`.`create_at` >= @__dateTime_0) AND (`t3`.`name` = '.NET'))) DESC LIMIT @__p_1
从结果看两者都是性能相当的，那么为什么本次的主题是暴打efcore呢？
接下来我为大家带来easy-query的究极子查询功能，将子查询转成groupJoin也就是隐式查询的隐式Group可以说.net应该没有ORM做到了这个功能
隐式Group
修改easy-query查询添加一行配置，是的你没听错就一行配置- LocalDateTime dateTime = LocalDateTime.now().plusDays(-7); List list = entityQuery.queryable(User.class) .configure(s->s.getBehavior().add(EasyBehaviorEnum.ALL_SUB_QUERY_GROUP_JOIN)) .where(u -> { u.comments().any(); }) .orderBy(u -> { u.comments().where(c -> { c.createAt().isAfter(dateTime); c.post().category().name().eq(".NET"); }).count().desc(); }) .limit(5).toList();
- SELECT t.`id`, t.`username` FROM `t_user` t LEFT JOIN (SELECT t1.`user_id` AS `userId`, (COUNT(*) > 0) AS `__any2__`, COUNT((CASE WHEN t1.`create_at` > '2025-10-19 22:30:12.833' AND t4.`name` = '.NET' THEN 1 ELSE NULL END)) AS `__count3__` FROM `t_comment` t1 LEFT JOIN `t_post` t3 ON t3.`id` = t1.`post_id` LEFT JOIN `t_category` t4 ON t4.`id` = t3.`category_id` GROUP BY t1.`user_id`) t2 ON t2.`userId` = t.`id` WHERE IFNULL(t2.`__any2__`, false) = true ORDER BY IFNULL(t2.`__count3__`, 0) DESC LIMIT 5
鱼和熊掌可以兼得
eq真正的做到了将子查询优化到无敌的存在，可以说目前市面上没有任何一款orm做到了如此智能的sql，性能和可维护性真的可以在easy-query上做到兼得。
具体eq的相关文档点击此处
插曲
我在看《从30秒到30毫秒：EF Core查询性能优化实战全记录》这个文章的时候看到
这个表达式，我随机在efcore上试了一下结果性能大跌眼镜要足足22秒也不知道原文是怎么测试的？？？- var dateTime = DateTime.Now.AddDays(-7); var users = context.Set() .AsNoTracking() .Where(u => u.Comments.Any(c => c.CreatedAt >= dateTime && c.Post.Category.Name ==".NET")) .Select(u =>new { u.Id, u.Username, CommentsCount = u.Comments.Count(c => c.CreatedAt >= dateTime && c.Post.Category.Name ==".NET") }) .OrderByDescending(u => u.CommentsCount) .Take(5) .ToList();
- Executed DbCommand (22,952ms) [Parameters=[@__dateTime_0='2025-10-19T22:48:18.9933730+08:00' (DbType = DateTime), @__p_1='5'], CommandType='Text', CommandTimeout='30'] SELECT `t`.`id` AS `Id`, `t`.`username` AS `Username`, ( SELECT COUNT(*) FROM `t_comment` AS `t6` INNER JOIN `t_post` AS `t7` ON `t6`.`post_id` = `t7`.`id` INNER JOIN `t_category` AS `t8` ON `t7`.`category_id` = `t8`.`id` WHERE (`t`.`id` = `t6`.`user_id`) AND ((`t6`.`create_at` >= @__dateTime_0) AND (`t8`.`name` = '.NET'))) AS `CommentsCount` FROM `t_user` AS `t` WHERE EXISTS ( SELECT 1 FROM `t_comment` AS `t0` INNER JOIN `t_post` AS `t1` ON `t0`.`post_id` = `t1`.`id` INNER JOIN `t_category` AS `t2` ON `t1`.`category_id` = `t2`.`id` WHERE (`t`.`id` = `t0`.`user_id`) AND ((`t0`.`create_at` >= @__dateTime_0) AND (`t2`.`name` = '.NET'))) ORDER BY ( SELECT COUNT(*) FROM `t_comment` AS `t3` INNER JOIN `t_post` AS `t4` ON `t3`.`post_id` = `t4`.`id` INNER JOIN `t_category` AS `t5` ON `t4`.`category_id` = `t5`.`id` WHERE (`t`.`id` = `t3`.`user_id`) AND ((`t3`.`create_at` >= @__dateTime_0) AND (`t5`.`name` = '.NET'))) DESC LIMIT @__p_1
合并子查询
别说eq欺负efcore，eq可以再额外查询的时候加一个子查询依然可以做到暴打efcore- LocalDateTime dateTime = LocalDateTime.now().plusDays(-7); entityQuery.queryable(User.class) .configure(s->s.getBehavior().add(EasyBehaviorEnum.ALL_SUB_QUERY_GROUP_JOIN)) .where(u -> { u.comments().any(); }) .orderBy(u -> { u.comments().where(c -> { c.createAt().isAfter(dateTime); c.post().category().name().eq(".NET"); }).count().desc(); }) .limit(5) .select(u -> Select.DRAFT.of( u.id(), u.username(), u.comments().count() //额外加一个用户评论数量 )).toList();
- SELECT t.`id` AS `value1`, t.`username` AS `value2`, IFNULL(t2.`__count4__`, 0) AS `value3` FROM `t_user` t LEFT JOIN (SELECT t1.`user_id` AS `userId`, (COUNT(*) > 0) AS `__any2__`, COUNT((CASE WHEN t1.`create_at` > '2025-10-19 23:14:34.908' AND t4.`name` = '.NET' THEN 1 ELSE NULL END)) AS `__count3__`, COUNT(*) AS `__count4__` FROM `t_comment` t1 LEFT JOIN `t_post` t3 ON t3.`id` = t1.`post_id` LEFT JOIN `t_category` t4 ON t4.`id` = t3.`category_id` GROUP BY t1.`user_id`) t2 ON t2.`userId` = t.`id` WHERE IFNULL(t2.`__any2__`, false) = true ORDER BY IFNULL(t2.`__count3__`, 0) DESC LIMIT 5
最后的最后
我非常感谢您能看到这边我相信eq绝对是你不二的orm选择 给c#转java的所有用户一个最好的抉择
框架地址 https://github.com/dromara/easy-query
文档地址 https://www.easy-query.com/easy-query-doc/
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
照妖镜
