内蒙古城乡建设和住房建设厅网站,wordpress导航图片尺寸,广西钦州住房与城乡建设局网站,如何充实网站内容一、前言
在上一篇我们分享了clickhouse的常用的语法规则优化策略#xff0c;这些优化规则更多属于引擎自带的优化策略#xff0c;开发过程中只需尽量遵守即可#xff0c;然而#xff0c;在开发过程中#xff0c;使用clickhouse更多将面临各种查询sql的编写甚至复杂sql的…一、前言
在上一篇我们分享了clickhouse的常用的语法规则优化策略这些优化规则更多属于引擎自带的优化策略开发过程中只需尽量遵守即可然而在开发过程中使用clickhouse更多将面临各种查询sql的编写甚至复杂sql的编写这就是本篇要探讨的关于clickhouse查询相关的优化策略。 二、关于单表查询相关优化策略 2.1 使用Prewhere 替代 where Prewhere 和 where 语句的作用相同用来过滤数据。不同之处在于 prewhere 只支持 *MergeTree 族系列引擎的表首先会读取指定的列数据来判断数据过滤等待数据过滤 之后再读取 select 声明的列字段来补全其余属性 比如当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能Prewhere 会自动优化 执行过滤阶段的数据读取方式降低 io 操作。某些场景下prewhere 语句比 where 语句处理的数据量更少性能更高。 举例来说我们在使用mysql查询一条数据如下面的这条sql在不考虑索引的情况下正常的执行过程是先扫描表的数据然后过滤出namexxx的符合条件的记录 select u.* from user u where u.name xxx 在clickhouse中默认情况下使用where 过程也是类似但使用了Prewhere 时并不需要进行全表的扫描了这样就大大提升了查询的性能 默认情况下prewhere自动开启 看下面的查询sql,使用explain查看下 EXPLAIN SYNTAX
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 UserID3198390223272470366; 可以看到引擎自动优化为了prewhere查询 关闭 where 自动转 prewhere 开启下面的配置可以关闭prewhere功能 set optimize_move_to_prewhere0; 再次查询可以发现prewhere就关闭了 默认情况肯定不会关闭 where 引擎会自动优化为 prewhere但某些场景即使开启优 化也不会自动转换成 prewhere需要手动指定 prewhere如下 使用常量表达式 使用默认值为 alias 类型的字段 包含了 arrayJOINglobalInglobalNotIn 或者 indexHint 的查询 select 查询的列字段和 where 的谓词相同 使用了主键字段 比如下面的sql在这种情况下就不会使用prewhere 2.2 使用数据采样替代全量查询或统计 在某些情况下比如数据量为TB级别的甚至更大的情况下不需要精确对数据进行统计而是一个预估值时可以通过采样运算这样可极大提升数据分析的性能 SAMPLE 0.1 #代表采样 10%的数据,也可以是具体的条数 SELECT Title,count(*) AS PageViews
FROM hits_v1
SAMPLE 0.1
WHERE CounterID 57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000; 可以发现使用这种方式计算统计结果时性能上有明显的提升 2.3 列裁剪与分区裁剪 列裁剪减少非必要的查询字段 表的数据量太大时应尽量避免使用 select * 操作一般来说查询的性能会与查询的字段大小和数量成线性增长字段越少消耗的 io 资源越少性能就会越高比如下面这样的sql就是需要尽量避免的小编测试在当前服务器下执行界面直接崩掉了select * from datasets.hits_v1; 改成下面这样就会好很多 select WatchID, JavaEnable, Title, GoodEvent, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID
from datasets.hits_v1; 分区裁剪 就是只读取需要的分区在过滤条件中指定 select WatchID, JavaEnable, Title, GoodEvent, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID
from datasets.hits_v1
where EventDate2014-03-23; 2.4 orderby 结合 where、limit 千万量级以上的数据集进行 order by 查询时最好搭配 where 条件和 limit 语句一起使用说到底就是对查询的结果集数量进行限制数据量减少了怎么都好说 SELECT UserID,Age
FROM hits_v1
WHERE CounterID57
ORDER BY Age DESC LIMIT 1000; 2.5 避免构建虚拟列 如非必须不要在结果集上构建虚拟列虚拟列非常消耗资源浪费性能可以考虑在前端进行处理或者在表中构造实际字段进行额外存储 比如下面的sqlIncome/Age 这个就是一个虚拟列表中并不存在这个字段而是处于业务的需要构建出来的不仅是clickhouse甚至mysql中这种写法也应该是尽量要避免的 SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1; 2.6 使用uniqCombined 替代 distinct 使用uniqCombined 替代 distinct 性能可提升 10 倍以上uniqCombined 底层采用类似 HyperLogLog 算法实现能接收 2%左右的数据误差可直接使用这种去重方式提升查询性能 Count(distinct )会使用 uniqExact精确去重不建议在千万级不同数据上执行 distinct 去重查询改为近似去重 uniqCombined 比如查询时可以使用下面这种方式 SELECT uniqCombined(rand()) from datasets.hits_v1; 2.7 其他单表查询中需要注意的事项 一查询熔断 为了避免因个别慢查询引起服务雪崩问题除了可以为单个查询设置超时以外还可以配置周期熔断在一个查询周期内如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。 二关闭虚拟内存 物理内存和虚拟内存的数据交换会导致查询变慢资源允许的情况下建议关闭虚拟内存 三配置 join_use_nulls 为每一个账户添加 join_use_nulls 配置左表中的一条记录在右表中不存在右表的相应字段会返回该字段相应数据类型的默认值而不是标准 SQL 中的 Null 值 四批量写入时先排序 批量写数据时必须控制每个批次的数据中涉及到的分区的数量在写入之前最好对需要导入的数据进行排序。因为无序的数据或者涉及的分区太多会导致 ClickHouse 无法及时对新导入的数据进行合并从而影响查询性能。 五关注 CPU cpu 一般在 50%左右会出现查询波动达到 70%会出现大范围的查询超时cpu 是最关 键的指标因此运维过程中要非常关注。 三、关于多表关联查询相关优化策略 前置准备 依次执行下面的sql从源表中复制出两个新表并从源表中导入部分数据 #创建小表
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 10; 3.1 使用in代替join 当多表联合查询时当最终查询的数据仅从其中一张表出来时可考虑用 IN 操作而不是使用JOIN 这个和clickhouse的自身的关联查询数据加载机制有关比如A join B时需要先把B加载到内存中然后从A中查询的结果跟内存中B的数据进行匹配如果B表的数据量非常大很有可能造成内存被耗尽 正确的使用方式 insert into hits_v2
select a.* from hits_v1 a where a. CounterID in (select CounterID from
visits_v1); 反例 insert into table hits_v2
select a.* from hits_v1 a left join visits_v1 b on a. CounterIDb.
CounterID; 3.2 大小表 JOIN 多表join时尽量要满足小表在右的原则右表关联时被加载到内存中与左表进行比较ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在所以右表必须是小表这一点必须注意。 下面用两个sql分别模拟下小表在左和小表在右的情况 小表在右 insert into table hits_v2
select a.* from hits_v1 a left join visits_v2 b on a. CounterIDb.
CounterID; 通过执行sql耗时大概在37秒多 大表在右 insert into table hits_v2
select a.* from visits_v2 b left join hits_v1 a on a. CounterIDb.
CounterID; 内存超过限制了直接执行失败 3.3 谓词下推 ClickHouse在join查询时不会主动发起谓词下推的操作需要每个子查询提前完成过滤操作要注意的是是否执行谓词下推对性能影响差别很大新版本中已经不存在此问题但是需要注意谓词的位置的不同依然有性能的差异 下面的sql会被优化为 prewhere Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a. CounterIDb.
CounterID
having a.EventDate 2014-03-17;Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a. CounterIDb.
CounterID
having b.StartDate 2014-03-17 3.4 分布式表使用GLOBAL 在真实的线上环境clickhouse通常多节点部署在这种情况下如果两张分布式表上在使用 IN 和 JOIN 之前必须加上 GLOBAL 关键字右表只会在接收查询请求的那个节点查询一次并将其分发到其他节点上。如果不加 GLOBAL 关键字的话每个节点都会单独发起一次对右表的查询而右表又是分布式表就导致右表一共会被查询 N²次N是该分布式表的分片数量这就是查询放大会带来很大开销 3.5 使用字典表 将一些需要关联分析的业务创建成字典表进行 join 操作前提是字典表不宜太大因为字典表会常驻内存 3.6 提前过滤数据 通过增加逻辑过滤以减少数据行的扫描达到提高执行速度及降低内存消耗的目的这个和mysql的思想是一致的
