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を使用したチューニングでもやってみようかと思います（たぶん）

あ、そろそろ今シーズンのスノボ旅行の予約もしないと…
今回も雪まつりと重なりそうなので、次回更新が北海道日記にならないように気を付けます