clickhouse 优化

谁借莪１个温暖的怀抱￠ 2023-01-20 09:40 87阅读 0赞

### ** clickhouse SQL优化技巧** ###

1.  sql慢查大部分主要体现在cpu 负载过高，io过高，或者查询的列中无索引导致的；注意；clickhouse本身不太支持高并发的场景，qps过高会导致clickhouse服务器cpu过高，导致慢查
2.  在这些情况下;常见的考虑的是 sql中是否有复杂的运算，查询的数量量是否过大，查询的列中索引是否有效；
3.  sql 查询特点：数量大，且分区跨度大
4.  data表格中有8亿多条数据，data表按照p\_data\_day 分区；
5.  数据会遍历整个分区，数据平均在1s左右分钟返回 ;
6.  优化思路：减少不必要数据的遍历（分区）；充分利用clickhouse 索引（group by 索引）
7.  针对sn的查询，建立物化视图；将8亿条数据按照sn号以及device\_id（mac\_code）建立256个分区；
8.  create MATERIALIZED VIEW IF NOT EXISTS data\_sn\_materialized
9.  engine = ReplicatedMergeTree('/clickhouse/tables/\{ck\_cluster\}/data\_sn\_materialized', '\{replica\}')
10. PARTITION BY sn\_sort\_key ORDER BY (sn\_sort\_key,sn,p\_day)
11. AS select halfMD5(\_sn) % 256 as sn\_sort\_key,sn,p\_day,count() as cnt
12.  from data group by sn\_sort\_key,sn,p\_day;
13. 优化后

查询语句；保持原来的出参和入参不变，数据能够在200ms以内返回，

================================================================

# [ClickHouse 的一些优化参数][ClickHouse] #

1.  max\_table\_size\_to\_drop
    
     *  此参数在 /etc/clickhouse-server/config.xml 中, 应用于需要删除表或分区的情况, 默认 50GB。
     *  如果你要删除的分区或表, 数据量达到了此参数值大小, 会删除失败。
     *  建议修改为 0, 代表无论数据多大, 都可以删除。
2.  max\_memory\_usage
    
     *  在 /etc/clickhouse-server/user.xml中, 表示单次查询占用内存最大值
     *  超过此值, 则请求失败, 建议在资源足够的情况下尽量调大。
3.  删除多个节点上的同一张表
    
     *  使用on cluster 关键字指定集群
        
            drop table t on cluster clickhouse_cluster
4.  自动数据备份
    
     *  只有 MergeTree 系列里的表可支持副本, 在表引擎名称上加上 Replicated 前缀。
     *  要实现数据备份, 需要配置Zookeeper, 在metrika.xml 中添加即可
     *  副本设置: metrika.xml中添加:
        
            <macros> <shard>01</shard> <replica>01</shard> </macros> <macros> <shard>01</shard> <replica>02</shard> </macros> <macros> <shard>02</shard> <replica>01</shard> </macros> <macros> <shard>02</shard> <replica>02</shard> </macros> <macros> <shard>03</shard> <replica>01</shard> </macros> <macros> <shard>03</shard> <replica>02</shard> </macros>

==========================================================

# clickhouse SQL优化技巧 #

sql慢查大部分主要体现在cpu 负载过高，io过高，或者查询的列中无索引导致的；注意；clickhouse本身不太支持高并发的场景，qps过高会导致clickhouse服务器cpu过高，导致慢查

在这些情况下;常见的考虑的是 sql中是否有复杂的运算，查询的数量量是否过大，查询的列中索引是否有效；

### sql 查询特点：数量大，且分区跨度大 ###

data表格中有8亿多条数据，data表按照p\_data\_day 分区；

select sn,COUNT(1) as valueQt from data WHERE   sn='70A0600018109' and p_day >= '2017-01-01' and p_data_day < '2020-08-13'
    group by sn;

数据会遍历整个分区，数据平均在1s左右分钟返回 ;

优化思路：减少不必要数据的遍历（分区）；充分利用clickhouse 索引（group by 索引）

*  针对sn的查询，建立物化视图；将8亿条数据按照sn号以及device\_id（mac\_code）建立256个分区；

create MATERIALIZED VIEW IF NOT EXISTS data_sn_materialized
    engine = ReplicatedMergeTree('/clickhouse/tables/{ck_cluster}/data_sn_materialized', '{replica}')
    PARTITION BY sn_sort_key ORDER BY (sn_sort_key,sn,p_day)
    AS select halfMD5(_sn) % 256 as sn_sort_key,sn,p_day,count() as cnt
     from data group by sn_sort_key,sn,p_day;

优化后

查询语句；保持原来的出参和入参不变，数据能够在200ms以内返回，

### sql 查询特点：数量大，且分区跨度大 ###

data 表格数据量在10亿多条，建表语句如下

CREATE TABLE data (
    `data_day` Date,
     `flow_type` UInt32 DEFAULT CAST(0,
     'UInt32'),
    .....
    ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{ck_cluster}/data', '{replica}') PARTITION BY data_day ORDER BY (flow_type, data_day) SETTINGS index_granularity = 8192;

查询语句

select ... from data where data_day = '2020-09-11'

我们观察到查询数据的时候，总是会具体到昨天；而且历史的数据不会再使用；

优化思路： 使用clickhouse的TTL，减少表容量，

CREATE TABLE dwrt.lc_order_flow (
    `data_day` Date,
     .....
     `flow_type` UInt32 DEFAULT CAST(0,
     'UInt32'),
    ....
    ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{ck_cluster}/data', '{replica}') PARTITION BY data_day ORDER BY (data_day, flow_type) TTL data_day + toIntervalDay(7) SETTINGS index_granularity = 8192;

====================================

**ClickHouse 性能优化**

似梦似意境 2020-08-12 23:40:52  1075  已收藏 1  
分类专栏： \# ClickHouse  
版权  
1. max\_table\_size\_to\_drop

此参数在 /etc/clickhouse-server/config.xml 中，应用于需要删除表或分区的情况，默认是50GB，意思是如果删除50GB以上的分区表会失败。建议修改为0，这样不管多大的分区表都可以删除。

2. max\_memory\_usage

此参数在 /etc/clickhouse-server/config.xml  中,表示单次Query占用内存最大值，超过的话会查询失败。建议尽量调大一些。

3.删除多个节点的同一张表

drop table 表名 on cluster clickhouse集群名称

4.自动数据备份

只有MergeTree系列里的表可支持副本，在表引擎名称上加上Replicated前缀，例如ReplicatedMergeTree。

同时必须配置zookeeper，在/etc/metrika.xml中加入相应配置即可。

5.尽量在ClickHouse少进行Join 操作