首页 > 其他分享 >详解 ClickHouse 的查询优化

详解 ClickHouse 的查询优化

时间:2024-06-24 12:29:04浏览次数:20  
标签:hits join CounterID 查询 v1 详解 select ClickHouse

一、单表查询

1. 使用 prewhere 替代 where

  • prewhere 和 where 语句的作用相同,都是用来过滤数据
  • prewhere 和 where 语句的不同在于:
    • prewhere 只支持 MergeTree 族系列引擎的表
    • prewhere 首先会读取指定的列数据来判断数据过滤,等待数据过滤之后再读取 select 声明的列字段来补全其余属性
    • prewhere 会自动优化执行过滤阶段的数据读取方式,降低 io 操作
  • 当查询列明显多于筛选列时使用 prewhere 可十倍提升查询性能
  • prewhere 默认是开启的,但下面这些情况需要手动指定 prewhere
    • 使用常量表达式
    • 使用默认值为 alias 类型的字段
    • 包含了 arrayJOIN,globalIn,globalNotIn 或者 indexHint 的查询
    • select 查询的列字段和 where 的谓词相同
    • 使用了主键字段
--先关闭 where 自动转 prewhere(默认情况下, where 条件会自动优化成 prewhere)
set optimize_move_to_prewhere=0;

--使用 where 语句
select WatchID,
 JavaEnable,
 Title,
 GoodEvent,
 EventTime,
 EventDate,
 CounterID,
 ClientIP,
 ClientIP6,
 RegionID,
 UserID,
 CounterClass,
 OS,
 UserAgent,
 URL,
 Referer,
 URLDomain,
 RefererDomain,
 Refresh,
 IsRobot,
 RefererCategories,
 URLCategories,
 URLRegions,
 RefererRegions,
 ResolutionWidth,
 ResolutionHeight,
 ResolutionDepth,
 FlashMajor,
 FlashMinor,
 FlashMinor2
from datasets.hits_v1 where UserID='3198390223272470366';

--使用 prewhere 关键字
select WatchID,
 JavaEnable,
 Title,
 GoodEvent,
 EventTime,
 EventDate,
 CounterID,
 ClientIP,
 ClientIP6,
 RegionID,
 UserID,
 CounterClass,
 OS,
 UserAgent,
 URL,
 Referer,
 URLDomain,
 RefererDomain,
 Refresh,
 IsRobot,
 RefererCategories,
 URLCategories,
 URLRegions,
 RefererRegions,
 ResolutionWidth,
 ResolutionHeight,
 ResolutionDepth,
 FlashMajor,
 FlashMinor,
 FlashMinor2
from datasets.hits_v1 prewhere UserID='3198390223272470366';

2. 数据采样

  • 采样修饰符只有在 MergeTree engine 表中才有效,且在创建表时需要指定采样策略
  • 通过采样运算可极大提升数据分析的性能
SELECT 
	Title,
	count(*) AS PageViews
FROM hits_v1
SAMPLE 0.1 --表示进行where过滤后再采样其中 10% 的数据,也可以是具体的条数
WHERE CounterID =57
GROUP BY Title
ORDER BY PageViews DESC 
LIMIT 1000

3. 列裁剪和分区裁剪

  • 列裁剪:查询时避免使用 select * ,应该直接 select 要查询的具体字段名,字段越少,消耗的 io 资源越少,性能就会越高
  • 分区裁剪:查询时使用 prewhere 筛选对应分区字段条件的数据,避免全表扫描

4. order by 结合 where 和 limit

  • 避免全局的 order by 排序
  • 搭配 where 条件和 limit 语句进行 order by 会提升性能和减少数据扫描量
--正例:
SELECT 
	UserID,
	Age
FROM hits_v1
WHERE CounterID=57
ORDER BY Age DESC 
LIMIT 1000

--反例:
SELECT 
	UserID,
	Age
FROM hits_v1
ORDER BY Age DESC

5. 避免构建虚拟列

  • 虚拟列:不是表中实际的字段,而是通过表中字段计算转换出来的列
  • 如非必须,不要在结果集上构建虚拟列,虚拟列非常消耗资源浪费性能,可以考虑在前端进行处理,或者在表中构造实际字段进行额外存储
