SQLチューニング
SDU(System Development Unit)のタキヤマです
半年前にDB関係で何か記事を…と書いていましたが、
海老名/厚木で開催されていた某10周年のまつりが忙しい中
順番が迫っているのを知ったので、何の準備も無いまま書いてみようかと思います。
(まつりの記事書いたら突き返されるかな?)
…とはいったものの、チューニング関係だとやっぱり実際にDB作って計測しながらでないと書くのは難しいので
とりあえず今回はSQLに少し触れてみます。
これもDBの種類やバージョン、データの状態とかオプティマイザの実行計画等々…によって
何が最適なのかは変わってきてしまうのでこう書けば必ず早くなるといったものでもないです。
例えば以下のようなものがあります。
INよりEXISTSを使う ※Oracle, PostgreSQL除く
--【遅】 SELECT * FROM A WHERE A.id IN(SELECT B.id FROM B); --【早】 SELECT * FROM A WHERE EXISTS(SELECT * FROM B WHERE A.id = B.id);
EXISTSでは*を使う
--【遅】 SELECT * FROM A WHERE EXISTS(SELECT B.name FROM B WHERE A.id = B.id); --【早】 SELECT * FROM A WHERE EXISTS(SELECT * FROM B WHERE A.id = B.id);
COUNTはCOUNT(列)を使う ※Oracleを除く
--【遅】 SELECT COUNT(*) FROM A --【早】 SELECT COUNT(id) FROM A
WHERE句の列に対して演算や関数を使うとインデックスが使用できない
--【遅】 SELECT * FROM A WHERE num * 10 > 100 --【早】 SELECT * FROM A WHERE num > 100 / 10
ORはなるべくINを使う
--【遅】 SELECT * FROM A WHERE name = 'a' OR name = 'Bb' --【早】 SELECT * FROM A WHERE name IN ('a','b')
ORDER BY句では列番号を使わない
--【遅】 SELECT id, name FROM A ORDER BY 1 --【早】 SELECT id, name FROM A ORDER BY id
テーブルの結合は絞り込んでから行う
--【遅】 SELECT * FROM A, B WHERE A.id = B.id AND B.num > 100; --【早】 SELECT * FROM A, (SELECT * FROM B WHERE B.num > 100) C WHERE A.id = C.id;
などなどなどなど
書ききれないくらい色々あったりします。
では最後に
こんな3つのテーブルがあるとします。
主キー、外部キー以外にインデックスは作成しないものとします。
※SQLは標準SQLでは無くOracle準拠です
CREATE TABLE item ( item_id NUMBER PRIMARY KEY , item_name VARCHAR(40) , price NUMBER ); CREATE TABLE customer ( cstmr_id NUMBER PRIMARY KEY , cstmr_name VARCHAR(40) , age NUMBER ); CREATE TABLE order ( order_id NUMBER PRIMARY KEY , item_id NUMBER , cstmr_id NUMBER , order_date DATETIME , FOREIGN KEY (item_id) REFERENCES item (item_id) , FOREIGN KEY (cstmr_id) REFERENCES customer (cstmr_id) );
20代の20時~22時の注文のうち、新しいものから順に1000件の中で
売上合計が高い商品順に並べる。
これを以下のようなSQLで検索しようとしたところ
想定していたよりもレスポンスが悪い結果となりました。
ではこのSQLをどのように書き換えたらレスポンスが改善できるでしょうか?
SELECT NarrowOrder.item_name as "商品名" , COUNT(*) as "売上件数" , SUM(NarrowOrder.price) as "売上合計" FROM ( SELECT item_name , price FROM ( SELECT item.item_name , item.price FROM order , customer , item WHERE customer.cstmr_id = order.cstmr_id AND item.item_id = order.item_id AND customer.age >= 20 AND customer.age < 30 AND TO_CHAR(order.order_date, 'hh24:mi:ss') >= '20:00:00' AND TO_CHAR(order.order_date, 'hh24:mi:ss') < '22:00:00' ORDER BY order.order_id DESC ) WHERE ROWNUM >= 1000 ) NarrowOrder GROUP BY NarrowOrder.item_name ORDER BY 3 DESC ;
以下のSQLは一例となりますが、
おそらく先程のSQLと比較すると、
データ量が多ければ多いほどはっきりと差が出てくると思われます。
(実際に試してはいないので説得力は弱いですが)
SELECT item.item_name as "商品名" , COUNT(*) as "売上件数" , SUM(item.price) as "売上合計" FROM ( SELECT item_id FROM ( SELECT item_id FROM order WHERE cstmr_id in ( SELECT cstmr_id FROM customer WHERE age >= 20 AND age < 30 ) AND TO_NUMBER(TO_CHAR(order.order_date, 'hh24miss')) BETWEEN 200000 AND 220000 ORDER BY order_id DESC ) WHERE ROWNUM >= 1000 ) NarrowOrder JOIN item ON NarrowOrder.item_id = item.item_id GROUP BY item.item_name ORDER BY SUM(item.price) DESC ;
※1000件の絞込みはOracle12cからインラインビューでROWNUMを使わなくとも
Order by句の次に「FETCH FIRST 1000 ROWS ONLY」と記載することも出来ます。
ポイントはデータの絞込みと結合の順番になります。
まずはユニークなデータであるcustomerを絞り込んでおき、
対象のデータを減らした上でorderと結合しています。
また、itemの結合をデータ件数の一番多いであろうorderでは無く、
検索条件を満たした上で1000件に絞った状態のNarrowOrderに対して行うようにしています。
注文時間の絞込みについては文字列で行わず、数値で行うようにし、かつBETWEENを使用するようにしています。
※インデックスを作成する場合はorderテーブルにcstmr_idとorder_dateの複合インデックスを作りヒント句で強制、日付型のままBETWEENを使わない方が早くなります
と、ここまで書いてみましたが、実際にはコストベースオプティマイザだと
しばらく使っているうちに統計情報からどんな実行計画が作られるかにもよってくるので
実践でのチューニングは色々と手間が掛かってくるわけです。
次回順番が回ってきた時は、実際のDBを使用したチューニングでもやってみようかと思います(たぶん)
あ、そろそろ今シーズンのスノボ旅行の予約もしないと…
今回も雪まつりと重なりそうなので、次回更新が北海道日記にならないように気を付けます