SQLで効率のよい書き方を研究
こんばんは。
笹亀です。
弊社では、MySQLを使用しているシステムが多くあります。
みなさんも、MySQLは頻繁に使用しているのではないでしょうか。
私が特にMySQLなどのRDBMS（Relational Database Management System）を使用するときに気をつけていることが、SQLを記述するということです。
私は「どのようにSQLを発行すればよいか」という点を気にしながら、コーディングをすることが多いです。
自分が経験したことで、SQLの書き方ひとつでも大きな問題になったこともあり、
SQLの書き方には知恵を絞って、いかに効率がよいSQLを記述するかということを心がけるようになりました。
今回はSQLの結果では同じ結果を返す、3種類の結合の速度を比べてみました。
今回はサンプルテーブルとして、下記の２つのテーブルを使用します
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を三種類作成しました。
取得するデータは「ユーザテーブルの名前、明細の数、金額＊購入した個数の合計」
１．INNER JOINでユーザテーブルと明細テーブルを結合した結果です。
２．LEFT JOINでユーザテーブルと明細テーブルを結合した結果です。
３．サブクエリでユーザテーブルを絞りこみを行ったデータに対して、INNER JOINで明細テーブルを結合した結果です。
※それぞれの実行時間はターミナル上の実行時間です。
１．
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)
２．
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)
３．
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)
１は２や３に比べて、約2倍の処理時間がかかってしまっています。
このように書き方ひとつで、結構な違いが出てきます。
みなさんのお勧めのSQLの書き方がございましたら、ご教授いただけたら幸いです。
