ClickHouseに基づいてツアーをすばやく検索する

この記事では、ツアー（ツアーはホテルとフライトのセットです）に基づいて検索を作成する方法を検討し、 ClickHouseとMySQL（InnoDBとMyISAM）の2つのオプションを検討します。

ツアーを見つけることの難しさは何ですか

ツアーオペレーター（TezTour、TUI、ナタリーツアーなど）は、一見したところ明らかではない方法で許可を販売します。

• 特定の日付のセットに対して、特定の数のホテルの客室が予約されています。
• いくつかの航空機が償還されています。
• 可能なすべての部屋タイプ、滞在期間、都市、出発日の組み合わせを含むツアーの新しいパッケージがリリースされています。

その後、そのような組み合わせ（その数は数億から数十億になることもあります）が検索されます。 検索フォームの例はTezTourで見ることができます-ユーザーは出発都市、宿泊施設の種類、国、およびユーザーが任意に選択できる残りのパラメーターのみを選択できます。



ツアー（組み合わせ）の合計数は数億に上るという事実にもかかわらず、パラメーター（出発都市、宿泊施設のタイプ、国）の固定セットごとに、最悪の場合、数千万のオプションがあります。 しかし、ツアーが非常に多い場合でも、ユーザーが設定する無料の基準を満たすレコードを見つける必要があり、並べ替えは多かれ少なかれ任意であるため、検索はそれほど簡単ではありません（原則として、価格によって並べ替えが行われますが、これが唯一の可能な基準ではありません） この記事では、ストップを除く、MySQLとClickHouseに基づくツアーのリアルタイム検索の簡略化されたアーキテクチャを考慮します（スラング用語、つまり、一部のオプションでは飛行機の番号または座席が終了したことを意味し、そのようなツアーは問題から除外する必要があります）。 検索をすばやく行い、任意のフィールドで並べ替えて結果を表示できるようにする方法を学習します。

データを取得する場所

最初は、ツアーオペレーターの実際のベースを取得して、システムのテストデータとしてアップロードしたいと考えていました。この方法では、結果を比較し、ツアーオペレーターのウェブサイトで検索を発行することで、検索の妥当性を確認できます。 しかし、残念ながら、パブリックドメインでツアーのデータベースを簡単に見つけることができなかったため、実際のツアーをシミュレートする一連の疑似ランダムデータに限定する必要があります。

フィールドのセット

都市、国、宿泊施設のタイプを検索するときに、名前に関連パラメーターのIDが含まれるテーブルを作成できるため、たとえばtours_12_55_1001、12、55、1001はそれぞれ都市、国、宿泊施設のIDです。 これにより、表示する必要があるレコードの数を大幅に削減すると同時に、テーブルに重複する値を格納しないため、スペースを節約できます。



それでも、そのようなテーブルの場合でも、数千万のオプションが存在する可能性があり、テストサンプルでは、​​次の構造を持つ正確に1,000万のレコードを生成します。

• tour_id-uint64-固有のオファーID
• package_id-uint64-ツアーの対応するパッケージのID（パッケージは全体として追加され、取り消されます）
• date_start-uint8-到着日-特定の瞬間からの日数
• stars-uint8-ホテルの星の数+特別な値（アパートなど）
• pansion-uint8-食べ物のタイプ（Bed＆Breakfast、All Inclusiveなど）
• 泊-uint8-滞在期間（泊数）
• region_id-uint16-ホテル地域ID
• hotel_id-uint32-ホテルID
• airport_id-uint8-到着空港ID
• 価格-uint16-オファー価格（ドル）

国やツアーオペレーターによって、リストは異なる場合があります（たとえば、出発空港がある場合や、価格が異なる通貨であり、uint16に収まらない場合など）。ただし、最も基本的なものとしてこのオプションに焦点を当てます。 。



生成のためのコードは、プログラマーによって10分で書かれています。





結果のcsvファイルは約500 MBを占有し、1000万件のレコードを含みます。このレコードでは、tour_idが単調に増加し、残りのフィールドはほぼ現実の範囲でランダムな値を持ちます。

