ClickHouse数据查询速度让我有点小小的震撼

于 2024-07-31 17:54:18 发布
阅读量387 收藏 6
点赞数 5
文章标签： clickhouse
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2302_76371691/article/details/140827587
版权

背景

现在有ck中有三张表，spu_app_orchestration，spu_app_orch_group，spu_upgrade_group，计算所有解决方案中升级时间的最长时间和最短时间，每个解决方案有都多次编排，时间的总时间为每次编排的配置时间+应用时间+脚本时间之和

sql层

1.我需要以sp_version分组查询spu_app_orchestration，得到每一个sp_version对应的spu_app_orchestration的id集合（每个spu_app_orch_id就属于一次编排）

SELECT 
    sp_version,
    groupArray(id) AS id_list 
FROM spu_app_orchestration 
WHERE project_id =-1 
GROUP by sp_version ;

2.每次的编排的总时间=配置时间+应用时间+脚本时间

配置时间：通过spu_app_orch_id到spu_upgrade_group表中查询，多个配置开始时间和结束时之差的和

 

SELECT 
         round(sum(
        if(dateDiff('second', start_date, last_state_date) >= 0, 
           dateDiff('second', start_date, last_state_date) / 60.0, 
           0)
    ), 2) AS cfg_time
FROM 
    spu_upgrade_group
WHERE 
    project_id = -1
    AND spu_app_orch_id = 114
    AND type NOT IN ('DB_DATA_BACKUP', 'FM_TOOL', 'GATEWAY_COMPARE', 'EXPORT_RESULT');

应用时间和脚本时间：通过spu_app_orch_id到spu_app_orch_group表中查询，多个配置开始时间和结束时之差的和

SELECT 
    round(sum(IF(dateDiff('second', s.app_start_date, s.app_last_state_date) >= 0, 
                 dateDiff('second', s.app_start_date, s.app_last_state_date) / 60.0, 
                 0)), 2) as app_time,
    round(sum(IF(dateDiff('second', s.db_start_date, s.db_last_state_date) >= 0, 
                 dateDiff('second', s.db_start_date, s.db_last_state_date) / 60.0, 
                 0)), 2) as db_time
FROM 
    spu_app_orch_group s 
WHERE 
s.project_id =-1
AND 
s.spu_app_orch_id = 114;

在java层

最开始

三层for循环

耗时：17s
public List<BuildUpgradeProjectDto> getBuildUpgradeProject() {
    // 格式化时间，精确到秒
    DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    List<BuildUpgradeProjectDto> buildUpgradeProjectDtos = new ArrayList<>();

    // 获取log表中有哪些type为7且store_status为S的记录
    List<HCPackageLogPO> logPOS = hcPackageLogPOMapperEx.selectLogByTypeCode(LogType.BUILD_UPGRADE, StoreStatus.STORED);
    System.out.println("第一个循环开始：" + sdf.format(new Date()));
    for (HCPackageLogPO po : logPOS) {
        BuildUpgradeProjectDto buildUpgradeProjectDto = new BuildUpgradeProjectDto();

        Long projectId = po.getProjectId();
        buildUpgradeProjectDto.setProjectId(projectId);

        Date uploadDate = po.getUploadDate();
        buildUpgradeProjectDto.setLatestUploadTime(uploadDate);

        String projectName = hcProjectInfoPOMapperEx.selectProjectNameByProjectId(projectId);
        buildUpgradeProjectDto.setProjectName(projectName);

        // 获取解决方案数量
        List<Map<String, Object>> solutionAndIdList = clickHouseMapper.selectSolutionAndIdList(projectId);
        if (CollectionUtils.isNotEmpty(solutionAndIdList)) {
            buildUpgradeProjectDto.setSolutionPlanUpgradeNum(solutionAndIdList.size());
            // 获取最长时间和最短时间，每个解决方案的每次编排的时间之和，每次编排时间=配置+应用+脚本
            Double maxTime = 0.0;
            Double miniTime = 0.0;
            System.out.println("第二个循环开始：" + sdf.format(new Date()));
            for (int i = 0; i < solutionAndIdList.size(); i++) {
                Map<String, Object> map = solutionAndIdList.get(i);
                // 每个解决方案
                // 获取 id_list 并进行类型转换
                Object idListObject = map.get("id_list");
                List<Long> spuAppOrchIdList = new ArrayList<>();
                if (idListObject instanceof BigInteger[]) {
                    BigInteger[] idArray = (BigInteger[]) idListObject;
                    for (BigInteger id : idArray) {
                        spuAppOrchIdList.add(id.longValue());
                    }
                }
                Double totalSpuTime = 0.0;
                System.out.println("第三个循环开始：" + sdf.format(new Date()));
                for (Long spuAppOrchId : spuAppOrchIdList) {
                    //每次编排
                    // 配置时间
                    Double cfgTime = clickHouseMapper.selectConfigureTime(projectId, spuAppOrchId);
                    // 应用时间和脚本时间
                    Map<String, Double> appAndDbTime = clickHouseMapper.selectAppAndDbTime(projectId, spuAppOrchId);
                    Double appTime = appAndDbTime.get("appTime");
                    Double dbTIme = appAndDbTime.get("dbTime");
                    totalSpuTime += cfgTime + appTime + dbTIme;
                }
                if (i == 0) {
                    maxTime = totalSpuTime;
                    miniTime = totalSpuTime;
                } else {
                    if (totalSpuTime > maxTime) {
                        maxTime = totalSpuTime;
                    }
                    if (totalSpuTime < miniTime) {
                        miniTime = totalSpuTime;
                    }
                }
                System.out.println("第三个循环结束：" + sdf.format(new Date()));
            }
            System.out.println("第二个循环结束：" + sdf.format(new Date()));
            buildUpgradeProjectDto.setMiniTime(miniTime + "min");
            buildUpgradeProjectDto.setMaximum(maxTime + "0min");
        } else {
            buildUpgradeProjectDto.setSolutionPlanUpgradeNum(0);
            buildUpgradeProjectDto.setMiniTime("0min");
            buildUpgradeProjectDto.setMaximum("0min");
        }

        buildUpgradeProjectDtos.add(buildUpgradeProjectDto);
    }
    System.out.println("第一个循环结束：" + sdf.format(new Date()));
    return buildUpgradeProjectDtos;
}

