Sql 过长查询报错、in过多、查询慢优化方案探索
目录
文章目录
Sql 过长查询报错、in过多、查询慢优化方案探索目录背景描述解决方案方案1（内存中过滤）存在问题方案2 In 查询的内容写入固化表固化表格式如下写多张固化表思路存在问题方案2 改进使用 left join方案3（精确查询条件）方案4 使用临时表方案5 固化写表使用事务特性查询执行过程性能分析写表性能分析写入固化表单条插入批处理sql 多values手动提交事务` PreparedStatement executeBatch` 方式写临时表插入多values方式手动提交数据查询Sql性能分析Sql in 子查询方式使用 exist 过滤join 方式性能分析结论写表性能分析1000条一次10000条写一次5000条写一次查询性能分析总结临时表、内存表、视图数据库临时表Mysql 使用临时表的情况创建临时表group by 语句distinct临时表的应用数据库内存表内存表介绍创建内存表内存表引用临时表和内存表区别数据库视图临时表实战背景描述
当不能直接进行sql联表查询的时候，一些中间数据需要写到表中进行查询从而解决一些无法解决的问题
Sql中通过sql in or in的方式拼接sql会导致sql长度过长，Sql执行报错。
使用in查询效率较慢
select * from test where id in('1001','1002') or id in('1001')
in的内容过长后，sql超过数据库对sql长度的设定后会报错
解决方案
方案1（内存中过滤）
in中的内容超过一定的阈值后，超过阈值数据的字段在内存中进行过滤，先查出其它条件符合的数据（sql不做分页查询 ），加载到内存中，最后进行内存分页/*** 内存分页** @param list数据* @param pageNum 当前页* @param pageSize 分页条数* @return List*/public <T> List<T> pageOperateInMemory(List<T> list, Integer pageNum, Integer pageSize) {if (CollectionUtils.isEmpty(list)) {return Collections.emptyList();}// 根据当前页和分页条数 从总记录数中取数据return list.stream().skip((long) pageSize * (pageNum - 1)).limit(pageSize).collect(Collectors.toList());}
存在问题
主表数据过大的时候，查询全部数据过慢方案2 In 查询的内容写入固化表
创建一张固化表保存In里面的数据，每次查询的时候先写表，再用这张表做关联查询# 方式 1 子查询select * from master_table x where col_one in(select code from table_temp y where x.col_one=y.code)select * from test x where exists (select id from test y where y.id=x.id )# 方式 2 inner joinselect x.* from master_table x inner join table_temp y on x.col_one = y.code
一次查询完成后用id删除数据
固化表格式如下
table_temp写多张固化表思路
每次查询生成一个查询id创建多张固化表，每次查询随机hash到随机表进行写表使用多线程写表（存在问题见存在的问题描述）存在问题
本地测试15万数据写入空表5秒左右，当表中数据越来越多的时候，写入速度变慢使用多项成写表的时候，数据量比较大的时候，写表速度不是特别理想，在并发情况下数据库处理大量sql不是很理想、并且在写完表后还有后续查询逻辑，sql查询不是特别理想此种方法不推荐方案2 改进使用 left join
写固化表在使用join方式比in子查询的方式快使用in子查询在遇到分页的问题的时候会比较慢，推荐优先使用join方式方案3（精确查询条件）
sql拼接更加精确的条件，减少in的查询范围方案4 使用临时表
经过实践发现使用临时表效果较好使用临时表详情：/qq_37248504/article/details/127349776方案5 固化写表使用事务特性
手动控制事务，写表之后事务不提交，等所有查询语句执行完成之后，回滚事务这种方案比较好，整体速度快查询执行过程性能分析
table_temp共有15万写表性能分析
写入固化表
单条插入
mybatis insert方式private void insertOneByOne(List<TempDO> tempDOList) {StopWatch stopWatch = new StopWatch("task1");stopWatch.start();int i = 0;for (TempDO tempDO : tempDOList) {testMapper.insertSelective(tempDO);i++;if (i == 10000) {stopWatch.stop();logger.info(String.valueOf(stopWatch.getTotalTimeMillis()));i = 0;}}}// 10000 条 需要 4分29秒
jdbcTemplate方式
private void insertOneByOneJdbcExcute(List<TempDO> tempDOList) {String sql = "INSERT INTO table_temp_one (ID,VER,CODE,ORGCODE,NAME,SHORTNAME,VALIDTIME,INVALIDTIME,PARENTCODE,ORDINAL,CREATEUSER,CREATETIME,PARENTS) VALUES\n" +"\t (?,?,?,?,?,?,?,?,?,?,?,?,?);\n";List<Object[]> objectList = new ArrayList<>();for (TempDO tempDO : tempDOList) {Object[] object = {tempDO.getId(), tempDO.getVer(), tempDO.getCode(), tempDO.getOrgcode(), tempDO.getName(), tempDO.getShortname(), tempDO.getValidtime(), tempDO.getInvalidtime(),tempDO.getParentcode(), tempDO.getOrdinal(), tempDO.getCreateuser(), tempDO.getCreatetime(), tempDO.getParents()};objectList.add(object);}StopWatch stopWatch = new StopWatch("task1");stopWatch.start();int i = 0;for (Object[] objects : objectList) {jdbcTemplate.update(sql, objects);i++;if (i == 10000) {stopWatch.stop();logger.info(String.valueOf(stopWatch.getTotalTimeMillis()));i = 0;}}}// 10000 条 需要 4分 36秒
批处理
sql 多values
@Insert("<script>INSERT INTO table_temp\n" +" (id,request_id,code,type)\n" +" values\n" +" <foreach collection=\"list\" item=\"item\" separator=\",\">\n" +" (#{item.id}, #{item.requestId}, #{item.code}, #{item.type})\n" +" </foreach></script>")int insertBatch(TempDO tempDO);写入 10000 条执行耗时：{}937写入 10000 条执行耗时：{}734写入 10000 条执行耗时：{}925写入 10000 条执行耗时：{}1241写入 10000 条执行耗时：{}959写入 10000 条执行耗时：{}999写入 10000 条执行耗时：{}701写入 10000 条执行耗时：{}352写入 10000 条执行耗时：{}582写入 10000 条执行耗时：{}390写入 10000 条执行耗时：{}527写入 10000 条执行耗时：{}388写入 10000 条执行耗时：{}357写入 10000 条执行耗时：{}491写入 10000 条执行耗时：{}300写入 10000 条执行耗时：{}23写入 150000 条执行耗时：{}9915------------------------------------写入 150000 条执行耗时：{}6206 ------------------------------------写入 150000 条执行耗时：{}5658写入 150000 条执行耗时：{}6674写入 150000 条执行耗时：{}7744
手动提交事务
SqlSessionFactory sqlSessionFactory = ApplicationContextRegister.getBean(SqlSessionFactory.class);SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);InsertTestMapper mapper = sqlSession.getMapper(InsertTestMapper.class);List<List<TempDO>> lists = splitList(list, 10000);StopWatch stopWatch = new StopWatch("totalTask");stopWatch.start();for (List<TempDO> tempDOList : lists) {StopWatch stopWatch1 = new StopWatch("task1");stopWatch1.start();mapper.insertBatch(tempDOList);stopWatch1.stop();System.out.println("写入 10000 条执行耗时：{}" + stopWatch1.getTotalTimeMillis());}mit();stopWatch.stop();System.out.println("写入 150000 条执行耗时：{}" + stopWatch.getTotalTimeMillis());---------------------------------------------------------------写入 10000 条执行耗时：{}159写入 10000 条执行耗时：{}106写入 10000 条执行耗时：{}106写入 10000 条执行耗时：{}106写入 10000 条执行耗时：{}106写入 10000 条执行耗时：{}139写入 10000 条执行耗时：{}109写入 10000 条执行耗时：{}106写入 10000 条执行耗时：{}133写入 10000 条执行耗时：{}107写入 10000 条执行耗时：{}107写入 10000 条执行耗时：{}107写入 10000 条执行耗时：{}106写入 10000 条执行耗时：{}107写入 10000 条执行耗时：{}121写入 10000 条执行耗时：{}0写入 150000 条执行耗时：{}4734---------------------------------------------------------------------写入 10000 条执行耗时：{}130写入 10000 条执行耗时：{}105写入 10000 条执行耗时：{}105写入 10000 条执行耗时：{}127写入 10000 条执行耗时：{}135写入 10000 条执行耗时：{}106写入 10000 条执行耗时：{}105写入 10000 条执行耗时：{}133写入 10000 条执行耗时：{}106写入 10000 条执行耗时：{}107写入 10000 条执行耗时：{}111写入 10000 条执行耗时：{}106写入 10000 条执行耗时：{}159写入 10000 条执行耗时：{}142写入 10000 条执行耗时：{}138写入 10000 条执行耗时：{}0写入 150000 条执行耗时：{}6495
PreparedStatement executeBatch方式
Connection connection = SpringContextUtils.getBean(JdbcTemplate.class).getDataSource().getConnection();String sql = "insert into table_temp(id, request_id,code,type) VALUES (?,?,?,?)";PreparedStatement ps = null;try {ps = connection.prepareStatement(sql);// 取消自动提交connection.setAutoCommit(false);StopWatch stopWatch = new StopWatch("totalTask");stopWatch.start();for (int i = 1; i <= list.size(); i++) {TempDO tempDO = list.get(i - 1);ps.setObject(1, tempDO.getId());ps.setObject(2, tempDO.getRequestId());ps.setObject(3, tempDO.getCode());ps.setObject(4, tempDO.getType());ps.addBatch();if (i % 10000 == 0) {StopWatch stopWatch1 = new StopWatch("task1");stopWatch1.start();ps.executeBatch();ps.clearBatch();stopWatch1.stop();System.out.println("写入 10000 条执行耗时：{}" + stopWatch1.getTotalTimeMillis());}}ps.executeBatch();ps.clearBatch();mit();//所有语句都执行完毕后才手动提交sql语句stopWatch.stop();System.out.println("写入 150000 条执行耗时：{}" + stopWatch.getTotalTimeMillis());} catch (SQLException e) {e.printStackTrace();} finally {connection.close();}写入 10000 条执行耗时：{}1696写入 10000 条执行耗时：{}1423写入 10000 条执行耗时：{}1192写入 10000 条执行耗时：{}1273写入 10000 条执行耗时：{}1203写入 10000 条执行耗时：{}1445写入 10000 条执行耗时：{}1226写入 10000 条执行耗时：{}1298写入 10000 条执行耗时：{}1666写入 10000 条执行耗时：{}1251写入 10000 条执行耗时：{}1190写入 10000 条执行耗时：{}1364写入 10000 条执行耗时：{}1263写入 10000 条执行耗时：{}1191写入 10000 条执行耗时：{}1682写入 150000 条执行耗时：{}20753--------------------------------------------------------------------写入 10000 条执行耗时：{}1579写入 10000 条执行耗时：{}1859写入 10000 条执行耗时：{}1483写入 10000 条执行耗时：{}1470写入 10000 条执行耗时：{}1488写入 10000 条执行耗时：{}1059写入 10000 条执行耗时：{}1112写入 10000 条执行耗时：{}1164写入 10000 条执行耗时：{}1426写入 10000 条执行耗时：{}1202写入 10000 条执行耗时：{}1315写入 10000 条执行耗时：{}1654写入 10000 条执行耗时：{}1301写入 10000 条执行耗时：{}1134写入 10000 条执行耗时：{}1529写入 150000 条执行耗时：{}21168
写临时表插入
先创建临时表，在执行插入，使用完之后删除多values方式
写入 10000 条执行耗时：{}1267写入 10000 条执行耗时：{}440写入 10000 条执行耗时：{}352写入 10000 条执行耗时：{}524写入 10000 条执行耗时：{}380写入 10000 条执行耗时：{}306写入 10000 条执行耗时：{}457写入 10000 条执行耗时：{}303写入 10000 条执行耗时：{}301写入 10000 条执行耗时：{}804写入 10000 条执行耗时：{}333写入 10000 条执行耗时：{}297写入 10000 条执行耗时：{}323写入 10000 条执行耗时：{}325写入 10000 条执行耗时：{}324写入 150000 条执行耗时：{}6746---------------------------------------------------------------写入 10000 条执行耗时：{}372写入 10000 条执行耗时：{}409写入 10000 条执行耗时：{}302写入 10000 条执行耗时：{}303写入 10000 条执行耗时：{}302写入 10000 条执行耗时：{}302写入 10000 条执行耗时：{}370写入 10000 条执行耗时：{}300写入 10000 条执行耗时：{}724写入 10000 条执行耗时：{}302写入 10000 条执行耗时：{}299写入 10000 条执行耗时：{}303写入 10000 条执行耗时：{}299写入 10000 条执行耗时：{}301写入 10000 条执行耗时：{}301写入 150000 条执行耗时：{}5196
手动提交数据
写入 10000 条执行耗时：{}379写入 10000 条执行耗时：{}219写入 10000 条执行耗时：{}174写入 10000 条执行耗时：{}165写入 10000 条执行耗时：{}162写入 10000 条执行耗时：{}155写入 10000 条执行耗时：{}227写入 10000 条执行耗时：{}239写入 10000 条执行耗时：{}163写入 10000 条执行耗时：{}202写入 10000 条执行耗时：{}157写入 10000 条执行耗时：{}175写入 10000 条执行耗时：{}166写入 10000 条执行耗时：{}160写入 10000 条执行耗时：{}169写入 150000 条执行耗时：{}4348
查询Sql性能分析
主表数据50万固化表（table_temp）数据50万Sql in 子查询方式
查询2s之内SELECT *FROM MASTER_TABLEWHERE (MASTER_TABLE.CODE in (select code from table_temp))
使用 exist 过滤
查询用时3-4 sSELECT *FROM MASTER_TABLE WHERE exists (select code from table_temp y where MASTER_TABLE.CODE = y.code )
join 方式
查询2s之内SELECT *FROM MASTER_TABLE inner join table_temp x on MASTER_TABLE.CODE = x.code
性能分析结论
写表性能分析
15万数据
10000条一次比5000、1000条一次写入速度快
固化表增加code索引后写入变慢
手动提交事务比自动提交事务快
固化表追加方式写的时候越往后越慢
1000条一次
写入150000条执行耗时：9995写入150000条执行耗时：992410000条写一次
固化表只有主键固化表 code设置索引5000条写一次
固化表只有主键固化表 code设置索引查询性能分析
固化表 code增加索引后三种方式秒出
固化表不加code索引、join方式查询最快
固化表只有主键
固化表 code设置索引
总结
使用固化表存储插入的时候使用批量手动提交事务的方式插入sql插入多values方式速度比较快sql查询使用left join比使用in子查询的方式快很多使用数据库临时表是最好的方案临时表、内存表、视图
数据库临时表
临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
外部临时表：通过CREATE TEMPORARY TABLE创建的临时表，这种临时表称为外部临时表。这种临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭。这种临时表的命名与非临时表可以同名（同名后非临时表将对当前会话不可见，直到临时表被删除）。
内部临时表：内部临时表是一种特殊轻量级的临时表，用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。内部临时表在SQL语句的优化过程中扮演着非常重要的角色，MySQL中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写SQL语句的时候应该尽量的去避免使用临时表。
如果超出了临时表的容量，临时表会转换成磁盘表
Mysql 使用临时表的情况
创建临时表
MySQL用于存储一些中间结果集的表，临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。# 创建 临时表语句create temporary table tb_temporay(id varchar(36),name varchar(30),code varchar(30));
查看sql元数据信息并没有这张表
group by 语句
先通过索引范围扫描得到需要的数据，然后将数据存入临时表，然后再进行排序和分组操作来完成GROUP BY。select count(*) from tb_data group by string1;
distinct
EXPLAIN查看执行计划结果的Extra列中，如果包含Using Temporary就表示会用到临时表。临时表的应用
当某一个SQL语句关联的表在2张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。临时表默认的是MyISAM,但是可以修改。可以把一些经常访问的数据放到临时表中，这样访问时会快一些,因为数据是在服务器内存中,另外每次查询的时候，数据库都需要生成一些临时数据在临时表里数据库内存表
内存表介绍
内存表的表结构建立在磁盘里面，数据放在内存里面；
当MySQL断开当前连接后，临时表的表结构和表数据都没了，但内存表的表结构和表数据都存在；当MySQL服务重启之后，内存表的数据会丢失，但表结构依旧存。
创建内存表
内存表与创建普通表一样，使用CREATE TABLE语句，但需要将存储引擎设置为：ENGINE = MEMORYcreate table tb_one (id varchar(36),name varchar(30),code varchar(30))ENGINE = MEMORY;insert into tb_one(id,name,code) values('1','2','3')
内存表引用
内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库。一旦服务器重启，所有内存表数据丢失，但是表结构仍然存在，因为表结构是存放在实际数据库路径下的，不会自动删除。重启之后，内存表将被清空，这时候对内存表的查询结果都是空的。临时表和内存表区别
数据库视图
视图（view）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。视图适合于多表连接浏览时使用，不适合增、删、改。临时表实战
使用临时表详情：/qq_37248504/article/details/127349776
