• # Sql语句过长报错、查询慢优化方案探索

    Sql 过长查询报错、in过多、查询慢优化方案探索

    目录

    文章目录

    背景描述

    • Sql中通过sql in or in 的方式拼接sql会导致sql长度过长，Sql执行报错。
    • 使用 in 查询效率较慢
    select * from test where id in('1001','1002')  or id in('1001')

    • 1
    • 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());
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    存在问题

    • 主表数据过大的时候，查询全部数据过慢

    方案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 join
select x.* from master_table x inner join  table_temp y on x.col_one = y.code

    • 1
    • 2
    • 3
    • 4
    • 5
    • 一次查询完成后用id删除数据

    临时表格式如下

    • table_temp
    标识名称含义
    idid一次查询生成的 uuid
    code值in中的值
    type类型类型
    request_id查询id查询id

    存在问题

    • 本地测试15万数据写入空表5秒左右，当表中数据越来越多的时候，写入速度变慢

    方案3（精确查询条件）

    • sql拼接更加精确的条件，减少 in的查询范围

    查询执行过程性能分析

    • 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秒

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 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秒

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    批处理
    sql 多values
    @Insert("")
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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    手动提交事务
    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());
}
sqlSession.commit();
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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    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();
    connection.commit();//所有语句都执行完毕后才手动提交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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69

    写临时表插入

    • 先创建临时表，在执行插入，使用完之后删除
    多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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    手动提交数据
    写入 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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    查询Sql性能分析

    • 主表数据50万
    • 组织机构表（table_temp）数据50万

    Sql in 子查询方式

    • 查询 2s之内
    SELECT MASTER_TABLE.ID, MASTER_TABLE.UNITCODE, MASTER_TABLE.CREATETIME, MASTER_TABLE.BILLSTATE, MASTER_TABLE.DEFINECODE, MASTER_TABLE.BILLCODE, MASTER_TABLE.CLASS_NAME, MASTER_TABLE.CLASS_NO, MASTER_TABLE.BILLDATE, MASTER_TABLE.MULTIBASEDATA1, MASTER_TABLE.MULTIBASEDATA2, MASTER_TABLE.MULTIORG1, MASTER_TABLE.MULTIORG2
FROM MASTER_TABLE
WHERE  
 (MASTER_TABLE.UNITCODE in (select code from table_temp))

    • 1
    • 2
    • 3
    • 4

    请添加图片描述

    使用 exist 过滤

    • 查询用时 3-4 s
    SELECT MASTER_TABLE.ID, MASTER_TABLE.UNITCODE, MASTER_TABLE.CREATETIME, MASTER_TABLE.BILLSTATE, MASTER_TABLE.DEFINECODE, MASTER_TABLE.BILLCODE, MASTER_TABLE.CLASS_NAME, MASTER_TABLE.CLASS_NO, MASTER_TABLE.BILLDATE, MASTER_TABLE.MULTIBASEDATA1, MASTER_TABLE.MULTIBASEDATA2, MASTER_TABLE.MULTIORG1, MASTER_TABLE.MULTIORG2
FROM MASTER_TABLE 
WHERE  exists (
select code from table_temp y where MASTER_TABLE.UNITCODE = y.code 
)

    • 1
    • 2
    • 3
    • 4
    • 5

    请添加图片描述

    join 方式

    • 查询 2s之内
    SELECT MASTER_TABLE.ID, MASTER_TABLE.UNITCODE, MASTER_TABLE.CREATETIME, MASTER_TABLE.BILLSTATE, MASTER_TABLE.DEFINECODE, MASTER_TABLE.BILLCODE, MASTER_TABLE.CLASS_NAME, MASTER_TABLE.CLASS_NO, MASTER_TABLE.BILLDATE, MASTER_TABLE.MULTIBASEDATA1, MASTER_TABLE.MULTIBASEDATA2, MASTER_TABLE.MULTIORG1, MASTER_TABLE.MULTIORG2
FROM MASTER_TABLE 
inner join table_temp x on MASTER_TABLE.UNITCODE = x.code 

    • 1
    • 2
    • 3

    请添加图片描述

    临时表、内存表、视图

    数据库临时表

    • 临时表只在当前连接可见，当关闭连接时，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));

    • 1
    • 2
    • 查看sql元数据信息并没有这张表
      请添加图片描述
    group by 语句
    • 先通过索引范围扫描得到需要的数据，然后将数据存入临时表，然后再进行排序和分组操作来完成 GROUP BY。
    select count(*) from tb_data group by string1;

    • 1

    请添加图片描述

    distinct
    • EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表。

    请添加图片描述

    临时表的应用

    • 当某一个SQL语句关联的表在2张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。
    • 程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。
    • 临时表默认的是MyISAM,但是可以修改。
    • 可以把一些经常访问的数据放到临时表中，这样访问时会快一些,因为数据是在服务器内存中,另外每次查询的时候，数据库都需要生成一些临时数据在临时表里

    数据库内存表

    内存表介绍

    • 内存表的表结构建立在磁盘里面，数据放在内存里面；

    • 当MySQL断开当前连接后，临时表的表结构和表数据都没了，但内存表的表结构和表数据都存在；当MySQL服务重启之后，内存表的数据会丢失，但表结构依旧存。

    创建内存表

    • 内存表与创建普通表一样，使用CREATE TABLE语句，但需要将存储引擎设置为：ENGINE = MEMORY
    create  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')

    • 1
    • 2
    内存表引用
    • 内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库。
    • 一旦服务器重启，所有内存表数据丢失，但是表结构仍然存在，因为表结构是存放在实际数据库路径下的，不会自动删除。重启之后，内存表将被清空，这时候对内存表的查询结果都是空的。

    临时表和内存表区别

    临时表内存表
    存储表结构和数据都存储在内存中表结构存储在磁盘中，表数据存储在内存中
    会话单个会话独享的，是会话级别的可以多个会话共享
    引擎临时表默认，myisam内存表默认，memory
    断开连接表结构和表数据都没了表结构和表数据都存在
    服务重启表结构和表数据都没了表结构存在，表数据不存在
    性能由于表数据都是存放在内存中，所以相对来说，查询速度较快，但是数据的维护较为困难

    数据库视图

    • 视图（view）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。
    • 一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。
    • 视图适合于多表连接浏览时使用，不适合增、删、改。
  • 相关阅读:
    八大排序（二）--------冒泡排序
    2021icpc南京站
    国自然中标越来越难，怎样才能赢在起跑线上？
    【位操作笔记】计算以2为底整数N的对数 查表法
    层次聚类分析及代码实现
    MySQL使用全文索引+ngram全文解析器进行全文检索
    【JavaEE---复习】四、事务
    量化金融模型ARCH模型官方例程（中文翻译版）
    超详细反编译python打包的exe
    如何在 Linux 中管理用户
  • 原文地址：https://blog.csdn.net/qq_37248504/article/details/126259099