谈谈ClickHouse性能情况以及相关优化

ClickHouse性能情况

主要分为4个方面

1、单个查询吞吐量

场景一：

如果数据被放置在page cache中，则一个不太复杂的查询在单个服务器上大约能够以2-10GB／s（未压缩）的速度进行处理（对于简单的查询，速度可以达到30GB／s）

场景二：

如果数据没有在page cache中的话，那么速度将取决于你的磁盘系统和数据的压缩率

例如：

a、如果一个磁盘允许以400MB／s的速度读取数据，并且数据压缩率是3，则数据的处理速度为1.2GB/s。 b、这意味着，如果你是在提取一个10字节的列，那么它的处理速度大约是1-2亿行每秒 c、对于分布式处理，处理速度几乎是线性扩展的，但这受限于聚合或排序的结果不是那么大的情况下

2、处理短查询的延时时间

（1）数据被page cache缓存的情况下，它的延迟应该小于50毫秒(最佳情况下应该小于10毫秒)，否则，延迟取决于数据的查找次数 （2）延迟可以通过以下公式计算得知：查找时间（10 ms） * 查询的列的数量 * 查询的数据块的数量

3、处理大量短查询

（1）ClickHouse可以在单个服务器上每秒处理数百个查询（在最佳的情况下最多可以处理数千个） （2）但是由于这不适用于分析型场景。建议每秒最多查询100次

4、数据写入性能

（1）建议每次写入不少于1000行的批量写入，或每秒不超过一个写入请求 （2）当使用tab-separated格式将一份数据写入到MergeTree表中时，写入速度大约为50到200MB/s （3）如果您写入的数据每行为1Kb，那么写入的速度为50，000到200，000行每秒 （4）如果您的行更小，那么写入速度将更高 （5）如果您的行更小，那么写入速度将更高

注意：ClickHouse并非无所不能，查询语句需要不断的调优，可能与查询条件有关，不同的查询条件表是左join还是右join也是很有讲究的

补充问题：

mysql与ClickHouse性能写入区别？

mysql：

（1）MySQL单条SQL是单线程的，只能跑满一个core （2）IO方面，MySQL是行存储，MySQL需要大量随机IO

ClickHouse：

（1）ClickHouse相反，有多少CPU，吃多少资源，所以飞快 （2）ClickHouse不支持事务，不存在隔离级别。ClickHouse的定位是分析性数据库，而不是严格的关系型数据库 （3）IO方面，ClickHouse是列存储，后者在count()这类操作天然有优势，ClickHouse基本是顺序IO

思考：

据导入的时候，数据肯定缓存在内存里了，这个的确，但是ClickHouse基本上是顺序IO。对IO基本没有太高要求，当然，磁盘越快，上层处理越快，但是99%的情况是，CPU先跑满了（数据库里太少见了，大多数都是IO不够用）

二、ClickHouse相关优化

（1）关闭虚拟内存，物理内存和虚拟内存的数据交换，会导致查询变慢

（2）为每一个账户添加join_use_nulls配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准SQL中的Null值

（3）JOIN操作时一定要把数据量小的表放在右边，ClickHouse中无论是Left Join 、Right Join还是Inner Join永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表

（4）批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致ClickHouse无法及时对新导入的数据进行合并，从而影响查询性能

（5）尽量减少JOIN时的左右表的数据量，必要时可以提前对某张表进行聚合操作，减少数据条数。有些时候，先GROUP BY再JOIN比先JOIN再GROUP BY查询时间更短

（6）ClickHouse的分布式表性能性价比不如物理表高，建表分区字段值不宜过多，防止数据导入过程磁盘可能会被打满

（7）CPU一般在50%左右会出现查询波动，达到70%会出现大范围的查询超时，CPU是最关键的指标，要非常关注

三、ClickHouse有哪些优缺点？

优点：

（1）为了高效的使用CPU，数据不仅仅按列存储，同时还按向量进行处理 （2）数据压缩空间大，减少IO；处理单查询高吞吐量每台服务器每秒最多数十亿行 （3）索引非B树结构，不需要满足最左原则；只要过滤条件在索引列中包含即可；即使在使用的数据不在索引中，由于各种并行处理机制ClickHouse全表扫描的速度也很快 （4）写入速度非常快，50-200M/s，对于大量的数据更新非常适用

缺点：

