1、订单查询优化方案
1.1、为什么要优化订单查询？
1）订单查询是一个高频接口，并且订单数据非常大。
2）面向C端用户的订单查询接口其访问量非常大。
3）对于运营端的订单查询接口虽然访问量不大但由于订单数据较多也需要进行优化，提高查询性能。
1.2、确定优化方向
面向查询类的接口的优化方向是什么呢？
1）使用缓存
将查询的订单数据进行缓存，提高查询性能。
2）优化数据库索引提高查询性能
当缓存中没有时或缓存过期时会从数据库查询数据，通过添加数据库索引去提高数据库的查询性能。
1.3、订单详情优化方案
针对单条订单信息查询接口可以使用缓存进行优化。
对于单条订单信息查询接口通过快照查询接口查询订单的详情信息。
参考AbstractStateMachine类的String getCurrentSnapshotCache(String bizId)快照查询方法，将快照信息缓存到 redis提供查询效率。
根据订单Id查询缓存信息，先从缓存查询如果缓存没有则查询快照表的数据然后保存到缓存中。缓存设置了过期时间是30分钟。
当订单状态变更，此时订单最新状态的快照有变更，会删除快照缓存，当再次查询快照时从数据库查询最新的快照信息进行缓存。
1.4、用户端订单列表优化方案
用户端通过小程序查询订单列表，界面上没有分页查询的按钮，用户端查询订单列表可采用滚动查询的方法。
滚动查询就是一次查询指定数量的记录，不用进行count查询，省去count查询的消耗。
1.4.1、首先查询符合条件的订单ID
由于是滚动查询需要传入滚动ID，这里我们在订单表使用排序字段sort_by作为滚动ID。
滚动ID是一种递增的序列号，按服务预约时间降序排列且滚动ID具有唯一性，滚动ID的规则是：服务预约时间+订单号后5位。
滚动查询方式： 按SORT_BY降序，取小于SORT_BY的n条记录 第一页传入SORT_BY参数为空 从第二页开始传入上一页最后一条记录的SORT_BY
1.4.2、使用覆盖索引优化
根据查询条件查询符合条件的订单ID，这里使用覆盖索引优化的方法。
我们知道在InnoDB存储引擎中有两种索引存储形式：
聚集索引：查询条件只有主键的情况会通过聚集索引查询。
非聚集索引：查询条件有多个，此时为了提高查询效率可以创建多个字段的联合索引，根据非聚集索引找到符合条件主键，如果要查询的列只有索引字段则通过非聚集索引直接拿到字段值返回，如果要查询列有一部分在索引之外此时会进行回表查询聚集索引最终拿到数据。
示例：
user表（id、name、 age、address）
对name、age创建联合索引。
sql1：
select id、name、age from user where name=? and age =?
该查询直接从索引中拿到符合条件的数据，不存在回表查询。
sql2：
select * from user where name=? and age =?
该查询列是select * ，address没有包含在索引中，where条件通过联合索引找到符合条件的主键，再通过主键回表查询聚集索引，最终拿到数据。
覆盖索引是什么呢？
覆盖索引是一种优化手段，上边的sql1就是实现了覆盖索引。
覆盖索引 （ covering index ） 指一个查询语句的执行只需要从非聚集索引中就可以得到查询记录，而不需要回表去查询聚集索引，可以称之为实现了索引覆盖。
根据上边的需求，我们根据查询条件建立联合索引，通过联合索引找到符合条件的订单ID（主键），从索引中找到的符合条件的订单ID无需回表查询聚集索引。
1.4.3、使用订单ID匹配缓存，如果有缓存则直接获取否则从数据库查询
2、订单详情优化
2.1、阅读状态机快照查询代码
在jzo2o-framework的状态机工程jzo2o-statemachine中提供了查询快照缓存的方法，具体在状态机抽象类AbstractStateMachine中
/**
* 获取当前状态的快照缓存
*
* @param bizId 业务id
* @return 快照信息
*/
public String getCurrentSnapshotCache(String bizId) {
//先查询缓存，如果缓存没有就查询数据库然后存缓存
String key = "JZ_STATE_MACHINE:" + name + ":" + bizId;
Object object = redisTemplate.opsForValue().get(key);
if (ObjectUtil.isNotEmpty(object)) {
return object.toString();
}
String bizSnapshot = getCurrentSnapshot(bizId);
redisTemplate.opsForValue().set(key, bizSnapshot, 30, TimeUnit.MINUTES);
return bizSnapshot;
}
状态机会在生成快照的地方进行清理快照缓存，保证再次查询快照缓存时可以查询到最新的快照信息。
在saveSnapshot()方法中保存快照方法和changeStatus()方法中提供了清理快照的代码。
public void saveSnapshot(Long dbShardId, String bizId, StatusDefine statusDefine, T bizSnapshot)
public void changeStatus(Long dbShardId, String bizId, StatusChangeEvent statusChangeEventEnum, T bizSnapshot)
2.2、订单详情查询优化
找到“根据订单id查询”接口，找到调用的查询订单详情的service方法：
@Override
public OrderResDTO getDetail(Long id) {
Orders orders = queryById(id);
//懒加载方式取消支付超时的 订单
orders = canalIfPayOvertime(orders);
OrderResDTO orderResDTO = BeanUtil.toBean(orders, OrderResDTO.class);
return orderResDTO;
}
上边的代码是查询数据库，现在改为查询快照缓存方法，如下：
@Override
public OrderResDTO getDetail(Long id) {
//查询订单
// Orders orders = queryById(id);
//从快照表查询快照
String currentSnapshotJson = orderStateMachine.getCurrentSnapshotCache(String.valueOf(id));
OrderSnapshotDTO orderSnapshotDTO = JsonUtils.toBean(currentSnapshotJson, OrderSnapshotDTO.class);
//懒加载方式取消支付超时的 订单
orderSnapshotDTO = canalIfPayOvertime(orderSnapshotDTO);
OrderResDTO orderResDTO = BeanUtil.toBean(orderSnapshotDTO, OrderResDTO.class);
return orderResDTO;
}
/**
* 如果支付过期则取消订单
* @param orderSnapshotDTO
*/
public OrderSnapshotDTO canalIfPayOvertime(OrderSnapshotDTO orderSnapshotDTO){
//订单状态
Integer ordersStatus = orderSnapshotDTO.getOrdersStatus();
//判断订单是未支付且支付超时(从订单创建时间开始15分钟未支付)
// if(ordersStatus==OrderStatusEnum.NO_PAY.getStatus() && orders.getCreateTime().isBefore(LocalDateTime.now().minusMinutes(15)) ){
if(ordersStatus==OrderStatusEnum.NO_PAY.getStatus() && orderSnapshotDTO.getCreateTime().plusMinutes(15).isBefore(LocalDateTime.now())){
//查询一下最新的支付状态，如果没有支付成功，再执行下边的取消代码
OrdersPayResDTO payResultFromTradServer = ordersCreateService.getPayResultFromTradServer(orderSnapshotDTO.getId());
//如果没有支付成功，再执行下边的取消代码
if(ObjectUtils.isNotNull(payResultFromTradServer) && payResultFromTradServer.getPayStatus()!= OrderPayStatusEnum.PAY_SUCCESS.getStatus()){
OrderCancelDTO orderCancelDTO = BeanUtils.toBean(orderSnapshotDTO,OrderCancelDTO.class);
orderCancelDTO.setCurrentUserType(UserType.SYSTEM);
orderCancelDTO.setCancelReason("订单支付超时系统自动取消");
cancelByNoPay(orderCancelDTO);
//从快照中查询订单数据
String jsonResult = orderStateMachine.getCurrentSnapshotCache(String.valueOf(orderSnapshotDTO.getId()));
orderSnapshotDTO = JSONUtil.toBean(jsonResult, OrderSnapshotDTO.class);
return orderSnapshotDTO;
}
}
return orderSnapshotDTO;
}
3、优化用户端订单列表
3.1、创建索引
3.1.1、理解滚动ID
滚动ID在用户端订单列表查询中至关重要，由于需要按服务预约时间降序排列且滚动ID具有唯一性，滚动ID的规则是：服务预约时间+订单号后5位
根据上边的方案查询滚动ID的规则如下伪代码：
select * from 订单表 where 滚动ID<? and 用户id=? order by 滚动ID desc
在创建订单时向订单表保存滚动ID为：服务开始时间（毫秒）+订单后5位
long sortBy = DateUtils.toEpochMilli(orders.getServeStartTime()) + orders.getId() % 100000;
orders.setSortBy(sortBy);
3.1.2、创建联合索引
在创建索引之前需要明确最终执行的SQL语句，如下：
SELECT id
FROM orders_0
WHERE (orders_status = ? AND user_id = ? AND display = 1 and sort_by<? )
ORDER BY sort_by DESC
LIMIT 10
在查询全部订单时 “orders_status = ?” 条件不需要，即执行SQL
SELECT id
FROM orders_0
WHERE ( user_id = ? AND display = 1 and sort_by<? )
ORDER BY sort_by DESC
LIMIT 10
按照最左前缀法则，查询条件必须包括最左边的索引列并将索引字段的顺序和SQL语句where 条件字段的顺序一致，下边创建两个联合索引：
创建索引query_index_0，满足最上边的SQL语句。
create index query_index_0
on `jzo2o-orders-1`.orders_0 (orders_status asc, user_id asc, display asc, sort_by desc);
创建索引query_index_1，满足最下边的SQL语句。
create index query_index_1
on `jzo2o-orders-1`.orders_0 (user_id asc, display asc, sort_by desc);
并且要注意上边两个SQL的查询都是按sort_by 降序排列，所以索引中 sort_by 为desc降序。
3.1.3、执行计划
- 什么是执行计划
索引创建完成如何知道这个SQL语句是否用到这个索引呢？并且跟踪我们的需求最终要实现覆盖索引。
通过MySQL的执行计划即可知道该SQL语句是否用上了我们创建的索引。
MySQL 的执行计划（Execution Plan）是 MySQL 查询优化器生成的一个描述查询执行方式的计划。它是一个详细的执行策略，指示 MySQL 数据库引擎如何执行查询以及如何获取所需的数据。
当你执行一个 SQL 查询时，MySQL 查询优化器负责决定如何最有效地执行这个查询。优化器会考虑多个因素，包括表的索引、表的大小、查询中的过滤条件等，然后生成一个执行计划，描述了查询的执行步骤和顺序。
使用 EXPLAIN 关键字来获取 MySQL 查询的执行计划，语法格式如下：
EXPLAIN sql语句
对第一个SQL我们加上一些模拟的参数，使用explain获取它的执行计划：
explain
SELECT id
FROM orders_0
WHERE (orders_status = 0 AND user_id = 1716346406098296832 AND display = 1 and sort_by<1698924600022)
ORDER BY sort_by DESC
LIMIT 10
输出：
[
{
"id": 1,
"select_type": "SIMPLE",
"table": "orders_0",
"partitions": null,
"type": "range",
"possible_keys": "query_index_0,query_index_1",
"key": "query_index_0",
"key_len": "26",
"ref": null,
"rows": 1,
"filtered": 100,
"Extra": "Using where; Using index"
}
]
每项的说明：
id： 每个查询步骤的唯一标识符。
select_type： 查询的类型，例如 SIMPLE（简单查询）或 PRIMARY（主查询，嵌套子查询的最外层查询）。
table： 查询涉及的表。
partitions： 使用的分区（如果有分区表的话）。
type： 查询使用的连接类型，例如 ALL（全表扫描）或 index（索引扫描）。
possible_keys： 可能用于此查询的键列表。
key： 实际用于此查询的键。
key_len： 使用的索引长度。
ref： 显示索引的哪一列被用于查询。
rows： MySQL 估计将需要读取的行数。
filtered： 在表中的行数的百分比，表示查询的条件有多少行满足。
Extra： 其他的额外信息，例如使用了哪些索引、是否使用了文件排序等。
如果key、key_len为null说明没有用到索引。
上边的输出说明：使用了query_index_0索引。
另外重点关注 Extra：
- Using where： 表示MySQL正在对检索出来的行进行额外的WHERE条件过滤，最终返回满足WHERE条件的行。
- Using index： 这部分表示MySQL使用了索引来加速查询，但不必回表读取实际的数据行。这种情况通常发生在覆盖索引的情况下，即索引包含了查询所需的所有列，因此MySQL不需要回到数据表中去获取数据，从而提高了查询的性能。
"Using where; Using index"表示使用了覆盖索引，使用了WHERE子句来过滤数据，能够高效地找到并返回满足查询条件的行，而不必浪费资源读取不必要的数据。
- 根据执行计划调试 SQL
如果我们把SQL改为如下内容，不仅查询ID还查询user_id:
explain
SELECT id,user_id
FROM orders_0
WHERE (orders_status = 0 AND user_id = 1716346406098296832 AND display = 1 and sort_by<1698924600022)
ORDER BY sort_by DESC
LIMIT 10
我们发现输出"Extra"仍然是 "Using where; Using index"
这是因为id、user_id都是索引中的字段，仍然满足覆盖索引。
如果改为查询列中是索引中不存在字段呢？下边的SQL添加了serve_item_name查询列。
explain
SELECT id,user_id,serve_item_name
FROM orders_0
WHERE (orders_status = 0 AND user_id = 1716346406098296832 AND display = 1 and sort_by<1698924600022)
ORDER BY sort_by DESC
LIMIT 10
输出：
[
{
"id": 1,
"select_type": "SIMPLE",
"table": "orders_0",
"partitions": null,
"type": "range",
"possible_keys": "query_index_0,query_index_1",
"key": "query_index_0",
"key_len": "26",
"ref": null,
"rows": 1,
"filtered": 100,
"Extra": "Using index condition"
}
]
从key、key_len可以看出使用了索引query_index_0，虽然serve_item_name在索引中不存在但是也使用了query_index_0索引，先从索引中查找满足条件的记录，索引中没有的数据再回表查询。
Using index condition表示：是MySQL一种优化手段，先从索引中查找满足条件的记录，索引中没有的数据再回表查询，可以有效减少回表的次数，大大提升了查询的效率。
如果我们想实现Extra为Using index的效果即覆盖索引该怎么实现呢？
可以在索引中添加serve_item_name字段
create index query_index_0
on `jzo2o-orders-0`.orders_0 (orders_status asc, user_id asc, display asc, sort_by desc, serve_item_name asc);
- 全表扫描
如果我们把SQL语句改为如下方式：
explain
SELECT id,user_id,serve_item_name
FROM orders_0
WHERE (display = 1 and sort_by<1698924600022)
ORDER BY sort_by DESC
LIMIT 10
输出：
[
{
"id": 1,
"select_type": "SIMPLE",
"table": "orders_0",
"partitions": null,
"type": "ALL",
"possible_keys": null,
"key": null,
"key_len": null,
"ref": null,
"rows": 10,
"filtered": 10,
"Extra": "Using where; Using filesort"
}
]
type: ALL
key和key_len为null
说明整个查询是全表查询没有用到索引。虽然我们创建的两个索引中包括了display 和sort_by，根据最左前缀法则 查询条件中没有包含索引中最左边的列，索引无效。
全表扫描通常会比较耗费情况，但也不是绝对的，有时候虽然有索引MySQL也会选择全表扫描的方式，这是因为优化器分析使用全表扫描比使用索引更耗费时间。
3.1.4、创建索引
在三个数据库中对三个订单表创建query_index_0索引
在三个数据库中对三个订单表创建query_index_1索引
3.2、查询订单ID列表==>优化Service方法
原来的代码：
/**
* 滚动分页查询
*
* @param currentUserId 当前用户id
* @param ordersStatus 订单状态，0：待支付，100：派单中，200：待服务，300：服务中，400：待评价，500：订单完成，600：已取消，700：已关闭
* @param sortBy 排序字段
* @return 订单列表
*/
@Override
public List<OrderSimpleResDTO> consumerQueryList(Long currentUserId, Integer ordersStatus, Long sortBy) {
//1.构件查询条件
LambdaQueryWrapper<Orders> queryWrapper = Wrappers.<Orders>lambdaQuery()
.eq(ObjectUtils.isNotNull(ordersStatus), Orders::getOrdersStatus, ordersStatus)
.lt(ObjectUtils.isNotNull(sortBy), Orders::getSortBy, sortBy)
.eq(Orders::getUserId, currentUserId)
.eq(Orders::getDisplay, EnableStatusEnum.ENABLE.getStatus());
Page<Orders> queryPage = new Page<>();
queryPage.addOrder(OrderItem.desc(SORT_BY));
queryPage.setSearchCount(false);
//2.查询订单列表
Page<Orders> ordersPage = baseMapper.selectPage(queryPage, queryWrapper);
List<Orders> records = ordersPage.getRecords();
List<OrderSimpleResDTO> orderSimpleResDTOS = BeanUtil.copyToList(records, OrderSimpleResDTO.class);
return orderSimpleResDTOS;
}
优化后的代码：
/**
* 滚动分页查询
*
* @param currentUserId 当前用户id
* @param ordersStatus 订单状态，0：待支付，100：派单中，200：待服务，300：服务中，400：待评价，500：订单完成，600：已取消，700：已关闭
* @param sortBy 排序字段
* @return 订单列表
*/
@Override
public List<OrderSimpleResDTO> consumerQueryList(Long currentUserId, Integer ordersStatus, Long sortBy) {
//构件查询条件
LambdaQueryWrapper<Orders> queryWrapper = Wrappers.<Orders>lambdaQuery()
.eq(ObjectUtils.isNotNull(ordersStatus), Orders::getOrdersStatus, ordersStatus)
.lt(ObjectUtils.isNotNull(sortBy), Orders::getSortBy, sortBy)
.eq(Orders::getUserId, currentUserId)
.eq(Orders::getDisplay, EnableStatusEnum.ENABLE.getStatus())
.select(Orders::getId);//只查询id列
Page<Orders> queryPage = new Page<>();
queryPage.addOrder(OrderItem.desc(SORT_BY));
queryPage.setSearchCount(false);
//查询订单id列表
Page<Orders> ordersPage = baseMapper.selectPage(queryPage, queryWrapper);
if (ObjectUtil.isEmpty(ordersPage.getRecords())) {
return new ArrayList<>();
}
//提取订单id列表
List<Long> orderIds= CollUtils.getFieldValues(ordersPage.getRecords(), Orders::getId);
//todo: 先查询缓存，缓存没有再查询数据库....
//根据订单id查询订单列表
List<Orders> ordersList = batchQuery(orderIds);
List<OrderSimpleResDTO> orderSimpleResDTOS = BeanUtil.copyToList(ordersList, OrderSimpleResDTO.class);
return orderSimpleResDTOS;
}
3.3、订单数据缓存
3.3.1、缓存结构
缓存订单数据使用Hash结构，如下：
我们需求是首先拿多个订单ID去缓存查询，如果有些key在缓存中不存在，此时去查询数据库，查询到数据再存入缓存。
示例：
查询100、101、102、103
缓存中已存在100、101的数据，现需要从数据库查询102、103的数据，查询到存储到缓存中。
3.3.2、编码
根据缓存需求我们开发缓存工具类CacheHelper ：
@Component
public class CacheHelper {
/**
* 批量获取缓存数据，按照id列表顺序返回目标数据,如果缓存不存在则查询数据库
*
* @param dataType 目标数据类型，CACHE_加dataType 为redisKey
* @param objectIds 目标数据唯一id
* @param batchDataQueryExecutor 批量目标数据获取执行器用于当缓存数据不存在时查询数据库
* @param clazz 目标数据类型class
* @param ttl 目标数据整体过期时间(ttl大于0才会设置有效期)
* @param <K> 目标数据id数据类型
* @param <T> 目标数据类型
* @return
*/
public <K, T> List<T> batchGet(String dataType, List<K> objectIds, BatchDataQueryExecutor<K, T> batchDataQueryExecutor, Class<T> clazz, Long ttl) {
下边使用CacheHelper 实现订单查询缓存，代码如下：
/**
* 滚动分页查询
*
* @param currentUserId 当前用户id
* @param ordersStatus 订单状态，0：待支付，100：派单中，200：待服务，300：服务中，400：待评价，500：订单完成，600：已取消，700：已关闭
* @param sortBy 排序字段
* @return 订单列表
*/
@Override
public List<OrderSimpleResDTO> consumerQueryList(Long currentUserId, Integer ordersStatus, Long sortBy) {
//构件查询条件
LambdaQueryWrapper<Orders> queryWrapper = Wrappers.<Orders>lambdaQuery()
.eq(ObjectUtils.isNotNull(ordersStatus), Orders::getOrdersStatus, ordersStatus)
.lt(ObjectUtils.isNotNull(sortBy), Orders::getSortBy, sortBy)
.eq(Orders::getUserId, currentUserId)
.eq(Orders::getDisplay, EnableStatusEnum.ENABLE.getStatus())
.select(Orders::getId);//只查询id列
Page<Orders> queryPage = new Page<>();
queryPage.addOrder(OrderItem.desc(SORT_BY));
queryPage.setSearchCount(false);
//查询订单id列表
Page<Orders> ordersPage = baseMapper.selectPage(queryPage, queryWrapper);
if (ObjectUtil.isEmpty(ordersPage.getRecords())) {
return new ArrayList<>();
}
//提取订单id列表
List<Long> ordersIds = CollUtils.getFieldValues(ordersPage.getRecords(), Orders::getId);
//先查询缓存，缓存没有再查询数据库
//参数1：redisKey的一部分
String redisKey = String.format(ORDERS, currentUserId);
//参数2：订单id列表
//参数3：batchDataQueryExecutor 当缓存中没有时执行batchDataQueryExecutor从数据库查询
// batchDataQueryExecutor的方法：Map<K, T> execute(List<K> objectIds, Class<T> clazz); objectIds表示缓存中未匹配到的id，clazz指定map中value的数据类型
//参数4：返回List中的数据类型
//参数5：过期时间
List<OrderSimpleResDTO> orderSimpleResDTOS = cacheHelper.<Long, OrderSimpleResDTO>batchGet(redisKey, ordersIds, (noCacheIds, clazz) -> {
List<Orders> ordersList = batchQuery(noCacheIds);
if (CollUtils.isEmpty(ordersList)) {
//为了防止缓存穿透返回空数据
return new HashMap<>();
}
Map<Long, OrderSimpleResDTO> collect = ordersList.stream().collect(Collectors.toMap(Orders::getId, o -> BeanUtil.toBean(o, OrderSimpleResDTO.class)));
return collect;
}, OrderSimpleResDTO.class, ORDERS_PAGE_TTL);
return orderSimpleResDTOS;
}
