ClickHouse学习-建表和索引的优化点(一)
ClickHouse 优化点
clickhouse 相对于mysql,除了在mysql在SQL和索引的优化空间比较大外,而其他的clickhouse的优化空间还是很大的,对于clickhouse他的服务端配置参数对于任务的影响还是很大的。现在我们来看看clickhouse都有哪些常规的优化点,今天主要学习一下创建表的时候需要注意的点
建表优化
1. 数据类型
1.1 null值尽量避免
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中正好是相反的他需要创建索引的时候基数相对较大的
- 多列索引创建业务场景,查询频率也是考量之一
相关文章
- .NET Core微服务系列基础文章索引(目录导航Draft版)
- 《T-SQL查询》读书笔记Part 3.索引的基本知识
- 设计模式的征途(C#实现)—文章目录索引
- 《智能时代》读书笔记:这是最好的时代,也是最坏的时代
- 《图解TCP/IP》读书笔记
- 借助 Lucene.Net 构建站内搜索引擎(下)
- 借助 Lucene.Net 构建站内搜索引擎(上)
- 操作系统核心原理-6.外存管理(上)磁盘基础
- pytorch中retain_graph==True的作用是什么
- MySQL六种约束是什么
- Pytorch中retain_graph的坑如何解决
- 用匹配的国家名称绑定数据帧
- 平均来自某些列的熊猫数据框
- 挖掘行并将它们添加到R中的空白数据框中
- 你必须知道的指针基础-2.指针的声明和使用及数组和指针的关系
- MySQL:在一个表上存在多个连接时计入NULL或0的值
- Hadoop学习笔记—15.HBase框架学习(基础知识篇)
- NoSQL初探之人人都爱Redis:(2)Redis API与常用数据类型简介
- NoSQL初探之人人都爱Redis:(1)Redis简介与简单安装
- 《大型网站技术架构》读书笔记三:大型网站核心架构要素