10.ClickHouse建表优化

Leefs 2022-05-04 PM 1638℃ 0条

[TOC]

### 一、数据类型

#### 1. 时间字段的类型

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

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

```sql
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);
```

#### 2. 空值存储类型

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

```sql
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;
```

查看存储的文件：（没有权限就用 root 用户）

![10.ClickHouse建表优化01.jpg](https://lilinchao.com/usr/uploads/2022/05/2903217878.jpg)

### 二、分区和索引

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

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

**比如官方案例的 hits_v1 表**：

```sql
……
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
……
```

**visits_v1 表：**

```sql
……
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
……
```

### 三、表参数

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

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

### 四、写入和删除优化

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

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

**写入过快报错，报错信息：**

```basic
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 处理** ：使用 WAL 预写日志，提高写入性能。`in_memory_parts_enable_wal` 默认为 true,在服务器内存充裕的情况下增加内存配额，一般通过 max_memory_usage 来实现。

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

### 五、常见配置

配置项主要在 config.xml 或 users.xml 中， 基本上都在 users.xml 里

+ config.xml 的配置项官网地址：https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings/
+ users.xml 的配置项官网地址：https://clickhouse.com/docs/en/operations/settings/settings/

##### CPU 资源

| 配置                                      | 描述                                                         |
| ----------------------------------------- | ------------------------------------------------------------ |
| background_pool_size                      | 后台线程池的大小，merge 线程就是在该线程池中执行，该线程池不仅仅是给 merge 线程用的，默认值 16，允许的前提下建议改成 **cpu 个数的 2 倍（线程数）**。 |
| background_schedule_pool_size             | 执行后台任务（复制表、Kafka 流、DNS 缓存更新）的线程数。默认 128，建议改成 **cpu 个数的 2 倍（线程数）**。 |
| background_distributed_schedule_pool_size | 后设置为分布式发送执行后台任务的线程数，默认 16，建议改成 **cpu个数的 2 倍（线程数）**。 |
| max_concurrent_queries                    | 最大并发处理的请求数(包含 select,insert 等)，默认值 100，推荐 **150(不够再加)~300**。 |
| max_threads                               | 设置单个查询所能使用的最大 cpu 个数，默认是 cpu 核数         |

##### 内存资源

| 配置                               | 描述                                                         |
| ---------------------------------- | ------------------------------------------------------------ |
| max_memory_usage                   | 此参数在 users.xml 中,表示单次 Query 占用内存最大值，该值可以设置的比较大，这样可以提升集群查询的上限。**保留一点给 OS，比如 128G 内存的机器，设置为 100GB**。 |
| max_bytes_before_external_group_by | 一般按照 max_memory_usage 的一半设置内存，当 group 使用内存超过阈值后会刷新到磁盘进行。因为 clickhouse 聚合分两个阶段：查询并及建立中间数据、合并中间数据，**结合上一项，建议 50GB**。 |
| max_bytes_before_external_sort     | 后当 order by 已使用 max_bytes_before_external_sort 内存就进行溢写磁盘(基于磁盘排序)，如果不设置该值，那么当内存不够时直接抛错，设置了该值 order by 可以正常完成，但是速度相对存内存来说肯定要慢点(实测慢的非常多，无法接受)。 |
| max_table_size_to_drop             | 此参数在 config.xml 中，应用于需要删除表或分区的情况，默认是50GB，意思是如果删除 50GB 以上的分区表会失败。**建议修改为 0**，这样不管多大的分区表都可以删除。 |

##### 存储

ClickHouse 不支持设置多数据目录，为了提升数据 io 性能，可以挂载虚拟券组，一个券组绑定多块物理磁盘提升读写性能，多数据查询场景 SSD 会比普通机械硬盘快 2-3 倍。

*附参考文章来源：*

*《尚硅谷大数据技术之ClickHouse》*

标签: ClickHouse

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：https://lilinchao.com/archives/2060.html

上一篇 09.ClickHouse查看执行计划

下一篇 11.ClickHouse之数据一致性

10.ClickHouse建表优化

评论啦~

栏目分类

标签云

友情链接申请

10.ClickHouse建表优化

 评论啦~

 栏目分类

标签云

友情链接申请

评论啦~

栏目分类

标签云

友情链接申请