一覧検索と分類ごとの件数取得を効率的に行いたい

[rytskywlkr]

現在MySQLを利用しており、Elasticsearchの導入を検討しているものです。

実現したいこと

まずは画面UI上で実現したいことを説明します。

一覧画面で一覧検索と合わせて、サイドバーに件数を表示したいです。

例えばサイドバーには国と承認済み・未承認タグ、およびそれぞれの件数が表示されているとします。
（□はチェックボックス）
ーーーーー
□日本 １０件
□米国 ４件
□中国 ４件

□承認済み １４件
□未承認 ４件
ーーーーー

日本をチェックすると以下の通り、
承認済み、未承認は日本に絞られた件数で再検索されます。（一覧も再検索されます。）
ーーーーー
■日本 １０件 ←ここをチェックすると
□米国 ４件
□中国 ４件

□承認済み ７件 ←件数が変わる（日本１０件のうち承認済みが７件と言う意味）
□未承認 ３件 ←件数が変わる（日本１０件のうち未承認が３件と言う意味）
ーーーーー

準備したもの

取得元のテーブル（MySQL）は以下の通りです。
・一覧テーブル

id	国	承認フラグ	その他一覧情報
１	日本	承認済み	〜
２	米国	未承認	〜

###現状の実現方法
上記を実現するために以下のSQL実行しています。

-- 一覧を表示するためのSQL
select
  その他一覧情報
from
  一覧テーブル
where
  国 in (チェックされた国) and
  承認フラグ in (チェックされた承認フラグ) and
  (その他一覧情報の絞り込み)

-- 国の件数を表示するためのSQL
select
  国，count(*)
from
  一覧テーブル
where
  承認フラグ in (チェックされた承認フラグ) and
  (その他一覧情報の絞り込み)
group by
  国

-- 承認フラグの件数を表示するためのSQL
select
  承認フラグ，count(*)
from
  一覧テーブル
where
  国 in (チェックされた国) and
  (その他一覧情報の絞り込み)
group by
  承認フラグ

現状の実現方法の課題

実際には上記以上にサイドバーのタグが複雑であること、データ量が多いことが原因で、
性能的にかなり問題が出ています。

質問

上記実現したいことを性能問題を回避して実現するために、Elasticsearchを利用して解決することは可能でしょうか。
例えば上記３本のSQLを１つのDSLにして問い合わせることは可能でしょうか。

環境

• MySQL5.7
• PHP7.2
  ※上記、現行環境ですが、あまりこだわりません。

よろしくお願いします。

[st1t (Shota Ito)]

あまり確実なことが言えないので的はずれなことを言っていたら申し訳ないのですが、
いくつか気になるところと参考だけ紹介しようと思います。

気になるところ

• Elasticsearchにはトランザクションがないことは問題にならないでしょうか
• ElasticsearchはあくまでもIndex的な使い方になるので、
  どこかにマスターデータはおいておいたほうが良いと思いますが大丈夫でしょうか
• 今回記載してもらっているような、
  いわゆる全文検索ではない検索の仕方であればAWS AuroraのようなフルマネージドなRDBMSやBigQuery等を検討されたほうが良いかもしれません

質問について

パフォーマンス面で改善できそうか

パフォーマンスについては少なくともボトルネックがどこにあるのかを明確にしないと答えるのは難しいかなと思います。
ただ、データ量が多いとのことですので、一般的にはDISK I/Oが多くてiowaitが発生してレスポンスの低下を招いているのではないかなと思っています。
データ量が多いだけであればElasticsearchはスケールアウトできますが、裏を返すと台数が多くなるので、そもそも何が原因でレスポンスが低下されているのかを明確化されることをおすすめします。

例えば上記３本のSQLを１つのDSLにして問い合わせることは可能でしょうか。

AND条件検索するという認識であれば少し古いですがElastic公式サイトの集約の実行が役立つかなと思います。
詳しくは公式ドキュメントの最新版を見てもらえればと。

ご参考まで

[rytskywlkr]

ご回答ありがとうございます。

Elasticsearchにはトランザクションがないことは問題にならないでしょうか
ElasticsearchはあくまでもIndex的な使い方になるので、どこかにマスターデータはおいておいたほうが良いと思いますが大丈夫でしょうか

マスタデータはMySQLで管理し、MySQLで更新されたデータはLogstashでElasticsearchに連携する想定です。

今回記載してもらっているような、いわゆる全文検索ではない検索の仕方であればAWS AuroraのようなフルマネージドなRDBMSやBigQuery等を検討されたほうが良いかもしれません

ありがとうございます。こちらのキーワードでも調査してみます。
一方でこちらの説明不足で申し訳ないですが、質問文の「その他一覧情報の絞り込み」には全文検索が含まれているため、Elasticsearchを検討しています。

AND条件検索するという認識であれば少し古いですがElastic公式サイトの集約の実行が役立つかなと思います。

