DDD死党:单引擎查询利器

2023年 12月 19日 82.2k 0

基于索引的单表查询,是 MySQL 正确打开方式!

基于 QueryObject 的声明式查询,是简单查询的正确使用方式!

1、应用场景

单表查询在业务开发中占比最大,是所有 CRUD Boy 的入门必备,所有人在 JavaBean 和 SQL 之间乐此不疲。

整体架构如下图所示:

这是一个简单的分层架构,主要有:

  • 接入层:接收用户或其他服务的请求,对参数进行基本验证。
  • 服务层:执行简单的业务逻辑,比如业务验证、数据转换、数据组装等。
  • 数据访问层。在 ORM 框架基础之上完成对数据库的访问。
  • 数据库层。负责数据存储和查询。

其中 ORM 框架尤为重要,帮我们完成 对象 与 关系数据 间的相互转换。因此,不少人认为玩好 ORM 就成为了高级开发人员。而实际情况是:该部分是最枯燥、最没有技术含量的“技能”。

目前,最常见的 ORM 便是 MyBatis 和 JPA,以一个简单的分页查询 User 为例做一个简短介绍。

按照用户状态分页查询 User 信息:

  • 用户状态和分页参数必填。
  • 其他参数手机号、生日区间选填。

查询入参如下:

@Data
public class QueryUserByStatus {
    private Integer status;
    private String mobile;
    private Date birthAfter;
    private Date birthBefore;
    private Pageable pageable;
}

接口签名如下:

Page queryByStatus(QueryUserByStatus queryByStatus);

这个是最简单的 case,分别使用 MyBatis 和 Jpa 进行实现。

(1)MyBatis

MyBatis是一款基于 Java 语言的持久层框架,它为SQL映射、数据处理和事务管理提供了优秀的支持。MyBatis已成为使用最广泛的ORM框架之一,它支持极为灵活的自定义SQL,同时也提供了与Spring Framework和Spring Boot等流行框架的集成方案,为Java程序员提供了极大的便利。

基于MyBatis实现的核心代码如下:

@Autowired
private MyBatisUserMapper userMapper;
public Page queryByStatus(QueryUserByStatus query){
    // 状态不填
    if (query.getStatus()  null){
        throw new IllegalArgumentException("status can not null");
    }
    // 分页必填
    if (query.getPageable()  null){
        throw new IllegalArgumentException("pageable can not null");
    }
    MyBatisUserExample userExample = new MyBatisUserExample();
    MyBatisUserExample.Criteria criteria = userExample.createCriteria();
    // 添加状态过滤
    criteria.andStatusEqualTo(query.getStatus());
    // 添加手机号过滤
    if (query.getMobile() != null){
        criteria.andMobileEqualTo(query.getMobile());
    }
    // 添加生日过滤
    if (query.getBirthAfter() != null){
        criteria.andBirthAtGreaterThan(query.getBirthAfter());
    }
    // 添加生日过滤
    if (query.getBirthBefore() != null){
        criteria.andBirthAtLessThan(query.getBirthBefore());
    }
    // 添加分页信息
    userExample.setOffset(query.getPageable().offset());
    userExample.setRows(query.getPageable().getPageSize());
    // 查询数据
    long totalItems = this.userMapper.countByExample(userExample);
    List users = this.userMapper.selectByExample(userExample);
    // 封装结果
    return new Page(users, query.getPageable(), totalItems);
}

(2)Jpa

JPA是Java Persistence API(Java持久化API)的简称,它是Sun官方提供的一套标准的ORM框架(对象关系映射框架)。JPA提供了一种以面向对象方式来管理关系型数据库的方法,使开发人员可以使用对象而不是SQL来操作数据库。JPA提供了一套公共的API,使开发人员可以在不同的ORM实现(如Hibernate、EclipseLink等)中自由切换。

基于Jpa实现的核心代码如下:

