MySQL道普請便り

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

この記事を読むのに必要な時間:およそ 4 分

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を使えそうな機会があれば導入を検討してみてください。

コメント

コメントの記入