なるほど、Aggligationでまとめて実行できそうな気がしました！
全文検索で絞り込みを行なった結果を集計するという方針で検討してみます。
性能面は複数回のSQL検索（全文検索含む）が問題であり、まとめて発行できるかがポイントだと考えています。

曖昧な質問にも関わらず、丁寧にご回答いただきありがとうございました。
こちらで調査して結論が出たら、こちらに記載いたします。

[tsgkdt]

こんにちわ。

左側の件数を表示しているところの２つは、１つに集約できそうに見えました。

以下試してみた内容です。
このようなデータでElasticsearchに入れたとします。

項目	フィールド名	型
国名	country	keyword
承認済みフラグ	approved	boolean
その他項目	test	text
その他項目2	test2	text
その他項目3	test3	text

@st1t さんがいうように集約を使って、国の件数と承認済みフラグの件数を取得してみます。
国ごとの承認済みフラグ件数、承認済みフラグごとの国の件数を取ることができれば、表示側で件数を計算すれば良いかなと思いました。

POST forum-1206/_search
{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "country_aggregations": {
      "terms": {
        "field": "country",
        "size": 10
      },
      "aggs": {
        "approved_aggregations_per_country": {
          "terms": {
            "field": "approved",
            "size": 2
          }
        }
      }
    },
    "approved_aggregations": {
      "terms": {
        "field": "approved",
        "size": 2
      },
      "aggs" : {
        "country_aggregations_per_approved": {
          "terms": {
            "field": "country",
            "size": 10
          } 
          
        }
      }
    }
  },
  "size": 0
}

とすれば、こんな感じになります。
日本で4件あり、そのうち承認済みは3件、未承認は1件というところまでが1回のクエリで取ることができます。

  "aggregations" : {
    "country_aggregations" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "日本",
          "doc_count" : 4,
          "approved_aggregations_per_country" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : 1,
                "key_as_string" : "true",
                "doc_count" : 3
              },
              {
                "key" : 0,
                "key_as_string" : "false",
                "doc_count" : 1
              }
            ]
          }
        },

一覧表示用のクエリも含めて1回で、というのはすぐには思いつかないところです。
何か良い方法がありましたら、私も知りたいです。

[rytskywlkr]

サンプルコードまでありがとうございます！
実現したいことはまさにこの通りです。

自分で詳細の調査をする前ですが、まずは回答させていただきます。

一覧表示用のクエリも含めて1回で、というのはすぐには思いつかないところです。
なるほどです。
queryの結果を一覧表示用にしたいところですが、国名や承認済みフラグで絞り込んでしまうと、
集計結果が正しくなくなってしまうので両立は難しいと理解しました。
（例えばqueryで国名＝日本で絞るとaggsで日本以外の件数が0になってしまうので）

いただいたサンプルコードのquery結果を呼び出し元のコードにて、
国名、承認済みフラグで絞り込むということも考えましたが、
queryのsizeをいくつにすれば良いのか決められないのですね。
（一覧に50件表示する画面として、queryのsizeを100件にしても絞り込んだ結果50件以下になってしまう可能性がある）

query結果をaggsで集計した後に、さらにqueryで絞り込むようなことが１回のリクエストでできるといいのですが・・

引き続き調査いたします。
何か認識相違などあればご指摘いただけると幸いです。

[johtani (Jun Ohtani)]

このへん読んでいただければいいんじゃないかと。
バージョンは古いので、そのままそのクエリが使えるかは怪しいですが、普通にクエリ＋Aggsでやれば、検索結果に対する集計しますよ。
https://www.elastic.co/guide/en/elasticsearch/guide/current/_scoping_aggregations.html

[rytskywlkr]

リクエストをまとめるだけなら、_msearchが使えそうですね。

The body includes the typical search body request (including the query , aggregations , from , size , and so on)

aggregationもできそうです。

ただ全文検索を複数回行う点ではリクエスト複数回と変わらないですね。
全文検索は１度行いその結果に対して、集計や絞り込みを行いたいです。

別件で先ほどいただいたサンプルコードのbucketについてですが、filterを利用して以下でもできるかもと思いました。（まだ環境がないので動作未確認。間違っていたらすみません。）

  "aggs": {
    "country_aggregations": {
      "filter" : { "term": { "approved": true } }, ←承認済みにチェックされていた場合
      "terms": {
        "field": "country",
        "size": 10
      },
    },
    "approved_aggregations": {
      "filters" : { "term": { "country": ['日本'] } }, ←国名にチェックされていた場合
      "terms": {
        "field": "approved",
        "size": 2
      },
    }
  },

[rytskywlkr]

ご回答いただきありがとうございます。

クエリ＋Aggsでやれば、検索結果に対する集計

こちらについては理解していましが、いただいたリンクのglobal指定というのは初見でしたので大変参考になりました。

今回の質問では、globalのように全てのqueryはリセットするのではなく、
queryの条件を１つ（上記例では国名など）を指定して除外した後に、集計ということができれば解決できるのですが、
マニュアルを見たところそういったものは見つけられませんでした。

