文档介绍：该【ClickHouse的执行计划以及优化策略 】是由【hh思密达】上传分享，文档一共【15】页，该文档可以免费在线阅读，需要了解更多关于【ClickHouse的执行计划以及优化策略 】的内容，可以使用淘豆网的站内搜索功能，选择自己适合的文档，以下文字是截取该文章内的部分文字，如需要获得完整电子版，请下载此文档到您的设备，方便您编辑和打印。:..ClickHouse的执?计划以及优化策略执??计划,??将?志级别设置为trace。<!--新版本默认是trace--><logger><level>trace</level></logger>然后还要真正执?相应的SQL语句,在执??志??查看,很明显这是?常不?便的。??引?了原?的执?计划的语法(此时处于试?期阶段),。。然后我们来介绍如何查看执?计划,不过介绍之前我们先创建?张数据表,这次我们采?真实的数据。?先ClickHouse官?提供了两个数据集,其中数据?数和字段数都?常的?,不亚于?些公司?产环境上的数据,我们来下载?下。#下载数据集,这?的数据集不是CSV、JSON,?、.mrk等物理?件#也就是说数据集本?就是符合ClickHouse物理存储的curl-O#所以我们直接解压到拷贝到/var/lib/clickhouse?录下即可tar--C/var/lib/clickhouse然后我们就可以使?hits_v1这张表了,我们之前说过,必须要先创建表然后再导?数据,因为创建表的时候会?成?些元信息,存储在/var/lib/clickhouse/metadata?录下,?光有数据没有元信息是不?的。但对于当前??则不?事先创建表,因为ClickHouse将元信息也准备好了,所以我们直接拷贝过去即可。压缩包解压之后,会有?个data?录和?个metadata?录,所以我们解压到/var/lib/clickhouse中,会?动将data?录??的内容合并到/var/lib/clickhouse的data?录中,将metadata?录??的内容合并到/var/lib/clickhouse的metadata?录中。[******@satoridata]#lsdatasetsdefaultsystem[******@satoridata]#lsdatasetshits_v1我们看到??多了?个datasets?录,datasets?录下才是hits_v1,显然我们后续需要使??查询。当然数据集还有?份,我们按照相同的套路即可。curl-Otar--C/var/lib/clickhouse我们来确认?下:[******@satoridatasets]#lshits_v1visits_v1显然数据集已经准备完毕,不过我们当前使?的是root?户,还应该要确保clickhouse?户有相应的操作权限。chownclickhouse:clickhouse/var/lib/clickhouse/data-R#然后重启ClickHouse,因为我们不是通过CREATETABLE创建的表#因此要重启,不然ClickHouse是不知道我们通过拷贝?件的?式新增了两张表clickhouserestart重启之后,执?SQL语句进?查看::..数据量还是不少的,、字段数130多个,、字段数为180多个,还是很?的。具体都有哪些字段,可以通过/var/lib/clickhouse/metadata/?件进?查看。有了数据集,我们就可以介绍查询计划了。当然使?这种规模的数据集有些?题?做,不过既然ClickHouse是为?数据准备的,那么使???点的数据集也?妨,?且我们后?也会经常使?这些数据集。基本语法在MySQL中查看执?计划使?的语法是什么呢?没错,EXPLAIN,在ClickHouse中也是如此,只不过ClickHouse将EXPLAIN变得更加丰富。EXPLAN[AST|SYNTAX|PLAN|PIPELINE][SETTINGS=value,...]SELECT...[FORMAT...]我们看?下第?个中括号??的内容,ClickHouse除了可以让我们查看执?计划之外,还可以查看很多其它内容。1)AST:查看编译之后的语法树,这个不是很常?EXPLAINASTSELECTUserID,count()<toDate('2014-03-17')GROUPBYUserID;执??下,查看?成的语法树::..2)SYNTAX:?于优化语法,有时我们指定的查询语句未必是最优的,那么ClickHouse在底层会进?优化,EXPLAINSYNTAX可以返回对?条SQL语句进?优化后的结果。通过对?优化前和优化后的SQL语句,可以有助于我们理解ClickHouse的优化机制EXPLAINSYNTAXSELECTUserID,count()<toDate('2014-03-17')GROUPBYUserIDORDERBYcountLIMIT10;我们看到仅仅是做了?些格式上调整,但优化前和优化后的语句本质上没差别,证明对于当前查询??,我们写的SQL语句就是最优的。因为这条语句太简单了,ClickHouse没有什么可优化的。然后我们来写?个?常规的语句,?如三元表达式:--这是嵌套的三元表达式,那么ClickHouse会怎么优化呢?EXPLAINSYNTAXSELECTnumber<5?'?于5':(number=5?'等于5':'?于5')FROMnumbers(10);:..注意:这?并没有开启优化,只不过是将三元表达式使?if语句替换了,因为没有嵌套的三元表达式在底层就是对应if函数的?个调?。只不过ClickHouse将?些?较特殊的函数调?,抽象成了?些语法糖,但本质上是没有变化的,所以当前的SQL语句并没有得到优化。事实上,ClickHouse对三元表达式的优化默认是关闭的,我们可以将其打开。--见名知意,就是当出现if的嵌套时,优化成multiIfSEToptimize_if_chain_to_multiif=1;SYNTAX还是很常?的,我们写完?条SQL语句之后,可以直接EXPLAINSYNTAX?下,然后将返回的结果替换掉我们原来的SQL语句。PLAN:查看执?计划,默认选项EXPLAINSELECTUserID,count()<toDate('2014-03-17')GROUPBYUserIDORDERBYcountLIMIT10;我们看到图中的EXPLAIN后?并没有带上PLAN,说明PLAN是默认选项,然后查看执?计划时还可以设置?些额外的参数::..header:打印计划中各个步骤的head说明,默认值为0表?关闭,如果开启,设置为1description:打印计划中各个步骤的描述,就是图中括号??的部分,默认值为1表?开启,如果关闭,设置为0actions:打印计划中各个步骤的详细信息,默认值为0表?关闭,如果开启,设置为1EXPLAINheader=1,actions=1SELECTUserID,count()<toDate('2014-03-17')GROUPBYUserIDORDERBYcountLIMIT10;输出的内容?常多,可以测试?下。PIPELINE:查看PIPELINE计划,类似于PLANEXPLAINPIPELINESELECTsum(number)FROMnumbers(100000)GROUPBYnumber%20;类似于PLAN,查看PIPELINE计划时还可以设置?些额外的参数:header:打印计划中各个步骤的head说明,默认值为0表?关闭,如果开启,设置为1graph:?DOT图形语?描述管道图,默认关闭,actions:表?当开启graph之后是否紧凑打印,默认开启EXPLAINPIPELINEheader=1,graph=1SELECTsum(number)FROMnumbers(100000)GROUPBYnumber%20;可以??查看?下输出。建表优化我们在创建表的时候,需要指定的内容?较多,?如ORDERBY、表引擎、表参数、分区字段等等,这些对后续数据的查询效率都是有影响的,当然指定合适的数据类型也是?常重要的。下?就来介绍?下常见的优化?段。数据类型在建表的时候能?数值类型和?期时间类型表?的字段就不要使?字符串,虽然字符串类型在以Hive为中?的数仓建设中?常常见,但ClickHouse却并?如此。我们知道在Hive中,?期?般都?字符串,不会特意使?Date类型。但在ClickHouse中,能不要String就不要?,因为后期还要转换。对于DateTime,ClickHouse底层会转成时间戳进?存储,但我们不要显式地使?UInt64类型来存储。因为DateTime不需要经过函数转换处理,执?效率?,可读性好。CREATETABLEtest_t(idUInt32,productString,amountDecimal(16,2),create_timeUInt32--这?使?了整数存储时间)ENGINE=ReplacingMergeTree(create_time)PARTITIONBYtoYYYYMMDD(toDate(create_time))--需要转换?次,否则报错PRIMARYKEYidORDERBYid除了?期类型和数值类型不?字符串表?之外,Null也是拖累性能的?个罪魁祸?,因为官?已经指出Null会影响性能了。因为存储Nullable类型的列时,需要创建?个额外的?件来存储Null标记,并且Nullable类型的列?法被索引。因此除了极特殊的情况,否则不要将列设置为Nullable,可以??个不可能出现的默认值、或者在业务中?意义的来代指空,例如将id设置为-1表?该商品没有id,?不是使?Null。分区和索引分区粒度根据业务特点决定,但不宜过粗或者过细,如果数据之间是严格按照时间来划分,?如经常要按天、按?或者按年汇总处理,那么不妨选择按天分区或者按?分区;如果数据按照地区来划分,?如经常针对不同的地区单独汇总,那么不妨按照地区分区。那么分区到底要分多少个区呢?以单表?亿条数:..据为例,分区??控制在10到30个最好。所以如果按照时间分区,那么我们?般都会按天、按?分区,?于按分钟分区则dark不必,因为这样分区?录就太多了。还有指定索引列,默认通过ORDERBY指定。ORDERBY在ClickHouse中是最重要的,因为分区内的排序通过ORDERBY指定,主键(索引)默认也是由ORDERBY指定,即使我们显式地使?PRIMARYKEY不使?ORDERBY,那么主键也必须是排序键的前缀。当然这?的ORDERBY指的是建表时的ORDERBY,不是查询语句中的ORDERBY。然后我们在通过ORDERBY指定索引列的时候,应该指定查询中经常被?来充当筛选条件的列,可以是单?维度,也可以是组合维度,如果是组合维度,那么索引列要满?查询频率?的在前原则。还有基数特别?的不适合做索引列,基数?指的就是那些重复数据?常少的列。表参数index_granularity是?来控制索引粒度的,默认是8192,如?必须不建议调整。另外,如果?张表不是必须要保留全量历史数据,则建议指定TTL,可以免去?动清理过期历史数据的?烦,TTL也可以通过ALTERTABLE语句随时修改。写?和删除优化尽量不要执?单条或?批量删除、插?操作,这样会产??分区?件,给后台Merge任务带来巨?压?。不要?次写?太多分区,或者数据写?太快,数据写?太快会导致Merge速度跟不上?报错,?般建议每秒钟发起2~3此写?操作,每次操作写?2w~5w条数据(依服务器性能?定)。常见配置我们知道配置?件位于/etc/clickhouse-server?录下,??,我们之前?,?常重要。它们都表?服务端的配置,???的配置是?法覆盖的,我们在命令?经常会使?set命令将某个参数进?修改,。当然?,,服务端?,。?我们修改配置主要是为了调整CPU、内存、IO,瓶颈主要在这?。因为ClickHouse会有后台线程Merge数据,所以?常的吃CPU;当然加载数据,对内存也是?个考量;同理还有IO,因为要从磁盘上读取?量数据。下?来介绍与这三个配置有关的参数。1)CPUbackground_pool_size:,?常重要的?个参数,表?后台线程池内的线程数量,Merge线程就是在该线程池中执?,该线程池不仅仅是给Merge线程?的。默认值为16,允许的前提下建议改成CPU个数的?倍。所以ClickHouse不建议和HDFS、Yarn等?起部署,因为ClickHouse太吃资源了,不然也达不到如此可观的速度background_schedule_pool_size:,表?执?后台任务的线程数,默认值为128,允许的前提下建议改成CPU个数的?倍background_distributed_schedule_pool_size:,表?分布式发送执?后台任务的线程数,默认值为16,允许的前提下建议改成CPU个数的?倍max_concurrent_queries:,表?最?并发处理的请求数(包含SELECT、INSERT等等),默认值为100,推荐150~300,不够再加max_threads:,表?单个查询所能使?的最?CPU个数,默认是CPU核数**以上是关于CPU相关的设置,如果发现机器吃不消了,那么不妨减少?下线程数。**2)Memorymax_memory_usage:,表?单次Query占?内存的最?值,该值可以设置的??些,这样可以提?集群查询的上限。当然也要保留?些给OS,?如128G的内存,设置为100G即可max_bytes_before_external_group_by:表?GROUPBY使?的内存的最?值,?旦超过这个最?值,那么会刷新到磁盘进?GROUPBY,?般按照max_memory_usage的?半设置即可。因为ClickHouse聚合分两个阶段,查询并建?中间数据、合并中间数据max_bytes_before_external_sort:表?ORDERBY使?的内存的最?值,?旦超过这个最?值,那么会刷新到磁盘进?ORDERBY。如果不设置该值,那么当内存不够的时候直接报错,设置了该值,ORDERBY在内存不够的时候可以基于磁盘完成,但是速度相对就慢了(实际测试发现慢得多,甚??法接受)。该参数和上?。max_table_size_to_drop:,应?于需要删除表或分区的情况,默认是50GB,意思是如果删除50GB以上的数据会失败。建议设置为0,表??论分区表多?都可以删除3)IO和HDFS不同,ClickHouse不?持设置多数据?录,为了提升IO性能,可以挂载虚拟券组(将多块磁盘虚拟成?块磁盘),通过?个券组绑定多块物理磁盘提升读写性能。或者使?SSD,但是成本就?较?了。ClickHouse语法优化规则很多数据库底层都内置了优化器,定义好了许多的优化规则,?于给我们的SQL语句进?优化,?如??表JOIN、谓词下推等等,就是为了避免开发?员执?慢查询。那么ClickHouse会对哪些查询进?优化呢?我们来看?下。COUNT优化:..我们说如果统计?张表有多少?,那么使?count()或者count(*)即可,。?件吗?我们在介绍MergeTree的时候说过,该?件??存储了表的?数,当使?count()或者count(*)的时候,直接读取该?件即可,此时是不需要全表扫描的。类似于关系型数据库也是如此,MySQL在使?count()的时候也是直接计算的B+树的叶?结点个数。但当我们count?个字段的时候,那么就必须要全表扫描了,?且我们说过count字段的时候统计的是该字段中?空的值的个数。如果该字段中没有空值,count(字段)的结果和count()、count(*)是相等的。对?输出信息的话,我们看到count(字段)进?了全表扫描。再?如count(1),我们看看它会不会被优化:satori:)EXPLAINSYNTAXSELECTcount(1);EXPLAINSYNTAXSELECTcount(1):f59337ec-58e3-4a37-b00d-eaa796f54f65┌─explain───────────────┐│SELECTcount()│││└───────────────────────┘:?个整型,没有什么实际意义,所以直接变成了count()。谓词下推在SQL中,谓词就是返回boolean值的函数,或隐式转换为bool的函数,说?了你就简单理解为WHERE语句即可。?谓词下推指的是将过滤表达式尽可能移动?靠近数据源的位置,从事后过滤变成事前过滤。举个最简单的栗?就是WHERE和HAVING,我们知道WHERE是发?在GROUPBY之前的,HAVAING发?在GROUPBY之后。SELECTUserID,count()=1785640464950496314;/*┌──────────────UserID─┬─count()─┐│1785640464950496314│105│└─────────────────────┴─────────┘*/上?这?SQL语句执?的时候虽然没有任何问题,但很明显这是?个糟糕的SQL语句,因为要先对将近900万的数据进?聚合,然后选择UserID为1785640464950496314的记录。既然如此,那我们为什么不能先把UserID为1785640464950496314的记录选出来,然后再单独进?聚合呢?这样的话数据量会少很多。:..我们看到在优化之后的SQL语句将条件从HAVING移到了WHERE,所以将过滤表达式尽可能移动?靠近数据源的位置,在计算之前先将??数据过滤掉,这个过程就是谓词下推。当然谓词下推不仅仅是这?的HAVING,?查询也?持,举个栗?,我们要根据UserID从hits_v1表中查询?个?户的记录,但是这些值必须存在于visits_v1的UserID字段中。SELECTUserID,(329024891984319329,3341630990649416532,3444082748272603552);显然这是?常简单的,但如果我们规定UserID还必须要出现在visits_v1表的UserID字段中,那么要怎么做呢?最简单的做法就是?个条件即可。SELECTUserID,(329024891984319329,3341630990649416532,3444082748272603552)ANDUserIDIN();但很明显这条语句就不是最优解,因为?查询会扫描全表,也就是visits_v1会全量读取。既然UserID要在两个表中都出现,那么就应该优先把过滤条件放在?查询??。SELECTUserID,(SELECTUserID--数据量?的话,还可以进?(329024891984319329,3341630990649416532,3444082748272603552));这种做法显然更优,因为visits_v1不需要全量读取,但ClickHouse?前还做不了这种优化,ClickHouse所能做的?查询谓词下推还是很有限的。当然不光是?查询,相?Hive,ClickHouse所做的优化?常有限,不同的SQL语句效率相差?倍以上都是很正常的,因为我们写SQL就不可以肆?忌惮。作为表进?JOIN的?查询会消除重复字段如果?查询中重复选择了某个字段,那么当它作为表进?JOIN的时候,会去除重复字段。,,(SELECTUserID,UserID,)bUSING(UserID)LIMIT3这?进?JOIN的右表是?个?查询,?在这个?查询??我们选择了两次UserID,那么ClickHouse会进?优化,变成只选择?次。:..可能有?好奇,如果我给第?个UserID起?个别名会怎么样呢?答案是即使起了别名,仍然只会选择?次。注意:这?的?查询在JOIN的时候会删除重复字段,但如果不是在JOIN的时候就不?样了。EXPLAINSYNTAXSELECTUserID,URLFROM(SELECTUserID,UserID,)LIMIT3这?的?查询当中我们选择了两个UserID,那么ClickHouse会不会变成?个呢?:..我们看到并没有优化掉,所以ClickHouse所做的优化还是?较有限的。聚合计算外推什么是聚合计算外推呢?举个栗?:SELECTsum(RequestNum*2);你觉得上?的SQL有能够优化的地?吗?我们看看ClickHouse是如何做的。没优化的时候,相当于是在sum之前先给每?条数据做?次乘法运算,然后进?sum;优化之后则是先进性sum,最后只对总和进??次乘法运算,显然后者更优。聚合函数消除我们在使?GROUPBY的时候有?个限制,那就是SELECT中没有使?聚合函数的字段必须出现在分组字段中。举?个栗?:--字段b出现在了SELECT中,并且没有使?聚合函数,所以它?定要出现在分组字段(GROUPBY)中--但没有出现,所以报错SELECTa,b,count(c)FROMtGROUPBYa;--此时没有问题,a和b都出现在GROUPBY中--?于字段c,它是以count(c)的形式出现的,使?了聚合函数,所以没问题SELECTa,b,count(c)FROMtGROUPBYa,b;--这条语句也是?法的,因为b没有出现在GROUPBY中,?于count(b)和b?关--既然SELECT中出现了没有使?聚合函数的字段b,那么它就必须要出现在GROUPBY中SELECTa,b,count(b),count(c)FROMtGROUPBYa;当然这些属于基础内容了,我主要想表达的是,我们可不可以对分组字段使?聚合函数呢??如说::..SELECTmax(UserID),max(Age),min(Age),sum(Age),但是这么做没有任何意义,因为分组就是把分组字段对应的值相同的归为?组,?如这?的age,所以每?组的age的值都是?样的。既然都?样,那么做聚合就没有太?意义,因此ClickHouse会那些对分组字段使?min、max、any的聚合函数给删掉。因为每?组的所有值都是?样的,最?值、最?值、第??的值,三者之间没差别。我们看到聚合函数min、max被剥掉了,只留下了Age字段,因为分组字段的值都是?样的,min、max、any没有意义。但聚合函数仅限于max、min、any,如果是sum就不会了,虽然从业务的?度上来说也没有太?意义,但毕竟sum涉及到加法运算,所以它不会被剥掉。删除重复的ORDERBYKEY类似于消除重复字段,如果指定了多个相同的排序字段,那么只会保留?个。,Age,AgeDESC,AgeDESC;我们看到即使不同的排序,也会只保留相同排序字段的第?个,因为同?个字段即升序?降序本?就很奇怪。删除重复的LIMITBYKEY还记得LIMITBY吗?"LIMITNBY字段"表?按照字段进?分组,然后选出每组的前N条数据。如果BY后?的字段重复了,那么也会删除掉。EXPLAINSYNTAXSELECTUserID,,UserID:..删除重复的USINGKEYUSING也是如此,直接看例?吧。,,,,(UserID,UserID);USING??指定了两个UserID,那么会变成?个。USING??指定了两个UserID,那么会变成?个,即使我们指定了前缀,?如USING(,),那么依旧会被优化成?个UserID。但是说实话,删除重复的ORDERBYKEY、LIMITBYKEY、USINGKEY,正常情况下很难出现,因为谁会没事故意将?个字段重复写两遍啊。当然字段多了倒是有可能发?,因为字段?多就可能忘记某个字段已经写过?遍了,但是字段少的情况下?乎不可能发?。所以这个ClickHouse的优化机制有点把?当傻?,?概感觉就是当你写了?个1+1=3,那么它能帮你改成1+1=2,然?查询?旦复杂,它就?法优化了。所以?切还需要我们来保证,当然后续ClickHouse的优化机制会变得越来越完善。标量替换这个主要体现在WITH?句上?,我们最开始介绍WITH?句的时候说过,WITH?句可以给?个普通的表达式赋值,也可以给?个查询赋值,但查询只能返回??数组。最终会将其作为?个标量,后续查询时直接?这个标量进?替换即可。这背后也是ClickHouse给我们做的优化,举个例?:WITH(SELECTsum(pressed_bytes))AStotal_bytesSELECTdatabase,(sum(pressed_bytes)/total_bytes)*,pressed_bytes进?sum