easy-query暴打efcore(包括其他所有orm)，隐式Group看我如何在子查询做到极致的性能天花板

介绍

标题为什么这么自信，eq做了很多ORM这么多年都没有解决的子查询性能问题，算是子查询最后一块拼图。平时怎么写子查询现在还是怎么写，但是会将多个相同子查询比如where子查询 order by子查询或者 select子查询进行合并，这就是隐式Group 解决的不是性能问题，而是解决了阅读性和性能双重问题。

本次测试代码都在地址处点击

背景

几个月前其实我已经注意到了一篇帖子《从30秒到30毫秒：EF Core查询性能优化实战全记录》，感兴趣的小伙伴可以自行网上收一下，讲的是如何通过优化提高efcore的性能。

我们对其进行简化，简化部分需求，来讲解本次主题，为什么easy-query这么强,为什么可以这么智能

本次使用的是MYSQL 8本地环境 java8和efcore9

实体关系如下：

用户：每个用户有多篇帖子和多条评论
评论：每条评论属于一个用户并关联一篇帖子
分类：帖子按分类组织
帖子：每个帖子所属一个类目并且有多个评论

挑战需求：查询过去7天在".NET"分类下评论最多的5位用户，
每位用户返回：

用户ID
用户名

隐式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<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;
}

通过easy-query的code-first生成数据库表，并且生成初始化数据，其中初始化数据为 9个类目，16个用户发布了15w个帖子并且一个评论100w条记录

点击查看初始化代码


    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[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<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();

    }

查询数据库

普通子查询

  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();

最终生成的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

平均耗时11秒左右

efcore

点击查看实体代码


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; }
}

查询数据库

     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();

生成的sql如下

      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

平均耗时11秒

从结果看两者都是性能相当的，那么为什么本次的主题是暴打efcore呢？

接下来我为大家带来easy-query的究极子查询功能，将子查询转成groupJoin也就是隐式查询的隐式Group可以说.net应该没有ORM做到了这个功能

隐式Group

修改easy-query查询添加一行配置，是的你没听错就一行配置

    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();

生成的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

平均耗时2.7秒是的你妹看错仅仅只需要一行代码即可让子查询性能提升4-5倍这是什么概念，这就是easy-query带来的最强子查询 隐式Group

鱼和熊掌可以兼得

eq真正的做到了将子查询优化到无敌的存在，可以说目前市面上没有任何一款orm做到了如此智能的sql，性能和可维护性真的可以在easy-query上做到兼得。

具体eq的相关文档点击此处

插曲

我在看《从30秒到30毫秒：EF Core查询性能优化实战全记录》这个文章的时候看到

这个表达式，我随机在efcore上试了一下结果性能大跌眼镜要足足22秒也不知道原文是怎么测试的？？？

 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();

生成的sql

      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

耗时22秒，当然也有可能是mysql的驱动优化的太辣鸡了。

合并子查询

别说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();

生成的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

最终平均耗时2.7-2.8秒，所以如果你有认真再看那么你就应该知道eq有多少强了，当之无愧《java最强orm没有之一》，如果你是efcore粉丝那么应该去github提一个issue让efcore也支持该功能，这个功能是eq在多年实际业务开发时获得的灵感目前已经使用近一年时间，包括我和很多群里的小伙伴都可以作为这个功能的见证者

最后的最后

我非常感谢您能看到这边我相信eq绝对是你不二的orm选择给c#转java的所有用户一个最好的抉择

框架地址 https://github.com/dromara/easy-query
文档地址 https://www.easy-query.com/easy-query-doc/

posted @ 2025-10-27 08:39 薛家明阅读(228) 评论(2) 收藏举报

刷新页面返回顶部

薛家明

easy-query暴打efcore(包括其他所有orm)，隐式Group看我如何在子查询做到极致的性能天花板

easy-query暴打efcore(包括其他所有orm)，隐式Group看我如何在子查询做到极致的性能天花板

介绍

背景

隐式Group

先说结论

创建实体

查询数据库

efcore

查询数据库

隐式Group

鱼和熊掌可以兼得

插曲

合并子查询

最后的最后

公告