程序员经验分享-hwhale

ClickHouse之单表多表查询优化

2023-10-27

1 本文简介

本文的意义是在使用过程中，对一些查询进行一些优化，使查询效率提升。
无论是在单表查询，还是在多表查询，或者是分布式表的查询。

2 单表查询优化

1 Prewhere 替代 where

  • Prewhere 和 where 语句的作用相同，用来过滤数据。
  • 不同之处在于 prewhere 只支持 MergeTree 族系列引擎的表首先会读取指定的列数据，来判断数据过滤，等待数据过滤 之后再读取 select 声明的列字段来补全其余属性。
  • 当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化 执行过滤阶段的数据读取方式，降低 io 操作。
  • 在某些场合下，prewhere 语句比 where 语句处理的数据量更少性能更高。
#关闭 where 自动转 prewhere(默认情况下， where 条件会自动优化成 prewhere) set optimize_move_to_prewhere=0;
# 使用 where
select WatchID,
   JavaEnable,
   Title,
   GoodEvent,
   ... ...
from datasets.hits_v1 **where** UserID='3198390223272470366';

# 使用 prewhere 关键字 
select WatchID,
	JavaEnable,
	Title,
	GoodEvent,
	... ...
from datasets.hits_v1 **prewhere** UserID='3198390223272470366';

**默认情况，我们肯定不会关闭 where 自动优化成 prewhere，**但是某些场景即使开启优
化，也不会自动转换成 prewhere，需要手动指定 prewhere:

  1. ⚫ 使用常量表达式

  2. ⚫ 使用默认值为alias类型的字段

  3. ⚫ 包含了arrayJOIN，globalIn，globalNotIn或者indexHint的查询

  4. ⚫ select查询的列字段和where的谓词相同

    select UserID from datasets.hits_v1 where UserID='3198390223272470366'

  5. ⚫ 使用了主键字段

    主键字段：order by 字段

2 数据采样

通过采样运算可极大提升数据分析的性能

SELECT 
	Title,
	count(*) AS PageViews
FROM hits_v1
SAMPLE 0.1 #代表采样 10%的数据,也可以是具体的条数 
WHERE CounterID =57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000

采样修饰符只有在 MergeTree engine 表中才有效，且在创建表时需要指定采样策略。

3 指定select字段查询与分区裁剪

数据量太大时应避免使用 select * 操作，查询的性能会与查询的字段大小和数量成线性
表换，字段越少，消耗的 io 资源越少，性能就会越高。

反例:

select * from datasets.hits_v1;

正例:

select WatchID,
   JavaEnable,
   Title,
   GoodEvent,EventTime,
   EventDate,
   CounterID,
   ClientIP,
   ClientIP6,
   RegionID,
   UserID
from datasets.hits_v1;

分区裁剪就是只读取需要的分区，在过滤条件中指定。也就是说：每次查询要在where后加上order by的字段进行查询，比如创建表时指定

partition by toYYYYMMDD(EventDate) order by (EventDate,UserID)，那么在查询时where条件指定分区查询。比如：

select WatchID,
   JavaEnable,
   Title,
   GoodEvent,
   EventTime,
   EventDate,
   CounterID,
   ClientIP,
   ClientIP6,
   RegionID,
   UserID
from datasets.hits_v1
where EventDate='2014-03-23';

4 orderby结合 where、limit

千万以上数据集进行 order by 查询时需要搭配 where 条件和 limit 语句一起使用。

正例:

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 避免构建虚拟列

虚拟列：原始表不存在的字段，查询语句虚拟出来的字段

如非必须，不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。
反例:

SELECT Income,Age,**Income/Age as IncRate** FROM datasets.hits_v1;

正例:

拿到 Income 和 Age 后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储

SELECT Income,Age FROM datasets.hits_v1;

6 uniqCombined 替代 distinct

性能可提升 10 倍以上，uniqCombined 底层采用类似 HyperLogLog 算法实现，**能接收 2% 左右的数据误差，可直接使用这种去重方式提升查询性能。**Count(distinct )会使用 uniqExact 精确去重。
不建议在千万级不同数据上执行 distinct 去重查询，改为近似去重 uniqCombined

反例:

select count(distinct userName) from hits_v1;

正例:

