ClickHouse优化典藏

• 建表时能用数值型或日期时间型表示的字段就不要用字符串，全String类型在以Hive为中心的数仓建设中常见，但ClickHouse环境不应受此影响。

• 虽然ClickHouse底层将DateTime存储为时间戳Long类型，但不建议存储Long类型，因为DateTime不需要经过函数转换处理，执行效率高、可读性好。

• 官方已经指出Nullable类型几乎总是会拖累性能，因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记，并且Nullable列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值（例如用-1表示没有商品ID）。

1.2 分区和索引

• 分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为Tuple()，以单表一亿数据为例，分区大小控制在10-30个为最佳。

• 必须指定索引列，ClickHouse中的索引列即排序列，通过order by指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、查询频率大的在前原则；还有基数特别大的不适合做索引列，如用户表的userid字段；通常筛选后的数据满足在百万以内为最佳。

• Index_granularity是用来控制索引粒度的，默认是8192，如非必须不建议调整。

• 如果表中不是必须保留全量历史数据，建议指定TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL 也可以通过alter table语句随时修改。

• 使用Prewhere替代where，当查询列明显多于筛选列时使用Prewhere可十倍提升查询性能，Prewhere会自动优化执行过滤阶段的数据读取方式，降低io操作。

# Prewhere会自动优化执行过滤阶段的数据读取方式，降低io操作
select * from school where class='xxxx' and (id='11' or code='doctor')
# 替换where关键字
select * from school prewhere class='xxxx' and (id='11' or code='doctor')

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

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

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

  反例：

select * from test.test;

        正例：

select login_id,name,sex from test.test;

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

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

• 使用uniqCombined替代distinct性能可提升10倍以上，uniqCombined底层采用类似HyperLogLog算法实现，能接收2%左右的数据误差，可直接使用这种去重方式提升查询性能。

• 对于一些确定的数据模型，可将统计指标通过物化视图的方式进行构建，这样可避免查询时重复计算的过程，物化视图会在有新数据插入时进行更新。

#通过物化视图提前预计算用户下载量
CREATE MATERIALIZED VIEW download hour_mv 
ENGINE =SummingMergeTree
PARTITION BY toYYYYMM(hour) ORDER BY (userid, hour) 
AS SELECT
toStartOfHour(when) AS hour,
userid,
count() as downloads,
sum(bytes) AS bytes
FROM download WHERE when >= toDateTime (2021-01-01 00:00:00") #设置更新点,该时间点之前的数据可以通过insert intoselect的方式进行插入
GROUP BY userid, hour

##或者
CREATE MATERIALIZED VIEW db.table_MV TO db.table new ##table new可以是一张mergetree表
AS SELECT FROM db.table old;

#不建议添加populate关键字进行全量更新

• 不建议在高基列上执行distinct去重查询，改为近似去重uniqCombined

        反例：

SELECT count( DISTINCT create_user ) from  app.scene_model

        正例：

SELECT uniqCombined( create_user ) from  app.scene_model

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

select a.* from a where a.uid in (select uid from b) 
#不要写成
select a.* from a left join b on a.uid=b.uid

• 多表join时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较，ClickHouse中无论是Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。
• ClickHouse在join查询时不会主动发起谓词下推的操作，需要每个子查询提前完成过滤操作，需要注意的是，是否执行谓词下推，对性能影响差别很大（新版本中已经不存在此问题，但是需要注意谓词的位置的不同依然有性能的差异）
• 将一些需要关联分析的业务创建成字典表进行join操作，前提是字典表不易太大，因为字典表会常驻内存

ENGINE = Dictionary (dict name)
或者
create database db dicENGINE = Dictionary

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

• 尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge任务带来巨大压力

• 不要一次写入太多分区，或数据写入太快，数据写入太快会导致Merge速度跟不上而报错，一般建议每秒钟发起2-3次写入操作，每次操作写入2w~5w条数据（依服务器性能而定）

报错信息

1. Code: 252, e.displayText() = DB::Exception: Too many parts(304). Merges are processing significantly slower than inserts
2. Code: 241, e.displayText() = DB::Exception: Memory limit (for query) exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888 bytes), maximum: 9.31 GiB

• “ Too many parts 处理 ” 详情请点击：ClickHouse新功能之WAL

• 在服务器内存充裕的情况下增加内存配额，一般通过max_memory_usage来实现
• 在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过max_bytes_before_external_group_by、max_bytes_before_external_sort参数来实现

CREATE DATABASE test ENGINE =MaterializeMySQL('192.168.0.88:3306', 'ck_test','root', '123")

        ClickHouse的数据导入，数据同步消耗的时间是数据计算时间的十几倍，通过以下导入方式再加上缓存、批处理等机制封装成新的同步工具，提高同步效率。

catfilename.orc |clickhouse-client --query="INSERT INTO test FORM AT ORC"

4.4 查询熔断

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

• 关闭虚拟内存，物理内存和虚拟内存的数据交换，会导致查询变慢。

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

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

• 尽量减少JOIN时的左右表的数据量，必要时可以提前对某张表进行聚合操作，减少数据条数。有些时候，先group by再join比先join再group by查询时间更短。

• ClickHouse的分布式表性能性价比不如物理表高，建表分区字段值不宜过多，防止数据导入过程磁盘可能会被打满。

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

上一篇：为什么MySQL会选错索引？

近期文章推荐：

CllickHouse 部署架构和国内大厂应用实践

MergeTree的Merge和Mutation机制

MergeTree的存储结构和查询加速

更多精彩内容，欢迎关注微信公众号