您好!
欢迎来到京东云开发者社区
登录
首页
博文
课程
大赛
工具
用户中心
首页
博文
课程
大赛
工具
更多
用户中心
开发者社区
>
博文
>
ClickHouse技术研究及语法简介
分享
打开微信扫码分享
点击前往QQ分享
点击前往微博分享
点击复制链接
ClickHouse技术研究及语法简介
自猿其说Tech
2021-09-23
IP归属:未知
45600浏览
ClickHouse
本文对Clickhouse架构原理、语法、性能特点做一定研究,同时将其与mysql、elasticsearch、tidb做横向对比,并重点分析与mysql的语法差异,为有mysql迁移clickhouse场景需求的技术预研及参考。 ### 1 基础概念 Clickhouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。 #### 1.1 集群架构 ClickHouse 采用典型的分组式的分布式架构,具体集群架构如下图所示: ![](//img1.jcloudcs.com/developer.jdcloud.com/cf97d794-ae83-4eb3-96ea-6d4c3de232ee20210923144406.png) - Shard:集群内划分为多个分片或分组(Shard 0 … Shard N),通过 Shard 的线性扩展能力,支持海量数据的分布式存储计算。 - Node: 每个 Shard 内包含一定数量的节点(Node,即进程),同一 Shard 内的节点互为副本,保障数据可靠。ClickHouse 中副本数可按需建设,且逻辑上不同 Shard 内的副本数可不同。 - ZooKeeper Service: 集群所有节点对等,节点间通过 ZooKeeper 服务进行分布式协调。 #### 1.2 数据分区 Clickhouse是分布式系统,其数据表的创建,与mysql是有差异的,可以类比的是在mysql上实现分库分表的方式。 Clichhouse先在每个 Shard 每个节点上创建本地表(即 Shard 的副本),本地表只在对应节点内可见;然后再创建分布式表[Distributed],映射到前面创建的本地表。 用户在访问分布式表时,ClickHouse 会自动根据集群架构信息,把请求转发给对应的本地表。 ![](//img1.jcloudcs.com/developer.jdcloud.com/01febbbc-c46a-46d1-bd4c-8e75ed0dc9d620210923144440.png) #### 1.3 列式存储 相对于关系型数据库(RDBMS),是按行存储的。以mysql中innodb的主键索引为例,构建主键索引的B+树中,每个叶子节点存储的就是一行记录。 而列式数据库,是将一个表,按column的维护进行存储,“单次磁盘I/O拿到的是一列的数据”。 ![](//img1.jcloudcs.com/developer.jdcloud.com/ef229eca-870b-446a-9f9e-e4ca5a3cacfb20210923144533.png) **列式存储的优点** 在查询时,只会读取涉及到的列,会大大减少IO次数/开销。并且clickhouse在存储时会按指定顺序排列数据,因此只需要按where条件指定列进行顺序扫描、多个列的扫描结果合并,即可找到满足条件的数据。 但由于insert数据时,是按行写入的,因此存储的过程会麻烦一些。 **查询时的区别:** - 列存储:仅从存储系统中读取必要的列数据(select + where 涉及到的),无用列不读取,速度非常快。 - 行存储:从存储系统读取所有满足条件的行数据,然后在内存中过滤出需要的字段,速度较慢。 #### 1.4 数据排序 每个数据分区内部,所有列的数据是按照 排序键(ORDER BY 列)进行排序的。 可以理解为:对于生成这个分区的原始记录行,先按 排序键 进行排序,然后再按列拆分存储。 #### 1.5 数据分块 每个列的数据文件中,实际是分块存储的,方便数据压缩及查询裁剪,每个块中的记录数不超过 index_granularity,默认 8192,当达到index_granularity的值,数据会分文件。 #### 1.6 向量化执行 在支持列存的基础上,ClickHouse 实现了一套面向向量化处理的计算引擎,大量的处理操作都是向量化执行的。 向量化处理的计算引擎: 基于数据存储模型,叠加批量处理模式,利用SIMD指令集,降低函数调用次数,降低硬件开销(比如各级硬件缓存),提升多核CPU利用率。 再加上分布式架构,多机器、多节点、多线程、批量操作数据的指令,最大限度利用硬件资源,提高效率。 注:SIMD指令,单指令多数据流,也就是说在同一个指令周期可以同时处理多个数据。(例如:在一个指令周期内就可以完成多个数据单元的比较). #### 1.7 编码压缩 由于 ClickHouse 采用列存储,相同列的数据连续存储,且底层数据在存储时是经过排序的,这样数据的局部规律性非常强,有利于获得更高的数据压缩比。 同时,超高的压缩比又可以降低存储读取开销、提升系统缓存能力,从而提高查询性能。 #### 1.8 索引 前面提到的列式存储,用于裁剪不必要的字段读取; 而索引,则用于裁剪不必要的记录读取(减少未命中数据的IO)。 简单解释: 以主键索引为例,Clickhouse存储数据时,会按排序键(ORDER BY)指定的列进行排序,并按Index_granularity参数切分成块,然后会抽取每个数据块的首行,组织为一份稀疏的排序索引。 类比B+树的查找过程,如果where条件中包含主键列,就可以通过稀疏索引快速的过滤。稀疏索引对于范围查找比较高效。 **二级索引,则是采用bloom filter来实现的:minmax,set,ngrambf/tokenbf。** #### 1.9 适用场景 OLAP 分析领域有两个典型的方向: - ROLAP, 通过列存、索引等各类技术手段,提升查询时性能。 **宽表、大表场景,where条件过多且动态,mysql无法每列都建索引。** - MOLAP, 通过预计算提前生成聚合后的结果数据,降低查询读取的数据量,属于计算换性能方式。 **复杂的报表查询,聚合、筛选很复杂的场景。** 既然是OLAP分析,对数据的使用有些基本要求: - 绝大多数都是用于读访问 - 无更新、大批量的更新(大于1000行)。(ck没有高速、低延迟的更新和删除方法) - 查询的列尽量少,但行数很多。 - 不需要事务、可以避免事务(clickhouse不支持事务) - 数据一致性要求较低 - 多表join时,只有一个是大表、大表关联小表 - 单表的查询、聚合效率最高,建议数据做宽表处理 ### 2 横向对比 搬仓系统面临的是从十几亿数据中进行查询、聚合分析,从世面上可选的支持海量数据读写的中间件中搜集到,能够有支持类似场景、有比较轻量级的产品大概有Clickhouse、ElasticSearch、TiDB。 #### 2.1 clickhouse与ElasticSearch对比 elastic生态很丰富,es作为其中的存储产品,从首个版本算起,已经有10年发展历史,主要解决的是搜索问题。es的底层存储采用lucene,主要包含行存储、列存储和倒排索引,利用分片与副本机制,解决了集群下搜索性能与高可用的问题。 es的优势: - 支持实时更新,对update、delete操作支持更完整。 - 数据分片更均匀,集群扩展更加方便 es的局限性: - 数据量超过千万或者亿级时,若聚合的列数太多,性能也到达瓶颈; - 不支持深度二次聚合,导致一些复杂的聚合需求,需要人工编写代码在外部实现,这又增加很多开发工作量。 ClickHouse 与 Elasticsearch(排序与聚合查询) 一样,都采用列式存储结构,都支持副本分片,不同的是 ClickHouse 底层有一些独特的实现,如下: - 合并树表引擎系列(MergeTree ),提供了数据分区、一级索引、二级索引。 - 向量引擎(Vector Engine),数据不仅仅按列存储,同时还按向量(列的一部分)进行处理,这样可以更加高效地使用 CPU 网上资料:<a href="https://www.jianshu.com/p/0defb5471bc6" target="_blank">聚合查询的性能对比</a> es对于在处理大查询,可能导致OOM问题,集群虽然能够对异常节点有自动恢复机制,但其查询数据量级不满足搬仓系统需求。 #### 2.2 clickhouse与TiDB对比 TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和 MySQL 协议,具有数据强一致的高可用特性,是一个不仅适合OLTP场景还适OLAP场景的混合数据库。 **TiDB的优势:** - 兼容Mysql协议和绝大多数Mysql语法,在大多数情况下,用户无需修改一行代码就可以从Mysql无缝迁移到TiDB - 高可用、强制一致性(Raft) - 支持ACID事务(依赖事务列表),支持二级索引 适合快速的点插入,点更新和点删除 **TiDB的局限性:** - 更擅长OLTP - 性能依赖硬件和集群规模,单机的读写性能不够出色 TiDB更加适合作为MySql的替代,其对MySQL的兼容可以使得我们的应用切换成本较低,并且TiDB提供的数据自动分片无需人工维护。 ### 3 为什么是clickhouse 我们的项目场景是每天要同步十几亿单表数据,基本业务的查询在百万,还包含复杂的聚合分析。而Clickhouse在处理单表海量数据的查询分析方面,是十分优秀的,因此选用clickhouse。 #### 3.1 clickhouse读写性能验证 官方公开benchmark测试显示能够达到50MB-200MB/s的写入吞吐能力,按照每行100Byte估算,大约相当于50W-200W条/s的写入速度。 下面是对Clickhouse的读写性能的简单测试,数据量越大差距越明显。 **1)JDBC方式单表、单次写入性能测试(性能更好):** ![](//img1.jcloudcs.com/developer.jdcloud.com/0d92797a-fdcc-48e8-86da-0557bb100d1120210923145050.png) **2)Mybatis方式单表、单次写入性能测试:** ![](//img1.jcloudcs.com/developer.jdcloud.com/2e1727d5-658e-4b83-b49b-d157109234db20210923145112.png) 聚合查询性能举例:下图是搬仓系统一个聚合查询,在clickhouse中不同数据量级情况下的表现。这个查询在mysql中执行,一百万左右的数据量时,耗时已经是分钟级别。 1)count+distinct方式聚合: ![](//img1.jcloudcs.com/developer.jdcloud.com/b7b0374d-51c9-4fb9-b3d6-3cdb553e139e20210923145159.png) 2)group by方式聚合: ![](//img1.jcloudcs.com/developer.jdcloud.com/b6744d74-66cb-4eb1-b222-13d6b7b63e1c20210923145246.png) #### 3.2 不足之处 作为分布式系统,通常包含三个重要组成:1、存储引擎。 2、计算引擎。 3、分布式管控层。 在分布式管控层,CK显得较为薄弱,导致运营、使用成本较高。 - 分布式表、本地表、副本的维护,这些都是需要用户自己来定义的,在使用时需要提前学习大量相关内容。 - 弹性伸缩:ck虽然可以做到水平增加节点,但不支持自动的数据均衡。也就是说当集群扩容后,需要手动将数据重写分片,或者依赖数据过期,才能保持存储压力的均衡。 - 故障恢复:在节点故障的情况下,ck不能利用其他机器补齐缺失的副本数据,需要用户ian补齐节点后,才能自动在副本件进行数据同步。 **这方面,由于我们直接采用京东云实例,可以省很多事情。** 计算引擎,CK在处理多表关联查询、复杂嵌套子查询等场景,需要人工优化,才能做到明显的性能提升; 实时写入,CK使用场景并不适合比较分散的插入,因为其没有实现内存表(Memory Table)结构,每批次写入直接落盘,单条记录实时写入会导致底层大量的小文件,影响查询性能。 **建议单次大批量写入方式、报表库场景降低小文件产生概率。** 集群模式下本地表的写入,需要自定义分片规则,否则随机写入会造成数据不均匀。 依赖分布式表的写入,对网络、资源的占用较高。 从数据量增长情况来看,使用场景: - 如果预估自己的业务数据量不大(日增不到百万行), 那么写分布式表和本地表都可以, 但要注意如果选择写本地表, 请保证每次写入数据都建立新的连接, 且每个连接写入的数据量基本相同,手动保持数据均匀 - 如果预估自己的业务数据量大(日增百万以上, 并发插入大于10), 那么请写本地表 - 建议每次插入50W行左右数据, 最多不可超过100W行. 总之CH不像MySQL要小事务. 比如1000W行数据, MySQL建议一次插入1W左右, 使用小事务, 执行1000次. CH建议20次,每次50W. 这是MergeTree引擎原理决定的, 频繁少量插入会导致data part过多, 合并不过来. - MergeTree系列:被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入,数据片段在后台按照一定的规则进行合并。相比在插入时不断修改(重写)已存储的数据,这种策略会高效很多。 - Log系列:功能相对简单,主要用于快速写入小表(1百万行左右的表),然后全部读出的场景。 - Integration系列:主要用于将外部数据导入到ClickHouse中,或者在ClickHouse中直接操作外部数据源。 - Special系列:大多是为了特定场景而定制的。上面提到的Distributed就属于该系列。 #### 4.1 MergeTree表引擎 主要用于海量数据分析,支持数据分区、存储有序、主键索引、稀疏索引、数据TTL等。MergeTree支持所有ClickHouse SQL语法,但是有些功能与MySQL并不一致,比如在MergeTree中主键并不用于去重。 先看一个创建表的简单语法: ```sql CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( ... ) ENGINE = ReplacingMergeTree([ver]) [PARTITION BY expr] -- 数据分区规则 [ORDER BY expr] -- 排序键 [SAMPLE BY expr] -- 采样键 [SETTINGS index_granularity = 8192, ...] -- 额外参数 ``` 先忽略表结构的定义,先看看相比于mysql建表的差异项。(指定集群、分区规则、排序键、采样0-1数字) 数据分区:每个分片副本的内部,数据按照 PARTITION BY 列进行分区,分区以目录的方式管理,本文样例中表按照时间进行分区。 ![](//img1.jcloudcs.com/developer.jdcloud.com/4b05dd96-28ed-4fbe-9664-ec68318ba91e20210923150403.png) 基于MergeTree表引擎,CK扩展很多解决特殊场景的表引擎,下面介绍几种常用的。 ##### 4.1.1 ReplacingMergeTree引擎 该引擎和 MergeTree 的不同之处在于它会删除排序键值(ORDER BY)相同的重复项。 官方建表语句: ```sql CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ENGINE = ReplacingMergeTree([ver]) [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [SETTINGS name=value, ...] ``` 注意:在设置表引擎时,比MergeTree多了一个参数:ver-版本列,ENGINE = ReplacingMergeTree([ver]) 。 在数据合并的时候,ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下: - 如果 ver 列未指定,保留最后一条。 - 如果 ver 列已指定,保留 ver 值最大的版本。 ReplacingMergeTree引擎,在数据写入后,不一定立即进行去重操作,或者不一定去重完毕(官方描述在10到15分钟内会进行合并)。 由于去重依赖的是排序键,ReplacingMergeTree引擎是会按照分区键进行分区的,**因此相同排序键的数据有可能被分到不同的分区,不同shard间可能无法去重。** ![](//img1.jcloudcs.com/developer.jdcloud.com/35c9d0fe-b97b-4d6e-a3a9-5170b55c3c3020210923150503.png) 在图上,分区1的文件块,会进行数据合并去重,但是分区1与分区2之间的数据是不会进行去重的。因此,如果要保证数据最终能够去重,要保证相同排序键的数据,会写入相同分区。 数据验证 **下图为ReplacingMergeTree引擎,以日期作为分区键,对于重复主键数据的去重测试:** ![](//img1.jcloudcs.com/developer.jdcloud.com/8cd9da58-d8fb-4a95-93c0-58877542a62320210923150525.png) ##### 4.1.2 CollapsingMergeTree引擎 该引擎要求在建表语句中指定一个标记列Sign,按照Sign的值将行分为两类:Sign=1的行称之为状态行,Sign=-1的行称之为取消行。每次需要新增状态时,写入一行状态行;需要删除状态时,则写入一行取消行。 使用场景: 1. 按clickhouse的架构,期合并、折叠操作,都是后台独立现场执行的,因此时间上并不能控制,何时折叠完成也无法预知。 1. 如果插入的状态行与取消行是乱序的,会导致无法正常折叠 ##### 4.1.3 VersionedCollapsingMergeTree表引擎 为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题,VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version,用于在乱序情况下记录状态行与取消行的对应关系。 主键相同,且Version相同、Sign相反的行,在Compaction时会被删除。 #### 4.2 数据副本 数据副本放在表引擎这里单独讲一下,是由于只有 MergeTree 系列里的表可支持副本: - ReplicatedMergeTree - ReplicatedSummingMergeTree - ReplicatedReplacingMergeTree - ReplicatedAggregatingMergeTree - ReplicatedCollapsingMergeTree - ReplicatedVersionedCollapsingMergetree - ReplicatedGraphiteMergeTree 副本是表级别的,不是整个服务器级的。所以,服务器里可以同时有复制表和非复制表。 副本不依赖分片。每个分片有它自己的独立副本。 要使用副本,必须配置文件中设置 ZooKeeper 集群的地址。**(京东云提供的clickhouse已经完成了配置,我们直接使用即可)** ```xml <zookeeper> <node index="1"> <host>example1</host> <port>2181</port> </node> <node index="2"> <host>example2</host> <port>2181</port> </node> <node index="3"> <host>example3</host> <port>2181</port> </node> </zookeeper> ``` 创建数据副本,是通过设置表引擎位置的参数来控制的,语法示例: ```sql CREATE TABLE table_name ( EventDate DateTime, CounterID UInt32, UserID UInt32 )ENGINE=ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/table_name', '{replica}') -- 这里 PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) ``` 定义数据副本,只需要在以上表引擎名字的前面,带上Replicated即可。 上方例子中,使用的表引擎为MergeTree,开启数据副本,关键字Replicated,参数有2个且必填: - zoo_path — ZooKeeper 中该表的路径。 - replica_name — ZooKeeper 中的该表的副本名称 示例中的取值,采用了变量{layer}、{shard}、{replica},他们的值取得是配置文件中的值,影响的是生成的副本粒度。 ```xml <macros> <layer>05</layer> <shard>02</shard> <replica>example05-02-1.yandex.ru</replica> </macros> ``` #### 4.3 Special系列 Special系列的表引擎,大多是为了特定场景而定制的。 - Memory:将数据存储在内存中,重启后会导致数据丢失。查询性能极好,适合于对于数据持久性没有要求的1亿一下的小表。在ClickHouse中,通常用来做临时表; - Buffer:为目标表设置一个内存buffer,当buffer达到了一定条件之后会flush到磁盘; - File:直接将本地文件作为数据存储; - Null:写入数据被丢弃、读取数据为空。 - Distributed:分布式引擎,可以在多个服务器上进行分布式查询 ##### 4.3.1 Distributed引擎 分布式表引擎,本身不存储数据,也不占用存储空间,在定义时需要指定字段,但必须与要映射的表的结构相同。可用于统一查询*MergeTree的每个分片,类比sharding中的逻辑表。 比如搬仓系统,使用ReplicatedReplacingMergeTree与Distributed结合,实现通过分布式表实现对本地表的读写(写入操作本地表,读取操作分布式表)。 ```sql CREATE TABLE IF NOT EXISTS {distributed_table} as {local_table} ENGINE = Distributed({cluster}, '{local_database}', '{local_table}', rand()) ``` 说明: - distributed_table:分布式表的表名 - local_table:本地表名 - as local_table:保持分布式表与本地表的表结构一致。此处也可以用 (column dataType)这种定义表结构方式代替 - cluster:集群名 注意事项: - 分布式表本身并不存储数据,只是提供了一个可以分布式访问数据的框架,查询分布式表的时候clickhouse会自动去查询对应的每个本地表中的数据,聚合后再返回 - 注意AS {local_table},它表明了分布式表所对应的本地表(本地表是存储数据的) - 可以配置Distributed表引擎中的最后一个参数 rand()来设置数据条目的分配方式 - 可以直接往分布式表中写数据,clickhouse会自动按照上一点所说的方式来分配数据和自平衡,数据实际会写到本地表 - 也可以自己写分片算法,然后往本地表中写数据【网上资料的场景是每天上千亿写入,性能考虑要直接写本地表】 #### 4.4 Log系列 Log系列表引擎功能相对简单,主要用于快速写入小表(1百万行左右的表),然后全部读出的场景。 几种Log表引擎的共性是: - 数据被顺序append写到磁盘上; - 不支持delete、update; - 不支持index; - 不支持原子性写; - insert会阻塞select操作。 它们彼此之间的区别是: - TinyLog:不支持并发读取数据文件,查询性能较差;格式简单,适合用来暂存中间数据; - StripLog:支持并发读取数据文件,查询性能比TinyLog好;将所有列存储在同一个大文件中,减少了文件个数; - Log:支持并发读取数据文件,查询性能比TinyLog好;每个列会单独存储在一个独立文件中。 #### 4.5 Integration系列 该系统表引擎主要用于将外部数据导入到ClickHouse中,或者在ClickHouse中直接操作外部数据源。 - Kafka:将Kafka Topic中的数据直接导入到ClickHouse; - MySQL:将Mysql作为存储引擎,直接在ClickHouse中对MySQL表进行select等操作;猜测:如果有join需求,又不想将mysql数据导入ck中 - JDBC/ODBC:通过指定jdbc、odbc连接串读取数据源; - HDFS:直接读取HDFS上的特定格式的数据文件。 ### 5 数据类型 clickhouse支持的数据类型如下图,分为基础类型、复合类型、特殊类型。 ![](//img1.jcloudcs.com/developer.jdcloud.com/c2d2f2c5-fb7d-4991-90f6-da8c775302be20210923151805.png) #### 5.1 CK与Mysql数据类型对照 ![](//img1.jcloudcs.com/developer.jdcloud.com/828b8fde-890d-4e7d-b0e1-5544337b66bf20210923152000.png) ![](//img1.jcloudcs.com/developer.jdcloud.com/1e31e946-6bb2-4cfe-bd7d-69ae07e3caad20210923152020.png)![](//img1.jcloudcs.com/developer.jdcloud.com/22518270-b413-4b85-b562-9680f8a017f120210923152032.png)![](//img1.jcloudcs.com/developer.jdcloud.com/b0dc00f3-86bb-46cf-91f3-83cd33dbb13520210923152046.png) ### 6 SQL语法-常用介绍 #### 6.1 DDL ##### 6.1.1 创建数据库: ```sql CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]; ``` 如果CREATE 语句中存在IF NOT EXISTS 关键字,则当数据库已经存在时,该语句不会创建数据库,且不会返回任何错误。 ON CLUSTER 关键字用于指定集群名称,在集群环境下必须指定该参数,否则只会在链接的节点上创建。 ##### 6.1.2 创建本地表: ```sql CREATE TABLE [IF NOT EXISTS] [db.]table_name ON CLUSTER cluster ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1, INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2 ) ENGINE = engine_name() [PARTITION BY expr] [ORDER BY expr] [PRIMARY KEY expr] [SAMPLE BY expr] [SETTINGS name=value, ...]; ``` 选项描述: - db:指定数据库名称,如果当前语句没有包含‘db’,则默认使用当前选择的数据库为‘db’。 - cluster:指定集群名称,目前固定为default。ON CLUSTER 将在每一个节点上都创建一个本地表。 - type:该列数据类型,例如 UInt32。 - DEFAULT:该列缺省值。如果INSERT中不包含指定的列,那么将通过表达式计算它的默认值并填充它(与mysql一致)。 - MATERIALIZED:物化列表达式,表示该列不能被INSERT,是被计算出来的; 在INSERT语句中,不需要写入该列;在SELECT * 查询语句结果集不包含该列;需要指定列表来查询(虚拟列) - ALIAS :别名列。这样的列不会存储在表中。 它的值不能够通过INSERT写入,同时SELECT查询使用星号时,这些列也不会被用来替换星号。 但是它们可以用于SELECT中,在这种情况下,在查询分析中别名将被替换。 - 物化列与别名列的区别: 物化列是会保存数据,查询的时候不需要计算,而别名列不会保存数据,查询的时候需要计算,查询时候返回表达式的计算结果 以下选项与表引擎相关,只有MergeTree系列表引擎支持: - PARTITION BY:指定分区键。通常按照日期分区,也可以用其他字段或字段表达式。(定义分区键一定要考虑清楚,它影响数据分布及查询性能) - ORDER BY:指定 排序键。可以是一组列的元组或任意的表达式。 - PRIMARY KEY: 指定主键,默认情况下主键跟排序键相同。因此,大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。 - SAMPLE BY :抽样表达式,如果要用抽样表达式,主键中必须包含这个表达式。 - SETTINGS:影响 性能的额外参数。 - GRANULARITY :索引粒度参数。 示例,创建一个本地表: ```sql CREATE TABLE ontime_local ON CLUSTER default -- 表名为 ontime_local ( Year UInt16, Quarter UInt8, Month UInt8, DayofMonth UInt8, DayOfWeek UInt8, FlightDate Date, FlightNum String, Div5WheelsOff String, Div5TailNum String )ENGINE = ReplicatedMergeTree(--表引擎用ReplicatedMergeTree,开启数据副本的合并树表引擎) '/clickhouse/tables/ontime_local/{shard}', -- 指定存储路径 '{replica}') PARTITION BY toYYYYMM(FlightDate) -- 指定分区键,按FlightDate日期转年+月维度,每月做一个分区 PRIMARY KEY (intHash32(FlightDate)) -- 指定主键,FlightDate日期转hash值 ORDER BY (intHash32(FlightDate),FlightNum) -- 指定排序键,包含两列:FlightDate日期转hash值、FlightNunm字符串。 SAMPLE BY intHash32(FlightDate) -- 抽样表达式,采用FlightDate日期转hash值 SETTINGS index_granularity= 8192 ; -- 指定index_granularity指数,每个分区再次划分的数量 ``` ##### 6.1.3 创建分布式表 基于本地表创建一个分布式表。基本语法: ```sql CREATE TABLE [db.]table_name ON CLUSTER default AS db.local_table_name ENGINE = Distributed(<cluster>, <database>, <shard table> [, sharding_key]) ``` 参数说明: - db:数据库名。 - local_table_name:对应的已经创建的本地表表名。 - shard table:同上,对应的已经创建的本地表表名。 - sharding_key:分片表达式。可以是一个字段,例如user_id(integer类型),通过对余数值进行取余分片;也可以是一个表达式,例如rand(),通过rand()函数返回值/shards总权重分片;为了分片更均匀,可以加上hash函数,如intHash64(user_id)。 示例,创建一个分布式表: ```sql CREATE TABLE ontime_distributed ON CLUSTER default -- 指定分布式表的表名,所在集群 AS db_name.ontime_local -- 指定对应的 本地表的表名 ENGINE = Distributed(default, db_name, ontime_local, rand()); -- 指定表引擎为Distributed(固定) ``` ##### 6.1.4 其他建表 clickhouse还支持创建其他类型的表: ![](//img1.jcloudcs.com/developer.jdcloud.com/9c2b8de3-d581-4f46-bc89-2148fe6bf5dd20210923152625.png) ##### 6.1.5 修改表 语法与mysql基本一致: ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ... 支持下列动作: - ADD COLUMN — 添加列 - DROP COLUMN — 删除列 - CLEAR COLUMN — 重置列的值 - COMMENT COLUMN — 给列增加注释说明 - MODIFY COLUMN — 改变列的值类型,默认表达式以及TTL 举例:ALTER TABLE bd01.table_1 ADD COLUMN browser String AFTER name; – 在name列后面追加一列 #### 6.2 DML ![](//img1.jcloudcs.com/developer.jdcloud.com/0bca9219-80ee-4258-8931-dde56f60390220210923152725.png) 注意: 1. 索引列不支持更新、删除 1. 分布式表不支持更新、删除 ### 杂7 复杂查询JOIN 所有标准 SQL JOIN 支持类型(INNER和OUTER可以省略): - INNER JOIN,只返回匹配的行。 - LEFT OUTER JOIN,除了匹配的行之外,还返回左表中的非匹配行。 - RIGHT OUTER JOIN,除了匹配的行之外,还返回右表中的非匹配行。 - FULL OUTER JOIN,除了匹配的行之外,还会返回两个表中的非匹配行。 - CROSS JOIN,产生整个表的笛卡尔积, “join keys” 是 不 指定。 查询优化: 1. A join B 的查询,比from A,B,C 多表的性能高很多 1. global join 会把书记发送给所有节点参与计算,针对较小的维度表性能较好 1. JOIN会在背地节点操作,适合于相同分片字段的两张表关联(A表与B表的分片字段都包含字段M) 1. IN的性能比JOIN好,优先使用JOIN 1. 先过滤再join效率更好(减低每个分片关联数据量级) 1. 在做多表join时,A表的查询过滤条件中如果能包含与B表的ON expr中字段过滤条件,性能更好 1. join的顺序,大表在左,小表在右;ck查询时会从右向左执行 **对比JOIN与IN的查询复杂度:** CK常用的表引擎会是分布式存储,因此查询过程一定是每个分片进行一次查询,这就导致了sql的复杂度越高,查询锁扫描的分片数量越多,耗时也就越久。 假设AB两个表,分别存储在10个分片中,join则是查询10次A表的同时,join10次B表,合计要10*10次。采用Global join则会先查询10次并生成临时表,再用临时表取和B表join,合计要10+10次。 **这算是分布式架构的查询特点,如果能干预数据分片规则,如果查询条件中带有分片列,则可以直接定位到包含数据的分片上,从而减小查询次数。** CK对于join语法上虽然支持,但是性能并不高。当join的左边是子查询结果时,ck是无法进行分布式join的。 ### 8 MySQL迁移到CK - 数据同步成本:clickhouse可以做到与mysql的表结构一致,进而数据同步成本较低,不需要调整数据结构、不需要额外做宽表处理(当然转为宽表效率更高)。 - SQL迁移成本:支持jdbc、mybatis接入;支持标准SQL的语法;支持join、in、函数,SQL迁移成本较低。 当然如果花功夫对表结构、SQL、索引等进行优化,能得到更好的查询效率。 **官方支持** 在2020年下半年,Yandex 公司在 ClickHouse 社区发布了MaterializeMySQL引擎,支持从MySQL全量及增量实时数据同步。MaterializeMySQL引擎目前支持 MySQL 5.6/5.7/8.0 版本,兼容 Delete/Update 语句,及大部分常用的 DDL 操作。 也就是说,**CK支持作为MySQL的从节点存在,依赖订阅binlog方式实现。** https://bbs.huaweicloud.com/blogs/238417 ### 9 总结 ClickHouse更加适合OLAP场景,在报表库中有极大性能优势。如果想作为应用数据库,可以灵活采用其表引擎特点,尽量避免数据修改。其实,没有最好的,只有最合适的。 ------------ ###### 自猿其说Tech-JDL京东物流技术发展部 ###### 作者:供应链技术部 耿宏宇
原创文章,需联系作者,授权转载
上一篇:细分场景下设计赋能仓储体验
下一篇:京东多端全流程交易解决方案阿波罗平台iOS单元测试实践
相关文章
京东mPaaS平台之Android组件化系统私有化部署改造实践!
别困惑,不是你的错!90%的开发者把Clubhouse看成了Clickhouse!
JUST技术: 使用 ClickHouse实现时序数据管理
自猿其说Tech
文章数
426
阅读量
2149940
作者其他文章
01
深入JDK中的Optional
本文将从Optional所解决的问题开始,逐层解剖,由浅入深,文中会出现Optioanl方法之间的对比,实践,误用情况分析,优缺点等。与大家一起,对这项Java8中的新特性,进行理解和深入。
01
Taro小程序跨端开发入门实战
为了让小程序开发更简单,更高效,我们采用 Taro 作为首选框架,我们将使用 Taro 的实践经验整理了出来,主要内容围绕着什么是 Taro,为什么用 Taro,以及 Taro 如何使用(正确使用的姿势),还有 Taro 背后的一些设计思想来进行展开,让大家能够对 Taro 有个完整的认识。
01
Flutter For Web实践
Flutter For Web 已经发布一年多时间,它的发布意味着我们可以真正地使用一套代码、一套资源部署整个大前端系统(包括:iOS、Android、Web)。渠道研发组经过一段时间的探索,使用Flutter For Web技术开发了移动端可视化编程平台—Flutter乐高,在这里希望和大家分享下使用Flutter For Web实践过程和踩坑实践
01
配运基础数据缓存瘦身实践
在基础数据的常规能力当中,数据的存取是最基础也是最重要的能力,为了整体提高数据的读取能力,缓存技术在基础数据的场景中得到了广泛的使用,下面会重点展示一下配运组近期针对数据缓存做的瘦身实践。
自猿其说Tech
文章数
426
阅读量
2149940
作者其他文章
01
深入JDK中的Optional
01
Taro小程序跨端开发入门实战
01
Flutter For Web实践
01
配运基础数据缓存瘦身实践
添加企业微信
获取1V1专业服务
扫码关注
京东云开发者公众号