SELECT uniqCombined(userName) from datasets.hits_v1

7 使用物化视图

什么是物化视图请关注本博客，或者关注博主公众号

img

8 其他注意事项

(1)查询熔断

为了避免因个别慢查询引起的服务雪崩的问题，除了可以为单个查询设置超时以外，还
可以配置周期熔断，在一个查询周期内，如果用户频繁进行慢查询操作超出规定阈值后将无
法继续进行查询操作。

(2)关闭虚拟内存

物理内存和虚拟内存的数据交换，会导致查询变慢，资源允许的情况下关闭虚拟内存。

(3)配置 join_use_nulls

为每一个账户添加join_use_nulls配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准 SQL 中的 Null 值。

(4)批量写入时先排序

批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致 ClickHouse 无法及时对新导入的数据进行合并，从而影响查询性能。

(5)关注 CPU

cpu 一般在 50%左右会出现查询波动，达到 70%会出现大范围的查询超时，cpu 是最关键的指标，要非常关注。

3 多表查询优化

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（强烈建议）

当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是 JOIN

原因：无论是left join、right join或者是inner join，CK都会把join右边的表全部加载到内存中，然后和左边的表一一匹配，如果右表足够大，内存将会爆掉。

insert into 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 left join visits_v1 b on a. CounterID=b. CounterID;

3 大小表 JOIN

多表 join 时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较，ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。

(1)小表在右

select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b.CounterID;

(2)大表在右

insert into table hits_v2 select a.* from visits_v2 b left join hits_v1 a on a. CounterID=b.CounterID;

4 注意谓词下推(版本差异)

ClickHouse 在join 查询时不会主动发起谓词下推的操作，需要每个子查询提前完成过滤操作，需要注意的是，是否执行谓词下推，对性能影响差别很大(新版本中已经不存在此问 题，但是需要注意谓词的位置的不同依然有性能的差异)

#使用join查询
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 关键字的话，每个节点 都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 N2次(N 是该分布式表的分片数量)，这就是查询放大，会带来很大开销。

img

如上图所示，

  1. 红色箭头是使用global关键字的结果，也就是如果在分片1查询时会对分布式表B发起N次查询
  2. 黄色箭头是未使用global关键字，一个箭头代表发起N次查询请求

6 使用字典表

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

7 提前过滤

通过增加逻辑过滤可以减少数据扫描，达到提高执行速度及降低内存消耗的目的

本文作者：好名字
原文链接：https://www.cuizb.top/myblog/article/1639839719
版权声明： 本博客所有文章除特别声明外，均采用 CC BY 3.0 CN协议进行许可。转载请署名作者且注明文章出处。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

clickhouse

Java

数据库

大数据

ClickHouse之单表多表查询优化 的相关文章