第一次优化

  1. 批量获取项目名称：通过一次性查询所有项目ID对应的名称，避免在循环中多次查询数据库。
  2. 并行流处理：使用 parallelStream 来并行处理日志记录和解决方案数据，充分利用多核CPU的性能。
  3. 线程安全的累加器：使用 DoubleAdder 来进行线程安全的时间累加操作。
耗时：10s

public List<BuildUpgradeProjectDto> getBuildUpgradeProject() {
        // 格式化时间，精确到秒
        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<BuildUpgradeProjectDto> buildUpgradeProjectDtos = new ArrayList<>();

        // 获取log表中有哪些type为7且store_status为S的记录
        List<HCPackageLogPO> logPOS = hcPackageLogPOMapperEx.selectLogByTypeCode(LogType.BUILD_UPGRADE, StoreStatus.STORED);
        System.out.println("第一个循环开始：" + sdf.format(new Date()));

        // 批量获取项目名称，减少单独查询
        Map<Long, String> projectNames = logPOS.stream()
                .map(HCPackageLogPO::getProjectId)
                .distinct()
                .collect(Collectors.toMap(
                        projectId -> projectId,
                        projectId -> hcProjectInfoPOMapperEx.selectProjectNameByProjectId(projectId)
                ));

        // 并行处理日志记录
        buildUpgradeProjectDtos = logPOS.parallelStream().map(po -> {
            BuildUpgradeProjectDto buildUpgradeProjectDto = new BuildUpgradeProjectDto();

            Long projectId = po.getProjectId();
            buildUpgradeProjectDto.setProjectId(projectId);

            Date uploadDate = po.getUploadDate();
            buildUpgradeProjectDto.setLatestUploadTime(uploadDate);

            String projectName = projectNames.get(projectId);
            buildUpgradeProjectDto.setProjectName(projectName);

            // 获取解决方案数量
            List<Map<String, Object>> solutionAndIdList = clickHouseMapper.selectSolutionAndIdList(projectId);
            if (CollectionUtils.isNotEmpty(solutionAndIdList)) {
                buildUpgradeProjectDto.setSolutionPlanUpgradeNum(solutionAndIdList.size());
                // 获取最长时间和最短时间，每个解决方案的每次编排的时间之和，每次编排时间=配置+应用+脚本
                DoubleAdder maxTime = new DoubleAdder();
                DoubleAdder miniTime = new DoubleAdder();
                miniTime.add(Double.MAX_VALUE);

                solutionAndIdList.parallelStream().forEach(map -> {
                    // 每个解决方案
                    // 获取 id_list 并进行类型转换
                    Object idListObject = map.get("id_list");
                    List<Long> spuAppOrchIdList = new ArrayList<>();
                    if (idListObject instanceof BigInteger[]) {
                        BigInteger[] idArray = (BigInteger[]) idListObject;
                        for (BigInteger id : idArray) {
                            spuAppOrchIdList.add(id.longValue());
                        }
                    }
                    DoubleAdder totalSpuTime = new DoubleAdder();

                    spuAppOrchIdList.parallelStream().forEach(spuAppOrchId -> {
                        //每次编排
                        // 配置时间
                        Double cfgTime = clickHouseMapper.selectConfigureTime(projectId, spuAppOrchId);
                        // 应用时间和脚本时间
                        Map<String, Double> appAndDbTime = clickHouseMapper.selectAppAndDbTime(projectId, spuAppOrchId);
                        Double appTime = appAndDbTime.get("appTime");
                        Double dbTime = appAndDbTime.get("dbTime");
                        totalSpuTime.add(cfgTime + appTime + dbTime);
                    });

                    synchronized (this) {
                        if (totalSpuTime.doubleValue() > maxTime.doubleValue()) {
                            maxTime.reset();
                            maxTime.add(totalSpuTime.doubleValue());
                        }
                        if (totalSpuTime.doubleValue() < miniTime.doubleValue()) {
                            miniTime.reset();
                            miniTime.add(totalSpuTime.doubleValue());
                        }
                    }
                });

                buildUpgradeProjectDto.setMiniTime(miniTime.doubleValue() + "min");
                buildUpgradeProjectDto.setMaximum(maxTime.doubleValue() + "min");
            } else {
                buildUpgradeProjectDto.setSolutionPlanUpgradeNum(0);
                buildUpgradeProjectDto.setMiniTime("0min");
                buildUpgradeProjectDto.setMaximum("0min");
            }

            return buildUpgradeProjectDto;
        }).collect(Collectors.toList());

        System.out.println("第一个循环结束：" + sdf.format(new Date()));
        return buildUpgradeProjectDtos;
    }

