ClickHouse(二)优化

ClickHouse优化

执行计划

    AST(语法树)、SYNTAX(优化后的SQL语句)、PIPELINE(查看PIPELINE计划，可看线程数)

建表优化

    数据类型优化：1.限定好数据类型  2.使用空值会对性能产生负面影响  
    分区索引优化：1.分区按照天分区(一亿分区)  
        2.order by(就是索引列) 查询高的放前面,重复值少的尽量不索引(intHash32(userID))
    表参数：index_granularity = 8192   TTL设置
    写入删除优化：最好不要执行单条、小批量删除和插入操作。一次不要写太多分区。
        2-3/s次写入，2w-5w/次。可能会出现：Too many parts 
        使用WAL预写日志(开启)，提高写入性能

资源优化

    background_pool_size=16                          //CPU个数的2倍
    background_schedule_pool_size=16                 //CPU个数的2倍
    background_distributed_schedule_pool_size=16     //CPU个数的2倍
    max_concurrent_queries=100                       //150-300 最大并发处理请求
    max_threads=cpu核数                              //单个查询能使用的最大cpu个数
    max_memory_usage       //单个查询的内存，128G的机器设置为100G
    max_bytes_before_external_group_by/max_memory_usage一半，
        group使用内存超过值后会使用磁盘 50G
    max_bytes_before_external_sort/max_memory_usage一半，
        order使用内存超过值后会使用磁盘 50G
    max_table_size_to_drop/删除表的时候超过50G就是报错  0多大就可以删

    存储：固态比机械块2-3倍，提升IO。clickhouse不支持多目录。绑定多块磁盘，挂载虚拟组。

ClickHouse语法优化规则

    count 不要写具体的字段
    having 下推到where  /  外部查询自动下推到子查询  /  UNION ALL 后的where 下推到内部
    聚合外推：sum(a *2) 优化成sum(a )*2
    聚合消除：groupby的字段然后求 min,max  优化后会去掉
    标量替换：将sum(aa)/sum(bb)  将aa，bb替换为标量  后面替换回来
    三元运算符：替换为multiIf()

单表查询优化

    1.where优化成prewhere  先查询where字段，然后补全对应字段/默认会自动优化
        不能优化的场景：常量表达式，主键字段，where和select相同
    2.采样操作：from table_name SAMPLE 0.1   //采样数据进行操作
    3.列裁剪和分区裁剪
    4.order by 结合where limit 使用
    5.避免构建虚拟列：a列/b列   最好在前端处理
    6.uniqCombined替代distinct 近似去重替代完全去重
    7.使用物化视图
    8.查询时长、关闭虚拟内存、配置join_use_nulls、批量写入的时候先排序(分区)、
        CPU超过50%出现查询波动，CPU70%要关注

多表查询优化

    (右全部写入内存，轮询左表)
    1.in 代替 join 
    2.小表在右
    3.谓词下推 join之前进行过滤 提前过滤
    4.分布式表需要使用 GLOBAL
    5.使用字典表(常驻内存)  

如何数据一致性

    1.grouo by 去重 
    2.FINAL ,查询语句前面加 (部分引擎支持)

物化视图

    真正的保存数据。对历史数据进行去重在物化视图里面是不好用的
    create MATERIALIZED view if not exists my_view engine=mergeTree 
    partition by aa order by bb as select ...