こんにちは。
データインテグレーション開発グループの長谷川です。
弊グループではぐるなびのビッグデータを使ったサービスの開発業務を行っており、データライブラリもその1つとなります。
データライブラリはPhase1リリースして1年以上が経過し、今回なぜPhase2として改修を行ったのかという理由や、改善箇所などを説明させていただきたいと思います。
また、最後に飲食店メニューにおける今年のトレンドについて、データライブラリのデータから簡単にご紹介させていただきます。
ぐるなびデータライブラリとは
ぐるなびは、日本国内における飲食店検索サービスの中でも 最大級の飲食店登録数 と 正確な飲食店情報の掲載 を特徴としています。
ぐるなびデータライブラリは、その膨大な飲食店ビッグデータを収集・分析し、食品メーカーなど食に関わる企業が抱える「何が売れるのか？」「誰が買うのか？」といった疑問に対して、営業戦略マーケティングや商品開発に必要な情報を引き出せる、オンラインツールサービスです。
機能概要
ぐるなびデータライブラリでは以下のような機能を提供しています。
推移分析
全国の飲食店の登録メニューの取扱い数推移を時系列で集計し、メニューや食材の提供・ニーズ推移を分析を行うことで、アイデアのスクリーニングや説得力のある提案書作りに役立ちます。
急上昇ランキング
飲食店の登録メニュー情報、およびユーザーの検索クエリをランキング表示し、今、どういったメニューやキーワードが注目されているかを可視化します。
組合わせ分析
指定したキーワードと同時にメニュー内に出現するキーワードを分析。
メニューの味つけや修飾語、食材産地といった詳細把握ができ、メニュー名や商品名のアイデアの発想に役立ちます。
店舗分析
指定したエリアの飲食店分布を業態・席数・予算や、ユーザーの年代・性別など、多様な軸でメニューの取扱状況を可視化し、営業戦略に活用できます。
レポートライブラリ
毎月、飲食店、ユーザーに向けて実施している独自調査レポートをダウンロード可能。
外食シーンの実態理解に役立ちます。
Phase2の開発理由
ぐるなびデータライブラリは2017年6月からスタートさせたサービスですが、その運用を通していくつもの課題が浮かび上がってきました。
データライブラリPhase1では、加盟店様が取り扱うメニューをキーワード単位で集計するために、メニュー名を形態素解析で分かち書きし、その結果をキーワードという形で取り扱っていました。
飲食店の提供するメニューを見ていただければすぐにわかると思いますが、メニューの中には同じ意味合いのものでも表記が異なるものが多々あります。
たとえば、サワーなどでおなじみの「シークヮーサー」などは「シークァサー」「 シークァーサー」「シークヮーサー」「 シークワサー」「シークワーサー」など、多くの表記が存在します。
Phase1ではこれらすべてを別々のキーワードとして扱っていたため「シークヮーサー」としての分析が難しいという問題がありました。
「シークヮーサー」推移検索の結果の例
「 シークァーサー」「 シークワサー」「シークワーサー」が分かれて集計されている
さらに、外食のメニューでは様々な単語を組み合わせた表記もよく登場します。
たとえば「唐揚げ」だけでも 、「若鶏の唐揚げ」「チキンの唐揚げ」「軟骨の唐揚げ」などの組み合わせ（本当はもっともっと沢山あります）が考えられます。
Phase1では、それぞれが「若鶏」「チキン」「軟骨」「唐揚げ」 に分割されていたため、「チキンの唐揚げ」だけを調べることが難しいという課題もありました。
それと同時に、メニューを分かち書きする弊害として、レコード数が大きく膨らみデータベースを圧迫するという問題も孕んでいました。
たとえば
「もも唐揚げが食べ放題！エビチリいためや牛スジポン酢などもついて120分飲み放題！」
というメニューがあったとします。
これはメニューとしては1レコードですが、形態素解析で分かち書きをすると
「もも」「唐」「揚げ」「が」「食べ」「放題」「！」「エビ」「チリ」「いため」「や」「牛」「スジ」「ポン酢」「など」「も」「つい」「て」「120」「分」「飲み」「放題」
の21レコードに膨れ上がってしまいます。
このように 機械的に分かち書きしてしまうと レコード数が無尽蔵に増え続けてしまいます。
Phase1では、 「特定の品詞のみを許可する」「NGワードに載っているキーワードは登録しない」「店舗の取扱いが２件以上キーワードのみに絞る」などの処理を施し、レコード数が不用意に増えないように制限をかけていました。
ほかにも、メニューに登場するキーワードの組み合わせを知りたい（これはPhase2で実装「組み合わせ分析」として実装されました）というような機能面の要望も多々あがっていため、これらを解決すべくPhase2の開発をスタートさせました。
有効語辞書の採用
Phase2では、Phase1の課題に対する解決策として有効語辞書を採用しました。
有効語辞書とは、表記ゆれのあるキーワードや、表記は別々だが同じ意味を指すキーワードなどをまとめた辞書をあらかじめ用意し、クエリ実行時に辞書を参照することで 出力結果の精度を向上させるための仕組みを指します。
これにより
「シークァサー」「 シークァーサー」「シークヮーサー」「 シークワサー」「シークワーサー」
はすべて
「シークヮーサー」
としてまとめることが可能になります。
また
「若鶏の唐揚げ」
も
「若鶏の唐揚げ」「若鶏」「唐揚げ」
を個別に集計することができるようになります。
反面、辞書そのもののメンテナンスをし続けなければならないというデメリットや、辞書の精度が低く未登録のキーワードが多い場合に思ったような集計結果にならない という問題も発生する可能性があります。
そこでまず辞書の初期作成時に、ぐるなびビッグデータ担当の企画チームが飲食店のメニューを徹底的に調査し、メニューに関係する有効語約７万語を収集・整理しました。
この結果、リリースに先駆けてかなり精度の高い有効語辞書を事前に用意することができました。
また有効語辞書は一度作って終わりということはなく、増え続ける新語に柔軟に対応できるように予めメンテナンスコストを下げるような設計をしておく必要があります。
そのためPhase2ではプログラムのロジックの大幅な見直しを行い、ワードIDベースだったテーブル構成をメニューIDベースとなるようデータを１から作り替えて、新たにメニューと辞書の関連を持つメニュー辞書というテーブルを追加しました。
この構成にすると、有効語辞書にキーワード追加があった場合でもメニュー辞書テーブルを作り替えるだけでよく、データ全体の再作成を行わずに済むためメンテナンスコストを最小限に抑えることが可能になります。
Phase2におけるメニューと有効語辞書の関係性
ここで注意すべき点として、ぐるなびデータライブラリが対象とするメニューは2018年11月時点で5000万レコードにも及び、これに対して有効語約7万のリレーションを作成するため、メニュー辞書テーブルの作成をいかに高速に行うかが課題となります。
また有効語辞書にない語が検索された場合であっても、結果の取りこぼしが発生しないようにする必要があります。
Phase2では、辞書にヒットしなかった時はメニューの全文検索によって集計を行う仕組みを新たに用意することで、辞書にないメニューも正しく集計できるように作られています。
バッチ処理の開発
データライブラリの全てのデータは週次のバッチ処理で生成されます。
バッチ処理では、2系統の処理を行います。
メニュー抽出処理
形態素解析をベースとした独自ルールを用い、メニュー情報と有効語辞書の紐づけをしたメニュー辞書テーブルを生成します。データ抽出処理
メニューや検索ログの履歴から週次や月次の推移データを作成します。
週次バッチはこれらすべての処理を数時間で完了させる必要があり、複数の技術を組み合わせることで短時間での実行完了を実現しています。
メニュー抽出処理
ぐるなびのビッグデータ基盤はMapRで構成されており、MapR Converged Data Platform上でデータを管理します。
MapR Converged Data Platformは様々なファイルインターフェイスを提供しており、NFSを経由したファイルの読み込み/書き込みもその機能の一つとなります。
NFSファイルインターフェイスにより、プログラム側からはNFSマウントされたファイル群として扱いつつ、そのファイル群に対して別のプログラムからThriftプロトコルを経由してHiveクエリが実行できるのが大きなメリットです。
データライブラリではこの機能を利用して、NAS上のファイルをINPUTとしてメニュー抽出処理を行い、OUTPUTしたファイル群を別のプログラムからhiveクエリで操作するというような構成となっています。
MapRの機能を使ったメニュー抽出の流れ
メニュー抽出は5000万以上のメニューを1件ずつ形態素解析し、約7万件の有効語辞書をルールベースで適用した後、その結果からメニュー辞書テーブルを作成するため、最終的に出来上がるメニュー辞書テーブルの件数は4億件以上に上ります。
この規模のデータ処理を高速に完了させるため、メニュー抽出ではhadoop Streamingを用いた並列処理を実装し、１時間内の処理完了を実現できています。
hadoop streamingによるメニュー抽出の実行フロー
データ抽出処理
データ抽出処理ではメニューや店舗情報が格納されている複数のテーブルに対してHiveQLを発行してデータの集計を行います。
Phase1ではPythonで書かれたバッチでこの処理を行っていましたが、このバッチは直列実行を想定して作られていたため、MapRノードに余裕があっても直前のクエリが完了しないと次のクエリが実行できず ボトルネックとなっていました。
そこでPhase2ではバッチ処理をすべてgo言語で書き直し、並行でクエリを実行することにより処理時間の短縮を図りました。
goのhive実行パッケージはgohiveを利用しています。
懸念点だった実行時間についても、4時間近くかかる処理時間を 並行処理により 2時間半まで短縮することができました。
サーバサイドプログラム
データライブラリはデータベースにMySQLを、サーバサイド言語はPHPとGoを使っています。
PHPは主にコンテンツの初期描画のみに使われ、メインコンテンツJavascriptの非同期通信をGo言語のAPIが受け取って表示します。
コンテンツ描画のイメージ
この構成はPhase1で採用されたもので、Phase2でも引き続きこの構成で実装されています。
処理概要
Phase1とPhase2の違いを簡単に説明します。
Phase1は期間テーブルごとに期間ID-キーワードID-店舗IDをプライマリキーとした期間データを格納し、入力された期間とキーワード、その他検索条件から店舗を集計してその結果を表示していました。
Phase2ではこの構成を変更し、期間ID-メニューID-店舗IDをプライマリキーとしたテーブル構成としました。
これにより辞書テーブルから入力語を検索して辞書IDを特定し、次にその辞書IDをキーにメニュー辞書テーブルから該当のメニューIDを検索、ヒットしたメニューIDで期間テーブルを検索して該当IDを持つ店舗を集計する形に処理を変更しました。
また辞書テーブルに検索語が存在しない場合は、メニューマスタを検索語で検索し部分一致したメニューIDで期間テーブルを検索することで、新語など辞書に含まれないキーワードであっても取りこぼさない処理を追加しました。
テーブル設計の工夫とGo言語によるAPI開発
データライブラリは1週間あたり1,000万以上のデータ（Phase1では4,000万件）を持つため、期間に応じてテーブルを分割しています。
週次データであれば2か月分(8～9週)を1テーブルとし、年間で6テーブルに分ける形になります。
期間ごとにテーブルを分けて週ID（ここではweek_idとします）の分散度合いをバランスすることで、クエリの効率が上がり、またフルスキャンの場合もスキャン対象が減るため、検索処理の高速化が期待できます。
このようにアクセスするテーブルが分割されている場合、期間に応じて複数のテーブルに対して並行処理でデータ取得ができれば より速くデータを取得することが可能になるため、APIの殆どは容易に並行処理が可能なGo言語で実装しました。
テーブル分割とAPIリクエストのイメージ
プログラムとクエリの最適化
データライブラリでは、プログラム速度を向上するために いくつもの最適化を行っています。
テンポラリテーブルの使用
たとえば「サラダ」を検索語として入力した場合、メニュー名や説明文に「サラダ」を含むメニューを持つ店舗を取得することが目的となります。
この処理を週次テーブルに対して実行する場合、まず辞書テーブルからサラダに対応する辞書IDを取得し、存在すれば次にメニュー辞書テーブルからメニューIDを、存在しなければメニューマスタからメニューIDを取得します。
その後、該当のメニューIDを週次デーブルから検索するためのIN句を作成しますが、「サラダ」の場合、対象となるメニューは約200万にもなるため、実行計画の statistics だけで5秒以上かかっていました。
mysql> SET profiling=1; mysql> SELECT * FROM weekly_YYYY_1 WHERE week_id=20180101 AND menu_id IN ( ..... <- メニューのIDをIN句で指定、サラダであれば約200万となる );
mysql> show profile; +----------------------+-----------+ | Status | Duration | +----------------------+-----------+ | starting | 0.502792 | | checking permissions | 0.000010 | | Opening tables | 0.000013 | | init | 0.577781 | | System lock | 0.000010 | | optimizing | 0.114398 | | statistics | 5.784587 | <- 「サラダ」を含むメニューが多いためstatisticsに時間がかかっている | preparing | 0.049519 | | executing | 0.000007 | | Sending data | 15.695182 | | end | 0.000009 | | query end | 0.000005 | | closing tables | 0.000006 | | freeing items | 0.035395 | | logging slow query | 0.009801 | | cleaning up | 0.000118 | +----------------------+-----------+
この部分を高速化するため、アクセスごとにメニューIDをINSERTしたテンポラリテーブルを用意し、期間テーブルとJOINする形に変更しました。
mysql> SET profiling=1;
mysql> CREATE TEMPORARY TABLE tmp_table <- テンポラリテーブルをMEMORYストレージエンジンで作成
(
menu_id INTEGER,
index(menu_id)
)
ENGINE=MEMORY DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tmp_table SELECT menu_id FROM menu_dict WHERE dict_id=XXXXX;
Query OK, 1973492 rows affected (1.27 sec) <- 「サラダ」を含むメニューIDをテンポラリテーブルにINSERT
Records: 1973492 Duplicates: 0 Warnings: 0
mysql> SELECT count(1) FROM weekly_YYYY_1 a
INNER JOIN tmp_table b ON a.menu_id = b.menu_id <- テンポラリテーブルとJOINする
WHERE week_id=20180101;
mysql> show profile; +----------------------+-----------+ | Status | Duration | +----------------------+-----------+ | starting | 0.000068 | | checking permissions | 0.000002 | | checking permissions | 0.000002 | | Opening tables | 0.000008 | | init | 0.000011 | | System lock | 0.000004 | | optimizing | 0.000009 | | statistics | 0.000055 | <- テンポラリテーブルをJOINしたためstatisticsのコストが不要になる | preparing | 0.000012 | | executing | 0.000002 | | Sending data | 16.377718 | | end | 0.000008 | | query end | 0.000005 | | closing tables | 0.000006 | | freeing items | 0.000014 | | logging slow query | 0.000001 | | cleaning up | 0.000002 | +----------------------+-----------+
テンポラリへのインサートが発生するものの、INを使ったクエリに比べて4秒以上処理を高速化できます。
テンポラリテーブルを使う際の注意として、サイズを正しく指定しないと途中で「converting HEAP to MyISAM」が走り、MyISAMへの変換が入るか、サイズ上限を超えてエラーになることがあります。
これを避けるために、予め max_heap_table_size と tmp_table_size を適切なサイズに指定しておきます（ただしテンポラリテーブルの多用はMySQLサーバのメモリ領域の圧迫を招く可能性があるため、あらかじめ最大接続数を見積もっておく必要があります）。
テンポラリテーブルのサイズを1Gに拡張する場合 mysql> SET max_heap_table_size = 1073741824; mysql> SET tmp_table_size = 1073741824;
また、Goでテンポラリテーブルを使う場合の注意点として、Goのsqlクライアントはコネクションプーリングを行うためテンポラリテーブルを作成したクエリと、インサートするクエリ、およびSELECTするクエリが必ず同じコネクションとなるようにしておく必要があります。
そのために予めトランザクションを発行しておき、同一トランザクション内でテンポラリテーブルの作成、インサートとSELECTを実行する必要があります。
// テンポラリテーブル作成からSELECTまでを1トランザクションで実行する // トランザクションを発行 tx, err := db.Begin() if err != nil { // エラー時の処理 } defer tx.Commit() // テンポラリテーブルを作成 query := "CREATE TEMPORARY TABLE ...." _, err := tx.Query(query) if err != nil { // エラー時の処理 } // テンポラリテーブルにメニューIDをINSERT query := "INSERT INTO XXXX VALUES ..." _, err := tx.Query(query) if err != nil { // エラー時の処理 } // SELECTの発行 query := "SELECT XXXX FROM ... " rows, err := tx.Query(query) // 後続処理 ...
テキスト連結から[]byteに変更する
INからテンポラリテーブルに変更する際、メニューIDをINSERTする部分も時間がかかっていたため、合わせてこちらの見直しも行いました。
修正前の実装部分は以下のようになっていました。
n:= len(in) s := "INSERT INTO XXXXX VALUES ("+fmt.Sprint(in[0])+")" for i := 1; i < n; i++ { s += ",("+fmt.Sprint(in[i])+")" } return s
この部分を予めIN句に入るワード数の長さを持つbyte型のスライスを作成して[]byteとしてappendし、最後にstringにキャストするように処理を変更しました。
n := len(in) var s = make([]byte, 0, n) for i := 0; i < n; i++ { if i == 0 { s = append(s, "("...) } else { s = append(s, ",("...) } s = append(s, fmt.Sprint(in[i])...) s = append(s, ")"...) } return "INSERT INTO XXXXX VALUES " + string(s)
実際にどれくらい変わるかをベンチマークで計測します。
package main import ( "fmt" "testing" ) func BenchmarkIntAdd(b *testing.B) { s := " IN ("+fmt.Sprint(0) b.ResetTimer() for i := 0; i < b.N; i++ { s += ","+fmt.Sprint(i) } s += ")" } func BenchmarkByteBuffer(b *testing.B) { n := b.N var s = make([]byte, 0,n) for i := 0; i < n; i++ { if i == 0 { s = append(s,"("...) } else { s = append(s,",("...) } s = append(s,fmt.Sprint(i)...) s = append(s,")"...) } }
$ go test -bench . -benchmem testing: warning: no tests to run BenchmarkIntAdd-8 200000 130510 ns/op 618417 B/op 3 allocs/op BenchmarkByteBuffer-8 10000000 207 ns/op 70 B/op 2 allocs/op PASS
この修正により、「サラダ」のようなメニューの多いキーワードで 最大で10秒ほど改善がみられました。
インデックスの見直し
このように個別に対応を入れていったものの、クエリのパフォーマンスはなかなか改善せず、特に複数のテーブルにSELECTを実行する推移検索は速度遅延が顕著でした。
以下に週ごとの検索ワードの取扱店舗数を取得するクエリの例を示します。
mysql> SELECT
-> week_id,
-> SUM(amount) FROM (
-> SELECT week_id, shop_id, MAX(amount) as amount
-> FROM
-> weekly_YYYY_1 a
-> INNER JOIN
-> tmp_table
-> ON
-> a.menu_id = tmp_table.menu_id
-> WHERE
-> week_id >= 20150701
-> AND
-> week_id <= 20150729
-> GROUP BY week_id, shop_id
-> ) B GROUP BY week_id;
このクエリを実行すると、1weekテーブルあたり1分以上かかっていました。
+-----------+-------------+ | period_id | SUM(amount) | +-----------+-------------+ | 20150706 | 54316 | | 20150713 | 54324 | | 20150720 | 54339 | | 20150727 | 54368 | +-----------+-------------+ 4 rows in set (1 min 19.39 sec)
weekテーブルは1年ごとに6分割しているため、3年間の推移情報を取得する場合18テーブルに対して並行処理を行う必要があります。
実際にその範囲で取得を行おうとすると、一つ一つのクエリの実行時間が長いため、5分以上かかっても処理が完了しないというような状態に陥っていました。
これを解消するため、まずEXPLAINを発行してどこがボトルネックになっているかの調査を開始しました。
mysql> explain SELECT
-> week_id,
-> SUM(amount) FROM (
-> SELECT week_id, shop_id, MAX(amount) as amount
-> FROM
-> week_YYYY_1 a
-> INNER JOIN
-> tmp_table
-> ON
-> a.menu_id = tmp_table.menu_id
-> WHERE
-> week_id >= 20150701
-> AND
-> week_id <= 20150729
-> GROUP BY week_id, shop_id
-> ) B GROUP BY week_id;
+----+-------------+------------+-------+---------------------------+-------------------+---------+--------------------------------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------------------+-------------------+---------+--------------------------------+----------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 11126202 | Using temporary; Using filesort |
| 2 | DERIVED | tmp_table | index | PRIMARY | PRIMARY | 4 | NULL | 1854367 | Using index; Using temporary; Using filesort |
| 2 | DERIVED | a | ref | PRIMARY,menu_id_week_id | menu_id_week_id | 4 | XXXXX.tmp_table.menu_id | 6 | Using index condition |
+----+-------------+------------+-------+---------------------------+-------------------+---------+--------------------------------+----------+----------------------------------------------+
3 rows in set (0.00 sec)
Using index conditionが表示されているため、WHERE・JOINで指定したカラムについてのインデックスは正しく貼られていますが、他の処理でテーブルスキャンが起きている可能性が考えられます。
週次テーブルのインデックスを見ると、WHEREおよびJOINに使われるカラムにインデックスが正しく貼られているため、サブクエリ内のSELECT句内のMAX関数が原因である可能性が高いと判断しました。
week_YYYY_1テーブルのインデックス
mysql> show index from week_YYYY_1; +-------------------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------------------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | weekly_YYYY_1 | 0 | PRIMARY | 1 | week_id | A | 8248 | NULL | NULL | | BTREE | | | | weekly_YYYY_1 | 0 | PRIMARY | 2 | shop_id | A | 1536905 | NULL | NULL | | BTREE | | | | weekly_YYYY_1 | 0 | PRIMARY | 3 | menu_id | A | 70697642 | NULL | NULL | | BTREE | | | | weekly_YYYY_1 | 1 | menu_id_week_id | 1 | menu_id | A | 3213529 | NULL | NULL | | BTREE | | | | weekly_YYYY_1 | 1 | menu_id_week_id | 2 | week_id | A | 70697642 | NULL | NULL | | BTREE | | | +-------------------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
例えばA店が「チキンサラダ」「コブサラダ」「海藻サラダ」など「サラダ」のつくメニューを多く取り扱っていて、 他方B店は「チキンサラダ」のみ取り扱っていた場合があるとします。
データライブラリの仕様上、「サラダ」を取り扱う店舗としては それぞれ1店舗ずつと見なす必要がありますが、 A店のような場合、「チキンサラダ」が週のうち2日、「コブサラダ」が7日扱われていたとすると、7日の方を採用すると いうルールがあります。
そのため、MAX関数を含んだうえでクエリを高速化する必要があり、検索する条件も多岐にわたるので事前集計も難しい という状態で、試行錯誤するものの依然としてクエリの速度は改善されませんでした。
そのような折、MySQLのリファレンスに目を通していると、インデックスに関する記述があるのを見つけました。
https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html
https://dev.mysql.com/doc/refman/5.6/ja/mysql-indexes.html
MySQL はこれらの操作にインデックスを使用します。 -- 中略 -- 特定のインデックス設定されたカラム key_col に対して、MIN() あるいは MAX() 値を見つけるため。
極値関数の対象カラムがインデックスにある場合、MySQLはテーブルスキャンを行うインデックス内で極値をカバーすると書かれており、早速期間テーブルにSELECT句、WHER句、ON句、それに極値関数すべてをカバーするための menu_id_week_id_amount インデックスを追加しました。
menu_id_week_id_amount追加後のweekly_YYYY_1テーブルのインデックス
+---------------+------------+------------------------+--------------+-------------+-----------+-------------+-----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------+------------+------------------------+--------------+-------------+-----------+-------------+-----------+--------+------+------------+---------+---------------+ | weekly_YYYY_1 | 0 | PRIMARY | 1 | week_id | A | 9431 | NULL | NULL | | BTREE | | | | weekly_YYYY_1 | 0 | PRIMARY | 2 | shop_id | A | 1464024 | NULL | NULL | | BTREE | | | | weekly_YYYY_1 | 0 | PRIMARY | 3 | menu_id | A | 74665254 | NULL | NULL | | BTREE | | | | weekly_YYYY_1 | 1 | menu_id_week_id | 1 | menu_id | A | 6222104 | NULL | NULL | | BTREE | | | | weekly_YYYY_1 | 1 | menu_id_week_id | 2 | week_id | A | 74665254 | NULL | NULL | | BTREE | | | | weekly_YYYY_1 | 1 | menu_id_week_id_amount | 1 | menu_id | A | 6222104 | NULL | NULL | | BTREE | | | | weekly_YYYY_1 | 1 | menu_id_week_id_amount | 2 | week_id | A | 74665254 | NULL | NULL | | BTREE | | | | weekly_YYYY_1 | 1 | menu_id_week_id_amount | 3 | amount | A | 37332627 | NULL | NULL | | BTREE | | | +---------------+------------+------------------------+--------------+-------------+-----------+-------------+-----------+--------+------+------------+---------+---------------+
この状態でEXPLAINを実行して新たに追加したインデックスが有効か確かめます。
mysql> EXPLAIN SELECT
-> week_id,
-> SUM(amount) FROM (
-> SELECT week_id, shop_id, MAX(amount) as amount
-> FROM
-> weekly_YYYY_1 a force index(menu_id_week_id_amount)
-> INNER JOIN
-> tmp_table
-> ON
-> a.menu_id = tmp_table.menu_id
-> WHERE
-> week_id >= 20150701
-> AND
-> week_id <= 20150729
-> GROUP BY week_id, shop_id
-> ) B GROUP BY week_id;
week_YYYY_1 （この場合は別名なのでa）のindex_commentがUsing index conditionからUsing where; Using indexに変わり、極値探索にインデックスが使われていることが確認できます。
+----+-------------+------------+------+----------------------------------------------------+-----------------------+---------+--------------------------------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+----------------------------------------------------+-----------------------+---------+--------------------------------+----------+---------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 11126202 | Using temporary; Using filesort | | 2 | DERIVED | tmp_table | ALL | PRIMARY | NULL | NULL | NULL | 1854367 | Using temporary; Using filesort | | 2 | DERIVED | a | ref | PRIMARY,menu_id_week_id,menu_id_week_id_amount | menu_id_week_id_amount | 4 | XXXXX.tmp_table.menu_id | 1 | Using where; Using index | +----+-------------+------------+------+-------------------------------------------------+--------------------------+---------+--------------------------------+----------+---------------------------------- 3 rows in set (0.00 sec)
この状態でクエリを実行すると20秒以内に結果が返るようになり、パフォーマンスの向上率は実に7倍以上になりました。
+-----------+-------------+ | period_id | SUM(amount) | +-----------+-------------+ | 20150706 | 54316 | | 20150713 | 54324 | | 20150720 | 54339 | | 20150727 | 54368 | +-----------+-------------+ 4 rows in set (18.90 sec)
個別のクエリの実行速度が飛躍的に向上したため複数期間の並行処理時にクエリが実行待ちとなることがなくなり、処理速度が通常の処理であれば十数秒で完結するようになりました。
急上昇ランキングのロジック見直し
他にもPhase2の際にロジック自体の見直しを行った機能がいくつかあります。
急上昇ランキングは現在の飲食業界のトレンドがわかる人気機能の一つですが、Phase1の急上昇ランキングのロジックは直近のキーワードに極端に強く反応する、また形態素解析の結果を扱うため単語として不十分なものが多く上がってくるなどの問題点があり、候補となったキーワードを運用側で確認して不要な場合は排除する必要がありました。
赤く囲った部分のようにノイズが多く混じっていたため、必要に応じて削除する作業が発生していた
新しい急上昇ランキングでは不要な形態素解析を廃止し、ノイズとなるような語が入り込みにくい設計に変更、急上昇の検知ロジックについても直近だけではなく過去の時系列を考慮するなどのいくつもの見直しを加えました。
この改修の結果急上昇ランキングの精度が飛躍的に向上し、また手動削除の手間が減ったため運用コストを大幅に下げることができました。
2018年10月8日週の急上昇ランキング
2018年10月11日に豊洲市場への移転があったため、急上昇ランキングにも反映されている
こちらの機能は入社二年目の若手メンバーが主力となって推進し、ほぼ彼一人でロジック設計から実装まで手がけました。
target_classオプションを使った検索ノイズ除去
前項でも触れた通り、Phase2では有効語辞書に登録されていない入力語をメニュー本文から部分一致で検索することで取りこぼしを防いでいます。
ここで検索対象となるメニューは5000万件以上あり、メニューそれぞれが「メニュー名」「メニュー説明」など複数のカラムを持つ構造となっているため、それらを高速に検索するためにGroongaを使用しています。
主に新語が入力された場合、有効語辞書に未登録のため全文検索となるケースが大半ですが、稀に飲食と関連が低い語が入力されることもあり、そのような場合はできるだけメニューに関連する語のみを返すのが望ましく、ノイズとなるような語は極力対象から除外する必要があります。
例えば「です」という単語は「あですがた」にも「～です。」にも含まれます。この場合「あですがた」のみを集計対象としたいため、助動詞の「です。」は検索結果から除外することで取扱数の精度が上がる場合があります。
Groongaは8.0.8からTokenMecabにtarget_classというオプションを指定できるようになり、これを使うと指定した品詞のトークンを対象・除外することができます。
データライブラリでは、target_classで「助詞」「助動詞」「フィラー」「記号」を除外指定することで検索結果に不要なメニューがカウントされるのを防いでいます。
管理画面から「です」の検索結果を確認
助動詞の「です」が含まれないことが確認できる
改善結果
Phase2リリース後、データライブラリがどのように改善されたかの一例を以下に示します。
シークヮーサー
シークヮーサーは有効語辞書の採用により一つの語として集計可能となりました。
有効語辞書が使われた場合は
「シークヮーサー は しーくぁーさー, しーくゎーさー, しーくわさー, しーくわーさー, シークァサー, シークァーサー, シークヮーサー, シークワサー, シークワーサー を含んでいます。」
と表記され、どの語が同一語として集計されたかが見てわかるようになっています。
カルパッチョ カルパッチョ -サーモン -アボカド
さらに有効語辞書の導入によって複雑な検索条件の指定も可能になりました。
たとえば「カルパッチョ」のうち「サーモン」「アボカド」を含まないカルパッチョの推移も表示できます。
この場合
「カルパッチョ -サーモン -アボカドは カルパッチョ を含み Salmon, あぼかど, あぼがど, さーもん, アボカド, アボガド, サーモン, 鰐梨 を除いています。」
と表示されます。
データサイズ
また、分かち書きから有効語辞書を使ったメニューIDベースに変更することで、かなりのデータ容量を減らすことができました。
リリース前後のDB容量の推移をグラフで確認すると、実に1/3近くまでサイズを減らすことに成功しています。
データで見る今年のトレンド
最後に、データライブラリから今年のトレンドを簡単に振り返ってみたいと思います。
豊洲市場
2018年の食に関わる大きなイベントとして、築地から豊洲への東京中央卸売市場の移転がありました。
この2市場の検索指数をデータライブラリで可視化すると、移設の完了した2018年10月を境に検索トレンドが逆転していることがわかります。
チーズタッカルビ
続いて今年大流行した「チーズタッカルビ」について取扱店舗推移を見てみたいと思います。
年初から右肩上がりに伸びており、10月頃に若干落ち込みはあるものの、取扱店舗が安定していることから一過性ではなく定番メニューとして認知されたことが確認できます。
パネチキン
同様に今年に入って流行した「パネチキン」については、2018年11月現在において、まだまだ伸びそうな勢いが確認できました。
マジ半端ないって
一部のインターネットユーザーが使っていた「マジ半端ないって」は2018年サッカーワールドカップでの大迫選手の活躍により、一躍今年のトレンド入りしました。
WC2018を契機に、このキーワードを使ったメニューが登場し、メニューとして提供され続けていることがわかります。
へんじんもっこ
トレンドではありませんが、私の地元である新潟県の郷土料理、「へんじんもっこ」が全国でどれくらい取り扱われているのかをデータライブラリで見てみたいと思います。
さすが全国のグルメが集まる東京ですね。データライブラリが対象としている店舗中では、新潟と東京の2都道府県の取扱いでした。
まとめ
Phase2では有効語辞書という仕組みを取り入れた結果、Phase1で指摘されていたデータ処理にまつわる課題を解決でき、より質の高いデータを提供することが可能になりました。
この記事を読んでご興味を持った方がおられたら、こちらからお問い合わせいただけますと幸いです。
引き続き、ぐるなび及びぐるなびデータライブラリをよろしくお願いいたします。
※2018/12/11 13:25 ベンチマークについて一部修正しました
お知らせ
今年はぐるなびアドベントカレンダー2018も実施しています！