SQLで効率のよい書き方

2008.04.24

その他

最近,私はAvionのレスポンス改善についてをよく調査している。
レスポンスとデータベースで実行されるもののスピードに強く関係があるのは常識だと思う。
だから、SQLで効率のよい書き方はプログラムのレスポンスに対して大事なことになった。

今回はSQLの結果では同じ結果を返す、3種類の結合の速度を比べてみた。
今回はサンプルテーブルとして、下記の2つのテーブルを使用する。
user_m;
+———–+————+——+—–+———————+
|Field   Type    Null Key Default Extra
+———–+————+——+—–+———————+
|user_id   int    NO PRI NULL auto_increment
|name  varchar(100) YES   NULL
|sex   bit    NO   1
|birth_day datetime   NO   1970-01-01
|delete_flag bit    NO   0
+———–+————+——+—–+———————+

user_buy_list;
+———- —+———– —+——+—–+—————-+
| Field Type Null Key Default Extra
+————–+————–+——+—–+—————-+
| buy_id int NO PRI NULL auto_increment
| user_id int NO FOR -1|
| product_name varchar(100) YES NULL
| price smallint NO 0
| number smallint NO 0

いわゆる、ユーザテーブルとユーザが購入した商品の明細テーブルだ。

ループでランダムテストデータを入れて、user_mテーブルに20000件、user_buy_list
に60000件になった。

さて、いよいよ実験をしてみたいとおもう。
ユーザテーブルと明細テーブルの結合SQLを三種類作成した。
取得するデータは「ユーザテーブルの名前、明細の数、金額*購入した個数の合計」
1.INNER JOINでユーザテーブルと明細テーブルを結合した結果。
2.LEFT JOINでユーザテーブルと明細テーブルを結合した結果。
3.サブクエリでユーザテーブルを絞りこみを行ったデータに対して、INNER JOINで
明細テーブルを結合した結果。

—————————————————————
1.
SELECT
um.name, count( * ) AS jyutyu,
sum(ubl.price * ubl.number) AS total
FROM user_m um
INNER JOIN user_buy_list ubl ON um.user_id=ubl.user_id
WHERE um.birth_day <= ‘1960’ AND um.sex = ‘1’
GROUP BY um.user_id;

**************************************************
4504 rows in set, 1 warning (0.49 sec)

—————————————————————
2.
SELECT
um.name, count( * ) AS jyutyu,
sum(ubl.price * ubl.number) AS total
FROM user_m um
LEFT JOIN user_buy_list ubl ON um.user_id=ubl.user_id
WHERE um.birth_day <= ‘1960’ AND um.sex = ‘1’
GROUP BY um.user_id;

***************************************************
4504 rows in set, 1 warning (0.26 sec)

—————————————————————

3.
SELECT
um.name, count( * ) AS jyutyu,
sum(ubl.price * ubl.number) AS total
FROM
(SELECT *
FROM user_m
WHERE birth_day <= ‘1960’ AND sex = ‘1’ ) as um
INNER JOIN user_buy_list ubl ON um.user_id=ubl.user_id
GROUP BY um.user_id;

***************************************************
4504 rows in set, 1 warning (0.27 sec)
—————————————————————-

1は2や3に比べて、約2倍の処理時間がかかってしまっている。
このように書き方ひとつで、結構な違いが出てくる。

みなさんのお勧めのSQLの書き方がございましたら、ご教授いただけたら幸いです。