{"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":"

Sistemi analitik i DB ClickHouse p\u00ebrpunon shum\u00eb rreshta t\u00eb ndrysh\u00ebm, duke konsumuar burime. P\u00ebr t\u00eb shpejtuar funksionimin e sistemit, vazhdimisht b\u00ebhen optimizime t\u00eb reja. Zhvilluesi i ClickHouse, Nikolai Kochetov, tregon p\u00ebr tipin e t\u00eb dh\u00ebnave me rreshta, p\u00ebrfshir\u00eb tipin e ri, LowCardinality, dhe shpjegon se si mund t\u00eb p\u00ebrshpejtohet puna me rreshta. <\/p>\n

\r\n \"Luaj\r\n
<\/div>\r\n <\/div><\/center>
\n\u2014 S\u00eb pari, le ta kuptojm\u00eb si mund t\u00eb ruhen rreshtat.
\n<\/a><\/noindex>
\n\"Optimizimi
\n
\nNe kemi tipe t\u00eb dh\u00ebnash me rreshta. String \u00ebsht\u00eb i p\u00ebrshtatsh\u00ebm si opsion default dhe duhet p\u00ebrdorur pothuajse gjithmon\u00eb. Ka nj\u00eb Overhead t\u00eb vog\u00ebl \u2014 9 bytes p\u00ebr nj\u00eb rresht. N\u00ebse duam q\u00eb madh\u00ebsia e rreshtave t\u00eb jet\u00eb fikse dhe e njohur paraprakisht, \u00ebsht\u00eb m\u00eb mir\u00eb t\u00eb p\u00ebrdorim FixedString. N\u00eb t\u00eb mund t\u00eb caktosh numrin e duhur t\u00eb bytes, \u00ebsht\u00eb i p\u00ebrshtatsh\u00ebm p\u00ebr t\u00eb dh\u00ebnat si adresat IP ose funksionet hash. <\/p>\n

\"Optimizimi
\n
\nSigurisht, ndonj\u00ebher\u00eb di\u00e7ka ngadal\u00ebson. Le t\u00eb themi se b\u00ebni nj\u00eb k\u00ebrkes\u00eb n\u00eb nj\u00eb tabel\u00eb. ClickHouse lexon nj\u00eb sasi t\u00eb madhe t\u00eb dh\u00ebnash, le t\u00eb themi me nj\u00eb shpejt\u00ebsi prej 100 GB\/s, nd\u00ebrsa rreshtat p\u00ebrpunohen pak. Ne kemi dy tabela q\u00eb ruajn\u00eb pothuajse t\u00eb dh\u00ebna t\u00eb nj\u00ebjta. Nga tabela e dyt\u00eb, ClickHouse lexon t\u00eb dh\u00ebna me nj\u00eb shpejt\u00ebsi m\u00eb t\u00eb madhe, por numri i rreshtave t\u00eb lexuar p\u00ebr sekond\u00eb \u00ebsht\u00eb tri her\u00eb m\u00eb i vog\u00ebl. <\/p>\n

\"Optimizimi
\n
\nN\u00ebse shikojm\u00eb madh\u00ebsin\u00eb e t\u00eb dh\u00ebnave t\u00eb kompresuara, ajo do t\u00eb rezultoj\u00eb pothuajse e barabart\u00eb. N\u00eb t\u00eb v\u00ebrtet\u00eb, n\u00eb tabela jan\u00eb t\u00eb shkruara t\u00eb nj\u00ebjtat t\u00eb dh\u00ebna \u2014 miliardi i par\u00eb i numrave \u2014 vet\u00ebm se n\u00eb kolon\u00ebn e par\u00eb jan\u00eb t\u00eb shkruara si UInt64, nd\u00ebrsa n\u00eb t\u00eb dyt\u00ebn \u2014 si String. P\u00ebr k\u00ebt\u00eb arsye, k\u00ebrkesa e dyt\u00eb ka nevoj\u00eb p\u00ebr m\u00eb shum\u00eb koh\u00eb p\u00ebr t\u00eb lexuar t\u00eb dh\u00ebnat nga disku dhe p\u00ebr t'i zbuluar ato. <\/p>\n

\"Optimizimi
\n
\nJa nj\u00eb shembull tjet\u00ebr. Le t\u00eb supozojm\u00eb se ka nj\u00eb grup t\u00eb njohur paraprakisht t\u00eb vargjeve, i cili \u00ebsht\u00eb i kufizuar me nj\u00eb konstant\u00eb 1000 ose 10,000 dhe gati asnj\u00ebher\u00eb nuk ndryshon. P\u00ebr k\u00ebt\u00eb rast na p\u00ebrshtatet tipin e t\u00eb dh\u00ebnave Enum, n\u00eb ClickHouse ka dy \u2014 Enum8 dhe Enum16. P\u00ebr shkak t\u00eb ruajtjes n\u00eb Enum, ne i p\u00ebrpunojm\u00eb k\u00ebrkesat shpejt. <\/p>\n

N\u00eb ClickHouse ka p\u00ebrshpejtimet 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, por, p\u00ebrkundrazi, stringu konstant reduktohet n\u00eb vler\u00ebn Enum. Pas k\u00ebsaj, gjith\u00e7ka krahasohet shpejt. <\/p>\n

Por ka edhe disavantazhe. Edhe n\u00ebse e dim\u00eb sakt\u00ebsisht grupin e vargjeve, ndonj\u00ebher\u00eb ai duhet t\u00eb plot\u00ebsohet. Kur vjen nj\u00eb varg i ri \u2014 duhet t\u00eb b\u00ebjm\u00eb ALTER. <\/p>\n

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

Ngrihet pyetja, a mund t\u00eb b\u00ebhet m\u00eb mir\u00eb? Mbase, po. Mund t\u00eb ruhen strukturat Enum jo n\u00eb skem\u00ebn e tabel\u00ebs, por n\u00eb ZooKeeper. Megjithat\u00eb, mund t\u00eb lindin probleme t\u00eb lidhura me sinkronizimin. P\u00ebr shembull, nj\u00eb replik\u00eb merr t\u00eb dh\u00ebna, nj\u00eb tjet\u00ebr jo, dhe n\u00ebse ajo ka nj\u00eb Enum t\u00eb vjet\u00ebr, di\u00e7ka mund t\u00eb prish\u00eb. (N\u00eb ClickHouse, ne jemi gati me k\u00ebrkesat ALTER jo bllokuese. Kur t'i p\u00ebrfundojm\u00eb ato plot\u00ebsisht, nuk do t\u00eb nevojitet 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. T\u00eb kujtoj se kjo \u00ebsht\u00eb nj\u00eb struktur\u00eb t\u00eb dh\u00ebnash key-value brenda ClickHouse, e cila mund\u00ebson marrjen e t\u00eb dh\u00ebnave nga burime t\u00eb jashtme, p\u00ebr shembull nga tabelat MySQL. <\/p>\n

N\u00eb fjalorin ClickHouse mbajm\u00eb shum\u00eb rreshta t\u00eb ndrysh\u00ebm, nd\u00ebrsa n\u00eb tabel\u00eb \u2013 identifikuesit e tyre si numra. N\u00ebse na nevojitet nj\u00eb rresht, 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, e shkruajm\u00eb 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, sintaksa e pak\u00ebndshme. N\u00ebse duam t\u00eb marrim nj\u00eb rresht, duhet t\u00eb th\u00ebrrasim dictGet. S\u00eb dyti, mungesa e disa optimizimeve. Krahasimi me nj\u00eb rresht konstant p\u00ebr fjalor\u00ebt nuk b\u00ebhet kaq shpejt. <\/p>\n

Problemet me p\u00ebrdit\u00ebsimin gjithashtu mund t\u00eb ndodhin. Supozoni se k\u00ebrkuam nj\u00eb rresht n\u00eb fjalorin me k\u00ebshill, por ai nuk \u00ebsht\u00eb ngarkuar n\u00eb cache. At\u00ebher\u00eb duhet t\u00eb presim derisa t\u00eb ngarkohen t\u00eb dh\u00ebnat nga burimi i jasht\u00ebm. <\/p>\n

\"Optimizimi
\n
\nNj\u00eb mang\u00ebsi e p\u00ebrgjithshme e t\u00eb dy metodave \u00ebsht\u00eb se ne mbajm\u00eb t\u00eb gjitha \u00e7el\u00ebsat n\u00eb nj\u00eb vend dhe i sinkronizojm\u00eb ato. Pse t\u00eb mos i mbajm\u00eb fjalor\u00ebt n\u00eb m\u00ebnyr\u00eb lokale? Pa sinkronizim \u2013 pa probleme. Mund t\u00eb ruajm\u00eb fjalorin lokalisht n\u00eb nj\u00eb cop\u00eb n\u00eb disk. K\u00ebshtu, b\u00ebjm\u00eb nj\u00eb Insert, regjistrojm\u00eb fjalorin. N\u00ebse punojm\u00eb me t\u00eb dh\u00ebnat n\u00eb memorie, mund t\u00eb regjistrojm\u00eb fjalorin ose n\u00eb bllokun e t\u00eb dh\u00ebnave, ose n\u00eb nj\u00eb cop\u00eb kolone, ose n\u00eb ndonj\u00eb cache p\u00ebr t\u00eb p\u00ebrshpejtuar llogaritjet.<\/p>\n

Kodimi i fjal\u00ebve t\u00eb vargjeve <\/h3>\n

\nK\u00ebshtu arrit\u00ebm n\u00eb krijimin e nj\u00eb lloji 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 paraqiten n\u00eb memorie dhe skema e p\u00ebrpunimit t\u00eb tyre. <\/p>\n

\"Optimizimi
\n
\nN\u00eb slajd ka dy kolona. Nga e djathta, rreshtat ruhen standardisht, n\u00eb tipin String. Shikohet se jan\u00eb disa modele telefonash celular\u00eb. Nga e majta \u00ebsht\u00eb nj\u00eb kolon\u00eb e ngjashme, vet\u00ebm se n\u00eb tipin 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 (numra rreshtash). <\/p>\n

Me ndihm\u00ebn e k\u00ebtyre dy strukturave mund t\u00eb rikuperohet kolona 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 p\u00ebrmes rreshtit. Kjo \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 apo t'i bashkojm\u00eb ata. <\/p>\n

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

\"Optimizimi
\n
\nKarakteristika e LowCardinality \u00ebsht\u00eb se ai mund t\u00eb ruhet p\u00ebr disa funksione. N\u00eb slajd shihet nj\u00eb shembull pyetje. N\u00eb rreshtin e par\u00eb kam krijuar nj\u00eb kolon\u00eb t\u00eb tipit LowCardinality nga String, e quajta S. M\u00eb pas e pyeta emrin e saj \u2014 ClickHouse tha se kjo \u00ebsht\u00eb LowCardinality nga String. E p\u00ebrshtatshme.<\/p>\n

Rreshti i tret\u00eb \u00ebsht\u00eb pothuajse i nj\u00ebjt\u00eb, vet\u00ebm se ne thirr\u00ebm funksionin length. N\u00eb ClickHouse funksioni length kthen tipin e t\u00eb dh\u00ebnave UInt64. Por tani kemi LowCardinality nga UInt64. \u00c7far\u00eb ka kuptim?<\/p>\n

\"Optimizimi
\n
\nN\u00eb fjalor ruheshin emrat e telefon\u00ebve mobil\u00eb, ne aplikuam funksionin length. Tani kemi nj\u00eb fjalor t\u00eb ngjash\u00ebm, q\u00eb p\u00ebrb\u00ebhet vet\u00ebm nga numra, \u2014 k\u00ebto jan\u00eb gjat\u00ebsit\u00eb e vargjeve. Kolona me pozita nuk ka ndryshuar. Si rezultat, ne p\u00ebrpunuam m\u00eb pak t\u00eb dh\u00ebna, kursyer n\u00eb koh\u00ebn e pyetjes. <\/p>\n

Mund t\u00eb jen\u00eb edhe optimizime t\u00eb tjera, p.sh., shtimi i nj\u00eb cache t\u00eb thjesht\u00eb. Kur llogaritet vlera e funksionit, mund ta mbajm\u00eb mend at\u00eb dhe t\u00eb formojm\u00eb nj\u00eb t\u00eb ngjash\u00ebm, pa e llogaritur p\u00ebrs\u00ebri. <\/p>\n

Optimizimi i GROUP BY gjithashtu mund t\u00eb b\u00ebhet, sepse kolona jon\u00eb me fjalor \u00ebsht\u00eb tashm\u00eb pjes\u00ebrisht e agreguar \u2014 mund t\u00eb llogarisim shpejt vlerat e funksioneve t\u00eb hash-it dhe t\u00eb gjejm\u00eb af\u00ebrsisht bucket-in ku t\u00eb vendosim rreshtin e ardhsh\u00ebm. Gjithashtu mund t\u00eb specializojm\u00eb disa funksione agreguese, si uniq, sepse n\u00eb t\u00eb mund t\u00eb d\u00ebrgojm\u00eb vet\u00ebm fjalorin dhe t\u00eb l\u00ebm\u00eb pozitat t\u00eb paprekura \u2014 k\u00ebshtu 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 ndodh n\u00ebse krijojm\u00eb nj\u00eb kolon\u00eb me llojin ton\u00eb t\u00eb dh\u00ebnash dhe vendosim shum\u00eb rreshta t\u00eb k\u00ebqinj n\u00eb t\u00eb? A do t\u00eb mbushet memoria jon\u00eb? Jo, p\u00ebr k\u00ebt\u00eb ClickHouse ka dy cil\u00ebsime speciale. E para \u00ebsht\u00eb low_cardinality_max_dictionary_size. Ky \u00ebsht\u00eb madh\u00ebsia maksimale e fjalorit q\u00eb mund t\u00eb shkruhet n\u00eb disk. Vendosja ndodh k\u00ebshtu: kur vendosim t\u00eb dh\u00ebna, na vjen nj\u00eb rrjedh\u00eb rreshtash, nga t\u00eb cilat 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 e regjistruam fjalorin aktual n\u00eb disk, nd\u00ebrsa rreshtat e tjer\u00eb \u2014 diku \"n\u00eb an\u00eb\", pran\u00eb indekseve. Si rezultat, ne kurr\u00eb nuk do t\u00eb rikonfigurojm\u00eb nj\u00eb fjalor t\u00eb madh dhe nuk do t\u00eb kemi probleme me memorien. <\/p>\n

Konfigurimi i dyt\u00eb quhet low_cardinality_use_single_dictionary_for_part. Imagjino q\u00eb n\u00eb skem\u00ebn e m\u00ebparshme, kur ne po insertonim t\u00eb dh\u00ebna, fjalori yn\u00eb u mbush dhe ne e regjistruam at\u00eb n\u00eb disk. Ngrihet pyetja, pse t\u00eb mos formojm\u00eb nj\u00eb tjet\u00ebr fjalor t\u00eb till\u00eb? <\/p>\n

Kur t\u00eb mbushet p\u00ebrs\u00ebri, ne do ta regjistrojm\u00eb n\u00eb disk dhe do t\u00eb fillojm\u00eb t\u00eb formojm\u00eb t\u00eb tretin. Ky konfigurim ndalon k\u00ebt\u00eb mund\u00ebsi nga automatizmi. <\/p>\n

N\u00eb t\u00eb v\u00ebrtet\u00eb, shum\u00eb fjalor\u00eb mund t\u00eb jen\u00eb t\u00eb dobish\u00ebm n\u00ebse duam t\u00eb insertojm\u00eb nj\u00eb grup rreshtash, por rast\u00ebsisht insertonim \"mbeturina\". Le t\u00eb themi, s\u00eb pari insertonim rreshta t\u00eb k\u00ebqij dhe pastaj rreshta t\u00eb mir\u00eb. At\u00ebher\u00eb fjalori do t\u00eb ndahet n\u00eb shum\u00eb fjalor\u00eb t\u00eb vegj\u00ebl. Disa prej tyre do t\u00eb jen\u00eb me \"mbeturina\", por t\u00eb fundit do t\u00eb jen\u00eb me rreshta t\u00eb mir\u00eb. Dhe n\u00ebse lexohet, le t\u00eb themi, vet\u00ebm granula e fundit, gjith\u00e7ka do t\u00eb funksionoj\u00eb shpejt. <\/p>\n

\"Optimizimi
\n
\nPara parafolloj se benefitet e LowCardinality, dua se shpejti se ne do t'i arrijm\u00eb rezultatet e reduktimit t\u00eb t\u00eb dh\u00ebnave n\u00eb disk (edhe pse mund ta b\u00ebjm\u00eb), pasi ClickHouse kompreson t\u00eb dh\u00ebnat. Ka nj\u00eb variant parazgjedhje \u2014 LZ4. Gjithashtu, mund t\u00eb b\u00ebhet kompresimi me ZSTD. Por t\u00eb dy algoritm\u00ebt tashm\u00eb implementojn\u00eb kompresimin me fjalor, k\u00ebshtu q\u00eb fjalori yn\u00eb i jasht\u00ebm ClickHouse nuk do t\u00eb ndihmoj\u00eb shum\u00eb. <\/p>\n

P\u00ebr t\u00eb mos qen\u00eb fjal\u00eb-p\u00ebr-fjal\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. Kan\u00eb dal\u00eb tre kolona, ku jan\u00eb regjistruar nj\u00eb miliard rreshta. N\u00eb kolon\u00ebn e par\u00eb, CodePage, ka vet\u00ebm 62 vlera. Dhe \u00ebsht\u00eb e dukshme se n\u00eb LowCardinality(String) e kemi kompresuar m\u00eb mir\u00eb. String \u00ebsht\u00eb pak m\u00eb e dob\u00ebt, por kjo \u00ebsht\u00eb ndoshta p\u00ebr shkak se fjalit\u00eb jan\u00eb t\u00eb shkurtra, ne ruajm\u00eb-ato gjat\u00ebsi, t\u00eb cilat z\u00ebn\u00eb shum\u00eb vend dhe kompresohen keq. <\/p>\n

N\u00ebse marrim PhoneModel, ata jan\u00eb 48 mij\u00eb \u2014 tashm\u00eb m\u00eb shum\u00eb, dhe ndryshimet midis String dhe LowCardinality(String) pothuajse nuk ka. P\u00ebr URL gjithashtu kemi kursyer 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 t\u00eb vler\u00ebsojm\u00eb shpejt\u00ebsin\u00eb e pun\u00ebs. P\u00ebr ta vler\u00ebsuar, kam p\u00ebrdorur nj\u00eb dataset q\u00eb p\u00ebrshkruan udh\u00ebtimet me taksi n\u00eb New York. Ai disponibel<\/a><\/noindex> n\u00eb GitHub. Aty ka pak m\u00eb shum\u00eb se nj\u00eb miliard udh\u00ebtime. Aty pasqyrohen pozita, koha e fillimit dhe p\u00ebrfundimit t\u00eb udh\u00ebtimit, m\u00ebnyra e pages\u00ebs, numri i pasagjer\u00ebve dhe madje lloji i taksis\u00eb \u2014 i gjelb\u00ebr, i 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 porositen m\u00eb shpesh taksit\u00eb. P\u00ebr k\u00ebt\u00eb duhet t\u00eb marrim pozit\u00ebn nga e cila \u00ebsht\u00eb porositur, t\u00eb b\u00ebjm\u00eb nj\u00eb grupim sipas saj dhe t\u00eb llogarisim funksionin count. Ja \u00e7far\u00eb jep ClickHouse. <\/p>\n

\"Optimizimi
\n
\nP\u00ebr t\u00eb matur shpejt\u00ebsin\u00eb e p\u00ebrpunimit t\u00eb k\u00ebrkes\u00ebs, krijova tre tabela me t\u00eb nj\u00ebjtat t\u00eb dh\u00ebna, por p\u00ebrdora p\u00ebr pozit\u00ebn ton\u00eb fillestare tre lloje t\u00eb dh\u00ebnash t\u00eb ndryshme \u2014 String, LowCardinality dhe Enum. LowCardinality dhe Enum rezultuan pes\u00eb her\u00eb m\u00eb t\u00eb shpejta se String. Enum \u00ebsht\u00eb m\u00eb e shpejt\u00eb sepse punon me numra. LowCardinality \u00ebsht\u00eb m\u00eb e shpejt\u00eb p\u00ebr shkak se implementon optimizimin GROUP BY. <\/p>\n

\"Optimizimi
\n
\nLe t\u00eb komplikohemi edhe m\u00eb tej me k\u00ebrkes\u00ebn \u2014 t\u00eb pyesim ku ndodhet parku m\u00eb i njohur n\u00eb New York. S\u00ebrish do ta masim k\u00ebt\u00eb sipas vendndodhjeve ku porositen m\u00eb shpesh taksit\u00eb, por do t\u00eb filtrojm\u00eb vet\u00ebm pozitat ku ka fjal\u00ebn 'park'. Po ashtu, do t\u00eb shtojm\u00eb funksionin like. <\/p>\n

\"Optimizimi
\n
\nPo shikimit n\u00eb koh\u00eb, shohim q\u00eb Enum papritur ka filluar t\u00eb ngadal\u00ebsohet. Madje, funksionon edhe m\u00eb ngadal\u00eb se tipi standard i t\u00eb dh\u00ebnave String. Kjo ndodh sepse funksioni like nuk \u00ebsht\u00eb optimizuar p\u00ebr Enum. Na duhet t\u00eb konvertojm\u00eb stringjet tona nga Enum n\u00eb stringje t\u00eb zakonshme \u2014 ne po b\u00ebjm\u00eb m\u00eb shum\u00eb pun\u00eb. LowCardinality(String) gjithashtu nuk \u00ebsht\u00eb optimizuar nga fillimi, por atje like punon mbi fjalorin, k\u00ebshtu q\u00eb k\u00ebrkesa p\u00ebrshpejtohet krahasuar me String. <\/p>\n

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

\"Optimizimi
\n
\nT\u00eb shohim te k\u00ebrkesa e fundit, nj\u00eb k\u00ebrkes\u00eb m\u00eb artificiale. Ne thjesht do t\u00eb llogarisim funksionin hash nga lokacioni yn\u00eb. Funksioni hash \u00ebsht\u00eb nj\u00eb k\u00ebrkes\u00eb mjaft e ngadalt\u00eb, b\u00ebhet ngadal\u00eb, k\u00ebshtu q\u00eb gjith\u00e7ka do t\u00eb ngadal\u00ebsohet rreth tri her\u00eb.<\/p>\n

\"Optimizimi
\n
\nLowCardinality vazhdon t\u00eb punoj\u00eb m\u00eb shpejt, ndon\u00ebse k\u00ebtu nuk ka filtrimit. Kjo ndodh sepse funksionet tona punojn\u00eb vet\u00ebm mbi fjalorin. Funksioni i llogaritjes s\u00eb hash-it ka nj\u00eb argument\u2014ai 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 nj\u00eb shpejt\u00ebsi q\u00eb nuk \u00ebsht\u00eb m\u00eb e ul\u00ebt se ajo e 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 stringjeve n\u00eb ClickHouse. Referati i Yandex | ProHoster","description":"Sistemi analitik i baz\u00ebs s\u00eb t\u00eb dh\u00ebnave ClickHouse p\u00ebrpunon shum\u00eb lloje t\u00eb ndryshme t\u00eb stringjeve, duke konsumuar burime. P\u00ebr t\u00eb p\u00ebrshpejtuar pun\u00ebn e sistemit, vazhdimisht po shtohen optimizime t\u00eb reja. Zhvilluesi i ClickHouse, Nikolaj Ko\u00e7etov, tregon p\u00ebr llojin e t\u00eb dh\u00ebnave string, p\u00ebrfshir\u00eb llojin e ri, LowCardinality, dhe shpjegon se si mund t\u00eb p\u00ebrmir\u00ebsohet punimi me stringjet. \u2014 S\u00eb pari, le t\u00eb kuptojm\u00eb se si mund t\u00eb ruajm\u00eb stringjet. Ne kemi tipe t\u00eb dh\u00ebnash string.","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. \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.","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"},"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}]}}