服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - DDD死党:单引擎查询利器

DDD死党:单引擎查询利器

2023-12-19 01:00未知服务器之家 Mysql

基于索引的单表查询,是 MySQL 正确打开方式! 基于 QueryObject 的声明式查询,是简单查询的正确使用方式! 1、应用场景 单表查询在业务开发中占比最大,是所有 CRUD Boy 的入门必备,所有人在 JavaBean 和 SQL 之间乐此不疲。 整体架

DDD死党:单引擎查询利器

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

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

1、应用场景

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

整体架构如下图所示:

DDD死党:单引擎查询利器

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

  • 接入层:接收用户或其他服务的请求,对参数进行基本验证。
  • 服务层:执行简单的业务逻辑,比如业务验证、数据转换、数据组装等。
  • 数据访问层。在 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<User> 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<MyBatisUser> 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<MyBatisUser> 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<JpaUser> 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<JpaUser> spec = Specification.where((root, query, cb) -> {
        List<Predicate> 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<JpaUser> 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 常见的查询优化手段非常多:

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

在众多优化方式中选择最主要的一项便是:索引优化:

  1. 提升基于 WHERE 条件的查询性能:在 WHERE 条件中使用了索引,可以更快地定位到匹配行,避免全表扫描。
  2. 提升基于范围查询的查询性能:如果仅需要一个范围,而不是整个表的数据,索引可以提高查询效率。
  3. 提升排序和分组查询性能:索引可以让 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 的示意图:

DDD死党:单引擎查询利器

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

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

索引

MySQL 中最常见的索引包括:

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

如下图所示:

DDD死党:单引擎查询利器

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

看一个回表的例子:

table: id, category, publisher, status, title\
index: idx_categity(category,status)

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

执行逻辑如图所示:

DDD死党:单引擎查询利器

  • 索引中存在 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)。
  • 每个查询,必须有对应的索引对性能进行保障,也就是所有的查询必须走索引。
  • 谨慎处理入参和返回值。
  1. 对入参进行严格验证,避免因为参数丢失或参数过多造成的性能问题。
  2. 对返回值进行验证,避免一次性返回过多数据操作性能问题。

规范

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

  • 统一使用 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<Order> listByUser(QueryOrderByUser query);
    Long countByUser(QueryOrderByUser query);
    Page<Order> pageByUser(QueryOrderByUser query);
    // User 和 Status 维度查询
    List<Order> listByUserAndStatus(QueryOrderByUserAndStatus query);
    Long countByUserAndStatus(QueryOrderByUserAndStatus query);
    Page<Order> pageByUserAndStatus(QueryOrderByUserAndStatus query);
}

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

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

3、框架与标准化

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

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

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

框架整体流程如下:

DDD死党:单引擎查询利器

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

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

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

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

DDD死党:单引擎查询利器

流程如下:

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

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

DDD死党:单引擎查询利器

核心模块包括:

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

(1)统一 API

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

  1. 注解:使用注解在 QueryObject 的字段上添加配置信息,使其具备过滤能力;
  2. 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<E> {
    // 检查查询对象的有效性
    void checkForQueryObject(Class cls);
    // 单条查询
    <Q> E get(Q query);
    // 分页查询
    default <Q, R> R get(Q query, Function<E, R> converter) {
        E entity = this.get(query);
        return entity  null ? null : converter.apply(entity);
    }
    // 统计查询
    <Q> Long countOf(Q query);
    // 列表查询
    default <Q, R> List<R> listOf(Q query, Function<E, R> converter) {
        List<E> entities = this.listOf(query);
        return CollectionUtils.isEmpty(entities) ? Collections.emptyList() : (List)entities.stream().filter(Objects::nonNull).map(converter).filter(Objects::nonNull).collect(Collectors.toList());
    }
    // 列表查询
    <Q> List<E> listOf(Q query);
    // 分页查询
    default <Q, R> Page<R> pageOf(Q query, Function<E, R> converter) {
        Page<E> entityPage = this.pageOf(query);
        return entityPage  null ? null : entityPage.convert(converter);
    }
    // 分页查询
    <Q> Page<E> pageOf(Q query);
}

集成示例

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

public class SingleQueryService {
    @Autowired
    private QueryObjectRepository<JpaUser> repository;
    public List<JpaUser> listByStatus(QueryUserByStatus query){
        return repository.listOf(query);
    }
    public Long countByStatus(QueryUserByStatus query){
        return this.repository.countOf(query);
    }
    public Page<JpaUser> 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);
    }
}

整体架构如下:

DDD死党:单引擎查询利器

核心流程如下:

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

整体架构如下:

DDD死党:单引擎查询利器

核心流程如下:

  • 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,以提升开发速度。

延伸 · 阅读

精彩推荐