MySQLでネットショップの詳細検索にインデックスを活用しまくる方法(前編)

こんにちは。もとやんです。
DBAとしてのネタもたまには書きたい！　ということで、MySQLのインデックスをフルに使って、ネットショップや、食べログやぐるなびなどの検索サイトによくある詳細検索を高速に行うための、SQLのクエリの高速化手法と言うか、テーブル設計を今回は紹介します。

あまりネットのブログとかでは見かけないノウハウだと思うので、ショッピングカートやグルメサイトのシステムを書こうとする人にとっては参考になるんじゃないでしょうか。
とはいえ、これは私が必要に応じて数年前に頭を捻って考えたものなので、もっと高効率に書けるよ！という方がおられれば、ぜひ教えて下さい。

今回扱うような検索にはSolr(Lucene)などが実は向いていたりするんですが、新しいストレージを作ると監視対象は増えるわ、そもそも普段の運用ノウハウ（チューニングや、クラッシュ時のリカバースキル、バックアップ方法など）にしたって蓄積が必要なので、すでにあるMySQLでまかなえるならまかないたいというニーズは大きいです。

対象とする検索の機能

まず、前提として以下の様な条件の検索を行いたいとします。

• キーワード検索（全文検索）
• 価格帯による検索（～1000円、1001～2000円、2001円～など）
• 特殊条件フラグによる検索（赤色の商品、やクレジットカード支払いOKの店など）で、当然複数指定可能
• 好きなフィールドで昇順、降順にソートできる
• ページネーションできる（何件中、何件～何件を表示）

で、最低1万件程度は登録されているものとします。
特に工夫しなくても数百件～一千件ぐらいであれば、よっぽど非効率なクエリをかかなり限り、まともなサーバなら1秒以内に結果が返ってくるでしょうが（ヘタしたらインデックス張るより早い）、検索対象が多くなってくると、ガンガン遅くなっていきます(Using filesort, Using temporary頻発とか)。
というのも、上記のような条件による検索は実はMySQLのインデックスの制約上、かなり速度を維持させるのに辛いものがあるからです。この辺りはクエリのパフォーマンスチューニングをされたことがある方なら容易に想像がつくと思います。

MySQL(B+ tree Index)で辛い所

