SQLの高速化の方法について

質問

自分ではすぐに浮かばないのですが、結構急ぎのことなのでお時間がある方でご存知の方がいらしたら教えて下さると嬉しいです。なので、途中で解決していたり質問を途中で消しているかもしれませんがよろしくお願いします。

SQLで自分が書いた文を高速化したいのですが、一般的にこうすると遅いからこうする方が良いというのがあれば教えてください。

一応自分でも調べててWHERE句では結合よりも先に条件を記載する方が良いとか書いてました。
あと、IN演算子がちょっと問題な演算子ということが。

確かにSQL文を書いているソースではいくつかSELECT文があって、それぞれでIN演算子を使っています。

調べてるとIN演算子をEXISTS演算子に書き換える方が良くなるとあったのですがEXISTS演算子はカッコ内で再度SELECT文を作るんですよね？

今使っているIN演算子では、ほぼカッコ内に数字のいくつかの候補があってそこから該当する数字を選ばれるという形の方。

WHERE　ＡＡＡ　ＩＮ　（'122','244','366'）

といった形です。

あるＳＥＬＥＣＴ文でＩＮ演算子を使って絞った値データを次のＳＥＬＥＣＴ分のＩＮ演算子の値候補として使用しています。

つまり、上記のような文１２２と２４４のデータが見つかってＳＥＬＥＣＴされると次のＳＥＬＥＣＴ文のＩＮ演算子の候補として１２２と２４４が入るという形です。

ちなみにこの処理のメインとなるテーブルでは一つの項目の値を木構造で分けているのでその辺りも考えないといけません。

以前、こちらのサイトで教えて貰った方法で動いている

GROUP BY ＡＡ．Ａ１ HAVING COUNT(*) >= 0

といった処理も重くしてしまうのでしょうか？

回答No.1

yambejp · ベストアンサー

よくある話ですがまずはexplainでインデックスがうまく利いているか
みながら調整していくことです。
この辺の情報を一つ一つ検証してみてください

参考URL：

http://dev.mysql.com/doc/refman/5.1/ja/tips.html

お礼

回答ありがとうございます。ちょっと難しそうな方法ですが、explainについてよくわかっていないので調べないといけないですね。

補足

phpMyAdminでいくつかあるSQLの内、あるSQLを実行してみると実は６秒近くかかってしまっていました。

(全く同じSQLを再度やるとブラウザのキャッシュで短時間でできてしまい、値を変えても形は同じなので短時間でできてしまうのでたまにしか検証できませんけども。)

ちなみに以下のSQL文です。

SELECT DISTINCT a_mst.a_code
FROM a_mst, b_mst BM1, b_mst BM2
WHERE BM1.b_code = 123
AND a_mst.a_flg =0
AND BM2.b_code IN ("200")
AND a_mst.a_code = BM1.b_code
AND a_mst.a_code = BM2.b_code
GROUP BY BM1.b_code
HAVING COUNT( * ) >=1
LIMIT 0 , 30

このSQL文は、後に実行される同様な書き方でより詳細なデータを出力させるSQL文に対するこの条件の場合の出力件数を調べるためのSQL文です。

通常であればそのあとに実行されるSQLに対する出力件数を簡単に調べられますが、一ページに表示させる最大表示件数で制限をかけているため、たとえ調べても最大表示件数以下になるからです。

補足として上記SQLの最後にあるLIMIT句はphpMyAdminが出力結果を出すために勝手に追加している句なので本来のSQLには含まれていません。

データベースは商品に関する情報を保持しており、a_mstは商品番号とその商品に関する情報が保持しています。b_mstはその商品に関するステータスを保持しています。

a_mstの場合、商品名やJANコード、原材料等(かぶる場合もあるかもしれませんが)通常一つの商品番号に対してそれぞれ異なる情報を持っているのでこのテーブルに保持しています。

b_mstの場合、仮設定ですがこれがamazonとかだと色んな種類の商品を取り扱っているのでこの商品は「衣類」、この商品は「飲み物」、この商品は「家電」といった分類データを保持しています。

b_mstを２つ使用しているのは、片方は「衣類」だという情報だけで、もう一方は「Tシャツ」だとか「日本(製)」だとか保持しています。

単なる衣類商品をだけを出力する場合は前者のみ使えばいいし、日本製のTシャツのMサイズとかを出力したい場合は前者「衣類」、後者「Tシャツ・日本・M」になります。

前者は一つ固定、後者は選択された項目によって可変するデータを一つのテーブルで保持しています。

本来であればその辺のデータを分けて管理するのがいいのでしょうが現状はこのテーブルをそのまま使用して使わないといけない状態なのでこの条件で改善したいです。

回答No.2

nora1962

.>　一応自分でも調べててWHERE句では結合よりも先に条件を記載する方が良いとか書いてました。
>　あと、IN演算子がちょっと問題な演算子ということが。

結合処理については、駆動表の絞り込みが出来るのならそこで件数を絞り込みを行うのは有効です。
しかし、絞り込みでそんなに件数が減らない場合、駆動表・内部表ともにWHERE条件を先に適用させるSQLは結合にINDEXが使えないので逆に遅くなる可能性があります。
ここらあたりはテーブル構造やデータの状況にもよるので一般論はありません。
IN句は意味的にOR条件と等値です。後はRDBMSの実装しだいですが、場合によってせっかくINDEXが使えるような場面でも、IN句の場合全表検索を選択する傾向があるのも事実です。

> WHERE　ＡＡＡ　ＩＮ　（'122','244','366'）
のような場合、オプティマイザがどのような実行計画を選択するかは場面によって違うと思いますが、テクニックとしては
( SELECT '122' UNION ALL SELECT '244' UNION ALL SELECT '366' ) AS Q
のような副問合せ（インラインビュー）を作り、結合処理に変換することでパフォーマンスが上がる場合もあります（AAAにINDEXがある場合）

> GROUP BY ＡＡ．Ａ１ HAVING COUNT(*) >= 0
> といった処理も重くしてしまうのでしょうか？

すみません、この条件の意味がよく分かりません。
「GROUP BY ＡＡ．Ａ１」で「COUNT(*)」が返す値はゼロ以上以外にあるのでしょうか？
（「COUNT(*)>0」ならまだ分かりますが、この場合はEXITS述語を使ったほうがパフォーマンス的に有利な気がします。）

この欄だけではSQL全般のセオリーは書ききれません。
http://books.shoeisha.co.jp/book/b104811.html
を是非読んでください。