質問: SQLのJOINのパフォーマンス向上で質問です。 ※手法として有償の製品でご存じのものがあれば教えていただきたいです。 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の製品リスト内の似たような製品をマスタから一気に取得したいと考えております。 宜しくお願い致します。
回答: >似たような製品を探す労力を省く イメージとしては,Amazonの「この商品を買った人はこんな商品も買っています」の欄でしょうか。 これは,リアルタイムで0から算出するような処理ではないですね・・・。 バッチ処理で,前もって付加しておくべき情報です。 マスタデータ内のそれぞれの商品情報に対して, 自分と似ているような「類似商品情報」を マスタデータ内の他のデータから探し, これらの商品同士が類似している,という関係を別テーブル等にまえもって記録しておきます。 類似の関係の導出方法は,商品の名前の前方一致であったり, ユーザの購買履歴から,同じような年齢層のユーザがさいきん買っているものを トレンド分析します。 レコメンドできる類似商品は定期的に更新されてゆきます。 これらは,マスタデータ(や購買ログ)など, 主に過去に確定済みの情報だけにもとづいて算出する性質の情報なので, リアルタイムでは処理しません。 しかもマスタデータの分量が膨大ですから,なおさらリアルタイム向きではありません。 1日おき,数時間おき,1時間おきなどの周期で 裏でバッチ処置で集計し,類似商品の紐付けや レコメンドの指標を更新しておきます。 そうすれば,ユーザがある商品の情報を調べたときには, あらかじめバッチ処理によって算出済みの類似商品情報を 一瞬で提供できます。 SQLのパフォーマンスを向上させる,という方針ではなく 前処理としてインデクシングをしておく(検索しやすいようにデータ同士の紐付けを事前に済ませておく),また 時間がかかる処理であることを前提に定期的なバッチ処理に回す という方針でご検討ください。