{"id":74737,"date":"2020-03-20T08:43:14","date_gmt":"2020-03-20T05:43:14","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/optimizacziya-strok-v-clickhouse-doklad-yandeksa"},"modified":"2020-03-20T08:43:14","modified_gmt":"2020-03-20T05:43:14","slug":"optimizacziya-strok-v-clickhouse-doklad-yandeksa","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/optimizacziya-strok-v-clickhouse-doklad-yandeksa","title":{"rendered":"Optimizimi i rreshtave n\u00eb ClickHouse. Raporti i Yandex.","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Baza analitike ClickHouse p\u00ebrpunon shum\u00eb lloje t\u00eb ndryshme rreshtash, duke konsumuar burime. P\u00ebr t\u00eb p\u00ebrshpejtuar funksionimin e sistemit, vazhdimisht shtohen optimizime t\u00eb reja. Zhvilluesi i ClickHouse, Nikolai Kochetov, flet p\u00ebr llojin e dh\u00ebnave string, p\u00ebrfshir\u00eb nj\u00eb tip t\u00eb ri, LowCardinality, dhe shpjegon se si mund t\u00eb p\u00ebrshpejtojm\u00eb pun\u00ebn me rreshtat. <\/p>\n

\r\n \"Luaj\r\n
<\/div>\r\n <\/div><\/center>
\n\u2014 S\u00eb pari, le t\u00eb kuptojm\u00eb si mund t\u00eb ruajm\u00eb rreshta.
\n<\/a><\/noindex>
\n\"Optimizimi
\n
\nNe kemi lloje t\u00eb dh\u00ebnash string. String \u00ebsht\u00eb i p\u00ebrshtatsh\u00ebm si parazgjedhje dhe duhet p\u00ebrdorur pothuajse gjithmon\u00eb. Ai ka nj\u00eb Overhead t\u00eb vog\u00ebl \u2014 9 byte p\u00ebr \u00e7do rresht. N\u00ebse duam q\u00eb madh\u00ebsia e rreshtave t\u00eb jet\u00eb e ngurt\u00eb dhe e njohur paraprakisht, at\u00ebher\u00eb \u00ebsht\u00eb m\u00eb mir\u00eb t\u00eb p\u00ebrdorim FixedString. N\u00eb t\u00eb mund t\u00eb caktojm\u00eb numrin e duhur t\u00eb byte-ve, q\u00eb \u00ebsht\u00eb i p\u00ebrshtatsh\u00ebm p\u00ebr t\u00eb dh\u00ebna si adresat IP ose funksionet hash. <\/p>\n

\"Optimizimi
\n
\nSigurisht, ndonj\u00ebher\u00eb di\u00e7ka mund t\u00eb ngadal\u00ebsoj\u00eb. Supozoni se po b\u00ebni nj\u00eb k\u00ebrkes\u00eb n\u00eb nj\u00eb tabel\u00eb. ClickHouse lexon nj\u00eb sasi t\u00eb madhe t\u00eb t\u00eb dh\u00ebnave, le t\u00eb themi, me shpejt\u00ebsi 100 GB\/s, megjithat\u00eb, numri i rreshtave t\u00eb p\u00ebrpunuar \u00ebsht\u00eb i vog\u00ebl. Ne kemi dy tabela q\u00eb ruajn\u00eb pothuajse t\u00eb nj\u00ebjtat t\u00eb dh\u00ebna. Nga tabela e dyt\u00eb, ClickHouse lexon t\u00eb dh\u00ebnat me nj\u00eb shpejt\u00ebsi m\u00eb t\u00eb madhe, por numri i rreshtave t\u00eb lexuar n\u00eb sekond\u00eb \u00ebsht\u00eb tre her\u00eb m\u00eb i ul\u00ebt. <\/p>\n

\"Optimizimi
\n
\nN\u00ebse shikojm\u00eb madh\u00ebsin\u00eb e t\u00eb dh\u00ebnave t\u00eb kompresuara, ajo do t\u00eb jet\u00eb pothuajse e barabart\u00eb. N\u00eb t\u00eb v\u00ebrtet\u00eb, t\u00eb dh\u00ebnat n\u00eb tabela jan\u00eb t\u00eb nj\u00ebjtat \u2014 miliardi i par\u00eb i numrave \u2014 vet\u00ebm q\u00eb n\u00eb kolon\u00ebn e par\u00eb ato jan\u00eb regjistruar si UInt64, nd\u00ebrsa n\u00eb t\u00eb dyt\u00ebn si String. P\u00ebr k\u00ebt\u00eb arsye, k\u00ebrkesa e dyt\u00eb lexon m\u00eb ngadal\u00eb t\u00eb dh\u00ebnat nga disku dhe i \u00e7kompreson ato. <\/p>\n

\"Optimizimi
\n
\nJa nj\u00eb shembull tjet\u00ebr. Supozoni se ekziston nj\u00eb grup i njohur paraprakisht rreshtash, i kufizuar n\u00eb nj\u00eb konstant\u00eb 1000 ose 10,000 dhe q\u00eb nuk ndryshon kurr\u00eb. P\u00ebr k\u00ebt\u00eb rast, lloji i t\u00eb dh\u00ebnave Enum na p\u00ebrshtatet, n\u00eb ClickHouse ka dy \u2014 Enum8 dhe Enum16. Duke ruajtur n\u00eb Enum, ne p\u00ebrpunojm\u00eb k\u00ebrkesat me shpejt\u00ebsi. <\/p>\n

N\u00eb ClickHouse ka optimizime p\u00ebr GROUP BY, IN, DISTINCT dhe optimizime p\u00ebr disa funksione, p\u00ebr shembull p\u00ebr krahasimin me nj\u00eb string konstant. Sigurisht, numrat n\u00eb string nuk konvertohen, p\u00ebrkundrazi, stringu konstant shnd\u00ebrrohet n\u00eb nj\u00eb vler\u00eb Enum. Pas k\u00ebsaj, gjith\u00e7ka krahasohet shpejt. <\/p>\n

Por ka dhe disavantazhe. Edhe n\u00ebse e dim\u00eb sakt\u00ebsisht grupin e rreshtave, ndonj\u00ebher\u00eb ai duhet t\u00eb zgjerohet. N\u00ebse mb arrives nj\u00eb rresht i ri, ne duhet t\u00eb b\u00ebjm\u00eb ALTER. <\/p>\n

\"Optimizimi
\n
\nALTER p\u00ebr Enum n\u00eb ClickHouse \u00ebsht\u00eb realizuar n\u00eb m\u00ebnyr\u00eb optimale. Ne nuk e shkruajm\u00eb t\u00eb dh\u00ebnat n\u00eb disk, por ALTER mund t\u00eb ngadal\u00ebsohen p\u00ebr shkak t\u00eb faktit se strukturat Enum ruhen n\u00eb skem\u00ebn e tabel\u00ebs vet\u00eb. Prandaj, duhet t\u00eb presim p\u00ebr k\u00ebrkesat p\u00ebr lexim nga tabela, p\u00ebr shembull. <\/p>\n

Shqet\u00ebsimi \u00ebsht\u00eb, a mund t\u00eb b\u00ebjm\u00eb m\u00eb mir\u00eb? Ndoshta, po. Mund t\u00eb ruajm\u00eb struktur\u00ebn Enum jo n\u00eb skem\u00ebn e tabel\u00ebs, por n\u00eb ZooKeeper. Megjithat\u00eb, mund t\u00eb lindin probleme q\u00eb lidhen me sinkronizimin. P\u00ebr shembull, nj\u00eb replik\u00eb m\u00eb merr t\u00eb dh\u00ebnat, nd\u00ebrsa nj\u00eb tjet\u00ebr jo, dhe n\u00ebse ajo ka nj\u00eb Enum t\u00eb vjet\u00ebr, di\u00e7ka do t\u00eb d\u00ebshtoj\u00eb. (N\u00eb ClickHouse, pothuajse kemi p\u00ebrfunduar k\u00ebrkesat ALTER me pa bllokim. Kur t\u2019i p\u00ebrfundojm\u00eb plot\u00ebsisht, nuk do t\u00eb duhet t\u00eb presim p\u00ebr k\u00ebrkesat p\u00ebr lexim.)<\/p>\n

\"Optimizimi
\n
\nP\u00ebr t\u00eb mos u marr\u00eb me ALTER Enum, mund t\u00eb p\u00ebrdorim fjalor\u00ebt e jasht\u00ebm t\u00eb ClickHouse. Dua t\u2019ju kujtoj se kjo \u00ebsht\u00eb nj\u00eb struktur\u00eb t\u00eb dh\u00ebnash key-value brenda ClickHouse, me ndihm\u00ebn e s\u00eb cil\u00ebs mund t\u00eb marrim t\u00eb dh\u00ebna nga burime t\u00eb jashtme, p\u00ebr shembull nga tabelat MySQL. <\/p>\n

N\u00eb fjalorin ClickHouse ne ruajm\u00eb shum\u00eb rreshta t\u00eb ndrysh\u00ebm, nd\u00ebrsa n\u00eb tabel\u00eb ruhen identifikuesit e tyre n\u00eb form\u00ebn e numrave. N\u00ebse na duhet t\u00eb marrim nj\u00eb rresht, ne th\u00ebrrasim funksionin dictGet dhe punojm\u00eb me t\u00eb. Pas k\u00ebsaj nuk duhet t\u00eb b\u00ebjm\u00eb ALTER. P\u00ebr t\u00eb shtuar di\u00e7ka n\u00eb Enum, ne e vendosim at\u00eb n\u00eb t\u00eb nj\u00ebjt\u00ebn tabel\u00eb MySQL. <\/p>\n

Por k\u00ebtu lindin probleme t\u00eb tjera. S\u00eb pari, sintaks\u00eb e pak\u00ebndshme. N\u00ebse duam t\u00eb marim nj\u00eb rresht, duhet t\u00eb th\u00ebrrasim dictGet. S\u00eb dyti, mungesa e optimizimeve t\u00eb caktuara. Krahasimi me nj\u00eb rresht konstant p\u00ebr fjalor\u00ebt nuk \u00ebsht\u00eb po aq i shpejt\u00eb. <\/p>\n

P\u00ebrve\u00e7 k\u00ebsaj, mund t\u00eb ket\u00eb probleme me p\u00ebrdit\u00ebsimin. D\u00ebgjojm\u00eb se k\u00ebrkuam nj\u00eb rresht n\u00eb fjalorin e memorizuar dhe nuk \u00ebsht\u00eb regjistruar n\u00eb memorie. N\u00eb k\u00ebt\u00eb rast, ne duhet t\u00eb presim derisa t\u00eb ngarkohen t\u00eb dh\u00ebnat nga burimi i jasht\u00ebm. <\/p>\n

\"Optimizimi
\n
\nDob\u00ebsia e p\u00ebrgjithshme e t\u00eb dy metodave \u00ebsht\u00eb se ne ruajm\u00eb t\u00eb gjith\u00eb \u00e7el\u00ebsat n\u00eb nj\u00eb vend dhe i sinkronizojm\u00eb. Pra, pse t\u00eb mos i ruajm\u00eb fjalor\u00ebt lokal? Pa sinkronizim \u2014 pa probleme. Mund t\u00eb ruajm\u00eb fjalorin lokal n\u00eb nj\u00eb bllok n\u00eb disk. K\u00ebshtu, b\u00ebjm\u00eb Insert, sh\u00ebnojm\u00eb fjalorin. N\u00ebse punojm\u00eb me t\u00eb dh\u00ebnat n\u00eb memorie, mund t\u00eb regjistrojm\u00eb fjalorin ose n\u00eb nj\u00eb bllok t\u00eb dh\u00ebnash, ose n\u00eb nj\u00eb cop\u00eb t\u00eb kolonn\u00ebs, ose n\u00eb ndonj\u00eb memorie cache p\u00ebr t\u00eb p\u00ebrshpejtuar llogaritjet.<\/p>\n

Kodimi i fjalor\u00ebve t\u00eb rreshtave <\/h3>\n

\nK\u00ebshtu arrit\u00ebm n\u00eb krijimin e nj\u00eb tipi t\u00eb ri t\u00eb t\u00eb dh\u00ebnave n\u00eb ClickHouse \u2014 LowCardinality. Ky \u00ebsht\u00eb nj\u00eb format ruajtjeje t\u00eb dh\u00ebnash: si shkruhen n\u00eb disk dhe si lexohen, si p\u00ebrfaq\u00ebsohen n\u00eb memorie dhe skema e p\u00ebrpunimit t\u00eb tyre. <\/p>\n

\"Optimizimi
\n
\nN\u00eb slajd ka dy kolonat. N\u00eb t\u00eb djatht\u00eb, rreshtat ruhen standardisht, n\u00eb llojin String. Duket se jan\u00eb disa modele telefonash celular\u00eb. N\u00eb t\u00eb majt\u00eb ka nj\u00eb kolon\u00eb t\u00eb ngjashme, por n\u00eb llojin LowCardinality. Ajo p\u00ebrb\u00ebhet nga nj\u00eb fjalor me shum\u00eb rreshta t\u00eb ndrysh\u00ebm (rreshtat nga kolona e djatht\u00eb) dhe nj\u00eb list\u00eb pozita (numrat e rreshtave). <\/p>\n

Me k\u00ebto dy struktura, mund t\u00eb rikonstruktojm\u00eb kolon\u00ebn origjinale. Ka gjithashtu nj\u00eb indeks t\u00eb kund\u00ebrt \u2014 nj\u00eb tabel\u00eb hash, q\u00eb ndihmon t\u00eb gjejm\u00eb pozitat n\u00eb fjalor n\u00ebp\u00ebrmjet rreshtit. Ajo \u00ebsht\u00eb e nevojshme p\u00ebr t\u00eb p\u00ebrshpejtuar disa pyetje. P\u00ebr shembull, n\u00ebse duam t\u00eb krahasojm\u00eb, t\u00eb k\u00ebrkojm\u00eb nj\u00eb rresht n\u00eb kolon\u00ebn ton\u00eb ose t'i bashkojm\u00eb bashk\u00eb. <\/p>\n

LowCardinality \u00ebsht\u00eb nj\u00eb tip parametrik t\u00eb dh\u00ebnash. Ai mund t\u00eb jet\u00eb ose nj\u00eb num\u00ebr, ose di\u00e7ka q\u00eb ruhet si num\u00ebr, ose nj\u00eb rresht, ose Nullable prej tyre. <\/p>\n

\"Optimizimi
\n
\nKarakteristika e LowCardinality \u00ebsht\u00eb se ai mund t\u00eb ruhet p\u00ebr disa funksione. N\u00eb slajd \u00ebsht\u00eb nj\u00eb shembull pyetjeje. N\u00eb rreshtin e par\u00eb krijova nj\u00eb kolon\u00eb me tipin LowCardinality nga String, e quajta S. M\u00eb pas e pyeta emrin e saj \u2014 ClickHouse tha se \u00ebsht\u00eb LowCardinality nga String. E gjitha \u00ebsht\u00eb e sakt\u00eb.<\/p>\n

Rreshti i tret\u00eb \u00ebsht\u00eb pothuajse i nj\u00ebjt\u00eb, vet\u00ebm se kemi thirrur funksionin length. N\u00eb ClickHouse funksioni length kthen nj\u00eb tip t\u00eb dh\u00ebnash UInt64. Por tani kemi LowCardinality nga UInt64. \u00c7far\u00eb kuptimi ka kjo?<\/p>\n

\"Optimizimi
\n
\nN\u00eb fjalor ruheshin emrat e telefonave celular\u00eb, ne aplikuam funksionin length. Tani kemi nj\u00eb fjalor t\u00eb ngjash\u00ebm, q\u00eb p\u00ebrb\u00ebhet vet\u00ebm nga numra \u2014 jan\u00eb gjat\u00ebsit\u00eb e rreshtave. Kolona me pozitat nuk u ndryshua. N\u00eb fund, p\u00ebrpunuam m\u00eb pak t\u00eb dh\u00ebna, kursyem koh\u00eb k\u00ebrkese. <\/p>\n

Mund t\u00eb ket\u00eb edhe optimizime t\u00eb tjera, p\u00ebr shembull shtimin e nj\u00eb cacher t\u00eb thjesht\u00eb. Kur llogarisim vler\u00ebn e funksionit, mund ta mbajm\u00eb mend at\u00eb dhe t\u00eb formojm\u00eb nj\u00eb t\u00eb till\u00eb, pa e llogaritur p\u00ebrs\u00ebri. <\/p>\n

Gjithashtu mund t\u00eb b\u00ebhet optimizimi GROUP BY, sepse kolona jon\u00eb me fjalor \u00ebsht\u00eb pjes\u00ebrisht e agreguar \u2014 mund t\u00eb llogaritim m\u00eb shpejt vlerat e funksioneve hash dhe p\u00ebraf\u00ebrsisht t\u00eb gjejm\u00eb bucket-in ku t\u00eb vendosim rreshtin e ardhsh\u00ebm. P\u00ebr m\u00eb tep\u00ebr, mund t\u00eb specializojm\u00eb disa funksione agregate, p\u00ebr shembull uniq, sepse n\u00eb t\u00eb mund t\u00eb d\u00ebrgohet vet\u00ebm fjalori, nd\u00ebrsa pozitat t\u00eb mbeten t\u00eb paprekura \u2014 k\u00ebshtu q\u00eb gjith\u00e7ka do t\u00eb funksionoj\u00eb m\u00eb shpejt. Dy optimizimet e para i kemi shtuar tashm\u00eb n\u00eb ClickHouse.<\/p>\n

\"Optimizimi
\n
\n\u00c7far\u00eb do t\u00eb ndodhte n\u00ebse krijojm\u00eb nj\u00eb kolon\u00eb me llojin ton\u00eb t\u00eb t\u00eb dh\u00ebnave dhe vendosim shum\u00eb vargje t\u00eb k\u00ebqija? A do t\u00eb mbushet memorja jon\u00eb? Jo, p\u00ebr k\u00ebt\u00eb ClickHouse ka dy cil\u00ebsime t\u00eb ve\u00e7anta. E para \u00ebsht\u00eb low_cardinality_max_dictionary_size. Kjo \u00ebsht\u00eb masa maksimale e fjalorit q\u00eb mund t\u00eb regjistrohet n\u00eb disk. Vendosja ndodh si\u00e7 vijon: kur ne vendosim t\u00eb dh\u00ebna, na vjen nj\u00eb rrjedh\u00eb vargjesh, nga t\u00eb cilat ne formojm\u00eb nj\u00eb fjalor t\u00eb madh t\u00eb p\u00ebrbashk\u00ebt. N\u00ebse fjalori b\u00ebhet m\u00eb i madh se vlera e cil\u00ebsimit, ne regjistrojm\u00eb fjalorin aktual n\u00eb disk, nd\u00ebrsa vargjet e tjera i vendosim diku 'n\u00eb an\u00eb', pran\u00eb indekseve. Si rezultat, ne kurr\u00eb nuk e llogarisim nj\u00eb fjalor t\u00eb madh dhe nuk kemi probleme me memorjen. <\/p>\n

Cil\u00ebsimi i dyt\u00eb quhet low_cardinality_use_single_dictionary_for_part. Imagjinoni q\u00eb n\u00eb skem\u00ebn e kaluar, kur ne vendosim t\u00eb dh\u00ebna, fjalori yn\u00eb u mbush dhe ne e regjistruam at\u00eb n\u00eb disk. Shfaqet pyetja, pse tani t\u00eb mos formojm\u00eb nj\u00eb fjalor tjet\u00ebr t\u00eb till\u00eb? <\/p>\n

Kur ai mbushet, ne p\u00ebrs\u00ebri do ta regjistrojm\u00eb n\u00eb disk dhe do t\u00eb fillojm\u00eb t\u00eb formojm\u00eb t\u00eb tretin. Ky cil\u00ebsim p\u00ebrjashton pik\u00ebrisht nj\u00eb mund\u00ebsi t\u00eb till\u00eb si parazgjedhje. <\/p>\n

N\u00eb t\u00eb v\u00ebrtet\u00eb, shum\u00eb fjalor\u00eb mund t\u00eb jen\u00eb t\u00eb dobish\u00ebm, n\u00ebse ne d\u00ebshirojm\u00eb t\u00eb vendosim nj\u00eb sasi t\u00eb caktuar vargjesh, por rast\u00ebsisht kemi vendosur 't\u00eb pap\u00ebrshtatshme'. Le t\u00eb themi, fillimisht vendos\u00ebm vargje t\u00eb k\u00ebqija dhe m\u00eb pas vendos\u00ebm t\u00eb mira. At\u00ebher\u00eb fjalori do t\u00eb ndahen n\u00eb shum\u00eb fjalor\u00eb t\u00eb vegj\u00ebl. Nj\u00eb pjes\u00eb e tyre do t\u00eb ket\u00eb 't\u00eb pap\u00ebrshtatshme', por t\u00eb fundit do t\u00eb jen\u00eb me vargje t\u00eb mira. Dhe n\u00ebse ne e lexojm\u00eb, le t\u00eb themi, vet\u00ebm granulat e fundit, gjith\u00e7ka do t\u00eb funksionoj\u00eb shpejt. <\/p>\n

\"Optimizimi
\n
\nPara se t\u00eb flasim p\u00ebr avantazhet e LowCardinality, do t\u00eb thosha se v\u00ebshtir\u00eb se do t\u00eb arrijm\u00eb t\u00eb ulim t\u00eb dh\u00ebnat n\u00eb disk (ndon\u00ebse kjo mund t\u00eb ndodh\u00eb), sepse ClickHouse e kompreson t\u00eb dh\u00ebnat. Ka nj\u00eb variant parazgjedhje \u2014 LZ4. Gjithashtu, mund t\u00eb b\u00ebjm\u00eb kompresim me ndihm\u00ebn e ZSTD. Por t\u00eb dy algoritm\u00ebt tashm\u00eb implementojn\u00eb kompresimin e fjalorit, prandaj fjalori yn\u00eb i jasht\u00ebm ClickHouse nuk do t\u00eb ndihmoj\u00eb shum\u00eb. <\/p>\n

P\u00ebr t\u00eb mos qen\u00eb i paqart\u00eb, kam marr\u00eb disa t\u00eb dh\u00ebna nga metrika \u2014 String, LowCardinality(String) dhe Enum \u2014 dhe i kam ruajtur ato n\u00eb lloje t\u00eb ndryshme t\u00eb t\u00eb dh\u00ebnave. K\u00ebshtu kemi krijuar tre kolona, ku jan\u00eb regjistruar nj\u00eb miliard rreshtash. N\u00eb kolon\u00ebn e par\u00eb, CodePage, ka vet\u00ebm 62 vlera. Dhe \u00ebsht\u00eb e qart\u00eb se n\u00eb LowCardinality(String) ato jan\u00eb kompresuar m\u00eb mir\u00eb. String \u00ebsht\u00eb pak m\u00eb keq, por kjo ndoshta \u00ebsht\u00eb p\u00ebr shkak se vargjet jan\u00eb t\u00eb shkurtra, ne ruajm\u00eb gjat\u00ebsi t\u00eb tyre, dhe ato z\u00ebn\u00eb shum\u00eb hap\u00ebsir\u00eb, kompresohen keq. <\/p>\n

N\u00ebse marim PhoneModel, ka 48 mij\u00eb \u2014 tashm\u00eb m\u00eb shum\u00eb, dhe dallimet midis String dhe LowCardinality(String) jan\u00eb pothuajse t\u00eb padukshme. P\u00ebr URL ne gjithashtu kursyem vet\u00ebm 2 GB \u2014 mendoj se nuk ia vlen t\u00eb mb\u00ebshtetemi n\u00eb k\u00ebt\u00eb. <\/p>\n

Vler\u00ebsimi i shpejt\u00ebsis\u00eb s\u00eb pun\u00ebs<\/h3>\n

\n\"Optimizimi
\nLidhja nga slidi<\/a><\/noindex><\/b><\/p>\n

Tani le ta vler\u00ebsojm\u00eb shpejt\u00ebsin\u00eb e pun\u00ebs. P\u00ebr ta vler\u00ebsuar, p\u00ebrdora nj\u00eb dataset me p\u00ebrshkrimin e udh\u00ebtimeve me taksi n\u00eb Nju Jork. Ai \u00ebsht\u00eb n\u00eb dispozicion<\/a><\/noindex> ndodhet n\u00eb GitHub. Pjesa ka pak m\u00eb shum\u00eb se nj\u00eb miliard udh\u00ebtimesh. Atje shfaqet lokacioni, koha e fillimit dhe p\u00ebrfundimit t\u00eb udh\u00ebtimit, m\u00ebnyra e pages\u00ebs, numri i pasagjer\u00ebve dhe madje edhe lloji i taksis\u00eb \u2014 e gjelb\u00ebr, e verdh\u00eb dhe Uber. <\/p>\n

\"Optimizimi
\n
\nK\u00ebrkesa e par\u00eb e b\u00ebra ishte mjaft e thjesht\u00eb \u2014 pyeta se ku shpesh porositen taksit. P\u00ebr k\u00ebt\u00eb nevojitet t\u00eb marrim lokacionin nga ku u b\u00eb porosia, t\u00eb b\u00ebjm\u00eb GROUP BY dhe t\u00eb llogarisim funksionin count. Ja \u00e7far\u00eb nxjerr ClickHouse. <\/p>\n

\"Optimizimi
\n
\nP\u00ebr t\u00eb matur shpejt\u00ebsin\u00eb e p\u00ebrpunimit t\u00eb k\u00ebrkes\u00ebs, krijova tri tabela me t\u00eb dh\u00ebna t\u00eb nj\u00ebjta, por p\u00ebrdora p\u00ebr lokacionin ton\u00eb fillestar tri lloje t\u00eb ndryshme t\u00eb t\u00eb dh\u00ebnave \u2014 String, LowCardinality dhe Enum. LowCardinality dhe Enum rezultuan pes\u00eb her\u00eb m\u00eb t\u00eb shpejt\u00eb se String. Enum \u00ebsht\u00eb m\u00eb i shpejt\u00eb sepse punon me numra. LowCardinality \u2014 p\u00ebr shkak se \u00ebsht\u00eb realizuar optimizimi GROUP BY. <\/p>\n

\"Optimizimi
\n
\nTani le t\u00eb complicojm\u00eb k\u00ebrkes\u00ebn \u2014 t\u00eb pyesim se ku ndodhet parku m\u00eb i njohur n\u00eb Nju Jork. P\u00ebrs\u00ebri do ta masim k\u00ebt\u00eb sipas asaj se ku shpesh porositen taksit, por k\u00ebt\u00eb her\u00eb do filtrojm\u00eb vet\u00ebm ato lokacione ku ka fjal\u00ebn \"park\". gjithashtu do t\u00eb shtojm\u00eb funksionin like. <\/p>\n

\"Optimizimi
\n
\nShikojm\u00eb koh\u00ebn \u2014 shohim se Enum papritur filloi t\u00eb ngadal\u00ebsohet. Dhe po funksionon madje m\u00eb ngadal\u00eb se tipi standard i t\u00eb dh\u00ebnave String. Kjo ndodh sepse funksioni like nuk \u00ebsht\u00eb aspak i optimizuar p\u00ebr Enum. Na duhet t\u00eb konvertojm\u00eb vargjet tona nga Enum n\u00eb vargje normale \u2014 ne po b\u00ebjm\u00eb m\u00eb shum\u00eb pun\u00eb. LowCardinality(String) gjithashtu nuk \u00ebsht\u00eb i optimizuar p\u00ebr default, por atje like punon mbi fjalorin, prandaj k\u00ebrkesa shpejtohet n\u00eb krahasim me String. <\/p>\n

Kur punojm\u00eb me Enum ka nj\u00eb problem m\u00eb t\u00eb madh. N\u00ebse duam ta optimizojm\u00eb, duhet ta b\u00ebjm\u00eb k\u00ebt\u00eb n\u00eb \u00e7do vend t\u00eb kodit. Le t\u00eb supozojm\u00eb se kemi shkruar nj\u00eb funksion t\u00eb ri \u2014 patjet\u00ebr duhet t\u00eb mendojm\u00eb p\u00ebr optimizimin p\u00ebr Enum. Nd\u00ebrsa n\u00eb LowCardinality gjith\u00e7ka \u00ebsht\u00eb optimizuar me default.<\/p>\n

\"Optimizimi
\n
\nT\u00eb shohim k\u00ebrkes\u00ebn e fundit, m\u00eb artificiale. Thjesht do t\u00eb llogarisim funksionin hash nga vendndodhja jon\u00eb. Funksioni hash \u00ebsht\u00eb nj\u00eb k\u00ebrkes\u00eb mjaft e ngadalshme, llogaritet me koh\u00eb, prandaj gjith\u00e7ka do t\u00eb ngadalsohet rreth tri her\u00eb.<\/p>\n

\"Optimizimi
\n
\nLowCardinality ende funksionon m\u00eb shpejt, edhe pse k\u00ebtu nuk ka filtrimin. Kjo ndodh sepse funksionet tona punojn\u00eb vet\u00ebm mbi fjalorin. Funksioni p\u00ebr llogaritjen e hash-it ka nj\u00eb argument \u2014 ai mund t\u00eb p\u00ebrpunoj\u00eb m\u00eb pak t\u00eb dh\u00ebna dhe gjithashtu mund t\u00eb kthej\u00eb LowCardinality. <\/p>\n

\"Optimizimi
\n
\nPlani yn\u00eb global \u00ebsht\u00eb t\u00eb arrijm\u00eb shpejt\u00ebsin\u00eb e funksionimit jo m\u00eb t\u00eb ul\u00ebt se String n\u00eb \u00e7do rast dhe t\u00eb ruajm\u00eb p\u00ebrshpejtimet. Dhe, ndoshta, ndonj\u00ebher\u00eb do ta z\u00ebvend\u00ebsojm\u00eb String me LowCardinality, ju do t\u00eb p\u00ebrdit\u00ebsoni ClickHouse dhe gjith\u00e7ka do t\u00eb funksionoj\u00eb pak m\u00eb shpejt.
\n
Burimi: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"

\u0410\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0430\u044f \u0421\u0423\u0411\u0414 ClickHouse \u043e\u0431\u0440\u0430\u0431\u0430\u0442\u044b\u0432\u0430\u0435\u0442 \u043c\u043d\u043e\u0436\u0435\u0441\u0442\u0432\u043e \u0440\u0430\u0437\u043d\u044b\u0445 \u0441\u0442\u0440\u043e\u043a, \u043f\u043e\u0442\u0440\u0435\u0431\u043b\u044f\u044f \u0440\u0435\u0441\u0443\u0440\u0441\u044b. \u0414\u043b\u044f \u0443\u0441\u043a\u043e\u0440\u0435\u043d\u0438\u044f \u0440\u0430\u0431\u043e\u0442\u044b \u0441\u0438\u0441\u0442\u0435\u043c\u044b \u043f\u043e\u0441\u0442\u043e\u044f\u043d\u043d\u043e \u0434\u043e\u0431\u0430\u0432\u043b\u044f\u044e\u0442\u0441\u044f \u043d\u043e\u0432\u044b\u0435 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438. \u0420\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a ClickHouse \u041d\u0438\u043a\u043e\u043b\u0430\u0439 \u041a\u043e\u0447\u0435\u0442\u043e\u0432 \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u044b\u0432\u0430\u0435\u0442 \u043e \u0441\u0442\u0440\u043e\u043a\u043e\u0432\u043e\u043c \u0442\u0438\u043f\u0435 \u0434\u0430\u043d\u043d\u044b\u0445, \u0432 \u0442\u043e\u043c \u0447\u0438\u0441\u043b\u0435 \u043e \u043d\u043e\u0432\u043e\u043c \u0442\u0438\u043f\u0435, LowCardinality, \u0438 \u043e\u0431\u044a\u044f\u0441\u043d\u044f\u0435\u0442, \u043a\u0430\u043a \u043c\u043e\u0436\u043d\u043e \u0443\u0441\u043a\u043e\u0440\u0438\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u0443 \u0441\u043e \u0441\u0442\u0440\u043e\u043a\u0430\u043c\u0438. \u2014 \u0421\u043d\u0430\u0447\u0430\u043b\u0430 \u0434\u0430\u0432\u0430\u0439\u0442\u0435 \u0440\u0430\u0437\u0431\u0435\u0440\u0435\u043c\u0441\u044f, \u043a\u0430\u043a \u043c\u043e\u0436\u043d\u043e \u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0441\u0442\u0440\u043e\u043a\u0438. \u0423 \u043d\u0430\u0441 \u0435\u0441\u0442\u044c \u0441\u0442\u0440\u043e\u043a\u043e\u0432\u044b\u0435 \u0442\u0438\u043f\u044b \u0434\u0430\u043d\u043d\u044b\u0445. […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":74738,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-74737","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Optimizimi i rrjeshtave n\u00eb ClickHouse. Prezantimi i Yandex | ProHoster","description":"Sistemi analitik i DB-s\u00eb ClickHouse p\u00ebrpunon shum\u00eb rrjeshta t\u00eb ndryshme, duke p\u00ebrdorur burime. P\u00ebr t\u00eb p\u00ebrshpejtuar pun\u00ebn e sistemit, vazhdimisht shtohen optimizime t\u00eb reja.","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/optimizacziya-strok-v-clickhouse-doklad-yandeksa","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sq_AL","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044f \u0441\u0442\u0440\u043e\u043a \u0432 ClickHouse. \u0414\u043e\u043a\u043b\u0430\u0434 \u042f\u043d\u0434\u0435\u043a\u0441\u0430 | ProHoster","og:description":"\u0410\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0430\u044f \u0421\u0423\u0411\u0414 ClickHouse \u043e\u0431\u0440\u0430\u0431\u0430\u0442\u044b\u0432\u0430\u0435\u0442 \u043c\u043d\u043e\u0436\u0435\u0441\u0442\u0432\u043e \u0440\u0430\u0437\u043d\u044b\u0445 \u0441\u0442\u0440\u043e\u043a, \u043f\u043e\u0442\u0440\u0435\u0431\u043b\u044f\u044f \u0440\u0435\u0441\u0443\u0440\u0441\u044b. \u0414\u043b\u044f \u0443\u0441\u043a\u043e\u0440\u0435\u043d\u0438\u044f \u0440\u0430\u0431\u043e\u0442\u044b \u0441\u0438\u0441\u0442\u0435\u043c\u044b \u043f\u043e\u0441\u0442\u043e\u044f\u043d\u043d\u043e \u0434\u043e\u0431\u0430\u0432\u043b\u044f\u044e\u0442\u0441\u044f \u043d\u043e\u0432\u044b\u0435 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438.","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/optimizacziya-strok-v-clickhouse-doklad-yandeksa","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-03-20T05:43:14+00:00","article:modified_time":"2020-03-20T05:43:14+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"74737","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 17:23:10","updated":"2022-09-27 14:48:44","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/74737","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/comments?post=74737"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/74737\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media\/74738"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=74737"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=74737"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=74737"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}