{"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\/sk\/blog\/administrirovanie\/optimizacziya-strok-v-clickhouse-doklad-yandeksa","title":{"rendered":"Optimaliz\u00e1cia re\u0165azcov v ClickHouse. Predn\u00e1\u0161ka Yandexu","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Analytick\u00e1 SYST\u00c9M ZPRACOV\u00c1N\u00cd DAT ClickHouse zpracov\u00e1v\u00e1 mnoho r\u016fzn\u00fdch \u0159et\u011bzc\u016f, \u010d\u00edm\u017e spot\u0159ebov\u00e1v\u00e1 zdroje. Aby se urychlil chod 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 urychlit pr\u00e1ci s \u0159et\u011bzci. <\/p>\n

\r\n \"Prehrajte\r\n
<\/div>\r\n <\/div><\/center>
\n\u2014 Nejprve si objasn\u011bme, jak lze \u0159et\u011bzce uchov\u00e1vat.
\n<\/a><\/noindex>
\n\"Optimaliz\u00e1cia
\n
\nM\u00e1me \u0159et\u011bzcov\u00e9 datov\u00e9 typy. String se dob\u0159e hod\u00ed jako v\u00fdchoz\u00ed, 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\u00e1 a zn\u00e1m\u00e1 p\u0159edem, je lep\u0161\u00ed pou\u017e\u00edt FixedString. V n\u011bm lze stanovit po\u017eadovan\u00fd po\u010det bajt\u016f, je praktick\u00fd pro data jako IP adresy nebo hashovac\u00ed funkce. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nSamoz\u0159ejm\u011b, n\u011bkdy n\u011bco zpomaluje. P\u0159edstavte si, \u017ee d\u011bl\u00e1te dotaz na tabulku. ClickHouse \u010dte docela velk\u00e9 mno\u017estv\u00ed dat, dejme tomu rychlost\u00ed 100 GB\/s, p\u0159i\u010dem\u017e po\u010det zpracov\u00e1van\u00fdch \u0159et\u011bzc\u016f je mal\u00fd. M\u00e1me dv\u011b tabulky, kter\u00e9 uchov\u00e1vaj\u00ed t\u00e9m\u011b\u0159 stejn\u00e9 \u00fadaje. Z druh\u00e9 tabulky ClickHouse \u010dte data vy\u0161\u0161\u00ed rychlost\u00ed, ale po\u010det \u0159et\u011bzc\u016f za sekundu se \u010dte t\u0159ikr\u00e1t m\u00e9n\u011b. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nPokud se pod\u00edv\u00e1me na velikost komprimovan\u00fdch dat, bude t\u00e9m\u011b\u0159 stejn\u00e1. Ve skute\u010dnosti jsou v tabulk\u00e1ch zaps\u00e1ny stejn\u00e9 \u00fadaje \u2014 prvn\u00edch miliard \u010d\u00edslic \u2014 pouze v prvn\u00edm sloupci jsou zaps\u00e1ny jako UInt64, zat\u00edmco ve druh\u00e9m jako String. Z toho d\u016fvodu druh\u00fd dotaz \u010dte data z disku d\u00e9le a dekomprimuje je. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nZde je jin\u00fd p\u0159\u00edklad. P\u0159edpokl\u00e1dejme, \u017ee existuje p\u0159edem zn\u00e1m\u00e1 \u0159ada \u0159et\u011bzc\u016f, kter\u00e1 je omezena konstantou 1000 nebo 10 000 a skoro se nikdy nem\u011bn\u00ed. Pro tento p\u0159\u00edpad n\u00e1m vyhovuje datov\u00fd typ Enum, v ClickHouse jsou dva \u2014 Enum8 a Enum16. D\u00edky uchov\u00e1v\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\u0159ev\u00e1d\u011bj\u00ed, ale naopak, konstantn\u00ed \u0159et\u011bzec se p\u0159ev\u00e1d\u00ed na hodnotu Enum. Pot\u00e9 se v\u0161e rychle porovn\u00e1. <\/p>\n

Ale jsou tu i nev\u00fdhody. I kdy\u017e zn\u00e1me p\u0159esn\u011b po\u010det \u0159et\u011bzc\u016f, n\u011bkdy se mus\u00ed doplnit. P\u0159i\u0161la nov\u00e1 \u0159et\u011bzec \u2014 mus\u00edme prov\u00e9st ALTER. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nALTER pre enum v ClickHouse je implementovan\u00fd optim\u00e1lne. Neprepisujeme \u00fadaje na disku, av\u0161ak ALTER m\u00f4\u017ee spomali\u0165 z d\u00f4vodu, \u017ee \u0161trukt\u00fary Enum s\u00fa ulo\u017een\u00e9 v sch\u00e9me samotnej tabu\u013eky. Preto mus\u00edme po\u010dka\u0165 na po\u017eiadavky na \u010d\u00edtanie z tabu\u013eky, napr\u00edklad. <\/p>\n

Vyskytuje sa ot\u00e1zka, d\u00e1 sa to urobi\u0165 lep\u0161ie? Asi \u00e1no. M\u00f4\u017eeme uchova\u0165 \u0161trukt\u00faru Enum nie v sch\u00e9me tabu\u013eky, ale v ZooKeeper. M\u00f4\u017eu sa v\u0161ak vyskytn\u00fa\u0165 probl\u00e9my so synchroniz\u00e1ciou. Napr\u00edklad jedna replika dostala \u00fadaje, druh\u00e1 nie, a ak m\u00e1 star\u00fd Enum, tak sa nie\u010do pokaz\u00ed. (V ClickHouse sme takmer dokon\u010dili neblokuj\u00face ALTER po\u017eiadavky. Ke\u010f ich dokon\u010d\u00edme \u00faplne, nebudeme musie\u0165 \u010daka\u0165 na po\u017eiadavky na \u010d\u00edtanie.)<\/p>\n

\"Optimaliz\u00e1cia
\n
\nAby sme sa nezaoberali ALTER Enum, m\u00f4\u017eeme pou\u017ei\u0165 extern\u00e9 slovn\u00edky ClickHouse. Pripom\u00ednam, \u017ee ide o \u0161trukt\u00faru d\u00e1t key-value vn\u00fatri ClickHouse, pomocou ktorej m\u00f4\u017eeme z\u00edskava\u0165 \u00fadaje z extern\u00fdch zdrojov, napr\u00edklad z tabuliek MySQL. <\/p>\n

V slovn\u00edku ClickHouse uchov\u00e1vame mno\u017estvo r\u00f4znych re\u0165azcov, a v tabu\u013eke s\u00fa ich identifik\u00e1tory vo forme \u010d\u00edsel. Ak potrebujeme z\u00edska\u0165 re\u0165azec, vol\u00e1me funkciu dictGet a pracujeme s n\u00edm. Potom u\u017e nemus\u00edme robi\u0165 ALTER. Aby sme nie\u010do pridali do Enum, vklad\u00e1me to do tej istej tabu\u013eky MySQL. <\/p>\n

Ale tu vznikaj\u00fa in\u00e9 probl\u00e9my. Po prv\u00e9, nepr\u00edjemn\u00e1 syntax. Ak chceme z\u00edska\u0165 re\u0165azec, mus\u00edme vola\u0165 dictGet. Po druh\u00e9, absencia niektor\u00fdch optimaliz\u00e1ci\u00ed. Porovn\u00e1vanie s kon\u0161tantn\u00fdm re\u0165azcom pre slovn\u00edky tak r\u00fdchlo neurob\u00edme. <\/p>\n

E\u0161te m\u00f4\u017eu nasta\u0165 probl\u00e9my s aktualiz\u00e1ciou. Predpokladajme, \u017ee sme po\u017eiadali o re\u0165azec v cache-slovn\u00edku, a ten sa do cache nedostal. Potom mus\u00edme \u010daka\u0165, k\u00fdm sa na\u010d\u00edtaj\u00fa \u00fadaje z extern\u00e9ho zdroja. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nSpolo\u010dn\u00fdm nedostatkom oboch met\u00f3d je, \u017ee uchov\u00e1vame v\u0161etky k\u013e\u00fa\u010de na jednom mieste a synchronizujeme ich. Tak pre\u010do ich neuchov\u00e1va\u0165 lok\u00e1lne? \u017diadna synchroniz\u00e1cia \u2014 \u017eiadne probl\u00e9my. M\u00f4\u017eeme uchov\u00e1va\u0165 slovn\u00edk lok\u00e1lne v kuse na disku. To znamen\u00e1, \u017ee sme urobili Insert, zap\u00edsali slovn\u00edk. Ak pracujeme s \u00fadajmi v pam\u00e4ti, m\u00f4\u017eeme zap\u00edsa\u0165 slovn\u00edk bu\u010f do bloku d\u00e1t, alebo do kusu st\u013apca, alebo do nejak\u00e9ho cache, aby sme ur\u00fdchlili v\u00fdpo\u010dty.<\/p>\n

K\u00f3dovanie re\u0165azcov pomocou slovn\u00edka <\/h3>\n

\nTak sme pri\u0161li k vytvoreniu nov\u00e9ho typu \u00fadajov v ClickHouse \u2014 LowCardinality. Je to form\u00e1t ukladania \u00fadajov: ako sa zapisuj\u00fa na disk a ako sa \u010d\u00edtaj\u00fa, ako s\u00fa reprezentovan\u00e9 v pam\u00e4ti a sch\u00e9ma ich spracovania. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nNa sn\u00edmke s\u00fa dve kol\u00f3nky. Vpravo sa riadky uchov\u00e1vaj\u00fa \u0161tandardne, v type String. Je jasn\u00e9, \u017ee ide o nejak\u00e9 modely mobiln\u00fdch telef\u00f3nov. V\u013eavo je presne tak\u00e1 ist\u00e1 kol\u00f3nka, iba v type LowCardinality. Sklad\u00e1 sa zo slovn\u00edka s mno\u017estvom r\u00f4znych riadkov (riadky z kol\u00f3nky vpravo) a zoznamu poz\u00edci\u00ed (\u010d\u00edsel riadkov). <\/p>\n

S pomocou t\u00fdchto dvoch \u0161trukt\u00far je mo\u017en\u00e9 obnovi\u0165 p\u00f4vodn\u00fa kol\u00f3nku. Existuje tie\u017e opa\u010dn\u00fd index \u2014 hash tabu\u013eka, ktor\u00e1 pom\u00e1ha n\u00e1js\u0165 poz\u00edciu v slovn\u00edku pod\u013ea riadku. Je potrebn\u00e1 na ur\u00fdchlenie niektor\u00fdch dotazov. Napr\u00edklad, ak chceme porovna\u0165, vyh\u013eada\u0165 riadok v na\u0161ej kol\u00f3nke alebo ich zl\u00fa\u010di\u0165. <\/p>\n

LowCardinality \u2014 je parametrov\u00fd typ \u00fadajov. M\u00f4\u017ee to by\u0165 \u010d\u00edslo, alebo nie\u010do, \u010do je uchov\u00e1van\u00e9 ako \u010d\u00edslo, alebo re\u0165azec, alebo Nullable z nich. <\/p>\n

\"Optimaliz\u00e1cia
\n
\n\u0160peci\u00e1lnos\u0165 LowCardinality spo\u010d\u00edva v tom, \u017ee sa m\u00f4\u017ee uchov\u00e1va\u0165 pre niektor\u00e9 funkcie. Na sn\u00edmke je vidie\u0165 pr\u00edklad dotazu. V prvej vet\u011b som vytvoril kol\u00f3nku typu LowCardinality od String a nazval ju S. Potom som sa sp\u00fdtal na jej n\u00e1zov \u2014 ClickHouse povedal, \u017ee je to LowCardinality od String. V\u0161etko je spr\u00e1vne.<\/p>\n

Tretia veta je takmer rovnak\u00e1, iba sme zavolali funkciu length. V ClickHouse funkcia length vracia typ \u00fadajov UInt64. Ale dostali sme LowCardinality od UInt64. O \u010do ide?<\/p>\n

\"Optimaliz\u00e1cia
\n
\nV slovn\u00edku boli ulo\u017een\u00e9 n\u00e1zvy mobiln\u00fdch telef\u00f3nov, pou\u017eili sme funkciu length. Teraz m\u00e1me analogick\u00fd slovn\u00edk, ktor\u00fd sa sklad\u00e1 iba z \u010d\u00edsel \u2014 to s\u00fa d\u013a\u017eky re\u0165azcov. St\u013apec s poz\u00edciami sa nezmenil. Napokon sme spracovali menej \u00fadajov, u\u0161etrili sme \u010das pri dotazovan\u00ed. <\/p>\n

M\u00f4\u017eu by\u0165 aj in\u00e9 optimaliz\u00e1cie, napr\u00edklad pridanie jednoduch\u00e9ho cache. Pri v\u00fdpo\u010dte hodnoty funkcie si ju m\u00f4\u017eeme zapam\u00e4ta\u0165 a zlo\u017ei\u0165 rovnak\u00fa, nemus\u00edme ju po\u010d\u00edta\u0165 znovu. <\/p>\n

M\u00f4\u017ee sa tie\u017e spravi\u0165 optimaliz\u00e1cia GROUP BY, preto\u017ee na\u0161a kol\u00f3nka so slovn\u00edkom u\u017e bola \u010diasto\u010dne agregovan\u00e1 \u2014 je mo\u017en\u00e9 r\u00fdchlej\u0161ie vypo\u010d\u00edtava\u0165 hodnotu hash funkci\u00ed a pribli\u017ene n\u00e1js\u0165 bucket, do ktor\u00e9ho sa m\u00e1 vlo\u017ei\u0165 \u010fal\u0161\u00ed riadok. Okrem toho je mo\u017en\u00e9 \u0161pecializova\u0165 niektor\u00e9 agrega\u010dn\u00e9 funkcie, napr\u00edklad uniq, preto\u017ee do nej m\u00f4\u017eeme posla\u0165 iba slovn\u00edk a poz\u00edcie necha\u0165 nedotknut\u00e9 \u2014 takto v\u0161etko bude fungova\u0165 r\u00fdchlej\u0161ie. Prv\u00e9 dve optimaliz\u00e1cie sme u\u017e pridali do ClickHouse.<\/p>\n

\"Optimaliz\u00e1cia
\n
\nA \u010do ak vytvor\u00edme st\u013apec s na\u0161\u00edm typom \u00fadajov a vlo\u017e\u00edme do neho ve\u013ea zl\u00fdch r\u00f4znych riadkov? Neprepln\u00ed sa na\u0161a pam\u00e4\u0165? Nie, na to m\u00e1 ClickHouse dve \u0161peci\u00e1lne nastavenia. Prv\u00e9 \u2013 low_cardinality_max_dictionary_size. To je maxim\u00e1lna ve\u013ekos\u0165 slovn\u00edka, ktor\u00e1 m\u00f4\u017ee by\u0165 zap\u00edsan\u00e1 na disk. Vkladanie prebieha takto: ke\u010f vklad\u00e1me \u00fadaje, prich\u00e1dza k n\u00e1m pr\u00fad riadkov, z ktor\u00fdch vytv\u00e1rame ve\u013ek\u00fd spolo\u010dn\u00fd slovn\u00edk. Ak sa slovn\u00edk stane v\u00e4\u010d\u0161\u00edm, ne\u017e je hodnota nastavenia, zap\u00ed\u0161eme aktu\u00e1lny slovn\u00edk na disk, a ostatn\u00e9 riadky \u2013 niekde \u201enavy\u0161e\u201c, ved\u013ea indexov. Nakoniec nikdy neprepo\u010d\u00edtame ve\u013ek\u00fd slovn\u00edk a nedostaneme probl\u00e9my s pam\u00e4\u0165ou. <\/p>\n

Druh\u00e9 nastavenie sa naz\u00fdva low_cardinality_use_single_dictionary_for_part. Predstavte si, \u017ee v predch\u00e1dzaj\u00facej sch\u00e9me, ke\u010f sme vkladali \u00fadaje, n\u00e1\u0161 slovn\u00edk preplnil a my sme ho zap\u00edsali na disk. Vyvst\u00e1va ot\u00e1zka, pre\u010do by sme teraz nemohli vytvori\u0165 e\u0161te jeden presne tak\u00fd ist\u00fd slovn\u00edk? <\/p>\n

Ke\u010f sa prepln\u00ed, znovu ho zap\u00ed\u0161eme na disk a za\u010dneme vytv\u00e1ra\u0165 tret\u00ed. Toto nastavenie presne vyp\u00edna tak\u00fato mo\u017enos\u0165 predvolene. <\/p>\n

V skuto\u010dnosti m\u00f4\u017ee by\u0165 ve\u013ea slovn\u00edkov u\u017eito\u010dn\u00fdch, ak chceme vlo\u017ei\u0165 nejak\u00fa mno\u017einu riadkov, ale n\u00e1hodou sme vlo\u017eili \"odpadky\". Povedzme, \u017ee najprv sme vlo\u017eili zl\u00e9 riadky, a potom sme vlo\u017eili dobr\u00e9. Potom sa slovn\u00edk rozdel\u00ed na ve\u013ea mal\u00fdch slovn\u00edkov. \u010cas\u0165 z nich bude s \"odpadkami\", ale posledn\u00e9 bud\u00fa s dobr\u00fdmi riadkami. A ak pre\u010d\u00edtame, povedzme, iba posledn\u00fa granulu, v\u0161etko bude fungova\u0165 r\u00fdchlo. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nPredt\u00fdm, ne\u017e hovor\u00edme o v\u00fdhod\u00e1ch LowCardinality, hne\u010f poviem, \u017ee sotva dosiahneme zmen\u0161enie \u00fadajov na disku (aj ke\u010f sa to m\u00f4\u017ee sta\u0165), preto\u017ee ClickHouse komprimuje \u00fadaje. Existuje predvolen\u00e1 mo\u017enos\u0165 \u2013 LZ4. M\u00f4\u017ee sa tie\u017e pou\u017ei\u0165 kompresia pomocou ZSTD. Ale oba algoritmy u\u017e realizuj\u00fa slovn\u00edkov\u00e9 kompresie, tak\u017ee n\u00e1\u0161 extern\u00fd slovn\u00edk ClickHouse ve\u013emi nepom\u00f4\u017ee. <\/p>\n

Aby som nebol len re\u010dami, vzal som niektor\u00e9 \u00fadaje z metriky \u2014 String, LowCardinality(String) a Enum \u2014 a ulo\u017eil ich do r\u00f4znych typov \u00fadajov. Vznikli tri st\u013apce, kde je zap\u00edsan\u00fdch jedna miliarda riadkov. V prvom st\u013apci, CodePage, je celkovo 62 hodn\u00f4t. A je vidno, \u017ee v LowCardinality(String) sme ich lep\u0161ie skompresovali. String je o nie\u010do hor\u0161\u00ed, ale pravdepodobne to s\u00favis\u00ed s t\u00fdm, \u017ee re\u0165azce s\u00fa kr\u00e1tke, uchov\u00e1vame ich d\u013a\u017eky, a tie zaberaj\u00fa ve\u013ea miesta, \u0165a\u017eko sa komprimuj\u00fa. <\/p>\n

Ak vezmeme PhoneModel, je ich 48 tis\u00edc \u2014 u\u017e viac, a rozdiely medzi String a LowCardinality(String) s\u00fa takmer \u017eiadne. Pri URL sme \u00fasporu dosiahli len 2 GB \u2014 mysl\u00edm, \u017ee na to sa netreba spolieha\u0165. <\/p>\n

Hodnotenie r\u00fdchlosti pr\u00e1ce<\/h3>\n

\n\"Optimaliz\u00e1cia
\nOdkaz zo sn\u00edmky<\/a><\/noindex><\/b><\/p>\n

Teraz ohodnot\u00edme r\u00fdchlos\u0165 pr\u00e1ce. Na jej hodnotenie som pou\u017eil dataset s popisom j\u00e1zd tax\u00edkom v New Yorku. Je je dostupn\u00fd<\/a><\/noindex> na GitHub. Obsahuje nie\u010do viac ne\u017e miliardu j\u00e1zd. Zobrazuje umiestnenie, \u010das za\u010diatku a ukon\u010denia jazdy, sp\u00f4sob platby, po\u010det pasa\u017eierov a dokonca aj typ tax\u00edka \u2014 zelen\u00fd, \u017elt\u00fd alebo Uber. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nPrv\u00fd dotaz som urobil celkom jednoduch\u00fd \u2014 sp\u00fdtal som sa, kde sa naj\u010dastej\u0161ie objedn\u00e1vaj\u00fa tax\u00edky. Na to treba vzia\u0165 umiestnenie, odkia\u013e sa objedn\u00e1valo, urobi\u0165 na \u0148om GROUP BY a spo\u010d\u00edta\u0165 funkciu count. Tu ClickHouse nie\u010do vyprodukuje. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nNa meranie r\u00fdchlosti spracovania dotazu som vytvoril tri tabu\u013eky s rovnak\u00fdmi \u00fadajmi, ale pre na\u0161u \u0161tartovaciu lokalitu som pou\u017eil tri r\u00f4zne typy \u00fadajov \u2014 String, LowCardinality a Enum. LowCardinality a Enum boli p\u00e4\u0165kr\u00e1t r\u00fdchlej\u0161ie ako String. Enum je r\u00fdchlej\u0161\u00ed, preto\u017ee pracuje s \u010d\u00edslami. LowCardinality \u2014 preto\u017ee bola implementovan\u00e1 optimaliz\u00e1cia GROUP BY. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nPo\u010fme e\u0161te skomplikova\u0165 dotaz \u2014 op\u00fdtajme sa, kde sa nach\u00e1dza najpopul\u00e1rnej\u0161\u00ed park v New Yorku. Op\u00e4\u0165 to budeme mera\u0165 pod\u013ea toho, kde sa naj\u010dastej\u0161ie objedn\u00e1vaj\u00fa tax\u00edky, ale filtrujeme len tie umiestnenia, kde je slovo \u201epark\u201c. Taktie\u017e prid\u00e1me funkciu like. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nPozer\u00e1me na \u010das \u2014 vid\u00edme, \u017ee Enum zrazu za\u010dal spoma\u013eova\u0165. A pracuje e\u0161te pomal\u0161ie ako \u0161tandardn\u00fd typ \u00fadajov String. To sa deje, preto\u017ee funkcia like nie je v\u00f4bec optimalizovan\u00e1 pre Enum. Mus\u00edme prev\u00e1dza\u0165 na\u0161e re\u0165azce z Enum na oby\u010dajn\u00e9 re\u0165azce \u2014 vykon\u00e1vame viac pr\u00e1ce. LowCardinality(String) tie\u017e nie je predvolene optimalizovan\u00fd, ale tam like funguje nad slovn\u00edkom, tak\u017ee sa po\u017eiadavka ur\u00fdchli v porovnan\u00ed so String. <\/p>\n

Pri pr\u00e1ci s Enum existuje v\u00e4\u010d\u0161\u00ed probl\u00e9m. Ak ho chceme optimalizova\u0165, mus\u00edme to urobi\u0165 na ka\u017edom mieste v k\u00f3de. Predpokladajme, \u017ee sme nap\u00edsali nov\u00fa funkciu \u2014 ur\u010dite mus\u00edme vymyslie\u0165 optimaliz\u00e1ciu pre Enum. A v LowCardinality je v\u0161etko optimalizovan\u00e9 predvolene.<\/p>\n

\"Optimaliz\u00e1cia
\n
\nPozrime sa na posledn\u00fd dotaz, ktor\u00fd je umel\u00fd. Jednoducho vypo\u010d\u00edtame hash funkciu na\u0161ej lokality. Hash funkcia je dos\u0165 pomal\u00fd dotaz, po\u010d\u00edta sa dlho, preto sa v\u0161etko spomal\u00ed trikr\u00e1t.<\/p>\n

\"Optimaliz\u00e1cia
\n
\nLowCardinality st\u00e1le funguje r\u00fdchlej\u0161ie, hoci tu nie je filtrovanie. To sa deje preto, lebo na\u0161e funkcie pracuj\u00fa iba so slovn\u00edkom. Funkcia na v\u00fdpo\u010det hash m\u00e1 jeden argument \u2014 dok\u00e1\u017ee spracov\u00e1va\u0165 menej \u00fadajov a tie\u017e m\u00f4\u017ee vr\u00e1ti\u0165 LowCardinality. <\/p>\n

\"Optimaliz\u00e1cia
\n
\nN\u00e1\u0161 glob\u00e1lny pl\u00e1n je dosiahnu\u0165 r\u00fdchlos\u0165 v\u00fdkonu nie ni\u017e\u0161iu ako pri String vo v\u0161etk\u00fdch pr\u00edpadoch a zachova\u0165 zr\u00fdchlenie. A mo\u017eno raz v bud\u00facnosti nahrad\u00edme String LowCardinality, aktualizujete ClickHouse a v\u0161etko v\u00e1m p\u00f4jde o nie\u010do r\u00fdchlej\u0161ie.
\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\udd47Optimaliz\u00e1cia re\u0165azcov v ClickHouse. Predn\u00e1\u0161ka Yandexu | ProHoster","description":"Analytick\u00e1 DB ClickHouse spracov\u00e1va mno\u017estvo r\u00f4znych re\u0165azcov, pri\u010dom spotreb\u00fava zdroje. Na ur\u00fdchlenie pr\u00e1ce syst\u00e9mu sa neust\u00e1le prid\u00e1vaj\u00fa nov\u00e9 optimaliz\u00e1cie.","canonical_url":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/optimizacziya-strok-v-clickhouse-doklad-yandeksa","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sk_SK","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\/sk\/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\/sk\/wp-json\/wp\/v2\/posts\/74737","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/comments?post=74737"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/74737\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media\/74738"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media?parent=74737"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/categories?post=74737"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/tags?post=74737"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}