アシアルブログ

アシアルの中の人が技術と想いのたけをつづるブログです

SQLで効率のよい書き方を研究

こんばんは。
笹亀です。

弊社では、MySQLを使用しているシステムが多くあります。
みなさんも、MySQLは頻繁に使用しているのではないでしょうか。
私が特にMySQLなどのRDBMS(Relational Database Management System)を使用するときに気をつけていることが、SQLを記述するということです。
私は「どのようにSQLを発行すればよいか」という点を気にしながら、コーディングをすることが多いです。
自分が経験したことで、SQLの書き方ひとつでも大きな問題になったこともあり、
SQLの書き方には知恵を絞って、いかに効率がよいSQLを記述するかということを心がけるようになりました。
今回はSQLの結果では同じ結果を返す、3種類の結合の速度を比べてみました。
今回はサンプルテーブルとして、下記の2つのテーブルを使用します


mysql> desc user_m;
+-----------+------------+------+-----+---------------------+----------------+
|Field      |Type        | Null | Key | Default             | Extra          |
+-----------+------------+------+-----+---------------------+----------------+
|user_id    | int(11)    | NO   | PRI | NULL                | auto_increment |
|name       |varchar(100)| YES  |     | NULL                |                |
|sex        |tinyint(1)  | NO   |     | 1                   |                |
|birth_day  |datetime    | NO   |     | 1970-01-01 00:00:00 |                |
|delete_flag|tinyint(1)  | NO   |     | 0                   |                |
+-----------+------------+------+-----+---------------------+----------------+




mysql> desc user_buy_list;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| buy_id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id      | int(11)      | NO   | MUL |         |                |
| product_name | varchar(100) | YES  |     | NULL    |                |
| price        | smallint(6)  | NO   |     | 0       |                |
| number       | smallint(6)  | NO   |     | 0       |                |
+--------------+--------------+------+-----+---------+----------------+


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

テストデータがないと、どのくらい効率がでるのかがわからないので、
ユーザデータのスクリプトを作成して、テストデータを実行しました。


<?php

/*
 * DB接続部分などの初期処理は省略
 */

for($i=0;$i<20000;$i++) {
	$sex = rand(1, 2);
	$name = "笹亀 弘" . $i;
	$birthday = rand(1930, 1995) . "-" . rand(1, 12) . "-" . rand(1, 28);
	if ($i > 200  & & $i % 200 == 0) {
		$delete_flag = '1';
	}else{
		$delete_flag = '0';
	}
	
	$sql = "INSERT INTO user_m (name,sex,birth_day,delete_flag)VALUES('{$name}','{$sex}','{$birthday}','{$delete_flag}')";
	$res = $db->query($sql);

}
?>


次に、明細テーブルも適当に3万件くらいデータを作成します。


<?php

/*
 * DB接続部分などの初期処理は省略
 */


for($i=0;$i<20000;$i++) {
	$product_num = rand(1, 5);
	for($j=0;$j<$product_num;$j++) {
		$product_name = "商品商品商品商品" . $j;
		$price = rand(1000, 2000);
		$number = rand(1, 5);

		$sql = "INSERT INTO user_buy_list (user_id,product_name,price,number)VALUES('{$i}','{$product_name}','{$price}','{$number}')";
		$res = $db->query($sql);
	}
}
?>


上記のデータ挿入により、テーブルのデータ数は以下のようになりました。


mysql> select count(*) from user_m;
+----------+
| count(*) |
+----------+
|    20000 |
+----------+

mysql> select count(*) from user_buy_list;
+----------+
| count(*) |
+----------+
|    59770 |
+----------+


さて、いよいよ実験をしてみたいとおもいます。
ユーザテーブルと明細テーブルの結合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の書き方がございましたら、ご教授いただけたら幸いです。