前言
为解决in()
中大量查询导致的索引失效问题 引入临时表解决,实际使用过程中遇到的问题如下:
一、业务中需要引入临时表查询SQL,但是临时表存在于会话中,如果用mybaitis
查询,则会发生共享临时表或者是会话中断无法访问临时表问题,藉此,写一套完整流程使用临时表
二、一条SQL 使用AB两个查询 使用union
链接 各自使用临时表 报错 临时表不存在(末尾解决)
临时表介绍
解决
使用sqlSession
来保持会话
1、创建临时表
使用DDL语句创建,每次会话请求,存在不创建
<!-- 创建临时表辅助报表查询语句-->
<update id="createTemp">
create temporary table IF NOT EXISTS `temp`(
som_code varchar(50) primary key
) ENGINE = InnoDB
</update>
2、插入数据
mybatis oracle insert 插入多条数据
DML语句插入数据 根据业务调整字段
<!-- 批量插入临时表-->
<insert id="addTemp">
INSERT INTO temp(som_code)
values
<foreach collection="list" item="item" index="index" separator=",">
( #{item} )
</foreach>
</insert>
<delete id="deleteTemp">
delete
from temp
</delete>
3、使用临时表
和正常表一致,直接将临时表当正常表用
临时表使用要加别名
select scr.*
from sixs_check_record scr
inner join temp temp on temp.som_code = scr.som_code
where scr.scr_status = 0
4、Mapper
/**
* 创建临时表
*/
void createTemp();
int addTemp(@Param("list") List<String> somCodes);
/**
* 删除已插入数据
* @return
*/
int deleteTemp();
5、Java接入
引入mybatis
后,spring会整合mybatis,连接池所拥有的会话自然也交予容器管理,化繁为简,我们直接注入mybaits
的会话工厂批量获取会话即可
5.1会话创建临时表
//获取会话工厂
@Autowired
private SqlSessionFactory sqlSessionFactory;
//打开会话
SqlSession sqlSession = sqlSessionFactory.openSession();
//判断阈值 设置一个in阈值 我的是200 超过引入临时表
SixsCheckRecordMapper mapper =null;
if(arr.size()> MaxInCount){
//mapper 除了注入 还可以直接获取 此类获取保持了会话 不可直接使用注入mapper
mapper = sqlSession.getMapper(SixsCheckRecordMapper.class);
//创建临时表
mapper.createTemp();
//默认 任何一次进来都删除 保证数据私有性
mapper.deleteTemp();
//添加数据
mapper.addTemp(tempArr);
//使用 这个看个人业务需求
sixsCheckRecordVos = mapper.querySomIdBySomCodeTemp();
}
5.2 其他语句使用
当前会话继续其他操作,还需要其他DML语句使用时,可参考如下方式获取mapper
或者方法,推荐直接获取mapper
,调用传参简单易懂,使用默认的CRUD传参复杂,灵活性,复用性都比较差,维护成本高
SixsCheckRecordMapper mapper = sqlSession.getMapper(SixsCheckRecordMapper.class);
// sqlSession.update("com.sixs.mapper.SixsCheckRecordMapper.createTemp");
// sqlSession.delete("com.sixs.mapper.SixsCheckRecordMapper.deleteTemp");
// sqlSession.insert("com.sixs.mapper.SixsCheckRecordMapper.addTemp",tempArr);
按照上述步骤已经创建临时表后,可直接使用,也可增加数据继续使用
if (size > MaxInCount) {
mapper = sqlSession.getMapper(SixsCheckRecordMapper.class);
//临时表已有数据存储 会话保持
sixsCheckRecordVos = mapper.queryReportByTermForTemp(paramSixsCheckRecord);
}
5.3 关闭会话(重要)
使用完成后需要手动调用会话关闭,避免资源浪费,临时表自动销毁
//关闭会话
if(sqlSession!=null){
closeSession(sqlSession);
}
/**
* 关闭已打开的会话
* @param sqlSession
*/
public void closeSession(SqlSession sqlSession){
// 关闭会话
sqlSession.close();
//设为null GC尽快回收
sqlSession=null;
}
优化
临时表优化有以下几个点
- 字段精简,参考建表规范
- 主键索引注意配合使用左查询时调用SQL也使用索引关联会更快
- 数据量,建议大量数据使用,几百条根据数据库性能决定是否使用
- 代码优化,及时释放资源,用完就销毁
- 同一条SQl 使用union连接两条SQL 使用两次临时表不可以,认为是两次会话
- …
union问题解决
这样使用临时表报错,因为临时表是会话级,如果同时被两条SQl执行,则第二条SQL找不到临时表
select * from A a left join temp temp on temp.id= a.id
union
select * from B b left join temp temp on temp.id= b.id
搜了半天没解决这个,后来跳出圈子一想:
将临时表复制一份,创建临时表2即可解决😃
select * from A a left join temp temp on temp.id= a.id
union
select * from B b left join temp2 temp on temp.id= b.id
推荐直接复制临时表1,使用之前复制一份
<!-- 创建临时表辅助报表查询语句-->
<update id="createTempTwo">
create temporary table IF NOT EXISTS `temp_two`(
som_code varchar(50) primary key
) ENGINE = InnoDB
</update>
<insert id="copyTemp">
insert into temp_two select * from temp
</insert>
<!-- 批量插入临时表-->
<insert id="addTempTwo">
INSERT INTO temp_two(som_code)
values
<foreach collection="list" item="item" index="index" separator=",">
( #{item} )
</foreach>
</insert>
<delete id="deleteTempTwo">
delete
from temp_two
</delete>