{"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\/cs\/blog\/administrirovanie\/optimizacziya-strok-v-clickhouse-doklad-yandeksa","title":{"rendered":"Optimalizace \u0159et\u011bzc\u016f v ClickHouse. Prezentace Yandexu","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Analytick\u00e1 SGBD ClickHouse zpracov\u00e1v\u00e1 mnoho r\u016fzn\u00fdch \u0159et\u011bzc\u016f a spot\u0159ebov\u00e1v\u00e1 p\u0159i tom zdroje. Aby se zrychlila pr\u00e1ce syst\u00e9mu, neust\u00e1le se p\u0159id\u00e1vaj\u00ed nov\u00e1 vylep\u0161en\u00ed. V\u00fdvoj\u00e1\u0159 ClickHouse Nikolaj Ko\u010detov hovo\u0159\u00ed o \u0159et\u011bzcov\u00e9m datov\u00e9m typu, v\u010detn\u011b nov\u00e9ho typu, LowCardinality, a vysv\u011btluje, jak lze zrychlit pr\u00e1ci s \u0159et\u011bzci. <\/p>\n

\r\n \"P\u0159ehr\u00e1t\r\n
<\/div>\r\n <\/div><\/center>
\n\u2014 Nejprve si ujasn\u011bme, jak m\u016f\u017eeme uchov\u00e1vat \u0159et\u011bzce.
\n<\/a><\/noindex>
\n\"Optimalizace
\n
\nM\u00e1me \u0159et\u011bzcov\u00e9 datov\u00e9 typy. String je ve v\u00fdchoz\u00edm nastaven\u00ed velmi vhodn\u00fd a m\u011bl by se pou\u017e\u00edvat t\u00e9m\u011b\u0159 v\u017edy. M\u00e1 mal\u00fd overhead \u2014 9 bajt\u016f na jeden \u0159et\u011bzec. Pokud chceme, aby velikost \u0159et\u011bzc\u016f byla pevn\u011b dan\u00e1 a zn\u00e1m\u00e1 p\u0159edem, je lep\u0161\u00ed pou\u017e\u00edt FixedString. U n\u011bj si m\u016f\u017eeme nastavit pot\u0159ebn\u00fd po\u010det bajt\u016f, je vhodn\u00fd pro data jako jsou IP adresy nebo hash funkce. <\/p>\n

\"Optimalizace
\n
\nSamoz\u0159ejm\u011b, n\u011bkdy n\u011bco zpomaluje. P\u0159edstavte si, \u017ee prov\u00e1d\u00edte dotaz na tabulku. ClickHouse \u010dte pom\u011brn\u011b velk\u00e9 mno\u017estv\u00ed dat, \u0159ekn\u011bme rychlost\u00ed 100 GB\/s, p\u0159i\u010dem\u017e zpracov\u00e1v\u00e1 m\u00e1lo \u0159et\u011bzc\u016f. M\u00e1me dv\u011b tabulky, kter\u00e9 uchov\u00e1vaj\u00ed t\u00e9m\u011b\u0159 stejn\u00e9 \u00fadaje. Z druh\u00e9 tabulky ClickHouse \u010dte data rychleji, ale po\u010det \u0159et\u011bzc\u016f za sekundu je t\u0159ikr\u00e1t ni\u017e\u0161\u00ed. <\/p>\n

\"Optimalizace
\n
\nPokud se pod\u00edv\u00e1me na velikost komprimovan\u00fdch dat, zjist\u00edme, \u017ee je t\u00e9m\u011b\u0159 stejn\u00e1. Ve skute\u010dnosti jsou v tabulk\u00e1ch zaps\u00e1na stejn\u00e1 data \u2014 prvn\u00ed miliarda \u010d\u00edsel \u2014 pouze v prvn\u00edm sloupci je ulo\u017eena jako UInt64, zat\u00edmco ve druh\u00e9m jako String. Proto\u017ee tak druh\u00fd dotaz trv\u00e1 d\u00e9le, \u010dte data z disku a dekomprimuje je. <\/p>\n

\"Optimalizace
\n
\nZde je dal\u0161\u00ed p\u0159\u00edklad. P\u0159edstavme si, \u017ee existuje p\u0159edem zn\u00e1m\u00e1 mno\u017eina \u0159et\u011bzc\u016f, kter\u00e1 je omezena konstantou 1000 nebo 10 000 a t\u00e9m\u011b\u0159 nikdy se nem\u011bn\u00ed. Pro tuto situaci se hod\u00ed typ dat Enum, v ClickHouse existuj\u00ed dva \u2014 Enum8 a Enum16. D\u00edky ukl\u00e1d\u00e1n\u00ed v Enum rychle zpracov\u00e1v\u00e1me dotazy. <\/p>\n

V ClickHouse existuj\u00ed zrychlen\u00ed pro GROUP BY, IN, DISTINCT a optimalizace pro n\u011bkter\u00e9 funkce, nap\u0159\u00edklad pro porovn\u00e1n\u00ed s konstantn\u00edm \u0159et\u011bzcem. Samoz\u0159ejm\u011b, \u017ee \u010d\u00edsla v \u0159et\u011bzci se nep\u0159etypov\u00e1vaj\u00ed, ale naopak se konstantn\u00ed \u0159et\u011bzec p\u0159ev\u00e1d\u00ed na hodnotu Enum. Pot\u00e9 se to rychle porovn\u00e1v\u00e1. <\/p>\n

Ale jsou tu i nev\u00fdhody. I kdy\u017e zn\u00e1me p\u0159esnou mno\u017einu \u0159et\u011bzc\u016f, n\u011bkdy se mus\u00ed dopl\u0148ovat. P\u0159i\u0161la nov\u00e1 \u0159et\u011bzec \u2014 mus\u00edme prov\u00e9st ALTER. <\/p>\n

\"Optimalizace
\n
\nALTER pro Enum v ClickHouse je realizov\u00e1n optim\u00e1ln\u011b. Nep\u0159episujeme data na disku, ale ALTER mohou zpomalovat kv\u016fli tomu, \u017ee struktury Enum jsou uchov\u00e1v\u00e1ny ve sch\u00e9matu tabulky samotn\u00e9. Proto mus\u00edme po\u010dkat na dotazy na \u010dten\u00ed z tabulky, nap\u0159\u00edklad. <\/p>\n

Vzn\u00e1\u0161\u00ed se ot\u00e1zka, zda lze to ud\u011blat l\u00e9pe? Pravd\u011bpodobn\u011b ano. M\u016f\u017eeme uchov\u00e1vat strukturu Enum nikoliv ve sch\u00e9matu tabulky, ale v ZooKeeperu. Mohou v\u0161ak nastat probl\u00e9my se synchronizac\u00ed. Nap\u0159\u00edklad jedna replikace dostala data a jin\u00e1 ne, a pokud m\u00e1 star\u00fd Enum, n\u011bco se rozbije. (V ClickHouse jsme t\u00e9m\u011b\u0159 dokon\u010dili neblokuj\u00edc\u00ed ALTER dotazy. Jakmile je dokon\u010d\u00edme, nebude nutn\u00e9 \u010dekat na dotazy na \u010dten\u00ed.)<\/p>\n

\"Optimalizace
\n
\nAbychom se nemuseli zab\u00fdvat ALTER Enum, m\u016f\u017eeme vyu\u017e\u00edt extern\u00ed slovn\u00edky ClickHouse. P\u0159ipom\u00edn\u00e1m, \u017ee to je struktura dat ve form\u00e1tu key-value uvnit\u0159 ClickHouse, pomoc\u00ed kter\u00e9 m\u016f\u017eeme z\u00edsk\u00e1vat data z extern\u00edch zdroj\u016f, nap\u0159\u00edklad z tabulek MySQL. <\/p>\n

Ve slovn\u00edku ClickHouse uchov\u00e1v\u00e1me mnoho r\u016fzn\u00fdch \u0159et\u011bzc\u016f a v tabulce jejich identifik\u00e1tory ve form\u011b \u010d\u00edsel. Pokud pot\u0159ebujeme z\u00edskat \u0159et\u011bzec, vol\u00e1me funkci dictGet a pracujeme s n\u00edm. Pot\u00e9 nemus\u00edme prov\u00e1d\u011bt ALTER. Chceme-li n\u011bco p\u0159idat do Enum, vlo\u017e\u00edme to do stejn\u00e9 tabulky MySQL. <\/p>\n

Ale zde vyvst\u00e1vaj\u00ed jin\u00e9 probl\u00e9my. Za prv\u00e9, nepohodln\u00e1 syntaxe. Pokud chceme z\u00edskat \u0159et\u011bzec, mus\u00edme volat dictGet. Za druh\u00e9, chyb\u00ed n\u011bkter\u00e1 optimalizace. Porovn\u00e1n\u00ed s konstantn\u00edm \u0159et\u011bzcem pro slovn\u00edky tak rychle neud\u011bl\u00e1me. <\/p>\n

Dal\u0161\u00ed mohou b\u00fdt probl\u00e9my s aktualizac\u00ed. P\u0159edpokl\u00e1dejme, \u017ee jsme po\u017e\u00e1dali o \u0159et\u011bzec v cache-slovn\u00edku, ale v cache se nedostal. Pak mus\u00edme \u010dekat, a\u017e se na\u010dtou data z extern\u00edho zdroje. <\/p>\n

\"Optimalizace
\n
\nCelkov\u00fd nedostatek obou metod spo\u010d\u00edv\u00e1 v tom, \u017ee uchov\u00e1v\u00e1me v\u0161echny kl\u00ed\u010de na jednom m\u00edst\u011b a synchronizujeme je. Pro\u010d tedy neschov\u00e1vat slovn\u00edky lok\u00e1ln\u011b? Bez synchronizace \u2014 bez probl\u00e9m\u016f. M\u016f\u017eeme uchov\u00e1vat slovn\u00edk lok\u00e1ln\u011b v bloku na disku. To znamen\u00e1, \u017ee jsme ud\u011blali Insert, zapsali slovn\u00edk. Pokud pracujeme s daty v pam\u011bti, m\u016f\u017eeme slovn\u00edk zapsat bu\u010f do bloku dat, nebo do kousku sloupce, nebo do n\u011bjak\u00e9ho cache, abychom urychlili v\u00fdpo\u010dty.<\/p>\n

Slovn\u00edm k\u00f3dov\u00e1n\u00edm \u0159et\u011bzc\u016f <\/h3>\n

\nTak jsme p\u0159i\u0161li ke vzniku nov\u00e9ho typu dat v ClickHouse \u2014 LowCardinality. To je form\u00e1t uchov\u00e1v\u00e1n\u00ed dat: jak se zapisuj\u00ed na disk a jak se \u010dtou, jak jsou prezentov\u00e1na v pam\u011bti a sch\u00e9ma jejich zpracov\u00e1n\u00ed. <\/p>\n

\"Optimalizace
\n
\nNa sn\u00edmku jsou dv\u011b sloupce. Vpravo jsou \u0159\u00e1dky standardn\u011b ulo\u017eeny jako typ String. Je jasn\u00e9, \u017ee se jedn\u00e1 o n\u011bjak\u00e9 modely mobiln\u00edch telefon\u016f. Vlevo je p\u0159esn\u011b stejn\u00fd sloupec, pouze s typem LowCardinality. Skl\u00e1d\u00e1 se ze slovn\u00edku s mnoha r\u016fzn\u00fdmi \u0159et\u011bzci (\u0159et\u011bzce z prav\u00e9ho sloupce) a seznamu pozic (\u010d\u00edsla \u0159\u00e1dk\u016f). <\/p>\n

S pomoc\u00ed t\u011bchto dvou struktur je mo\u017en\u00e9 obnovit p\u016fvodn\u00ed sloupec. Existuje tak\u00e9 inverzn\u00ed index \u2014 hashovac\u00ed tabulka, kter\u00e1 pom\u00e1h\u00e1 naj\u00edt podle \u0159et\u011bzce pozici ve slovn\u00edku. Ta je pot\u0159ebn\u00e1 pro zrychlen\u00ed n\u011bkter\u00fdch dotaz\u016f. Nap\u0159\u00edklad pokud chceme porovnat, vyhledat \u0159et\u011bzec v na\u0161em sloupci nebo je slou\u010dit. <\/p>\n

LowCardinality je parametrick\u00fd typ dat. M\u016f\u017ee to b\u00fdt bu\u010f \u010d\u00edslo, nebo n\u011bco, co je ulo\u017eeno jako \u010d\u00edslo, nebo \u0159et\u011bzec, nebo Nullable z nich. <\/p>\n

\"Optimalizace
\n
\nSpecifikum LowCardinality je v tom, \u017ee m\u016f\u017ee b\u00fdt ukl\u00e1d\u00e1n pro n\u011bkter\u00e9 funkce. Na sn\u00edmku je vid\u011bt p\u0159\u00edklad dotazu. V prvn\u00edm \u0159\u00e1dku jsem vytvo\u0159il sloupec typu LowCardinality ze String, nazval jsem ho S. Pot\u00e9 jsem se zeptal na jeho n\u00e1zev \u2014 ClickHouse \u0159ekl, \u017ee je to LowCardinality z String. To je spr\u00e1vn\u011b.<\/p>\n

T\u0159et\u00ed \u0159\u00e1dek je t\u00e9m\u011b\u0159 stejn\u00fd, pouze jsme zavolali funkci length. V ClickHouse funkce length vrac\u00ed datov\u00fd typ UInt64. Ale dostali jsme LowCardinality z UInt64. V \u010dem je smysl?<\/p>\n

\"Optimalizace
\n
\nVe slovn\u00edku byly n\u00e1zvy mobiln\u00edch telefon\u016f, pou\u017eili jsme funkci length. Nyn\u00ed m\u00e1me podobn\u00fd slovn\u00edk tvo\u0159en\u00fd pouze \u010d\u00edsly \u2014 to jsou d\u00e9lky \u0159et\u011bzc\u016f. Sloupec s pozicemi se nezm\u011bnil. Nakonec jsme zpracovali m\u00e9n\u011b dat a u\u0161et\u0159ili \u010das dotazu. <\/p>\n

Mohou existovat i dal\u0161\u00ed optimalizace, nap\u0159\u00edklad p\u0159id\u00e1n\u00ed jednoduch\u00e9 cache. P\u0159i v\u00fdpo\u010dtu hodnoty funkce si ji m\u016f\u017eeme zapamatovat a vytvo\u0159it stejnou, ani\u017e bychom ji museli znovu po\u010d\u00edtat. <\/p>\n

D\u00e1le m\u016f\u017ee b\u00fdt optimalizov\u00e1n GROUP BY, proto\u017ee n\u00e1\u0161 sloupec se slovn\u00edkem je ji\u017e \u010d\u00e1ste\u010dn\u011b agregov\u00e1n \u2014 m\u016f\u017eeme rychleji vypo\u010d\u00edtat hodnoty hash funkc\u00ed a p\u0159ibli\u017en\u011b naj\u00edt bucket, do kter\u00e9ho um\u00edstit dal\u0161\u00ed \u0159\u00e1dek. Tak\u00e9 m\u016f\u017eeme specializovat n\u011bkter\u00e9 agrega\u010dn\u00ed funkce, nap\u0159\u00edklad uniq, proto\u017ee do n\u00ed lze poslat pouze slovn\u00edk, zat\u00edmco pozice z\u016fstanou nedot\u010deny \u2014 t\u00edm v\u0161e pob\u011b\u017e\u00ed rychleji. Prvn\u00ed dv\u011b optimalizace jsme ji\u017e p\u0159idali do ClickHouse.<\/p>\n

\"Optimalizace
\n
\nA co kdy\u017e vytvo\u0159\u00edme sloupec s na\u0161\u00edm typem dat a vlo\u017e\u00edme do n\u011bj hodn\u011b \u0161patn\u00fdch r\u016fzn\u00fdch \u0159et\u011bzc\u016f? Nep\u0159ete\u010de n\u00e1m pam\u011b\u0165? Ne, pro to m\u00e1 ClickHouse dv\u011b speci\u00e1ln\u00ed nastaven\u00ed. Prvn\u00ed je low_cardinality_max_dictionary_size. To je maxim\u00e1ln\u00ed velikost slovn\u00edku, kter\u00fd m\u016f\u017ee b\u00fdt zaps\u00e1n na disk. Vkl\u00e1d\u00e1n\u00ed prob\u00edh\u00e1 n\u00e1sleduj\u00edc\u00edm zp\u016fsobem: kdy\u017e vkl\u00e1d\u00e1me data, p\u0159ich\u00e1z\u00ed k n\u00e1m proud \u0159et\u011bzc\u016f, z nich\u017e tvo\u0159\u00edme velk\u00fd spole\u010dn\u00fd slovn\u00edk. Pokud slovn\u00edk p\u0159es\u00e1hne hodnotu nastaven\u00ed, zap\u00ed\u0161eme aktu\u00e1ln\u00ed slovn\u00edk na disk a ostatn\u00ed \u0159et\u011bzce um\u00edst\u00edme \u201en\u011bkde vedle\u201c, bl\u00edzko index\u016f. D\u016fsledkem je, \u017ee nikdy nep\u0159epo\u010d\u00edt\u00e1me velk\u00fd slovn\u00edk a nevzniknou probl\u00e9my s pam\u011bt\u00ed. <\/p>\n

Druh\u00e9 nastaven\u00ed se naz\u00fdv\u00e1 low_cardinality_use_single_dictionary_for_part. P\u0159edstavte si, \u017ee v p\u0159edchoz\u00edm sch\u00e9matu, kdy\u017e jsme vkl\u00e1dali data, n\u00e1\u0161 slovn\u00edk p\u0159ete\u010de a zap\u00ed\u0161eme ho na disk. Objevuje se ot\u00e1zka, pro\u010d te\u010f nevytvo\u0159it je\u0161t\u011b jeden stejn\u00fd slovn\u00edk? <\/p>\n

Kdy\u017e p\u0159ete\u010de, znovu ho zap\u00ed\u0161eme na disk a za\u010dneme vytv\u00e1\u0159et t\u0159et\u00ed. Toto nastaven\u00ed pr\u00e1v\u011b tuto mo\u017enost ve v\u00fdchoz\u00edm nastaven\u00ed deaktivuje. <\/p>\n

Ve skute\u010dnosti m\u016f\u017ee b\u00fdt mnoho slovn\u00edk\u016f u\u017eite\u010dn\u00fdch, pokud chceme vlo\u017eit n\u011bjakou mno\u017einu \u0159et\u011bzc\u016f, ale n\u00e1hodou jsme vlo\u017eili \u201eodpad\u201c. \u0158ekn\u011bme, \u017ee jsme nejprve vlo\u017eili \u0161patn\u00e9 \u0159et\u011bzce a pak dobr\u00e9. Pak se slovn\u00edk rozd\u011bl\u00ed na mnoho mal\u00fdch slovn\u00edk\u016f. N\u011bkter\u00e9 z nich budou s \u201eodpadem\u201c, ale ty posledn\u00ed budou s dobr\u00fdmi \u0159et\u011bzci. A pokud p\u0159e\u010dteme, \u0159ekn\u011bme, pouze posledn\u00ed granuli, pak v\u0161e bude fungovat rychle. <\/p>\n

\"Optimalizace
\n
\nNe\u017e za\u010dnu mluvit o v\u00fdhod\u00e1ch LowCardinality, hned \u0159eknu, \u017ee pravd\u011bpodobn\u011b nedos\u00e1hneme sn\u00ed\u017een\u00ed dat na disku (a\u010dkoliv se to m\u016f\u017ee st\u00e1t), proto\u017ee ClickHouse komprimuje data. Existuje v\u00fdchoz\u00ed varianta \u2014 LZ4. M\u016f\u017eete tak\u00e9 pou\u017e\u00edt kompresi pomoc\u00ed ZSTD. Ale oba algoritmy ji\u017e implementuj\u00ed slovn\u00edkov\u00e9 komprese, tak\u017ee n\u00e1\u0161 extern\u00ed slovn\u00edk ClickHouse nebude p\u0159\u00edli\u0161 n\u00e1pomocn\u00fd. <\/p>\n

Abych nebyl neopodstatn\u011bn\u00fd, vzal jsem n\u011bkter\u00e1 data z metriky \u2014 String, LowCardinality(String) a Enum \u2014 a ulo\u017eil je do r\u016fzn\u00fdch typ\u016f dat. Vznikly t\u0159i sloupce, kde je z\u00e1znam jednoho miliardy \u0159\u00e1dk\u016f. V prvn\u00edm sloupci, CodePage, je pouze 62 hodnot. A je vid\u011bt, \u017ee v LowCardinality(String) n\u00e1m je stla\u010dili l\u00e9pe. String je o n\u011bco hor\u0161\u00ed, ale to pravd\u011bpodobn\u011b z d\u016fvodu, \u017ee \u0159et\u011bzce jsou kr\u00e1tk\u00e9, uchov\u00e1v\u00e1me jejich d\u00e9lky, a ty zab\u00edraj\u00ed hodn\u011b m\u00edsta, \u0161patn\u011b se stla\u010duj\u00ed. <\/p>\n

Pokud vezmeme PhoneModel, je jich 48 tis\u00edc \u2014 u\u017e v\u00edc, a rozd\u00edly mezi String a LowCardinality(String) jsou t\u00e9m\u011b\u0159 \u017e\u00e1dn\u00e9. Pro URL jsme tak\u00e9 u\u0161et\u0159ili jen 2 GB \u2014 mysl\u00edm, \u017ee se na to ned\u00e1 spol\u00e9hat. <\/p>\n

Hodnocen\u00ed rychlosti pr\u00e1ce<\/h3>\n

\n\"Optimalizace
\nOdkaz na sn\u00edmek<\/a><\/noindex><\/b><\/p>\n

Nyn\u00ed ohodno\u0165me rychlost pr\u00e1ce. K jej\u00edmu hodnocen\u00ed jsem pou\u017eil dataset s popisem j\u00edzd taxi v New Yorku. Ten je dostupn\u00e1<\/a><\/noindex> najdete na GitHubu. Obsahuje n\u011bco m\u00e1lo p\u0159es miliardu j\u00edzd. Zaznamen\u00e1v\u00e1 zde lokaci, \u010das za\u010d\u00e1tku a konce j\u00edzdy, zp\u016fsob platby, po\u010det pasa\u017e\u00e9r\u016f a dokonce typ taxi \u2014 zelen\u00e9, \u017elut\u00e9 a Uber. <\/p>\n

\"Optimalizace
\n
\nPrvn\u00ed dotaz jsem ud\u011blal pom\u011brn\u011b jednoduch\u00fd \u2014 zeptal jsem se, odkud je taxi nej\u010dast\u011bji objedn\u00e1v\u00e1no. K tomu je t\u0159eba vz\u00edt lokaci, odkud byly objedn\u00e1vky, prov\u00e9st GROUP BY podle n\u00ed a spo\u010d\u00edtat funkci count. Tady ClickHouse n\u011bco vyd\u00e1v\u00e1. <\/p>\n

\"Optimalizace
\n
\nAbych zm\u011b\u0159il rychlost zpracov\u00e1n\u00ed dotazu, vytvo\u0159il jsem t\u0159i tabulky se stejn\u00fdmi daty, ale pro na\u0161i v\u00fdchoz\u00ed lokaci jsem pou\u017eil t\u0159i r\u016fzn\u00e9 typy dat \u2014 String, LowCardinality a Enum. LowCardinality a Enum byly p\u011btkr\u00e1t rychlej\u0161\u00ed ne\u017e String. Enum je rychlej\u0161\u00ed, proto\u017ee pracuje s \u010d\u00edsly. LowCardinality \u2014 proto\u017ee je implementov\u00e1na optimalizace GROUP BY. <\/p>\n

\"Optimalizace
\n
\nPoj\u010fme je\u0161t\u011b zt\u00ed\u017eit dotaz \u2014 zeptejte se, kde se nach\u00e1z\u00ed nejpopul\u00e1rn\u011bj\u0161\u00ed park v New Yorku. Op\u011bt to budeme m\u011b\u0159it podle toho, kde se nej\u010dast\u011bji objedn\u00e1vaj\u00ed taxi, ale z\u00e1rove\u0148 filtrujeme pouze ty lokace, kde je slovo \"park\". P\u0159id\u00e1me tak\u00e9 funkci like. <\/p>\n

\"Optimalizace
\n
\nD\u00edv\u00e1me se na \u010das \u2014 vid\u00edme, \u017ee Enum najednou za\u010dal zpomalovat. A funguje je\u0161t\u011b pomaleji ne\u017e standardn\u00ed typ dat String. To se d\u011bje, proto\u017ee funkce like nen\u00ed v\u016fbec optimalizov\u00e1na pro Enum. Mus\u00edme p\u0159ev\u00e1d\u011bt na\u0161e \u0159et\u011bzce z Enum na oby\u010dejn\u00e9 \u0159et\u011bzce \u2014 d\u011bl\u00e1me v\u00edc pr\u00e1ce. LowCardinality(String) tak\u00e9 nen\u00ed implicitn\u011b optimalizov\u00e1n, ale tam like pracuje s slovn\u00edkem, tak\u017ee se dotaz zrychluje ve srovn\u00e1n\u00ed se String. <\/p>\n

P\u0159i pr\u00e1ci s Enum existuje glob\u00e1ln\u011bj\u0161\u00ed probl\u00e9m. Pokud ho chceme optimalizovat, mus\u00edme to ud\u011blat na ka\u017ed\u00e9m m\u00edst\u011b v k\u00f3du. P\u0159edpokl\u00e1dejme, \u017ee jsme napsali novou funkci \u2014 je nezbytn\u00e9 vymyslet optimalizaci pro Enum. A v LowCardinality je v\u0161e optimalizov\u00e1no automaticky.<\/p>\n

\"Optimalizace
\n
\nPod\u00edvejme se na posledn\u00ed dotaz, kter\u00fd je um\u011blej\u0161\u00ed. Jednodu\u0161e spo\u010d\u00edt\u00e1me hash funkci na\u0161\u00ed lokace. Hash funkce je pom\u011brn\u011b pomal\u00fd dotaz, trv\u00e1 dlouho, tedy se to v\u0161echno zpomal\u00ed zhruba t\u0159ikr\u00e1t.<\/p>\n

\"Optimalizace
\n
\nLowCardinality st\u00e1le funguje rychleji, i kdy\u017e zde nen\u00ed filtrov\u00e1n\u00ed. D\u011bje se to, proto\u017ee na\u0161e funkce pracuj\u00ed pouze se slovn\u00edkem. Funkce pro v\u00fdpo\u010det hash m\u00e1 jeden argument \u2014 m\u016f\u017ee zpracov\u00e1vat m\u00e9n\u011b dat a tak\u00e9 m\u016f\u017ee vr\u00e1tit LowCardinality. <\/p>\n

\"Optimalizace
\n
\nNa\u0161\u00edm glob\u00e1ln\u00edm c\u00edlem je dos\u00e1hnout rychlosti, kter\u00e1 nebude ni\u017e\u0161\u00ed ne\u017e u String v jak\u00fdchkoliv p\u0159\u00edpadech, a zachovat zrychlen\u00ed. A mo\u017en\u00e1 jednoho dne vym\u011bn\u00edm String za LowCardinality, vy aktualizujete ClickHouse a v\u0161echno bude o n\u011bco rychlej\u0161\u00ed.
\n
Zdroj: 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\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\udd47Optimalizace \u0159et\u011bzc\u016f v ClickHouse. Prezentace Yandexu | ProHoster","description":"Analytick\u00e1 datab\u00e1ze ClickHouse zpracov\u00e1v\u00e1 mnoho r\u016fzn\u00fdch \u0159et\u011bzc\u016f a spot\u0159ebov\u00e1v\u00e1 zdroje. Pro zrychlen\u00ed syst\u00e9mu se neust\u00e1le p\u0159id\u00e1vaj\u00ed nov\u00e9 optimalizace.","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/optimizacziya-strok-v-clickhouse-doklad-yandeksa","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","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\/cs\/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\/cs\/wp-json\/wp\/v2\/posts\/74737","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=74737"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/74737\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/74738"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=74737"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=74737"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=74737"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}