基于索引的单表查询，是 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<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 常见的查询优化手段非常多：
- 索引优化：分析表数据和查询需求，创建合适的索引来提高查询效率。
- 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, 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）。
- 每个查询，必须有对应的索引对性能进行保障，也就是所有的查询必须走索引。
- 谨慎处理入参和返回值。
- 对入参进行严格验证，避免因为参数丢失或参数过多造成的性能问题。
- 对返回值进行验证，避免一次性返回过多数据操作性能问题。
规范
对于一个查询请求，需要具备：
- 统一使用 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 框架支持。
框架整体流程如下：
该模式下，开发查询功能只需：
- 根据业务需求定义 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<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);
}
}整体架构如下：
核心流程如下：
- 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，以提升开发速度。
