基于索引的单表查询，是 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 常见的查询优化手段非常多：

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 的示意图：

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

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

索引

MySQL 中最常见的索引包括：

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

如下图所示：

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

看一个回表的例子：

table: id, category, publisher, status, title\
index: 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）。
• 每个查询，必须有对应的索引对性能进行保障，也就是所有的查询必须走索引。
• 谨慎处理入参和返回值。

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

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

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

注解

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

常见注解如下：

针对之前的 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，以提升开发速度。