基于索引的单表查询,是 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)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 之上,以声明化的方式,对过滤功能进行描述。
常见注解如下:
注解 |
含义 |
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,以提升开发速度。