ClickHouse学习-建表和索引的优化点（一）

ClickHouse 优化点

clickhouse 相对于mysql，除了在mysql在SQL和索引的优化空间比较大外，而其他的clickhouse的优化空间还是很大的，对于clickhouse他的服务端配置参数对于任务的影响还是很大的。现在我们来看看clickhouse都有哪些常规的优化点，今天主要学习一下创建表的时候需要注意的点

建表优化

1. 数据类型

1.1 null值尽量避免

• 关于NULL存储的问题，尽量不要制定为nullable 这样会影响列的查询性能，因为如果有null值对于clickhouse来讲会存储在一个新的文件中，所以这在查询和筛选过程中会多差一个文件，包括影响了索引的性能，如果了解mysql的同学在mysql中设置为null的也会影响索引性能。

  官方文档： clickhouse底层是如何存储的？

  允许用特殊标记 (NULL) 表示«缺失值»，可以与 TypeName 的正常值存放一起。例如，Nullable(Int8) 类型的列可以存储 Int8 类型值，而没有值的行将存储 NULL。

  对于 TypeName，不能使用复合数据类型 阵列 和 元组。复合数据类型可以包含 Nullable 类型值，例如Array(Nullable(Int8))。

  Nullable 类型字段不能包含在表索引中。

  除非在 ClickHouse 服务器配置中另有说明，否则 NULL 是任何 Nullable 类型的默认值。

    *要在表的列中存储 `Nullable` 类型值，ClickHouse 除了使用带有值的普通文件外，还使用带有 `NULL` 掩码的单独文件。 掩码文件中的条目允许 ClickHouse 区分每个表行的 `NULL` 和相应数据类型的默认值。 由于附加了新文件，`Nullable` 列与类似的普通文件相比消耗额外的存储空间。*
  

  注意点

  使用 Nullable 几乎总是对性能产生负面影响，在设计数据库时请记住这一点

  掩码文件中的条目允许ClickHouse区分每个表行的对应数据类型的«NULL»和默认值由于有额外的文件，«Nullable»列比普通列消耗更多的存储空间

1.2 日期都存储为日期类型

• 时间戳类型。用四个字节（无符号的）存储 Unix 时间戳）。允许存储与日期类型相同的范围内的值。最小值为 1970-01-01 00:00:00。时间戳类型值精确到秒（没有闰秒）。

2. 分区和索引优化

2.1 分区优化

• 分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为Tuple()，以单表一亿数据为例，分区大小控制在10-30个为最佳。
• 那些有相同分区表达式值的数据片段才会合并。这意味着 你不应该用太精细的分区方案（超过一千个分区）。否则，会因为文件系统中的文件数量过多和需要打开的文件描述符过多，导致 SELECT 查询效率不佳。
• 还有就是一般我们都是使用的是日期作为分区键，同一分区内有序，不同分区不能保证有序。

2.2 索引优化

我们先搞清楚，clickhouse的索引是如何存储的，当数据被插入到表中时，会创建多个数据片段并按主键的字典序排序。例如，主键是 (CounterID, Date) 时，片段中数据首先按 CounterID 排序，具有相同 CounterID 的部分按 Date 排序。下图也就是他的排序规则（稀疏索引）

不同分区的数据会被分成不同的片段，ClickHouse 在后台合并数据片段以便更高效存储。不同分区的数据片段不会进行合并。合并机制并不保证具有相同主键的行全都合并到同一个数据片段中。

数据片段可以以 Wide 或 Compact 格式存储。在 Wide 格式下，每一列都会在文件系统中存储为单独的文件，在 Compact 格式下所有列都存储在一个文件中。Compact 格式可以提高插入量少插入频率频繁时的性能。

数据存储格式由 min_bytes_for_wide_part 和 min_rows_for_wide_part 表引擎参数控制。如果数据片段中的字节数或行数少于相应的设置值，数据片段会以 Compact 格式存储，否则会以 Wide 格式存储。

每个数据片段被逻辑的分割成颗粒（granules）。颗粒是 ClickHouse 中进行数据查询时的最小不可分割数据集。ClickHouse 不会对行或值进行拆分，所以每个颗粒总是包含整数个行。每个颗粒的第一行通过该行的主键值进行标记，ClickHouse 会为每个数据片段创建一个索引文件来存储这些标记。对于每列，无论它是否包含在主键当中，ClickHouse 都会存储类似标记。这些标记让您可以在列文件中直接找到数据。

颗粒的大小通过表引擎参数 index_granularity 和 index_granularity_bytes 控制。颗粒的行数的在 [1, index_granularity] 范围中，这取决于行的大小。如果单行的大小超过了 index_granularity_bytes 设置的值，那么一个颗粒的大小会超过 index_granularity_bytes。在这种情况下，颗粒的大小等于该行的大小。

我们已经知道索引是如何存储的了，那我们就可以试着优化一下

• 从上面的结构我们可以看出他是一个稀疏索引，从图中我们可以清楚的看见他的创建规则，必须指定索引列，ClickHouse中的索引列即排序列，通过order by指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来
• 可以是单一维度，也可以是组合维度的索引，通常需要满足高级列在前、查询频率大的在前原则；
• 基数特别大的不适合做索引列（可以对比上图索引创建规则），如用户表的userid字段；
• 通常筛选后的数据满足在百万以内为最佳。
• 还有就是索引粒度的设定。

总结

• 建表优化，创建字段的时候尽量不要使用nullable
• 日期尽量都使用date类型
• 索引存储的规则
• 创建索引尽量选择基数大的，也就是重复相对较多的（因为是稀疏索引）在mysql中正好是相反的他需要创建索引的时候基数相对较大的
• 多列索引创建业务场景，查询频率也是考量之一