もし見落としなどがあればご指摘いただけると幸いです。
引き続き調査します。

[rytskywlkr]

マニュアルを見ていたらpost_filterを見つけました。

これがあれば、１つのDSLで実現できるような気がします。以下イメージです。

• queryで「その他一覧情報の絞り込み」を行う
• aggsで「国名」毎の件数を集計する（filterで承認済みフラグを絞り込み）
• aggsで「承認済みフラグ」毎の件数を集計する（filterで国名を絞り込み）
• 集計後にpost_filterで一覧表示用に「国名」と「承認済みフラグ」絞り込み

色々と教えていただいたおかげです。ありがとうございます。

環境構築後に動作確認してまた報告いたします。

[johtani (Jun Ohtani)]

あー、そもそもの質問の仕様を理解してませんでした。
確かにpost_filterでうまいくと思いますが、ちょっと画面の動きなどが気になったので質問です。

そもそもの仕様で質問があるんですが、

1. 日本と米国の両方をチェックするとどうなりますか？0件ですか？
2. 日本と承認済みをチェックするとどうなりますか？日本の件数は変わるんですか？
3. チェックボックスをクリックするとすぐに検索ですか？それともその後検索ボタンをクリックとかですか？その後検索ボタンの場合は組み合わせによっては一覧表示は0件の可能性があると思うんですが、サイドの件数は0件にはならないような表示になるんでしょうか？

総件数の表示などはするのでしょうか？
サイドバーのカテゴリーは更に増えたりするんでしょうか？

[rytskywlkr]

ご質問ありがとうございます。

日本と米国の両方をチェックするとどうなりますか？0件ですか？

この点、説明が不足していてすみません。
日本と米国をチェックすると以下のようになります。

ーーーーー
■日本 １０件 ←ここをチェックした後に
■米国 ４件←さらにここをチェックする
□中国 ４件

□承認済み １０件 ←件数が変わる（日本と米国の１４件のうち承認済みが１０件と言う意味）
□未承認 ４件 ←件数が変わる（日本と米国１４件のうち未承認が４件と言う意味）

※一覧には１４件表示
ーーーーー

ちなみに、さらに中国をチェックすると、
（例の通り、国が３つしかないとしたら）国名は全部チェックしたことになるので、
承認済み、未承認の件数は、国名を全部チェックしていない場合と同じになります。
ーーーーー
■日本 １０件
■米国 ４件
■中国 ４件

□承認済み １４件 ←件数が変わる（日本と米国と中国の１８件のうち承認済みが１４件と言う意味）
□未承認 ４件 ←件数が変わる（日本と米国と中国の１８件のうち未承認が４件と言う意味）

※一覧には１８件表示
ーーーーー

2. 日本と承認済みをチェックするとどうなりますか？日本の件数は変わるんですか？

変わります。
以下のような流れです。
ーーーーー
■日本 １０件 ←まずはここをチェックすると
□米国 ４件
□中国 ４件

□承認済み ７件 ←件数が変わる（日本１０件のうち承認済みが７件と言う意味）
□未承認 ３件 ←件数が変わる（日本１０件のうち未承認が３件と言う意味）
※一覧には１０件表示
ーーーーー

ーーーーー
■日本 ７件 ←件数が変わる（日本１０件のうち承認済みが７件という意味）
□米国 ３件←件数が変わる（米国4件のうち承認済みが３件という意味）
□中国 ４件←件数が変わる（中国4件のうち承認済みが４件という意味）

■承認済み ７件 ←その後にここをチェックすると
□未承認 ３件

※一覧には７件表示
ーーーーー

3. チェックボックスをクリックするとすぐに検索ですか？それともその後検索ボタンをクリックとかですか？その後検索ボタンの場合は組み合わせによっては一覧表示は0件の可能性があると思うんですが、サイドの件数は0件にはならないような表示になるんでしょうか？

すぐに検索です。
(その他一覧情報の絞り込み)で検索結果が０件であれば国名、承認フラグの件数は０になります。

総件数の表示などはするのでしょうか？

検索ヒット件数を表示します。（初期表示は検索条件がないので総件数が表示されます）
post_filter後の値が取れれば良いと考えています。

サイドバーのカテゴリーは更に増えたりするんでしょうか？

増えます。便宜的に国名、承認フラグとしましたが、
別の概念での分類も必要ですし、
さらに国名を選択するとその配下に都市名の一覧が出てきて、
さらに絞り込みが可能になります。

上記ロジックを現状MySQLで実現しているのですが、
一覧取得SQL、国名別件数取得SQL、承認フラグ別件数取得SQLと
分ける必要（それぞれWhere句が異なるため）があり、
また (その他一覧情報の絞り込み)で全文検索もしているので、性能的な課題を抱えています。

Elasticsearchは全文検索ができる事に加えて、
JSONで階層化して結果を返却できる点が上記課題を解決につながると思い、
相談させていただきました。

[rytskywlkr]

post_filterで期待する動作ができたことをご報告いたします。
ご回答いただいた皆様ありがとうございました。