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

こんにちわ。もとやんです。
さて、今日は前回の続きを解説します。

前回のおさらい

まずは前回でやったことをザッとまとめておきます。

  • 転置インデックス用のテーブルを作る
  • キーワード検索用にngram(bigram)で分かち書きしたデータを作る
  • それ以外のフラグによる検索のために、n(今回は2)文字を超える特殊文字列で他の属性を突っ込む
  • 全文検索インデックスを張る
  • 全文検索クエリを調整して条件に合致するIDだけを抽出する

残る問題

  • ソート(ページネーション含む)
  • 更に複雑なクエリ(柔軟な価格レンジ、地理空間)に対応するための手法
  • どれだけ早くなったのか

抽出したID群をソートする

さて、では前回の続きの内容に入ります。itemsテーブルにおける条件合致商品のIDのリストが現在得られています。
ここでは仮に得られたIDを「1,5,16,45,78,120,201」としましょう。此処では価格(items.price)が安い順(asc)に並ぶようにクエリを書きます。

サイトに表示するためには、これを指定された条件で並べ替えなければいけません。また、ついでにページネーションも実施しておきたいところです。
そこで前回の時に、itemsテーブルに複合インデックスを設定してもらったわけですがソート用のクエリでバッチリ役に立ってくれます。
なお、前回の記事公開時点で、複合インデックスの順序に誤りがありました。2013.8.26付で修正していますので、本記事公開前に前回をお読みいただいた方はご参照ください。

MySQLでのソートに役立つ:オトコのソートテクニック2008

上述のサイトを参照していただければ、お分かりいただけると思いますがMySQLのOrder Byに関するクエリオプティマイザはかなり気が利かない。
そこでかなり単純なクエリでソートとページネーションだけを行い、確実にインデックスを効かせる小技が必要になってきます。


SELECT id FROM items WHERE id IN (1,5,16,45,78,120,201) ORDER BY price;

これでソートはOK。ページネーションを行う場合のクエリも一応書いておきます。1ページに5件ずつ表示するとして、これが1ページ目(ソート結果の1番目から使う)だったなら。

SELECT id FROM items WHERE id IN (1,5,16,45,78,120,201) ORDER BY price LIMIT 0, 5;

こう。

で、ソートしてページネーション用に切りだされたIDの列が「1,120,45,78,5」だったとします。もちろんID=1の商品が安い(items.priceが小さい)。
この順序を保持したまま、itemsテーブルから全フィールドの情報(ページに必要なフィールドは各自のサイト毎で洗ってください)を抜き出します。そのクエリはこう。


SELECT * FROM items WHERE id IN (1,120,45,78,5) ORDER BY FIELD(id,1,120,45,78,5);

はい。これで完了です。ちなみにページネーションの時に○件中という値が必要なら、最初にinverted_indexテーブルからIDを抽出した時のIDの個数を見ておけば大丈夫。
ちなみに最後のクエリは抽出にはインデックスがガッツリ効きますが(プライマリキーなので)、ORDER BYについてはid順ではないので効きません。
もっとも1ページ分に表示するだけの要素でしか無いので、ここにusing filesortがついたところで、ソート対象はせいぜい100とかそこらでしょう。余裕です。

ところで

SELECT * FROM items WHERE id IN (1,5,16,45,78,120,201) ORDER BY price LIMIT 0, 5;

みたいに、クエリを統合すれば、いい感じになるんじゃね? と思った方もいるかもしれませんが、クエリオプティマイザの関係でインデックスが使われなくなったりします。
今回は3つのクエリに分割して結果を得ていますが、これはアプリケーション側がかなりの割合でインテリジェンスにクエリを構築することで、MySQLを手なづけていると言えます。

実際、適切なインデックス計画をMySQLのクエリオプティマイザに完全に任せるにはまだまだ難しい点が有りこういった小技で対処しなくてはいけないことも有ります。
今回のテーブル構成とクエリを一例として、今後のデータモデリングの参考にしてください。

更に複雑なクエリ(柔軟な価格レンジ、地理空間)に対応するための手法

ここから先はおまけ編です。完全な解答を出さない所もあるのであしからず。
柔軟な価格レンジ(価格自由入力式)ならこうだ。例では0~1000円。

EXPLAIN SELECT id FROM item WHERE id IN (1,5,16,45,78,120,201) AND price BETWEEN 0 and 1000 ORDER BY price LIMIT 0, 5;

地理空間
地理空間インデックスについてですが、「近くのお店検索」などに使います。
要は指定領域内にあるデータの抽出によって実現されます。実はこの検索、実現がかなり難しいです。ここではいくつかの方法をお話します。

1つ目。空間インデックスを使う。

まず、空間インデックスは記事公開時点の最新正式版であるMySQL5.6で、MyISAMでしか実装されていません。
MySQL5.7で実装予定ですが、現状でやるなら、MyISAMに空間インデックスを逃して、そこの抽出結果(IDリスト)との積集合(INTERSECT)をとる必要があるでしょう。
ちなみにINTERSECTもMySQLは未実装なので、Inner Joinで代替する必要があります。

2つ目。パトリシアトライを自前実装

要は空間インデックスを自分で作る方法です。今、どうしてもInnoDBにインデックスを持たせたいなら、自分でインデックスを作るしかありません。
groongaで高速な位置情報検索
この辺を見れば実装自体は難しくありませんが、正直MySQLの正当な使い方ではなくなってくること必死なので、後々泣くことになるでしょう。後任者が。

緯度と経度を普通に検索

緯度と経度をそれぞれbetween演算子などを使って、絞り込む方法です。
だれでも思いつきます。ただしインデックスは使われません。正確に言うと片方しか使われません。
前回も書きましたが、複合インデックスを使ったMySQLのレンジソート(範囲検索)は、それ以上のインデックスの仕様ができなくなります。
よって、緯度か経度かどちらかだけがインデックス検索の対象になるので、選択性の高い方(より絞り込める方=分散が大きい方)をクエリの前の方に書いてください。

どれだけ早くなったのか

最適化の基本は「推測するな、測定せよ」です。
もちろん私はこのクエリを作るときに、EXPLAINを走らせまくって実験をし、高速化を行いました(インデックスが可能な限り使われるようにクエリをチューニングしています)。
ただ、ここではあえてその結果を出しません。
もしこの構造を実コードに採用されるなら、実際に手元でEXPLAINを実行し、インデックス計画を確認して下さい。これは必須です。
また、その際にはなるべくというか、絶対に本番データに近いデータで実行してください。

MySQLのクエリオプティマイザは、条件に引っかかる行数などを勘案し、実行計画を構築します。なので、本番環境とテスト環境でデータがあまりにも違う場合、クエリ最適化のテスト環境としては役に立ちません。
つまり、どのようなハックを使うにせよ、それが有効かどうかは貴方自身で確かめる必用があるのです。

EXPLAINを見るときのお供に:MySQLのEXPLAINを徹底解説!!

最後に

さて、2回にわたってお話した、高速な詳細検索を実現するデータベース構造と、クエリチューニングはいかがでしたでしょうか。
低速なサイトのチューニングをしたことがある方であればおわかりいただけるかと思いますが、クエリの実行速度はそのテーブル構造で決まります。
それぞれのアプリケーションの要求するクエリパターンを予想し、テーブル構造をつくり上げるわけですが、実際の設計はかなりパターン化されています。
今後もあまり世に大きく出ていないのでは? というパターンに気がつけばこのブログで書いていこうと思います。

コメント

タイトルとURLをコピーしました