第58回　viewの使いどころを考えてみよう

viewを試してみよう

みなさんは普段MySQLでviewを使っているでしょうか？ viewは定義した構文をもとに、あたかもテーブルが存在するかのように仮想的なテーブルを定義できる機能になります。実際にはview自体はデータを持たず、定義したviewに従って各テーブルからデータを取得しています。ORACLEやPostgreSQLのようなマテリアライズドビューはMySQLにはありませんが、viewそのものの機能はMySQL5.0から追加されました。

今回はviewの機能を紹介しながら、使い所を考えていきたいと思います。

MySQLでのviewの作成方法

まず、テストデータとして下記のような、名前と電話番号をもつテーブルを定義してみます。

mysql> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)

mysql> use d1
Database changed
mysql> CREATE TABLE t1 (id int,name varchar(20),phone varchar(11));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t1 VALUES (1,'aaa','11111111111'), (2,'bbb','22222222222'),(3,'ccc','33333333333');
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+------+-------------+
| id   | name | phone       |
+------+------+-------------+
|    1 | aaa  | 11111111111 |
|    2 | bbb  | 22222222222 |
|    3 | ccc  | 33333333333 |
+------+------+-------------+
3 rows in set (0.00 sec)

viewを作成する時はCREATE VIEW構文を用いて、CREATE VIEW 〈view_name〉 AS 〈SELECT_statement〉を使って定義します。今回はphoneカラムを除いたviewをt1_viewとして定義してみます。

mysql> CREATE VIEW t1_view AS SELECT id, name FROM t1;
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| t1           |
| t1_view      |
+--------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1_view;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
|    3 | ccc  |
+------+------+
3 rows in set (0.01 sec)

viewを作成することができました。データもphoneカラムを除いて表示されています。

続いて2つのテーブルをJOINするようなviewを作ってみます。

mysql> CREATE TABLE t2(id int, email varchar(256));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t2 VALUES (2,'bbb@bbb.net'),(3,'ccc@ccc.com');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE view t1_t2_view AS SELECT t1.id, t1.name, t2.email FROM t1 INNER JOIN t2 ON t1.id = t2.id;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM t1_t2_view;
+------+------+-------------+
| id   | name | email       |
+------+------+-------------+
|    2 | bbb  | bbb@bbb.net |
|    3 | ccc  | ccc@ccc.com |
+------+------+-------------+
2 rows in set (0.00 sec)

viewを取得するSQLだけでt1テーブルとt2テーブルがJOINされた結果が返ってきました。

viewの変更、削除

続いてはviewの定義変更、viewの削除を行います。viewの変更はALTER VIEW 〈view_name〉 AS 〈SELECT_statement〉、削除はDROP VIEW 〈view_name〉を使います。また、CREATE OR REPLACE VIEW を使うと既存のviewが存在する場合はALTER VIEW、ない場合はCREATE VIEWが実行されます。

mysql> ALTER VIEW t1_view AS SELECT id, phone FROM t1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM t1_view;
+------+-------------+
| id   | phone       |
+------+-------------+
|    1 | 11111111111 |
|    2 | 22222222222 |
|    3 | 33333333333 |
+------+-------------+
3 rows in set (0.00 sec)

mysql> DROP VIEW t1_view;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+--------------+
| Tables_in_d1 |
+--------------+
| t1           |
+--------------+
1 row in set (0.00 sec)

ただし、サブクエリを使ったviewの作成やユーザー変数の変更などはできません。詳しい制限は公式ドキュメントの13.1.20 CREATE VIEW 構文をご確認ください。

viewのアルゴリズム

viewを作成する際にALGORITHM=〈algorithm_name〉とすることでviewのアルゴリズムを定義することができます。

MERGE

viewを参照するSELECT文のテキストとviewがマージされた後、viewの定義部分が対応するステートメントの部分と置き換えされます。先ほどのt1_viewで表すと、以下の2つが同じものになります。

SELECT * FROM t1_view WHERE id = 1;

SELECT id, phone FROM t1 WHERE id = 1;

TEMPTABLE

viewの結果がテンポラリーテーブル内に取得され、その後ステートメントを実行します。先ほどのt1_viewで表すと、以下の2つが同じものとなります。

SELECT * FROM t1_view WHERE id = 1;

SELECT * FROM (SELECT id, phone FROM t1) AS t1_view WHERE id = 1;

UNDEFINED