第二次优化

想到大部分的耗时还是在for循环里面，ck的查询速度非常快，为什么不废弃for循环直接改造sql直接返回所有解决方案的总耗时？

耗时：200ms

sql：

        SELECT
            ROUND(SUM(cfg_time + app_time + db_time), 2) AS total_time
        FROM
            (SELECT
                 sp_version,
                 id AS spu_app_orch_id
             FROM
                 spu_app_orchestration
             WHERE
                 project_id = #{projectId,jdbcType=BIGINT}
            ) AS a
                JOIN
            (SELECT
                 spu_app_orch_id,
                 ROUND(SUM(
                               IF(dateDiff('second', start_date, last_state_date) >= 0,
                                  dateDiff('second', start_date, last_state_date) / 60.0,
                                  0)
                           ), 2) AS cfg_time
             FROM
                 spu_upgrade_group
             WHERE
                 project_id = #{projectId,jdbcType=BIGINT}
               AND type NOT IN ('DB_DATA_BACKUP', 'FM_TOOL', 'GATEWAY_COMPARE', 'EXPORT_RESULT')
             GROUP BY
                 spu_app_orch_id
            ) AS b
            ON
                a.spu_app_orch_id = b.spu_app_orch_id
                JOIN
            (SELECT
                 s.spu_app_orch_id,
                 ROUND(SUM(IF(dateDiff('second', s.app_start_date, s.app_last_state_date) >= 0,
                              dateDiff('second', s.app_start_date, s.app_last_state_date) / 60.0,
                              0)), 2) AS app_time,
                 ROUND(SUM(IF(dateDiff('second', s.db_start_date, s.db_last_state_date) >= 0,
                              dateDiff('second', s.db_start_date, s.db_last_state_date) / 60.0,
                              0)), 2) AS db_time
             FROM
                 spu_app_orch_group s
             GROUP BY
                 s.spu_app_orch_id
            ) AS c
            ON
                b.spu_app_orch_id = c.spu_app_orch_id
        GROUP BY
            a.sp_version;

    public List<BuildUpgradeProjectDto> getBuildUpgradeProject() {
        List<BuildUpgradeProjectDto> buildUpgradeProjectDtos = new ArrayList<>();

        // 获取log表中有哪些type为7且store_status为S的记录
        List<HCPackageLogPO> logPOS = hcPackageLogPOMapperEx.selectLogByTypeCode(LogType.BUILD_UPGRADE, StoreStatus.STORED);
        // 并行处理日志记录
        buildUpgradeProjectDtos = logPOS.parallelStream().map(po -> {
            BuildUpgradeProjectDto buildUpgradeProjectDto = new BuildUpgradeProjectDto();

            Long projectId = po.getProjectId();
            buildUpgradeProjectDto.setProjectId(projectId);

            Date uploadDate = po.getUploadDate();
            buildUpgradeProjectDto.setLatestUploadTime(uploadDate);

            String projectName = hcProjectInfoPOMapperEx.selectProjectNameByProjectId(projectId);
            buildUpgradeProjectDto.setProjectName(projectName);

            // 获取解决方案数量
            List<Map<String, Object>> solutionAndIdList = clickHouseMapper.selectSolutionAndIdList(projectId);
            if (CollectionUtils.isNotEmpty(solutionAndIdList)) {
                buildUpgradeProjectDto.setSolutionPlanUpgradeNum(solutionAndIdList.size());
                // // 获取最长时间和最短时间，每个解决方案的每次编排的时间之和，每次编排时间=配置+应用+脚本
                List<Double> spuTimes = clickHouseMapper.selectSpuTime(projectId);
                
                buildUpgradeProjectDto.setMiniTime(Collections.min(spuTimes));
                buildUpgradeProjectDto.setMaximum(Collections.max(spuTimes));
            } else {
                buildUpgradeProjectDto.setSolutionPlanUpgradeNum(0);
                buildUpgradeProjectDto.setMiniTime(0.00);
                buildUpgradeProjectDto.setMaximum(0.00);
            }

            return buildUpgradeProjectDto;
        }).collect(Collectors.toList());
        return buildUpgradeProjectDtos;
    }

