ふりだしに戻る。

2007/11/05

会社のプロジェクトで、実践投入してるStarbug1ですが、早くも致命的な欠陥が露呈しました。最近、ソート機能やキーワード検索(全ての項目から検索)を実装したのですが、その時にjoinやら副問合せやらが盛り沢山になってしまい、検索の性能が実用に耐えないということがわかりました。複数の検索条件を指定して検索すると、10秒近くかかってしまっていました。予想よりかなり早い段階で性能問題が出てしまい、チョッピリ落ち込んでますorz.

実は、恥かしながら今までindexを定義していませんでした。SQLは得意でないんですが、なんとなくindexを定義してみたところ、少ない検索条件であれば、我慢できるとは言えませんが、最低限使える状態(1個条件を指定して2秒弱、結果81件)にはなりました。

データの持ち方は、影舞から継承しているんですが(影舞のDBスキーマまでは調査してません)、各項目をメタ情報としてDBに保存することで、運用中でも柔軟なカスタマイズができるようにしています。これはそのまま、検索時の負荷が大きいということと直結してしまいます。

どうゆうテーブルでチケットを格納しているかを、イメージで言うと

ID	件名	投稿者	状態	優先度	詳細
1	検索が遅い	smeghead	新規	高	検索条件を指定して検索すると遅い。。。

ではなく

ID	項目名	値
1	件名	検索が遅い
1	投稿者	smeghead
1	状態	新規
1	優先度	高
1	詳細	検索条件を指定して検索すると遅い。。。

という形でチケットを保存しています。

具体的には、検索条件が増えれば増える程その分値を持っているテーブル(element)を、inner join していくことになります。

検索条件が一つ(例えば状態)の時のjoinだけしたときの総件数を調べるため実際に発行しているSQLを元に、調査用SQLを書きました。↓のようになります。実際一覧画面に表示される検索結果は、たかが81件です。

sqlite> select
...>  count(*)
...> from ticket as t
...> left join reply as r
...> on t. id = r.ticket_id
...> inner join element as e1
...> on t.id = e1.ticket_id
...>    and (e1.reply_id = (select max(id) from reply where ticket_id = t.id)
...>         or ((select count(id) from reply where ticket_id = t.id) = 0
...>            and e1.reply_id is null))
...> ;
2688

2688件でした。現在の状態では、81件を取得するため2688個の母体から検索しているということになります。

しかし、検索条件1個とキーワード検索が絡んだりしたら、酷いです。

sqlite> select
...>  count(*)
...> from ticket as t
...> inner join element as e
...> on t.id = e.ticket_id
...> left join reply as r
...> on t.id = r.ticket_id
...> inner join element as e1
...>  on t.id = e1.ticket_id
...>     and (e1 .reply_id = (select max(id) from reply where ticket_id = t.id)
...>         or ((select count(id) from reply where ticket_id = t.id) = 0
...>            and e1.reply_id is null))
...> ;
134352

このSQL自体が、全然帰ってこない。

。。。13万件を越えています。81件を取得するため13万個の母体から検索しているということになります。

カスタマイズ性を捨てることは考えてないですが、根本的に対策しないと、Cで書いてる意味がないというか、一番大切な目的である快適な動作が、既に欠如してます。

考えられる対策

テーブル定義から作りなおす。
indexを適切に定義する。
部分的には、検索UIも変えないといけない。
DBをsqlite3から、常駐型に変える。

まずは、内部的なテーブル定義からやりなおす必要がありそうです。versionが上がってきてからでは大変なので、α版のうちに最適化しときたいです。

あと、影舞が速くはないけど、あの速度で動くのは、苦労とか工夫とかしてるのではないかと思うので影舞のソースも研究しないと(rubyの勉強も兼ねて読もうか)。先人のノウハウを盗みたい。

この記事の投稿者最新記事

しめじ(smeghead)

sql starbug1

7件のコメント

rayfill 2007/11/06

抽出される総行数より途中でサブクエリの条件が変わるものが入ってることが遅い原因だと思われます(ticket.idの数だけreplyテーブルがフルスキャンされてると思われます)。
サブクエリをすべてのticket.idに対してマッチするようにしてin演算子使ってマッチを取るようにすれば多少はマシになるかもしれません。

elementテーブルのフレキシブルすぎる構造が足を引っ張る原因になりかねないです。
カラム構造をあとづけで増減するだけならデータベース側がサポートしていればalter table table_name add column_name column_type; などで増減できる場合がある(ためしたらsqlite3でも使えるようです)のでそっちを検討するのは駄目なんでしょうか？
smeghead 2007/11/06