--反例:IncRate 为虚拟列
SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;

--正例:拿到 Income 和 Age 后,考虑在前端进行处理,或者在表中构造实际字段进行额外存储
SELECT Income,Age FROM datasets.hits_v1;

6. 使用 uniqCombined 替代 distinct

  • uniqCombined 底层采用类似 HyperLogLog 算法实现,性能可提升 10 倍以上,但有 2% 左右的数据误差
  • distinct 会使用 uniqExact 精确去重
select count(distinct UserID) from datasets.hits_v1;

SELECT uniqCombined(UserID) from datasets.hits_v1;

7. 其他注意事项

  • 查询熔断:为了避免因个别慢查询引起的服务雪崩的问题,除了可以为单个查询设置超时以外,还可以配置周期熔断,在一个查询周期内,如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作
  • 关闭虚拟内存:物理内存和虚拟内存的数据交换,会导致查询变慢,资源允许的情况下关闭虚拟内存
  • 配置 join_use_nulls:为每一个账户添加 join_use_nulls 配置,左表中的一条记录在右表中不存在,右表的相应字段会返回该字段相应数据类型的默认值,而不是标准 SQL 中的 Null 值
  • 批量写入时先排序:批量写入数据时,必须控制每个批次的数据中涉及到的分区的数量,在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多,会导致 ClickHouse 无法及时对新导入的数据进行合并,从而影响查询性能
  • 关注 CPU:CPU 一般在 50%左右会出现查询波动,达到 70%会出现大范围的查询超时,CPU 是最关键的指标,要非常关注

二、多表关联

1. 数据准备

--创建小表
CREATE TABLE visits_v2
ENGINE = CollapsingMergeTree(Sign)
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from visits_v1 limit 10000;

--创建 join 结果表:避免控制台疯狂打印数据
CREATE TABLE hits_v2
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from hits_v1 where 1=0; --不取数据只取表结构

2. 用 in 代替 join

ClickHouse 的 join 是将右表(无论 left join、right join 还是 inner join)的数据全部加载到内存(可能 OOM),然后左表的每一条数据都去内存中查询能否匹配

--正例:使用 in
insert into table hits_v2
select a.* from hits_v1 a where a.CounterID in (select CounterID from visits_v1);

--反例:使用 join
insert into table hits_v2
select a.* from hits_v1 a join visits_v1 b on a.CounterID=b.CounterID;

3. 大小表 join

因为 ClickHouse 进行 join 的底层特性,所以必须要满足小表在右的原则

--正例:小表在右
insert into table hits_v2
select a.* from hits_v1 a left join visits_v2 b on a.CounterID=b.CounterID;

--反例:大表在右
insert into table hits_v2
select a.* from visits_v2 b left join hits_v1 a on a.CounterID=b.CounterID;

4. 注意谓词下推

ClickHouse 在 join 查询时不会主动发起谓词下推的操作,需要每个子查询提前完成过滤操作

Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b.
CounterID
having a.EventDate = '2014-03-17';

Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b.
CounterID
having b.StartDate = '2014-03-17';

insert into hits_v2
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b.
CounterID
where a.EventDate = '2014-03-17';

insert into hits_v2
select a.* from (
 select * from
 hits_v1
 where EventDate = '2014-03-17'
) a left join visits_v2 b on a. CounterID=b. CounterID;

5. 分布式表使用 global

两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字,右表只会在接收查询请求的那个节点查询一次,并将其分发到其他节点上。如果不加 GLOBAL 关键字的话,每个节点都会单独发起一次对右表的查询,而右表又是分布式表,就导致右表一共会被查询 N²次(N是该分布式表的分片数量),这就是查询放大,会带来很大开销。

6. 使用字典表

将一些需要关联分析的业务创建成字典表进行 join 操作,前提是字典表不宜太大,因为字典表会常驻内存

标签:hits,join,CounterID,查询,v1,详解,select,ClickHouse
From: https://blog.csdn.net/weixin_44480009/article/details/139922122