@Autowired
private JpaUserRepository jpaUserRepository;
public Page queryByStatus(QueryUserByStatus queryByStatus){
    // 状态必填
    if (queryByStatus.getStatus()  null){
        throw new IllegalArgumentException("status can not null");
    }
    // 分页必填
    if (queryByStatus.getPageable()  null){
        throw new IllegalArgumentException("pageable can not null");
    }
    // 构建分页参数
    Pageable pageable = PageRequest.of(queryByStatus.getPageable().getPageNo(), queryByStatus.getPageable().getPageSize());
    // 构建过滤条件
    Specification spec = Specification.where((root, query, cb) -> {
        List predicates = Lists.newArrayList();
        // 添加状态过滤
        Predicate statusPredicate = cb.equal(root.get("status"), queryByStatus.getStatus());
        predicates.add(statusPredicate);
        // 添加手机过滤
        if (queryByStatus.getMobile() != null){
            Predicate mobilePredicate = cb.equal(root.get("mobile") , queryByStatus.getMobile());
            predicates.add(mobilePredicate);
        }
        // 添加生日过滤
        if (queryByStatus.getBirthAfter() != null){
            Predicate birthAfterPredicate = cb.greaterThan(root.get("birthAt") , queryByStatus.getBirthAfter());
            predicates.add(birthAfterPredicate);
        }
        // 添加生日过滤
        if (queryByStatus.getBirthBefore() != null){
            Predicate birthBeforePredicate = cb.lessThan(root.get("birthAt") , queryByStatus.getBirthBefore());
            predicates.add(birthBeforePredicate);
        }
        // 组合过滤条件
        return cb.and(predicates.toArray(new Predicate[predicates.size()]));
    });
    // 查询数据
    org.springframework.data.domain.Page all = this.jpaUserRepository.findAll(spec, pageable);
    // 封装结果
    return new Page(all.getContent(), queryByStatus.getPageable(), all.getTotalElements());
}

(3)问题分析

通常情况下,使用哪个 ORM 框架,都是由公司规范规定,一般人没办法左右。但,无论使用哪个框架,面对的问题基本是一致的。

这种开发模型,存在以下几个问题:

  • 过于繁琐,开发效率低:一个简单的查询请求,包括参数验证、ORM API调用、数据转换等工作,涉及多个层次多个类的协调一致,常见问题包括:
  • 重复性劳动:没有什么技术含量,首先是使用 “字段” 或 “属性” 调用各种 API,然后是各种类型间的转化,枯燥无味。
  • 容易出错:涉及参数和字段较多,容易设置错位,比如参数设置错误、对象转换时字段设置错误等。
  • 性能瓶颈:实际开发中,性能瓶颈并没有在 ORM 框架本身,主要是对 MySQL 使用不当,特别是没有发挥索引的优势,常见问题包括:
  • 没有合适索引:设计之初并未考虑索引,或者对索引缺乏有效的管理。
  • 参数丢失导致无法使用索引:参数丢失导致最左匹配原则被破坏,无法高效的使用索引。
  • 返回结果过多导致性能低下:一次性返回大量数据,增加 DB 和 应用程序的负载,最终导致性能低下。

2、MySQL 查询正确打开方式