検索条件

各フィールドの値のセットと並べ替えで検索できるようにする必要がありますが、原則として、出発日と期間の範囲はそれほど広くはありません-たとえば、3月1日から5月1日までの出発日範囲のツアーを探すことはほとんどありません。 広範囲の出発日の検索も機能すると想定できますが、この範囲が大幅に制限されている場合、たとえば平均で10日以内の場合、応答時間とリソース消費を最適化する必要があります。 この知識は、インデックスを選択するときに役立ちます。

MySQLの実装

MySQLを選択したのは、このデータベースに精通していることと、このタスクに比較的適しているためです。



少し異なる3つのアプローチを試してみましょう：単純なInnoDB、InnoDB +主キー（date_start、id）およびMyISAM。 テーブルの構造は3つすべてのアプローチで同じであり、違いはエンジンとインデックスに課すもののみです。

 CREATE TABLE `tours` ( `tour_id` bigint(20) NOT NULL, `package_id` bigint(20) NOT NULL, `date_start` tinyint(4) NOT NULL, `stars` tinyint(4) NOT NULL, `pansion` tinyint(4) NOT NULL, `nights` tinyint(4) NOT NULL, `region_id` smallint(6) NOT NULL, `hotel_id` int(11) NOT NULL, `airport_id` tinyint(4) NOT NULL, `price` smallint(6) NOT NULL )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Innodb

最初に、標準インデックスPRIMARY KEY (`tour_id`), KEY `date_start` (`date_start`)



とともに標準InnoDBエンジンを使用してみてください。 date_startのキーは、出発日の範囲によって選択を高速化し、primary KEY tour_idは、tour_idが単調に増加することを期待して作成され、最適な挿入速度を提供します。



MySQL 5.7.17のデフォルトのインストールにデータを貼り付けます（innodb_buffer_pool_size = 128 MB、ネタバレの下の残りの設定を参照）：

 LOAD DATA LOCAL INFILE 'tours.csv' INTO TABLE tours FIELDS TERMINATED BY ','
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

充填中に、ボトルネックがクライアントではなくMySQLサーバーにあることを確認し（クライアントのCPU使用率が100％であってはなりません）、リクエストの実行時間を確認します。

 Query OK, 10000000 rows affected, 11 warnings (1 min 35.09 sec) Records: 10000001 Deleted: 0 Skipped: 1 Warnings: 11
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

InnoDBでは、1000万件のレコードを挿入するのに1.5分でかまいません。 列名がそこに書き込まれているため、欠陥はCSVファイルの最初の行を参照します。



結果のテーブルサイズは、490 MBのデータと162 MBのインデックスです。 クエリの実行中、MySQLは3.5 GBのディスクに書き込み、CPU負荷は約100％でした（テストラップトップでは4コアのうち1コアが使用されました）。



検索クエリを実行する前に、データを挿入するための他の可能なオプションを見てみましょう。

InnoDB +主キー（date_start、tour_id）

ほとんどのリクエストが出発日の比較的狭い範囲にのみ影響することが事前にわかっているため、tour_idの代わりに出発日でデータをクラスター化できます-この場合、date_startによる読み取りはディスク（またはbuffer_pool）からのランダムな読み取りを少なくします。このようなテーブルはより高速になります。



挿入の様子を見てみましょう。

 Query OK, 10000001 rows affected, 10 warnings (1 min 13.34 sec) Records: 10000001 Deleted: 0 Skipped: 0 Warnings: 10
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

予想に反して、そのようなテーブルへの挿入は、「通常の」構造への挿入よりも少し高速でした。 これは、おそらく「余分な」date_startインデックスがなく、PRIMARY KEYに含まれているためです。 前回よりも少し少なく、わずか3 GBしか書き込まれていません。



テーブルのサイズは538 MBのデータと0 MBのインデックスです（セカンダリインデックスはありませんが、PRIMARY KEYはInnoDBのデータの一部として書き込まれます）