相关文章

  • 【数据结构与算法】拓扑排序,关键活动,关键路径 详解
    拓扑排序算法booltopologicalSort(){ stack<int>stk; intid[N]; intcnt=0; for(inti=1;i<=n;i++){ if(!inDeg[i]){ stk.push(i); } id[i]=inDeg[i]; } while(stk.size()){ intt=stk.top(); stk.pop(); cout<<t<......
  • 【数据结构与算法】最小生成树,Prim算法,Kruskal算法 详解
    最小生成树的实际应用背景。最节省经费的前提下,在n个城市之间建立通信联络网。Kruskal算法(基于并查集)voidinit(){for(inti=1;i<=n;i++){pre[i]=i;}}llroot(lla){lli=a;while(pre[i]!=i){i=pre[i];......
  • 【JS逆向百例】某点数据逆向分析,多方法详解
    前言最近收到粉丝的私信,其在逆向某个站点时遇到了些问题,在查阅资料未果后,来询问K哥,K哥一向会尽力满足粉丝的需求。网上大多数分析该站点的教程已经不再适用,本文K哥将提供3种解决方案,对于webpack不太熟练的小伙伴来说,这是一个很好的练手案例:逆向目标目标:某点数据,排行榜......
  • 深入理解泛型(经典详解)
    深入理解泛型(经典详解):<T>T和T的使用以及public<E>List<E>get()泛型方法详解、类型擦除、通配符的使用、泛型类的应用、泛型之间的继承_泛型t-CSDN博客一、为什么要使用泛型?泛型俗称“标签”,使用<E>表示。泛型就是在允许定义类,接口时通过一个标识表示某个属性的类型或者......
  • 纯真IP库查询方法(2024-6-19更新qqwry.dat后无法查询,修改代码)
    2024-6-19更新qqwry.dat后使用pthon38那篇文章里的代码无法查询,使用pythom2的代码,修改之后python3可用,将文件放到工程里查询,不用Lib库里的。修改后的qqwry.py如下,python3可用。coding=utf-8forPython2.7为https://pypi.python.org/pypi/qqwry-py3的Python2版版本:2017-10-......
  • 库分表后复杂查询的应对之道:基于DTS实时性ES宽表构建技术实践
    1问题域业务发展的初期,我们的数据库架构往往是单库单表,外加读写分离来快速的支撑业务,随着用户量和订单量的增加,数据库的计算和存储往往会成为我们系统的瓶颈,业界的实践多数采用分而治之的思想:分库分表,通过分库分表应对存系统读写性能瓶颈和存储瓶颈;分库分表帮我们解决问题的同时......
  • 关于锁的使用,千万不要踩这个坑!(附带Synchronized详解和ZooKeeper、Redis等分布式锁详解
    1、分布式锁在分布式系统中,我们经常会使用各种锁来保证数据的一致性和并发安全。一些常见的分布式锁实现包括:基于ZooKeeper的分布式锁:使用ZooKeeper节点的特性来实现分布式锁。基于Redis的分布式锁:利用Redis的原子性操作和过期时间特性来实现分布式锁。Redlock算法:由......
  • 线程进程以及多线程多进程(超详解)
    目录前言一、什么是进程和线程进程(Process)线程(Thread)多线程(Multithreading)多进程(Multiprocessing)相互组合关系二、资源分配进程私有资源共享资源线程私有资源共享资源多进程私有资源共享资源多线程私有资源共享资源进程的共享和私有资源线......
  • 【Oracle】Oracle数据库查询某张表的全部字段与类型
    【Oracle】Oracle数据库查询某张表的全部字段与类型原文链接:https://blog.csdn.net/LI_AINY/article/details/86597377PS:TABLE_NAME对应的表名要全部大写查询表的所有字段名以及属性(所有用户)SELECT*FROMALL_TAB_COLUMNSWHERETABLE_NAME='T_UNIT_NAME'查询表的所有字......
  • MVCC详解
    什么是MVCC:MVCC(MultiVersionConcurrencyControl的简称),代表多版本并发控制。与MVCC相对的,是基于锁的并发控制,Lock-BasedConcurrencyControl)。MVCC最大的优势:读不加锁,读写不冲突。在读多写少的OLTP应用中,读写不冲突是非常重要的,极大的增加了系统的并发性能学习MVCC前,我们先......