结论

在使用ck作为数据储存数据库时，查询速度非常快，可以尽量不使用循环，能用sql查的就用sql查

I.T.A
关注
ClickHouse进阶（十）：Clickhouse数据查询-4
qq_32020645的博客
09-07 352
关注IT贫道，获取高质量博客内容！🏡个人主页：含各种IT体系技术,📌订阅：拥抱独家专题，你的订阅将点燃我的创作热情！✏️评论：留下心声墨迹，你的评论将是我努力改进的方向！clickhouse也支持Having子句，需要与group by 同时出现，不能单独使用，它能够在聚合计算之后实现二次过滤数据。
大数据ClickHouse进阶（七）：ClickHouse 数据查询
Lansonli（蓝深李）的博客
09-17 3027
ClickHouse完全使用SQL作为查询语言，能够以Selete查询语句从数据库中查询数据，虽然ClickHouse拥有优秀的查询性能，但是我们也不能滥用查询，掌握ClickHouse支持的各种查询子句很有必要，使用不恰当的SQL语句进行查询不仅带来低性能，还可能带来系统不可预知的错误。此外需要注意，ClickHouse中对字段的解析大小写敏感，select a与select A表示的语义不同，下篇文章我们学习下ClickHouse中支持的查询语句，敬请期待。
ClickHouse 概述
程序员学习圈
08-10 1058
1.ClickHouse 概述 * ClickHouse 是俄罗斯的Yandex(欧洲最大的互联网公司之一)于2016年开源的列式存储数据库(DBMS)，使用C++语言编写，主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告。 * 为什么叫ClickHouse：Click Stram+Data WareHouse
加速您的ClickHouse查询
ClickHouseDB的博客
03-04 2817
ClickHouse 查询处理架构针对实时分析进行了优化，并且具有使查询处理快速的默认设置。
ClickHouse查询语句详解
m0_67401055的博客
08-01 6226
下面我们会逐一介绍各个子表达式，主要介绍和标准SQL不同的部分，如果某条语句执行报错，则可能是clickhouse版本不支持。
SQL 循环遍历
gracehaohaoxue的专栏
12-19 2373
一：遍历 数据表 select  HID into #temp from  MD_SheetTemplate  where  SheetStateID=1 declare  @HID  varchar(50) WHILE EXISTS(  select  HID  from  #temp  )    ---遍历临时表 begin   select top 1 @HID=HID  fro
二百四十三、ClickHouse——截取ClickHouse表中时间戳字段的时分秒部分
最新发布
tiantang2renjian的博客
07-04 837
截取ClickHouse表中时间戳字段的时分秒部分
ClickHouse数据查询
weixin_59295776的博客
03-13 1460
SELECT *可以定义子查询 ,但是一定还要注意的是,子查询只能返回一行结果 ,否则会抛出异常WITH (SELECT *) AS subSELECT*,sub。
六千字呕心沥血深度总结，为您揭秘ClickHouse为什么查询这么快！
jmysql的博客
08-05 567
关于库引擎，简单总结一下。ClickHouse 也支持在创建库的时候，指定库引擎，目前支持 5 种，分别是:Ordinary，Dictionary， Memory， Lazy， MySQL，其实 Ordinary 是默认库引擎，在此类型库引擎下，可以使用任意类型的表引擎。Ordinary引擎: 默认引擎，如果不指定数据库引擎创建的就是 Ordinary 数据库;Dictionary引擎: 此数据库会自动为所有数据字典创建表;...
springboot整合ClickHouse，实现数据高性能查询分析
06-25
ClickHouse不应该被用作通用数据库，而是作为超高性能的海量数据快速查询的分布式实时处理平台，在数据汇总查询方面(如GROUP BY)，ClickHouse的查询速度非常快。 2、数据分析能力 OLAP场景特征 · 大多数是读请求 ...
Clickhouse如何循环遍历，生成随机数
m0_37658349的博客
01-04 1205
【代码】Clickhouse如何循环遍历，生成随机数。
浅谈ClickHouse查询优化
weixin_59801183的博客
10-24 1333
ClickHouse是一个高性能的列式数据库管理系统，适用于实时分析和大数据处理。为了提高查询性能，ClickHouse采用了多种查询优化技术，如索引、数据预取、查询重写和执行计划。本文将详细讲解这些查询优化技术，以及如何在实际应用中使用它们。
clickhouse 查询优化_clickhouse查询耗时30多秒还有优化的地方？
weixin_31640639的博客
01-14 1134
@kikanjuu [CLICK_DB_AGGR_NEW_APP_IP_TCP]={.time_interval = 3600 * 24,.data_save_time = 3600 * 24 * 90,.table_name = “aggr_log_app_ip_tcp”,.create_db_table = “create table if not exists aggr_log_app_ip...
clickhouse运维常用语句
JH_Zhai的博客
08-29 590
Clickhouse
高级DBA教你clickhouse数据库查询正在执行的SQL语句的汇总方法全网最详细
nasen512的博客
02-27 4000
ClickHouse 中更新数据的语句是异步执行的，应用并不知道 ClickHouse 何时能更新数据，如果要做到强一致性，需要应用反查确认。请注意，这只是一个基本示例，具体的查询可能会根据你的 ClickHouse 安装和配置而有所不同。请注意，这只是一个基本示例，具体的查询可能会根据你的 ClickHouse 安装和配置而有所不同。表中的字段含义可能会因 ClickHouse 的版本和配置而有所不同。表中的这些字段，你可以了解查询的执行情况、性能指标以及用户活动等信息，有助于监控和优化数据库的使用。
6亿数据秒级查询，ClickHouse太快了！
wangxi06的专栏
04-01 297
图片来自 Pexels 本文记录一下测试结果，有做超大数据量分析技术选型需求的朋友可以参考下。 服务器信息 如下： CPU：Intel Xeon Gold 6240 @ 8x 2.594GHz 内存：32G 系统：CentOS 7.6 Linux 内核版本：3.10.0 磁盘类型：机械硬盘 文件系统：ext4 Clickhouse 信息 如下： 部署方式：单机部署 版本：20.8.11.17 测试情况 测试数据和测试方法来自 Clickshouse 官方的 ...
clickhouse 查询优化_如何提升查询速度？试试ClickHouse
weixin_35763787的博客
01-14 4895
一、ClickHouse 是什么？ClickHouse：是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)我们首先理清一些基础概念OLTP：是传统的关系型数据库，主要操作增删改查，强调事务一致性，比如银行系统、电商系统OLAP：是仓库型数据库，主要是读取数据，做复杂数据分析，侧重技术决策支持，提供直观简单的结果接着我们用图示，来理解一下列式数据库和行式数据库区别在传统的行式数据库系统中...
ClickHouse语法笔记
weixin_46216804的博客
10-13 220
ClickHouse语法笔记
clickhouse使用经验总结
dkgee
12-17 8035
1、java连接clickhouse中，尽量不用使用循环查，容易造成超时异常； 2、高并发查询clickhouse造成超时。 3、clickhouse适合大数据量，单查询。 4、高并发需要优化clickhouse的配置，具体包含 max_concurrent_queries 100个并发 1000个 uncompressed_cache_size 8G 总内存*2/3 5、对于数据去重，除了使用distinct函数外，还有uniq()、uniqComb...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值