マイサム

昔、MyISAMはMySQLのデフォルトエンジンであり、FULL SCANの挿入と実行の速度で有名でした。 私たちの仕事の理想的な候補ではないものは何ですか？ テーブル構造は、InnoDBの最初のバージョンと同じままです。

 Query OK, 10000000 rows affected, 11 warnings (40.39 sec) Records: 10000001 Deleted: 0 Skipped: 1 Warnings: 11
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果は確かに優れていますが、桁違いではありません。 設定を調整することで、MyISAMの場合とInnoDBの場合の両方でより良い結果を得ることができますが、これは別の大きな記事のトピックです。 350 MBがディスクに書き込まれ、テーブルのサイズは286 MBのデータ+ 205 MBのインデックスでした。 楽器によると、MyISAMのテーブルのサイズよりも小さいサイズがディスクに書き込まれたのはなぜかわかりませんでした。 読者は、MySQLインスタンスで実験を再現して、数値を比較しようとする場合があります。

クリックハウス

最後のオプションは、 ClickHouseというYandexのデータベースです 。 これは、クエリの自動並列化とその他のnishtyakovの束を持つ列データベースです。 LinuxおよびDockerコンテナ用のバイナリアセンブリもあり、macOSサポートが発表されています。 macOSのバイナリアセンブリが見つからなかったため、 yadi.sk / d / RffdbxaM3GL7Acを収集してレイアウトしました （アセンブリにはgcc-6、数十ギガバイトのスペース、ラップトップで約2時間必要です）。 macOSでテストを行います。



まず、テーブルを作成します。

 CREATE TABLE tours ( tour_id UInt64, package_id UInt64, date_start UInt8, stars UInt8, pansion UInt8, nights UInt8, region_id UInt16, hotel_id UInt32, airport_id UInt8, price UInt16, date Date MATERIALIZED toDate(date_start) ) ENGINE = MergeTree(date, date_start, 8192)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

タイプMergeTreeのテーブルを作成する場合（デフォルトで推奨）、日付付きの列を指定する必要があります（シャーディングが実行されます）。 ここで私は少し怠けていて、値toDate（date_start）を持つMATERIALIZED日付列を作成しました。 実際のデータでは、Date型のdate_start列を作成する方がはるかに簡単です。クリックハウスでは、列の圧縮のために日付が多くのスペースを占有しません。

ClickHouse、データ挿入

次に、データを挿入します。

 $ cat tours.csv | pv | clickhouse --client --query 'INSERT INTO tours FORMAT CSVWithNames' 524MiB 0:00:04 [ 123MiB/s]
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

pvユーティリティがまだインストールされていない場合は、インストールすることをお勧めします。これにより、操作の進行状況を出力し、パイプを通過したバイト数を表示できます。



csvファイルの挿入が約100 Mb / sの挿入速度で4秒で行われたことがわかります（著者によると）。 挿入時に、サーバーは7秒のCPU時間を費やしました。



データサイズは378 MBであり、378 MBもディスクに書き込まれました（ほとんどの場合、ボリューム全体がメモリ内でソートされ、1つのピースに記録されました）。



残念ながら、テストデータはランダムであるため、列の圧縮から大きな利点を引き出すことができませんでした。 実際のデータはより適切に圧縮される必要があるため、最終サイズはさらに小さくする必要があります。

データ挿入時間、比較

1つのテーブルにすべての挿入インジケーターを収集しましょう。

方法	時間	テーブルサイズ	ディスクに書き込みました
Innodb	95秒	652 Mb	3.5 GB
InnoDB + PK	73秒	538 Mb	3.0 GB
マイサム	40秒	491 Mb	350 MB？
クリックハウス	4秒	378 Mb	378 Mb

サンプリング

