概要

現場でビュー, マテリアライズド・ビューを扱う機会があったため、備忘録としてまとめる。

環境

PostgreSQL 13.1

ビュー(View)とは

仮想的なテーブルを指す。データは内部に持たず、実態は**「名前をつけたSELECT文」**である。

ビューのメリット

結合などを利用した複雑な検索処理をシンプルに記述できる。重複する検索処理にも有効。
機密情報をアクセスしないようなビューを作成することで、データ参照を許可する範囲を適切に定めることができる。

ビューのデメリット

実行されるSQL文は、送信するSQLから見ると想像以上に負荷がかかる処理になる可能性がある。
データを持たないため、後述するマテリアライズド・ビューより処理速度は遅い。
インデックスの付与ができない。

ビューの作成と削除

以下の家計簿テーブルを例に、10月分の家計簿だけを抽出した「家計簿10月」のビューを作成。

日付	メモ	収入	支出
2020-09-29	食費	0	1000
2020-10-10	映画	0	2000
2020-10-12	歯医者	0	1900
2020-10-20	給料	200000	0
2020-10-28	食費	0	1500
2020-11-12	洋服	0	5000
2020-11-20	給料	200000	0

-- ビューの作成
CREATE VIEW 家計簿10月 AS 
SELECT * FROM 家計簿
WHERE 日付 >= '2020-10-01'
AND   日付 <= '2020-10-30';

上記のSQL文でビューを作成すると、検索文は以下のようにシンプルなものになる。

-- ビューを利用した検索
SELECT * FROM 家計簿10月;

| 日付       | メモ   | 収入   | 支出 | 
| ---------- | ------ | ------ | ---- | 
| 2020-10-10 | 映画   | 0      | 2000 | 
| 2020-10-12 | 歯医者 | 0      | 1900 | 
| 2020-10-20 | 給料   | 200000 | 0    | 
| 2020-10-28 | 食費   | 0      | 1500 |

ビューの削除は以下のSQL文で実施する。

DROP VIEW 家計簿10月;

マテリアライズド・ビュー

SELECT文による検索結果をキャッシュして保持している仮想的なテーブル。
ビューの性能上の問題となる場合に、パフォーマンス向上を目的として使用される。
ビューはSQL文を処理する度にDBに再検索をかけているが、マテリアライズド・ビューでは検索結果を保持するため、あるビューを頻繁に参照する場合に、毎回検索処理を実行する必要がなくなり性能向上に繋がる。

また、マテリアライズド・ビューはインデックスの作成が可能である。
ただ、元のテーブルの構造やデータが更新された際、マテリアライズド・ビューも更新するにはリフレッシュ(バッチ更新)が必要となる。

ビューとマテリアライズド・ビューの比較

項目	ビュー	マテリアライズド・ビュー
データの保持	しない	する
参照の動作	SQL文を実行し処理結果を返す	保持しているSQL文の処理結果を返す
処理速度	遅い	速い
インデックスの作成	できない	できる

リフレッシュについて

マテリアライズド・ビューは、マテリアライズド・ビュー作成時のテーブル情報を保持するだけであり、その後のテーブルへの更新処理（UPDATEやDELETE）によって更新されない。マテリアライズド・ビューの内容を最新のテーブルの状態と同じ状態にしたい場合にリフレッシュが必要となる。

-- リフレッシュのコマンド
REFRESH MATERIALIZED VIEW マテリアライズド・ビュー名;

マテリアライズド・ビューの動作確認

ビューでも使った家計簿テーブルでマテリアライズド・ビューを作成する。

-- テーブル作成/データ投入
CREATE TABLE 家計簿(
日付 DATE PRIMARY KEY, メモ TEXT,収入 INT,支出 INT);

INSERT INTO 家計簿(日付, メモ, 収入, 支出)
VALUES('2020-9-29', '食費', 0, 1000),
('2020-10-10', '映画', 0, 2000),
('2020-10-12', '歯医者', 0, 1900),
('2020-10-20', '給料', 200000, 0),
('2020-10-28', '食費', 0, 1500),
('2020-11-12', '洋服', 0, 5000),
('2020-11-20', '給料', 200000, 0);

-- マテリアライズド・ビューを作成
CREATE MATERIALIZED VIEW 家計簿10月
AS --この下にSQLを書く
SELECT * FROM 家計簿
WHERE 日付 >= '2020-10-01'
AND   日付 <= '2020-10-30';

-- 検索
SELECT * FROM 家計簿10月;
| 日付       | メモ   | 収入   | 支出 | 
| ---------- | ------ | ------ | ---- | 
| 2020-10-10 | 映画   | 0      | 2000 | 
| 2020-10-12 | 歯医者 | 0      | 1900 | 
| 2020-10-20 | 給料   | 200000 | 0    | 
| 2020-10-28 | 食費   | 0      | 1500 |

リフレッシュの動作確認

-- 元のテーブル更新
UPDATE 家計簿 SET メモ = 映画鑑賞 where 日付 = '2020-10-10';

    日付    |   メモ   |  収入  | 支出
------------+----------+--------+------
 2020-09-29 | 食費     |      0 | 1000
 2020-10-12 | 歯医者   |      0 | 1900
 2020-10-20 | 給料     | 200000 |    0
 2020-10-28 | 食費     |      0 | 1500
 2020-11-12 | 洋服     |      0 | 5000
 2020-11-20 | 給料     | 200000 |    0
 2020-10-10 | 映画鑑賞 |      0 | 2000     -- 映画->映画鑑賞に更新
(7 rows)

元のテーブルが更新された状態でマテリアライズド・ビューを確認すると、更新が反映されていないことが確認できる。

SELECT * FROM 家計簿10月;

    日付    |  メモ  |  収入  | 支出
------------+--------+--------+------
 2020-10-10 | 映画   |      0 | 2000      -- 更新されていない
 2020-10-12 | 歯医者 |      0 | 1900
 2020-10-20 | 給料   | 200000 |    0
 2020-10-28 | 食費   |      0 | 1500

このタイミングでリフレッシュコマンドを実行し、更新されたかを確認すると、更新が反映されていることがわかる。

-- マテリアライズド・ビューの更新
REFRESH MATERIALIZED VIEW 家計簿10月;
REFRESH MATERIALIZED VIEW

SELECT * FROM 家計簿10月;

   日付    |   メモ   |  収入  | 支出
------------+----------+--------+------
 2020-10-10 | 映画鑑賞 |      0 | 2000     -- メモの内容が正しく更新されていることが確認できる
 2020-10-12 | 歯医者   |      0 | 1900
 2020-10-20 | 給料     | 200000 |    0
 2020-10-28 | 食費     |      0 | 1500

まとめ

今回はビュー、マテリアライズド・ビューの概要と、PostgreSQLを使ったビュー・マテリアライズド・ビューの動作確認の様子を記述した。調査の中で、MySQL(8.0時点)ではマテリアライズド・ビューが非対応だったり、Oracle DBだとマテリアライズド・ビューの作成時にリフレッシュのタイミングの指定が可能だったり、DBMSの種類によって仕様が結構違うという点が見られた。

参考

スッキリわかるSQL入門
 マテリアライズドビューの機能（PostgreSQL）
マテリアライズド・ビューについて
 Oracle DB言語リファレンス CREATE MATERIALIZED VIEW