ClickHouse ã¯ç¹æ®ãªã·ã¹ãã ã§ããããã䜿çšããå Žåã¯ãã®ã¢ãŒããã¯ãã£ã®ç¹åŸŽãèæ ®ããããšãéèŠã§ãã ãã®ã¬ããŒãã§ã¯ãAlexey ããéå¹ççãªäœæ¥ã«ã€ãªããå¯èœæ§ããããClickHouse ã䜿çšããéã®ããããééãã®äŸã«ã€ããŠèª¬æããŸãã å®éã®äŸã§ã¯ãXNUMX ã€ãŸãã¯å¥ã®ããŒã¿åŠçã¹ããŒã ãéžæããããšã§ããã©ãŒãã³ã¹ãæ¡éãã«å€åããå¯èœæ§ãããããšã瀺ããŸãã
ããã«ã¡ã¯ãã¿ããªïŒ ç§ã®åå㯠AlexeyãClickHouse ãäœã£ãŠããŸãã
ãŸããæ¥ãã§ããŸãããä»æ¥ã¯ ClickHouse ãäœã§ãããã«ã€ããŠã¯èª¬æããŸããã æ£çŽã«èšããšããã飜ããŸããã ãããäœãªã®ããæ¯å話ããŸãã ãããŠãããã誰ãããã§ã«ç¥ã£ãŠããŸãã
代ããã«ãã©ã®ãããªééããèããããããã€ãŸã ClickHouse ãã©ã®ããã«èª€ã£ãŠäœ¿çšãããã説æããŸãã å®éãç§ãã¡ã¯ ClickHouse ãã·ã³ãã«ã§äŸ¿å©ã§ãããã«äœ¿ããã·ã¹ãã ãšããŠéçºããŠããã®ã§ãå¿é ããå¿ èŠã¯ãããŸããã ã€ã³ã¹ããŒã«ããŸããããåé¡ãããŸããã
ãã ãããã®ã·ã¹ãã ã¯ç¹æ®ã§ããããã®ã·ã¹ãã ãå¿«é©ãŸãŒã³ããå€ããŠããŸããããªç°åžžãªäœ¿çšäŸã«ç°¡åã«ééããå¯èœæ§ãããããšãèæ ®ããå¿ èŠããããŸãã
ã§ã¯ãã©ããªçæãããã®ã§ããããïŒ äž»ã«åœããåã®ããšã話ããŸãã ãã¹ãŠã¯èª°ã«ãšã£ãŠãæããã§ããã誰ãããã¹ãŠãç解ããŠãããèªåããšãŠãè³¢ãããšãå¬ããæããç解ããŠããªã人ã¯äœãæ°ããããšãåŠã¶ã§ãããã
æåã®æãåçŽãªäŸã¯ãæ®å¿µãªããé »ç¹ã«çºçããŸãããå°ããªãããã䌎ãå€æ°ã®æ¿å ¥ãã€ãŸãå€æ°ã®å°ããªæ¿å ¥ã§ãã
ClickHouse ãæ¿å ¥ãå®è¡ããæ¹æ³ãèæ ®ãããšãXNUMX åã®ãªã¯ãšã¹ãã§å°ãªããšã XNUMX ãã©ãã€ãã®ããŒã¿ãéä¿¡ã§ããŸãã åé¡ãããªãã
ãããŠãå žåçãªããã©ãŒãã³ã¹ãã©ã®ãããªãã®ã«ãªãããèŠãŠã¿ãŸãããã ããšãã°ãYandex.Metrica ããŒã¿ããã®ããŒãã«ããããŸãã ãããã 105 ããã€ãã®ã³ã©ã ã éå§çž®ã§ 700 ãã€ãã ãããŠãXNUMX äžè¡ãã€ãããã§é©åã«æ¿å ¥ããŠãããŸãã
MergeTree ãããŒãã«ã«æ¿å ¥ãããšã400 ç§ããã 000 äžè¡ãåŠçãããããšãããããŸãã çŽ æŽãããã ã¬ããªã±ãŒããããããŒãã«ã§ã¯å°ãå°ãããªããXNUMX ç§ãããçŽ XNUMX è¡ã«ãªããŸãã
ãŸããã¯ã©ãŒã©ã ã®æ¿å ¥ãæå¹ã«ãããšãããã©ãŒãã³ã¹ã¯å°ãäœäžããŸããã250 ç§ããã 000 ã¿ãŒã ãšããååãªããã©ãŒãã³ã¹ãåŸãããŸãã ã¯ã©ãŒã©ã ã®æ¿å ¥ã¯ãClickHouse* ã®ææžåãããŠããªãæ©èœã§ãã
â»2020幎çŸåšã
äœãæªãããšããããã©ããªããŸããïŒ MergeTree ããŒãã«ã« 59 è¡ãæ¿å ¥ãããšã10 ç§ããã 000 è¡ãååŸãããŸãã ããã¯6åé ãã§ãã ReplicatedMergeTree ã§ã¯ã2 ç§ããã XNUMX è¡ã ã¯ã©ãŒã©ã ããªã³ã«ãªã£ãŠããå ŽåãXNUMX ç§ããã XNUMX è¡ã®é床ã«ãªããŸãã ç§ã®æèŠã§ã¯ãããã¯ããçš®ã®çµ¶å¯Ÿçãªãã ããªãããšã§ãã ã©ãããããããªã«ãã£ããã§ãããã§ããïŒ ç§ã®Tã·ã£ãã«ããClickHouseã®é床ãèœãšããªãããã«ãšæžããŠãããŸãã ããããããã«ãããããããããã¯æã èµ·ãããŸãã
å®ã¯ããããç§ãã¡ã®æ¬ ç¹ãªã®ã§ãã ãã¹ãŠãããŸãæ©èœãããããšãç°¡åã ã£ãã®ã§ãããããã¯ãªããŸããã§ããã ã¹ã¯ãªããã§ã¯ãããå¿ èŠã§ã¯ãªãã£ããããå®è¡ããŸããã§ããã ç§ãã¡ã¯ãã§ã«èãæã£ãŠããŸããã å ¥ãå£ã§ããããåãåããŸããããåé¡ã¯ãããŸããã ãããæ¿å ¥ãããšããã¹ãŠãæ£åžžã«åäœããŸãã ãããããã¡ãããããããçš®é¡ã®ã·ããªãªãå¯èœã§ãã ããšãã°ãããŒã¿ãçæããããµãŒããŒãå€æ°ããå Žåã§ãã ãŸããããŒã¿ã®æ¿å ¥é »åºŠã¯ããã»ã©é«ããããŸããããããã§ãé »ç¹ã«æ¿å ¥ãè¡ãããããšã«ãªããŸãã ãããŠããããäœãšãåé¿ããå¿ èŠããããŸãã
æè¡çãªèŠ³ç¹ããèŠããšãéèŠãªã®ã¯ãClickHouse ã§æ¿å ¥ãè¡ããšãã«ãããŒã¿ãã©ã® memtable ã«ãå ¥ããªããšããããšã§ãã ãã°ã memTable ãååšããªããããå®éã®ãã°æ§é MergeTree ã¯ãªããåãªã MergeTree ã§ãã ãã§ã«åã«é 眮ãããŠããããŒã¿ããã ã¡ã«ãã¡ã€ã« ã·ã¹ãã ã«æžã蟌ãã ãã§ãã ãŸããåã 100 åããå Žåã¯ã200 ãè¶ ãããã¡ã€ã«ãå¥ã®ãã£ã¬ã¯ããªã«æžã蟌ãå¿ èŠããããŸãã ããã¯ãã¹ãŠéåžžã«é¢åã§ãã
ãããŠãäœããã®æ¹æ³ã§ ClickHouse ã«ããŒã¿ãèšé²ããå¿ èŠãããç¶æ³ã®å Žåããã©ããã£ãŠæ£ããè¡ãã®ã§ãã?ããšããçåãçããŸãã
æ¹æ³ 1. ãããæãç°¡åãªæ¹æ³ã§ãã äœããã®åæ£ãã¥ãŒã䜿çšããŸãã ããšãã°ãã«ãã«ã Kafka ããããŒã¿ãæœåºããXNUMX ç§ã« XNUMX åãããåŠçããã ãã§ãã ãããŠããã¹ãŠãããŸããããé²é³ãããšããã¹ãŠãããŸããããŸãã
æ¬ ç¹ã¯ãKafka ããŸãããã°ãåæ£ã·ã¹ãã ã§ããããšã§ãã ããªãã®äŒç€Ÿã«ãã§ã« Kafka ãå°å ¥ãããŠããå Žåãç解ã§ããŸãã ããã§ããã䟿å©ã§ããã ãããããããååšããªãå Žåã¯ãããã«å¥ã®åæ£ã·ã¹ãã ããããžã§ã¯ãã«ãã©ãã°ããåã«ãXNUMX åèããå¿ èŠããããŸãã ãããã£ãŠã代æ¿æ¡ãæ€èšãã䟡å€ããããŸãã
æ¹æ³ 2. ããã¯æãªããã®ä»£æ¿æ段ã§ãããšåæã«ãéåžžã«ç°¡åã§ãã ãã°ãçæããäœããã®ãµãŒããŒã¯ãããŸããã ãããŠããã°ããã¡ã€ã«ã«æžã蟌ãã ãã§ãã ããšãã°ãXNUMX ç§ã« XNUMX åããã®ãã¡ã€ã«ã®ååãå€æŽããæ°ãããã¡ã€ã«ãåãé¢ããŸãã ãããŠãcron ãŸãã¯äœããã®ããŒã¢ã³ãä»ããŠå¥ã®ã¹ã¯ãªãããæãå€ããã¡ã€ã«ãååŸããClickHouse ã«æžã蟌ã¿ãŸãã ãã°ã XNUMX ç§ã« XNUMX åèšé²ããã°ããã¹ãŠåé¡ãããŸããã
ãã ãããã®æ¹æ³ã®æ¬ ç¹ã¯ããã°ãçæãããŠãããµãŒããŒãã©ããã«æ¶ããŠããŸããšãããŒã¿ãæ¶ããŠããŸããšããããšã§ãã
æ¹æ³ 3. ãã XNUMX ã€ã®èå³æ·±ãæ¹æ³ããããŸããããã¯äžæãã¡ã€ã«ããŸã£ããå¿ èŠãšããŸããã ããšãã°ãããçš®ã®åºåã¹ãããŒããããŒã¿ãçæãããã®ä»ã®èå³æ·±ãããŒã¢ã³ããããšããŸãã ãŸãã倧éã®ããŒã¿ã RAM ããããã¡ã«çŽæ¥èç©ã§ããŸãã ãããŠãååãªæéãçµéãããããã®ãããã¡ãèã«çœ®ããŠãæ°ãããããã¡ãäœæããå¥ã®ã¹ã¬ããã§ããã§ã«èç©ãããŠãããã®ã ClickHouse ã«æ¿å ¥ããŸãã
äžæ¹ãkill -9ã§ã¯ããŒã¿ãæ¶ããŸãã ãµãŒããŒãã¯ã©ãã·ã¥ãããšããã®ããŒã¿ã倱ãããŸãã ãã XNUMX ã€ã®åé¡ã¯ãããŒã¿ããŒã¹ã«æžã蟌ãããšãã§ããªãã£ãå ŽåãããŒã¿ã RAM ã«èç©ãããããšã§ãã ãããŠãRAMã足ããªããªãããåã«ããŒã¿ã倱ããããã®ã©ã¡ããã§ãã
æ¹æ³ 4. ãã XNUMX ã€ã®èå³æ·±ãæ¹æ³ã§ãã äœããã®ãµãŒããŒããã»ã¹ãããã®ã§ããããïŒ ãŸããClickHouse ã«ããŒã¿ãããã«éä¿¡ã§ããŸããããã㯠XNUMX åã®æ¥ç¶ã§å®è¡ãããŸãã ããšãã°ãtransfer-encoding: chunked with insert ã䜿çšã㊠http ãªã¯ãšã¹ããéä¿¡ããŸããã ãŸãããã£ã³ã¯ãçæãããããšã¯ããã»ã©ãŸãã§ã¯ãªããããåè¡ãéä¿¡ã§ããŸããããã®ããŒã¿ããã¬ãŒã åããããã®ãªãŒããŒãããã¯çºçããŸãã
ãã ãããã®å ŽåãããŒã¿ã¯ããã« ClickHouse ã«éä¿¡ãããŸãã ãããŠãClickHouse èªäœããããããããã¡ãªã³ã°ããŸãã
ããããåé¡ãçããŸãã ããã§ãäžå®å šãªæ¿å ¥ãšãªããããããã»ã¹ã匷å¶çµäºãããå Žåã ClickHouse ããã»ã¹ã匷å¶çµäºãããå Žåãå«ããŠãããŒã¿ã倱ãããŸãã ãŸããClickHouse ã§ã¯ãè¡ã®ãµã€ãºãç¹å®ã®æå®ããããããå€ãŸã§ã¯æ¿å ¥ãã¢ãããã¯ã«ãªããŸãã åççã«ã¯ãããã¯èå³æ·±ãæ¹æ³ã§ãã ã䜿çšã§ããŸãã
æ¹æ³ 5. ãã XNUMX ã€ã®èå³æ·±ãæ¹æ³ããããŸãã ããã¯ãã³ãã¥ããã£ãéçºããããŒã¿ã®ãããåŠççšã®ãµãŒããŒã®äžçš®ã§ãã èªåã§èª¿ã¹ãããã§ã¯ãªãã®ã§ãäœã®ä¿èšŒãã§ããŸããã ãã ããClickHouse èªäœã«ã€ããŠã¯ä¿èšŒã¯ãããŸããã ããããªãŒãã³ãœãŒã¹ã§ãããäžæ¹ã§ãç§ãã¡ãæäŸããããšããŠããå質åºæºã«æ £ããŠãããããããŸããã ããããããã«ã€ããŠã¯ãããããŸãããGitHub ã«ã¢ã¯ã»ã¹ããŠãã³ãŒããèŠãŠãã ããã ããããããæ®éã®ããšãæžããã®ãããããªãã
â»2020幎çŸåšãæ€èšã«å ããã¹ã
æ¹æ³ 6. ãã XNUMX ã€ã®æ¹æ³ã¯ããããã¡ ããŒãã«ã䜿çšããããšã§ãã ãã®æ¹æ³ã®å©ç¹ã¯ã䜿çšãéå§ããã®ãéåžžã«ç°¡åã§ããããšã§ãã ãããã¡ ããŒãã«ãäœæããããã«æ¿å ¥ããŸãã
æ¬ ç¹ã¯ãåé¡ãå®å šã«è§£æ±ºãããŠããªãããšã§ãã MergeTree ã®ãããªã¬ãŒã㧠10 ç§ããã 000 ãããã§ããŒã¿ãã°ã«ãŒãåããå¿ èŠãããå Žåããããã¡ ããŒãã«ã®ã¬ãŒãã§ã¯ãå°ãªããšã XNUMX ç§ãããæ倧æ°åãŸã§ã°ã«ãŒãåããå¿ èŠããããŸãã XNUMX ç§ããã XNUMX ãè¶ ããå Žåã¯ããŸã åé¡ããããŸãã ããããããã§æ¿å ¥ãããšãXNUMX ç§ããã XNUMX äžè¡ã«ãªãããšãããããŸããã ãããŠãããã¯ãã§ã«ããªãéãããŒã¿ã«åºã¥ããŠããŸãã
ãŸãããããã¡ããŒãã«ã«ã¯ãã°ããããŸããã ãŸãããµãŒããŒã«åé¡ãããå ŽåãããŒã¿ã¯å€±ãããŸãã
ããŸãã«ãæè¿ ClickHouse 㧠Kafka ããããŒã¿ãååŸããæ©äŒãåŸãŸããã Kafka ãšããããŒãã« ãšã³ãžã³ããããŸãã ãã äœæããã ãã§ãã ãããŠãå®äœåãããè¡šçŸãããã«æããããšãã§ããŸãã ãã®å Žåãããèªäœã Kafka ããããŒã¿ãæœåºããå¿ èŠãªããŒãã«ã«æ¿å ¥ããŸãã
ãããŠããã®æ©äŒã§ç¹ã«å¬ããã®ã¯ããããè¡ã£ãã®ãç§ãã¡ã§ã¯ãªããšããããšã§ãã ããã¯ã³ãã¥ããã£æ©èœã§ãã ç§ããã³ãã¥ããã£æ©èœããšèšããšãã¯ã決ããŠè»œèããã€ããã¯ãããŸããã ã³ãŒããèªãã§ã¬ãã¥ãŒããŸããããåé¡ãªãåäœããã¯ãã§ãã
â»2020幎çŸåšãåæ§ã®ãµããŒããç»å ŽããŠããŸãã
ããŒã¿ãæ¿å ¥ãããšãã«ä»ã«äžäŸ¿ãŸãã¯äºæããªãããšãèãããããã®ã¯äœã§ãã? å€ã®æ¿å ¥ãªã¯ãšã¹ããäœæããå€ã«ããã€ãã®èšç®åŒãèšè¿°ããå Žåã ããšãã°ãnow() ãèšç®åŒã§ãã ãã®å ŽåãClickHouse ã¯ãããã®åŒã®ã€ã³ã¿ãŒããªã¿ãåè¡ã§èµ·åããå¿ èŠããããããã©ãŒãã³ã¹ãæ¡éãã«äœäžããŸãã ããã¯é¿ããæ¹ãè¯ãã§ãããã
* çŸæç¹ã§ã¯ãåé¡ã¯å®å šã«è§£æ±ºãããŠãããVALUES ã§åŒã䜿çšãããšãã«ããã©ãŒãã³ã¹ãäœäžããããšã¯ãªããªããŸããã
ãã XNUMX ã€ã®äŸã¯ãå€æ°ã®ããŒãã£ã·ã§ã³ã«å±ãã XNUMX ã€ã®ãããã«ããŒã¿ããããšãã«åé¡ãçºçããå¯èœæ§ãããå Žåã§ãã ããã©ã«ãã§ã¯ãClickHouse ããŒãã£ã·ã§ã³ã¯æããšã§ãã ãŸããXNUMX äžè¡ã®ããããæ¿å ¥ããæ°å¹Žåã®ããŒã¿ãããå Žåãããã«ã¯æ°åã®ããŒãã£ã·ã§ã³ãååšããããšã«ãªããŸãã ããã¯ãå éšã§åžžã«æåã«ããŒãã£ã·ã§ã³ã«åå²ãããããããµã€ãºãæ°ååå°ããããããååšãããšããäºå®ãšåçã§ãã
* æè¿ãClickHouse ã¯å®éšã¢ãŒãã§ããã£ã³ã¯ãšå è¡æžã蟌ã¿ãã°ãåãã RAM å ã®ãã£ã³ã¯ã®ã³ã³ãã¯ã ãã©ãŒãããã®ãµããŒããè¿œå ããŸãããããã«ãããåé¡ã¯ã»ãŒå®å šã«è§£æ±ºãããŸããã
次ã«ãXNUMX çªç®ã®ã¿ã€ãã®åé¡ã§ããããŒã¿åãèŠãŠã¿ãŸãããã
ããŒã¿åæå®ã¯å³å¯ãŸãã¯æååã«ããããšãã§ããŸãã String ã¯ããããååŸããŠããã¹ãŠã®ãã£ãŒã«ããæåååã§ãããšå®£èšãããšãã®ãã®ã§ãã ããã¯æäœã ã ãããè¡ãå¿ èŠã¯ãããŸããã
ãã£ãŒã«ããæååãããããšãäŒãããå Žåã«ããããæ£ããè¡ãæ¹æ³ãèããŠã¿ãŸããããClickHouse ã«ãããèªåçã«ç解ãããŸããç§ã¯æ°ã«ããŸããã ããããããã§ãåªåãã䟡å€ã¯ãããŸãã
ããšãã°ãIP ã¢ãã¬ã¹ããããŸãã ããã±ãŒã¹ã§ã¯ããããæååãšããŠä¿åããŸããã ããšãã°ã192.168.1.1ã ãŸããå¥ã®ã±ãŒã¹ã§ã¯ãUInt32* åã®æ°å€ã«ãªããŸãã IPv32 ã¢ãã¬ã¹ã«ã¯ 4 ãããã§ååã§ãã
ãŸããå¥åŠãªããšã«ãããŒã¿ã¯ã»ãŒåçã«å§çž®ãããŸãã ãã¡ããéãã¯ããã§ãããããããã»ã©å€§ããªéãã¯ãããŸããã ãããã£ãŠããã£ã¹ã¯ I/O ã«ç¹å¥ãªåé¡ã¯ãããŸããã
ãã ããããã»ããµæéãšã¯ãšãªå®è¡æéã«ã¯å€§ããªéãããããŸãã
äžæã® IP ã¢ãã¬ã¹ãæ°å€ãšããŠä¿åãããŠããå Žåã¯ããã®æ°ãæ°ããŠã¿ãŸãããã ããã¯ã137 ç§ããã 37 å 4 äžè¡ã«çžåœããŸãã åããã®ãæååã®åœ¢åŒã®å ŽåãXNUMX ç§ããã XNUMX äžè¡ã«ãªããŸãã ãªããã®ãããªå¶ç¶ãèµ·ãã£ãã®ãã¯ããããŸããã ç§ã¯ãããã®èŠæ±ãèªåã§å®è¡ããŸããã ãã ããããã§ãçŽ XNUMX åé ããªããŸãã
ãããŠããã£ã¹ã¯å®¹éã®éããèšç®ãããšããã¯ãéãããããŸãã åºæã® IP ã¢ãã¬ã¹ãéåžžã«å€ãååšããããããã®å·®ã¯çŽ XNUMX åã® XNUMX ã§ãã ãããŠãå°æ°ã®ç°ãªãæå³ãæã€è¡ãããå Žåããããã¯èŸæžã«åŸã£ãŠç°¡åã«ã»ãŒåãããªã¥ãŒã ã«å§çž®ãããŸãã
ãããŠãXNUMXåã®ã¿ã€ã å·®ã¯éè·¯äžã«ããããã§ã¯ãããŸããã ãã¡ããæ°ã«ããªããããããŸããããããã»ã©ã®éããèŠããšæ²ãããªããŸãã
ããŸããŸãªã±ãŒã¹ãèŠãŠã¿ãŸãããã
1. ç°ãªãäžæã®å€ãã»ãšãã©ãªãå Žåã® XNUMX ã€ã®ã±ãŒã¹ã ãã®å Žåããããããåç¥ã§ãããã©ã® DBMS ã«ã䜿çšã§ããç°¡åãªæ¹æ³ã䜿çšããŸãã ããã¯ãã¹ãŠãClickHouse ã ãã§ãªãçã«ããªã£ãŠããŸãã æ°å€èå¥åãããŒã¿ããŒã¹ã«æžã蟌ãã ãã§ãã ãŸããã¢ããªã±ãŒã·ã§ã³åŽã§æååã«å€æããããå ã«æ»ãããšãã§ããŸãã
ããšãã°ãå°åããããšããŸãã ãããŠãããæååãšããŠä¿åããããšããŠããŸãã ãããŠããã«ã¯ãã¢ã¹ã¯ã¯ãšã¢ã¹ã¯ã¯å°æ¹ããšæžãããã§ãããã ãããŠããã¢ã¹ã¯ã¯ããšæžãããŠããã®ãèŠããšãäœã§ããªãã®ã§ãããã¢ã¹ã¯ã¯ã«ãªããšããªããå®å šã«æ²ãããªããŸãã ããã¯ãã€ãæ°ã§ãã
代ããã«ãUlnt32 ãš 250 ãšããæ°åãæžãçããã ãã§ããYandex ã«ã¯ 250 ããããŸãããå®éã®æ°åã¯ç°ãªãå ŽåããããŸãã 念ã®ããèšã£ãŠãããŸãããClickHouse ã«ã¯ãžãªããŒã¹ãæäœããæ©èœãçµã¿èŸŒãŸããŠããŸãã éå±€çãªãã®ãå«ãé åãå«ããã£ã¬ã¯ããªãæžãçããã ãã§ããã€ãŸããã¢ã¹ã¯ã¯ãã¢ã¹ã¯ã¯å°åãããã³å¿ èŠãªãã®ããã¹ãŠååšããŸãã ãªã¯ãšã¹ãã¬ãã«ã§å€æããããšãã§ããŸãã
4 çªç®ã®ãªãã·ã§ã³ã¯ã»ãŒåãã§ãããClickHouse å ã§ãµããŒããããŠããŸãã ãã㯠Enum ããŒã¿åã§ãã Enum å ã«å¿ èŠãªå€ããã¹ãŠæžã蟌ãã ãã§ãã ããšãã°ãããã€ã¹ã®çš®é¡ãšããã«æžã蟌ã¿ãŸã: ãã¹ã¯ããããã¢ãã€ã«ãã¿ãã¬ããããã¬ãã åèš XNUMX ã€ã®ãªãã·ã§ã³ããããŸãã
æ¬ ç¹ã¯ãå®æçã«å€æŽããå¿ èŠãããããšã§ãã ãªãã·ã§ã³ãXNUMXã€è¿œå ãããã ãã§ãã ããŒãã«ãå€æŽããŠã¿ãŸãããã å®éãClickHouse ã®ããŒãã«ã®å€æŽã¯ç¡æã§ãã Enum ã®å Žåã¯ãã£ã¹ã¯äžã®ããŒã¿ãå€æŽãããªããããç¹ã«ç¡æã§ãã ãã ããalter ã¯ããŒãã«ã®ããã¯* ãååŸããããããã¹ãŠã®éžæãå®è¡ããããŸã§åŸ æ©ããå¿ èŠããããŸãã ãããŠããã®å€æŽãå®è¡ãããåŸã«ã®ã¿ãã€ãŸãããŸã ããã€ãã®äžéœåãååšããŸãã
* ClickHouse ã®ææ°ããŒãžã§ã³ã§ã¯ãALTER ã¯å®å šã«ãã³ããããã³ã°ã«ãªã£ãŠããŸãã
ClickHouse ã«ãšã£ãŠéåžžã«ãŠããŒã¯ãªãã XNUMX ã€ã®ãªãã·ã§ã³ã¯ãå€éšèŸæžã®æ¥ç¶ã§ãã ClickHouse ã«æ°åãæžã蟌ãã§ãéœåã®è¯ãã·ã¹ãã ã«ãã£ã¬ã¯ããªãä¿åã§ããŸãã ããšãã°ãMySQLãMongoãPostgres ã䜿çšã§ããŸãã ãã®ããŒã¿ã http çµç±ã§éä¿¡ããç¬èªââã®ãã€ã¯ããµãŒãã¹ãäœæããããšãã§ããŸãã ãããŠãClickHouse ã¬ãã«ã§ããã®ããŒã¿ãæ°å€ããæååã«å€æããé¢æ°ãäœæããŸãã
ããã¯ãå€éšããŒãã«ã§çµåãå®è¡ããç¹æ®ãªæ¹æ³ã§ãããéåžžã«å¹ççã§ãã ãããŠãéžæè¢ã¯ XNUMX ã€ãããŸãã äžå®æœåœ¢æ ã§ã¯ããã®ããŒã¿ã¯å®å šã«ãã£ãã·ã¥ãããïŒå ã«å®å šã«ååšããããé »åºŠã§æŽæ°ãããã ãŸããå¥ã®ãªãã·ã§ã³ã§ã¯ããã®ããŒã¿ã RAM ã«åãŸããªãå Žåã¯ãéšåçã«ãã£ãã·ã¥ããããšãã§ããŸãã
ããã«äŸã瀺ããŸãã Yandex.Direct ããããŸãã ãããŠåºåäŒç€ŸãšãããŒããããŸãã åºåäŒç€Ÿã¯ããããæ°åäžç€ŸãããšæãããŸãã ãããŠãããã¯RAMã«ã»ãŒåãŸããŸãã ãããŠãäœååãã®ãããŒããããŸããããããã¯åãŸããŸããã ãããŠãMySQL ã®ãã£ãã·ã¥ãããèŸæžã䜿çšããŸãã
å¯äžã®åé¡ã¯ããããçã 100% ã«è¿ãå Žåããã£ãã·ã¥ãããèŸæžãæ£åžžã«åäœããããšã§ãã ãããå°ããå Žåã¯ãããŒã¿ã®ãããããšã«ã¯ãšãªãåŠçãããšãã«ãå®éã«ã¯äžè¶³ããŠããããŒãååŸã㊠MySQL ããããŒã¿ãååŸããå¿ èŠããããŸãã ClickHouse ã«ã€ããŠã¯ãé床ãäœäžããããšã¯ãªããšãŸã ä¿èšŒã§ããŸããä»ã®ã·ã¹ãã ã«ã€ããŠã¯è©±ããŸããã
ããã«ãèŸæžã¯ãClickHouse ã®ããŒã¿ãé¡ã£ãŠæŽæ°ããéåžžã«ç°¡åãªæ¹æ³ã§ãã ã€ãŸããåºåäŒç€Ÿã«é¢ããã¬ããŒããããããŠãŒã¶ãŒãåºåäŒç€Ÿãå€æŽããã ãã§ããã¹ãŠã®å€ãããŒã¿ããã¹ãŠã®ã¬ããŒãã§ãã®ããŒã¿ãå€æŽãããŸããã è¡ãããŒãã«ã«çŽæ¥æžã蟌ãå Žåãè¡ãæŽæ°ããããšã¯ã§ããŸããã
æååã®èå¥åãã©ãã§ååŸããã°ãããããããªãå Žåã®å¥ã®æ¹æ³ã åçŽã«ããã·ã¥åããããšãã§ããŸãã ããã«ãæãç°¡åãªãªãã·ã§ã³ã¯ 64 ããã ããã·ã¥ãååŸããããšã§ãã
å¯äžã®åé¡ã¯ãããã·ã¥ã 64 ãããã®å Žåãã»ãŒç¢ºå®ã«è¡çªãçºçããããšã§ãã ãªããªããããã«XNUMXåã®è¡ãããã°ããã®ç¢ºçã¯ãã§ã«é¡èã«ãªãããã§ãã
ãã®ããã«åºåäŒç€Ÿã®ååãããã·ã¥åããã®ã¯ããŸãè¯ãããšã§ã¯ãããŸããã ç°ãªãäŒæ¥ã®åºåãã£ã³ããŒã³ãæ··ãã£ãŠããŸããšãèš³ã®åãããªããã®ã«ãªã£ãŠããŸããŸãã
ãããŠãç°¡åãªããªãã¯ããããŸãã 確ãã«ãããã¯æ·±å»ãªããŒã¿ã«ã¯ããŸãé©ããŠããŸããããããã»ã©æ·±å»ã§ãªãå Žåã¯ãã¯ã©ã€ã¢ã³ãèå¥åãèŸæžããŒã«è¿œå ããã ãã§ãã ãããŠãè¡çªãçºçããŸããããã㯠XNUMX ã€ã®ã¯ã©ã€ã¢ã³ãå ã§ã®ã¿ã§ãã ãããŠãYandex.Metrica ã®ãªã³ã¯ ãããã«ãã®ã¡ãœããã䜿çšããŸãã ããã« URL ããããããã·ã¥ãä¿åããŸãã ãããŠãã¡ãããè¡çªãããããšãæ¿ç¥ããŠããŸãã ããããããŒãžã衚瀺ããããšãã«ãXNUMX 人ã®ãŠãŒã¶ãŒã® XNUMX ããŒãžã«ããã€ãã® URL ããã£ã€ããŠããŠãããã«æ°ã¥ã確çã¯ç¡èŠã§ããŸãã
ããŸãã«ãå€ãã®æäœã§ã¯ããã·ã¥ã ãã§ååã§ãããæååèªäœãã©ãã«ãä¿åããå¿ èŠã¯ãããŸããã
å¥ã®äŸãšããŠã¯ãWeb ãµã€ãã®ãã¡ã€ã³ãªã©ãæååãçãå ŽåããããŸãã ãã®ãŸãŸä¿ç®¡ã§ããŸãã ããšãã°ããã©ãŠã¶èšèªã® ru 㯠2 ãã€ãã§ãã ãã¡ãããã€ãã¯æ¬åœã«æ®å¿µã§ããã2ãã€ãã¯æ®å¿µã§ã¯ãããŸããã®ã§ãå®å¿ãã ããã å¿é ããªãã§ãã®ãŸãŸã«ããŠãããŠãã ããã
ãã XNUMX ã€ã®ã±ãŒã¹ã¯ãéã«ãå€ãã®è¡ãããããã®äžã«å€ãã®åºæã®è¡ããããã»ããã§ããæœåšçã«ç¡å¶éã§ããå Žåã§ãã å žåçãªäŸã¯ãæ€çŽ¢ãã¬ãŒãºã URL ã§ãã ã¿ã€ããã¹ãå«ãèªå¥ãæ€çŽ¢ããŸãã XNUMX æ¥ãããã®äžæã®æ€çŽ¢ãã¬ãŒãºãäœä»¶ãããèŠãŠã¿ãŸãããã ãããŠããããããã¹ãŠã®ã€ãã³ãã®ã»ãŒååã§ããããšãããããŸããã ãã®å ŽåãããŒã¿ãæ£èŠåããèå¥åãæ°ããŠãå¥ã®ããŒãã«ã«å ¥ããå¿ èŠããããšèãããããããŸããã ãããããã®å¿ èŠã¯ãããŸããã ãããã®è¡ã¯ãã®ãŸãŸã«ããŠãããŠãã ããã
åå¥ã«ä¿åãããšçµåãå¿ èŠã«ãªããããäœãèãåºããªãã»ããè¯ãã§ãããã ãããŠããã®çµåã¯ããŸã ã¡ã¢ãªã«åãŸãå Žåã«ã¯ãããããã¡ã¢ãªãžã®ã©ã³ãã ã¢ã¯ã»ã¹ã§ãã é©åããªãå Žåã¯åé¡ãçºçããŸãã
ããŒã¿ãæå®ã®å Žæã«ä¿åãããŠããå Žåã¯ããã¡ã€ã« ã·ã¹ãã ããå¿ èŠãªé åºã§èªã¿åãããã ãã§ããã¹ãŠåé¡ãããŸããã
URL ããã®ä»ã®è€éãªé·ãæååãããå Žåã¯ãäºåã«äœããã®æœåºãèšç®ããŠå¥ã®åã«æžã蟌ãããšãã§ããããšãæ€èšãã䟡å€ããããŸãã
ããšãã°ãURL ã®å Žåããã¡ã€ã³ãåå¥ã«ä¿åã§ããŸãã æ¬åœã«ãã¡ã€ã³ãå¿ èŠãªå Žåã¯ããã®åã䜿çšããã ãã§ãURL ã¯ããã«ããã觊ããå¿ èŠããããŸããã
äœãéãã®ãèŠãŠã¿ãŸãããã ClickHouse ã«ã¯ãã¡ã€ã³ãèšç®ããç¹æ®ãªæ©èœããããŸãã éåžžã«é«éã§ãããæé©åãããŠããŸãã æ£çŽã«èšããšãRFC ã«ãæºæ ããŠããŸããããããã§ãå¿ èŠãªãã®ã¯ãã¹ãŠèæ ®ãããŠããŸãã
ããã±ãŒã¹ã§ã¯ãåçŽã« URL ãååŸããŠãã¡ã€ã³ãèšç®ããŸãã ãã㯠166 ããªç§ã«ãªããŸãã æ¢è£œã®ãã¡ã€ã³ã䜿çšãããšãããã 67 ããªç§ãã€ãŸãã»ãŒ XNUMX åé«éã«ãªãããšãããããŸãã ãŸããèšç®ãå¿ èŠã«ãªãããã§ã¯ãªããèªã¿åãããŒã¿ãå°ãªããªããããé«éã«ãªããŸãã
ãã®ãããXNUMX ã€ã®ãªã¯ãšã¹ãã¯äœéã§ãããXNUMX ç§ãããã®ã®ã¬ãã€ãã®é床ãé«ããªããŸãã ããå€ãã®ã®ã¬ãã€ããèªã¿åãããã§ãã å šãäžèŠãªããŒã¿ã§ãã ãªã¯ãšã¹ãã®å®è¡ã¯éããªã£ãããã«èŠããŸãããå®äºãŸã§ã«æéãããããŸãã
ãã£ã¹ã¯äžã®ããŒã¿éãèŠããšãURL 㯠126 ã¡ã¬ãã€ãããã¡ã€ã³ã¯ããã 5 ã¡ã¬ãã€ãã§ããããšãããããŸãã 25åå°ãªãããšãããããŸãã ãã ããããã§ãããªã¯ãšã¹ãã®å®è¡é床ã¯ããã 4 åã§ãã ããããããã¯ããŒã¿ããããã ããã§ãã ãŸããå¯ãå Žåã¯ããã£ã¹ã¯ I/O ã«ããããããã 25 åé«éã«ãªãã§ãããã
ã¡ãªã¿ã«ããã¡ã€ã³ã URL ã«æ¯ã¹ãŠã©ã®ãããå°ããããèŠç©ãããšãçŽ 4 åå°ããããšãããããŸãããäœããã®çç±ã§ããã£ã¹ã¯äžã«å ããããŒã¿ã®å æé㯠25 åã® XNUMX ã«ãªããŸãã ãªãïŒ å§çž®ã®ããã URL ããã¡ã€ã³ãå§çž®ãããŸãã ããããå€ãã®å ŽåãURL ã«ã¯å€§éã®ãŽããå«ãŸããŠããŸãã
ãããŠãã¡ãããç®çã®å€ã«åãããŠç¹å¥ã«èšèšãããããŸãã¯é©åãªããŒã¿åã䜿çšããããšã«ã¯äŸ¡å€ããããŸãã IPv4 ã䜿çšããŠããå Žåã¯ãUInt32* ãæ ŒçŽããŸãã IPv6 ã®å ŽåãIPv16 ã¢ãã¬ã¹ã¯ 6 ãããã§ãããããFixedString(128) ã«ãªããŸããã€ãŸãããã€ããªåœ¢åŒã§çŽæ¥ä¿åãããŸãã
ããããIPv4 ã¢ãã¬ã¹ã䜿çšããå Žåãš IPv6 ã䜿çšããå Žåãããå Žåã¯ã©ããªãã§ãããã? ã¯ããäž¡æ¹ä¿ç®¡ã§ããŸãã 4 ã€ã®å㯠IPv6 çšã§ããã 4 ã€ã®å㯠IPv6 çšã§ãã ãã¡ãããIPv4 ã IPvXNUMX ã§è¡šç€ºãããªãã·ã§ã³ããããŸãã ããã§ãæ©èœããŸããããªã¯ãšã¹ã㧠IPvXNUMX ã¢ãã¬ã¹ãé »ç¹ã«å¿ èŠã«ãªãå Žåã¯ãå¥ã®åã«å ¥ãããšããã§ãããã
* ClickHouse ã«ã¯ãæ°å€ãšåããããå¹ççã«ããŒã¿ãä¿åããªãããæååãšåããããç°¡åã«ããŒã¿ãè¡šçŸã§ããåå¥ã® IPv4ãIPv6 ããŒã¿åãè¿œå ãããŸããã
äºåã«ããŒã¿ãååŠçãã䟡å€ãããããšã«æ³šæããããšãéèŠã§ãã ããšãã°ãçã®ãã°ãããã€ãåãåã£ããšããŸãã ãããŠãããã« ClickHouse ã«å ¥ããã¹ãã§ã¯ãªããããããŸããããäœãããã«ãã¹ãŠãããŸãããããã«èªæãããŸãã ããããããã§ãå¯èœãªèšç®ãå®è¡ãã䟡å€ã¯ãããŸãã
ããšãã°ããã©ãŠã¶ã®ããŒãžã§ã³ã æãæããããªãè¿ãã®éšéã§ã¯ããã©ãŠã¶ã®ããŒãžã§ã³ã次ã®ããã«ãã€ãŸãæååãšããŠä¿åãããŠããŸã: 12.3. 次ã«ãã¬ããŒããäœæããããã«ããã®æååãé åã«åå²ããããã«é åã®æåã®èŠçŽ ã«åå²ããŸãã åœç¶ã®ããšãªããããã¹ãŠãé ããªããŸãã ãªããããªããšãããã®ããšå°ããŸããã 圌ãã¯ææå°æ©ãªæé©åã奜ãã§ã¯ãªããšç§ã«èšããŸããã ãããŠç§ã¯ææå°æ©ã«æ²èŠ³ããã®ã奜ãã§ã¯ãããŸããã
ãããã£ãŠããã®å Žå㯠4 ã€ã®åã«åå²ããæ¹ãæ£ç¢ºã§ãã ãã㯠ClickHouse ãªã®ã§ãå¿é ããªãã§ãã ããã ClickHouse ã¯ååããŒã¿ããŒã¹ã§ãã ãããŠãå°ããªåããã¡ããšããŠããã°ããã»ã©è¯ãã®ã§ãã 5 ã€ã®ãã©ãŠã¶ããŒãžã§ã³ãããã5 ã€ã®åãäœæããŸãã ããã§å€§äžå€«ã§ãã
ããã§ãéåžžã«é·ãæååãéåžžã«é·ãé åãå€æ°ããå Žåã«ã©ãããã°ããããèŠãŠã¿ãŸãããã ClickHouse ã«ä¿åããå¿ èŠã¯ãŸã£ãããããŸããã 代ããã«ãClickHouse ã«ã¯èå¥åã®ã¿ãä¿åã§ããŸãã ãããŠããããã®é·ãè¡ãä»ã®ã·ã¹ãã ã«å ¥ããŠãã ããã
ããšãã°ãåœç€Ÿã®åæãµãŒãã¹ã® 512 ã€ã¯ãããã€ãã®ã€ãã³ã ãã©ã¡ãŒã¿ãŒãæã£ãŠããŸãã ã€ãã³ãã®ãã©ã¡ãŒã¿ãŒãå€æ°ããå Žåã¯ãæåã«èŠã€ãã£ã 512 ãä¿åããŸããXNUMX ã¯æ®å¿µã§ã¯ãªãããã§ãã
ããŒã¿ ã¿ã€ãã決å®ã§ããªãå Žåã¯ãClickHouse ã«ããŒã¿ãèšé²ããããšãã§ããŸãããäžæããŒã¿å°çšã®ãã° ã¿ã€ãã®äžæããŒãã«ã«ããŒã¿ãèšé²ããããšãã§ããŸãã ãã®åŸãããã«ã©ã®ãããªå€ã®ååžãããã®ãââãäžè¬çã«äœãååšããã®ããåæããæ£ããã¿ã€ããäœæã§ããŸãã
*ClickHouse ã«ããŒã¿åãè¿œå ãããŸãã
次ã«ãå¥ã®èå³æ·±ãäºäŸãèŠãŠã¿ãŸãããã æã ç©äºã人ã ã«ãšã£ãŠå¥åŠã«æ©èœããããšããããŸãã å ¥ã£ãŠããŠãããèŠãŸãã ãããŠããã㯠MySQL ããŒãžã§ã³ 3.23 ã®ã»ããã¢ããã«è±å¯ãªçµéšãæã€ãéåžžã«çµéšè±å¯ã§è¡æãªç®¡çè ã«ãã£ãŠè¡ããããã®ã§ããããšãããã«ããããŸããã
ããã«ã¯ XNUMX ã®ããŒãã«ããããããããã®ããŒãã«ã«ã¯èª°ãäœãç¥ã£ãŠãããã XNUMX ã§å²ã£ãäœããèšé²ãããŠããŸãã
ç§ã¯ååãšããŠãä»ã®äººã®çµéšãå°éããŸããããã«ã¯ããã®çµéšãéããŠåŸãããèŠãã¿ã®ç解ãå«ãŸããŸãã
ãããŠãã®çç±ã¯å€ããå°ãªããæããã§ãã ãããã¯ãä»ã®ã·ã¹ãã ã䜿çšããŠããéã«èç©ãããå¯èœæ§ã®ããå€ãåºå®èŠ³å¿µã§ãã ããšãã°ãMyISAM ããŒãã«ã«ã¯ã¯ã©ã¹ã¿ãŒåãããäž»ããŒããããŸããã ãããŠããã®ããŒã¿ã®åå²æ¹æ³ã¯ãåãæ©èœãå®çŸããããã®å¿ æ»ã®è©Šã¿ã§ããå¯èœæ§ããããŸãã
ãã XNUMX ã€ã®çç±ã¯ã倧ããªããŒãã«ã«å¯ŸããŠå€æŽæäœãå®è¡ããã®ãé£ããããšã§ãã ãã¹ãŠããããã¯ãããŠããŸããŸãã ãã ããææ°ããŒãžã§ã³ã® MySQL ã§ã¯ããã®åé¡ã¯ããã»ã©æ·±å»ã§ã¯ãªããªããŸããã
ãŸãã¯ãããšãã°ãã€ã¯ãã·ã£ãŒãã£ã³ã°ã§ãããããã«ã€ããŠã¯åŸã§è©³ãã説æããŸãã
ClickHouse ã§ã¯ãããè¡ãå¿ èŠã¯ãããŸããããŸããäž»ããŒãã¯ã©ã¹ã¿ãŒåãããããŒã¿ãäž»ããŒã«ãã£ãŠé åºä»ããããããã§ãã
ãŸããæã ããClickHouse ã®ç¯å²ã¯ãšãªã®ããã©ãŒãã³ã¹ã¯ãããŒãã« ãµã€ãºã«å¿ããŠã©ã®ããã«å€åããŸãã?ããšå°ããããããšããããŸãã å šãå€ãããªããšèšããŸãã ããšãã°ãXNUMX åè¡ã®ããŒãã«ããããXNUMX äžè¡ã®ç¯å²ãèªã¿åããšããŸãã ãã¹ãŠé 調ã ããŒãã«ã« XNUMX å è¡ãããXNUMX äžè¡ãèªã¿åãå Žåãããã¯ã»ãŒåãã«ãªããŸãã
ãããŠç¬¬ XNUMX ã«ãæåããŒãã£ã·ã§ã³ãªã©ã®ããããäœæ¥ãå¿ èŠãããŸããã ãã¡ã€ã« ã·ã¹ãã äžã«ãããã®ã調ã¹ãŠã¿ããšãããŒãã«ãéåžžã«éèŠã§ããããšãããããŸãã ãããŠäžã«ã¯ä»åãã®ãããªãã®ããããŸãã ã€ãŸããClickHouse ããã¹ãŠãè¡ã£ãŠãããã®ã§ãèŠããå¿ èŠã¯ãããŸããã
ClickHouse ã§ã®å€æŽã¯ãåã®è¿œå /ãããããå€æŽããå Žåã¯ç¡æã§ãã
ãŸããããŒãã«ã« 10 è¡ãŸã㯠10 è¡ãããå Žåã¯ããŸã£ããåé¡ãªããããå°ããªããŒãã«ãäœæããªãã§ãã ããã ClickHouse ã¯ã¬ã€ãã³ã·ãŒã§ã¯ãªãã¹ã«ãŒããããæé©åããã·ã¹ãã ã§ããããã000 è¡ãåŠçããã®ã¯æå³ããããŸããã
倧ããªããŒãã«ã XNUMX ã€äœ¿çšããã®ãæ£ããã§ãã å€ãåºå®èŠ³å¿µãåãé€ããŸãããããã¹ãŠãããŸããããŸãã
ããã«ãææ°ããŒãžã§ã³ã§ã¯ãåã ã®ããŒãã£ã·ã§ã³ã«å¯ŸããŠããããçš®é¡ã®ã¡ã³ããã³ã¹æäœãå®è¡ããããã«ãä»»æã®ããŒãã£ã·ã§ã³ ããŒãäœæã§ããããã«ãªããŸããã
ããšãã°ãäžéããŒã¿ãåŠçããå¿ èŠãããå Žåããã£ã³ã¯ãåãåããæçµããŒãã«ã«æžã蟌ãåã«ãããã®ãã£ã³ã¯ã«å¯ŸããŠå€æãå®è¡ããå¿ èŠãããå Žåãªã©ãå€æ°ã®å°ããªããŒãã«ãå¿ èŠã§ãã ãã®ã±ãŒã¹ã«ã¯ãStripeLog ãšããçŽ æŽãããããŒãã« ãšã³ãžã³ããããŸãã ãã㯠TinyLog ã«äŒŒãŠããŸãããããããåªããŠããã ãã§ãã
* çŸåšãClickHouse ã«ããããŸã
ãã 5 ã€ã®ã¢ã³ããã¿ãŒã³ã¯ãã€ã¯ãã·ã£ãŒãã£ã³ã°ã§ãã ããšãã°ãããŒã¿ãã·ã£ãŒãã£ã³ã°ããå¿ èŠãããããµãŒããŒã 6 å°ãããææ¥ã«ã¯ãµãŒããŒã 5 å°ã«ãªããšããŸãã ãããŠããã®ããŒã¿ããªãã©ã³ã¹ããæ¹æ³ãèããŸãã ãã®ä»£ããã«ã1 ã€ã®ã·ã£ãŒãã§ã¯ãªã 000 ã®ã·ã£ãŒãã«åå²ããŸãã 次ã«ããããã®åãã€ã¯ãã·ã£ãŒããå¥ã®ãµãŒããŒã«ãããã³ã°ããŸãã ããšãã°ã200 ã€ã®ãµãŒããŒäžã§ XNUMX ã® ClickHouse ãååŸã§ããŸãã åå¥ã®ããŒããŸãã¯åå¥ã®ããŒã¿ããŒã¹äžã®åå¥ã®ã€ã³ã¹ã¿ã³ã¹ã
ããããããã¯ClickHouseã§ã¯ããŸãè¯ããããŸããã 56 ã€ã® ClickHouse ã€ã³ã¹ã¿ã³ã¹ã§ãã56 ã€ã®ãªã¯ãšã¹ããåŠçããããã«å©çšå¯èœãªãã¹ãŠã®ãµãŒã㌠ãªãœãŒã¹ã䜿çšããããšããããã§ãã ã€ãŸããããçš®ã®ãµãŒããŒããããããšãã° 200 åã®ããã»ããµ ã³ã¢ãæèŒãããŠãããšããŸãã 10 ç§ãããã¯ãšãªãå®è¡ãããšã000 ã³ã¢ã䜿çšãããŸãã ãããŠãXNUMX ã€ã®ãµãŒããŒäžã« XNUMX ã® ClickHouse ãé 眮ãããšãXNUMX ã®ã¹ã¬ãããéå§ãããããšãããããŸãã äžè¬ã«ããã¹ãŠãéåžžã«æªããªããŸãã
ãã XNUMX ã€ã®çç±ã¯ããããã®ã€ã³ã¹ã¿ã³ã¹éã§ã®äœæ¥ã®åæ£ãäžåäžã«ãªãããšã§ãã æ©ãçµãã人ãããã°ãé ãçµãã人ãããŸãã ããããã¹ãŠã XNUMX ã€ã®ã€ã³ã¹ã¿ã³ã¹ã§çºçããå ŽåãClickHouse èªäœãã¹ã¬ããéã§ããŒã¿ãæ£ããåæ£ããæ¹æ³ãèŠã€ãåºãã§ãããã
ãã XNUMX ã€ã®çç±ã¯ãTCP çµç±ã§ããã»ããµééä¿¡ãè¡ãããšã§ãã ããŒã¿ã¯ã·ãªã¢ã«åãéã·ãªã¢ã«åããå¿ èŠããããããã¯èšå€§ãªæ°ã®ãã€ã¯ãã·ã£ãŒãã«ãªããŸãã ããã¯åã«å¹æçã«æ©èœããŸããã
ãããã¢ã³ããã¿ãŒã³ãšã¯èšããŸããããã¢ã³ããã¿ãŒã³ã§ãã ããã¯å€§éã®äºåéèšã§ãã
äžè¬ã«ãäºåéèšã¯é©åã§ãã 1 åè¡ãããããããéèšãããš 000 è¡ã«ãªããã¯ãšãªãå³åº§ã«å®è¡ãããããã«ãªããŸããã ãã¹ãŠãçŽ æŽãããã§ãã ããªãã¯ãããè¡ãããšãã§ããŸãã ãã®ãããClickHouse ã«ã AggregatingMergeTree ãšããç¹å¥ãªããŒãã« ã¿ã€ãããããããŒã¿ãæ¿å ¥ããããšå¢åéèšãå®è¡ããŸãã
ããããããã®ããã«ããŒã¿ãéèšãããã®ããã«ããŒã¿ãéèšããããšèããããšããããŸãã ãŸããé£ã®éšéã§ã¯ãã©ããšã¯èšããŸããããSummingMergeTree ããŒãã«ã䜿çšããŠäž»ããŒã§éèšããŠãããçŽ 20 åãäž»ããŒãšããŠäœ¿çšãããŠããŸãã 念ã®ãããæ©å¯ä¿æã®ããã«ããã€ãã®åã®ååãå€æŽããŸããããã»ãŒããã ãã§ãã
ãããŠããã®ãããªåé¡ãçããŸãã ãŸããããŒã¿éãããŸãæžããŸããã äŸãã°XNUMXåã«æžããŸãã ããŒã¿ãéçŽãããŠããªãå Žåã«çããç¡å¶éã®åææ©èœãå©çšããã«ã¯ãXNUMX åã劥åœãªäŸ¡æ Œã§ãã ããŒã¿ãéçŽãããŠããå Žåãåæã®ä»£ããã«åŸãããã®ã¯æ²æšãªçµ±èšã ãã§ãã
ãããŠãããã®äœããããªã«ç¹å¥ãªã®ã§ããããïŒ å®éãé£ã®éšéã®æ åœè ããäž»ããŒã«å¥ã®åãè¿œå ããããã«äŸé Œãã«è¡ãããšããããŸãã ã€ãŸãããã®ããã«ããŒã¿ãéèšããŸããããããå°ãå€ãã®ããŒã¿ãå¿ èŠã«ãªããŸãã ãã ããClickHouse ã«ã¯å€æŽäž»ããŒããããŸããã ãããã£ãŠãããã€ãã®ã¹ã¯ãªããã C++ ã§èšè¿°ããå¿ èŠããããŸãã ãããŠãããšãããã C++ ã§ãã£ãŠããç§ã¯ã¹ã¯ãªããã奜ãã§ã¯ãããŸããã
ClickHouse ãäœã®ããã«äœãããã®ããèŠããšãééèšããŒã¿ã¯ãŸãã«ãããçãŸããã·ããªãªã§ãã ééèšããŒã¿ã« ClickHouse ã䜿çšããŠããå Žåãããã¯æ£ããè¡ãããŠããŸãã éèšããã°ãããã¯èš±å®¹ã§ããå ŽåããããŸãã
ãã XNUMX ã€ã®èå³æ·±ãã±ãŒã¹ã¯ãç¡éã«ãŒãå ã®ã¯ãšãªã§ãã æã ãç§ã¯å®çšŒåãµãŒããŒã«è¡ãããã㧠show processlist ã確èªããŸãã ãããŠãã®ãã³ã«ãäœãæãããããšãèµ·ãã£ãŠããããšã«æ°ã¥ããŸãã
ããšãã°ããã®ããã«ã XNUMX åã®ãªã¯ãšã¹ãã§ãã¹ãŠãå®è¡ã§ããããšãããã«ããããŸãã URLãšãªã¹ããããã«æžã蟌ãã ãã§ãã
ç¡éã«ãŒãã«ãããã®ãããªã¯ãšãªã®å€ããåé¡ãšãªãã®ã¯ãªãã§ãããã? ã€ã³ããã¯ã¹ã䜿çšãããŠããªãå Žåã¯ãåãããŒã¿ã«å¯ŸããŠäœåºŠããã¹ããããšã«ãªããŸãã ããããã€ã³ããã¯ã¹ã䜿çšãããŠããå Žåãããšãã° ru ã®äž»ããŒããããããã« url = äœããæžã蟌ã¿ãŸãã ãããŠãããŒãã«ãã URL ã XNUMX ã€ã ãèªã¿åãããã°ããã¹ãŠåé¡ãªããšèããŸãã ããããå®éã«ã¯éããŸãã ClickHouse ã¯ãã¹ãŠããããã§å®è¡ããããã§ãã
ClickHouse ã®ã€ã³ããã¯ã¹ããŸã°ãã§ãããããç¹å®ã®ç¯å²ã®ããŒã¿ãèªã¿åãå¿ èŠãããå Žåã¯ãããå°ãå€ãèªã¿åããŸãã ãã®ã€ã³ããã¯ã¹ã䜿çšãããšãããŒãã«å ã® XNUMX ã€ã®åå¥ã®è¡ãæ€çŽ¢ããããšã¯ã§ãããããçš®ã®ç¯å²ã®ã¿ãæ€çŽ¢ã§ããŸãã ãããŠãããŒã¿ã¯ãããã¯åäœã§å§çž®ãããŸãã XNUMX è¡ãèªã¿åãã«ã¯ããããã¯å šäœãåãåºãããããã¯ã解ãå¿ èŠããããŸãã ãŸãã倧éã®ã¯ãšãªãå®è¡ããŠããå Žåã¯ãéè€ããéšåãå€ããªããäœåºŠãç¹°ãè¿ãå®è¡ããå¿ èŠãããäœæ¥ãå€ããªããŸãã
ããŸãã«ãClickHouse ã§ã¯ãããšãã¡ã¬ãã€ããæ°çŸã¡ã¬ãã€ãã§ãã£ãŠããIN ã»ã¯ã·ã§ã³ã«è»¢éããããšãæããå¿ èŠã¯ãªãããšã«æ³šæããŠãã ããã ç§ãã¡ã®å®è·µããæãåºããã®ã§ãããMySQL ã§å€§éã®å€ã IN ã»ã¯ã·ã§ã³ã«è»¢éãããšãããšãã°ããã« 100 ã¡ã¬ãã€ãã®æ°å€ã転éãããšãMySQL 㯠10 ã®ã¬ãã€ãã®ã¡ã¢ãªãæ¶è²»ããä»ã«ã¯äœãèµ·ãããŸãããåäœãæªãã
XNUMX ã€ç®ã¯ãClickHouse ã§ã¯ãã¯ãšãªã§ã€ã³ããã¯ã¹ã䜿çšãããŠããå Žåãåžžã«ãã« ã¹ãã£ã³ãããé ããªãããšã§ããã€ãŸããããŒãã«ã®ã»ãŒå šäœãèªã¿åãå¿ èŠãããå Žåãã·ãŒã±ã³ã·ã£ã«ã«å®è¡ããŠããŒãã«å šäœãèªã¿åããŸãã äžè¬çã«ã圌ã¯èªåã§ãããç解ããŸãã
ããããããã§ãããã€ãã®å°é£ããããŸãã ããšãã°ããµãã¯ãšãªã䜿çšãã IN ã¯ã€ã³ããã¯ã¹ã䜿çšããªãããšã§ãã ããããããã¯ç§ãã¡ã®åé¡ã§ããã解決ããå¿ èŠããããŸãã ããã«ã¯åºæ¬çãªãã®ã¯äœããããŸããã ä¿®æ£ãããŠããã ããŸã*ã
ãã 100 ã€ã®èå³æ·±ãç¹ã¯ãéåžžã«é·ããªã¯ãšã¹ãããããåæ£ãªã¯ãšã¹ãåŠçãé²è¡äžã®å Žåããã®éåžžã«é·ããªã¯ãšã¹ãã¯å§çž®ãããã«åãµãŒããŒã«éä¿¡ãããããšã§ãã ããšãã°ã500 ã¡ã¬ãã€ããš 50 ãµãŒããŒãªã©ã§ãã ãããã£ãŠããããã¯ãŒã¯äžã§ XNUMX ã®ã¬ãã€ãã転éãããããšã«ãªããŸãã ãããéä¿¡ããããã¹ãŠãæ£åžžã«å®äºããŸãã
* ãã§ã«äœ¿çšããŠããŸã; ãã¹ãŠãçŽæã©ããã«ä¿®æ£ãããŸããã
ãããŠãããªãäžè¬çãªã±ãŒã¹ã¯ããªã¯ãšã¹ãã API ããéä¿¡ãããå Žåã§ãã ããšãã°ãç¬èªã®ãµãŒãã¹ãäœæãããšããŸãã ãããŠã誰ããããªãã®ãµãŒãã¹ãå¿ èŠãšããŠããå ŽåãAPI ãéããšãæåéã XNUMX æ¥åŸã«ç解ã§ããªãããšãèµ·ãã£ãŠããããšã«æ°ã¥ããŸãã ãã¹ãŠãéè² è·ã«ãªãã決ããŠèµ·ããã¹ãã§ã¯ãªãã£ãã²ã©ããªã¯ãšã¹ããããã€ãå ¥ã£ãŠããŸãã
ãããŠã解決ç㯠XNUMX ã€ã ãã§ãã API ãå ¬éããŠããå Žåã¯ããããåæããå¿ èŠããããŸãã ããšãã°ãããçš®ã®ãã«ããå°å ¥ããŸãã ä»ã«éåžžã®ãªãã·ã§ã³ã¯ãããŸããã ããããªããšãããã«ã¹ã¯ãªãããäœæãããåé¡ãçºçããŸãã
ãããŠãClickHouse ã«ã¯ã¯ã©ãŒã¿èšç®ãšããç¹å¥ãªæ©èœããããŸãã ããã«ãã¯ã©ãŒã¿ ããŒã転éããããšãã§ããŸãã ããã¯ãããšãã°å éšãŠãŒã¶ãŒ ID ã§ãã ãããŠãã¯ã©ãŒã¿ã¯ããããã«å¯ŸããŠåå¥ã«èšç®ãããŸãã
ããŠãããäžã€èå³æ·±ãããšããããŸãã ããã¯æåã¬ããªã±ãŒã·ã§ã³ã§ãã
ClickHouse ã«ã¯ã¬ããªã±ãŒã·ã§ã³ ãµããŒããçµã¿èŸŒãŸããŠããã«ãããããããClickHouse ãæåã§ã¬ããªã±ãŒãããŠããã±ãŒã¹ãç§ã¯å€ãç¥ã£ãŠããŸãã
åçã¯äœã§ãã? ããŒã¿åŠçãã€ãã©ã€ã³ããããŸãã ãŸããããšãã°ãç°ãªãããŒã¿ã»ã³ã¿ãŒã§ãç¬ç«ããŠåäœããŸãã ClickHouse ã§ã¯åãããŒã¿ãåãæ¹æ³ã§æžã蟌ã¿ãŸãã 確ãã«ãå®éã«ãã£ãŠã¿ããšãã³ãŒãå ã®ããã€ãã®æ©èœã«ãã£ãŠããŒã¿ãäŸç¶ãšããŠåå²ããããšãããããŸãã ãããããªãã®äžã«ããããšãé¡ã£ãŠããŸãã
ãŸããå Žåã«ãã£ãŠã¯æåã§åæããå¿ èŠããããŸãã ããšãã°ã管çè ã¯æã« XNUMX å rsync ãå®è¡ããŸãã
å®éãClickHouse ã«çµã¿èŸŒãŸããŠããã¬ããªã±ãŒã·ã§ã³ã䜿çšããæ¹ãã¯ããã«ç°¡åã§ãã ãã ããZooKeeper ã䜿çšããå¿ èŠããããããããã€ãã®çŠå¿ãããå¯èœæ§ããããŸãã ZooKeeper ã«ã€ããŠæªãããšã¯èšããŸãããååãšããŠãã·ã¹ãã ã¯æ©èœããŸãããJava ææçã®ãã㧠ZooKeeper ã䜿çšããªã人ãããŸããClickHouse 㯠C++ ã§æžãããéåžžã«åªããã·ã¹ãã ã§ãããèªç±ã«äœ¿çšã§ããŸãããã¹ãŠããŸããããŸãã ZooKeeper 㯠Java ã«ãããŸãã ã©ãããããããèŠãããªãå Žåã§ããæåã¬ããªã±ãŒã·ã§ã³ã䜿çšã§ããŸãã
ClickHouse ã¯å®çšçãªã·ã¹ãã ã§ãã 圌女ã¯ããªãã®ããŒãºãèæ ®ã«å ¥ããŠãããŸãã æåã¬ããªã±ãŒã·ã§ã³ãããå Žåã¯ãæåã¬ããªã«ãåç §ããŠãããã®éã§ãã§ã€ã«ãªãŒããŒãå®è¡ããåæ£ããŒãã«ãäœæã§ããŸãã ãŸããã©ã€ã³ã系統çã«åå²ããŠããå Žåã§ããããããåé¿ã§ããç¹å¥ãªãªãã·ã§ã³ããããŸãã
ããªããã£ã ããŒãã« ãšã³ãžã³ã䜿çšãããšãããã«åé¡ãçºçããå¯èœæ§ããããŸãã ClickHouse ã¯ãããŸããŸãªããŒãã« ãšã³ãžã³ãå€æ°åããã³ã³ã¹ãã©ã¯ã¿ãŒã§ãã ããã¥ã¡ã³ãã«èšèŒãããŠããããã«ããã¹ãŠã®æ·±å»ãªå Žåã«ã¯ãMergeTree ãã¡ããªã®ããŒãã«ã䜿çšããŠãã ããã ãããŠæ®ãã®ãã¹ãŠ - ããã¯ãåã ã®ã±ãŒã¹ãŸãã¯ãã¹ãã®å Žåã«åœãŠã¯ãŸããŸãã
MergeTree ããŒãã«ã§ã¯ãæ¥ä»ãšæå»ã¯å¿ èŠãããŸããã ãŸã ã䜿ãããã ããŸãã æ¥ä»ãšæå»ããªãå Žåã¯ãããã©ã«ãã 2000 ã§ããããšãæžã蟌ã¿ãŸãã ããã¯æ©èœãããªãœãŒã¹ã¯å¿ èŠãããŸããã
ãŸãããµãŒããŒã®æ°ããããŒãžã§ã³ã§ã¯ãããŒãã£ã·ã§ã³ ããŒã䜿çšããã«ã«ã¹ã¿ã ããŒãã£ã·ã§ã³åãæå®ããããšãã§ããŸãã ãããåãã ããã
äžæ¹ãããªããã£ããªããŒãã« ãšã³ãžã³ã䜿çšããããšãã§ããŸãã ããšãã°ãããŒã¿ãäžåºŠå ¥åããŠãèŠãŠãã²ãããåé€ããŸãã ãã°ã䜿çšã§ããŸãã
ãããã¯ãäžéåŠççšã«å°éãä¿åããã«ã¯ãStripeLog ãŸã㯠TinyLog ã䜿çšããŸãã
ããŒã¿éãå°ãªããRAM å ã§äœãããããã ãã§æžãå Žåã¯ãã¡ã¢ãªã䜿çšã§ããŸãã
ClickHouse ã¯åæ£èŠåãããããŒã¿ãããŸã奜ã¿ãŸããã
å žåçãªäŸã次ã«ç€ºããŸãã ããã¯èšå€§ãªæ°ã® URL ã§ãã ãããã次ã®ããŒãã«ã«çœ®ããŸãã ãããŠãJOIN ãå®è¡ããããšã«ããŸããããClickHouse 㯠Hash JOIN ã®ã¿ããµããŒãããŠãããããããã¯ååãšããŠæ©èœããŸããã æ¥ç¶ããå¿ èŠããã倧éã®ããŒã¿ãåŠçã§ããåå㪠RAM ããªãå ŽåãJOIN ã¯æ©èœããŸãã*ã
ããŒã¿ã®ã«ãŒãã£ããªãã£ãé«ãå Žåã¯ãå¿é ããå¿ èŠã¯ãããŸãããéæ£èŠå圢åŒã§ä¿åããŠãã ãããURL ã¯ã¡ã€ã³ ããŒãã«ã«çŽæ¥é 眮ãããŸãã
* ãããŠçŸåšãClickHouse ã«ã¯ããŒãžçµåããããäžéããŒã¿ã RAM ã«åãŸããªãç¶æ³ã§ãæ©èœããŸãã ããããããã¯å¹æããªããå§åã¯åŒãç¶ãæå¹ã§ãã
ä»ã«ãããã€ãäŸããããŸãããããããã¢ã³ããã¿ãŒã³ã§ãããã©ããã¯ãã§ã«çåã§ãã
ClickHouse ã«ã¯æ¢ç¥ã®æ¬ é¥ã XNUMX ã€ãããŸãã ã¢ããããŒãæ¹æ³ãããããŸãã*ã ããæå³ã§ã¯ãããã¯è¯ãããšã§ãã äŒèšãªã©ã®éèŠãªããŒã¿ãããå ŽåãæŽæ°ããªãããã誰ããããéä¿¡ã§ããŸããã
* ããã ã¢ãŒãã§ã®æŽæ°ãšåé€ã®ãµããŒãã¯ããªãåã«è¿œå ãããŸããã
ãã ããããã¯ã°ã©ãŠã³ãã§ãããã®ããã«æŽæ°ã§ããç¹å¥ãªæ¹æ³ãããã€ããããŸãã ããšãã°ãReplaceMergeTree ã®ãããªããŒãã«ã§ãã ããã¯ã°ã©ãŠã³ãã®ããŒãžäžã«æŽæ°ãè¡ãããŸãã ããŒãã«ã®æé©åã䜿çšããŠããã匷å¶ã§ããŸãã ãã ããããŒãã£ã·ã§ã³ãå®å šã«äžæžãããŠããŸãããããããé »ç¹ã«å®è¡ããªãã§ãã ããã
ClickHouse ã®åæ£ JOIN ããã¯ãšãª ãã©ã³ããŒã§ã¯é©åã«åŠçãããŸããã
æªããã©ãããŸã«ã¯OKã
select* ã䜿çšããŠããŒã¿ãèªã¿åãå Žåã«ã®ã¿ ClickHouse ã䜿çšããŸãã
é¢åãªèšç®ã« ClickHouse ã䜿çšããããšã¯ãå§ãããŸããã ããããç§ãã¡ã¯ãã§ã«ãã®æšå¥šäºé ããé¢ãã€ã€ãããããããã¯å®å šã«çå®ã§ã¯ãããŸããã ãããŠæè¿ãClickHouse - Catboost ã«æ©æ¢°åŠç¿ã¢ãã«ãé©çšããæ©èœãè¿œå ããŸããã ãããŠããäœãšããæãããããšã ããããšæãã®ã§ãããã¯ç§ãæ©ãŸããŸãã ããã¯ãXNUMX ãã€ããããã®ãµã€ã¯ã«æ°ã§ãã ç§ã¯ãã€ãã«ã¯ããã¯ãç¡é§ã«ããã®ãæ¬åœã«å«ãã§ãã
ã§ããå¿é ããªãã§ãClickHouse ãã€ã³ã¹ããŒã«ããã°ããã¹ãŠããŸããããŸãã ã©ã¡ãããšãããšãç§ãã¡ã«ã¯ã³ãã¥ããã£ããããŸãã ã¡ãªã¿ã«ãã³ãã¥ããã£ãšã¯ããªãèªèº«ã®ããšã§ãã äœãåé¡ãããå Žåã¯ãå°ãªããšãç§ãã¡ã®ãã£ããã«ã¢ã¯ã»ã¹ããŠãã ãããå©ããŠãããããšãé¡ã£ãŠããŸãã
質å
ãå ±åããããšãããããŸãïŒ ClickHouse ã®ã¯ã©ãã·ã¥ã«ã€ããŠã©ãã«èŠæ ãç³ãç«ãŠãã°ããã§ãã?
ä»ããç§ã«å人çã«èŠæ ãèšã£ãŠãããã§ãããã
æè¿ã¯ãªãã¯ããŠã¹ã䜿ãå§ããŸããã ç§ã¯ããã« cli ã€ã³ã¿ãŒãã§ãŒã¹ãåé€ããŸããã
ããªãã¯ã©ãããŒã§ãã
å°ãåŸãå°ããªéžæã§ãµãŒããŒãã¯ã©ãã·ã¥ãããŸããã
ããªãã«ã¯æèœããããŸãã
GitHub ã®ãã°ããªãŒãã³ããŸããããç¡èŠãããŸããã
èŠãã§ãããã
ã¢ã¬ã¯ã»ã€ã¯ç§ãã ãŸããŠå ±åæžã«åå ãããå éšã®ããŒã¿ã«ã©ã®ããã«ã¢ã¯ã»ã¹ããããæãããšçŽæããŸããã
éåžžã«ã·ã³ãã«ã
æšæ¥ãã®ããšã«æ°ã¥ããŸããã ããã«è©³ããã
ããã«ã¯æãããããªãã¯ã¯ãããŸããã ããã®ã¯ãããã¯ããšã®å§çž®ã ãã§ãã ããã©ã«ã㯠LZ4 ã§ãããZSTD* ãæå¹ã«ããããšãã§ããŸãã 64 ãããã€ããã 1 ã¡ã¬ãã€ããŸã§ã®ãããã¯ã
* ä»ã®ã¢ã«ãŽãªãºã ãšãã§ãŒã³ã§äœ¿çšã§ããç¹æ®ãªå§çž®ã³ãŒããã¯ã®ãµããŒãããããŸãã
ãããã¯ã¯åãªãçããŒã¿ã§ãã?
å®å šã«çã§ã¯ãããŸããã é åããããŸãã æ°å€åãããå Žåãè¡å ã®æ°å€ãé åã«é 眮ãããŸãã
æããã§ãã
AlexeyãIP çµç±ã® uniqExact ã®äŸãã€ãŸããuniqExact ã¯æ°å€ãããè¡ã§èšç®ããæ¹ãæéãããããšããäºå®ãªã©ã æ ¡æ£æã«è³ã§ãã§ã€ã³ãããããŠãã£ã¹ãããŠã¿ããã©ãã§ããããïŒ ã€ãŸããç§ãã¡ã®ãã£ã¹ã¯ã§ã¯ããã»ã©éãã¯ãªããšãã£ããã£ãããã§ããã ãã£ã¹ã¯ããè¡ãèªã¿åã£ãŠãã£ã¹ããããšãéèšã¯éããªããŸãã? ãããšããããã§ããŸã ãããã«å©çãåŸãã§ããããïŒ ããããã¹ãããããã§ãããäœããã®çç±ã§ãã³ãããŒã¯ã«ç€ºãããŸããã§ããã
ãã£ã¹ãããªããããé ããªããšæããŸãã ãã®å ŽåãIP ã¢ãã¬ã¹ãæååãã解æããå¿ èŠããããŸãã ãã¡ãããClickHouse ã§ã¯ãIP ã¢ãã¬ã¹è§£æãæé©åãããŠããŸãã ç§ãã¡ã¯äžçæžåœåªåããŸããããããã«ã¯äžäžåã®ïŒã®åœ¢åŒã§æ°åãæžãããŠããŸãã ãšãŠãäžå¿«ã§ãã äžæ¹ãuniqExact é¢æ°ã¯æååã«å¯ŸããŠã¯åäœãé ããªããŸããããã¯ãæååã§ããã ãã§ãªããã¢ã«ãŽãªãºã ã®ç°ãªãç¹æ®åãéžæãããŠããããã§ãã æååã¯åçŽã«ç°ãªãæ¹æ³ã§åŠçãããŸãã
ãã£ãšåå§çãªããŒã¿åãæ¡çšãããã©ããªãã§ãããã? ããšãã°ããŠãŒã¶ãŒIDãã¡ã¢ããŠããããäžè¡æžããŠã¹ã¯ã©ã³ãã«ããã»ãã楜ããã§ãããããããã§ãªãã§ããããã
ç§ã¯çãã çµå±ã®ãšãããæ°åã®è§£æã¯æ·±å»ãªåé¡ãªã®ã§ãããã«æ²ããããšã«ãªããšæããŸãã ãã®ååã¯ãXNUMXäžåã®XNUMXã®åœ¢åŒã§æ°å€ã解æããããšãããã«é£ãããã«ã€ããŠå ±åããããããã«æããŸãããããããããã§ã¯ãããŸããã
ã¢ã¬ã¯ã»ã€ããããå ±åããããšãããããŸãïŒ ãããŠClickHouseãæ¬åœã«ããããšãïŒ èšç»ã«ã€ããŠè³ªåããããŸãã èŸæžãäžå®å šã«æŽæ°ããæ©èœã®èšç»ã¯ãããŸãã?
ã€ãŸããéšåçãªåèµ·åã§ãã?
ã¯ãã¯ãã ããã« MySQL ãã£ãŒã«ããèšå®ããæ©èœãšåæ§ã§ããã€ãŸãããã£ã¯ã·ã§ããªãéåžžã«å€§ããå Žåã¯ããã®ããŒã¿ã®ã¿ãããŒããããããã«åŸã§æŽæ°ããŸãã
éåžžã«èå³æ·±ãæ©èœã§ãã ãããŠããã£ããã§èª°ãããããææ¡ãããšæããŸãã ããããããããªããããã ã£ããããããŸããã
ç§ã¯ããã¯æããªãã
ãªãã»ã©ããªã¯ãšã¹ãã XNUMX ã€ããããšãããããŸããã ãããŠããã£ãããšãããå§ããããšãã§ããŸãã ãã ãããã®æ©èœã®å®è£ ã¯éåžžã«ç°¡åã§ããããšãããã«èŠåããããšæããŸãã ã€ãŸããçè«çã«ã¯ãããŒãã«ã«ããŒãžã§ã³çªå·ãæžã蟌ãã§ããããããŒãžã§ã³æªæºã®ããŒãžã§ã³ããšèšè¿°ããã ãã§ãã ããã¯ããããããããæ奜家ã«æäŸããããšãæå³ããŸãã ããªãã¯æ奜家ã§ããïŒ
ã¯ãããããæ®å¿µãªãã C++ ã§ã¯ã§ããŸããã
ããªãã®åå㯠C++ ã§ã®æžãæ¹ãç¥ã£ãŠããŸãã?
誰ãèŠã€ããŸãã
çŽ æŽããã*ã
* ãã®æ©èœã¯ã¬ããŒãã® XNUMX ãæåŸã«è¿œå ãããŸããã質åã®äœæè
ããããéçºããéä¿¡ããŸããã
ããããšãããããŸãïŒ
ããã«ã¡ã¯ïŒ ãå ±åããããšãããããŸãïŒ ClickHouse ã¯å©çšå¯èœãªãªãœãŒã¹ããã¹ãŠæ¶è²»ããã®ãéåžžã«äžæã ãšãã£ããããŸããã ãããŠãLuxoft ã®é£ã®è¬æŒè ã¯ãRussian Post ã«å¯Ÿãããœãªã¥ãŒã·ã§ã³ã«ã€ããŠè©±ããŸããã 圌ã¯ã圌ã㯠ClickHouse ãéåžžã«æ°ã«å ¥ã£ãŠããããCPU ããã¹ãŠäœ¿ãæãããŠãããããäž»ãªç«¶åä»ç€Ÿã®ä»£ããã«ããã䜿çšããªãã£ããšè¿°ã¹ãŸããã ãããŠããããèªåãã¡ã®ã¢ãŒããã¯ãã£ã Docker ã䜿ã£ã ZooKeeper ã«çµã¿èŸŒãããšãã§ããŸããã§ããã ClickHouse ãå©çšã§ãããã®ããã¹ãŠæ¶è²»ããªãããã«ãäœããã®æ¹æ³ã§ ClickHouse ãå¶éããããšã¯ã§ããŸãã?
ã¯ããããã¯å¯èœã§ãããéåžžã«ç°¡åã§ãã æ¶è²»ããã³ã¢ãæžããããå Žåã¯ã次ã®ããã«èšè¿°ããŸãã set max_threads = 1
ã ããã§ããªã¯ãšã¹ãã XNUMX ã€ã®ã³ã¢ã§å®è¡ãããŸãã ããã«ããŠãŒã¶ãŒããšã«ç°ãªãèšå®ãæå®ã§ããŸãã ããã§åé¡ãããŸããã ãããŠãLuxoft ã®ååã«ãããã¥ã¡ã³ãã«ãã®èšå®ãèŠã€ãããªãã£ãã®ã¯è¯ããªããšäŒããŠãã ããã
ã¢ã¬ã¯ã»ã€ãããã«ã¡ã¯ïŒ ãã®ç¹ã«ã€ããŠãèãããããšæããŸãã å€ãã®äººã ClickHouse ããã°ã®ã¹ãã¬ãŒãžãšããŠäœ¿ãå§ããŠãããšèããã®ã¯ãããåããŠã§ã¯ãããŸããã ã¬ããŒãã§ã¯ããããè¡ããªããã€ãŸãé·ãæååãä¿åããå¿ èŠããªããšèšã£ãŠããŸããã ããªãã¯ããã«ã€ããŠã©ãæããŸããïŒ
ãŸãããã°ã¯ååãšããŠé·ãæååã§ã¯ãããŸããã ãã¡ããäŸå€ããããŸãã ããšãã°ãJava ã§æžããããµãŒãã¹ã«ãã£ãŠã¯äŸå€ãã¹ããŒããããã°ã«èšé²ãããŸãã ãªã©ãšããç¡éã«ãŒããç¹°ãè¿ãããããŒããã©ã€ãã®å®¹éãäžè¶³ããŠããŸããŸãã 解決çã¯ãšãŠãç°¡åã§ãã ç·ãéåžžã«é·ãå Žåã¯ãã«ããããŠãã ããã é·ããšã¯ã©ãããæå³ã§ããïŒ æ°åãããã€ãã¯ãã¡ã§ã*ã
* ClickHouse ã®ææ°ããŒãžã§ã³ã§ã¯ããé©å¿ã€ã³ããã¯ã¹ç²åºŠããæå¹ã«ãªã£ãŠãããé·ãè¡ãä¿åããåé¡ã¯ã»ãšãã©è§£æ±ºãããŠããŸãã
ãããã€ãã£ãŠæ®éã§ããïŒ
ããã¯æ£åžžã§ãã
ããã«ã¡ã¯ïŒ ãå ±åããããšãããããŸãïŒ ãã®ä»¶ã«ã€ããŠã¯ãã£ããã§ãã§ã«è³ªåããŸããããåçããã£ããã©ããèŠããŠããŸããã CTE ã®ããã« WITH ã»ã¯ã·ã§ã³ãäœããã®æ¹æ³ã§æ¡åŒµããäºå®ã¯ãããŸãã?
ãŸã ã WITH ã»ã¯ã·ã§ã³ã¯ãã軜èã§ãã ç§ãã¡ã«ãšã£ãŠã¯ã¡ãã£ãšããæ©èœã®ãããªãã®ã§ãã
ããããŸããã ããããšãïŒ
ãå ±åããããšãããããŸãïŒ ãšãŠãèå³æ·±ãïŒ ã°ããŒãã«ãªè³ªåã ããããäœããã®ã¹ã¿ãã®åœ¢ã§ãããŒã¿ã®åé€ãå€æŽããèšç»ã¯ãããŸãã?
å¿ ç¶çã«ã ããã¯ãã¥ãŒå ã®æåã®ã¿ã¹ã¯ã§ãã ç§ãã¡ã¯çŸåšããã¹ãŠãæ£ããè¡ãæ¹æ³ã«ã€ããŠç©æ¥µçã«èããŠããŸãã ãããŠããŒããŒã*ãæŒãå§ããå¿ èŠããããŸãã
* ããŒããŒãã®ãã¿ã³ãæŒããŠãã¹ãŠãå®è¡ããŸããã
ããã¯ã·ã¹ãã ã®ããã©ãŒãã³ã¹ã«äœããã®åœ±é¿ãäžããã®ã§ãããã? æ¿å ¥ã¯ä»ãšåããããæ©ããªããŸããïŒ
ããããåé€èªäœãšæŽæ°èªäœã¯éåžžã«éããã®ã«ãªãã§ãããããããã¯éžæã®ããã©ãŒãã³ã¹ãæ¿å ¥ã®ããã©ãŒãã³ã¹ã«ã¯åœ±é¿ããŸããã
ãããŠãããäžã€å°ããªè³ªåã ãã¬ãŒã³ããŒã·ã§ã³ã§ã¯äž»ããŒã«ã€ããŠè©±ããŸããã ãããã£ãŠãããã©ã«ãã§ã¯æåäœã®ããŒãã£ã·ã§ã³åå²ãè¡ãããŠããŸããã ãããŠãæã«åãŸãæ¥ä»ç¯å²ãèšå®ãããšããã®ããŒãã£ã·ã§ã³ã®ã¿ãèªã¿åãããŸãã
ã¯ãã
質åã äž»ããŒãéžæã§ããªãå Žåãããã¯ã°ã©ãŠã³ãã§ã®ãã®ããŒã¿ã®åé 眮ãå°ãªããªããããé åºããåãŸãããã«ãç¹ã«ãæ¥ä»ããã£ãŒã«ãã«åŸã£ãŠéžæããã®ã¯æ£ããã§ãããã? ç¯å²ã¯ãšãªããªããäž»ããŒãéžæããããšããã§ããªãå Žåãäž»ããŒã«æ¥ä»ãå ¥ãã䟡å€ã¯ãããŸãã?
ã¯ãã
ããããããã®ãã£ãŒã«ãã§äžŠã¹æ¿ããå Žåã«ããŒã¿ãããé©åã«å§çž®ãããã£ãŒã«ããäž»ããŒã«é 眮ããããšãåççã§ããå¯èœæ§ããããŸãã ããšãã°ããŠãŒã¶ãŒIDã ããšãã°ããŠãŒã¶ãŒã¯åããµã€ãã«ã¢ã¯ã»ã¹ããŸãã ãã®å ŽåããŠãŒã¶ãŒ ID ãšæå»ãå ¥åããŸãã ããããã°ãããŒã¿ã¯ããé©åã«å§çž®ãããŸãã æ¥ä»ã«é¢ããŠã¯ãæ¥ä»ã«å¯Ÿããç¯å²ã¯ãšãªãå®éã«ååšãããä»åŸãå®è¡ããªãå Žåã¯ãäž»ããŒã«æ¥ä»ãå ¥ããå¿ èŠã¯ãããŸããã
ã¯ããããããšãããããŸãïŒ
åºæïŒ habr.com