ツアーを検索する際に、多少なりとも真実の種類のクエリに対応するいくつかの選択を行います。

 -- 1. « » SELECT * FROM tours ORDER BY price ASC LIMIT 20; -- 2. « »   10  (1/9  ) SELECT * FROM tours WHERE date_start BETWEEN 40 AND 50 ORDER BY price ASC LIMIT 20; -- 3. 5     500  600  SELECT * FROM tours WHERE nights = 5 AND price BETWEEN 500 AND 600 ORDER BY price ASC LIMIT 20; -- 4.   500  600 ,      SELECT * FROM tours WHERE price BETWEEN 500 AND 600 ORDER BY nights DESC, stars DESC LIMIT 20; -- 5.      SELECT * FROM tours WHERE date_start BETWEEN 10 AND 20 AND hotel_id = 767036 ORDER BY price LIMIT 20;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

MyISAMでは、IGNORE INDEX（date_start）が指定されている場合、クエリ時間は括弧内に表示されます。

ClickHouseの場合、*ではなくtour_idとpriceのみを選択すると、クエリ時間が括弧内に表示されます。



結果は次のとおりです。

方法	1ミリ秒	2ミリ秒	3ミリ秒	4ミリ秒	5ミリ秒
Innodb	4300	3800	3800	3800	3700
InnoDB + PK	4600	600	4000	4000	540
マイサム	1300	1600 （1000）	800	700	1500（670）
クリックハウス	120（40）	40（14）	150（54）	180（80）	14（8）

コールドキャッシュ（ラップトップSSD）でも同じです：

方法	1ミリ秒	2ミリ秒	3ミリ秒	4ミリ秒	5ミリ秒
Innodb	5500	5000	4800	4700	4700
InnoDB + PK	12700	1960	12100	12000	1720
マイサム	14800 （1060）	800	920	799	14900（767）
クリックハウス	570（188）	177（90）	591（224）	608（254）	122（55）

分析

チューニングなしで、MyISAMはMySQLに最適であり、date_startインデックスは、特にコールドキャッシュの場合にのみ悪化します（これは、MyISAMの文字列へのランダムアクセスが非常に効率的ではないためです-常に1つずつ読み取りますコールドキャッシュの場合、SSD上でも壊滅的な結果をもたらすread（）システムコールを含む行）。 一部のリクエストは、InnoDB + PKスキームでより高速に実行されますが、ウォームキャッシュでのみ実行されます。 コールドスタートの場合、MyISAMの方がはるかに適切であり、date_startにインデックスがありません（代わりに、日付範囲でテーブルを展開し、クエリを並行して開始できます）。



ClickHouseは列ベースであるため、SELECT *という形式のクエリはあまり便利ではありません。これは、クエリの実行にかかる時間で確認できます。 可能であれば、必要な列のみを選択する必要があります（いずれにしても、tour_idのメタ情報は信頼できるリポジトリのどこかに複製する必要があり、ClickHouseから直接取得する代わりにそこから取得できます）。 また、ClickHouseは利用可能なすべてのカーネルへのリクエストの実行を自動的に並列化し、必要な列のみをスキャンするため、キャッシュがウォームアップされると非常に高速に動作します。 コールドスタートの場合、すべてのスピーカーではなく、必要なスピーカーのみを選択することが望ましい理由は特に顕著です。



ただし、コールドスタートでも、ClickHouseは例外なく、すべてのケースでクエリ実行時間の点でリードしています。

おわりに

この記事では、MySQL（3つの異なるアプローチ）とClickHouseに基づいて、ストップを考慮せずにツアーを検索するための簡略化されたモデルを構築しました。



この記事が、ツアーで検索を行う人の検索速度を向上させ、検索コストを削減し、ClickHouseがどのタスクに適しているかについての基本的な理解を与えることを願っています。 最後まで読んでくれてありがとう、コメントを聞いてうれしいです。



PS MySQLとClickHouseを隠して結果を改善し、ベンチマークを公開できる場合は、コメントに必ず記入してください。これは記事への追加として役立つでしょう。

参照資料

1. クリックハウス
2. ツアーデータジェネレーター
3. macOS用にコンパイルされたClickHouseバイナリ