（1）不支持事务，不支持真正的删除/更新 （2）不支持高并发，官方建议qps为100，可以通过修改配置文件增加连接数，但是在服务器足够好的情况下 （3）不支持真正的删除/更新支持 不支持事务（期待后续版本支持） （4）不支持二级索引 （5）有限的SQL支持，join实现与众不同 （6）不支持窗口功能 （7）元数据管理需要人工干预维护 （8）SQL满足日常使用80%以上的语法，join写法比较特殊；最新版已支持类似SQL的join，但性能不好 （9）ClickHouse快是因为采用了并行处理机制，即使一个查询，也会用服务器一半的CPU去执行，所以ClickHouse不能支持高并发的使用场景，默认单查询使用CPU核数为服务器核数的一半，安装时会自动识别服务器核数，可以通过配置文件修改该参数

四、ClickHouse的特性有哪些？

（1）真正的列式数据库管理系统

概述： 除了数据本身外不应该存在其他额外的数据意味着为了避免在值旁边存储它们的长度«number»，你必须支持固定长度数值类型  例如： a、10亿个UInt8类型的数据在未压缩的情况下大约消耗1GB左右的空间，如果不是这样的话，这将对CPU的使用产生强烈影响 b、即使是在未压缩的情况下，紧凑的存储数据也是非常重要的，因为解压缩的速度主要取决于未压缩数据的大小 注意： a、在一些其他系统中也可以将不同的列分别进行存储，但由于对其他场景进行的优化，使其无法有效的处理分析查询。例如：HBase，BigTable，Cassandra，HyperTable b、在这些系统中，你可以得到每秒数十万的吞吐能力，但是无法得到每秒几亿行的吞吐能力 说明： a、ClickHouse不单单是一个数据库， 它是一个数据库管理系统 b、它允许在运行时创建表和数据库、加载数据和运行查询，而无需重新配置或重启服务

（2）数据压缩 

a、一些列式数据库管理系统中(例如：InfiniDB CE 和 MonetDB) 并没有使用数据压缩 b、但是, 若想达到比较优异的性能，数据压缩确实起到了至关重要的作用。

（3）数据的磁盘存储 

a、许多的列式数据库(如 SAP HANA, Google PowerDrill)只能在内存中工作，这种方式会造成比实际更多的设备预算 b、ClickHouse被设计用于工作在传统磁盘上的系统，它提供每GB更低的存储成本，但如果有可以使用SSD和内存，它也会合理的利用这些资源

（4）多核心并行处理 

ClickHouse会使用服务器上一切可用的资源，从而以最自然的方式并行处理大型查询

（5）多服务器分布式处理 

a、列式数据库管理系统中，几乎没有一个支持分布式的查询处理 b、在ClickHouse中，数据可以保存在不同的shard上，每一个shard都由一组用于容错的replica组成，查询可以并行地在所有shard上进行处理。这些对用户来说是透明的

（6）支持SQL 

a、ClickHouse支持基于SQL的声明式查询语言，该语言大部分情况下是与SQL标准兼容的 b、支持的查询包括 GROUP BY，ORDER BY，IN，JOIN以及非相关子查询 c、不支持窗口函数和相关子查询

（7）向量引擎 

为了高效的使用CPU，数据不仅仅按列存储，同时还按向量(列的一部分)进行处理，这样可以更加高效地使用CPU

（8）实时的数据更新 

a、ClickHouse支持在表中定义主键 b、为了使查询能够快速在主键中进行范围查找，数据总是以增量的方式有序的存储在MergeTree中 c、因此，数据可以持续不断地高效的写入到表中，并且写入的过程中不会存在任何加锁的行为

（9）索引

按照主键对数据进行排序，这将帮助ClickHouse在几十毫秒以内完成对数据特定值或范围的查找

（10）适合在线查询

在线查询意味着在没有对数据做任何预处理的情况下以极低的延迟处理查询并将结果加载到用户的页面中

（11）支持近似计算 

ClickHouse提供各种各样在允许牺牲数据精度的情况下对查询进行加速的方法： a、用于近似计算的各类聚合函数，如：distinct values, medians, quantiles b、基于数据的部分样本进行近似查询。这时，仅会从磁盘检索少部分比例的数据 c、不使用全部的聚合条件，通过随机选择有限个数据聚合条件进行聚合。这在数据聚合条件满足某些分布条件下，在提供相当准确的聚合结果的同时降低了计算资源的使用

（12）支持数据复制和数据完整性 

a、ClickHouse使用异步的多主复制技术 b、当数据被写入任何一个可用副本后，系统会在后台将数据分发给其他副本，以保证系统在不同副本上保持相同的数据 c、在大多数情况下ClickHouse能在故障后自动恢复，在一些少数的复杂情况下需要手动恢复。