rayfillさん、コメントありがとうございます。

> 抽出される総行数より途中でサブクエリの条件が変わるものが入ってることが遅い原因だと思われます(ticket.idの数だけreplyテーブルがフルスキャンされてると思われます)。
> サブクエリをすべてのticket.idに対してマッチするようにしてin演算子使ってマッチを取るようにすれば多少はマシになるかもしれません。

結合条件でサブクエリを使うのではなく、where句でサブクエリで絞りこむということでしょうか。rayfillさんのアドバイスを理解できている自信がないですm(_ _)m
でも、where句で絞るようにしたら、簡易的な確認で約2割速くなりました!
開発初期の怠慢で、ticketテーブルとreplyテーブルを別で定義してしまったことが、妙なjoinを行なう原因になってしまってます。この構造の問題は、今回の再構築で対処できればと思います。

> カラム構造をあとづけで増減するだけならデータベース側がサポートしていればalter table table_name add column_name column_type; などで増減できる場合がある(ためしたらsqlite3でも使えるようです)のでそっちを検討するのは駄目なんでしょうか？

わざわざ試していただいたのですね。恐縮です。普段の業務(java)で、動的にカラムを増やすといことが無いに等しいので、動的にカラムを増やすというのは、全く気がつかなかったアプローチでした。(正規化し過ぎ症候群でもあるので^^;)
私もちょっと試してみたのですが、カラム名の追加と名前の変更はできるようですが、削除できないようでした。でも、この方法がパフォーマンスは一番良さそうですね。SQLの組み立ての処理を書く負担はそれなりにありそうです。もうちょっと考えてみます。貴重なアドバイスありがとうございます。
discypus 2007/11/06

SQL文の吟味には http://www.sqlite.org/optoverview.html が参考になりそうです。
あと、EXPLAIN http://www.sqlite.org/lang_explain.html と http://www.sqlite.org/opcode.html で、
SQL文がどのように解釈されているのか調べることが出来ます。
また、ある程度レコードが増えたら統計データをANALYZEで更新 http://www.sqlite.org/lang_analyze.html
すればEXPLAINの結果も変わると思います。
あと、他のDB(RDMBS)とのベンチマークがあります。ちょっと古いデータですが。
http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison
smeghead 2007/11/06

discypusさん、ありがとうございます。
EXPLAINがあれば、ちゃんとチューニングできるのですね。ベンチマークは他のDBとも遜色ないのがわかりました。DBのせいにしては、いけないですね。反省。
sqliteの特徴を、うまく利用することも鍵になりそうです。教えていただいたURL読んでみます。
rayfill 2007/11/07

前のコメントで言ってたことはこういうことです。
　inner joinされているelementのon句での条件式のなかにサブクエリでt.idを参照されてますが、こういうサブクエリの外部のパラメータを使うサブクエリを相関サブクエリと呼びます。で、最適化結果がよく行われていたとしてもt.idの種類は81種類あるみたいなのでselect max(id) from reply where ticket_id = idのクエリが最低でも81回分は実行されていることになります。max関数の場合、条件に適合する行をすべて抽出して最大値を求めるわけですから結構コストは大きめです。最適化が悪い場合、もっと多い回数実行されることもありますし、結合条件が適用される順序も悪いとサブクエリの実行回数は桁が簡単に変わるくらい増えていくでしょう。
なので相関サブクエリを使う場合、抽出されるパターンを一括で抽出できるようにSQLを書き換え(select max(id)…の場合replyテーブルからチケットごとの最新リプライを抽出しているようなので
e1 .reply_id = (select max(id) from reply where ticket_id = t.id)
を
e1.reply_id in (select max(id) from reply group by ticket_id)
(各ticket_idごとにもっとも大きいidを表として取り出してます)。
に変更すると高速化される可能性があります。
あー、例として書いた条件式は適当ですので(そもそもカラムの用途があってるかの確証もないです)間違ってたらごめんなさい。
rayfill 2007/11/07

「相関サブクエリを使う場合～」じゃなくて「相関サブクエリを使わずに、～」になってました・・・。
smeghead 2007/11/07

rayfillさん、おはようございます。
やっぱり昨日の時点では理解できてなかったです。解説していただいて、in演算子を使うという部分も含めて理解できました。確かに、group byの結果をinで比較する方が効率的ですね。後で、in+group byで絞る方法で、どのくらい効率化されるか測ってみたいです。かなり早くなりそう。
#SQLを手続き脳で書いてしまうので、集合で考えるようにSQLドリル書き取りが必要のようです。
ありがとうございます。

コメントする

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。