随机推荐

  • 【嵌入式】STM32利用arm-dsp库进行PID调节控制

    目录 一 实验简介 1 原理 2 所用外设 二 代码 1 PWM输出配置 2 定时器触发的DMA传输的ADC 3 主体代码 三 实验结果 在工程实际中 应用最为广泛的调节器控制规律为比例 积分 微分控制 简称 PID 控制 又称 PID调节

  • 重回csdn

    前段时间csdn帐号被盗 后来帐号被封 今刚找回来 所有博文付之一炬

  • Windows与Centos下的深度学习部署

    文章介绍2种深度学习部署思路 1 Windows下部署深度学习 确保只用本地机器也可以跑深度学习 GPU版本 2 Centos下部署深度学习 Windows作为可视化界面 利用Centos的CPU跑深度学习 CPU版本 一 Windows1

  • 程序设计综合实习(C语言):学生成绩文件管理

    一 目的 1 掌握文件指针的概念和运用 2 掌握文件的相关操作 打开 读 写 关闭 3 掌握文件的定位操作 二 实习环境 Visual Stdio 2022 三 实习内容与步骤 1 定义一个结构体数组 存放10个学生的学号 姓名 三门课的成

  • 转：人生比努力更重要的是选择，与时俱进是成功的源泉。

    如果有一天你隔壁开火锅店的张三 卖手机卖得比你好 你不用觉得惊讶 因为 这是一个跨界的时代 每一个行业都在整合 都在交叉 都在相互渗透 移动互联网改变生活 未来我们的生活会是什么样子呢 我们设想一下 晚上带着家人去吃饭 拿出手机点击附近餐厅

  • 汇编语言程序设计的实验环境及上机步骤

    汇编语言源程序编制完成后 在计算机上的操作过程分为四个阶段 编辑 汇编 连接 运行调试 如图1 一 实验环境 汇编语言程序设计的实验环境如下 硬件环境 微型计算机 Intel x86系列CPU 一台 软件环境 Windows98 2000

  • 类和对象

    一 类 每个类都定义了自己的作用域 每个类的成员函数和成员变量都在这个作用域内 成员函数可以任意访问所在类的其他函数和成员变量 C 的封装特性是对外的 在类内是可以任意调用和使用的 类似于C中的结构体 要访问类定义的对象的成员 类定义的对象

  • 通过css实现对勾（√）和叉号（×）

    通过css实现对勾 和叉号 第一种方式 HTML span class status correct span span class status incorrect span CSS correct display inline bloc

  • 字符编码和字符集

    编码和解码 按照某种规则 将字符存储到计算机中 称为 编码 那么 将计算机中的二进制数据按照某种规则解析显示出来 称为 解码 字符编码 就是一套自然语言的字符和二进制数据之间的对应规则 把这种对应规则 写成一张表 就是 编码表 也叫字符集

  • kv遍历

    var builder var builderKey var builderValue for var i 0 i lt message length i var c message charAt i var nextStr message

  • 互联网高可用架构探讨

    高可用指标与问题 高可用 英文单词High Availability 缩写HA 它是分布式系统架构设计中一个重要的度量 业界通常用多个9来衡量系统的可用性 如下表 既然有可用率 有一定会存在不可用的情况 系统宕机一般分为有计划的和无计划的

  • 数码管：3位6脚的数码管分析和编码

    基本简介 一个1位数码管共有8段 ABCDEFG 小数点 如下图 按照我们常规的设计方案一个 IO 口控制一个段 一个1位数码管那么就需要8个IO 当然我们也可以使用扩展 IO 芯片 例如 74HC164 74HC595 那么这种方案就占用

  • Linux 中卷组不真实存在情况下，强制删除卷组及其PV信息

    由于手误删除lVm顺序不对 导致真实pV 已经不存在 但是卷组信息及其PV信息还存在系统中 pvdisplay和vgdisplay都能看到信息 这时候可以到 dev 目录下手动rm 掉 pv 再查看卷组 就真的不存在了

  • Android取消点击效果,Android中如何取消listview的点击效果

    Android中如何取消listview的点击效果 在xml文件里面有listselecter的属性引用 android layout width match parent android layout height wrap conten

  • 【3D游戏基础】蒙皮骨骼动画与骨架

    效果 目标 画出蒙皮动画的骨架 视频 https www bilibili com video BV1pM411m7Yw PPT https zfxdvouj61 feishu cn file boxcnwgESO6zdQetO7oNhKb

  • vant Cascader 级联选择 异步加载

  • .suo介绍

    suo suo是一种文件的格式 suo 解决方案用户选项 记录所有将与解决方案建立关联的选项 以便在每次打开时 它都包含用户所做的自定义设置 比如VS布局以及项目最后编译的而又没有关掉的文件用于下次打开时用 其中 VS布局包括 监视器123

  • Linux性能测试工具

    在Linux Benchmark Suite Homepage网站上列举了诸多Linux性能测试工具 包括CPU RAM ROM Cache net等性能测试 lmbench 测试工具 这个工具集中包含以下几种测试工具 我们主要使用到bw

  • 织梦DedeCMS移动站的搜索功能实现方法

    很早之前在做博客的时候 就考虑到把搜索功能在移动端实现 初略的做完后 发现移动端总是会跳转到pc模版 很不友好 前几天 刚好有时间就把这个功能给实现了 其实很简单 接下来放出教程 一 复制 plus search php文件 打开PC端的文

  • ClickHouse之单表多表查询优化

    文章目录 1 本文简介 2 单表查询优化 1 Prewhere 替代 where 2 数据采样 3 指定select字段查询与分区裁剪 4 orderby结合 where limit 5 避免构建虚拟列 6 uniqCombined 替代

热门标签