2012年5月13日日曜日

MySQL VIEWについてメモ

VIEWは使ったことなかったが、使用されてるシステムをみることになったのでメモ。
ちょっといじってみた感じVIEWじゃない方がいいんじゃないかとも思う。重いので。

まず適当にテーブルとビューを用意。
CREATE TABLE `students` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `class` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  `score` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `class` (`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE VIEW class_summary AS 
   SELECT `class`,
          MAX(`score`) max,
          MIN(`score`) min,
          AVG(`score`) avg
     FROM `students`
 GROUP BY `class`;

# 適当にデータ挿入

mysql> select * from class_summary;
+-------+------+------+---------+
| class | max  | min  | avg     |
+-------+------+------+---------+
|     1 |   93 |   32 | 66.3333 |
|     2 |   96 |   12 | 62.8500 |
|     3 |   77 |   30 | 54.1667 |
|     4 |   75 |   30 | 53.7000 |
|     5 |  100 |   46 | 71.4000 |
+-------+------+------+---------+
5 rows in set (0.00 sec)
とりあえずgroup by使ったビューを試してみたかったので、こんな感じ。

で、class=3のsummaryが欲しいとすると、きっとこんなSQL。
mysql> SELECT * FROM class_summary WHERE class = 3;
+-------+------+------+---------+
| class | max  | min  | avg     |
+-------+------+------+---------+
|     3 |   77 |   30 | 54.1667 |
+-------+------+------+---------+
1 row in set (0.01 sec)

explainを見ると、
mysql> EXPLAIN SELECT * FROM class_summary WHERE class = 3;
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
|  1 | PRIMARY     |  | ALL   | NULL          | NULL  | NULL    | NULL |    5 | Using where |
|  2 | DERIVED     | students   | index | NULL          | class | 1       | NULL |   99 |             |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
2 rows in set (0.00 sec)
studentsのフルインデックススキャンになってしまう。。そりゃ遅い罠。

VIEWを使わなければちゃんとインデックスを使用できるし、もちろん同じ結果を得られる。
mysql> EXPLAIN SELECT class, MAX(score) max, MIN(score) min, AVG(score) avg FROM students WHERE class = 3 GROUP BY class;
+----+-------------+----------+------+---------------+-------+---------+-------+------+-------+
| id | select_type | table    | type | possible_keys | key   | key_len | ref   | rows | Extra |
+----+-------------+----------+------+---------------+-------+---------+-------+------+-------+
|  1 | SIMPLE      | students | ref  | class         | class | 1       | const |   30 |       |
+----+-------------+----------+------+---------------+-------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> SELECT class, MAX(score) max, MIN(score) min, AVG(score) avg FROM students WHERE class = 3 GROUP BY class;
+-------+------+------+---------+
| class | max  | min  | avg     |
+-------+------+------+---------+
|     3 |   77 |   30 | 54.1667 |
+-------+------+------+---------+
1 row in set (0.00 sec)

こんなことになるなら集約したVIEWの上手な使い時があまりわからない。
SQL書くのを省略するため(？)に使うのはデータが増えてきたときに遅くなのでやめた方が良さそう。

システムではもっと複雑なビューで結合もしてるので、ビューなしのSQLに書き換えるのは面倒だな。。
ラベル: