※手法として有償の製品でご存じのものがあれば教えていただきたいです。
400万件程の商品マスタ情報を持つテーブルAと、300件程度の商品情報をもつテーブルBを製品型名で前方一致の突合せをしたいと考えております。
結合条件を、製品型名の完全一致で結合(JOIN)した時のパフォーマンスは、0.001秒台と問題ないのですが、前方一致(LIKE)でJOINすると120～220秒程度で著しく遅くなります。
この著しいパフォーマンス低下を改善したいのですが、何か改善できる製品、手法等ご存じの方いらしたら教えていただけないでしょうか？
以下、EXPLAIN文を付与して流したSQLの結果です。
テーブル名| type | rows | key_len | Extra
テーブルA | index | 370 | 92 | Using index
テーブルB | index | 2642603 | 1405 | Using where; Using join buffer (Block Nested Loop)
▼環境
MySQL5.6.19 Community Srv(Win64)
Win srv 2012R2 std
mem:8GB
CPU:Intel Xeon E3-1220
disk：15Krpm SAS HDD
似たような製品を探す労力を省くために、LIKE JOINを使って、テーブルAの製品リスト内の似たような製品をマスタから一気に取得したいと考えております。
宜しくお願い致します。
No.2
170
63
＞似たような製品を探す労力を省く
イメージとしては，Amazonの「この商品を買った人はこんな商品も買っています」の欄でしょうか。
これは，リアルタイムで０から算出するような処理ではないですね・・・。
バッチ処理で，前もって付加しておくべき情報です。
マスタデータ内のそれぞれの商品情報に対して，
自分と似ているような「類似商品情報」を
マスタデータ内の他のデータから探し，
これらの商品同士が類似している，という関係を別テーブル等にまえもって記録しておきます。
類似の関係の導出方法は，商品の名前の前方一致であったり，
ユーザの購買履歴から，同じような年齢層のユーザがさいきん買っているものを
トレンド分析します。
レコメンドできる類似商品は定期的に更新されてゆきます。
これらは，マスタデータ（や購買ログ）など，
主に過去に確定済みの情報だけにもとづいて算出する性質の情報なので，
リアルタイムでは処理しません。
しかもマスタデータの分量が膨大ですから，なおさらリアルタイム向きではありません。
１日おき，数時間おき，１時間おきなどの周期で
裏でバッチ処置で集計し，類似商品の紐付けや
レコメンドの指標を更新しておきます。
そうすれば，ユーザがある商品の情報を調べたときには，
あらかじめバッチ処理によって算出済みの類似商品情報を
一瞬で提供できます。
SQLのパフォーマンスを向上させる，という方針ではなく
前処理としてインデクシングをしておく（検索しやすいようにデータ同士の紐付けを事前に済ませておく），また
時間がかかる処理であることを前提に定期的なバッチ処理に回す
という方針でご検討ください。
No.2
17063
ここでベストアンサー
＞似たような製品を探す労力を省く
イメージとしては，Amazonの「この商品を買った人はこんな商品も買っています」の欄でしょうか。
これは，リアルタイムで０から算出するような処理ではないですね・・・。
バッチ処理で，前もって付加しておくべき情報です。
マスタデータ内のそれぞれの商品情報に対して，
自分と似ているような「類似商品情報」を
マスタデータ内の他のデータから探し，
これらの商品同士が類似している，という関係を別テーブル等にまえもって記録しておきます。
類似の関係の導出方法は，商品の名前の前方一致であったり，
ユーザの購買履歴から，同じような年齢層のユーザがさいきん買っているものを
トレンド分析します。
レコメンドできる類似商品は定期的に更新されてゆきます。
これらは，マスタデータ（や購買ログ）など，
主に過去に確定済みの情報だけにもとづいて算出する性質の情報なので，
リアルタイムでは処理しません。
しかもマスタデータの分量が膨大ですから，なおさらリアルタイム向きではありません。
１日おき，数時間おき，１時間おきなどの周期で
裏でバッチ処置で集計し，類似商品の紐付けや
レコメンドの指標を更新しておきます。
そうすれば，ユーザがある商品の情報を調べたときには，
あらかじめバッチ処理によって算出済みの類似商品情報を
一瞬で提供できます。
SQLのパフォーマンスを向上させる，という方針ではなく
前処理としてインデクシングをしておく（検索しやすいようにデータ同士の紐付けを事前に済ませておく），また
時間がかかる処理であることを前提に定期的なバッチ処理に回す
という方針でご検討ください。
そうでしたか。事前に類似関係をはじきだしておく方法は使えないということですね。どうしてもリアルタイム性が求められると。
Amazonの類似商品よりも，Google等の「サジェスト機能」のほうが近いでしょうかね。
（途中まで入力すると，前方一致でマッチするような候補がリストアップされる）
リアルタイムの方針を貫く場合，まっさきにチューニングするのがインデックスです。
explainの結果を見ると，レコードアクセスタイプ（type）が，フルインデックス，つまり１件ずつアクセスが走るという状況のようです。
該当カラム（製品の型番）に，CREATE INDEX文でインデックスは作成されていますか？
前方一致に限ればインデックスがかなり有効ですが・・・
インデックスの次に打つことのできる手は，検索範囲を限定させることです。
製品のカテゴリを前もってグループ分けできるといいんですが，それは無理そうでしょうか。
再度Amazonの例を出して済みませんが，例えば商品名を調べる場合，本なのか，CDなのか，プルダウンから選択できるようになってますよね。
あれのおかげで，商品マスタ内の全件から調べる必要が無くなり，数分の１～１０分の１に検索コストが抑えられます。
今回の場合だと，４００万件の商品をぜんぶ一緒くたにして，カテゴリに関係なく全ジャンル横断で，名前だけを見る必要があるのでしょうか。
システムのパフォーマンス要件に問題が生じた場合，全ジャンルからはわざと検索できないようにして，一つのカテゴリを選ばせてからそのカテゴリ内だけで検索できるようなUIにしておき，そのおかげでユーザの体感速度は上がりストレスを感じさせないで済む。という方法もあります。
仰る通り、Googleのサジェスト機能を異なったワードで一気に300件分同時に走らせる処理というイメージです。
結合条件に使用している双方のテーブルのキーには、BTREEでインデックスを作成済みですが、Explainの結果はフルインデックスになってしまいます。
※インデックスがない場合はAllとなります。
マスタ側のインデックスが複合インデックスでしたので、結合に使用するキーのみのインデックスに作成し直してトライしてみましたが、複合インデックスの時と同様にフルインデックススキャンで処理が走りました。
検索範囲の限定は可能であれば実施したかったのですが、残念ながら現状の要件では製品名のみで検索となっており(カテゴライズされていない状態)、実現ができない状態です。
ですので、初回は完全一致の結合で検索をしてもらい、それ以降の検索で完全一致で見つからなかったもののみを対象にして、前方一致検索で探してもらうなど、アプリ側の操作方法を工夫して制限していく手法しか今のところ解決策がみつからない状態です。
1
0
そうでしたか。事前に類似関係をはじきだしておく方法は使えないということですね。どうしてもリアルタイム性が求められると。
2015/01/20 20:01:09Amazonの類似商品よりも，Google等の「サジェスト機能」のほうが近いでしょうかね。
（途中まで入力すると，前方一致でマッチするような候補がリストアップされる）
リアルタイムの方針を貫く場合，まっさきにチューニングするのがインデックスです。
explainの結果を見ると，レコードアクセスタイプ（type）が，フルインデックス，つまり１件ずつアクセスが走るという状況のようです。
該当カラム（製品の型番）に，CREATE INDEX文でインデックスは作成されていますか？
前方一致に限ればインデックスがかなり有効ですが・・・
インデックスの次に打つことのできる手は，検索範囲を限定させることです。
製品のカテゴリを前もってグループ分けできるといいんですが，それは無理そうでしょうか。
再度Amazonの例を出して済みませんが，例えば商品名を調べる場合，本なのか，CDなのか，プルダウンから選択できるようになってますよね。
あれのおかげで，商品マスタ内の全件から調べる必要が無くなり，数分の１～１０分の１に検索コストが抑えられます。
今回の場合だと，４００万件の商品をぜんぶ一緒くたにして，カテゴリに関係なく全ジャンル横断で，名前だけを見る必要があるのでしょうか。
システムのパフォーマンス要件に問題が生じた場合，全ジャンルからはわざと検索できないようにして，一つのカテゴリを選ばせてからそのカテゴリ内だけで検索できるようなUIにしておき，そのおかげでユーザの体感速度は上がりストレスを感じさせないで済む。という方法もあります。
仰る通り、Googleのサジェスト機能を異なったワードで一気に300件分同時に走らせる処理というイメージです。
2015/01/21 15:22:20結合条件に使用している双方のテーブルのキーには、BTREEでインデックスを作成済みですが、Explainの結果はフルインデックスになってしまいます。
※インデックスがない場合はAllとなります。
マスタ側のインデックスが複合インデックスでしたので、結合に使用するキーのみのインデックスに作成し直してトライしてみましたが、複合インデックスの時と同様にフルインデックススキャンで処理が走りました。
検索範囲の限定は可能であれば実施したかったのですが、残念ながら現状の要件では製品名のみで検索となっており(カテゴライズされていない状態)、実現ができない状態です。
ですので、初回は完全一致の結合で検索をしてもらい、それ以降の検索で完全一致で見つからなかったもののみを対象にして、前方一致検索で探してもらうなど、アプリ側の操作方法を工夫して制限していく手法しか今のところ解決策がみつからない状態です。