1 前言
超时的问题。针对这个问题在SQL的优化方法失效的时候可以遵循以空间换取时间的原则提升查询速度，这种通过增加适当数据冗余的设计也是常见的优化方法之一。本文提出一种设计想法，供读者参考。
我这里使用的是MySQL数据库，一开始考虑通过创建视图的方式，让Dao层直接查询视图是否可以提升查询效率，之前知道数据库的表中的数据是真实存在在物理表中的，视图是一种虚拟表。当物理表中的数据发生更新，虚拟表中的数据也会对应更新。觉得这种方式不就可以完美的解决问题嘛后来经过测试发现：首先，MySQL的视图不是一种物化视图，它相当于一个虚拟表，本身不存储数据，当SQL在操作视图时所有数据都是从其他表中查询出来的（和代码中写select * from (视图) 没什么本质区别）。所以使用视图并不能将常用数据分离出来，优化查询速度。其次，视图是为了简化设计，清晰编码的东西，它并不能提高性能，它的存在只会降低性能。它的存在为了设计上的方便性。
实现视图的方法有两种，分别为合并算法和临时表算法，合并算法是指查询视图是将视图定义的SQL合并到查询SQL中，比如create view v1 as select * from user where sex = ‘m’; 当我们要查询视图时，MySQL会将select id, name, from v1;合并成select id, name from v1 where sex = 'm’执行。临时表算法是将视图查出来的数据保存到一个临时表中，查询的时候查这个临时表。不管合并算法还是临时算法，都会给数据库带来额外的开销，如果使用临时表后会使MySQL的优化变得很困难，而且临时表不能永久的保存数据。之所以称为临时表，就是因为该表中的内容只是临时存在的。当一个会话或者事务结束时，该表中的内容就会被自动清空。 所以这种方法也不太适合当前场景。
最后只剩下一种选择物化视图，物化视图是查询结果的预运算，物化视图的结果一般存储于表中。 物化视图用于需要对查询立即做出响应，而又需要耗费长时间获得结果。物化视图必须能快速更新。它介于对更新频率和内容的准确性的要求。一般来说物化视图能够在一定时间内及时更新。
但是，MySQL本来不支持视图的。在5.0以上的版本，支持了视图功能，但是可惜的是不提供物化视图
所以最后只能另辟蹊径，通过创建结果表和结果索引表来解决这个问题。下面的章节将详细展开这种方法如何实现。
2 解决方案
总得来说，这种解决方案是通过创建结果表和结果索引表进行存储结果数据，后端代码只需要查询最新的更新结果即可，为了防止结果数据越来越多，结果数据只保留几个需要的版本，结果数据可以调整参数进行保留。
2.1 表的设计
2.1.1 结果表
结果表的除了结果集列名外加上创建时间和批次号这两个字段。
CREATE TABLE `common_file_view` (
`id` varchar(50) NOT NULL COMMENT '主键',
`company_code` varchar(10) DEFAULT NULL COMMENT '公司编码',
`file_id` varchar(50) DEFAULT NULL COMMENT '文件ID',
`serial_number` varchar(50) DEFAULT NULL COMMENT '用户编号',
`customer_name` varchar(60) DEFAULT NULL COMMENT '客户名称',
`file_type1` int(50) DEFAULT NULL COMMENT 'aaaa',
`file_type2` int(50) DEFAULT NULL COMMENT 'bbbb',
`create_time` datetime DEFAULT NULL COMMENT '创建时间', -- 加上这个字段
`batch_num` varchar(50) DEFAULT NULL COMMENT '批次号', -- 加上这个字段
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;2.1.2 结果索引表
CREATE TABLE `common_file_view_index` (
`id` varchar(50) NOT NULL COMMENT '主键',
`record_count` int(11) DEFAULT NULL COMMENT '记录数量',
`create_time` datetime NOT NULL COMMENT '创建时间',
`batch_num` varchar(50) DEFAULT NULL COMMENT '批次号',
`execute_time` int(10) DEFAULT NULL COMMENT '执行时间毫秒值',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;2.2 定义异步执行方法
使用@Async注解来定义异步执行方法，使用@Async注解需要注意再启动类上面加上@EnableAsync注解
@Async("DataExecutor")
@Transactional(rollbackFor = Exception.class)
public void preparedData(FileUserInfoQuery fileUserInfoQuery) {
long startTime = System.currentTimeMillis();
fileUserInfoQuery.setBatchNum(UuidUtils.getUUID());
Integer count = commonFileViewMapper.prepareData(fileUserInfoQuery);
CommonFileViewIndex index = new CommonFileViewIndex();
index.setId(UuidUtils.getUUID());
index.setRecordCount(count);
index.setCreateTime(new Date());
index.setBatchNum(fileUserInfoQuery.getBatchNum());
index.setExecuteTime(System.currentTimeMillis() - startTime);
commonFileViewIndexMapper.insert(index);
}
@Async("DataExecutor")
@Transactional(rollbackFor = Exception.class)
public void deleteData() {
List<CommonFileViewIndex> indexList = commonFileViewIndexMapper
.selectList(new LambdaQueryWrapper<CommonFileViewIndex>().orderByDesc(CommonFileViewIndex::getCreateTime));
List<String> batchNumList = new ArrayList<>();
for (int i = 0; i < indexList.size(); i++) {
// 控制保留近多少个版本的数据
if (i < 5) {
continue;
}
batchNumList.add(indexList.get(i).getBatchNum());
}
if (ToolUtil.isNotEmptyList(batchNumList)) {
LambdaQueryWrapper<CommonFileView> deleteQuery = new LambdaQueryWrapper<>();
deleteQuery.in(CommonFileView::getBatchNum, batchNumList);
commonFileViewMapper.delete(deleteQuery);
LambdaQueryWrapper<CommonFileViewIndex> deleteIndexQuery = new LambdaQueryWrapper<>();
deleteIndexQuery.in(CommonFileViewIndex::getBatchNum, batchNumList);
commonFileViewIndexMapper.delete(deleteIndexQuery);
}
} 这里需要注意的是异步方法需要新建一个类，并将这个类添加@Component注解放入Bean容器中；因为当该异步方法放置在调用类内部，该方法就无法实现异步功能
上文数据准备方法【commonFileViewMapper.prepareData(fileUserInfoQuery)】方法是通过SQL进行批量插入结果表中的，具体实现如下所示：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.jeecg.modules.system.mapper.CommonFileViewMapper">
<sql id="queryFileUserInfoSql">
select
uuid() as id,
u.company_code as company_code,
file.id as id,
file.contract_user_id as serial_number,
u.customer_name as customer_name,
count(case file_type when '1' then file_type end) as file_type1,
count(case file_type when '2' then file_type end) as file_type2,
now() as create_time
<if test="param.batchNum != null and param.batchNum != '' ">
, #{param.batchNum} as batch_num
</if>
from bill_common_file file
left join
(select * from bill_userinfo union all select * from artb_userinfo_history) u
on file.contract_user_id = u.serial_number and u.archive = 0
</sql>
<sql id="whereCondition">
<if test="param.companyCode != null and param.companyCode != '' ">
and u.company_code = #{param.companyCode}
</if>
<if test="param.customerName != null and param.customerName != '' ">
and u.customer_name like concat('%',#{param.customerName}, '%')
</if>
<if test="param.fileName != null and param.fileName != '' ">
and file.file_name like concat('%',#{param.fileName}, '%')
</if>
</sql>
<!-- 这里是准备数据的SQL-->
<insert id="prepareData">
insert into common_file_view
<include refid="queryFileUserInfo" />
</insert>
<sql id="queryFileUserInfo">
<include refid="queryFileUserInfoSql"/>
<where>
<include refid="whereCondition"/>
</where>
group by file.contract_user_id,u.heating_year
</sql>
</mapper>2.3 自定义线程池
如果不自定义线程池，Spring会给出一个默认的线程池，默认的线程池在并发情况下会无限创建线程，其默认配置：
默认核心线程数：8，
最大线程数：Integer.MAX_VALUE，
队列使用LinkedBlockingQueue，
容量是：Integer.MAX_VALUE，
空闲线程保留时间：60s
线程拒绝策略：AbortPolicy所以我这里为了解决这些缺陷，自定义一个线程池并通过@Async(“DataExecutor”)引用这个线程池
/**
* 自定义线程池
*
* @author 一朝风月
* @date 2023-02-28 13:39
*/
@Configuration
public class ExecutorConfig {
@Bean("DataExecutor")
public Executor myExecutor() {
ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
executor.setCorePoolSize(8);
executor.setMaxPoolSize(10);
executor.setQueueCapacity(1000);
executor.setKeepAliveSeconds(3);
executor.setThreadNamePrefix("prepareData");
executor.setRejectedExecutionHandler( new ThreadPoolExecutor.AbortPolicy());
executor.initialize();
return executor;
}
}这里我把值写死在类中，可以把这些配置抽取到配置文件中，方便参数进行修改。
2.4 数据查询逻辑的实现
@Override
public Result<?> queryFileUserInfo(Page<BillCommonFileVo> page, FileUserInfoQuery fileUserInfoQuery) {
CommonFileViewIndex fileViewIndex = billCommonFileMapper.getLastFileViewIndex();
if (fileViewIndex == null) {
dataService.preparedData(fileUserInfoQuery);
return Result.OK("没有符合要求的数据，请稍后重试!");
}
// 当视图数据超过5分钟，更新视图列表
if (System.currentTimeMillis() - fileViewIndex.getCreateTime().getTime() > 300000) {
dataService.preparedData(fileUserInfoQuery);
}
IPage<BillCommonFileVo> pageList = billCommonFileMapper.getFileViewList(page, fileViewIndex.getBatchNum());
dataService.deleteData();
return Result.OK(pageList);
}3 测试结果
最后前端可以实现秒级响应，解决了复杂SQL等待时间过长的问题。
