ClickHouse概览

　　希望通过以下介绍可以回答以下问题：CK是什么，为什么这么快CK的索引设计是怎样的常规查询有哪些优化方法（索引、物化视图、映射）一些入门的优化建议和配置优化参数有哪些如何选择合适的数据类型和编解码器Ck是什么
　　ClickstreamDatawareHouse
　　ClickHouse是一个用于在线分析处理（OLAP）的面向列的SQL数据库管理系统（DBMS）OLAP：大量数据集数十亿甚至万亿数据组织包含在许多列的表中任意查询仅返回少数列毫秒或者秒级返回结果
　　CK的设计目标：回答两个问题Filteraggregate。也即主要解决groupby类查询CK为什么这么快架构选择列式存储单次查询经常只使用其中部分列列存储后可以压缩，并且相同格式压缩比较高索引磁盘存储性价比高，同时也支持SSD和RAM有序存储数据压缩一般情况下，同一列数据差异性较小，可以实现较好的压缩比可指定专用编解码器向量化查询更高的CPU缓存利用率SIMD可伸展性并行执行，利用所有可用的CPU核数和磁盘来执行查询数据有多个shards，每个shard可以有多个replicas。所有shard都用于并行执行。对用户透明注重设计细节
　　相比于通用性，CK更注重效率。每一个任务都有各自的特征，不能简单的丢进去随便执行。CK的设计是具体任务具体分析。仅HashTable在ck中就有30多种变化。同样在算法选择上，也会根据不同的任务选择合适的算法。CK索引设计
　　为什么不是B（）tree查询时间复杂度：O（log2n），额外的磁盘和内存开销插入成本高有时会发生重新平衡Tree
　　稀疏索引CREATETABLEhitsUserIDURL（UserIDUInt32，URLString，EventTimeDateTime）ENGINEMergeTreePRIMARYKEY（UserID，URL）ORDERBY（UserID，URL，EventTime）SETTINGSindexgranularity8192，indexgranularitybytes0；
　　数据存储在磁盘上，按照primaryKeycolumn排序
　　数据处理的基本单位：granule
　　Primaryindex
　　Granule定位：markfile（多了一步，但是节约内存资源）
　　定位granual示例SELECTURL，count（URL）ASCountFROMhitsUserIDURLWHEREUserID749927693GROUPBYURLORDERBYCountDESCLIMIT10；
　　查询流程
　　数据写入预排序算法：LSMLSM算法的几个核心步骤：数据写入存储系统前首先记录日志，防止系统崩溃记录完日志后在内存中以供使用，当内存达到极限后写入磁盘，记录合并次数Level为0（L0）。已经写入磁盘的文件不可变。每过一段时间将磁盘上L和L1的文件合并我们用一个示例来展示下整个过程T0时刻，数据库为空。T1时刻，clickhouse收到一条500条insert的插入请求，这500条数据时乱序的。此时，clickhouse开始插入操作。首先将500条插入请求一次性写入日志。接着在内存中进行排序，排序完成后将有序的结果写入磁盘，此时L0；T2时刻，clickhouse收到一条800条insert的插入请求，这800条数据时乱序的。此时，clickhouse开始插入操作。首先将800条插入请求一次性写入日志。接着在内存中进行排序，排序完成后将有序的结果写入磁盘，此时L0；T3时刻，clickhouse开始合并，此时此刻，磁盘上存在两个L0的文件。这两个文件每个文件内部有序，但可能存在重合。（例如第一批500条的范围是300400，第二批800条数据的范围是350700）。因此需要合并。clickhouse在后台完成合并后，产生了一个新的L1的文件。将两个L0的文件标记为删除。T4时刻，clickhouse开始清理，将两个被标记为删除的文件真正地物理删除。T5时刻，clickhouse收到一条100条insert的插入请求，这100条数据时乱序的。此时，clickhouse开始插入操作。首先将100条插入请求一次性写入日志。接着在内存中进行排序，排序完成后将有序的结果写入磁盘，此时L0；T6时刻，clickhouse开始合并，此时此刻，磁盘上存在1个L0的文件和1个L1的文件。这两个文件每个文件内部有序，但不存在重合。（例如L0文件的范围是100200，L1文件的范围是300700）。因此不需要合并。clickhouse在后台将L0的文件升级成L1，此时数据库内存在两个L1的互不重合的文件。以上就是LSM算法在clickhouse上的应用，我们总结一下，clickhouse使用LSM算法将乱序的数据在内存中排序为有序的数据，然后写入磁盘保存，并且定期合并有重合的磁盘文件。
　　CK的预排序设计带来的弊病：
　　查询时，有多个level，返回数据是怎样的？如何解决。如果查询发生在合并前，就有可能数据分布在两个数据文件内。此时clickhouse默认会返回两个列表，这两个列表内部有序，但相互之间却会有重合合并后查询优化PrimaryIndex失效时怎么办
　　当查询在作为复合键的一部分并且是第一个键列的列上进行过滤时，ClickHouse将在键列的索引标记上运行二进制搜索算法。
　　但是当不能命中primaryindex时。clickhouse会怎么办SELECTUserID，count（UserID）ASCountFROMhitsUserIDURLWHEREURLhttp：publicsearchGROUPBYUserIDORDERBYCountDESCLIMIT10；10rowsinset。Elapsed：0。086sec。Processed8。81millionrows，799。69MB（102。11millionrowss。，9。27GBs。）
　　1。通用排除搜索算法（Genericexclusionsearchalgorithm）
　　排除规则：
　　两种情况：
　　通常情况下：如果userId的基数较大时，不管是排除算法和skipindex都不会有好的效果。这时有以下三种解决方案。不同点在于附加表的查询和插入对用户的透明度
　　2。SecondaryTables
　　使用不同的主键创建第二个表。查询时必须显示指定具体的表，并且查询时必须两张表显示写入以保持同步CREATETABLEhitsURLUserID（UserIDUInt32，URLString，EventTimeDateTime）ENGINEMergeTreePRIMARYKEY（URL，UserID）ORDERBY（URL，UserID，EventTime）SETTINGSindexgranularity8192，indexgranularitybytes0；INSERTINTOhitsURLUserIDSELECTfromhitsUserIDURL；OPTIMIZETABLEhitsURLUserIDFINAL；
　　3。MaterializedViews
　　使用物化视图，附加表是隐式创建的，并且数据在两个表之间自动保持同步CREATEMATERIALIZEDVIEWmvhitsURLUserIDENGINEMergeTree（）PRIMARYKEY（URL，UserID）ORDERBY（URL，UserID，EventTime）POPULATEASSELECTFROMhitsUserIDURL；SELECTUserID，count（UserID）ASCountFROMmvhitsURLUserIDWHEREURLhttp：publicsearchGROUPBYUserIDORDERBYCountDESCLIMIT10；
　　4。Projections
　　投影是最透明的选项，除了可以隐式的创建和同步附加表，还会自动选择最有效的表版本进行查询ALTERTABLEhitsUserIDURLADDPROJECTIONprjurluserid（SELECTORDERBY（URL，UserID））；ALTERTABLEhitsUserIDURLMATERIALIZEPROJECTIONprjurluserid；
　　5。DataSkippingindex
　　跳过一定没有匹配值的数据块。只适用于mergeTree引擎CREATETABLEskiptable（mykeyUInt64，myvalueUInt64）ENGINEMergeTreeprimarykeymykeySETTINGSindexgranularity8192；INSERTINTOskiptableSELECTnumber，intDiv（number，4096）FROMnumbers（100000000）；ALTERTABLEskiptableADDINDEXvixmyvalueTYPEset（100）GRANULARITY2；ALTERTABLEskiptableMATERIALIZEINDEXvix；SELECTFROMskiptableWHEREmyvalueIN（125，700）
　　Skippingindex并不总是有效，和数据在granual的分布强相关查询优化案例（通过映射提升查询效率）
　　1。limit优化
　　ALTERTABLEukpricepaidwithprojectionsADDPROJECTIONprjobytownprice（SELECTORDERBYtown，price）；ALTERTABLEukpricepaidwithprojectionsMATERIALIZEPROJECTIONprjobytownpriceSETTINGSmutationssync1；
　　2。聚合优化
　　ALTERTABLEukpricepaidwithprojectionsADDPROJECTIONprjgbycounty（SELECTcounty，avg（price）GROUPBYcounty）；ALTERTABLEukpricepaidwithprojectionsMATERIALIZEPROJECTIONprjgbycountySETTINGSmutationssync1；
　　思考一下：如何有效识别单行
　　查询复杂度约束配置
　　内存限制：
　　maxmemoryusageThemaximumamountofRAMtouseforrunningaqueryonasingleserver。
　　maxmemoryusageforuserThemaximumamountofRAMtouseforrunningauser’squeriesonasingleserver。
　　maxservermemoryusageLimitstotalRAMusagebytheClickHouseserver。
　　maxservermemoryusagetoramratio
　　线程
　　maxthreadpoolsize（10000）ClickHouseusesthreadsfromtheGlobalThreadpooltoprocessqueries
　　maxthreadpoolfreesize（1000）空闲线程数，超过这个值就会有线程被释放
　　threadpoolqueuesize（10000）
　　backgroundpoolsize（16）backgroundmergesandmutations
　　concurrentthreadssoftlimitnum（2022，为了提高查询QPS）
　　concurrentthreadssoftlimitratiotocores（2022）
　　maxconcurrentqueries（100）
　　maxconcurrentinsertqueries
　　maxconcurrentselectqueries
　　maxconcurrentqueriesforuser
　　maxconcurrentqueriesforallusers
　　maxthreadsThemaximumnumberofqueryprocessingthreads，excludingthreadsforretrievingdatafromremoteservers。Defaultvalue：thenumberofphysicalCPUcores。maxinsertthreads
　　ThemaximumnumberofthreadstoexecutetheINSERTSELECTquery。使用建议（偏运维）CPU缩放调节器
　　始终使用performancescalinggovernorCPU限制
　　处理器可能会过热。使用dmesg查看CPU的时钟速率是否因过热而受到限制。也可以在数据中心级别外部设置限制。您可以使用turbostat在负载下对其进行监控。内存
　　少量数据，采用和数据量一样的内存
　　大量数据，内存越多越好。
　　不要禁用overcommit（catprocsysvmovercommitmemory）
　　使用perftop查看内核在内存管理上花费的时间。
　　建议内存至少32g存储子系统
　　SSDHDD优先选择带有本地硬盘驱动器的大量服务器，而不是带有附加磁盘架的少量服务器。磁盘阵列
　　使用HDD时，您可以组合它们的RAID10、RAID5、RAID6或RAID50。对于Linux，软件RAID更好（使用mdadm）。我们不建议使用LVM。创建RAID10时，选择远布局。如果您的预算允许，请选择RAID10。
　　如果您有超过4个磁盘，请使用RAID6（首选）或RAID50，而不是RAID5。使用RAID5、RAID6或RAID50时，始终增加stripecachesize，因为默认值通常不是最佳选择。
　　对于大多数RAID配置，64KB的块大小就足够了。clickhouse服务器的平均写入大小约为1MB（1024KB），因此建议的条带大小也为1MB。如果将块大小设置为1MB除以RAID阵列中非奇偶校验磁盘的数量，则可以根据需要优化块大小，以便在所有可用的非奇偶校验盘上并行执行每个写入操作。切勿将块大小设置得太小或太大。
　　使用长队列启用NCQ。对于HDD，选择CFQ调度器，对于SSD，选择noop。不要减少预读设置。对于HDD，启用写缓存。
　　确保为操作系统中的NVME和SSD磁盘启用fstrim（通常使用cronjob或systemd服务实现）。文件系统
　　Ext4是最可靠的选项，将装载选项设置为：noatimeXFS也支持可以在NFS上工作，但不太好FAT32、exFAT不支持不要使用带压缩或加密的文件系统，CK自带的有，效果更好Linux内核
　　不要使用过时的linux内核网络
　　如果您使用的是IPv6，请增加路由缓存的大小。3。2之前的Linux内核在IPv6实现方面存在许多问题。
　　如果可能，至少使用10GB网络。1Gb也可以工作，但对于修补具有数十TB数据的副本，或处理具有大量中间数据的分布式查询，情况会更糟。大页（hugepages）
　　旧的Linux内核上，禁用透明大页，会干扰内存分配，导致性能限制下降。较新的Linux内核上开启透明大页是可以的虚拟机监控程序配置
　　OpenStack：nova。confcpumodehostpassthrough
　　libvert：
　　这对于ClickHouse能够使用cpuid指令获得正确的信息非常重要。否则，当管理程序在旧CPU型号上运行时，可能会出现非法指令崩溃。ClickHouseKeeper和ZooKeeper
　　在CK集群中建议ClickHouse代替Zookeeper防病毒软件
　　如果使用防病毒软件，请将其配置为跳过带有ClickHouse数据文件（varlibClickHouse）的文件夹，否则可能会降低性能，并且在数据摄取和后台合并过程中可能会遇到意外错误。优化Demo，通过优化字段Type和Codec来降低磁盘使用和提升查询速度
　　数据集
　　过去100年特定地点和特定时间的天气记录
　　表结构CREATETABLEnoaacodecv1（stationidStringCOMMENTIdofthestationatwhichthemeasurementastaken，dateDate32，tempAvgInt64COMMENTAveragetemperature（tenthsofadegreesC），tempMaxInt64COMMENTMaximumtemperature（tenthsofdegreesC），tempMinInt64COMMENTMinimumtemperature（tenthsofdegreesC），precipitationInt64COMMENTPrecipitation（tenthsofmm），snowfallInt64COMMENTSnowfall（mm），snowDepthInt64COMMENTSnowdepth（mm），percentDailySunInt64COMMENTDailypercentofpossiblesunshine（percent），averageWindSpeedInt64COMMENTAveragedailywindspeed（tenthsofmeterspersecond），maxWindSpeedInt64COMMENTPeakgustwindspeed（tenthsofmeterspersecond），weatherTypeString，locationPoint，elevationFloat64，nameString）ENGINEMergeTree（）ORDERBY（stationid，date）
　　导入数据大约10亿行。磁盘占用量如下：SELECTname，formatReadableSize（sum（datacompressedbytes））AScompressedsize，formatReadableSize（sum（datauncompressedbytes））ASuncompressedsize，round（sum（datauncompressedbytes）sum（datacompressedbytes），2）ASratioFROMsystem。columnsWHEREtablenoaacodecv1GROUPBYnameORDERBYsum（datacompressedbytes）DESCnamecompressedsizeuncompressedsizeratiodate2。24GiB3。93GiB1。76tempMax522。77MiB7。87GiB15。41tempMin519。53MiB7。87GiB15。51precipitation494。41MiB7。87GiB16。29tempAvg130。69MiB7。87GiB61。64snowDepth62。33MiB7。87GiB129。26weatherType37。87MiB6。87GiB185。7snowfall32。94MiB7。87GiB244。56location14。89MiB15。73GiB1081。94averageWindSpeed14。64MiB7。87GiB550。29maxWindSpeed11。09MiB7。87GiB726。54name9。63MiB14。58GiB1549。63elevation7。95MiB7。87GiB1012。79stationid7。60MiB11。80GiB1589。03percentDailySun6。59MiB7。87GiB1222。6715rowsinset。Elapsed：0。005sec。SELECTformatReadableSize（sum（datacompressedbytes））AScompressedsize，formatReadableSize（sum（datauncompressedbytes））ASuncompressedsize，round（sum（datauncompressedbytes）sum（datacompressedbytes），2）ASratioFROMsystem。columnsWHEREtablenoaacodecv1compressedsizeuncompressedsizeratio4。07GiB131。58GiB32。361rowinset。Elapsed：0。004sec。SELECTelevationrange，uniq（stationid）ASnumstations，max（tempMax）10ASmaxtemp，min（tempMin）10ASmintemp，sum（precipitation）AStotalprecipitation，avg（percentDailySun）ASavgpercentsunshine，max（maxWindSpeed）ASmaxwindspeed，sum（snowfall）AStotalsnowfallFROMnoaacodecv1WHERE（date19700101）AND（stationidIN（SELECTstationidFROMstationsWHEREcountrycodeUS））GROUPBYfloor（elevation，2）ASelevationrangeORDERBYelevationrangeASCFORMATNullOk。0rowsinset。Elapsed：1。615sec。Processed331。11millionrows，23。19GB（204。98millionrowss。，14。36GBs。）
　　通过修改type，降低磁盘占用量CREATETABLEnoaacodecv3（stationidLowCardinality（String）COMMENTIdofthestationatwhichthemeasurementastaken，dateDate32，tempAvgInt16COMMENTAveragetemperature（tenthsofadegreesC），tempMaxInt16COMMENTMaximumtemperature（tenthsofdegreesC），tempMinInt16COMMENTMinimumtemperature（tenthsofdegreesC），precipitationUInt16COMMENTPrecipitation（tenthsofmm），snowfallUInt16COMMENTSnowfall（mm），snowDepthUInt16COMMENTSnowdepth（mm），percentDailySunUInt8COMMENTDailypercentofpossiblesunshine（percent），averageWindSpeedUInt16COMMENTAveragedailywindspeed（tenthsofmeterspersecond），maxWindSpeedUInt16COMMENTPeakgustwindspeed（tenthsofmeterspersecond），weatherTypeEnum8（Normal0，Fog1，HeavyFog2，Thunder3，SmallHail4，Hail5，Glaze6，DustAsh7，SmokeHaze8，BlowingDriftingSnow9，Tornado10，HighWinds11，BlowingSpray12，Mist13，Drizzle14，FreezingDrizzle15，Rain16，FreezingRain17，Snow18，UnknownPrecipitation19，GroundFog21，FreezingFog22），latFloat32，lonFloat32，elevationInt16，nameLowCardinality（String））ENGINEMergeTree（）ORDERBY（stationid，date）SELECTname，formatReadableSize（sum（datacompressedbytes））AScompressedsize，formatReadableSize（sum（datauncompressedbytes））ASuncompressedsize，round（sum（datauncompressedbytes）sum（datacompressedbytes），2）ASratioFROMsystem。columnsWHEREtablenoaacodecv3GROUPBYnameORDERBYsum（datacompressedbytes）DESCnamecompressedsizeuncompressedsizeratiodate2。24GiB3。94GiB1。76precipitation469。11MiB1。97GiB4。3tempMax451。33MiB1。97GiB4。47tempMin437。15MiB1。97GiB4。61tempAvg120。28MiB1。97GiB16。76snowDepth42。80MiB1。97GiB47。1snowfall32。61MiB1。97GiB61。81weatherType16。48MiB1008。00MiB61。16averageWindSpeed10。27MiB1。97GiB196。24maxWindSpeed6。31MiB1。97GiB319。57name3。99MiB1。92GiB492。99lat3。57MiB3。94GiB1127。84lon3。57MiB3。94GiB1130。25stationid3。40MiB1。92GiB577。5elevation1。89MiB1。97GiB1065。35percentDailySun1。51MiB1008。00MiB667。67SELECTformatReadableSize（sum（datacompressedbytes））AScompressedsize，formatReadableSize（sum（datauncompressedbytes））ASuncompressedsize，round（sum（datauncompressedbytes）sum（datacompressedbytes），2）ASratioFROMsystem。columnsWHEREtablenoaacodecv3compressedsizeuncompressedsizeratio3。81GiB35。34GiB9。28
　　优化codecCREATETABLEnoaacodecoptimal（stationidLowCardinality（String），dateDate32CODEC（DoubleDelta，ZSTD（1）），tempAvgInt16CODEC（T64，ZSTD（1）），tempMaxInt16CODEC（T64，ZSTD（1）），tempMinInt16CODEC（T64，ZSTD（1）），precipitationUInt16CODEC（T64，ZSTD（1）），snowfallUInt16CODEC（T64，ZSTD（1）），snowDepthUInt16CODEC（ZSTD（1）），percentDailySunUInt8，averageWindSpeedUInt16CODEC（T64，ZSTD（1）），maxWindSpeedUInt16CODEC（T64，ZSTD（1）），weatherTypeEnum8（Normal0，Fog1，HeavyFog2，Thunder3，SmallHail4，Hail5，Glaze6，DustAsh7，SmokeHaze8，BlowingDriftingSnow9，Tornado10，HighWinds11，BlowingSpray12，Mist13，Drizzle14，FreezingDrizzle15，Rain16，FreezingRain17，Snow18，UnknownPrecipitation19，GroundFog21，FreezingFog22），latFloat32，lonFloat32，elevationInt16，nameLowCardinality（String））ENGINEMergeTreeORDERBY（stationid，date）SELECTname，formatReadableSize（sum（datacompressedbytes））AScompressedsize，formatReadableSize（sum（datauncompressedbytes））ASuncompressedsize，round（sum（datauncompressedbytes）sum（datacompressedbytes），2）ASratioFROMsystem。columnsWHEREtablenoaacodecoptimalGROUPBYnameORDERBYsum（datacompressedbytes）DESCnamecompressedsizeuncompressedsizeratioprecipitation416。51MiB2。01GiB4。93tempMax400。70MiB2。01GiB5。13tempMin388。00MiB2。01GiB5。29tempAvg101。50MiB2。01GiB20。24snowDepth43。48MiB2。01GiB47。24snowfall28。72MiB2。01GiB71。51date24。28MiB4。01GiB169。17weatherType16。90MiB1。00GiB60。76averageWindSpeed9。37MiB2。01GiB219。32maxWindSpeed6。17MiB2。01GiB332。67name5。07MiB1。98GiB400。41stationid4。52MiB1。97GiB447。45lat3。64MiB4。01GiB1128。65lon3。63MiB4。01GiB1130。98elevation1。93MiB2。01GiB1066。81percentDailySun1。56MiB1。00GiB658。76SELECTformatReadableSize（sum（datacompressedbytes））AScompressedsize，formatReadableSize（sum（datauncompressedbytes））ASuncompressedsize，round（sum（datauncompressedbytes）sum（datacompressedbytes），2）ASratioFROMsystem。columnsWHEREtablenoaacodecoptimalcompressedsizeuncompressedsizeratio1。42GiB36。05GiB25。36SELECTelevationrange，uniq（stationid）ASnumstations，max（tempMax）10ASmaxtemp，min（tempMin）10ASmintemp，sum（precipitation）AStotalprecipitation，avg（percentDailySun）ASavgpercentsunshine，max（maxWindSpeed）ASmaxwindspeed，sum（snowfall）AStotalsnowfallFROMnoaacodecoptimalWHERE（date19700101）AND（stationidIN（SELECTstationidFROMstationsWHEREcountrycodeUS））GROUPBYfloor（elevation，2）ASelevationrangeORDERBYelevationrangeASCFORMATNull0rowsinset。Elapsed：1。235sec。Processed330。20millionrows，6。48GB（267。28millionrowss。，5。25GBs。）
　　优化结果总结
　　磁盘占用（压缩前后）4。07GiB131。58GiB1。42GiB36。05GiB
　　相同的语句查询速度1。615sec1。235sec
　　type和codec优化建议
　　选择使用哪种编解码器和压缩算法最终归结为了解数据的特征以及编解码器和压缩算法的属性。多测试、以实际测试结果为准。
　　以下为一般准则：总是采用ZSTD，没有编解码器的ZSTD通常优于其他压缩的选项。特别是浮点数。Deltaforintegersequences。只要有单调序列或连续值中的小增量，就选择Delta编解码器。前提是导数产生的数字很小。如果不是，尝试DoubleDelta。单调增量均匀的序列将压缩得更好。未知模式，尝试Gorilla和T64。Gorilla主要用于值变化较小的浮点数据。专门计算当前值和先前值之间的XOR，并将其以紧凑的二进制形式写入：当相邻值相同时，结果最好。T64forsparseorsmallranges，T64可以对稀疏数据或块中的范围很小时有效。避免使用T64随机数。Gorilla可能用于浮点和仪表数据。Gorilla在浮点数据上的有效性，特别是代表仪表读数的数据，即随机尖峰。这与算法属性一致。delta编码可以改进ZSTD压缩如果可能，LZ4优于ZSTD如果在LZ4和ZSTD之间获得可比较的压缩，请选择前者，因为它提供更快的解压缩并且需要更少的CPU。然而，在大多数情况下，ZSTD的性能将大大优于LZ4。与没有编解码器的ZSTD相比，其中一些编解码器与LZ4结合可以更快地工作，同时提供类似的压缩。然而，这将是特定于数据的，并且需要测试。