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

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

1. 应用场景

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

整体架构如下图所示：

image

这是一个简单的分层架构，主要有：

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

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

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

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

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

查询入参如下：

@Datapublic 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.1. MyBatis

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

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

@Autowiredprivate 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);}

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

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

@Autowiredprivate 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());}

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

这种开发模型，存在以下几个问题：

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

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

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

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

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

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

2.1.1. B+Tree

B+Tree 在 MySQL 中是如此重要，它是 MySQL 使用的默认索引。B+Tree 索引不仅可以加速单个键值查询，还可以支持范围查找并为查询结果排序。此外，B+Tree 还可以支持高效的插入和删除操作，当在一个 B+Tree 索引中插入或删除记录时，B+Tree 索引通过特定规则进行拆分和合并来实现重新平衡。\

在 MySQL 中，B+Tree 索引不仅适用于普通表，还适用于主键索引、唯一索引、辅助索引等。因此，了解 B+Tree 索引的设计和原理对于开发高效、可扩展的 MySQL 应用程序至关重要。

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

image

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

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

MySQL 中最常见的索引包括：

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

如下图所示：

image

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

看一个回表的例子：

table: id, category, publisher, status, title\

index: idx_categity(category,status)

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

执行逻辑如图所示：

image

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

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

2.1.3. 高性能查询

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

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

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

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

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

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

对于一个查询请求，需要具备：

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

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

// 可以支持多组高效查询// User维度查询对象@Datapublic class QueryOrderByUser {    // user id 不能为 null，不然无法使用索引    @NotNull    private Long userId;    private Integer status;    private Pageable pageable;}// User 和 Status 维度查询@Datapublic 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 框架支持；

框架整体流程如下：

image

该模式下，开发查询功能只需：

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

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

核心功能全部在 QueryRepository 中，其核心流程如下：

image

流程如下：

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

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

image

核心模块包括：

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

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

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

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

常见注解如下：

注解

含义

FieldEqualTo

等于

FieldGreaterThan

大于

FieldGreaterThanOrEqualTo

大于等于

FieldIn

in 操作

FieldIsNull

是否为 null

FieldLessThan

小于

FieldLessThanOrEqualTo

小于等于

FieldNotEqualTo

不等于

FieldNotIn

not in

EmbeddedFilter

嵌入查询对象

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

@Datapublic 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 提供不同的实现。

3.2. MyBatis 支持

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

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

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

整体架构如下：

image

核心流程如下：

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

整体架构如下：

image

核心流程如下：

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