记录一次 ClickHouse 查询从 2s 降低到 0.2s

2025-04-02数据库3 分钟179 次阅读
AI 摘要salmon-ai

本文详细解析了一次 ClickHouse 慢 SQL 优化实践:从原始 2.

前言

在近期的业务实践中,我针对 ClickHouse 的慢 SQL 进行了排查和优化。这里我们将通过一个真实案例,解析 SQL 性能优化的过程,并总结优化策略,希望为有类似需求的同学提供参考。

原来的慢 SQL

我们需要统计某个页面的访问用户数,原始 SQL 如下:

sql 复制代码
select count(distinct uid) as visit_num
from shop_rlb_event_mini_trace_visit
where mid = 'rpq5w'
  and url = 'package-gn/pages/activity/gnp/detail/index'
  and store_id = '122'
  and params like '{"id":"3703"%';

在正式环境中,该表数据量超过 1 亿行,执行上述 SQL 查询耗时约 2.2s。
image.png
image.png

调优过程

调优第一步:调整查询条件顺序

  • 过程:调整 WHERE 查询条件顺序和使用 PREWHERE 查看效果
  • 结论:调整是有效果的,时间稳定在1.2s左右
sql 复制代码
select count(distinct uid) as visit_num
from shop_rlb_event_mini_trace_visit
    PREWHERE params like '{"id":"3703"%'
    and mid = 'mnkfg'
    and store_id = '122'
    and url = 'package-gn/pages/activity/gnp/detail/index'
image.png

原理:
ClickHouse 在查询时,通常会先应用 PREWHERE 语句筛选出符合条件的数据,再执行 WHERE 过滤。合理调整条件顺序,并利用 PREWHERE,可以减少不必要的数据读取,加快查询速度。

调优第二步:查看执行计划

  • 过程:使用执行计划,继续深入分析
  • 结论:可以看出,查询了127个分区。那么减少分区是不是就可以降低查询时间?答案是肯定的。

那么要如何减少分区查询?回归业务需求,即这条查询语句,如果可以传入时间,那么就可以减少分区的查询(我们之前已对数据表进行了全库的表分区,毋庸置疑所有表都是有按时间分区的,该表也不例外)我们先查看建表 SQL,可以看到确实是通过时间进行分区的。

sql 复制代码
CREATE TABLE data_analyze_uat.shop_rlb_event_mini_trace_visit
(
    `date_create` Date,
    `mid` String,
    `url` String COMMENT '页面路径',
    `uid` String,
    `gmt_create` String,
    `store_id` String COMMENT '门店id',
    `store_number` String COMMENT '门店编号',
    `store_name` String COMMENT '门店名',
    `params` String COMMENT '参数',
    `scene` String COMMENT '场景值',
    `trace_id` String COMMENT '访问链路id',
    `model` String COMMENT '设备信息',
    `network_type` String COMMENT '网络信息',
    `wx_version` String COMMENT '平台/微信 版本信息',
    `mobile` String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date_create)
ORDER BY (date_create, gmt_create)
SETTINGS index_granularity = 8192

使用执行计划,进行分析

sql 复制代码
EXPLAIN indexes = 1
select count(distinct uid) as visit_num
from shop_rlb_event_mini_trace_visit
    PREWHERE
    params like '{"id":"3703"%'
        and mid = 'rpq5w'
        and store_id = '122'
        and url = 'package-gn/pages/activity/gnp/detail/index';
image.png

调优第三步:回归代码

  • 过程:查看代码,分析业务是否可以使用日期参数过滤。
    • 通过层层代码检查,发现这个统计用户数据,是根据某个活动进行查询已访问人数。
    • 既然有活动,那么必然就有活动开始时间,那么就可以使用活动开始时间作为我们查询条件之一。那么我们先验证下假设有时间参数下,sql的执行情况
  • 结论:有时间参数的情况下,执行耗时仅0.26s;查看执行计划,仅扫描了17个分区,比原来扫描127个分区大大减少了查询时间
sql 复制代码
select count(distinct uid) as visit_num
from shop_rlb_event_mini_trace_visit
    PREWHERE
    params like '{"id":"3703"%'
        and mid = 'rpq5w'
        and store_id = '122'
        and url = 'package-gn/pages/activity/gnp/detail/index'
        and date_create >= '2025-01-01';

image.png
image.png

调优第四部:调整代码

加参数字段

java 复制代码
# 类地址:com.xxxxx.GnTrackDataQuery
 
public class GnTrackDataQuery {
// 省略原有字段
 
/**
* 活动开始时间
*/
private String beginTime;
}

引用的代码,修改传入参数

java 复制代码
# 类地址:com.xxxxx.GroupNoteFranchiseServiceImpl#details
 
public class GroupNoteFranchiseServiceImpl implements GroupNoteFranchiseService {
    public GnDetailFrVO details(GnDetailQuery query) {
        // 省略原有代码
 
      // 传入活动时间,有效过滤非必要数据,加快查询
        trackDataQuery.setBeginTime(DateUtil.formatDate(gnActivityVO.getBeginTime()));
  }
}
image.png

修改查询的sql语句

sql 复制代码
-- 文件地址:sql/gn/sumGnActivity.sql
 
-- 修改where为PREWHERE
-- 修改参数顺序
-- 加入日期参数
 
select count(distinct uid) as visit_num
from shop_rlb_event_mini_trace_visit
PREWHERE params like #{content}
and mid = #{mid}
and store_id = #{storeId}
and url = #{url}
and date_create >= #{beginTime}

总结

最终经过以上分析调整,我们从最开始的2.2s降低到0.2s。性能提升超过 10 倍。优化的关键点如下:

  1. 调整 WHERE 查询顺序,利用 PREWHERE 提前过滤数据。
  2. 使用 EXPLAIN 分析执行计划,减少分区扫描。
  3. 结合业务逻辑,增加时间筛选条件,显著降低查询范围。
  4. 调整 Java 代码,确保 SQL 查询传递有效参数,避免全表扫描。

希望本次案例分析能对大家在 ClickHouse 优化中有所启发!

觉得有用就点个赞,不用登录也能读

评论

0 条
登录 之后才能评论
—
还没有评论,来说第一句