ClickHouse的一些优化操作（五）
目录
一 建表优化
1 数据类型
1.1 时间字段的类型
建表时能用数值型或日期时间型表示的字段就不要用字符串，全 String 类型在以 Hive 为中心的数仓建设中常见，但 ClickHouse 环境不应受此影响。 虽然 ClickHouse 底层将 DateTime 存储为时间戳 Long 类型，但不建议存储 Long 类型， 因为 DateTime 不需要经过函数转换处理，执行效率高、可读性好。
create table t_type2( id UInt32, sku_id String, total_amount Decimal(16,2) , create_time Int32) engine =ReplacingMergeTree(create_time)partition by toYYYYMMDD(toDate(create_time)) –-需要转换一次，否则报错 primary key (id) order by (id, sku_id);1.2 空值存储类型
官方已经指出Nullable类型几乎总是会拖累性能，因为存储Nullable列时需要创建一个 额外的文件来存储 NULL 的标记，并且 Nullable 列无法被索引。因此除非极特殊情况，应直 接使用字段默认值表示空，或者自行指定一个在业务中无意义的值(例如用-1 表示没有商品 ID)。
CREATE TABLE t_null( x Int8, y Nullable(Int8)) ENGINE TinyLog;INSERT INTO t_null VALUES (1, NULL), (2, 3);SELECT x + y FROM t_null;官网说明:https://clickhouse.tech/docs/zh/sql-reference/data-types/nullable/
2 分区和索引
分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为 Tuple()， 以单表一亿数据为例，分区大小控制在 10-30 个为最佳。 必须指定索引列，ClickHouse 中的索引列即排序列，通过 order by 指定，一般在查询条 件中经常被用来充当筛选条件的属性被纳入进来;可以是单一维度，也可以是组合维度的索 引; 通常需要满足高级列在前、查询频率大的在前原则; 基数特别大的不适合做索引列， 如用户表的 userid 字段; 通常筛选后的数据满足在百万以内为最佳。
比如官方案例的 hits_v1 表:
...... PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID))......visits_v1 表:
...... PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) ......3 表参数
Index_granularity 是用来控制索引粒度的，默认是 8192，如非必须不建议调整。 如果表中不是必须保留全量历史数据，建议指定 TTL(生存时间值)，可以免去手动过期 历史数据的麻烦，TTL 也可以通过 alter table 语句随时修改。
4 写入和删除优化
(1)尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台 Merge 任务带来巨大压力 (2)不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不 上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据(依服务器 性能而定)
写入过快的报错信息：
1. Code: 252, e.displayText() = DB::Exception: Too many parts(304).Merges are processing significantly slower than inserts2. Code: 241, e.displayText() = DB::Exception: Memory limit (for query)exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888bytes), maximum: 9.31 GiB处理方式:
“ Too many parts 处理 ” :使用 WAL 预写日志，提高写入性能。 in_memory_parts_enable_wal 默认为 true 在服务器内存充裕的情况下增加内存配额，一般通过 max_memory_usage 来实现 在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数 来实现。
5 常见配置
配置项主要在 config.xml 或 users.xml 中， 基本上都在 users.xml 里 ➢ config.xml的配置项 https://clickhouse.tech/docs/en/operations/server-configuration-parameters/settings/ ➢ users.xml的配置项 https://clickhouse.tech/docs/en/operations/settings/settings/
5.1 CPU 资源
5.2 内存资源
5.3 存储
ClickHouse 不支持设置多数据目录，为了提升数据 io 性能，可以挂载虚拟券组，一个券 组绑定多块物理磁盘提升读写性能，多数据查询场景 SSD 会比普通机械硬盘快 2-3 倍。
虚拟券组：将多个物理磁盘虚拟化一个磁盘组，对外提供读和写
二 CK底层语法优化
1 count优化
在调用 count 函数时，如果使用的是 count() 或者 count(*)，且没有 where 条件，则 会直接使用 system.tables 的 total_rows，
EXPLAIN SELECT count()FROM datasets.hits_v1;Union Expression (Projection) Expression (Before ORDER BY and SELECT) MergingAggregated ReadNothing (Optimized trivial count)注意 Optimized trivial count ，这是对 count 的优化。
如果 count 具体的列字段，则不会使用此项优化:
EXPLAIN SELECT count(CounterID) FROM datasets.hits_v1;Union Expression (Projection) Expression (Before ORDER BY and SELECT) Aggregating Expression (Before GROUP BY) ReadFromStorage (Read from MergeTree)2消除子查询重复字段
下面语句子查询中有两个重复的 id 字段，会被去重:
EXPLAIN SYNTAX SELECT a.UserID, b.VisitID, a.URL, b.UserIDFROM hits_v1 AS a LEFT JOIN ( SELECT UserID, UserID as HaHa, VisitID FROM visits_v1) AS b USING (UserID) limit 3;//返回优化语句: SELECT UserID, VisitID, URL, b.UserIDFROM hits_v1 AS aALL LEFT JOIN( SELECT UserID, VisitID FROM visits_v1) AS b USING (UserID)LIMIT 33 谓词下推
当 group by 有 having 子句，但是没有 with cube、with rollup 或者 with totals 修饰的时 候，having 过滤会下推到 where 提前过滤。例如下面的查询，HAVING name 变成了 WHERE name，在 group by 之前过滤:
EXPLAIN SYNTAX SELECT UserID FROM hits_v1 GROUP BY UserID HAVING UserID = '8585742290196126178';//返回优化语句SELECT UserIDFROM hits_v1WHERE UserID = \'8585742290196126178\' GROUP BY UserID子查询也支持谓词下推:
EXPLAIN SYNTAXSELECT *FROM( SELECT UserID FROM visits_v1)WHERE UserID = '8585742290196126178'//返回优化后的语句 SELECT UserID FROM( SELECT UserID FROM visits_v1WHERE UserID = \'8585742290196126178\')WHERE UserID = \'8585742290196126178\'4 聚合计算外推
聚合函数内的计算，会外推，例如:
EXPLAIN SYNTAXSELECT sum(UserID * 2)FROM visits_v1//返回优化后的语句 SELECT sum(UserID) * 2 FROM visits_v15 聚合函数消除
如果对聚合键，也就是 group by key 使用 min、max、any 聚合函数，则将函数消除
EXPLAIN SYNTAXSELECT sum(UserID * 2), max(VisitID), max(UserID)FROM visits_v1GROUP BY UserID//返回优化后的语句SELECT sum(UserID) * 2, max(VisitID), UserIDFROM visits_v1GROUP BY UserID6 删除重复的 order by key
重复的聚合键 id 字段会被去重:
EXPLAIN SYNTAXSELECT *FROM visits_v1ORDER BY UserID ASC, UserID ASC, VisitID ASC, VisitID ASC//返回优化后的语句: select ......FROM visits_v1ORDER BY UserID ASC, VisitID ASC7 删除重复的 limit by key
重复声明的 name 字段会被去重:
EXPLAIN SYNTAXSELECT *FROM visits_v1LIMIT 3 BY VisitID, VisitIDLIMIT 10//返回优化后的语句:select ......FROM visits_v1LIMIT 3 BY VisitIDLIMIT 108 删除重复的 USING Key
重复的关联键 id 字段会被去重:
EXPLAIN SYNTAXSELECT a.UserID, a.UserID, b.VisitID, a.URL, b.UserIDFROM hits_v1 AS aLEFT JOIN visits_v1 AS b USING (UserID, UserID)//返回优化后的语句: SELECT UserID, UserID, VisitID, URL, b.UserIDFROM hits_v1 AS aALL LEFT JOIN visits_v1 AS b USING (UserID)注意：如果USING里是不同表相同字段是不会被删除的比如：a.id b.id
9 标量替换
如果子查询只返回一行数据，在被引用的时候用标量替换，例如下面语句中的total_disk_usage 字段:
EXPLAIN SYNTAXWITH ( SELECT sum(bytes) FROM system.parts WHERE active ) AS total_disk_usageSELECT (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage, tableFROM system.partsGROUP BY tableORDER BY table_disk_usage DESCLIMIT 10;//返回优化后的语句:WITH CAST(0, \'UInt64\') AS total_disk_usage SELECT (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage, tableFROM system.partsGROUP BY tableORDER BY table_disk_usage DESCLIMIT 1010 三元运算优化
如果开启了 optimize_if_chain_to_multiif 参数，三元运算符会被替换成 multiIf 函数，例如:
EXPLAIN SYNTAXSELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10)settings optimize_if_chain_to_multiif = 1;//返回优化后的语句:SELECT multiIf(number = 1, \'hello\', number = 2, \'world\', \'atguigu\') FROM numbers(10)SETTINGS optimize_if_chain_to_multiif = 1;在此次查询语句加上settings optimize_if_chain_to_multiif = 1;只是影响本次查询，并不会影响其他查询
腾讯云开发者
