ClickHouse性能优化典藏
一、 基础优化
1 表优化
1.1 数据类型
建表时能用数值型或日期时间型表示的字段就不要用字符串，全String类型在以Hive为中心的数仓建设中常见，但ClickHouse环境不应受此影响。
虽然ClickHouse底层将DateTime存储为时间戳Long类型，但不建议存储Long类型，因为DateTime不需要经过函数转换处理，执行效率高、可读性好。
官方已经指出Nullable类型几乎总是会拖累性能，因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记，并且Nullable列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值（例如用-1表示没有商品ID）。
1.2 分区和索引
分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为Tuple()，以单表一亿数据为例，分区大小控制在10-30个为最佳。
必须指定索引列，ClickHouse中的索引列即排序列，通过order by指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、查询频率大的在前原则；还有基数特别大的不适合做索引列，如用户表的userid字段；通常筛选后的数据满足在百万以内为最佳。
1.3 表参数
Index_granularity是用来控制索引粒度的，默认是8192，如非必须不建议调整。
如果表中不是必须保留全量历史数据，建议指定TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL 也可以通过alter table语句随时修改。
二、 查询优化
2.1 单表查询
使用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
采样修饰符只有在MergeTree engine表中才有效，且在创建表时需要指定采样策略。
数据量太大时应避免使用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
2.2 多表关联
当多表联查是，查询的数据仅从其中一张表出时，可考虑用 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参数来实现
四、 运维优化
4.1 配置
配置 描述
backgroup_pool_size
后台线程池的大小，merge线程就是在该线程池中执行，该线程池不仅仅是给merge线程用的，默认值16，允许的前提下建议改成cpu个数的2倍。
log_queries 默认值为0，修改为1，系统会自动创建system_query_log表，并记录每次查询的query信息 。
max_execution_time 设置单次查询的最大耗时时，单位是秒；默认无限制；需要注意的是客户端的超时设置会覆盖该参数
max_concurrent_queries 最大并发处理的请求数(包含select,insert等)，默认值100，推荐150(不够再加)。
max_threads 设置单个查询所能使用的最大cpu个数，默认是cpu核数
max_memory_usage 此参数在config.xml 中,表示单次Query占用内存最大值，该值可以设置的比较大，这样可以提升集群查询的上限。
max_bytes_before_external_group_by 一般按照max_memory_usage的一半设置内存，当group使用内存超过阈值后会刷新到磁盘进行。
max_bytes_before_external_sort 当order by已使用max_bytes_before_external_sort内存就进行溢写磁盘(基于磁盘排序)，如果不设置该值，那么当内存不够时直接抛错，设置了该值order by可以正常完成，但是速度相对存内存来说肯定要慢点(实测慢的非常多，无法接受)。
max_table_size_to_drop 此参数在 config.xml 中，应用于需要删除表或分区的情况，默认是50GB，意思是如果删除50GB以上的分区表会失败。建议修改为0，这样不管多大的分区表都可以删除。
4.2 存储
ClickHouse不支持设置多数据目录，为了提升数据io性能，可以挂载虚拟券组，一个券组绑定多块物理磁盘提升读写性能，多数据查询场景SSD会比普通机械硬盘快2-3倍。
4.3 数据同步
新版clickhouse提供了一个实验性的功能，就是讲ClickHouse伪装成一个MySQL的备库去实时拉取MySQL中的数据，当MySQL库表数据发生变化时会实时同步导入ClickHouse中，这样就省掉了单独维护实时spark/flink任务读取kafka数据再存入clickhouse的环节，大大降低了运维成本提升了效率。
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是最关键的指标，要非常关注。