MySQL 常见的查询优化手段非常多:

  • 索引优化:分析表数据和查询需求,创建合适的索引来提高查询效率。
  • SQL语句优化:优化SQL语句的写法,避免使用子查询、联合查询、多层嵌套等耗费资源的操作。
  • 数据库结构优化:合理设计数据库结构,避免冗余数据以及过多分表分库导致性能低下。
  • 控制结果集大小:查询的结果集越大,查询时间就越长。尽量限制结果集大小,避免不必要的计算。
  • 数据库连接池优化:通过优化数据库连接池的配置,避免连接池满载以及连接超时等问题,提高数据库处理效率。
  • 数据库批量操作优化:通过批量操作来减少单次与数据库的交互次数,提高执行效率。
  • 在众多优化方式中选择最主要的一项便是:索引优化:

  • 提升基于 WHERE 条件的查询性能:在 WHERE 条件中使用了索引,可以更快地定位到匹配行,避免全表扫描。
  • 提升基于范围查询的查询性能:如果仅需要一个范围,而不是整个表的数据,索引可以提高查询效率。
  • 提升排序和分组查询性能:索引可以让 MySQL 更快地执行排序和聚合,快速定位数据,而不是遍历整个表。
  • (1)B+Tree 与 高效查询

    B+Tree 在 MySQL 中极为重要,它既是一种数据的组织结构,比如聚簇索引。又是查询优化最重要的一种手段,比如非聚簇索引。

    B+Tree

    B+Tree 在 MySQL 中是如此重要,它是 MySQL 使用的默认索引。B+Tree 索引不仅可以加速单个键值查询,还可以支持范围查找并为查询结果排序。此外,B+Tree 还可以支持高效的插入和删除操作,当在一个 B+Tree 索引中插入或删除记录时,B+Tree 索引通过特定规则进行拆分和合并来实现重新平衡。在 MySQL 中,B+Tree 索引不仅适用于普通表,还适用于主键索引、唯一索引、辅助索引等。因此,了解 B+Tree 索引的设计和原理对于开发高效、可扩展的 MySQL 应用程序至关重要。

    以下是一个 B+Tree 的示意图:

    B+Tree作为一种数据组织方式,有以下几个特点:

    • 非叶子节点只存储关键字和页码,而不保存数据。这也是B+Tree和B-Tree的主要区别,这种特性使得B+Tree可以更快的查找特定关键字。
    • 叶子节点包含所有数据和关键字,形成一个有序链表。这种结构使得B+Tree在范围查询时更高效。
    • 支持高效范围查找,基于在叶子节点形成的有序链表,可以更快地查找满足查询条件的数据。
    • 支持快速的插入和删除,基本上所有的操作都可以在O(log n)的时间复杂度内完成。
    • 分级结构可以支持多级索引查找,充分利用磁盘I/O缓存和预取来提高查询效率。

    索引

    MySQL 中最常见的索引包括:

    • 聚簇索引(主键索引):一个表只能有一个聚簇索引,对应表的数据存储方式,即数据按照聚簇索引来排序和存储,叶节点存储了完整的数据行。在使用聚簇索引进行查找时,只需查找一次聚簇索引就能找到需要的数据行。
    • 非聚簇索引(辅助索引):一个表可以有多个非聚簇索引,节点存储了完整的索引和指向数据行信息(指针或主键)。查询时需要查找两次索引,第一次查询索引信息,第二次查找数据行。

    如下图所示:

    这种先查辅助索引再查主键索引的行为,我们称之为“回表”。

    看一个回表的例子:

    table: id, category, publisher, status, titleindex: idx_categity(category,status)

    查询语句:select * from tb_news where category = 2 and publisher = 14

    执行逻辑如图所示:

    • 索引中存在 category 列,category = 2 的过滤在引擎层完成,返回数据的主键。
    • 引擎完成 category = 2 过滤后,需要 publisher 和全部数据,所以进行回表操作。
    • 从主键索引表中获取全部数据,在内存中执行 publisher = 14 的过滤。
    • 将满足条件的数据放入到 Result 中进行返回。

    一般情况下,回表的性能损失还是可接受的,可以在发现问题后进行处理。可将更多精力放在提升研发效率上。

    (2)高性能查询

    基于 B+Tree 数据结构的特点,在以下场景可以高效使用索引:

    • 全值匹配:与索引中的所有列进行匹配;
    • 匹配最左前缀:并非与索引中的所有列进行匹配,从索引左侧进行匹配。
    • 匹配列前缀:匹配某一列的开头部分(like ‘aaa%’)。
    • 匹配范围值: 大于、等于、小于等。
    • 精确匹配列然后范围匹配:先精确匹配,然后进行范围匹配。
    • 只访问索引查询:如果索引中存在查询所需所有数据,就没有必要追溯原数据。
    • 支持查询中的order by、group by:order by、group by 与 where 条件组合,如果符合最左匹配,及可提升性能。

    以下几种情况无法使用索引:

    • 不是从最左列开始查询,无法使用索引。
    • 不能跳过索引中的列对后面的列进行查询。
    • 如果索引使用范围查询,则后面所有列无法使用索引进行优化。

    (3)查询规范

    在了解 MySQL B+Tree 的内部实现之后,可以推导出一套规范,来对查询性能进行保障。

    原则

    • 仅使用 MySQL 的单表查询,避免多表 Join 引入的性能问题(多表查询解决方案见:内存Join)。
    • 每个查询,必须有对应的索引对性能进行保障,也就是所有的查询必须走索引。
    • 谨慎处理入参和返回值。
  • 对入参进行严格验证,避免因为参数丢失或参数过多造成的性能问题。
  • 对返回值进行验证,避免一次性返回过多数据操作性能问题。
  • 规范

    对于一个查询请求,需要具备:

    • 统一使用 Query Object 模式,对入参进行封装,以便接口的升级和扩展。
    • 每一组查询,可以存在: get(单条返回值)、list(多条返回值)、count(统计计数)、page(分页)开头的多个方法,操作后面紧跟 By + 维度。
    • 维度结构应该与表的索引结构保持一致,以保障所有的查询,都能应用索引。
    • 索引维度体现在方法签名中,并且保障满足最左匹配原则。
    • 多维索引,可以基于最左匹配原则生成多组方法;索引列(A,B),可以生成 A、AandB 两组方法。

    假如在order表中存在一个索引(user_id, status),那么可以存在以下查询:

    // 可以支持多组高效查询
    // User维度查询对象
    @Data
    public class QueryOrderByUser {
        // user id 不能为 null,不然无法使用索引
        @NotNull
        private Long userId;
        private Integer status;
        private Pageable pageable;
    }
    // User 和 Status 维度查询
    @Data
    public class QueryOrderByUserAndStatus {
        // user id 不能为 null,不然无法使用索引
        @NotNull
        private Long userId;
        // status 不能为 null,不然无法使用索引
        @NotNull
        private Integer status;
        private Pageable pageable;
    }
    // 查询服务如下
    public interface OrderService {
        // User 维度查询
        List listByUser(QueryOrderByUser query);
        Long countByUser(QueryOrderByUser query);
        Page pageByUser(QueryOrderByUser query);
        // User 和 Status 维度查询
        List listByUserAndStatus(QueryOrderByUserAndStatus query);
        Long countByUserAndStatus(QueryOrderByUserAndStatus query);
        Page pageByUserAndStatus(QueryOrderByUserAndStatus query);
    }

    这样便可以在性能和扩展性间找到一个良好的平衡点。

    • 性能。由 MySQL 的索引进行保障,可能不是最优解(存在回表)但绝对不是最差情况。
    • 扩展性。默认查询维度(get、list、count、page)基本能满足日常业务开发;查询条件也可基于 Query Object 进行扩展;

    3、框架与标准化

    我们需要一个框架,在满足原则和规范前提下,灵活的定制简单数据查询,但又不能过于灵活,需要对使用方式进行严格限制。

    灵活定制,快速开发,提升效率,降低bug;对使用进行限制,是为了将掌控权控制在开发,不会因为使用不当造成线上问题。因此,对框架有如下要求:

    • 支持灵活的查询定义,无需手写 SQL。
    • 支持常见的查询,包括过滤、排序、分页等。
    • 多 ORM 支持,提供对 MyBatis 和 Jpa 框架支持。

    框架整体流程如下:

    该模式下,开发查询功能只需:

    • 根据业务需求定义 QueryObject,主要包括过滤、排序、分页等。
    • 使用 QueryObject 调用 QueryRepository 相关接口完成查询,常见功能包括:单条查询、列表查询、计数查询、分页查询等。

    只需在QueryObject上进行定义,无需编写 SQL,由框架对 QueryObject 进行解析,完成动态查询。

    核心功能全部在 QueryRepository 中,其核心流程如下:

    流程如下:

    • 验证参数:基于 Spring Validate 框架完成基本的参数校验。
    • 解析QueryObject:从 QueryObject 中提取信息,转为为 ORM 的查询对象。
    • 设置最大返回值:【可配】设置最大返回值,避免结果太多造成性能低下。
    • 执行查询:调用 ORM 框架的查询接口执行查询命令。
    • 处理查询结果:【可配】对查询结果进行处理打印日志 or 异常中断。

    为了支持多个 ORM 框架,整体结构设计如下:

    核心模块包括:

    • API:提供统一的接口和配置能力,对使用方式进行规范;。
    • MyBatis 实现:基于 MyBatis 实现 API 中定义的全部功能,完成与 MyBatis 框架的集成。
    • JPA 实现:基于 JPA 实现 API 中定义的全部功能,完成与 JPA 框架的集成。

    (1)统一 API

    提供统一的接口和配置能力,对使用方式进行规范。其中包括两大部分:

  • 注解:使用注解在 QueryObject 的字段上添加配置信息,使其具备过滤能力;
  • QueryRepository接口:提供一组标准的 API,实现常见的 get、list、count 和 page 查询;
  • 注解

    注解配置于 QueryObject 之上,以声明化的方式,对过滤功能进行描述。

    常见注解如下:

    注解

    含义

    FieldEqualTo

    等于

    FieldGreaterThan

    大于

    FieldGreaterThanOrEqualTo

    大于等于

    FieldIn

    in 操作

    FieldIsNull

    是否为 null

    FieldLessThan

    小于

    FieldLessThanOrEqualTo

    小于等于

    FieldNotEqualTo

    不等于

    FieldNotIn

    not in

    EmbeddedFilter

    嵌入查询对象

    针对之前的 User 查询实例,对应的 查询对象定义如下:

    @Data
    public class QueryUserByStatus {
        // 状态相等
        @FieldEqualTo("status")
        @NotNull
        private Integer status;
        // 手机号相等
        @FieldEqualTo("mobile")
        private String mobile;
        // 生日比该值大
        @FieldGreaterThan("birthAt")
        private Date birthAfter;
        // 生日比该值小
        @FieldLessThan("birthAt")
        private Date birthBefore;
        // 自动具备分页能力
        private Pageable pageable;
    }

    接口

    有了 QueryObject 之后,需要一组查询 API 以满足各个场景需求,标准的 API 接口定义如下:

    public interface QueryObjectRepository {
        // 检查查询对象的有效性
        void checkForQueryObject(Class cls);
        // 单条查询
         E get(Q query);
        // 分页查询
        default  R get(Q query, Function converter) {
            E entity = this.get(query);
            return entity  null ? null : converter.apply(entity);
        }
        // 统计查询
         Long countOf(Q query);
        // 列表查询
        default  List listOf(Q query, Function converter) {
            List entities = this.listOf(query);
            return CollectionUtils.isEmpty(entities) ? Collections.emptyList() : (List)entities.stream().filter(Objects::nonNull).map(converter).filter(Objects::nonNull).collect(Collectors.toList());
        }
        // 列表查询
         List listOf(Q query);
        // 分页查询
        default  Page pageOf(Q query, Function converter) {
            Page entityPage = this.pageOf(query);
            return entityPage  null ? null : entityPage.convert(converter);
        }
        // 分页查询
         Page pageOf(Q query);
    }

    集成示例

    有了 QueryObject 和 API 之后,便可以轻松完成各种查询:

    public class SingleQueryService {
        @Autowired
        private QueryObjectRepository repository;
        public List listByStatus(QueryUserByStatus query){
            return repository.listOf(query);
        }
        public Long countByStatus(QueryUserByStatus query){
            return this.repository.countOf(query);
        }
        public Page pageByStatus(QueryUserByStatus query){
            return this.repository.pageOf(query);
        }
    }

    万事具备,只欠最后的 QueryObjectRepository 实现,针对不同的 ORM 提供不同的实现。

    (2)MyBatis 支持

    基于 MyBatis Generator 的 Example 机制实现,需要配置相关的 Generator 以生成 EntityExample 对象。

    直接继承BaseReflectBasedExampleSingleQueryRepository,注入 Mapper 实现,指定好 Example 类即可,具体如下:

    @Service
    public class MyBatisBasedQueryRepository extends BaseReflectBasedExampleSingleQueryRepository {
        // 注入 MyBatis 的 Mapper 类
        public MyBatisBasedQueryRepository(MyBatisUserMapper mapper) {
            // 指定查询所需的 Example 类
            super(mapper, MyBatisUserExample.class);
        }
    }

    整体架构如下:

    核心流程如下:

    • ExampleConverter 将输入的 QueryObject 转换为 XXXExample 实例。
    • 使用 XXXExample 实例 调用 XXXMapper 的 selectByExample 方法获取返回值。
    • 返回值通过 ResultConverter 将 Entity 转换为最终结果。

    其中,从 QueryObject 到 Example 实例的转换为框架的核心,主要包括如下几部分:

    • Pageable。从 QueryObject 中读取 Pageable 属性,并设置 Example 对象的 offset 和 rows 属性。
    • Sort。从 QueryObject 中读取 Sort 属性,并设置 Example 对象的 orderByClause 属性。
    • 过滤注解。遍历 QueryObject 中属性,根据注解查找到注解处理器,由注解处理器为 Example 添加 Criteria,以进行数据过滤。

    (3)Jpa 支持

    基于 JPA 框架的 JpaSpecificationExecutor 实现,EntityRepository 需继承 JpaSpecificationExecutor 接口。

    直接继承BaseSpecificationQueryObjectRepository,注入 JpaSpecificationExecutor 和 实体对象即可,具体如下:

    public class JpaBasedQueryRepository extends BaseSpecificationQueryObjectRepository {
        // 注入 JpaUserRepository 和 specificationConverterFactory(框架自动生成)
        public JpaBasedQueryRepository(JpaUserRepository userRepository,
                                       SpecificationConverterFactory
                                               specificationConverterFactory) {
            // 指定实体对象 JpaUser
            super(userRepository, JpaUser.class, specificationConverterFactory);
        }
    }

    整体架构如下:

    核心流程如下:

    • SpecificationConverter 将输入的 QueryObject 转换为 Specification、Pageable、Sort等实例。
    • 使用 SpecificationExecutor 实例的查询方法获取返回值。
    • 返回值通过 ResultConverter 将 Entity 转换为最终结果。

    其中,从 QueryObject 到相关输入参数的转换为框架的核心,主要包括如下几部分:

    • Pageable。从 QueryObject 中读取 Pageable 属性,并转化为 Spring data 的 Pageable 实例。
    • Sort。从 QueryObject 中读取 Sort 属性,并转换为Spring data 的 Sort 实例。
    • 过滤注解。遍历 QueryObject 中属性,根据注解查找到注解处理器,由注解处理器将其转化为 Predicate 实例,最终封装为 Specification 示例。

    4、小结

    本文从一个日常开发场景出发,提出两个关键问题:

    • 代码过于繁琐,容易出错,同时开发效率低下。
    • 对性能设计关注不足,容易遗漏,产生性能问题。

    对于性能问题,从 MySQL B+Tree 进行推演,总结出该场景下的最佳使用实践,并将其提取为规范。

    对于代码繁琐问题,提出通过在 QueryObject 上增加注解的方式来实现简单查询。

    两者相结合,便形成了 Single Query 框架:

    • 提供一套注解,应用于 QueryObject 之上,以声明化的方式完成查询定义。
    • 提供一套API,以 QueryObject 作为参数,提供 单条、批量、统计、分页等查询。
    • 提供MyBatis和Jpa两套实现,快速实现 QueryObjectRepository,以提升开发速度。

    相关文章

    Oracle如何使用授予和撤销权限的语法和示例
    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
    社区版oceanbase安装
    Oracle 导出CSV工具-sqluldr2
    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

    发布评论