• インデックスが1本のクエリにおいて同時に1つしか使えない
• インデックス結合(Index Merge)がバージョン5.0から使えるようになったが、複合インデックスよりは遅い
• レンジサーチ(10 < `field` AND `field < 10 AND )をするとそれ以上インデックスを使えなくなる
• キーワード検索にLIKE検索(‘%keyword%’)を使うと、インデックスを使えない(強制Full search)

などなど、色々とシビアな条件があります。
この条件をすべてクリアしつつ高速な検索を実現するには、3本のクエリでデータを取ります。

高速検索のためのテーブル構造

• 転置インデックスを採用(検索用のテーブルを作る)
• 検索テーブルは、検索対象テーブル(以下、マスタテーブル)の外部キーとなるidと、テキストフィールド(此処ではft_indexとします)のテーブル
• マスタテーブルに、主キーと、ソート対象となる各フィールドのインデックスの複合インデックスを作っておく(順序はソート対象、主キー)

2013.8.26 訂正:マスタテーブルに作っておく複合インデックスの順序ですが、(順序は主キー、ソート対象)と誤って表記していましたが、正しくは(順序はソート対象、主キー)です。

ついでにマスタテーブルを此処では商品テーブルとして、テーブル名をitems、そしてフィールドを以下の感じにします。
itemsテーブル

inverted_indexテーブル

そしてinverted_indexテーブルのft_indexに、全文インデックス(Full Text Index, FT index)を設定します。そしてこのインデックスを作る前に、my.iniにて「ft_min_word_len=1」と、設定し、mysqldを再起動してください。
レンタルサーバなどでMySQLの設定ファイルを編集する権限をお持ちでない場合の逃げ方は、後で参考サイトを出します。

実際のクエリの流れ

これで前提については説明し終えたので、早速検索用SQLの書き方に入ります。大きく以下の流れになります。

1. inverted_indexテーブルに対する全文検索を用いて、条件に合致する全商品のIDを取得
2. 1.で得たIDを、今度はitemsテーブルに対して使用して、並び替えフィールドで並べ替えた結果のIDリストを得る(ページネーションの処理も此処でする)
3. 表示するページの分だけの商品情報をitemsテーブルから取得する

それぞれのステップに1本のSQLクエリが走るので、計3本のクエリで結果を得る。サブクエリを使えばまとめることもできますが、MySQLのバージョンによってはクエリオプティマイザの関係で残念なコトになるので、クエリ分割をおすすめします。

全文検索クエリの書き方と、全文検索用のインデックスデータの作り方

MySQLの全文検索は日本語に対応していません(v5.6時点)。正しく言うと、ngramを含めた分かち書をMySQL側で行うことができません。データを作る人間が自前で分かち書きして上げる必要があります。

MySQLで全文検索 – FULLTEXTインデックスの基礎知識

分かち書きについては上記サイトが参考になります。この参考サイトの内容は基本的にキーワード検索を行うためのものであるのに対し、今回はそこに並び替えなどの諸条件を同時にうまいことやるための方法を上乗せするのが、今回のブログポストの目的となります。
また、ここではitemsテーブルのname,name_ruby,descフィールドについてはutf8で、bi-gram(2文字区切り)で分かち書きします。そうすると、当然各トークン(単語)の文字列長は最大2文字になるので、3文字以上の単語を各種フラグとして、inverted_indexテーブルのft_indexフィールドについでに放り込んでおけば使えます。今回は例として、価格帯と、店長のおすすめ、という2つの要素を放り込みます。

つまりft_indexフィールドに挿入されるデータは例えば次のような形になります。


INSERT INTO inverted_index (item_id, ft_index) VALUES ('PRICE_RANGE_0_1000 SHOP_OWNER_RECOMMEND まな な板 マナ ナイ イタ 刃持 持ち ちが が良 良い い桜 桜製 製ま まな な板 板で です す。');


ピンときた方も居るでしょう。要は価格帯のレンジや、フラグの有無によって、3文字以上の特殊文字列を定義して全文検索インデックスに放り込んでおけば、あとは全文検索のクエリを工夫するだけで自由度の高い絞り込みが可能になります。
たとえば上記にヒットする例として

• 商品名か、説明文に「まな板」を含む
• 0~1000円の価格帯の商品
• 店長のおすすめ商品

ということであれば、

SELECT item_id FROM inverted_index WHERE MATCH ( ft_index ) AGAINST ('+PRICE_RANGE_0_1000 +SHOP_OWNRE_RECOMMENDED +"まな な板"' IN BOOLEAN MODE);

これでIDのリストが出ます。検索対象に含めたい単語やフレーズの前に「+」を付けるのが一つポイントなんですが、此処については7.8.2. ブール全文検索に詳しく書いてあります。
ちなみにキーワード検索で検索する部分のクエリは””(二重引用符)で囲んでください。これをしておかないと、検索結果にノイズがまじります(具体的に言うと、「まな」と「な板」という部分文字列を含んだものが全て入る)。

ここまででテーブル構造と、転置インデックス用のデータの作り方、検索結果のIDリスト(未整列)の取得までを扱いました。

ここまででかなり長くなってきてしまったので、記事を分割します。
次回の後編で、MySQLで頭を使うことになる、検索結果のソート(ORDER BY)を高速に行う部分、ページネーション用に部分的なデータを切り出す部分、InnoDBやMyISAMの差を含めた細かなチューニングを扱いたいと思います。

SQLの高速化、クエリの最適化はDBAやプログラマにとって、永遠の課題の一つです。今後も何か一般的に使えるんじゃね？　と思ったものについては色々書いていきます。