easy-query暴打efcore(包括其他所有orm)，隐式Group看我如何在子查询做到极致的性能天花板
<h1 id="easy-query暴打efcore包括其他所有orm隐式group看我如何在子查询做到极致的性能天花板">easy-query暴打efcore(包括其他所有orm)，隐式Group看我如何在子查询做到极致的性能天花板</h1><h2 id="介绍">介绍</h2>
<p>文档地址 https://www.easy-query.com/easy-query-doc/</p>
<p>GITHUB地址 https://github.com/dromara/easy-query</p>
<p>GITEE地址 https://gitee.com/dromara/easy-query</p>
<p><font color="red"><strong>标题为什么这么自信，<code>eq</code>做了很多ORM这么多年都没有解决的子查询性能问题，算是子查询最后一块拼图。平时怎么写子查询现在还是怎么写，但是会将多个相同子查询比如where子查询 order by子查询或者 select子查询进行合并，这就是隐式Group 解决的不是性能问题，而是解决了阅读性和性能双重问题。</strong></font></p>
<p>本次测试代码都在地址处点击</p>
<h2 id="背景">背景</h2>
<p>几个月前其实我已经注意到了一篇帖子《从30秒到30毫秒：EF Core查询性能优化实战全记录》，感兴趣的小伙伴可以自行网上收一下，讲的是如何通过优化提高efcore的性能。</p>
<p>我们对其进行简化，简化部分需求，来讲解本次主题，为什么easy-query这么强,为什么可以这么智能</p>
<p>本次使用的是MYSQL 8本地环境 java8和efcore9</p>
<p>实体关系如下：</p>
<ul>
<li>用户：每个用户有多篇帖子和多条评论</li>
<li>评论：每条评论属于一个用户并关联一篇帖子</li>
<li>分类：帖子按分类组织</li>
<li>帖子：每个帖子所属一个类目 并且有多个评论</li>
</ul>
<p>挑战需求：查询过去7天在".NET"分类下评论最多的5位用户，<br>
每位用户返回：</p>
<ul>
<li>用户ID</li>
<li>用户名</li>
</ul>
<h2 id="隐式group">隐式Group</h2>
<p>一种更加智能的sql生成，完美的融合了表达式的阅读性和性能，在复杂子查询嵌套和多子查询下合并有种非常高的性能提升，且表达式本身不需要任何变化，是一种非常智能的表达式转变。</p>
<h2 id="先说结论">先说结论</h2>
<table>
<thead>
<tr>
<th>框架耗时</th>
<th>easy-query普通子查询</th>
<th>efcore子查询</th>
<th>easy-query隐式Group</th>
</tr>
</thead>
<tbody>
<tr>
<td>mysql8</td>
<td>11s</td>
<td>11s</td>
<td>2.7s</td>
</tr>
</tbody>
</table>
<h2 id="创建实体">创建实体</h2>
<details>
<summary>点击查看实体代码</summary>
<pre><code class="language-java">
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
@EntityProxy
@Table("t_user")
public class User implements ProxyEntityAvailable<User, UserProxy> {
private String id;
private String username;
@Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {UserProxy.Fields.id}, targetProperty = {PostProxy.Fields.userId})
private List<Post> posts;
@Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {UserProxy.Fields.id}, targetProperty = {CommentProxy.Fields.userId})
private List<Comment> comments;
}
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
@EntityProxy
@Table("t_post")
public class Post implements ProxyEntityAvailable<Post , PostProxy> {
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<Comment> comments;
}
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
@EntityProxy
@Table("t_category")
public class Category implements ProxyEntityAvailable<Category, CategoryProxy> {
private String id;
private String name;
@Navigate(value = RelationTypeEnum.OneToMany, selfProperty = {CategoryProxy.Fields.id}, targetProperty = {PostProxy.Fields.categoryId})
private List<Post> posts;
}
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
@EntityProxy
@Table("t_comment")
public class Comment implements ProxyEntityAvailable<Comment, CommentProxy> {
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;
}
</code></pre>
</details>
<p>通过easy-query的code-first生成数据库表，并且生成初始化数据，其中初始化数据为 9个类目，16个用户 发布了15w个帖子并且一个评论100w条记录</p>
<details>
<summary>点击查看初始化代码</summary>
<pre><code class="language-java">
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<Category> 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<User> 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<Post> 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);
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<Comment> 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();
}
</code></pre>
</details>
<h2 id="查询数据库">查询数据库</h2>
<p>普通子查询</p>
<pre><code class="language-java">LocalDateTime dateTime = LocalDateTime.now().plusDays(-7);
List<User> 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();
</code></pre>
<p>最终生成的sql为</p>
<pre><code class="language-sql">
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
</code></pre>
<p>平均耗时11秒左右</p>
<h2 id="efcore">efcore</h2>
<details>
<summary>点击查看实体代码</summary>
<pre><code class="language-c#">
public class User
{
public string Id { get; set; }
public string Username { get; set; } = null!;
public ICollection<Post> Posts { get; set; } = new List<Post>();
public ICollection<Comment> Comments { get; set; } = new List<Comment>();
}
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<Comment> Comments { get; set; } = new List<Comment>();
}
public class Category
{
public string Id { get; set; }
public string Name { get; set; } = null!;
public ICollection<Post> Posts { get; set; } = new List<Post>();
}
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; }
}
</code></pre>
</details>
<h3 id="查询数据库-1">查询数据库</h3>
<pre><code class="language-c#"> var dateTime = DateTime.Now.AddDays(-7);
var users = context.Set<User>()
.AsNoTracking()
.Where(u =>u.Comments.Any())
.OrderByDescending(u => u.Comments
.Count(c =>
c.CreatedAt >= dateTime &&
c.Post.Category.Name ==".NET")
)
.Take(5)
.ToList();
</code></pre>
<p>生成的sql如下</p>
<pre><code class="language-sql"> Executed DbCommand (11,016ms) , 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
</code></pre>
<p>平均耗时11秒</p>
<p>从结果看两者都是性能相当的，那么为什么本次的主题是暴打efcore呢？</p>
<p>接下来我为大家带来easy-query的究极子查询功能，将子查询转成<code>groupJoin</code>也就是隐式查询的<code>隐式Group</code>可以说.net应该没有ORM做到了这个功能</p>
<h2 id="隐式group-1">隐式Group</h2>
<p>修改easy-query查询添加一行配置，是的你没听错就一行配置</p>
<pre><code class="language-java"> LocalDateTime dateTime = LocalDateTime.now().plusDays(-7);
List<User> 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();
</code></pre>
<p>生成的sql如下</p>
<pre><code class="language-sql"> 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
</code></pre>
<p>平均耗时2.7秒 是的你妹看错仅仅只需要一行代码即可让子查询性能提升4-5倍这是什么概念，这就是easy-query带来的最强子查询 <code>隐式Group</code></p>
<h2 id="鱼和熊掌可以兼得">鱼和熊掌可以兼得</h2>
<p><code>eq</code>真正的做到了将子查询优化到无敌的存在，可以说目前市面上没有任何一款orm做到了如此智能的sql，性能和可维护性真的可以在easy-query上做到兼得。</p>
<p>具体<code>eq</code>的相关文档点击此处</p>
<h2 id="插曲">插曲</h2>
<p>我在看《从30秒到30毫秒：EF Core查询性能优化实战全记录》这个文章的时候看到<br>
<img src="https://img2024.cnblogs.com/blog/1346660/202510/1346660-20251026225507735-1447210006.png"></p>
<p>这个表达式，我随机在efcore上试了一下结果性能大跌眼镜要足足22秒也不知道原文是怎么测试的？？？</p>
<pre><code class="language-c#"> var dateTime = DateTime.Now.AddDays(-7);
var users = context.Set<User>()
.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();
</code></pre>
<p>生成的sql</p>
<pre><code class="language-sql"> Executed DbCommand (22,952ms) , 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
</code></pre>
<p>耗时22秒，当然也有可能是mysql的驱动优化的太辣鸡了。</p>
<h2 id="合并子查询">合并子查询</h2>
<p>别说<code>eq</code>欺负efcore，eq可以再额外查询的时候加一个子查询依然可以做到暴打efcore</p>
<pre><code class="language-java">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();
</code></pre>
<p>生成的sql如下</p>
<pre><code class="language-sql"> 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
</code></pre>
<p>最终平均耗时2.7-2.8秒，所以如果你有认真再看那么你就应该知道<code>eq</code>有多少强了，当之无愧《java最强orm没有之一》，如果你是efcore粉丝那么应该去github提一个issue让efcore也支持该功能，这个功能是<code>eq</code>在多年实际业务开发时获得的灵感目前已经使用近一年时间，包括我和很多群里的小伙伴都可以作为这个功能的见证者</p>
<h2 id="最后的最后">最后的最后</h2>
<p>我非常感谢您能看到这边我相信eq绝对是你不二的orm选择 给c#转java的所有用户一个最好的抉择</p>
<p>框架地址 https://github.com/dromara/easy-query<br>
文档地址 https://www.easy-query.com/easy-query-doc/</p><br><br>
来源：https://www.cnblogs.com/xuejiaming/p/19167564
頁:
[1]