MySQLが実行時に効率が良いのはMERGEなのかTEMPTABLEなのかを判断して実行されます。デフォルトはUNDEFINEDになります。基本的にはMERGEが選択されるようですが、DISTINCTやGROUP BYが入っているような時はTMPTABLEが選択されるようです。

viewを使ってデータを操作する

viewによってはデータの更新が可能な場合があり、viewを経由してそのもののテーブルのデータを操作することができます。先ほど作成したt1_viewにid = 4のデータを挿入してみます。

mysql> SELECT * FROM t1_view;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
|    3 | ccc  |
+------+------+
3 rows in set (0.00 sec)

mysql> INSERT INTO t1_view VALUES (4,'ddd');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1_view;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
|    3 | ccc  |
|    4 | ddd  |
+------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM t1;
+------+------+-------------+
| id   | name | phone       |
+------+------+-------------+
|    1 | aaa  | 11111111111 |
|    2 | bbb  | 22222222222 |
|    3 | ccc  | 33333333333 |
|    4 | ddd  | NULL        |
+------+------+-------------+
4 rows in set (0.00 sec)

t1_viewを経由してもとのテーブルt1にデータが挿入されていることが確認できました。

このviewを使ってデータを操作するには、viewを定義する時にDISTINCTや、GROUP BYなどを使用していると操作することができません。詳細はマニュアルの20.5.3 更新可能および挿入可能なビューを確認してください。

viewの情報を取得する

SHOW TABLESの構文では対象がTABLEなのかviewなのか判断できません。しかし、SHOW VIEWS構文はありません。ただし、作成した全てのviewはinformation_schemaのviewsテーブルで一覧を取得することができます。

MySQL5.7以降ではsysスキーマがあるため、sysスキーマを除外する場合はwhere句にtable_schema != 'sys'とすることで、sysスキーマ以外のviewを全て取得することができます。

mysql> SELECT table_name, table_schema FROM information_schema.views WHERE table_schema != ('sys');
+------------+--------------+
| TABLE_NAME | table_schema |
+------------+--------------+
| t1_view    | d1           |
+------------+--------------+
1 rows in set (0.01 sec)

また、SHOW CREATE VIEW〈view_name〉構文または、SHOW CREATE TABLE〈view_name〉を利用することで、viewの情報を取得することができます。

この2つを組み合わせることで、viewの定義文だけを取得するといったことも可能になります。

create view文を取得する例

$ MYSQL_PWD=***** mysql -sse "SELECT  TABLE_SCHEMA, TABLE_NAME FROM information_schema.views  WHERE TABLE_SCHEMA != 'sys'" | while read table_scema table_name; do MYSQL_PWD=***** mysql -sse "show create view $table_scema.$table_name" | awk -F'\t' '{print$2";"}'; done
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `d1`.`t1_t2_view` AS select `d1`.`t1`.`id` AS `id`,`d1`.`t1`.`name` AS `name`,`d1`.`t2`.`email` AS `email` from (`d1`.`t1` join `d1`.`t2` on((`d1`.`t1`.`id` = `d1`.`t2`.`id`)));
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `d1`.`t1_view` AS select `d1`.`t1`.`id` AS `id`,`d1`.`t1`.`name` AS `name` from `d1`.`t1`;

viewの使いどころを考える

ここまではviewの使い方をみてきましたが、続いては使いどころを考えてみましょう。

viewの機能を利用することでメリットとなる部分は

特定のカラムだけを表示させることが可能になる。（⁠それ以外のカラムを表示させないようにできる）
JOINが多いSQLは再利用するのであればviewによって簡潔に記述することができ、他人でも理解し易いSQLになる

といったことが考えられます。

たとえば1つ目ののメリットを考えると、開発チームが本番用のデータを参照できるような環境があるとすれば、個人情報や機微情報の入ったカラムだけを除外して、開発チームメンバーがそのviewに対して参照のみ可能にすることができます。

また、2つ目のメリットを考えると、JOINが多いクエリなどをviewで記述することでSQLを簡潔に記述できるようになります。ただし、この場合ですと、そのクエリをチューニングするといった時に実態はJOINされたSQLであるため、viewの定義文を確認して対象のテーブルを探し出す作業などが必要なので、管理が面倒となる場合もあります。

まとめ

今回はMySQLのviewの使い方とその使いどころについて説明していきました。多少制限はありますが、viewの機能としては非常にシンプルなので、使い方次第では普段の運用を楽にしてくれる可能性もあるかもしれません。もしviewを使えそうな機会があれば導入を検討してみてください。